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

Deploying the SQL Authorizer via Helm Chart

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: Cick on the Settings icon next to your Tenant Name on the top right.
   • Environment Settings: Click on the three dots next to your Environment name.
3. Click Policy Authorization Agents.
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

Deploying the SQL Authorizer via Docker

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 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