In PostgreSQL, why do tables created by a migration login stay unreadable to analysts after an admin ran ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES?
answer
- whose future objects, exactly
- FOR ROLE defaults to yourself
- not inherited from group roles
- psql \ddp and pg_default_acl
- existing tables need a separate GRANT
basics
~20 sALTER DEFAULT PRIVILEGES applies only to objects later created by the role named in FOR ROLE, which defaults to whoever ran it. Tables the migration login creates use that login's own entries, so the admin's entry never fires.
solid answer
~40 sDefault privileges are stored per creating role in `pg_default_acl`, and when an object is created PostgreSQL applies only the entries of the current role, never those of roles it is a member of. Without `FOR ROLE`, the admin's statement registered defaults for the admin, so tables created by `hr_migrator` get nothing beyond the owner's rights. The fix is to choose one creator: have migrations run `SET ROLE hr_owner` and register `ALTER DEFAULT PRIVILEGES FOR ROLE hr_owner IN SCHEMA hr GRANT SELECT ON TABLES TO hr_analysts`. Then backfill the existing tables with `GRANT SELECT ON ALL TABLES IN SCHEMA hr TO hr_analysts`, because defaults never touch objects that already exist, and make sure the analysts have `USAGE` on schema `hr`. psql's `\ddp` shows which creator each entry belongs to.
code
sql · 9 lines-- run as hr_owner (a migration after SET ROLE hr_owner) or as a superuser
ALTER DEFAULT PRIVILEGES FOR ROLE hr_owner IN SCHEMA hr
GRANT SELECT ON TABLES TO hr_analysts;
ALTER DEFAULT PRIVILEGES FOR ROLE hr_owner IN SCHEMA hr
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO hr_app;
-- defaults never reach existing objects or the schema itself
GRANT USAGE ON SCHEMA hr TO hr_analysts, hr_app;
GRANT SELECT ON ALL TABLES IN SCHEMA hr TO hr_analysts;go deeper
Remember that ALTER DEFAULT PRIVILEGES affects only future objects, and that existing tables need GRANT ... ON ALL TABLES IN SCHEMA.
Explain the creator rule: entries belong to the role named in FOR ROLE, default to yourself, and are not inherited by members who create objects.
Diagnose with \ddp and table owners, then fix ownership so one NOLOGIN role creates everything, with defaults, backfill and schema USAGE in place.
Weigh a single-creator ownership model with explicit defaults against broad predefined roles, given data sensitivity and how many teams deploy schema changes.
## What a default-privilege entry is `ALTER DEFAULT PRIVILEGES` stores a rule in the `pg_default_acl` catalog: for objects of one class (`defaclobjtype`: tables, sequences, functions, types, schemas or large objects) created **by one role** (`defaclrole`), optionally only inside one schema (`defaclnamespace`), add these privileges (`defaclacl`). It never touches objects that already exist, and it is per database: an entry applies to objects created in the database where the command ran. The column that bites in practice is `defaclrole`. With no `FOR ROLE` clause, the target is the role running the command. ## The creator rule When `CREATE TABLE` runs, PostgreSQL: 1. takes the **current role**, meaning the session's login or whatever role `SET ROLE` switched to; 2. looks up that role's global entry for tables and its entry for the table's schema; 3. adds their union to the new table's access-control list, next to the owner's own rights. Entries belonging to roles that the current role is merely a member of are not consulted. The reference page is explicit that default privileges are not inherited from roles the current role belongs to. ## Diagnosing the HR case The admin, connected as `postgres`, ran `ALTER DEFAULT PRIVILEGES IN SCHEMA hr GRANT SELECT ON TABLES TO hr_analysts`. That created an entry for `postgres`. The migration tool connects as `hr_migrator` and creates tables as itself, so no entry matches. Two quick checks show it: - psql's `\ddp` lists every entry with its owner, schema and object type, and only `postgres` appears; - `pg_tables.tableowner` for the new tables says `hr_migrator`. The second check exposes a related smell: ownership scattered across whichever login happened to run each deploy. ## The fix, in order 1. **Choose one creator.** Make `hr_owner` a `NOLOGIN` role that owns schema `hr`, and start every migration with `SET ROLE hr_owner`. 2. **Register defaults for that creator**: `ALTER DEFAULT PRIVILEGES FOR ROLE hr_owner IN SCHEMA hr GRANT SELECT ON TABLES TO hr_analysts;` plus the DML grant for the application role. The command must run as `hr_owner` itself, as a role that inherits `hr_owner`'s privileges, or as a superuser; otherwise it fails with `permission denied to change default privileges`. 3. **Backfill what exists**: `GRANT SELECT ON ALL TABLES IN SCHEMA hr TO hr_analysts;` and move stray tables with `ALTER TABLE ... OWNER TO hr_owner`. 4. **Grant the schema**: `GRANT USAGE ON SCHEMA hr TO hr_analysts;`. Table defaults say nothing about the schema they live in. 5. **Cover the other classes you need**: `ON SEQUENCES` and `ON FUNCTIONS` are separate entries; a table entry does not reach them. ## Proving the fix A cheap test before the next real deploy: in a transaction, `SET ROLE hr_owner`, create a throwaway table such as `hr.probe`, and run psql's `\dp hr.probe`. The access privileges column should now include an item like `hr_analysts=r/hr_owner`, meaning `SELECT` granted by the owner through the default entry. Roll the transaction back and nothing remains. If the item is missing, the creator in that session was not the role the entry names. ## Global versus per-schema entries | Entry | Effect | |---|---| | without `IN SCHEMA` | the creator's global defaults for that object class | | with `IN SCHEMA hr` | privileges **added** to the global defaults for objects created in `hr` | Per-schema entries can only add. The built-in defaults grant `EXECUTE` on new functions to `PUBLIC`; `ALTER DEFAULT PRIVILEGES IN SCHEMA hr REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC` therefore has no effect unless it undoes an earlier per-schema grant. The form that works is the global one: `ALTER DEFAULT PRIVILEGES FOR ROLE hr_owner REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC`. ## What default privileges do not do - They do not change existing objects. - They do not follow membership: only the creating role's entries count. - They do not cover a creator you did not name, so a hotfix table created by a personal login escapes them. - They create a dependency on the role: a role that has entries cannot be dropped until `DROP OWNED BY` removes them or the entries are reversed. An alternative for pure readers is the predefined role `pg_read_all_data`, which needs no defaults at all because it is checked at access time, at the price of covering every table in the cluster. For an HR product with salary data that is usually too broad, which is why the single-creator pattern with explicit defaults is the common answer.
- Why does ALTER DEFAULT PRIVILEGES IN SCHEMA hr REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC leave new PostgreSQL functions executable by everyone?Per-schema entries are added to the global defaults and cannot subtract from them. The built-in global default grants `EXECUTE` to `PUBLIC`, so the per-schema revoke only undoes a matching per-schema grant. Revoke globally instead: `ALTER DEFAULT PRIVILEGES FOR ROLE hr_owner REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC`.
- The migration login holds hr_owner WITH INHERIT FALSE, SET TRUE. How can it register default privileges for hr_owner?Running `ALTER DEFAULT PRIVILEGES FOR ROLE hr_owner` directly is refused, because the check requires having `hr_owner`'s privileges through inheritance. The migration can `SET ROLE hr_owner` first and then run the command without `FOR ROLE`, which targets the current role, `hr_owner`.
- ALTER DEFAULT PRIVILEGES applies to every table created in the schema, by anyone.
- Default privileges set for a group role apply to tables its members create.
- ALTER DEFAULT PRIVILEGES also grants on the tables that already exist.
- A table default grant also gives USAGE on the schema.
- A per-schema REVOKE can remove a privilege the global defaults grant.