PostgreSQL Permissions for AI Tools: Least-Privilege Setup

The short answer

Give an AI tool a dedicated PostgreSQL login that owns nothing and can reach only the database, schemas, tables, columns, rows, and operations required for one workflow. Start with CONNECT, schema USAGE, and SELECT on an explicit table list. Add future-object privileges deliberately, use row-level security only when the tenant model is trustworthy, and test both allowed and denied statements while connected as the restricted role.

Do not use the application owner, migration role, database owner, or a developer's personal login. A prompt such as “never modify data” is not an authorization control.

This guide builds a PostgreSQL permission boundary that works independently of Cursor, Claude, ChatGPT, VS Code, an MCP server, or another agent runtime. If a gateway adds a narrower policy, PostgreSQL should still remain the final enforcement layer.

What the permission stack should look like

A production request usually crosses several independent boundaries:

Person or automation
  → AI client identity
  → MCP server or gateway policy
  → dedicated PostgreSQL role
  → schema, table, column, and row privileges

Each layer answers a different question:

The effective permission is the intersection of these controls. A read-only MCP link cannot safely compensate for a PostgreSQL owner credential, and a restricted database role does not tell you which human used a shared MCP link.

Step 1: define one AI workflow

Write down the exact database tasks before creating a role. For example:

The support assistant may read customer names, subscription status, and non-sensitive ticket metadata for one tenant. It may not read password hashes, payment tokens, internal notes, or other tenants. It may not insert, update, delete, execute arbitrary functions, or create objects.

Turn that sentence into a permission matrix:

If the workflow needs both analytics and data changes, create separate roles or at least separate grants and MCP links. A narrow read role is easier to review than one role whose behavior changes according to the prompt.

Step 2: create a non-owner login

Run role administration as an authorized database administrator. Do not put a production password directly in a shared SQL file.

CREATE ROLE ai_reader
  WITH LOGIN
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  CONNECTION LIMIT 5;

Then set the password through your secret-management workflow or interactively in psql:

\password ai_reader

Only roles with LOGIN can start a database connection. The other attributes make the intended boundary explicit, but they do not grant access to application data. PostgreSQL object access is added separately with GRANT.

The role should not own the database, schemas, tables, views, functions, or sequences it uses. Object owners retain powerful implicit rights, and superusers bypass normal permission checks.

Step 3: grant database and schema access

Assume the database is app_production and the approved schema is reporting:

GRANT CONNECT ON DATABASE app_production TO ai_reader;
GRANT USAGE ON SCHEMA reporting TO ai_reader;

CONNECT permits the role to connect to that database. Schema USAGE permits access to objects inside the schema when the role also has privileges on those objects. It does not grant SELECT on every table and does not grant the ability to create objects.

Audit inherited and public access too. A role can receive privileges through membership in another role or through grants to PUBLIC. Removing a direct grant from ai_reader does not help if the same privilege arrives through another path.

Do not blindly revoke shared production privileges as part of an AI rollout. First inspect dependencies, then change broad PUBLIC or group grants through the normal database change process.

Step 4: grant SELECT on an explicit table list

The smallest useful read-only role names the approved relations:

GRANT SELECT ON TABLE
  reporting.customer_summary,
  reporting.ticket_summary,
  reporting.subscription_status
TO ai_reader;

This is safer than granting every current and future table in a schema. A later migration may add reporting.raw_exports or another relation that was never reviewed for AI access.

If the whole schema is intentionally a curated analytics boundary, you can grant all existing tables:

GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO ai_reader;

That statement affects existing tables and views. It does not automatically cover objects created later.

Step 5: decide how future tables receive privileges

Use ALTER DEFAULT PRIVILEGES only after identifying the role that creates the future objects:

ALTER DEFAULT PRIVILEGES
  FOR ROLE app_owner
  IN SCHEMA reporting
  GRANT SELECT ON TABLES TO ai_reader;

This applies to future tables created by app_owner in reporting. It does not change existing tables, and it does not apply to tables created by some other migration or owner role.

That owner-specific behavior is the most common source of broken permission setups. If deploy_role and analytics_owner both create objects, review and configure defaults for each relevant owner. Inspect the result in psql:

\ddp

Choose one of two models deliberately:

