Integration Patterns

The SQL Database Authorizer supports multiple integration patterns to accommodate different architectures and development environments. You can interact with the Authorizer via a standard REST API, or use one of the available SDKs for Java or .NET to simplify integration and reduce implementation effort. All options provide consistent behavior and support the same enforcement capabilities, including row-level filtering, column masking, and conditional logic. Choose the pattern that best fits your system design and technology stack.

• REST API
• Java SDK
• .NET SDK

API Endpoint

You can use the ReSQL API call in a variety of deployment scenarios, such as data services, SQL proxies, or middleware, to ensure that access control is applied consistently before SQL queries reach your database or Google BigQuery engine. The Authorizer evaluates incoming SQL statements against PlainID Policies and returns a rewritten version that enforces row-level filtering, column-level restrictions, and masking as needed.

The Authorizer supports the full range of standard SQL syntax across PostgreSQL, SQL Server, and BigQuery, including quantified comparisons (ANY/ALL/SOME), LATERAL joins, GROUPING SETS and CUBE, aggregate FILTER clauses, ordered-set aggregates (e.g. PERCENTILE_CONT, MODE() WITHIN GROUP), VALUES table constructors, and BigQuery pipe syntax (|>). Queries using these constructs are parsed, evaluated, and rewritten the same way as any other supported query.

To explore and integrate with the API, you can either download the full OpenAPI specification or use our Postman collection to test requests and understand response behavior interactively.

Download the OpenAPI Spec

Postman Collection

Use these resources to:

• Review supported request fields, response structures, and error handling
• Explore enforcement behaviors like masking and column filtering
• Test different configurations and enforcement flags using your own SQL queries

Once familiar with the structure, you can start integrating the API directly into your authorization flow. For SDK-based integration options, see the Integration Patterns guide.

Request Format

The API accepts a JSON request body with the SQL query and metadata required for Policy evaluation.

Request URL

http://ADDRESS_OF_AUTHORIZER_SERVICE/resql

Request Headers

Allowlisting

To make the header x-user-info available to the PDP, it must be explicitly allowlisted:

config:
  runtime:
    allowedForPassThroughHeaders:
      - "x-user-info"

Once allowlisted, x-user-info is forwarded to the PDP runtime and can be used in policy evaluation, logging, or downstream calls, preventing unintended exposure of other headers.

Core Parameters

Enforcement Flags

Runtime Fine-Tune

You can tailor how the Policy Decision Point (PDP) evaluates access requests and formats the authorization response by configuring the RuntimeFineTune section of your request.

Runtime parameters are passed as part of the API call. You can use the ReSQL API Call in our Postman Collection as a reference for structuring the request. All Runtime parameters are optional. If a parameter is not provided, the PDP applies its default behavior.

Note: Parameters like clientID, clientSecret, and entityTypeId are not part of the runtimeFineTune. See the below examples or the Postman collection for structured examples.

For the complete and up-to-date list of supported Runtime parameters, refer to the Policy Resolution API documentation.

Below are two runtimeFineTune examples:

• UseCache – Specifies whether cached policy evaluation results can be reused during policy resolution.
• CombinedMultiValue – Controls how multi-value identity attributes are evaluated when calculating access decisions.

Note: Enabling runtimeFineTune flags that modify the response structure may impact response parsing and lead to unexpected behavior.

Example Requests

Simple Query

{
  "sql": "SELECT e1.first_name, e1.last_name, e1.department_id FROM employees e1 WHERE e1.first_name = 'bardak'",
  "user": "Jordan",
  "clientID": "PBMHCUXHGCQ629H234Q7",
  "clientSecret": "B0JCgbFFWbNIls3NjKy42352v0jvL2JAzb2345dg",
  "entityAttributes": {
    "DemoGroup": [{}]
  },
  "flags": {
    "expandStarColumn": true,
    "emptyRLSTreatAsDenied": false,
    "emptyCLSTreatAsPermitted": false,
    "ignoreRuntimeCLSResponse": false,
    "oppositeColumnFilteringBehavior": false,
    "policiesJoinOperation": "OR",
    "runtimeCLSAsMasked": true,
    "columnsResourceType": ""
  }
}

Complex Query

