skip to content

A role has been granted SELECT on every table in a schema, yet it cannot read a table created last week. Why does that happen, and what mechanism makes access apply to objects created in the future?

level: seniorimportance: should knowfreq 34%

answer

  1. ALL TABLES = snapshot at execution, not a rule
  2. default privileges keyed on the CREATING role
  3. FOR ROLE app_owner - or the rule never fires
  4. not retroactive; per object kind (tables vs sequences)
  5. CI check: connect as consumer, assert positives and negatives

basics

~20 s

Granting on all tables in a schema is a one-time bulk operation over the tables that existed at that moment, not a standing rule. New objects carry no grants for non-owners. Default privileges - configured per creating role and schema - attach chosen grants automatically to objects created later.

solid answer

~60 s

`GRANT SELECT ON ALL TABLES IN SCHEMA app TO reporting` is expanded at execution time into one grant per existing table. It creates no rule, so any table created afterwards is invisible to `reporting` until someone grants again. This is the single most common cause of 'it worked in staging, permission denied in production' after a migration. Two durable fixes: 1. **Default privileges** - a standing configuration keyed on *which role creates the object* and *in which schema*: `ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT SELECT ON TABLES TO reporting`. Crucially it applies only to objects created by that role, so if a migration runs as someone else, the defaults do not fire. 2. **Explicit grants in the migration** that creates the object, reviewed in the same diff. I use both: defaults as a safety net, explicit grants for anything sensitive so the access change is visible in code review. Either way I add a CI check that connects as the consuming role and exercises the new object.

code

sql · 12 lines
sql
-- one-time catch-up for objects that already exist
GRANT SELECT ON ALL TABLES IN SCHEMA app TO reporting;

-- standing rule for objects created later BY app_owner
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO reporting;

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT USAGE ON SEQUENCES TO app_rw;

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

go deeper

for a junior

Know that granting on all tables covers only the tables that exist right then, and that new tables need a new grant.

for a middle

Name default privileges as the mechanism for future objects and note that they are not retroactive.

for a senior

Emphasise the creating-role key, per-object-kind configuration including sequences and functions, the combination with explicit grants in migrations, and a CI assertion covering both positive and negative access.

for a principal

Decide the organisational policy: defaults as a safety net versus explicit grants as the reviewable record, how new-object access is approved for sensitive data, and how the configuration is reproduced across environments and restores.

## The misunderstanding The phrase 'ALL TABLES IN SCHEMA' reads like a standing rule and is not one. The engine expands it when the statement runs: for each table currently in the schema, record a grant. The result is N grant rows. Nothing about the schema itself is marked. Create table N+1 tomorrow and it has no grant rows for anyone but its owner. Symptoms are always the same shape: the deploy succeeds, the migration succeeds, and the first request that touches the new table fails with a permission error - typically only in the environment where the grants were applied by hand once, long ago. ## Default privileges The mechanism designed for this is *default privileges*: a stored rule saying 'when role X creates an object of kind K in schema S, automatically grant these privileges to role Y'. The subtlety that trips people up is the **creator key**. The rule is attached to a creating role. `ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT ON TABLES TO reporting` executed by you applies to objects *you* create. If migrations run as `app_owner`, you must write `FOR ROLE app_owner`, and the role executing the statement must be a member of it. Teams frequently configure defaults under a personal account, see it work in their sandbox, and find it silently inert in CI where a different role creates the objects. Other properties worth knowing: - Defaults are **not retroactive**. Existing objects keep whatever grants they have; you still need a one-time bulk grant to catch up. - They are configured per **object kind** - tables (which includes views), sequences, functions, types, schemas - so granting on TABLES does not cover the sequences behind identity columns, a classic cause of 'SELECT works but INSERT fails'. - They can also *revoke* from PUBLIC by default, which is a good way to make new functions non-executable by everyone. - They are per-schema or database-wide; database-wide rules are blunt and easy to forget. ## Explicit grants in migrations The alternative is to put the grants next to the DDL: the same changeset that creates the table also grants SELECT to the reporting role and the DML verbs to the runtime role. Advantages: the access change is visible in the pull request, so a reviewer notices that a new table containing card data just became readable by the reporting role. It is reproducible across environments with no out-of-band state. It fails loudly if a role name is wrong. Disadvantage: it is repetitive, and it will be forgotten at some point. ## What I actually recommend Combine them, with a check: 1. Configure default privileges `FOR ROLE app_owner` as a safety net so a forgotten grant is not an outage. 2. Write explicit grants in migrations for anything with a sensitivity dimension, so the reviewer sees it. 3. Add a CI assertion: connect as each consuming role and verify both positives (can read the tables it must) and negatives (cannot read the schema it must not). This turns the whole scheme from a convention into a test, and it catches over-broad defaults just as well as missing grants. 4. Ensure migrations set the creating role explicitly (`SET ROLE app_owner`) so both ownership and the default-privilege rules apply deterministically regardless of which credential ran the deploy. ## Related failure modes - **Sequences.** With serial/identity columns, `INSERT` also needs `USAGE` on the sequence in some engines. Defaults configured only `ON TABLES` leave this gap. - **Functions.** New functions are typically executable by PUBLIC by default; a defaults rule that revokes `EXECUTE` from PUBLIC on functions is a cheap hardening win. - **Views over new tables.** Granting on a view does not require the grantee to have rights on the base tables (the view's owner's rights are used), which is why curated views are a good way to give stable access while base tables churn - it sidesteps the whole problem for reporting consumers. - **Restores and clones.** A dump/restore may carry object grants but not the default-privilege configuration in the way you expect; verify after cloning an environment. ## The one-line answer 'Bulk grants are a snapshot; default privileges are a rule - and the rule is keyed to who creates the object, so make migrations create objects as a known role.'

  • A team configured default privileges but new tables still have no grants. What is the most likely cause?
    The rule was created without FOR ROLE, so it applies only to objects created by the role that ran the ALTER DEFAULT PRIVILEGES statement - while migrations actually run as a different role, such as the owner or a deploy account. Default privileges are keyed on the creating role, so the fix is to declare the rule FOR ROLE app_owner and to have migrations explicitly create objects as that role.
  • SELECT works on a new table but INSERT fails. What is usually missing?
    Access to the sequence behind an auto-generated key. Default privileges are configured per object kind, so a rule covering TABLES does not cover SEQUENCES; the inserting role needs USAGE (or SELECT/UPDATE, depending on engine) on the sequence. Adding a defaults rule ON SEQUENCES alongside the one for tables closes it.

saying these in an interview costs you the question

  • Believing GRANT ... ON ALL TABLES IN SCHEMA creates a standing rule that covers future tables.
  • Configuring default privileges without FOR ROLE and assuming they apply to objects created by the migration account.
  • Expecting default privileges to retroactively fix existing objects.
  • Covering only TABLES in the defaults and then being surprised that inserts fail on sequence permissions.

context

resolving links…