A Read-Only Database Role for Agent Tools: Grants and Limits

Most read-only roles people hand to tools are less read-only than they think. These are the four leak paths.

The ownership point surprises people. The PostgreSQL documentation states plainly that the right to modify or destroy an object is inherent in being the object's owner and cannot be granted or revoked in itself (PostgreSQL documentation). If your agent tool connects with the same role that ran the migrations, the grants you write later do not constrain it at all. The role must be created fresh, own nothing and inherit from nothing.

Start with a dedicated login. The pattern most read-only guides converge on is CONNECT on the database, USAGE on the schema, then SELECT only on the tables you name (Simon Willison). The canonical Stack Overflow answer makes the same point: granting CONNECT alone lets a user connect but gives them no other privileges, and USAGE on the schema is what lets them see objects (Stack Overflow).

CREATE ROLE agent_analytics LOGIN PASSWORD :'pw'
  NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;

GRANT CONNECT ON DATABASE analytics TO agent_analytics;
GRANT USAGE ON SCHEMA public TO agent_analytics;

NOINHERIT is not decorative. If the role is ever added to a group, or a group is added to it by accident, inheritance is how privileges travel without anyone writing a GRANT against the tool role. Keeping it off makes every effective privilege traceable to one line of your allow-list.

Do not grant USAGE on schemas the agent has no business seeing. USAGE on a schema is what lets a role see the object names inside it, and a tool that can enumerate names will try them.

Table-level grants are the baseline. Column-level grants are where least privilege pays off, and the syntax is supported directly in GRANT: the privilege is applied per column (PostgreSQL documentation).

-- Baseline: whole table
GRANT SELECT ON public.orders TO agent_analytics;

-- Narrower: named columns only
GRANT SELECT (order_id, placed_at, status, total_cents, region_code)
  ON public.order_items TO agent_analytics;

GRANT SELECT (order_id, customer_id, sku, quantity, total_cents)
  ON public.order_items TO agent_analytics;

Two operational consequences follow, and both bite in production.

First, SELECT * against a table where the role holds only column grants fails. The query needs every column, and the role cannot read email, phone or whatever else you withheld. So the agent's query builder must name columns. That is a feature: it forces the tool to declare what it reads and keeps sensitive columns out of the model context entirely.

Second, column grants and table grants fail differently. A table-level denial is a whole-table error. A column-level denial fires when the planner resolves the target list. Your error handling must tolerate both, and your test suite must exercise both.

A useful decision rule: grant at table level for dimensions the tool legitimately reads end to end (country codes, product categories), and at column level for anything carrying a person or a payment. If a column never belongs in a prompt, it should never be in the grant.

Revoking is not symmetric with granting, and doing it in the wrong order leaves gaps. Run the sequence below as the object owner or a superuser.

-- 1. Remove the accidental PUBLIC surface
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM PUBLIC;

-- 2. Remove anything previously granted to the tool role
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM agent_analytics;

-- 3. Remove what the tool role inherited from groups
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM analyst_group;

-- 4. Remove future-object defaults
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  REVOKE SELECT ON TABLES FROM agent_analytics;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  REVOKE SELECT ON SEQUENCES FROM agent_analytics;

-- 5. Re-apply the allow-list from step 2

Steps 1 through 3 handle the present. Step 4 handles the future, and it is the one people skip. ALTER DEFAULT PRIVILEGES sets privileges applied to objects created later and does not affect objects that already exist; it also applies only to objects created by the role whose defaults you are altering (PostgreSQL documentation). If your migration role and your schema owner are different roles, you need one ALTER DEFAULT PRIVILEGES block per creating role. A single block against the wrong owner is silently ineffective.

Note the sequence revokes. A read-only analytics tool rarely needs sequence USAGE, and sequence access is a write-path primitive.

For a second wall, set the role's default transaction mode to read-only so that even a mistaken GRANT cannot produce a successful write:

ALTER ROLE agent_analytics SET default_transaction_read_only = on;

This is session state, not a privilege, and it can be changed inside the session unless you also prevent that. Treat it as a speed bump behind the grants, not a replacement for them.

