Deployment Patterns

The SQL Authorizer can be deployed via Helm chart or as a Docker container, providing flexibility for both Kubernetes-based and standalone environments. Each method supports the same core functionality and allows you to configure connectivity to SQL databases or BigQuery.

This guide provides instructions for deploying the SQL Authorizer using Helm and Docker, including configuration details, examples for different deployment modes, and support for both SQL-based databases and Google BigQuery (GBQ). Choose the deployment approach that best aligns with your infrastructure and scalability needs:

• Helm Chart
• Docker

The SQL Authorizer supports integration with Secret Manager stores. This enables secure, dynamic retrieval of sensitive credentials (like, database passwords and Redis secrets) using a templated syntax. For more information, refer to Secret Management Configuration

Choose the deployment approach that best aligns with your infrastructure and scalability needs:

Helm Chart Deployment

The Helm chart allows for streamlined deployment of the SQL Authorizer into your Kubernetes environment. It supports configuration for PostgreSQL, SQL Server, and GBQ, with optional caching and database connectivity for enhanced functionality such as SELECT * resolution and masking based on column types.

To download the Helm Chart

1. Open the PlainID Platform.
2. Open your Tenant or Environment Settings.
   • Tenant Settings: Click on the Settings icon in the Tenant Navigation bar on the left.
   • Environment Settings: In your Environment Workspace, click on Environment Settings in the left sidebar.
3. Click Authorizers.
4. Click the Download dropdown and select Helm.
5. Open the Helm Chart from your Downloads folder.

values.yaml Configuration

Below is a breakdown of key configuration fields in the values.yaml file.

Response Mapping

response:
  translation:
    attributes:
      projectId: "projectId"   # BigQuery only – attribute name for the project ID
      dataset: "dataset"       # BigQuery only – attribute name for the dataset
      column: "column"         # All DBs – attribute name for the column
      table: "table"           # All DBs – attribute name for the table
    columns:
      resource:
        type: "columns"        # Resource type for column-level filtering (default: "columns")

Star Resolution and Database Configuration

star:
  resolution:
    enabled: false             # Enables/disables resolution of SELECT * into actual column names
    host: "YOUR_SQL_SERVER_ADDRESS"         # Required for PostgreSQL/SQL Server
    user: "YOUR_SQL_SERVER_USER"
    password: "YOUR_SQL_SERVER_PASSWORD"
    port: "YOUR_SQL_SERVER_PORT"
    default: "YOUR_SQL_SERVER_DEFAULT_DATABASE"
    tls:
      enabled: false           # Enables/disables TLS for DB connections
    credentialFiles:
      - "YOUR_BIGQUERY_CREDENTIAL_FILE_0"   # Required for GBQ
    defaultProjectId: "YOUR_BIGQUERY_DEFAULT_PROJECT_ID"
    defaultDataset: "YOUR_BIGQUERY_DEFAULT_DATASET"

Redis Caching

To improve performance and reduce latency, the PAA uses Redis-based caching for faster policy evaluation and reduced load on connected data sources.

Caching is particularly effective in high-throughput environments or when database access adds latency. By reusing previously computed results, the PAA avoids redundant processing for repeated requests.

Redis Deployment Options

Each cache type uses specific Redis key prefixes to separate cached data. You can configure Redis in one of the following ways:

1. Managed Redis (Recommended for Production)
   Use an external Redis service (e.g., AWS ElastiCache, Azure Redis).
   Update the Redis section in values-custom.yaml and point the authz configuration to the managed Redis endpoint.

2. PAA Redis (Bundled with Helm Chart)
   Use the Redis instance installed with the chart.
   Ensure the authz service is configured to connect to the bundled Redis.

3. Sidecar Redis (For Local or Custom Use)
   Run Redis as a sidecar container in the same pod.
   Add Redis under extraContainers in values.yaml, and point authz to localhost.

Sample Configuration

