PgBeam vs a read-only Postgres role

a read-only Postgres role: A read-only Postgres role is a database role with SELECT granted and writes withheld, created with GRANT inside the database.

A read-only role is the first thing most teams reach for when an agent needs database access, and for blocking writes it works. The gap shows up the moment you need anything more than read-only: a role cannot mask a column, cap query volume, audit per agent, or be revoked without a rotation. PgBeam enforces read-only at the wire and layers the rest on top, per credential, without changing your roles.

PgBeam vs a read-only Postgres role, side by side

Keep the role, add the policy it cannot express

These compose. The role stays as the floor Postgres enforces on its own, and the policy carries the rules that refer to columns, volume, and identity, which a role has no grammar for. Nothing here changes your database roles or needs a migration.

1. 1

   Keep the read-only role as the floor

   Do not remove it. A proxy is a process, and a process can be misconfigured or bypassed by anyone holding the database's own connection string. The role is the control that holds when that happens, so it stays, and the session-level setting is worth adding because it survives a transaction being committed out from under it.

   psql, as an adminCopy

   CREATE ROLE agent_ro LOGIN PASSWORD 'rotate-me';
   ALTER ROLE agent_ro SET default_transaction_read_only = on;
   GRANT CONNECT ON DATABASE mydb TO agent_ro;
   GRANT USAGE ON SCHEMA public TO agent_ro;
   GRANT SELECT ON public.orders, public.order_items TO agent_ro;

2. 2

   Write the four rules a GRANT cannot say

   Column masking, a row cap, a query budget, and a statement timeout. Each of these is a sentence about a column or about volume, and GRANT's vocabulary is objects and privileges, so none of them can be written as a grant at all.

   TerminalCopy

   pgbeam policies create \
     --name agent-readonly \
     --mode read_only \
     --allow public.orders --allow public.order_items \
     --mask customers.email=redact \
     --mask customers.ssn=hash \
     --max-rows 500 \
     --budget-queries-per-hour 300 \
     --statement-timeout-ms 8000

3. 3

   Issue one credential per agent, not one role per agent

   This is the step that makes the audit trail answer the question an incident starts with. Three agents sharing agent_ro are one row in pg_stat_activity and one name in the log. Three credentials against the same upstream role are three identities, and revoking one does not touch the other two.

   TerminalCopy

   pgbeam agents create --name analytics-agent --policy pol_xxx --expires 30d
   pgbeam agents create --name support-copilot --policy pol_xxx --expires 30d
   
   # revoke one without rotating anything
   pgbeam agents revoke agt_xxx --yes

4. 4

   Check the rules you wrote are the rules that fire

   dry-eval runs a statement through the data plane's own policy engine and prints the verdict without touching your database. Run the statements you are afraid of before an agent does.

   TerminalCopy

   pgbeam policies dry-eval --policy pol_xxx --sql "UPDATE public.orders SET status = 'paid'"
   pgbeam policies dry-eval --policy pol_xxx --sql "SELECT ssn FROM public.customers"
   pgbeam policies dry-eval --policy pol_xxx --sql "SELECT * FROM public.orders FOR UPDATE"

Point PgBeam at the read-only role you already have. The agent credential's policy is a ceiling under whatever that role can do, never a way around it, so adopting this cannot widen an agent's access beyond what the role already granted.

Six statements a read-only role permits

Every statement below is allowed by a role granted SELECT on the public schema, because every one of them is a read or is refused for a reason the role cannot state. The second column is what the same statement meets under a policy. Verdicts and rule names are the ones the policy engine emits.

1  SELECT id, total FROM public.orders LIMIT 10
2  SELECT email, ssn FROM public.customers WHERE id = 42
3  SELECT * FROM public.customers
4  UPDATE public.orders SET status = 'paid'
5  SELECT id FROM public.orders FOR UPDATE
6  SELECT most_common_vals FROM pg_stats WHERE tablename = 'customers'

1  Verdict: allow    Rule: ok
2  Verdict: mask     Rule: masked_column      Masked: customers.email (redact), customers.ssn (hash)
3  Verdict: block    Rule: table_not_allowed
   Reason:  table "public.customers" is not in this credential's allowlist
4  Verdict: block    Rule: read_only
   Reason:  this credential is read-only; UPDATE is not permitted
5  Verdict: block    Rule: locking_select
   Reason:  SELECT ... FOR UPDATE/SHARE takes row locks and is blocked in read-only mode
6  Verdict: block    Rule: table_not_allowed
   Reason:  relation "pg_stats" is not in this credential's allowlist

What happens when the thing you are worried about happens

The four questions a role cannot answer, in the order they usually get asked, with the honest boundary on each.

blocked by PgBeam agent policy: table "public.customers" is not in
this credential's allowlist

pgbeam agents revoke agt_xxx --yes

pgbeam audit list --event blocked --limit 20
pgbeam audit verify

More comparisons

Questions