On , I learnt ...

The correct sequence of SQL statements for dropping a role

Suppose you create a PostgreSQL user with read-only privileges as follows:

-- Create the new role.
CREATE ROLE :username WITH LOGIN PASSWORD :'password';

-- Connect to database in question:
\c :database_name

-- Act as table owner.
SET ROLE :table_owner;

-- Grant read-only access to the new role.
GRANT CONNECT ON DATABASE :database_name TO :username;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO :username;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO :username;

-- Revoke default privileges.
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO :username;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO :username;

then the appropriate sequence of statements for dropping this user is:

-- Connect to database in question:
\c :database_name

-- Act as table owner.
SET ROLE :table_owner

-- Revoke all permissions from current database.
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM :username;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM :username;
REVOKE ALL ON DATABASE :database_name FROM :username;

-- Revoke default privileges.
ALTER DEFAULT PRIVILEGES FOR ROLE :table_owner IN SCHEMA public REVOKE ALL ON TABLES FROM :username;
ALTER DEFAULT PRIVILEGES FOR ROLE :table_owner IN SCHEMA public REVOKE ALL ON SEQUENCES FROM :username;

-- Drop role.
DROP ROLE IF EXISTS :username;

These statements largely mirror the creation statements although the dropping of default privileges requires a reference to the “target role” that granted these permissions in the first place. That was the bit that was tripping me up.

PostgreSQL docs for ALTER DEFAULT PRIVILEGES