PostgreSQL

One server, many databases#

Its server.yaml lists each database under the name calls use, with its own login, or a read and a write login, and the connection string of each, so the databases can sit on different Postgres servers. Every tool except list_databases takes a required database argument, and each call runs on a login of that one database.

Behind Straza, a role covers every database or only the ones it names. The gateway refuses a call on a database that none of the caller's roles covers, before any policy runs, and the audit record names the database. Your IGA reads over SCIM which databases each role covers. When a role on named databases admits a call, Straza sends the database's id with it, and the server refuses the call if that name now leads to a database dropped and created again.

Your SQL as tools#

The catalog turns SQL statements you review into tools. Each statement starts at a -- name: line in a .sql file, and the tools.yaml of its folder gives the tool its description, its mode, its databases, its login, its parameters and its caps. Postgres reports the type of each parameter at start, and the tool's input schema follows it.

-- name: close_stale_tickets
UPDATE public.tickets
SET status = 'closed'
WHERE status = 'pending'
  AND updated_on < current_date - $1::int4
RETURNING id;

version: 1
tools:
  close_stale_tickets:
    description: Closes the tickets that have waited in pending for more than a number of days. Returns the id of each ticket it closed, and refuses a call that would close more than 50.
    mode: write
    databases: [helpdesk]
    login: write
    params:
      - name: days
        description: How many days a ticket waits in pending before it closes, from 7 to 90.
        required: true
        minimum: 7
        maximum: 90
    limits: { write_rows: 50 }

At start the server prepares every statement on each of its databases and checks the grants of its login, and it does not serve while one fails. adaptive-pg-mcp check runs the same checks without serving, so CI can run them before a deploy. A read tool runs in a read-only transaction, and a write tool is rolled back when it changes more rows than its cap or changes structure. Add each catalog tool by name to the role that matches its mode, and under straza.objects.tools in the manifest with {argument: database}, as the built-in tools are.

Roles and tools#

Three suggested application roles, split by risk; each reaches only this server. Risk comes from the MCP annotations the server declares on each tool and what each tool does, read in its code.

Register with strazad#

Installing also creates the server’s administration role, mcp-admin-postgres, which your IGA can assign to the people who maintain it.

1. Install the manifest below.
   shellCopy

   strazactl apps install -f postgres.yaml

2. Store the server’s credential. strazactl asks for it at a hidden prompt.
   shellCopy

   strazactl apps secret set postgres

3. Store the listing credential. Straza sends it to list_databases alone, lists the databases with it, and lists them again every 10 minutes.
   shellCopy

   strazactl apps secret set postgres --listing
   strazactl apps objects postgres

4. Create the suggested roles, each on every database. A role on this server says which databases it covers, so the roles go in as a role file. Save this as postgres-roles.yaml:
   yamlCopy

   apiVersion: straza.dev/v1beta1
   kind: Role
   metadata:
     name: postgres-read
   spec:
     kind: application
     server: postgres
     bindings:
       - app: postgres
         tools: [list_databases, list_schemas, list_tables, describe_table, read_rows, aggregate, query_sql]
         covers: every
   ---
   apiVersion: straza.dev/v1beta1
   kind: Role
   metadata:
     name: postgres-write
   spec:
     kind: application
     server: postgres
     bindings:
       - app: postgres
         tools: [insert_rows, update_rows, delete_rows, execute_sql]
         covers: every
   ---
   apiVersion: straza.dev/v1beta1
   kind: Role
   metadata:
     name: postgres-admin
   spec:
     kind: application
     server: postgres
     bindings:
       - app: postgres
         tools: [execute_ddl]
         covers: every

   Then open a draft from it and run the strazactl drafts publish command that the draft prints.
   shellCopy

   strazactl drafts create -f postgres-roles.yaml

5. To keep a role to some databases, name them under covers. list_databases reaches every database, so such a role leaves it out.
   yamlCopy

   apiVersion: straza.dev/v1beta1
   kind: Role
   metadata:
     name: postgres-helpdesk
   spec:
     kind: application
     server: postgres
     bindings:
       - app: postgres
         tools: [list_schemas, list_tables, describe_table, read_rows, aggregate, insert_rows, update_rows, delete_rows]
         covers:
           objects: [helpdesk]

The manifest#

From Straza's eval stack, without its comment lines and the lines for that stack's own catalog tools, and with server.version set to this release; its URL and descriptions are that stack's values.

apiVersion: straza.dev/v1beta1
kind: App
metadata:
  name: postgres
  namespace: ai.straza
  description: adaptive-pg-mcp, a Postgres MCP server for four showcase databases, via streamable HTTP
server:
  name: ai.straza/adaptive-pg-mcp
  description: Typed tools, the helpdesk and sales teams' SQL as named tools, and free SQL on four databases.
  repository:
    url: https://github.com/strazahq/adaptive-pg-mcp
    source: github
  version: "0.1.0"
straza:
  runtime:
    kind: remote
    remote:
      url: http://adaptive-pg-mcp:8080/mcp
  credential:
    kind: static
    inject:
      as: header
      name: Authorization
      template: "Bearer {{secret}}"
  exposure:
    tools: ["*"]
  limits:
    rps: 10
  objects:
    type: database
    list:
      tool: list_databases
    tools:
      list_databases: all
      list_schemas: { argument: database }
      list_tables: { argument: database }
      describe_table: { argument: database }
      read_rows: { argument: database }
      aggregate: { argument: database }
      insert_rows: { argument: database }
      update_rows: { argument: database }
      delete_rows: { argument: database }
      query_sql: { argument: database }
      execute_sql: { argument: database }
      execute_ddl: { argument: database }

Credentials#

What the server reads at start to reach PostgreSQL databases, under the grants of each login:

• dsn_env: for each login of each database in server.yaml, the environment variable that holds its connection string, a libpq URL or key=value pairs. A database has one login, or a read and a write login and any extra login a catalog tool names (required)
• gateway_tokens_sha256: with mode: straza in server.yaml, the SHA-256 of the token Straza calls with; adaptive-pg-mcp token prints a new token and its hash, and the token is the secret that strazactl apps secret set stores
• listing_tokens_sha256: with mode: straza, the SHA-256 of the token Straza lists the databases with, which opens list_databases alone; the token is the listing credential that strazactl apps secret set --listing stores
• tls: optional: the certificate and key files HTTPS serves; without them HTTP listens on a loopback address only, unless allow_plain_http: true says something in front encrypts the traffic
• ADAPTIVE_PG_MCP_TOKEN: stdio only: the caller's token, checked once at start

Server-side gates#

Settings the server reads at start. Each one switches writes on or registers more tools, as listed; a tool the server does not register does not exist for any role.

• free_sql: in server.yaml, required: off serves no free SQL tool, read serves query_sql, and read-write also serves execute_sql and execute_ddl. Free SQL runs with every grant the login holds on its database.

Related posts#

• BlogRole-based access control for AI agents