medium risksecurity relevant~35 min

Runbook: Create a Role and Grant Least Privilege

1 · Prerequisites

Confirm every item is in place before any state change.

A written statement of what the role must be able to do: which databases, which schemas, which tables, and whether it writes
A database superuser connection, or a role with sufficient privilege to create roles and grant on the objects concerned
Knowledge of who owns the objects the role will access, because default privileges are granted per owner and not per schema
A secrets store to receive the credential, and confirmation that the credential will never be written into a ticket, a chat message or a configuration file in version control
A test client on the application network path, to verify the role works where it will actually be used
Agreement on whether the role carries a VALID UNTIL, and if so who renews it

2 · Pre-checks

Read-only diagnostic commands. If any of these don't match expected output, stop and investigate further.

· Confirm the role does not already exist. SELECT rolname, rolcanlogin, rolsuper, rolvaliduntil, rolconnlimit FROM pg_roles WHERE rolname = :role; A role reused across two applications cannot be revoked for one of them.
· Establish who owns the objects. SELECT n.nspname, c.relname, pg_get_userbyid(c.relowner) AS owner FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = :schema AND c.relkind = 'r' ORDER BY 1,2; Default privileges apply to objects created by a specific role, so you need the owner's name before you can make future tables inherit anything.
· Check what PUBLIC already has. \dp in the target schema, and SELECT nspname, nspacl FROM pg_namespace WHERE nspname = :schema; PostgreSQL 15 and later no longer grant CREATE on the public schema to PUBLIC, but an upgraded cluster may still carry the old grant.
· Confirm the connection path will work. A role with perfect privileges and no matching pg_hba.conf rule cannot connect. Check pg_hba_file_rules for a rule that covers this role, database and source address before creating anything.
· Decide the password policy for this role now. Whether it has a VALID UNTIL, who rotates it, and where the value lives. An expiry set at creation and never revisited is a scheduled outage with no notice.
· **Confirm password_encryption is scram-sha-256.** SHOW password_encryption; A role created while this is md5 stores a weaker verifier, and changing the setting afterwards does not re-hash existing passwords.

3 · Procedure

Execute each step in order. Verify the expected output of a step before moving to the next.

1Create a group role for the privilege set, and a login role for the client. The group holds the grants; the login role is a member of it. This separates "what this kind of access means" from "who has it", and it makes adding a second client a one-line change rather than a repeat of this whole procedure.
2Create the group role with no login. CREATE ROLE app_readonly NOLOGIN; It exists only to be granted to.
3Create the login role with a strong password from the secrets store. CREATE ROLE app_ro LOGIN PASSWORD '<from-secrets-store>'; Generate the value in the secrets store and read it from there; do not invent it at the keyboard and paste it into two places.
4Set the connection limit deliberately. ALTER ROLE app_ro CONNECTION LIMIT 40; A per-role limit means one misbehaving client exhausts its own allocation rather than the cluster's.
5Grant connect on the database, and nothing broader. GRANT CONNECT ON DATABASE orders TO app_readonly;
6Grant usage on the schema. GRANT USAGE ON SCHEMA app TO app_readonly; Usage on a schema permits naming objects inside it; it grants nothing on the objects themselves.
7Grant on the objects that exist today. GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readonly; and, for a writing role, GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_readonly; plus GRANT USAGE ON ALL SEQUENCES IN SCHEMA app TO app_readonly; if it inserts into columns with defaults.
8Grant on the objects that do not exist yet. ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT SELECT ON TABLES TO app_readonly; This is the step that is most often missed, and its absence produces a role that works today and fails after the next migration.
9Make the login role a member of the group. GRANT app_readonly TO app_ro;
10Set role-scoped safety limits. ALTER ROLE app_ro SET statement_timeout = '30s'; and, for a reporting role, ALTER ROLE app_ro SET work_mem = '64MB'; and ALTER ROLE app_ro SET idle_session_timeout = '10min';. These are cheaper to set now than to add during an incident.
11Store the credential in the secrets store and nowhere else. Record the store path in the change note, never the value.
12Verify from the application network path, as the new role, against the real database: connect, run a permitted statement, and run a statement that must fail.
13Record the role, its group, its grants, its owner, and the secrets-store path.

4 · Verification

Confirm the procedure actually fixed the problem.