Below is a sample configuration for connecting to an external Redis instance using the externalRedis section in your values-custom.yaml file. For full parameter details, see the Sample values.yaml file.

externalRedis:
  host: ""                # Redis server hostname or IP
  port: 6379              # Redis port (default: 6379)
  username: ""            # Optional: Redis ACL user
  password: ""            # Redis password (ignored if using existingSecret)
  existingSecret: ""      # Optional: Name of secret containing Redis credentials
  existingSecretKeyName: "redis-password"  # Key inside the secret with password
  tls: false              # Enable if using TLS to connect to Redis

If using an existing Kubernetes secret for Redis credentials, externalRedis.password is ignored.

Deployment Examples

With DB Connectivity and Caching (Recommended for Production)

This example enables star resolution, Redis caching, and BigQuery access:

config:
  database:
    driver: "bigquery"
    star:
      resolution:
        enabled: true
        credentialFiles:
          - /app/plainid-presales-142815.json
        defaultProjectId: 'plainid-142815'
        defaultDataset: 'bq2'

  redis:
    enabled: true
    host: "127.0.0.1"
    port: "6379"
    password: ""
    db: "0"

  caching:
    runtime:
      type: "redis"
      prefix: "sql-authz:v1:pdp"
      ttl: "5m"
    tableSchema:
      type: "redis"
      prefix: "sql-authz:v1:tableSchema"
      ttl: "3h"

Without DB Connectivity and Caching (Basic Setup)

This minimal setup disables star resolution and caching but allows BigQuery access:

config:
  database:
    driver: "bigquery"
    star:
      resolution:
        enabled: false
        credentialFiles:
          - /app/plainid-presales-142815.json
        defaultProjectId: 'plainid-142815'
        defaultDataset: 'bq2'
        tls:
          enabled: false
  redis:
    enabled: false

BigQuery Credential Example

To access BigQuery securely, the SQL Authorizer requires a service account credential file provided by Google Cloud IAM. This file contains the necessary authentication details that allow the Authorizer to act on behalf of a service account when querying BigQuery.

The file must be in standard Google Cloud service account JSON format, and it is referenced in the configuration (e.g., in values.yaml, environment variables, or Docker volume mounts) using the credentialFiles setting.

The Authorizer uses these credentials to authenticate API requests to BigQuery and perform operations such as column inspection, star expansion (SELECT *), and type resolution—only if DB connectivity is enabled.

Valid BigQuery credential file example:

{
  "type": "service_account",
  "project_id": "your-project-id",
  "private_key_id": "your-private-key-id",
  "private_key": "-----BEGIN PRIVATE KEY-----\nYOUR-DUMMY-PRIVATE-KEY\n-----END PRIVATE KEY-----\n",
  "client_email": "service-account@your-project-id.iam.gserviceaccount.com",
  "client_id": "123456789012345678901",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/service-account%40your-project-id.iam.gserviceaccount.com"
}

⚠️ Important: Ensure this credential file is securely stored and correctly referenced in your deployment configuration. Avoid committing it to version control systems.

Helm Commands

To install or upgrade the Authorizer using Helm:

# Add the Helm repo (if applicable)
helm repo add plainid https://your-helm-repo-url/

# Install the Authorizer
helm install sql-authorizer plainid/sql-authorizer -f values.yaml

# Or upgrade an existing release
helm upgrade sql-authorizer plainid/sql-authorizer -f values.yaml

Docker Deployment

The SQL Authorizer can be deployed as a lightweight, standalone Docker container. This method is ideal for environments that do not use Kubernetes or for local testing and development. Configuration is managed through environment variables, allowing easy integration with various SQL databases and Google BigQuery. The container connects to the PlainID Runtime service to enforce fine-grained access control on SQL queries.

Make sure to obtain the necessary credentials from the PlainID team. These are required to pull the Docker image and configure access to your Policy Runtime service and data sources.

Running the Authorizer Service

