3. Access drift
On this page
Bob joins reporting and Alice moves to another team. The role hierarchy makes the intended change obvious: add Bob to orders_reader, remove Alice. The database has a longer memory.
Chapter 3 · Drift
PostgreSQL 18.3 · PGlite ↗Bob joins the team; Alice leaves it
Membership makes the team change look simple—until the direct grants you created earlier defeat the offboarding test.
Chapter 3 · Drift
PG 18.3Step 1 of 4
Every Run starts from this step’s prepared database. Changes from previous runs are discarded.
Step 1
Change the team
The intended rule is now “Bob is an orders reader; Alice is not.” Membership expresses that change in two edges.
Add Bob and remove Alice from orders_reader.
Editable SQL · disposable browser database · changes are discarded after the run
PostgreSQL output
PostgreSQL’s rows, command tags, or exact error will appear here.
How the browser lab models roles
The selector sets session authorization inside an isolated PGlite database. Statements run one at a time and autocommit, like psql: execution stops at the first error, and earlier statements keep their effect. PostgreSQL performs ordinary role, ownership, schema, and object checks; the diagram comes from catalog privilege queries after your SQL. This is not a password, CONNECT, pg_hba.conf, or concurrent-session test.
Desired state turns the surprise into a plan
Merge Bob's role into chapter 2's policy and replace its orders_reader member list with the one below. Retain its other roles and grants. Alice remains a login role, but is no longer a member and has no direct grants in the complete policy:
roles:
- name: bob
login: true
memberships:
- role: orders_reader
members:
- name: bob
- name: reporting_app
An authoritative pgroles plan compares that graph with PostgreSQL. Alice’s old USAGE and SELECT appear as revocations instead of remaining invisible history. Review the exact SQL, then apply it as one transaction.
Revoking one edge proves only that the edge is gone. Verify that this role can no longer perform the forbidden operation—in this story, Alice selecting from app.orders. A failed report does not prove Alice lacks every other database capability.
Download the complete policy after this chapter.
PGlite can prove the authorization result, but it does not model passwords, pg_hba.conf, concurrent sessions, or session termination. In production, revoke durable authorization, terminate sessions when required, and verify both. Netchecks can run exactly these positive and negative access assertions continuously from inside your cluster.