1. Explicit review: grant each new relation after a migration. This is slower but prevents accidental exposure.
2. Curated schema: automatically grant future relations in a schema whose contents are already governed for AI access.

Do not enable automatic future access merely to avoid permission errors.

Step 6: protect sensitive columns

Table-level SELECT exposes every column in that relation. For sensitive data, a dedicated view is usually easier to understand and maintain:

CREATE VIEW reporting.customers_safe AS
SELECT
  id,
  organization_id,
  display_name,
  subscription_status,
  created_at
FROM app.customers;

GRANT SELECT ON reporting.customers_safe TO ai_reader;

Keep the base table ungranted. The view becomes the reviewed contract for the AI workflow.

PostgreSQL also supports column-level privileges:

GRANT SELECT (
  id,
  organization_id,
  display_name,
  subscription_status,
  created_at
) ON app.customers TO ai_reader;

Column grants can be precise, but they are easier to overlook during schema changes and query generation. Confirm that no table-level SELECT, inherited role, or PUBLIC grant restores access to all columns.

Step 7: add row-level security when rows need different visibility

Row-level security (RLS) can restrict which rows a role sees. This example gives one fixed role access to one tenant:

ALTER TABLE app.tickets ENABLE ROW LEVEL SECURITY;

CREATE POLICY ai_reader_tenant_42
  ON app.tickets
  FOR SELECT
  TO ai_reader
  USING (organization_id = 42);

The role still needs table SELECT; the policy narrows the matching rows.

RLS has important ownership rules. Superusers and roles with BYPASSRLS bypass row security. Table owners normally bypass it too unless the owner enables FORCE ROW LEVEL SECURITY. That is another reason the AI login must not own the table.

For multi-tenant applications, do not let an untrusted client choose a session variable and treat that value as verified tenant identity. Bind tenant context in a trusted application layer, use a carefully reviewed policy design, and test it with the same effective role and connection path used in production.

Step 8: add operational guardrails

Timeouts reduce runaway-query impact. They are useful safeguards, not authorization controls:

ALTER ROLE ai_reader IN DATABASE app_production
  SET statement_timeout = '15s';

ALTER ROLE ai_reader IN DATABASE app_production
  SET idle_in_transaction_session_timeout = '30s';

ALTER ROLE ai_reader IN DATABASE app_production
  SET default_transaction_read_only = 'on';

statement_timeout cancels statements that run too long. idle_in_transaction_session_timeout limits abandoned open transactions. default_transaction_read_only is a useful default, but it is not a substitute for removing INSERT, UPDATE, DELETE, and DDL privileges: a caller may be able to change a session default, while PostgreSQL grants remain enforceable.

Also consider connection-pool limits, query-result limits, lock timeouts, read replicas, and workload monitoring according to your production architecture.

Step 9: create a separate narrow write role when needed

Do not turn the general reader into a broad writer. Define the exact table and operations:

CREATE ROLE ai_draft_writer
  WITH LOGIN
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  CONNECTION LIMIT 3;

GRANT CONNECT ON DATABASE app_production TO ai_draft_writer;
GRANT USAGE ON SCHEMA support TO ai_draft_writer;
GRANT SELECT, INSERT, UPDATE ON support.draft_replies TO ai_draft_writer;

This example intentionally omits DELETE, TRUNCATE, schema CREATE, and ownership. If inserts use a separately managed sequence, grant only the required sequence privilege after verifying the table design:

GRANT USAGE ON SEQUENCE support.draft_replies_id_seq TO ai_draft_writer;

If the workflow is narrower than generic SQL, a purpose-built function or API operation may be easier to authorize than granting direct writes. Review SECURITY DEFINER functions carefully because they can execute with the function owner's privileges.

Step 10: inspect the resulting privileges

Inspect object privileges and default privileges in psql:

\du ai_reader
\dn+ reporting
\dp reporting.*
\ddp

You can also inspect explicit table grants:

SELECT
  table_schema,
  table_name,
  privilege_type,
  is_grantable
FROM information_schema.role_table_grants
WHERE grantee = 'ai_reader'
ORDER BY table_schema, table_name, privilege_type;

Review role memberships separately. Effective access can be broader than rows explicitly granted to ai_reader.

The most reliable test is to connect directly as the restricted login through the same host, database, and TLS path the AI integration will use.

Step 11: run allowed and denied tests

