6. Offboarding an owner
On this page
Priya is leaving Acme. Her application permissions were easy to remove, but one legacy table still belongs to her. PostgreSQL refuses the simple DROP ROLE—and that is the clue you need.
Chapter 6 · Offboarding
PostgreSQL 18.3 · PGlite ↗Priya is leaving Acme
Removing a login is not enough when the role owns database objects. Let PostgreSQL expose the dependency, then retire the owner deliberately.
Chapter 6 · Offboarding
PG 18.3Step 1 of 2
Every Run starts from this step’s prepared database. Changes from previous runs are discarded.
Step 1
Try the obvious DROP ROLE
PostgreSQL refuses to erase a role while objects still depend on it. That refusal protects the database from an incomplete offboarding.
Try to drop Priya.
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.
Encode the retirement, not just the absence
Remove Priya's external role definition from chapter 5's policy and add this retirement. Retain the other roles, grants, memberships, and default privileges. The retirement declares how pgroles can remove her safely:
retirements:
- role: priya
reassign_owned_to: app_owner
drop_owned: true
terminate_sessions: true
pgroles inspects dependencies before applying a drop. The generated sequence can terminate other sessions, reassign owned objects, remove remaining privileges, and finally drop the role. Review this plan carefully: role retirement is deliberately destructive.
Roles are cluster-wide, but cleanup must account for dependencies in every database. Repeat the necessary reassignment and privilege cleanup in each affected database before the final DROP ROLE.
Reassign objects you intend to preserve before running DROP OWNED. DROP OWNED can delete objects still owned by the retiring role; it is not merely privilege cleanup.
PGlite demonstrates ownership dependencies, REASSIGN OWNED, DROP OWNED, and DROP ROLE. It has no pool of authenticated concurrent sessions. In production, verify that old sessions have ended and that the identity provider can no longer authenticate the person.
Offboarding is complete when authentication is disabled, every authorization path is gone, owned objects have a successor, and active sessions are handled.
Download the complete policy after this chapter.