Ownership, default privileges, and the permission disaster they cause · PostgreSQL

The commonest permission failure in PostgreSQL is not a missing grant. It is a grant that was correct when it was made and does not cover the objects created since.

The demonstration

A schema with a read-only group, granted SELECT on all tables. Then a new table is created.

Read-only / SafeGRANT ON ALL TABLES, then a new table— Captured from a live 18.6 cluster. Same grant, same schema, opposite results.

$ psql -U postgres  # GRANT, CREATE TABLE, then check both

GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_ro;
CREATE TABLE app.created_later(id int);

    tbl      | app_ro_can_select
---------------+-------------------
existing      | t
created_later | f
(2 rows)

ALL TABLES is not a standing instruction. It expands, at the moment the statement runs, to the list of tables that exist, and grants on each of them. A table created a second later is not covered, and nothing reports this.

The fix, and its trap

ALTER DEFAULT PRIVILEGES records a rule applied whenever an object is created.

Configuration changea standing rule, and a table created afterwards— Captured live. The new table is covered without any further GRANT.

$ psql -U postgres  # ALTER DEFAULT PRIVILEGES, then CREATE TABLE

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

CREATE TABLE app.created_after_default(id int);

        tbl          | app_ro_can_select
-----------------------+-------------------
created_after_default | t
(1 row)

The trap is in the FOR ROLE clause. Default privileges are recorded per creating role, and apply only when that role creates an object.

Read-only / Safethe rule, as stored— One row per (creating role, schema, object type). Captured live.

$ psql -U postgres -c 'SELECT defaclrole::regrole AS for_role, defaclnamespace::regnamespace AS in_schema, defaclobjtype AS objtype, defaclacl FROM pg_default_acl'

 for_role  | in_schema | objtype |      defaclacl
-----------+-----------+---------+----------------------
app_owner | app       | r       | {app_ro=r/app_owner}
(1 row)

Omitting FOR ROLE records the rule for the role running the statement, which is usually a superuser or a DBA — and if migrations actually run as app_owner, the rule never fires.

Setting default privileges as the wrong role, and believing the problem is solved

This is the failure mode that makes default privileges feel unreliable.

A DBA connects as postgres, runs ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT ON TABLES TO app_ro, tests it by creating a table as postgres, and confirms it works. The rule was recorded for postgres and the test exercised exactly that path.

Migrations then run as app_owner. No rule exists for app_owner, so every table the migration creates is invisible to app_ro, and the failure appears the next morning attached to whichever report ran first.

The check is pg_default_acl: its for_role column must list the role that actually creates objects, which is a question about your deployment pipeline rather than about the database.

SELECT defaclrole::regrole AS for_role,
       defaclnamespace::regnamespace AS in_schema,
       defaclobjtype AS objtype, defaclacl
  FROM pg_default_acl ORDER BY 1, 2;

Cross-check that list against the roles your migrations connect as. Any creating role missing from it is a future permission incident.

Covering the ground properly

A complete setup for one schema needs both halves, and needs sequences as well as tables:

-- Retrospective: everything that exists now
GRANT USAGE ON SCHEMA app TO app_ro, app_rw;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_ro;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_rw;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_rw;

-- Prospective: everything app_owner creates from now on
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO app_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT USAGE, SELECT ON SEQUENCES TO app_rw;

Three things that are easy to omit:

USAGE ON SCHEMA is a prerequisite for every table privilege beneath it. Without it, table grants exist and are unusable, and the error is the same permission denied — this is the other half of the diagnosis in the previous lesson.

Sequences back every GENERATED or serial column. A role with INSERT on a table and nothing on its sequence cannot insert, and the error names the sequence rather than the table, which sends people looking in the wrong place.

Default privileges are per schema. A rule for app does nothing for a schema added later, so creating a schema is also a permissions task.

What ownership confers

Ownership is not a privilege and cannot be granted with GRANT. The owner of an object may always ALTER it, DROP it, and change its privileges, regardless of what has been granted or revoked. Revoking everything from an owner does not stop them.

This is why the previous lesson’s pattern puts ownership on a NOLOGIN role: the ability to drop a table should not be attached to a credential that an application holds.

-- Move existing objects to the intended owner
REASSIGN OWNED BY old_role TO app_owner;

-- Then the old role can be dropped, once it owns nothing
DROP OWNED BY old_role;
DROP ROLE old_role;

DROP ROLE fails while the role owns anything or holds any privilege, which is a useful safety property and a frequent surprise during decommissioning. REASSIGN OWNED and DROP OWNED are the two statements that clear the way, and they operate per database — they must be run in each database where the role owns something.

Reading an ACL, and what an empty one means

Privileges are stored as an access control list on each object, and \dp or relacl shows them in a compact notation that repays five minutes of study.

app_ro=r/app_owner
│      │ │
│      │ └── granted by app_owner
│      └──── r = SELECT
└─────────── grantee

The letters are the privileges: r read, w update, a insert, d delete, D truncate, x references, t trigger, U usage, C create, c connect, T temporary, X execute. A grantee of =r/owner with nothing before the equals sign means PUBLIC.

Two states are worth distinguishing because they look similar and are not.

An empty ACL — relacl is null — means privileges have never been altered, so the object has its default privileges: the owner has everything, and nobody else has anything except whatever PUBLIC gets by default for that object type. It does not mean “no access”.

A non-empty ACL that omits a role means privileges have been explicitly managed and that role was not included. That is a decision somebody made.

The distinction matters when auditing. A schema full of null ACLs has never had its permissions considered, which is a different finding from a schema whose ACLs deliberately exclude a role, and the remediation differs accordingly.

PUBLIC deserves particular attention. Every role is implicitly a member of it, so a privilege granted to PUBLIC is granted to everyone, including roles created afterwards. PostgreSQL 15 changed the most consequential default here: CREATE on the public schema is no longer granted to PUBLIC, which closed a long-standing hazard where any role could create objects in a shared schema.

Production discipline

Pair every GRANT ON ALL with an ALTER DEFAULT PRIVILEGES. The first covers the past, the second the future.
Always write FOR ROLE, naming the role that actually creates objects. Omitting it records the rule for whoever ran the statement, which is usually not the migration role.
Audit pg_default_acl against your deployment pipeline. A creating role missing from that list is a future incident.
Grant USAGE ON SCHEMA and sequence privileges too. Both produce permission denied errors that point somewhere unhelpful.
Keep ownership on a NOLOGIN role. The owner can always drop the object, whatever has been revoked.
Use REASSIGN OWNED and DROP OWNED per database when decommissioning a role, since DROP ROLE refuses while anything is owned.

Cross-course references

Git, CI/CD & GitOps — Part CVI (Change management) covers making a migration’s permission consequences part of its review, which is where the default-privileges gap is cheapest to catch.
Secrets, PKI & Certificate Management — Part I (Foundations) covers blast-radius reasoning, which is the argument for separating ownership from the credentials an application holds.
Ansible for Production Sysadmins — Part XII (Idempotency) covers expressing these grants as a desired state rather than as one-time statements.