Secure PostgreSQL Access for AI Coding Agents

A conservative role for diagnostic reads

create role agent_diagnostics login password 'generated-secret';

grant connect on database app_db to agent_diagnostics;
grant usage on schema public to agent_diagnostics;
grant select on all tables in schema public to agent_diagnostics;

alter role agent_diagnostics set default_transaction_read_only = on;
alter role agent_diagnostics set statement_timeout = '15s';
alter role agent_diagnostics set lock_timeout = '2s';
alter role agent_diagnostics set idle_in_transaction_session_timeout = '30s';
alter role agent_diagnostics set application_name = 'ai-agent-diagnostics';

A readable application_name makes sessions easier to identify in pg_stat_activity and logs. Use a distinct value and credential per integration.

Prefer a database you can throw away

A local restore, database branch, or sanitized staging copy lets the agent inspect realistic schema and data distribution without reaching live customer records. Reset the environment from a known snapshot after migrations or experiments.

Remove or transform secrets and personal data before copying production. A read-only role still reads whatever its SELECT privileges expose, and model transcripts or tool logs can retain returned values.

Make write access task-specific

Schema changes and data fixes need writes, but they do not need permanent owner access. Create a short-lived credential for one environment, limit its schema privileges, require review for tool calls, and revoke it when the task ends.

Keep backup deletion, credential management, network policy, and production deployment outside the same agent role. Recovery is not a safety net if the actor that made the mistake can also erase it.

Bound time, locks, and result size

statement_timeout stops runaway execution, lock_timeout avoids waiting indefinitely behind production work, and idle_in_transaction_session_timeout closes forgotten transactions. Set limits on the role so they apply even when the client omits them.

The MCP or agent tool should also cap returned rows and truncate oversized values. Database timeouts protect the server; result limits protect the client context and reduce accidental data exposure.

Check the active guardrails

select
  current_user,
  current_setting('transaction_read_only') as read_only,
  current_setting('statement_timeout') as statement_timeout,
  current_setting('lock_timeout') as lock_timeout,
  current_setting('application_name') as application_name;

Audit the database identity, not the prose

Database logs and pg_stat_activity can attribute activity to a login and application_name. They cannot reliably tell whether a human typed the SQL, an agent proposed it, or an MCP server transformed it. Preserve tool-call records alongside database logs when auditability matters.

Rotate credentials and terminate sessions when an integration is removed. Search configuration repositories and secret managers for stale connection strings rather than assuming uninstalling the client revoked access.

Test the failure path

Before granting useful access, ask the integration to perform operations that should be denied: update a row, create a table, read a restricted schema, hold a lock, and run past the timeout. Verify the database blocks each one.

Follow the Postgres MCP server setup for the client-facing workflow. Keep a normal database client available for reviewing generated SQL and inspecting the same schema without routing every question through an agent.