Designing the role layer

You have layers 1 and 2. Nobody has a password, every connection is attributable, and offboarding is one group removal. Then someone runs a verification query and finds the development application can read production data.

Nothing in Google Cloud failed. Layer 3 is PostgreSQL, and PostgreSQL has three behaviours that surprise people. Each has cost a real team real time.

Trap 1 — an owner holds DDL unconditionally

The intuitive design is two roles: app_rw for the application, app_ro for read-only humans. Revoke CREATE on the schema so the application cannot create tables, and you are done.

Here is what that misses. Consider how tables usually come to exist: a migration runs, or a dump is restored, as some administrative role. Later, someone reassigns ownership to app_rw — typically because the original role has to be dropped and PostgreSQL will not drop a role that owns anything. app_rw is now the owner of every table. And then:

-- connected as the application, a member of app_rw
ALTER TABLE public.customers ADD COLUMN probe int;
ALTER TABLE   -- it worked

DROP TABLE public.orders;
DROP TABLE    -- so did this

The revoke did nothing, and it never could have. PostgreSQL's documentation is clear that the right to ALTER and DROP an object is inherent in ownership and cannot be granted or revoked. No grant is consulted. Revoking CREATE prevented new objects and left everything already there fully exposed to the role the application logs in as.

Now ALTER and DROP are unreachable by anything that can log in, because the role that holds them has no members who can.

The consequence you must plan for

Schema migrations can no longer run as the application. This is not a gap to patch later — decide it before you cut over. Two workable answers:

• Run migrations as a dedicated DDL role. A LOGIN role, used by nothing but your migration job, made a member of app_owner. The neat trick is ALTER ROLE ddl_role IN DATABASE appdb SET role = app_owner, which makes every session it opens run as app_owner from login. Objects a migration creates are then owned by app_owner directly, with no cleanup step.
• Run migrations manually as an administrative role, then re-run your grants. Simpler, and it does not scale to a team that deploys often.

Trap 2 — roles are cluster-wide, privileges are per-database

This one is the most expensive, because it produces no error and no log line.

A single Cloud SQL instance commonly holds several databases — development and staging together, or several services. You give each its own service account, each its own key, and you have a per-environment boundary.

You do not. In PostgreSQL, pg_authid and pg_auth_members are shared catalogs: roles and their memberships are one set per instance. Privileges are per-database; the roles holding them are not.

Played out as it actually happens:

1. Database A is set up. Its script grants app_rw to service account A. That membership is cluster-wide.
2. Database B is set up later. Its script grants privileges inside B to app_rw.
3. Service account A now holds SELECT and INSERT on database B's data, having been named nowhere in step 2.

SELECT has_table_privilege('app-a@PROJECT_ID.iam',
                           'public.customers', 'SELECT');
 t   -- in database B. Nobody granted this.

The fix: put the database name in the role name

app_owner_appdb, app_rw_appdb, app_ro_appdb. Now a membership can only mean one database, because the privileges the role holds exist in one database.

Derive the suffix inside your bootstrap script from current_database() rather than passing it as an argument. The script already takes its database from the connection, so it cannot be run against the wrong one — let the role names inherit that property. There is then no argument to mistype and no way for the roles to disagree with the database they govern.

DO $$
DECLARE
  db   text := current_database();
  rw   text := format('app_rw_%s', db);
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = rw) THEN
    EXECUTE format('CREATE ROLE %I NOLOGIN', rw);
  END IF;
END $$;

Do this for all three roles, including app_owner. It has no members today, so it cannot leak today — but it is the same cluster-wide shape, one GRANT away from the same failure, and a design whose whole point is that shared role names were the bug should not keep one.

The verification that would have caught it

This is the query worth adding to your setup checklist. Run it in each database and read every column:

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;

The only passing result is one true per row, on the diagonal:

appdb_a   a=true    b=false
appdb_b   a=false   b=true

A true in the wrong column is a principal reaching a database it has no business in. Two false values usually means the grants were never run for that database.

Trap 3 — grants are a snapshot, not a rule

GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_ro_appdb;

That reads like a standing rule. It is not. It grants on the tables that exist at that instant. Every table created afterwards is uncovered, and nothing reports it — you find out when a user hits a new table and cannot read it, long after whoever ran the migration has moved on.

The durable fix is ALTER DEFAULT PRIVILEGES, which is a standing rule:

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner_appdb IN SCHEMA public
  GRANT SELECT ON TABLES TO app_ro_appdb;

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner_appdb IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw_appdb;

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner_appdb IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO app_rw_appdb;

Even with defaults in place, re-run your grants script after every restore. A restore creates objects outside the path your defaults cover. Make the script idempotent so re-running it is free and boring.

The shape that results

Four properties fall out of it, and all four are worth being able to state:

1. No principal that can log in owns anything, so nothing that logs in holds DDL.
2. Role names are per-database, so a membership cannot mean two databases.
3. Privileges reach new tables automatically, because defaults are attached to the single known owner.
4. Adding a person is one GRANT of membership — or nothing at all, if they are in a group that already has it.

Check yourself