1. Launch the service within a Docker container.
2. Define the PDP (Policy Decision Point) Runtime endpoint. This determines which Runtime service the Authorizer connects to for enforcement.

Environment Variables for the Docker Container

Example Docker Command

docker run -d -t -i \
  -e RUNTIME_URL=http://policy-runtime.plainid-pdp/api/runtime/resolution/v3 \
  -e HTTP_PORT=8080 \
  -e DB_USER=sa \
  -e DB_PWD=XXXXX \
  -e DB_HOST=YOUR_MSSQL_SERVER \
  -e DB_PORT=1433 \
  -e DB_DEFAULT=testdb \
  -e DB_ENABLED=true \
  -e DB_DRIVER=sqlserver \
  -e LOGGING_ROOT_LEVEL=trace \
  -p 8080:8080 \
  --name consolidated-sql-authorizer \
  registry.plainid.io/sql-authorizer:latest

Secret Manager Service Configuration

To enable secret resolution using {{store=...}} syntax, you must configure the Secret Management service. This service fetches and refreshes secrets at runtime from your configured secret providers (e.g., Azure Key Vault, AWS Secrets Manager). Refer to Secret Manager Service Configuration to learn how to configure the service.

Store Syntax

To securely provide sensitive values (such as database or Redis credentials), use the following syntax in your values.yaml configuration after you've also added the configuration parameters above:

{{store=STORE_NAME, key=SECRET_KEY, refreshInterval=DURATION}}

Secret Manager Configuration and Store Example with GBQ

...
config:
  database:
    # database server driver. possible values: sqlserver / postgres
    driver: "bigquery"
    star:
      resolution:
        # Enable/disable star resolution
        enabled: true
        gbqCredentials:
          - json: "{{store=AZURE_KEY_VAULT_STORE, key=557eafcd26f8, refreshInterval=15}}"
          - type: "service_account"
            project_id: "plainid-142815"
            private_key_id: "{{store=AZURE_KEY_VAULT_STORE, key=private-key-id, refreshInterval=15}}"
            private_key: "{{store=AZURE_KEY_VAULT_STORE, key=private-key, refreshInterval=15}}"
            client_email: "{{store=AZURE_KEY_VAULT_STORE, key=client-email, refreshInterval=15}}"
            client_id: "{{store=AZURE_KEY_VAULT_STORE, key=client-id, refreshInterval=15}}"
            auth_uri: "https://accounts.google.com/o/oauth2/auth"
            token_uri: "https://oauth2.googleapis.com/token"
            auth_provider_x509_cert_url: "https://www.googleapis.com/oauth2/v1/certs"
...      
  redis:
    host: "master.redis-pw-rotation.0wgi7l.use2.cache.amazonaws.com"
    port: "6379"
    password: "{{store=AWS_SECRETS_MANAGER_STORE, key=redis-rotated-pw, jsonpath=$.password, refreshInterval=15}}"
    db: "0"
    ssl: true  

  secretManager:
    enabled: true
    url: http://my-plainid-paa-secrets-mgmt:8080
    timeout: 30s
    cache:
      ttl: 1h
...

Secret Manager Configuration and Store Example with SQL

config:
  database:
    # database server driver. possible values: sqlserver / postgres
    driver: "sqlserver"
    star:
      resolution:
        # Enable/disable star resolution
        enabled: true
        # SQL DB address
        host: "host.docker.internal"
        # SQL DB Username
        user: "{{store=AZURE_KEY_VAULT_STORE, key=dbuser, refreshInterval=15}}"
        # SQL DB password
        password: "{{store=AZURE_KEY_VAULT_STORE, key=dbpass, refreshInterval=15}}"
        # SQL DB port
        port: "1433"
        # Default database to connect to
        default: "testdb"
        tls:
          enabled: false

  redis:
    host: "master.redis-pw-rotation.0wgi7l.use2.cache.amazonaws.com"
    port: "6379"
    password: "{{store=AWS_SECRETS_MANAGER_STORE, key=redis-rotated-pw, jsonpath=$.password, refreshInterval=15}}"
    db: "0"
    ssl: true

  secretManager:
    enabled: true
    url: http://my-plainid-paa-secrets-mgmt:8080
    timeout: 30s
    cache:
      ttl: 1h
      

