Create a Read-Only DB Role for Agents

Prepare a SELECT-only role and protected connection. The SQL, role name, grants, denylist, RLS behavior, timeouts, and connection steps are examples to adapt to your system, not live production configuration.

Access model

1. SELECT-only grants. Grant no write permissions; use a denylist to exclude secrets and PII.
2. Current and future tables. Grant SELECT on all tables in <application_schema>, including future tables through default privileges, then revoke denylisted tables. Never grant the <restricted_schema> schema. New sensitive tables need a manual revoke.
3. Soft guardrails. Set default_transaction_read_only = on and a short statement_timeout suited to the workload.

RLS: tables may return no rows when the role has no applicable policy. Review row-level policies with the database administrator to ensure the role sees only the intended rows.

Workflow

1. Check the role: select rolname from pg_roles where rolname = '<reader_role>';. Use your chosen name; if it exists, update rather than recreate it.
2. Agree on the denylist with the human. Identify secret or PII tables agents must never see, such as tables containing credentials, webhook payloads, and identity data.
3. Save SQL in the repo, e.g. docs/<setup-file>.sql. Comment what changes, why, and how to apply, verify, and revert. Chat-only SQL is insufficient.
4. The human applies it. Agents never run production DDL. In Supabase, paste the file into the SQL editor, then delete the query from its history because it contains the password. The human stores the password in a password manager.
5. Wire the connection through a protected secret manager or local environment configuration; never commit it. Supabase session poolers typically use <role>.<project-ref> on port 5432. Install psql if missing (Homebrew libpq on macOS).
6. Run every verification check below.
7. Create a project-local usage skill covering key tables, query patterns, read-only access, and never pasting PII into commits or docs.

SQL template

-- Example only. Rename the role, schema, timeout, and denylist for your system.

-- 1. role + soft guardrails
create role <reader_role> with login password 'REPLACE_ME';
alter role <reader_role> set default_transaction_read_only = on;
alter role <reader_role> set statement_timeout = '10s';  -- example timeout; change as needed

-- 2. SELECT-only grants, denylist model
grant usage on schema <application_schema> to <reader_role>;
grant select on all tables in schema <application_schema> to <reader_role>;
alter default privileges for role <owner_role> in schema <application_schema>
  grant select on tables to <reader_role>;   -- future tables auto-readable

-- 3. denylist: keep secrets and PII invisible (replace with your own tables)
revoke select on table <application_schema>.<sensitive_table_1> from <reader_role>;
revoke select on table <application_schema>.<sensitive_table_2> from <reader_role>;

Revert: drop owned by <reader_role>; drop role <reader_role>;

Verification

All checks must pass before declaring done:

# Load the connection URL from your secret manager or local environment configuration.
psql "<readonly-connection-url>" -X -c "select current_user;"                      # -> <reader_role>
psql "<readonly-connection-url>" -X -c "show statement_timeout;"                   # -> matches your chosen timeout
psql "<readonly-connection-url>" -X -c "select count(*) from <application_schema>.<readable_table>;"  # -> real number, NOT 0
psql "<readonly-connection-url>" -X -c "delete from <application_schema>.<any_table> where false;"
# -> ERROR: read-only transaction (soft guardrail)
psql "<readonly-connection-url>" -X -c "begin; set transaction read write; delete from <application_schema>.<any_table> where false; rollback;"
# -> ERROR: permission denied (the hard wall)
psql "<readonly-connection-url>" -X -c "select * from <application_schema>.<denylisted_table> limit 1;"  # -> ERROR: permission denied
psql "<readonly-connection-url>" -X -c "select * from <restricted_schema>.<identity_table> limit 1;"    # -> ERROR: permission denied

Verify both protections: writes fail under the read-only guardrail, and fail with permission denied when it is off. If any check fails, correct the configuration through the human and rerun all checks.

Troubleshooting and maintenance

• No rows across tables: check RLS policies and consult the database administrator.
• Write verification succeeds: stop. Have the human revoke the role's privileges and correct the setup, then rerun all checks.
• Supabase authentication fails: check the pooler username, usually <role>.<project-ref>.
• Legitimate query times out: add filters or limits before raising the timeout.
• New sensitive table: add revoke select to the denylist.
• Password rotation: have the human run alter role <reader_role> with password '...' and update the stored connection secret.
• Never allow agents to write through this role. Production writes remain human-only.