Safe Postgres access for AI agents: role, RLS, limits

A read-only role is the right first step for giving an AI agent database access, and for an agent that only reads it’s most of the job. This post is about the agent that has to write: a support agent that can cancel one customer’s paid order, say. We set it up in three layers, test each one, and then show the gap they share.

Everything here was run on PostgreSQL 18.6 against a small demo store database (500 orders, 120 customers). The SQL is complete; the output is pasted, not retyped.

Three layers

The role decides what the agent may do: which tables, which columns, which verbs.
Row-level security decides which rows: this customer’s orders, not everyone’s.
Limits decide how much and how long: statement time, lock waits, idle transactions, connections.

Each layer is enforced by Postgres, not by the agent’s instructions. That’s the point: an instruction in a prompt is a request, a privilege is a fact.

Layer 1: the role

CREATE ROLE agent_support LOGIN PASSWORD 'blog-agent-throwaway' CONNECTION LIMIT 3;
ALTER ROLE agent_support SET statement_timeout = '2s';
ALTER ROLE agent_support SET lock_timeout = '1s';
ALTER ROLE agent_support SET idle_in_transaction_session_timeout = '3s';
GRANT USAGE ON SCHEMA public TO agent_support;
GRANT SELECT (id, customer_id, status, total_cents) ON public.orders TO agent_support;
GRANT UPDATE (status) ON public.orders TO agent_support;

A new role starts with nothing; until privileges are granted, only an object’s owner (or a superuser) can do anything with it.² So every line above is a decision. The agent can read four columns of orders and change one. Postgres can grant SELECT, INSERT, UPDATE and REFERENCES on single columns, while TRUNCATE is table-level only and isn’t granted here.⁴ No other table is visible.

Both refusals come back as errors the agent can read:

-- a column it can't write
   UPDATE public.orders SET total_cents = 0 WHERE customer_id = 7;
   ERROR:  permission denied for table orders

-- a column it can't read
   SELECT created_at FROM public.orders LIMIT 1;
   ERROR:  permission denied for table orders

Don’t rely on a tool’s read-only mode instead of this. Datadog Security Labs showed that the archived reference Postgres MCP server’s read-only mode ran each query in a read-only transaction that a stacked COMMIT; ends, after which later statements run with the role’s full rights.⁸ With a role like this one, “full rights” are four columns and one update.

Layer 2: row-level security per customer

The support agent works on one customer’s conversation at a time. Row-level security can tie its view to that customer:

ALTER TABLE public.orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY support_one_customer ON public.orders FOR ALL TO agent_support
  USING (customer_id = current_setting('app.customer_id', true)::int)
  WITH CHECK (customer_id = current_setting('app.customer_id', true)::int AND status IN ('paid', 'cancelled'));

Once row security is on, access with no matching policy is denied, and table owners bypass it unless FORCE ROW LEVEL SECURITY is set, so the agent must never connect as the owner.³ The true in current_setting('app.customer_id', true) matters: it returns NULL instead of an error when the setting doesn’t exist.⁶ NULL matches no customer, so a session that hasn’t said who it’s working for sees nothing:

-- no customer set: RLS shows nothing
   SELECT count(*) FROM public.orders;
    count
   -------
        0

Set the customer and the agent sees exactly that customer’s orders. Try to touch another customer’s order and the update simply matches nothing:

-- customer 7 set
   SET app.customer_id = '7';
   SELECT id, status, total_cents FROM public.orders ORDER BY id;
    id  |  status   | total_cents
   -----+-----------+-------------
    100 | shipped   |       11622
    256 | shipped   |        2344
    407 | shipped   |       25123
    469 | shipped   |       32670
    484 | cancelled |        8202
   (5 rows)

-- another customer's order
   SET app.customer_id = '7';
   UPDATE public.orders SET status = 'cancelled' WHERE customer_id = 8;
   UPDATE 0

UPDATE 0 is not an error. Tell the agent, in its tool description, that zero rows is an answer and not a reason to retry another way.

Layer 3: limits

Limits protect the database from an agent that loops, scans or forgets to commit. All four are set on the role in layer 1.