Secret Manager Configuration via Docker

 docker run -d -t -i \
-e RUNTIME_URL=http://policy-runtime.plainid-pdp/api/runtime/resolution/v3 \
-e HTTP_PORT=8080 \
-e DB_USER="{{store=AZURE_KEY_VAULT_STORE, key=db-username}}" \
-e DB_PWD="{{store=AZURE_KEY_VAULT_STORE, key=db-password}}" \
-e REDIS_PASS="{{store=AWS_SECRETS_MANAGER_STORE, key=redis-rotated, jsonpath=$.password, refreshInterval=3600}}" \
-e DB_BIGQUERY_CREDENTIALS='[{"json":"{{store=AZURE_KEY_VAULT_STORE, key=plainid-presales-557eafcd26f8}}"}]' \
-p 8080:8080 \
--name authz-sql-pdp-modifier plainid/authz-sql-pdp-modifier:latest

Standalone Deployment

The PlainID SQL Authorizer can be deployed as a standalone service to provide authorization enforcement for SQL queries in non-Kubernetes environments. In this deployment model, the service runs independently as a local process or lightweight daemon and acts as a proxy between client applications and the target database.

The Authorizer intercepts SQL queries sent from PlainID SQL libraries or integrated applications, evaluates them against authorization decisions returned by the PlainID PDP Runtime, and dynamically rewrites the queries according to configured authorization policies.

Standalone deployment is suitable for local development environments, VM-based deployments, lightweight installations, and simple proof-of-concept setups. The service is configured through a local YAML configuration file and exposes health endpoints for operational monitoring and readiness verification.
To download the Standalone file:

1. Open the PlainID Platform.
2. Open your Tenant or Environment Settings.
   • Tenant Settings: Click on the Settings icon in the Tenant Navigation bar on the left.
   • Environment Settings: In your Environment Workspace, click on Environment Settings in the left sidebar.
3. Click Policy Authorization Agents.
4. Click the Download dropdown and select SQL Standalone.
5. Open the file from your Downloads folder.

Prerequisites

Before deploying the SQL PDP Modifier, ensure the following requirements are met:

• Access to a PlainID PDP Runtime endpoint
• Network connectivity between the modifier service and the PDP Runtime
• Access to the target SQL database or data source
• A supported Linux or compatible runtime environment

Installation

1. Create a local configuration file:

cp sample-config.yaml config.yaml

2. Update config.yaml with your environment-specific values.

3. Start the service in the background:

./start.sh

To run it in the foreground instead:

./authz-sql-pdp-modifier -c "./config.yaml"

4. Stop the background process when needed:

./stop.sh

Configuration

The standalone deployment is configured using the config.yaml file. This file defines runtime connectivity, database integration, service ports, logging behavior, and optional TLS settings.

Before starting the service, ensure all required environment-specific values are properly configured.

Running and Verifying

Start the service in the background:

./start.sh

Check the log file:

tail -f ./authz-sql-pdp-modifier.log

The service listens on http.port for API traffic and on management.port for health endpoints. With the default sample configuration, verify health on port 8081:

curl http://127.0.0.1:8081/health/liveness
curl http://127.0.0.1:8081/health/readiness

A successful response indicates that the service is operational and ready to process authorization requests.

Operational Notes

The modifier service must remain connected to the configured PlainID PDP Runtime endpoint to evaluate authorization policies. Health endpoints can be integrated with external monitoring systems, and configuration changes require a service restart to take effect.

Stopping the Service

Stop the running service:

./stop.sh