{
  "sql": "WITH ranked_salaries AS (SELECT employee_id, amount, ROW_NUMBER() OVER (ORDER BY amount DESC) AS salary_rank FROM salaries) SELECT e.id, e.first_name, e.last_name, rs.amount FROM employees e, ranked_salaries rs WHERE e.id = rs.employee_id AND rs.salary_rank <= 5 ORDER BY rs.amount DESC",
  "entityTypeId": "Main",
  "clientID": "PBMHCUXHGCQ629H234Q7",
  "clientSecret": "B0JCgbFFWbNIls3NjKy42352v0jvL2JAzb2345dg",
  "flags": {
    "expandStarColumn": true,
    "emptyRLSTreatAsDenied": false,
    "emptyCLSTreatAsPermitted": false,
    "ignoreRuntimeCLSResponse": false,
    "oppositeColumnFilteringBehavior": true,
    "policiesJoinOperation": "OR",
    "runtimeCLSAsMasked": true,
    "columnsResourceType": "",
  "runtimeFineTune": {
    "useCache": false,
    "combinedMultiValue": false,
    "accessTokenFormat": "JSON"  
    }
  }
}

Example Response

{
  "sql": "SELECT e1.first_name, e1.last_name, NULL AS department_id FROM employees e1 WHERE e1.first_name = 'bardak' AND e1.location = 'US'",
  "wasModified": true,
  "outcome": "RESTRICTED",
  "coverage": "COMPLETE",
  "assets": [
    { "name": "hr_db.public.employees", "governed": true }
  ],
  "error": ""
}

Response Fields

Outcome Values

The outcome field describes what the Authorizer applied to the query across all referenced Assets.

The Authorizer returns RESTRICTED when any referenced Asset has a row filter other than full access (1=1), or a column with an operation other than VIEW.

Coverage Values

The coverage field describes how many of the referenced Assets exist in PlainID. The Authorizer derives it only from the governed flag of each entry in assets.

Asset Fields

Each entry in the assets array includes the following fields:

The assets array does not include CTE names, subquery aliases, derived tables, temporary tables, or table-valued functions. These objects also do not count toward coverage.

How Outcome and Coverage Relate

The Authorizer calculates outcome and coverage independently, and neither value overrides the other. For example, a join between a restricted governed Asset and an undefined Asset returns RESTRICTED together with PARTIAL, so you can see both facts. The one exception: an ERROR outcome always returns a NOT_APPLICABLE coverage.

The response fields always stay consistent with each other:

• RESTRICTED always returns wasModified: true, and UNRESTRICTED always returns wasModified: false.
• The error field contains a value only when outcome is ERROR. Authorization results never populate error, so you never need to parse the error string to understand how Policies affected a query.

Response Examples

Governed Asset, No Restrictions

A permissive mode Policy that states no masks, with full row access. The PDP returns the row filter 1=1 and the VIEW operation for every column, so the Authorizer injects nothing.

{
  "sql": "SELECT * FROM europe_dw.dbo.vw_emea_chatbot",
  "wasModified": false,
  "outcome": "UNRESTRICTED",
  "coverage": "COMPLETE",
  "assets": [
    { "name": "europe_dw.dbo.vw_emea_chatbot", "governed": true }
  ],
  "error": ""
}

Asset Not Defined in PlainID

The query references a table that PlainID does not define, such as a mistyped name. This response matches the previous example in every field except coverage.

{
  "sql": "SELECT * FROM europe_dw.dbo.vw_emea_chatbo",
  "wasModified": false,
  "outcome": "UNRESTRICTED",
  "coverage": "NONE",
  "assets": [
    { "name": "europe_dw.dbo.vw_emea_chatbo", "governed": false }
  ],
  "error": ""
}

Row Filtering Applied

The PDP returns a row filter other than 1=1.

{
  "sql": "SELECT id, email FROM europe_dw.dbo.employees WHERE region = 'EMEA'",
  "wasModified": true,
  "outcome": "RESTRICTED",
  "coverage": "COMPLETE",
  "assets": [
    { "name": "europe_dw.dbo.employees", "governed": true }
  ],
  "error": ""
}

Column Masking Applied

A least privilege mode Policy grants id, so its operation is VIEW. The Policy does not state salary, so the default mask applies.

{
  "sql": "SELECT id, NULL AS salary FROM europe_dw.dbo.employees",
  "wasModified": true,
  "outcome": "RESTRICTED",
  "coverage": "COMPLETE",
  "assets": [
    { "name": "europe_dw.dbo.employees", "governed": true }
  ],
  "error": ""
}

The response does not identify which column the Authorizer masked or why. To see that detail, use the Authorization Audit Report.

Join Between a Restricted Asset and an Undefined Asset

