--- name: prove-a-read-only-credential-cannot-write description: Confirm that a database role handed to an agent actually refuses writes, by the permission check rather than by an error that happened to arrive first. runbook: schema_version: v1 semver: 1.0.0 profile: P1 min_runtime_profile: R1 domain: data-pipelines targets: - postgres trigger: manual execution: human-with-agent risk: read-only blast_radius: service duration: 5-30m capabilities: - cli:psql publisher: std lang: en license: CC-BY-4.0 inputs: role_url: type: string required: true description: connection string for the role being proved, on the direct endpoint pooled_url: type: string required: false description: the same role through a connection pooler, when one is in front of the database allowed_table: type: string required: true description: a table the role is meant to read private_table: type: string required: true description: a table the role must not reach steps: - id: s1 kind: check title: The role holds no administrative membership tool: cli:psql command: psql "{{inputs.role_url}}" -Atc "select bool_or(true) from pg_auth_members m join pg_roles r on r.oid = m.member where r.rolname = current_user" expect: nothing — the role belongs to no group assert: stdout_not_matches: ^t$ on_fail: escalate:s11 next: s2 - id: s2 kind: check title: It can read what it is meant to read tool: cli:psql command: psql "{{inputs.role_url}}" -Atc "select count(*) >= 0 from {{inputs.allowed_table}}" expect: the query returns rather than being refused assert: exit_code: 0 on_fail: escalate:s11 next: s3 - id: s3 kind: check title: The write privilege is absent, asked directly tool: cli:psql command: psql "{{inputs.role_url}}" -Atc "select has_table_privilege(current_user, '{{inputs.allowed_table}}', 'INSERT')" expect: false — the privilege the next step tries to exercise does not exist assert: stdout_matches: ^f$ on_fail: escalate:s11 next: s4 - id: s4 kind: check title: And a write attempt is actually refused tool: cli:psql command: psql "{{inputs.role_url}}" -v ON_ERROR_STOP=1 -Atc "insert into {{inputs.allowed_table}} select * from {{inputs.allowed_table}} where false" expect: refused, with SQLSTATE 42501 or 25006 in the message assert: exit_code: 3 on_fail: s5 next: s6 - id: s5 kind: wait title: Fix the probe, then attempt the write again duration: 5m retry: max: 2 target: s4 next: s11 - id: s6 kind: check title: A private table is out of reach tool: cli:psql command: psql "{{inputs.role_url}}" -Atc "select has_table_privilege(current_user, '{{inputs.private_table}}', 'SELECT')" expect: false — the role cannot see it at all assert: stdout_matches: ^f$ on_fail: escalate:s11 next: s7 - id: s7 kind: check title: The grant list is the one that was intended tool: cli:psql command: psql "{{inputs.role_url}}" -Atc "select string_agg(distinct table_name, ',' order by table_name) from information_schema.role_table_grants where grantee = current_user" expect: exactly the tables granted deliberately, and nothing a later migration added assert: exit_code: 0 on_fail: escalate:s11 next: s8 - id: s8 kind: decision title: Is there a pooler in front of this database? question: Do clients reach the database through a connection pooler as well as directly? branches: pooled: s9 default: s10 - id: s9 kind: check title: The same refusal holds through the pooler tool: cli:psql command: psql "{{inputs.pooled_url}}" -Atc "select has_table_privilege(current_user, '{{inputs.allowed_table}}', 'INSERT')" expect: false — grants hold on both paths, even where role settings do not assert: stdout_matches: ^f$ on_fail: escalate:s11 next: s10 - id: s10 kind: check title: The second line of defence is in force here tool: cli:psql command: psql "{{inputs.role_url}}" -Atc "select current_setting('default_transaction_read_only')" expect: on — and off through a pooler, which is why the grants above are the real defence assert: stdout_matches: ^(on|off)$ on_fail: escalate:s11 next: end:success - id: s11 kind: escalate title: Escalate before the credential is used to: team:platform source: url: https://runbooks.directory/r/std/prove-a-read-only-credential-cannot-write/source.md fetched_at: 2026-09-04 upstream_state: current --- ## When to reach for this Before a read-only credential is handed to anything that runs unattended — an agent loop, a scheduled job, a contractor's console — and again after every migration. What is being established is not that the role *should* be read-only. It is that it *is*, on the paths it will actually be used through. The division this exists to hold: **the machine reads, the person writes.** A loop that can select is a loop whose worst mistake is a wrong answer. A loop that can update is a loop whose worst mistake is a production incident, and no prompt reliably prevents the second. ## Why a write test proves less than it looks like it does This is the step people get wrong, and the reason this procedure has a decision in the middle of it. "I tried to write and it failed" establishes nothing on its own. A statement can fail *before* the permission check ever runs — on a type mismatch, a constraint, a generated column — and the error looks like a refusal to anybody reading the exit code. Two real cases from one afternoon: an insert refused with `42804` because an id column was a UUID and the probe passed an integer, and after that was fixed, `428C9` because a generated column cannot be written. Neither statement reached an access check. Both looked like proof. So the check requires the *specific* codes — `42501` insufficient privilege, `25006` read-only transaction — and treats anything else as **failed for another reason, does not count**. And the probe is shaped so nothing but permissions can stop it: `insert into t select * from t where false` is type-correct by construction, touches no generated column, and inserts no rows even if it succeeds. If the probe fails another way, the answer is to fix the probe, not to record a pass. ## Two paths, and a defence that only exists on one `ALTER ROLE ... SET default_transaction_read_only = on` is a good second line: even if a grant is forgotten, the write fails with `25006`. It is also invisible through a connection pooler — PgBouncer and its kin do not replay role settings — so on the pooled endpoint the setting reads `off` while the direct one reads `on`. That does not weaken the main defence: grants apply on both paths. What it means is that a proof performed on one endpoint is a proof about one endpoint. Test whichever ones will carry traffic. ## Testing as the role, not as the owner `SET ROLE` from an administrative connection is not this test. Inheritance is different under an owner, and some refusals simply do not appear. Connect as the role, with its own credentials, the way the thing being restricted will connect. ## Grants that do not grow on their own Grant tables one by one rather than through `ALTER DEFAULT PRIVILEGES`. The default-privileges route is less typing and it silently extends the role to every table created afterwards — including the ones holding personal data, credentials and keys that nobody would have granted deliberately. The price is a manual grant after a migration that adds a table the role needs, which is why this procedure runs then too. It catches the opposite mistake as well: a migration that handed the role more than it should have. ## A role a console created may not be what you asked for On managed platforms, a role created through the web console, the CLI or the provider API can be placed in an administrative group automatically — which makes it a writer whatever the grants say. Creating the role in SQL avoids it, and the first step of this procedure checks for it rather than trusting either route. ## Where the credential lives Not in the file the writing tools read. If the loop's connection string sits in the same environment file as the owner's, a session that reaches for the first thing it finds will find the wrong one, and the restriction is undone by a variable name. Separate file, separate variable, and the owner's variable unset in the loop's environment.