ALTER DEFAULT PRIVILEGES: why new tables skip your grants

A GRANT covers only the objects that exist when you run it. Tables created later start with default privileges. ALTER DEFAULT PRIVILEGES sets what future objects get, but only for objects created by the role named in FOR ROLE, or the current role if you name none. That detail causes most surprises.

The problem

You grant SELECT on every table in a schema to a reporting role. A week later a migration creates a new table. The reporting role cannot read it, and someone fixes that with a broad grant to make the error go away. Over time the grants drift toward too much.

The statement

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema
  GRANT SELECT ON TABLES TO reporting;

This changes nothing on existing objects. It sets the privileges applied to tables created in the future, in that schema, by the current role. Without IN SCHEMA, it applies to all future objects of that kind in the current database.

Whose objects does it cover

The manual says defaults apply to objects created by the target role, "or the current role if unspecified." At creation time, only the current role's defaults apply, not those of roles it belongs to. So if migrations run as deploy, you need:

ALTER DEFAULT PRIVILEGES FOR ROLE deploy IN SCHEMA myschema
  GRANT SELECT ON TABLES TO reporting;

Setting defaults while logged in as yourself and then wondering why deploy tables are missing is the classic mistake.

Other rules

• Supported object types include schemas, tables (and views), sequences, functions, types and large objects.
• Per-schema defaults add to global defaults. A per-schema REVOKE only reverses an earlier per-schema GRANT, so you cannot subtract a global grant that way.
• IN SCHEMA is not allowed for schemas and large objects.
• In psql, \ddp shows existing default privilege settings.
• Before dropping a role that has altered defaults, reverse the changes or use DROP OWNED BY.

A tidy approach

1. Pick one migration role that owns all objects.
2. Set defaults for that role per schema, for each reader or writer role.
3. Keep reader roles to SELECT, and writer roles to the minimum.
4. Check \ddp after setup and again in review.

Pair this with the schema rules in the public schema post so new objects land only where you expect.

Quick check

Sources

• PostgreSQL documentation: ALTER DEFAULT PRIVILEGES

Last reviewed: 19 October 2026.