✓The role connects from the application's network path, using the credential from the secrets store, with a driver configured for sslmode=verify-full.
✓A permitted statement succeeds. For a read-only role, a SELECT against a table in the target schema.
✓A statement that must fail does fail, with ERROR: permission denied for table .... A role that has never been refused anything has not been shown to be least-privileged.
✓A table created after the grants is accessible without any further action, which proves ALTER DEFAULT PRIVILEGES was applied to the correct owner: create a table as the owner, then select from it as the new role.
✓\dp app.* shows the intended access control lists, and shows the group role rather than the login role, confirming the grants landed on the group.
✓SELECT rolname, rolcanlogin, rolsuper, rolcreatedb, rolcreaterole, rolvaliduntil, rolconnlimit FROM pg_roles WHERE rolname IN (:role, :group); shows no unintended attribute — in particular rolsuper, rolcreatedb and rolcreaterole are all false.
✓The credential appears in the secrets store and appears nowhere in the change record, the ticket, or any repository.

5 · Rollback

If verification fails, undo the procedure in reverse order.

↶Revoke membership first: REVOKE app_readonly FROM app_ro;. This removes the access immediately for new statements without dropping anything.
↶To remove the role entirely, drop the login role before the group: DROP ROLE app_ro;. A role cannot be dropped while it owns objects or holds grants, and the error names what is in the way.
↶If the role owns objects, decide deliberately between REASSIGN OWNED BY app_ro TO app_owner; and DROP OWNED BY app_ro;. The first transfers, the second deletes the objects. Read the difference twice before running either.
↶To reverse default privileges, repeat the ALTER DEFAULT PRIVILEGES statement with REVOKE in place of GRANT, for the same owner and schema. Default privileges are recorded per grantor, so revoking as a different role does nothing and reports success.
↶If the credential was exposed in the process — pasted into a ticket, echoed into a shell history, written to a log — rotate it rather than rolling back. ALTER ROLE app_ro PASSWORD '<new>'; and update the secrets store. An exposed credential is not undone by deleting the message.
↶Record the rollback and its reason. A role that was created and removed is information the next person needs.

6 · Escalation

When the runbook isn't enough, contact:

· The role needs a privilege that would make it effectively a superuser — pg_read_server_files, pg_execute_server_program, CREATEROLE, or SUPERUSER itself: escalate to the data owner. These are not degrees of least privilege; they are the absence of it.
· The application will not work without CREATE on a schema in production: escalate to the application owner. A service that performs DDL at runtime is a design question, and granting it quietly is how a migration becomes an incident.
· The objects have several owners and no single ALTER DEFAULT PRIVILEGES covers them: escalate to whoever owns the schema. The answer is usually to consolidate ownership, and doing that by hand under time pressure is worse than the current problem.
· The role must access objects in a schema owned by another team: escalate rather than granting. Cross-team access needs the other team's agreement recorded, and a grant made without it will be revoked without warning.
· The credential must be shared with a third party or placed in a system you do not control: escalate to security before creating it. The question is not whether the grant is narrow but whether the credential can be revoked when the relationship ends.
· You are asked to reuse an existing role because creating one is inconvenient: escalate. Shared roles cannot be revoked for one consumer, and the inconvenience is paid once while the coupling is paid forever.

Two roles, not one. A group role that holds the privileges and a login role that is a member of it.

That separation costs one extra statement today and repays it every time somebody asks for a second client with the same access, or asks what “read-only on the orders database” actually means, or asks you to revoke one consumer without disturbing the others.

The step that is always missed

ALTER DEFAULT PRIVILEGES is what makes the grant survive the next migration

GRANT SELECT ON ALL TABLES IN SCHEMA app grants on the tables that exist at that moment. It is not a standing rule. The next table created by a migration is not covered, and the role that worked perfectly on Monday fails on Tuesday with permission denied.

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO app_readonly;

Note FOR ROLE app_owner. Default privileges are recorded per grantor: they apply to objects created by that role. Setting them without naming the owner records them against you, and objects the migration creates as app_owner inherit nothing.

This is why the verification step in this runbook creates a table and then reads it as the new role. Nothing else tests this.

Blast radius

Action	Reversible?	What it costs if wrong
`CREATE ROLE ... NOLOGIN`	Yes	Nothing
`GRANT` on existing objects	Yes, with `REVOKE`	Read or write access, immediately
`ALTER DEFAULT PRIVILEGES`	Yes, with the matching `REVOKE`	Future objects, silently
`DROP OWNED BY`	No	Deletes the objects the role owns
Exposing the credential	No	Rotation, not rollback

Prove the refusal

A role that has never been refused anything has not been shown to be least-privileged. Two checks, both cheap:

-- as the new role: this must succeed
SELECT count(*) FROM app.orders;

-- as the new role: this must fail
INSERT INTO app.orders DEFAULT VALUES;
-- ERROR:  permission denied for table orders

The credential

Generate it in the secrets store. Read it from there. Record the store path in the change note and never the value.

A credential that has been pasted into a ticket, echoed into a shell history file, or committed to a repository has been disclosed, and the remedy is rotation rather than deletion of the message.