Do not stop when one SELECT succeeds. Build a small regression matrix and keep the expected result beside each query:

Run destructive-looking tests only against a disposable fixture or inside an approved test environment. The point is to prove denial, not to experiment against production data.

Repeat the matrix after migrations, ownership changes, role membership changes, new default privileges, or an RLS policy update.

Download the reusable SQL kit

The Postgres AI Access Security Kit packages the non-owner role, explicit grants, owner-correct future-object privileges, timeouts, RLS review points, inspection queries, denial tests, and emergency revocation examples into one annotated SQL file.

Download the SQL template directly if you already understand the permission model. Review every identifier and statement first; the file keeps destructive-looking validation and emergency commands commented out.

Step 12: plan revocation and rotation

To remove table access:

REVOKE SELECT ON reporting.ticket_summary FROM ai_reader;

To stop new connections while investigating:

ALTER ROLE ai_reader NOLOGIN;

Disabling the database role can affect every service sharing that credential. This is why each environment and workflow should have a dedicated role, while each AI client or user should have a separate identity at the MCP or gateway layer.

Document who can revoke the client credential, disable the gateway link, set NOLOGIN, terminate active database sessions, and rotate the password. Test the sequence before an incident.

Adding an MCP permission layer

PostgreSQL grants define the outer boundary. An MCP gateway can narrow access further and add client-level authentication, attribution, and revocation.

For example, datamcp supports PostgreSQL MCP links with Read Only, Read, Write & Delete, Full Access, and Custom table-level operation policies. Those policies remain bounded by the effective PostgreSQL role. A link that permits UPDATE cannot make PostgreSQL accept an update the connection role is not allowed to perform.

Create separate links for separate clients or environments, review successful and denied query activity, and delete a link independently when one client should lose access. The PostgreSQL MCP server page describes the hosted connection model, while the MCP authorization guide separates authentication, scopes, link permissions, and backend grants.

See the hosted PostgreSQL MCP workflow if you want to add a separately authenticated gateway layer. Begin with the restricted role from this guide and verify an allowed read plus a denied write before expanding access.

Production checklist

• The AI role is dedicated and has LOGIN only because it needs a direct connection
• The role is not a superuser, database owner, schema owner, or table owner
• Role memberships and PUBLIC grants have been reviewed
• CONNECT, schema USAGE, and table privileges match a written workflow
• Sensitive columns are excluded through reviewed views or column grants
• RLS policies use trustworthy tenant identity and are tested as the effective role
• Default privileges name every relevant object-creating role
• Timeouts and connection limits reduce operational impact
• Allowed and denied statements are part of a repeatable test matrix
• Development, staging, and production use separate credentials
• Client-level access can be revoked without rotating every database user
• Database-role disablement and password rotation have named owners

For the wider threat model—including prompt injection, token handling, SSRF, local-server risk, logging, and incident response—use the MCP security checklist for production databases.

PostgreSQL permissions for AI tools FAQ

Should an AI tool use a read-only PostgreSQL user?

Start with a dedicated read-only role for schema inspection, analytics, support lookup, and code-assistance workflows. Add write access only when one documented task requires it, and limit that access to specific tables and operations.

Is default_transaction_read_only enough?

No. It is a session default and an operational safeguard, not the primary authorization boundary. Remove write and DDL privileges with PostgreSQL roles and grants.

Should I grant SELECT on all tables in a schema?

Only when the schema is intentionally curated as one access boundary. Explicit table grants are safer when new relations may contain secrets, authentication data, billing data, or unreviewed exports.

Why did ALTER DEFAULT PRIVILEGES not work for a new table?

Default privileges apply according to the role that creates the future object. If a different owner or migration role created the table, configure that role's defaults or grant the table explicitly.

Does row-level security apply to the table owner?

Table owners normally bypass RLS, while superusers and roles with BYPASSRLS always bypass it. Keep the AI role non-owner and test policies with the actual effective role. The owner can use FORCE ROW LEVEL SECURITY when the design requires owner enforcement.

Do MCP permissions replace PostgreSQL grants?

No. MCP permissions can narrow operations and improve client-level control, but the PostgreSQL role should independently reject anything outside the database policy.

Official PostgreSQL references

• Role attributes
• Privileges and ownership
• GRANT
• ALTER DEFAULT PRIVILEGES
• Row security policies
• REVOKE