Each Asset carries its own governed value. The response shows both that the Authorizer rewrote the query and that coverage is incomplete.

{
  "sql": "SELECT * FROM europe_dw.dbo.employees a JOIN europe_dw.dbo.unknown_tbl b ON a.id = b.id WHERE a.region = 'EMEA'",
  "wasModified": true,
  "outcome": "RESTRICTED",
  "coverage": "PARTIAL",
  "assets": [
    { "name": "europe_dw.dbo.employees", "governed": true },
    { "name": "europe_dw.dbo.unknown_tbl", "governed": false }
  ],
  "error": ""
}

Error Example

{
  "sql": "",
  "wasModified": false,
  "outcome": "ERROR",
  "coverage": "NOT_APPLICABLE",
  "assets": [],
  "error": "Error processing query: Error runtime HTTP status: 424, msg: Read timed out, agentName: internal_asset"
}

SDK Support

To streamline integration into your codebase, PlainID provides SDKs that wrap the REST API and handle request generation, flag encoding, and response parsing.

• Java SDK
• .NET SDK
• Python SDK

Use these SDKs to avoid manual request building and ensure consistency with evolving API standards.

Java SDK

This library enables the use of the PlainID SQL-PDP service with Java SpringBoot and applies SQL filtering rules to SQL queries generated by SpringBoot Hibernate. A minimum Java version of 1.8 and Spring Boot version 2.0.0 or higher is required.

To include this library using Maven:
Add the following dependency to your pom.xml:

<dependency>
    <groupId>com.plainid.libs</groupId>
    <artifactId>plainid-sql-pdp-auth-lib</artifactId>
    <version>1.2.17</version>
</dependency>

Initializing the Library Configuration

Configure the library by adding the following settings to your application.yaml or application.properties. Alternatively, you can choose not to define properties in your application.properties file and instead, set everything through Environment variables:

plainid.sqlpdp.pdpUrl=http://localhost:8080/sql
plainid.sqlpdp.clientID=XXXXXX
plainid.sqlpdp.clientSecret=XXXXXXXXXX
plainid.sqlpdp.entityTypeId=Main
plainid.sqlpdp.emptyCLSTreatAsPermitted=true
plainid.sqlpdp.emptyRLSTreatAsDenied=false
plainid.sqlpdp.ignoreRuntimeCLSResponse=true
plainid.sqlpdp.ignoreSqlPdpServiceErrors=true
plainid.sqlpdp.oppositeColumnFilteringBehavior=true
plainid.sqlpdp.expandStarColumn=true
plainid.sqlpdp.policiesJoinOperation=OR
plainid.sqlpdp.runtimeCLSAsMasked=true
plainid.sqlpdp.requestMapping='[JSON_ARRAY_OF_REQUEST_MAPPING_CONFIGS]'
plainid.sqlpdp.entityIdJwtMapping=user_id
plainid.sqlpdp.columnsResourceType=CustomColumns

To configure the way a request is sent to the PDP service, create a bean of type QueryModificationFlags and set the desired flags according to the following parameters or set everything via the Environment variables:

@Bean
public QueryModificationFlags queryModificationFlags() {
    return new QueryModificationFlags(true, true, false, true, true, "OR", true);
}

Request Mapping

The parameter accepts configuration properties that define mappings between various sources and their mapping targets in the PDP/Runtime calls. A source refers to where data is retrieved from and a target specifies where the data should be mapped.

Sources

• headers: Data is retrieved from the request headers.
• url: Data is extracted from the request URL based on a regex pattern.
• qs (query-string parameter): Data is retrieved from the query string parameters of the request.
• env (environment variable): Data is retrieved from the environment variables of the application.

Targets

• entityAttributes: The data is mapped to the entityAttributes section in the Policy resolution call.
• environment: The data is mapped to the Environment section in the Policy resolution call.
• contextData: The data is mapped to the contextData section in the Policy resolution call.

Configuration Structure

{ "source.sourceKey": "target.targetKey" }

Fields:

• source: Specifies the source type. It can be headers, url, env or qs.
• sourceKey: For headers it is the header to look for, following an optional possible JSON path extraction rule; for qs, this is the exact key to look for. For url, this is a regex pattern to match and extract data.
• target: Specifies the target type. It can be entityAttributes, environment, or contextData.
• targetKey: This is the key under which the data from the source is stored in the target.

Example of a configuration:

