6. Offboarding an owner

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.3

Step 1 of 2

Every step seeds its own database — jump anywhere

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.

Run as

DROP ROLE 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

Omitting an owned role from desired state says where you want to end. A retirement declares how pgroles can reach that state safely:

pgroles.yamlpolicy schema

SectionFieldRole / profile / settingObjectPrivilege / typeUnknown

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.

The browser cannot prove session termination

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.

Guide

Advanced: membership mechanics

Control automatic inheritance, SET ROLE, and delegated administration.

Open guide

Guide

Role retirement reference

Review retirement fields and executor requirements.

Open guide