The analytics team had a read-only role, and it had worked for a year. Dashboards queried whatever they wanted, nobody could write anything, and the permissions were considered done — until a Monday morning when every dashboard against the new charge_events table failed with the same line: ERROR: permission denied for table charge_events, SQLSTATE 42501. The table had been created Saturday by the migration pipeline. The GRANT SELECT ON ALL TABLES IN SCHEMA public that "gave analytics read access" had last run the previous quarter, and it had covered exactly the tables that existed that quarter. Nobody had lied; the database had simply done what it was told, twice: it granted on all tables, meaning all tables that exist right now, and it applied default privileges for the role we happened to name — which was not the role that creates tables.
This is the most common permissions bug I see in PostgreSQL shops, and it survives code review because the failure is delayed: everything works until the next CREATE TABLE. Here is the model that actually holds up, the audit queries that find drift, and the two-role pattern we now standardize on.
Why do GRANTs stop working on newly created tables?
Because a GRANT is a one-time operation on a fixed list of objects, and no form of GRANT ... ON ALL TABLES reaches into the future — access to objects that do not exist yet is governed by a separate mechanism, ALTER DEFAULT PRIVILEGES, and the two are completely independent. The part that bites is not the mechanism, it is the assumption: a sentence like "we granted read access on the schema" feels permanent, and it is really a snapshot. Every CREATE TABLE, CREATE SEQUENCE, and CREATE FUNCTION afterward starts life with default privileges, which means the owner can do everything and everyone else can do nothing. If your permissions model is a quarterly GRANT script, your real security posture is whatever happened since the script last ran, and the failure always surfaces as a broken dashboard on a Monday rather than as anything the migration pipeline notices.
How does ALTER DEFAULT PRIVILEGES actually work?
It attaches a grant template to a creator role — optionally narrowed to a schema — so that every object that role creates from then on receives the listed privileges automatically. The load-bearing word is creator: default privileges are keyed on the role that will own the new objects, and the most common real-world failure is setting them for the wrong one. In our incident, someone had run ALTER DEFAULT PRIVILEGES as the application runtime role, but tables were created by the migration role, so the template sat attached to a role that never created anything while the migrator's tables came out locked down. The correct form names the creating role explicitly:
-- Run as a role with rights over the creator's future objects
ALTER DEFAULT PRIVILEGES FOR ROLE app_migrator
IN SCHEMA public
GRANT SELECT ON TABLES TO app_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE app_migrator
IN SCHEMA public
GRANT SELECT, USAGE ON SEQUENCES TO app_readonly;Sequences are the second silent half of this trap: a read-only role that can SELECT a table but not its sequences will fail the moment a query touches nextval or a DEFAULT that calls it — or, more commonly, when the write role's grants were copied without the sequence line and inserts start failing with permission denied for sequence. The table itself is introspectable in psql with \ddp, and the catalog behind it is pg_default_acl, keyed by defaclrole — the creator — which is exactly the column to audit. One more nuance worth knowing before it surprises you: ALTER DEFAULT PRIVILEGES changes only what future objects get; it does nothing to objects already created, so adopting it is always a two-step — fix the past with a bulk GRANT, then fix the future with default privileges.
What does a sane PostgreSQL role model look like?
The pattern that survives staff turnover is three roles, not fifty: a NOLOGIN owner role that owns every object, a migration role whose only power is membership in the owner, and runtime roles with exactly the verbs they need. The owner being NOLOGIN matters — it cannot be used as a backdoor login, and ownership stops being tied to whichever human ran the first CREATE TABLE. The migration role connects and immediately SET ROLE to the owner, so every object it creates is owned by the owner role and matches the default-privileges template attached to that role. Runtime read-only access on PostgreSQL 14 and later has a shortcut worth using: the predefined pg_read_all_data role grants SELECT on everything, present and future, without any default-privileges wiring at all — we use it for exactly the analytics case from the incident, with pg_write_all_data as its write counterpart where appropriate. The caveat that keeps people honest: pg_read_all_data is broad by design, so it answers "read-only reporting" and nothing narrower; least-privilege tenants still need the explicit GRANT plus default-privileges pattern.
Two operational interactions deserve a sentence each. Under transaction-mode connection pooling, SET ROLE and session-level state do not survive statement boundaries, so the migration pattern of "connect, SET ROLE, create" must run through a session-mode or direct connection — the failure modes are catalogued in the PgBouncer transaction mode notes. And permissions changes belong in the same review as the DDL itself; the migration review checklist is the right place to hang "does this migration create objects, and are they owned and granted by the roles the templates expect?"
How do you audit privilege drift before users find it?
You compare three things: what default-privilege templates exist, who actually owns the tables, and what the runtime roles can currently reach. The ownership audit is the highest signal — any table not owned by the owner role is a template miss waiting to happen:
SELECT n.nspname AS schema, c.relname, pg_get_userbyid(c.relowner) AS owner
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p', 'S')
AND n.nspname = 'public'
AND pg_get_userbyid(c.relowner) <> 'app_owner'
ORDER BY c.relkind, c.relname;The templates themselves are one catalog read: SELECT * FROM pg_default_acl shows every FOR ROLE rule in effect, and an empty result on a cluster that "has read-only access handled" is the incident from the first paragraph, photographed in advance. For the runtime check, has_table_privilege('app_readonly', 'public.charge_events', 'SELECT') answers "can they read it" without logging in as them, and we run it in CI after migrations against a sentinel list of roles. The remediation for drift is mechanical: bulk GRANT across the schema to repair the present, ALTER DEFAULT PRIVILEGES FOR ROLE to repair the future, and — the step everyone skips — reassign or fix ownership of the stray tables, because an object owned by a departed contractor's role is a DROP ROLE incident scheduled for their offboarding day.
Watching permission failures with MonPG
Permission bugs announce themselves in application logs as 42501 errors, but their database-side shape is quieter: objects appearing with unexpected owners, roles accumulating grants nobody remembers, and the slow drift between the templates you think exist and the ones pg_default_acl actually holds. MonPG tracks the operational side of PostgreSQL — connections by role, statement latency, error-adjacent counters — as part of its PostgreSQL monitoring, so the Monday-morning symptom shows up against the Saturday-morning deploy line on one screen. Permissions are schema, and schema deserves the same treatment as data: versioned, reviewed, audited, and never assumed.