Statement time. statement_timeout aborts any statement that runs longer than its limit.¹

   SELECT pg_sleep(3);
   ERROR:  canceling statement due to statement timeout

Lock waits. lock_timeout aborts a statement that waits too long for a lock, so an agent queues behind a migration for one second, not for ever.¹ With another session holding a lock on orders:

   UPDATE public.orders SET status = 'cancelled' WHERE id = (SELECT min(id) FROM public.orders);
   ERROR:  canceling statement due to lock timeout

Idle transactions. An agent that opens a transaction and then goes off to think (or call a slow model) holds its locks the whole time. idle_in_transaction_session_timeout ends that session.¹

   BEGIN;
   SELECT 1 AS opened;
   SELECT 2 AS still_there;
   FATAL:  terminating connection due to idle-in-transaction timeout

Connections. CONNECTION LIMIT caps concurrent sessions for the role, so a retry loop from this role stops at three sessions instead of taking every connection the server has. Postgres enforces the limit approximately and never for superusers.⁷

   psql: error: connection to server at "127.0.0.1", port 55489 failed: FATAL:  too many connections for role "agent_support"

The gap: the session owns its settings

Here is the part most guides leave out. A setting attached with ALTER ROLE … SET becomes the role’s session default at login.⁵ A default, not a lock. If the agent can send its own SQL, it can change it:

-- the session turns the timeout off
   SET statement_timeout = 0;
   SELECT pg_sleep(3);
    pg_sleep
   ----------

   (1 row)

Worse, the same is true of the setting the row-level security policy trusts:

-- the session picks another customer
   SET app.customer_id = '8';
   SELECT count(*) FROM public.orders;
    count
   -------
        3

Nothing failed. The policy did exactly what it says: show the orders of the customer named in app.customer_id. The session named a different customer.⁵

So: the role (layer 1) holds no matter what SQL the agent sends. Row-level security keyed on a session variable, and every limit in layer 3, hold only if the agent can’t send SET.

Closing the gap

You have three options, in order of preference.

Don’t give the agent raw SQL. Give it tools with fixed queries, cancel_order(order_id) rather than execute_sql(sql). The tool server opens the transaction, runs SET LOCAL app.customer_id = … from the conversation it’s serving (not from the model), runs its one statement, and commits. The agent never gets a chance to change a setting.

If it must write SQL, put something between it and Postgres that refuses session changes. That means parsing each statement and refusing SET, RESET, set_config(…) and anything with more than one statement, then running what’s left inside a transaction with the settings applied by the proxy, not the session. In Deedglass, agent SQL runs as a least-privilege role in a transaction Deedglass controls, one statement per action, and every write commits together with a ledger row, or not at all.⁹ The connector’s limits live in its config, not in the agent’s session:

config:
  mode: sql
  allowed_schemas: [public]
  writable_tables: [public.orders, public.refunds]
  row_limit: 500
  max_rows_affected: 1000
  statement_timeout_ms: 5000
  lock_timeout_ms: 2000

Key row-level security to something the session can’t change. session_user is the role the connection logged in as; SET ROLE changes current_user but leaves session_user alone.¹⁰ If you have a handful of tenants, a login role per tenant with USING (tenant_role = session_user) holds even when the agent sends raw SQL, at the cost of managing those roles and their passwords.

The short version

A narrow role is the boundary. Column grants, no ownership, no superuser. It holds whatever the agent sends.
Row-level security narrows rows. Use current_setting(…, true) so an unset tenant sees nothing.
Set statement, lock, idle and connection limits on the role. They catch loops and forgotten transactions.
If the agent can send SET, the limits and any session-keyed policy are its to change. Use fixed tools, a proxy that refuses session changes, or policies keyed to session_user.

To see what your own agent’s role can change today, run the free scan, npx deedglass scan; the grade is explained here and nothing leaves your machine. To watch rules decide each call and a check report what committed, without installing anything, try the playground. For the vendor-free basics with a tested SQL file, start with the guide to giving Claude Code or Cursor write access to Postgres.

How to give an AI agent safe Postgres access: the role, RLS and statement limits