Creating a proper read-only Postgres role for backups

The least-privilege role for pg_dump: grants, default privileges, the PUBLIC TEMP gotcha, and how to prove the role can't write.

The role

CREATE ROLE backup_reader LOGIN PASSWORD '...' 
  NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;
GRANT CONNECT ON DATABASE mydb TO backup_reader;
GRANT USAGE ON SCHEMA public TO backup_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_reader;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO backup_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO backup_reader;

Repeat the schema grants per schema. The DEFAULT PRIVILEGES line is the one everyone forgets — without it, next month's new tables silently vanish from your backups' permissions.

The PUBLIC TEMP gotcha

Postgres grants TEMPORARY on every database to PUBLIC by default, so your 'read-only' role can create temp tables. Harmless? Mostly — but it means a leaked backup credential can burn CPU/disk, and it fails strict write-probe checks:

REVOKE TEMPORARY ON DATABASE mydb FROM PUBLIC;

Grant TEMP back explicitly to roles that need it.

Prove it

Trust, but verify — as the role:

BEGIN; CREATE TEMP TABLE t(i int); ROLLBACK;  -- must fail
INSERT INTO users SELECT * FROM users WHERE false;  -- must fail

Firedrill runs this probe automatically at connect time and refuses roles that can write — a backup service never needs write access to your database.