{
  "env.MY_ENV_VAR": "entityAttributes.myEnvVar",
  "headers.x-user-info.$.user.department": "contextData.userInfo",
  "headers.Accept-Encoding": "entityAttributes.Accept-Encoding-Target",
  "qs.difficulty": "environment.difficulty",
  "url./api/([^/]+)(/|$)": "environment.urlPartExample"
}

• Data from the MY_ENV_VAR environment variable is mapped to entityAttributes under the key myEnvVar.
• Data from the x-user-info header is extracted and mapped through json path $.user.department to contextData under the key userInfo.
• Data from the Accept-Encoding header is mapped to contextData under the key Accept-Encoding-Target.
• Data from the difficulty querystring parameter is mapped to the environment section.
• Data matching the regex pattern /api/([^/]+)(/|$) from the URL is mapped to entityAttributes under the key urlPartExample.

The configuration should be passed as a JSON string to the plainid.sqlpdp.requestMapping property or environment variable: PLAINID_RUNTIME_REQUEST_MAPPING.

Using Context and Authorization

To fetch the current user and security context sent to the SQL Database Authorizer Service, the library retrieves the information through a call to Spring Security Core Context by default:

Authentication authentication=SecurityContextHolder.getContext().getAuthentication();
String u=authentication==null null:authentication.getName();

To override this behavior and inject your user ID and context, use the following code before performing database actions:

com.plainid.sqlpdplib.Context.setUsername("YOUR_USERNAME");

To inject entity attributes within the Runtime for further Policy resolution based on Dynamic Groups, insert the following code before performing any database actions:

EntityAttributes entityAttributes = new EntityAttributes();
entityAttributes.addAttribute("ATTRIBUTE_NAME", "ATTRIBUTE_VALUE");
com.plainid.sqlpdplib.Context.setEntityAttributes(entityAttributes);

.NET SDK

The PlainID SQL PDP Authorizer Library enables the use of the SQL PDP service with Entity Framework Core. It applies SQL filtering rules in addition to the SQL queries generated by Entity Framework Core. The library is compatible with .NET Core 6 and .NET Core 7, and it also supports .NET Core/Standard 2 for ADO.NET-like solutions.

Installing via NuGET

dotnet add package PlainID.SQL.PDP.Auth.Lib-v 1.0.13

Quick Library Setup using the "Magic Hook" Method

PlainLibrary.HookAll(IServiceCollection services, Options plainOptions = null)

For advanced setup:

Authorization Middleware:

PlainLibrary.HookAuthorizationMiddleware(IServiceCollection services, Options plainOptions = null)

DB interceptor:

PlainLibrary.HookDb(Options plainOptions = null)

Initializing Library Options

Library options can be configured using the OptionsBuilder. These options can be set through code or environment variables:

// Reading from environment variables.
OptionsBuilder builder = new OptionsBuilder();
           builder.LoadFromEnvironmentVariables();
           _options = builder.Build();

// Assigning options manually.  
var builder = new OptionsBuilder();
       builder
           .WithClientId("PIXEPN6N4AKKCYES51FL")
           .WithClientSecret("xUGcm3UF2q7NKzSDuNLtfq1utN38H2nF0Exrvr3O")
           .WithModifierServiceURL("https://sdl-pdp-modifier.local.platform.test/resql")
           .WithFlags(new QueryModificationFlags {
               EmptyCLSTreatAsPermitted = true,
               IgnoreRuntimeCLSResponse = false,
               IgnoreSqlPdpServiceErrors = false,
               OppositeColumnFilteringBehavior = true,
               RuntimeCLSAsMasked = true,
               PoliciesJoinOperation = "OR",
               ExpandStarColumn = true,
               ColumnsResourceType = "CustomColumns"
           });

PlainLibrary.SetOptions(builder.Build());  

Environment Variables

Request Mapping

Same source/target/config structure as above (headers, url, qs → entityAttributes, environment, contextData). The configuration should be passed as a JSON string to the RequestMappingConfigs property or environment variable: PLAINID_RUNTIME_REQUEST_MAPPING.

Setting up the Library Using Dependency Injection

DB Interceptor

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<MyDbContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"))
               .AddInterceptors(new PlainIDbCommandInterceptor()));
}

Authorization Middleware

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllersWithViews(options => { options.Filters.Add<PlainAuthFilter>(); });
}

Manually Injecting User Context

PlainContext.SetUser("SomeUSERID");

EntityAttributes entityAttributes = new EntityAttributes();
entityAttributes.AddAttribute("ATTRIBUTE_NAME", "ATTRIBUTE_VALUE");
PlainContext.SetEntityAttributes(entityAttributes);

