Learn PostgreSQL Roles
3. Access drift
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 step seeds its own database — jump anywhereStep 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
The policy already says Alice is no longer a member and contains no direct Alice grants:
roles:
- name: alice
login: true
- name: bob
login: true
- name: reporting_app
login: true
- name: orders_reader
grants:
- role: orders_reader
privileges: [USAGE]
object: { type: schema, name: app }
- role: orders_reader
privileges: [SELECT]
object: { type: table, schema: app, name: orders }
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. Test the operation to prove effective access is gone.
Negative tests belong in offboarding
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.