A reporting role was granted SELECT on every table in a schema, and a week later new tables in that same schema are unreadable to it. Why does that happen, and how is it normally handled?

## Why bulk grants do not persist The privilege system stores an access control list per object. There is no data structure meaning 'this role may read anything that ever appears in this schema' -- the schema-level privilege only governs name resolution within the namespace, not the contents. So a bulk grant is expanded at execution: the engine resolves the object list once and writes one privilege entry per object. That is why the behaviour looks like a bug and is not one. The grant did exactly what it said, for the objects that existed when it ran. ## The creator's implicit rights A newly created object has an owner (normally the role that created it) with implicit full rights, and nothing else. Every other access must be granted. This is why the failure mode is asymmetric: the migration user and any superuser-equivalent see the new table immediately, while the reporting role does not -- so the problem is usually discovered by a downstream consumer, hours or days later, rather than by the person who ran the DDL. ## Remedy 1: default privilege rules Engines commonly provide a mechanism that says: 'when role X creates an object of type T in schema S, automatically apply these grants'. It is the cleanest fix, and it has one sharp edge worth naming in an interview: **the rule is keyed on the creating role**, not on the schema alone. Consequences: - If tables are usually created by the migration user but someone creates one manually as themselves, the new table has no grants. - If the deployment pipeline's database identity changes -- a new service account, a rotated credential mapped to a different role -- every subsequently created object silently misses its grants. - The rules must be declared per object type; tables, sequences and routines are separate declarations, and forgetting sequences is a classic cause of an INSERT failing while a SELECT works. ## Remedy 2: grants in the migration Treat access as part of the schema definition: the change set that creates a table also contains the grants for it. Advantages: fully explicit, versioned, reviewable, and immune to the creator-identity trap. Disadvantages: it is a human convention, so it decays unless enforced by review checklist or an automated check that fails CI when a new object appears without grants. ## Remedy 3: stable indirection and reconciliation Two complementary patterns: - **Indirection.** Consumers read through views (or a dedicated reporting schema) owned by a single role. New base tables do not need consumer grants at all -- only the curated view does, and adding it is a deliberate act. This also gives you column and row shaping for free. - **Reconciliation.** A scheduled job compares actual privileges against the intended model and either reports drift or re-applies it. This is the safety net that catches whatever the other mechanisms missed, and it doubles as the access-review artefact auditors ask for. ## Choosing For an application's own service accounts, migration-time grants are usually right: the access model is small and belongs with the schema. For broad consumers such as analytics roles, default privileges plus a drift check scale better because the object set changes constantly. The strongest answer says that whichever mechanism you choose, the failure is *silent*, so a detection mechanism is not optional -- alert on 'objects in schema S with no grant to the expected consumer role'. ## Related gotcha The mirror-image case is a bulk REVOKE: it also applies only to objects existing at the time, so revoking 'all tables in a schema' does not stop the role reading a table created after the revoke. Any offboarding or lockdown procedure that relies on a bulk revoke needs the same follow-up.