statement_timeout, lock_timeout and friends: timeouts for an AI agent's Postgres role

The short answer

Give the AI agent its own Postgres role and set four timeouts on it with ALTER ROLE: statement_timeout (kills long queries), lock_timeout (stops waiting on locks), idle_in_transaction_session_timeout (ends sessions that leave a transaction open) and, on PostgreSQL 17 or later, transaction_timeout (caps a whole transaction).¹¹² Role settings apply at login, so every connection the agent opens starts with them.³

In our run on PostgreSQL 18.6, each timeout did its job. But all four are ordinary user settings, and the agent’s role turned them off with a single SET.⁴ Timeouts protect the database from accidents, not from a session that decides to ignore them.

The four timeouts

Agents are good at writing queries and bad at knowing what a query costs on a large table. They also leave transactions open while they “think” between steps. Each timeout covers a different way that goes wrong.

A few details from the docs matter in practice:

• All four default to 0, which means off. A bare number is read as milliseconds.¹
• statement_timeout runs from when the command arrives until the server finishes it. If several statements arrive in one simple-query message, each gets its own timer.¹
• lock_timeout counts each lock attempt separately, and there’s no point setting it at or above a nonzero statement_timeout, which would always fire first.¹
• An idle open transaction costs more than you’d think. The docs note it can hold locks and stops vacuum from removing dead rows, so tables bloat.¹
• transaction_timeout arrived in PostgreSQL 17, released on 26 September 2024. If it’s shorter than or equal to statement_timeout or idle_in_transaction_session_timeout, the longer one is ignored. Prepared transactions are exempt.²

There’s a fifth, idle_session_timeout, for sessions idle outside a transaction. The docs warn to be careful with it behind connection poolers, which may not handle a closed connection well.¹ Most agents connect through a pool, so we leave it off.

Set them on the agent’s role

The docs say not to set statement_timeout, lock_timeout or transaction_timeout in postgresql.conf, because they would hit every session, including your migrations and reports.¹ Put them on the role the agent logs in as:

Code A database command in SQL. The text around it says what it does.

-- once, as an admin
ALTER ROLE agent SET statement_timeout = '5s';
ALTER ROLE agent SET lock_timeout = '2s';
ALTER ROLE agent SET idle_in_transaction_session_timeout = '30s';
ALTER ROLE agent SET transaction_timeout = '60s';   -- PostgreSQL 17+

The values become the session defaults whenever that role starts a new session. They are applied at login only, so SET ROLE agent from another session won’t pick them up.³ If the agent uses more than one database, ALTER ROLE agent IN DATABASE app SET ... overrides the role-wide value for that database.³

This only works if the agent has its own role. If it shares your application’s login, you can’t give it tighter limits without slowing your app. Our safe Postgres access guide covers creating that role, and default privileges covers keeping its grants narrow as tables are added.

What happened when we ran them

We started a throwaway PostgreSQL 18.6 cluster on a laptop, created a 1,000-row orders table, a login role agent with SELECT and UPDATE on it, and the four settings above. Then we connected as agent.⁴

At login, pg_settings showed each value with source user, meaning it came from the role:

Code What the screen or file shows, copied as is.

idle_in_transaction_session_timeout|30000|ms|user
lock_timeout|2000|ms|user
statement_timeout|5000|ms|user
transaction_timeout|60000|ms|user

Then we tried four ways an agent can stall a database:

Note the difference in the last two rows. A statement or lock timeout cancels one statement and leaves the session usable. The idle and transaction timeouts end the whole connection, and whatever the transaction did is rolled back.⁴ An agent framework that doesn’t expect a dropped connection may retry blindly, so check what yours does. Our post on agents that retry and write twice shows why that matters.

The limit: the role can turn them off

Every one of these settings has the user context in pg_settings. The docs say any user can change their own session value for such settings with SET.⁴ So we tried, as agent:

Code What the screen or file shows, copied as is.

SET statement_timeout = 0;
SELECT pg_sleep(6);   -- ran to completion

ALTER ROLE agent SET statement_timeout = 0;
ALTER ROLE

Both worked. The second is worse: the docs confirm ordinary roles can set their own defaults³, so the agent removed its limit for every future session too. GRANT SET ON PARAMETER doesn’t help here; we ran REVOKE SET ON PARAMETER statement_timeout from PUBLIC and from agent, and the next SET still succeeded.⁴

That’s not a Postgres bug. These are comfort settings for a client, not a security boundary. For an agent, it means:

• Timeouts catch mistakes. A query the agent didn’t realise was expensive, a lock it didn’t expect, a transaction it forgot. That’s most of the real risk.
• They don’t stop a session that wants more time. A prompt-injected agent, or one told “the query keeps timing out, raise the limit”, can lift them. Watch your logs for SET statement_timeout and ALTER ROLE from the agent’s role.
• Real limits live outside the session. A gateway or proxy that sees each statement before it reaches Postgres can refuse SET and ALTER ROLE from the agent, and cap time itself. Grants (what the role can touch) are a true boundary; timeouts are not.

The same is true of default_transaction_read_only, which also has the user context.⁴ A role told to be read-only by default can SET itself out of it. Use GRANT and REVOKE for read-only, as our read-only access glossary entry explains.

Choosing values

There’s no right number for every database, but these starting points are easy to defend:

1. statement_timeout: a few seconds for an interactive agent. Long enough for an indexed query, short enough that a full-table scan on a big table fails fast. Raise it per task, not per role.
2. lock_timeout: shorter than statement_timeout. Otherwise it never fires.¹ One or two seconds keeps the agent from queuing behind (or in front of) your app.
3. idle_in_transaction_session_timeout: tens of seconds. Agents pause between tool calls while the model thinks. Too short and normal multi-step work gets killed; too long and locks pile up.
4. transaction_timeout: the longest task you’d accept, and longer than the other two, or they’re ignored.¹

If the agent runs migrations, give migrations a separate role with its own limits. A migration on a large table may need minutes and a lock, which is exactly what you don’t want the everyday agent role to have. Our post on letting agents write migrations safely has the pattern.

A checklist

1. Give the agent its own login role. Never share the application’s.
2. ALTER ROLE agent SET all four timeouts; check them with pg_settings after logging in as the agent.³
3. Keep lock_timeout below statement_timeout, and transaction_timeout above both.¹¹
4. Make sure the agent framework handles a cancelled statement and a dropped connection without retrying a write blindly.
5. Alert on SET statement_timeout, SET lock_timeout and ALTER ROLE sent by the agent’s role.
6. Treat grants, not timeouts, as the boundary. Put anything the agent must not be able to undo outside its session.

To see a gate refuse statements before they reach a real database, open the playground in your browser. To grade what your coding agents can reach on your own machine, including database URLs in their configs, run npx deedglass scan (the grade, explained).

Written with AI assistance and checked against our claims register.