Role pattern — Postgres on Google Cloud

Why it is shaped like this

Three PostgreSQL behaviours drive every choice below: an owner's DDL cannot be revoked, roles are cluster-wide while privileges are per-database, and GRANT … ON ALL is a snapshot. The reasoning is lesson 7.

The roles

Role	Login	Holds	Members
`app_owner_<db>`	No	Owns every object. `CREATE` on schemas.	Only the migration role. Nothing that serves traffic.
`app_rw_<db>`	No	`SELECT`, `INSERT`, `UPDATE`, `DELETE`; sequence `USAGE`	The application's service account; developers who write
`app_ro_<db>`	No	`SELECT`	Everyone else — the correct default
`ddl_<db>`	Yes	Nothing directly — it is a member of the owner	The migration job only

The first three are NOLOGIN group roles. Principals never receive privileges directly; they receive membership. That is what makes adding a person one statement and removing them one statement.

1. Create the roles, names derived from the database

DO $$
DECLARE
  db    text := current_database();
  owner text := format('app_owner_%s', db);
  rw    text := format('app_rw_%s',    db);
  ro    text := format('app_ro_%s',    db);
  r     text;
BEGIN
  FOREACH r IN ARRAY ARRAY[owner, rw, ro] LOOP
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = r) THEN
      EXECUTE format('CREATE ROLE %I NOLOGIN', r);
      RAISE NOTICE 'created role %', r;
    END IF;
  END LOOP;
END $$;

Deriving the suffix from current_database() rather than taking it as an argument is the point. The script already gets its database from the connection and cannot be run against the wrong one — the role names now inherit that property. There is no argument to mistype and no way for the roles to disagree with the database they govern.

2. Move ownership off whatever created the objects

After a restore or an initial migration, objects are owned by whichever role ran it. Move them:

DO $$
DECLARE
  owner text := format('app_owner_%s', current_database());
BEGIN
  EXECUTE format('GRANT %I TO CURRENT_USER', owner);
  EXECUTE format('REASSIGN OWNED BY CURRENT_USER TO %I', owner);
END $$;

REASSIGN OWNED only moves what the named role owns

If some objects are owned by a different role — the application's own IAM login role, say, because it once ran a migration — this reports success and moves nothing. Always verify with §6 rather than trusting the statement.

3. Grant privileges on what exists now

DO $$
DECLARE
  db  text := current_database();
  rw  text := format('app_rw_%s', db);
  ro  text := format('app_ro_%s', db);
  s   text;
BEGIN
  FOR s IN
    SELECT nspname FROM pg_namespace
    WHERE  nspname NOT LIKE 'pg\_%' AND nspname <> 'information_schema'
  LOOP
    EXECUTE format('GRANT USAGE ON SCHEMA %I TO %I, %I', s, rw, ro);
    EXECUTE format(
      'GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA %I TO %I',
      s, rw);
    EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO %I', s, ro);
    EXECUTE format(
      'GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA %I TO %I', s, rw);

    -- no principal that logs in may create objects
    EXECUTE format('REVOKE CREATE ON SCHEMA %I FROM PUBLIC', s);
  END LOOP;
END $$;

4. Make it apply to objects that do not exist yet

DO $$
DECLARE
  db    text := current_database();
  owner text := format('app_owner_%s', db);
  rw    text := format('app_rw_%s',    db);
  ro    text := format('app_ro_%s',    db);
  s     text;
