Give an AI Agent Read-Only Access to Postgres

Define the boundary in Postgres first

The safest database tool for an agent starts below the MCP server. PostgreSQL has object privileges such as SELECT, INSERT, UPDATE, DELETE, CREATE, CONNECT and USAGE; use those native controls before trusting a tool description or model prompt. The agent role should not own application tables, should not be a superuser and should not inherit the application runtime role. Ownership matters because the owner can alter or drop objects even when ordinary privileges look narrow.

Decide whether the agent needs base tables at all. For many teams the better boundary is a reporting schema with stable views: columns are renamed for humans, sensitive fields are omitted, and joins that encode business rules are written once. A support agent asking for subscription status does not need raw payment tokens, deleted rows or every internal audit column. A finance analyst may need aggregate revenue views but not row-level personal data. This is where source-scoped credentials and database-level least privilege meet.

Create a narrow reader role

Make the database role boring. It should have login, connection to one database, schema usage for the schemas it can inspect, and SELECT on the relations it may query. Run grants as the object owner or a role that can grant those privileges, and repeat the same policy for future tables with default privileges where your migration owner creates them.

CREATE ROLE agent_reader LOGIN PASSWORD 'replace-with-secret';

REVOKE ALL ON DATABASE appdb FROM PUBLIC;
GRANT CONNECT ON DATABASE appdb TO agent_reader;

GRANT USAGE ON SCHEMA reporting TO agent_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO agent_reader;

ALTER DEFAULT PRIVILEGES IN SCHEMA reporting
  GRANT SELECT ON TABLES TO agent_reader;

That example is intentionally small. Do not grant CREATE on schemas, do not grant ALL PRIVILEGES, and do not reuse a role that can run migrations. If the connector must see multiple schemas, name each schema explicitly and document why. If the agent only needs a subset of a wide table, create a view and grant SELECT on the view instead of the table. Review read-only versus write tool boundaries before adding any mutating path.

Expose schema and query as separate tools

An agent needs schema context before it writes a useful query, but schema inspection and query execution are different capabilities. Publish a schema tool that returns tables, columns, primary keys, descriptions and a few safe examples. Publish a query tool that accepts one SELECT statement plus optional parameters. Do not expose a general SQL console with whatever the model sends.

The MCP tool flow is simple: the client asks for available tools with JSON-RPC tools/list, then invokes one with tools/call. Tool annotations such as readOnlyHint: true can help clients reason about risk, but they are hints, not enforcement. The enforcement lives in the Postgres role, the parser or allowlist in the connector, and the runtime policy around calls.

{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "tools/list",
  "params": {}
}

{
  "jsonrpc": "2.0",
  "id": 2,
  "method": "tools/call",
  "params": {
    "name": "postgres_query",
    "arguments": {
      "sql": "select account_id, plan, status from reporting.accounts where status = $1 limit 50",
      "params": ["past_due"]
    }
  }
}

Keep the tool contract plain: SELECT only, one statement only, parameterized values, no DDL, no DML and no transaction control supplied by the model.

Clamp every query execution

Read-only access still needs runtime limits. A SELECT can scan a billion rows, call expensive functions, reveal regulated data or join tables in a way the user did not intend. Put the connector in charge of the transaction envelope and make the model supply only the query shape and values.

BEGIN READ ONLY;
SET LOCAL statement_timeout = '5s';
SET LOCAL search_path = reporting;

select account_id, plan, status
from accounts
where status = $1
limit 100;

COMMIT;

Log enough to debug without turning logs into a data leak. Store actor, source, tool name, decision, latency, row count and normalized query shape. Be careful with literal values because a user may ask for an email address, invoice number or other sensitive identifier. Pair this with agent tool-call audit logging so later reviews can reconstruct what the agent actually requested.

Wire the endpoint into clients

Once the Postgres reader is behind a remote MCP endpoint, client configuration should contain the endpoint and an agent credential, not a database password. For Claude Code, an HTTP MCP server can be added from the command line, including an authorization header when the endpoint uses a bearer-style agent key.

claude mcp add --transport http spyn3 https://api.spyn3.com/agent/mcp \
  --header "Authorization: Bearer $SPYN3_AGENT_KEY"

For Cursor, project configuration belongs in .cursor/mcp.json when the team wants the same server available in that repo. Keep the secret in the environment and interpolate it into the header instead of committing it.

{
  "mcpServers": {
    "spyn3": {
      "url": "https://api.spyn3.com/agent/mcp",
      "headers": {
        "Authorization": "Bearer ${env:SPYN3_AGENT_KEY}"
      }
    }
  }
}

For VS Code, the workspace file .vscode/mcp.json uses a top-level servers object for MCP servers. A minimal remote entry keeps the transport and URL explicit; use the server's supported authentication flow or profile-level secret handling rather than embedding database credentials in the workspace file.

{
  "servers": {
    "spyn3": {
      "type": "http",
      "url": "https://api.spyn3.com/agent/mcp"
    }
  }
}

How SPYN3 handles this

SPYN3's Postgres connector does not proxy a database URL to the agent. It generates three read-only tools: list_tables, describe_table and query, where query takes an sql argument and runs inside a read-only transaction. The connection string stays encrypted in the workspace, and each call is timed and logged like any other tool call. A role with only SELECT grants is still the right thing to connect with: the read-only transaction is a second wall, not the first.