Postgres SET ROLE per user: making an AI agent query as the person it serves

The short answer

Have the agent’s login role hold no table access of its own, and for each request open a transaction, run SET LOCAL ROLE <user role> and set the user’s identity with set_config('app.tenant', '<id>', true). Row-level security policies on the user role then filter every query to that user, and both settings vanish at commit, so the next request on the pooled connection starts clean. After SET ROLE, permission checks run as the named role.¹

The catch: SET ROLE, RESET ROLE and set_config are ordinary SQL. If the model can write raw SQL into that transaction, it can change the identity too. The pattern holds only when your tool code, not the model, sends those statements.

The pattern

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

-- once, at setup
create table tickets(id int primary key, tenant text, body text);
alter table tickets enable row level security;
create role tenant_user nologin;
grant select, update on tickets to tenant_user;
create policy by_tenant on tickets to tenant_user
  using (tenant = current_setting('app.tenant', true));
create role agent_pool login password '...' noinherit;
grant tenant_user to agent_pool;

Each piece has a job:

Per request, your tool runs:

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

begin;
set local role tenant_user;
select set_config('app.tenant', 'acme', true);  -- true = local to this transaction
-- ... the agent's query here ...
commit;

A tested run

We ran this on PostgreSQL 18.6, with three tickets: two for acme, one for globex. First, the pool role on its own, before the pattern:

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

ERROR:  permission denied for table tickets

Good: noinherit means the login role reaches nothing by default. Inside the transaction, after the role switch and the tenant setting:

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

 current_user | count
--------------+-------
 tenant_user  |     2

Two rows, both acme. After commit, on the same connection:

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

 current_user | tenant_after_commit
--------------+---------------------
 agent_pool   |

The role is back to the pool login and the tenant is empty, because SET LOCAL and a local set_config last only until the transaction ends. That’s what makes the pattern safe on a pooled connection: a request that forgets to set the tenant gets nothing, not the previous user’s data.

Where it breaks: the model can send SET too

Same transaction, one more statement, the kind a model might send if your tool passes its SQL straight through:

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

select set_config('app.tenant', 'globex', true);
select current_user, count(*) as after_model_changes_setting from tickets;

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

 current_user | after_model_changes_setting
--------------+-----------------------------
 tenant_user  |                           1

Still tenant_user, now seeing globex’s ticket. Nothing was bypassed: the policy did exactly what it says, with a value the model chose.

Roles have the same weakness. RESET ROLE and SET ROLE NONE can be executed by any user, and SET ROLE works for any role the session user holds the SET option on, directly or through a chain of memberships.¹ If agent_pool is a member of a role per tenant, the model can switch between them. Membership grants carry a SET option that defaults to TRUE; GRANT ... WITH SET FALSE turns it off, but then your tool can’t switch either.²

So SET ROLE per user is a correct way to apply policies, and not a boundary against the SQL the model writes.

Three ways to keep the identity out of the model’s hands

1. The model doesn’t write SQL. Give it tools like get_ticket(id) and update_ticket(id, body). Your code builds the transaction, sets role and tenant from the authenticated request, and runs a fixed, parameterised statement. This is the strongest option and the simplest to reason about.
2. The model writes SQL; your tool checks it. Parse each statement and refuse anything that isn’t a plain SELECT, INSERT, UPDATE or DELETE: no SET, RESET, set_config, DO, function calls you didn’t list, or multiple statements. A parser catches what a regex misses. A tool-call policy is the place to state those rules.
3. One connection per user identity. Instead of switching roles, log in as a role that belongs to one tenant, with no membership it can switch into. Costs more connections; removes the switch.

Details that bite

• session_user doesn’t change. SET ROLE changes current_user; the session user stays the login role.¹ Write policies and audit triggers against the one you mean.
• SET ROLE can’t run inside a SECURITY DEFINER function.¹ Do the switch in the transaction, not in a helper function with owner rights.
• Role defaults are only defaults. A setting attached with ALTER ROLE ... SET becomes the session default at login, and a session can change it; in our earlier test a plain SET turned off a role’s statement_timeout.⁴ Don’t put the tenant in a role default and assume it sticks.
• Views can skip the policy. A view runs with its owner’s rights unless it has security_invoker = true. See the view post.
• Admin memberships undo all of this. If the pool role belongs to a provider admin group, the model can SET ROLE into it; the admin roles post shows that test.

A checklist

• Login role is noinherit and reaches no table without switching.
• Policies are written for the user role and read the tenant with current_setting(name, true).
• SET LOCAL ROLE and a local set_config run inside every transaction, sent by your code.
• The model can’t send SET, RESET, set_config or multiple statements.
• No view without security_invoker over a table with policies.
• The pool role belongs to no admin group.

Our safe Postgres access post covers the role, policies and limits as one setup. To see what a configured agent can reach before you start, paste the agent’s MCP config into the check page or run npx deedglass scan (how the grade works). To try the checks in your browser without installing anything, open the playground.

Written with AI assistance and checked against our claims register.