BEGIN
  FOR s IN
    SELECT nspname FROM pg_namespace
    WHERE  nspname NOT LIKE 'pg\_%' AND nspname <> 'information_schema'
  LOOP
    EXECUTE format(
      'ALTER DEFAULT PRIVILEGES FOR ROLE %I IN SCHEMA %I
         GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO %I',
      owner, s, rw);
    EXECUTE format(
      'ALTER DEFAULT PRIVILEGES FOR ROLE %I IN SCHEMA %I
         GRANT SELECT ON TABLES TO %I', owner, s, ro);
    EXECUTE format(
      'ALTER DEFAULT PRIVILEGES FOR ROLE %I IN SCHEMA %I
         GRANT USAGE, SELECT ON SEQUENCES TO %I', owner, s, rw);
  END LOOP;
END $$;

FOR ROLE is the whole statement

Default privileges attach to the role that creates the object. Omit FOR ROLE and they attach to whoever runs this script, and do nothing for tables the migration role creates later. This only works because §2 arranged for a single known owner.

5. A DDL role for migrations

DO $$
DECLARE
  db    text := current_database();
  owner text := format('app_owner_%s', db);
  ddl   text := format('ddl_%s',       db);
  s     text;
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = ddl) THEN
    EXECUTE format('CREATE ROLE %I LOGIN', ddl);   -- password set out of band
  END IF;

  EXECUTE format('GRANT CONNECT ON DATABASE %I TO %I', db, ddl);
  EXECUTE format('GRANT %I TO %I', owner, ddl);

  -- every session it opens runs AS the owner, from login
  EXECUTE format('ALTER ROLE %I IN DATABASE %I SET role = %I', ddl, db, owner);

  FOR s IN
    SELECT nspname FROM pg_namespace
    WHERE  nspname NOT LIKE 'pg\_%' AND nspname <> 'information_schema'
  LOOP
    EXECUTE format('GRANT CREATE ON SCHEMA %I TO %I', s, owner);
  END LOOP;
END $$;

The SET role line is what makes this clean. A migration connecting as ddl_<db> is immediately acting as app_owner_<db>, so anything it creates is owned by the owner role directly. The default privileges from §4 then apply automatically — no post-migration grants step, and no object ever owned by the role the migration job logs in as.

Blast radius

A leaked ddl_<db> credential holds DDL on one database and nothing on any other — the same per-database boundary the application roles have. Handing migrations an instance-wide administrative password instead would put every database on the instance in scope.

6. Prove it, in every database

-- a. nothing that can log in owns anything
SELECT schemaname, tablename, tableowner
FROM   pg_tables
WHERE  schemaname NOT LIKE 'pg\_%' AND schemaname <> 'information_schema'
  AND  tableowner <> format('app_owner_%s', current_database());
-- expect: zero rows

-- b. who is a member of what
SELECT g.rolname AS role, r.rolname AS member
FROM   pg_auth_members m
JOIN   pg_roles g ON g.oid = m.roleid
JOIN   pg_roles r ON r.oid = m.member
WHERE  g.rolname LIKE 'app\_%' OR g.rolname LIKE 'ddl\_%'
ORDER  BY 1, 2;
-- expect: no login role in app_owner_* except the ddl role

-- c. no legacy instance-wide role still reaches this database
SELECT rolname FROM pg_roles
WHERE  rolname IN ('app_owner', 'app_rw', 'app_ro');
-- expect: zero rows

The diagonal — run in every database

SELECT current_database(),
       has_table_privilege('app-a@PROJECT_ID.iam', t, 'SELECT') AS a,
       has_table_privilege('app-b@PROJECT_ID.iam', t, 'SELECT') AS b
FROM  (SELECT format('%I.%I', schemaname, tablename) AS t
       FROM   pg_tables WHERE schemaname = 'public' LIMIT 1) x;

appdb_a   a=true    b=false
appdb_b   a=false   b=true
-- one true per row, on the diagonal. Anything else is a real finding.
-- a true in the wrong column = cross-environment reach
-- two falses in a row  = grants never run for that database

Check table privilege, never schema usage

PUBLIC holds USAGE on schema public by default, so has_schema_privilege reads true even after a revoke and proves nothing.

Operating rules

The script is idempotent. Re-running it is free and boring — that is what makes people run it.
Re-run it after every restore. A restore creates objects your last grant never covered.
Run §6 as part of it, not as a separate thing someone might skip.
The script lives in version control alongside the infrastructure.
No passwords in it. The DDL role's password is set out of band.
Adding a person is GRANT app_rw_<db> TO "them@example.com"; — or nothing, if a group already holds it.