A grant set you have not tested is a hypothesis. Write the test as part of your migration and assert on the error class rather than the message text, which changes with version and locale.

import psycopg

WRITE_ATTEMPTS = [
    "INSERT INTO public.orders (order_id, status) VALUES (1, 'test')",
    "UPDATE public.orders SET status = 'test' WHERE order_id = 1",
    "DELETE FROM public.orders WHERE order_id = 1",
    "TRUNCATE public.orders",
]

with psycopg.connect(dsn, user="agent_analytics") as conn:
    for stmt in WRITE_ATTEMPTS:
        try:
            with conn.transaction():
                conn.execute(stmt)
        except psycopg.errors.InsufficientPrivilege as exc:
            assert exc.sqlstate == "42501", exc.sqlstate
        else:
            raise AssertionError(f"write succeeded: {stmt}")

SQLSTATE 42501 is insufficient_privilege, the class PostgreSQL raises when a role lacks a needed privilege. Asserting on the class rather than on English text means the test survives a locale change or a server upgrade.

Add four more assertions to the same file:

1. A SELECT * on the column-restricted table fails.
2. A SELECT naming only granted columns succeeds and returns rows.
3. A SELECT on a table outside the allow-list fails.
4. After each failed write, the target row count is unchanged.

The third catches a PUBLIC leak, because it fails only if you actually removed PUBLIC access. The fourth catches a driver that swallows the error and retries, which is the failure case the test exists for.

Run the suite in CI against a database restored from a recent production dump where possible, and against a schema-only fixture where it is not. A fixture that mirrors the real object list is what catches "a new table appeared and the role can read it" before the next deploy.

The role is only as strong as the credential handling around it. Two practical points.

If you reach Postgres through PgBouncer, the authentication path matters. PgBouncer authenticates against an internal user list or passes the attempt through to Postgres, and it pools connections per user and per database (ScaleGrid). Transaction pooling, the recommended mode for most applications, assigns a server connection only for the duration of a transaction and breaks session-based features such as SET and LISTEN (Heroku Dev Center). That matters here because SET default_transaction_read_only is a session-level statement and does not persist across statements under transaction pooling. Grants do persist, because they are catalog checks and not session state. This is the strongest argument for putting the boundary in GRANT rather than in a session variable.

Separately, keep the credential distinct per tool. One agent_readonly shared by five tools gives you one revocation event for five consumers, and the audit trail, which comes from the role name in the server log, collapses to one indistinguishable actor. Per-tool roles also let you revoke one tool without breaking the other four.

If several tools talk to several databases and you want the key rotation and call logging in one place rather than in each tool's environment, a gateway in front of the models does not remove the database boundary, but it does centralize the key. Quanta Inference (https://quantaintelligence.ai/inference) puts text, image and embedding models behind one API key, which helps when the agent that writes the SQL and the agent that reads the results are different models. The database still enforces the grants.

The allow-list drifts. New tables appear, someone adds a grant to fix an incident, and the boundary widens without anyone deciding to widen it. Run this and compare against the allow-list you keep in version control.

SELECT table_schema, table_name, privilege_type, grantee
FROM information_schema.role_table_grants
WHERE grantee IN ('agent_analytics', 'PUBLIC')
ORDER BY table_schema, table_name, grantee;

SELECT table_name, column_name, privilege_type
FROM information_schema.column_privileges
WHERE grantee = 'agent_analytics'
ORDER BY table_name, column_name;

SELECT defaclrole::regrole, defaclnamespace::regnamespace, defaclacl
FROM pg_default_acl;

The first query is the reachability map. The second is the column allow-list. The third is where default privileges live, and a row naming the tool role is the one you did not expect.

Diff that output against the committed allow-list in CI and fail the build on any addition. That converts an access-control review into a unit test, which is the only form of review that reliably happens.

One last calibration point on indexing, since teams often reach for index tuning to fix an agent that is misbehaving. Index advisors that optimize against a workload can and do produce recommendations that regress query performance when cost estimates are off (arXiv). A grant does not have that failure mode: it either permits a table or it does not. That asymmetry is the reason to settle the grants before tuning anything.