Limitations

The library's hook features will not function correctly on arm64 architecture, such as M1 Macs.

Legacy Access Directly to the API

The library can be used to directly access the API without using Entity Framework Core, supporting legacy code or direct use of the functionality.

using Microsoft.Extensions.Logging;
using NLog.Config;
using NLog.Extensions.Logging;
using NLog.Targets;
using PlainID.SQL.PDP.Auth.Lib;
using LogLevel = NLog.LogLevel;

public class MainClass {
    static ILogger logger;

    static void createLogger() {
        var config = new LoggingConfiguration();
        var consoleTarget = new ColoredConsoleTarget() {
            Layout = @"${date:format=HH\:mm\:ss} ${logger} ${message}"
        };
        config.AddTarget("console", consoleTarget);
        config.AddRule(LogLevel.Trace, LogLevel.Off, consoleTarget);

        var loggerFactory = new LoggerFactory();
        loggerFactory.AddNLog();
        loggerFactory.ConfigureNLog(config);

        logger = loggerFactory.CreateLogger<MainClass>();
    }

    public static void Main() {
        createLogger();
        logger.LogInformation("Starting Direct-Mode Test");

        var optionsBuilder = new OptionsBuilder();
        optionsBuilder.LoadFromEnvironmentVariables();
        optionsBuilder
            .WithClientId("PBMZCUX4GCQ6BQH1CFQ7")
            .WithClientSecret("B0JCgbzMbNIls3NjKy4P12CTv0jvL2JAzbF5T8dg")
            .WithEntityTypeId("Main")
            .WithModifierServiceURL("https://authz-sql-pdp-modifier.local.platform.test/resql")  
            .WithFlags(new QueryModificationFlags {
                EmptyCLSTreatAsPermitted = true,
                IgnoreRuntimeCLSResponse = false,
                IgnoreSqlPdpServiceErrors = true,
                OppositeColumnFilteringBehavior = true,
                RuntimeCLSAsMasked = true
            });

        var options = optionsBuilder.Build();

        var entityAttributes = new MapArrayAttributes();
        entityAttributes.AddAttribute("DemoGroup", new object[] { "Jordan" });

        PlainContext.SetEntityAttributes(entityAttributes);
        PlainContext.SetUser("Jordan");
        PlainLibrary.SetLogger(logger);
        PlainLibrary.SetOptions(options);

        AuthServiceApi authServiceApi = new AuthServiceApi();
        var modifiedSql = authServiceApi.CallSqlModifer("SELECT first_name, last_name, department_id FROM employees");
        logger.LogInformation("Modified SQL: {modifiedSql}", modifiedSql);

        logger.LogInformation("Ending Direct-Mode Test");
    }
}

Python SDK

The plainid_sql_authorizer_client dynamically rewrites SQL queries in accordance with Authorization Policies, enforcing both row-level and column-level security at execution time.

Note: This component supports a single identity per request.

Authentication

• Client credentials provide client_id and client_secret at construction time.
• JWT token provides an auth_token per request. The Bearer prefix is added automatically if missing.

To use the SQL Authorizer:

1. Initialize the client:

from core_plainid.clients.plainid_sql_authorizer_client import PlainIDSQLAuthorizerClient
from core_plainid.models.request.sql_authorizer_request import (
    SQLAuthorizerRequest,
    SQLAuthorizerFlags,
    PoliciesJoinOperation,
)

sql_authorizer = PlainIDSQLAuthorizerClient(
    base_url="https://your-sql-authz.plainid.cloud",
    client_id="your_client_id",
    client_secret="your_client_secret",
)

2. Create the request:

request = SQLAuthorizerRequest(
    sql="SELECT * FROM accounts WHERE country = 'US'",
    entity_id="your_entity_id",
    entity_type_id="your_entity_type",
    flags=SQLAuthorizerFlags(
        empty_rls_treat_as_denied=True,
        empty_cls_treat_as_permitted=True,
        expand_star_column=True,
        policies_join_operation=PoliciesJoinOperation.OR,
    ),
)

3. Execute authorization:

response = sql_authorizer.authorize_sql(request)
print(response.sql)
print(response.was_modified)

4. Optionally, use JWT authentication per request:

sql_authorizer = PlainIDSQLAuthorizerClient(
    base_url="https://your-sql-authz.plainid.cloud",
    client_id="your_client_id",
)

response = sql_authorizer.authorize_sql(request, auth_token="your_jwt_token")