Postgres DROP ROLE Helper

DROP ROLE fails far more often than it succeeds, because a PostgreSQL role is referenced from three different places: objects it owns, privileges it was granted, and default privileges someone set on its behalf - and all three are recorded per database. The error message names a count, not a list, so people delete grants at random until it works. This generator writes the dependency audit first, then the per-database REASSIGN OWNED and DROP OWNED script in the order that actually clears the dependencies, then the final DROP ROLE, plus a decoder for each DETAIL line you can get back. Everything runs in your browser; no connection details are sent anywhere.

Role to dropNew ownerStrategy

Databases (comma separated)

Lock out and terminate the role's sessions firstInclude role membership cleanup

Step 1 - audit the dependencies

-- 1. Does the role exist, and can it log in?
SELECT rolname, rolcanlogin, rolsuper, rolcreatedb, rolreplication
FROM pg_roles WHERE rolname = 'app_legacy';

-- 2. Which databases hold objects that depend on the role?
--    Run this ONCE from any database: pg_shdepend is a shared catalog.
SELECT d.datname AS database,
       s.deptype,
       count(*) AS objects
FROM pg_shdepend s
JOIN pg_roles r      ON r.oid = s.refobjid
LEFT JOIN pg_database d ON d.oid = s.dbid
WHERE r.rolname = 'app_legacy'
GROUP BY 1, 2
ORDER BY 1, 2;
-- deptype 'o' = the role OWNS the object, 'a' = the role only has privileges ON it.
-- A NULL database means a shared object (a database, tablespace or another role).

-- 3. Inside ONE database, list the exact objects it owns.
--    Repeat this in every database the previous query reported.
SELECT n.nspname AS schema, c.relname AS name,
       CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view'
                      WHEN 'm' THEN 'matview' WHEN 'S' THEN 'sequence'
                      WHEN 'i' THEN 'index' WHEN 'p' THEN 'partitioned table'
                      WHEN 'f' THEN 'foreign table' ELSE c.relkind::text END AS kind
FROM pg_class c
JOIN pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE r.rolname = 'app_legacy'
ORDER BY 1, 3, 2;

-- 4. Schemas, functions and default privileges are separate catalogs.
SELECT nspname AS schema_owned FROM pg_namespace n
JOIN pg_roles r ON r.oid = n.nspowner WHERE r.rolname = 'app_legacy';

SELECT n.nspname, p.proname FROM pg_proc p
JOIN pg_roles r ON r.oid = p.proowner
JOIN pg_namespace n ON n.oid = p.pronamespace WHERE r.rolname = 'app_legacy';

-- Default privileges (\ddp in psql) are the usual cause of a DROP ROLE that
-- still fails after you reassigned everything you can see:
SELECT n.nspname, d.defaclobjtype, d.defaclacl
FROM pg_default_acl d
JOIN pg_roles r ON r.oid = d.defaclrole
LEFT JOIN pg_namespace n ON n.oid = d.defaclnamespace
WHERE r.rolname = 'app_legacy';

-- 5. Shared objects REASSIGN OWNED cannot fix from inside a database:
SELECT datname FROM pg_database d JOIN pg_roles r ON r.oid = d.datdba WHERE r.rolname = 'app_legacy';
SELECT spcname FROM pg_tablespace t JOIN pg_roles r ON r.oid = t.spcowner WHERE r.rolname = 'app_legacy';

Step 2 - fix and drop

-- Run as a superuser (or a role that is a member of both roles).
-- Connect to EACH database separately: REASSIGN OWNED and DROP OWNED only
-- act on the database you are currently connected to.

-- Stop the role from opening new sessions and close the ones it has.
ALTER ROLE app_legacy NOLOGIN;
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'app_legacy' AND pid <> pg_backend_pid();

-- ===== database: appdb =====
\connect appdb
REASSIGN OWNED BY app_legacy TO app_owner;   -- moves ownership, keeps every object
DROP OWNED BY app_legacy;                   -- now only removes GRANTs + default privileges

-- ===== database: reporting =====
\connect reporting
REASSIGN OWNED BY app_legacy TO app_owner;   -- moves ownership, keeps every object
DROP OWNED BY app_legacy;                   -- now only removes GRANTs + default privileges

-- Role memberships live in a shared catalog; clear them from any database.
-- Roles this role is a member OF:
SELECT 'REVOKE ' || quote_ident(g.rolname) || ' FROM ' || quote_ident(m.rolname) || ';'
FROM pg_auth_members am
JOIN pg_roles g ON g.oid = am.roleid
JOIN pg_roles m ON m.oid = am.member
WHERE m.rolname = 'app_legacy' OR g.rolname = 'app_legacy';
-- Run the statements that query prints, then continue.

-- Shared objects must be handled explicitly (REASSIGN OWNED does not reach them):
-- ALTER DATABASE some_db OWNER TO app_owner;
-- ALTER TABLESPACE some_ts OWNER TO app_owner;

-- Finally:
DROP ROLE app_legacy;

Still failing? Decode the DETAIL line

-- DROP ROLE still fails? Read the DETAIL line: it names the database.
--   DETAIL: 3 objects in database reporting
-- That means you have not run the block above in 'reporting' yet. Connect to
-- that exact database and run REASSIGN OWNED / DROP OWNED there.

--   DETAIL: privileges for table public.orders
-- The role no longer owns anything, but still holds a GRANT. DROP OWNED BY
-- removes those grants inside the current database - run it everywhere.

--   DETAIL: owner of default privileges on new relations belonging to role x
-- Someone ran ALTER DEFAULT PRIVILEGES FOR ROLE x. DROP OWNED BY x clears it,
-- or undo it explicitly, connected to the right database and schema:
-- ALTER DEFAULT PRIVILEGES FOR ROLE app_legacy IN SCHEMA public REVOKE ALL ON TABLES FROM PUBLIC;

-- Verify nothing is left before you retry:
SELECT d.datname, s.deptype, count(*)
FROM pg_shdepend s
JOIN pg_roles r ON r.oid = s.refobjid
LEFT JOIN pg_database d ON d.oid = s.dbid
WHERE r.rolname = 'app_legacy'
GROUP BY 1, 2;
-- Zero rows = DROP ROLE will succeed.

Notes and warnings

REASSIGN OWNED BY moves ownership of every object in the current database to the new owner. No data is touched, no object is dropped - it is a catalogue update, so it is fast.

Order matters. DROP OWNED BY after REASSIGN OWNED BY is not redundant: REASSIGN transfers ownership but leaves the role's GRANTs and its ALTER DEFAULT PRIVILEGES entries behind, and those alone are enough to block DROP ROLE.

Both statements are per-database. pg_shdepend is shared, so the audit query finds every database at once, but the fix has to be re-run inside each one. A role that owns nothing but holds a SELECT grant in a database you forgot will still block the drop.

Objects owned in databases you cannot connect to (for example a template database) have to be handled there too. Databases and tablespaces owned by the role need an explicit ALTER ... OWNER TO.

If the role is used by a running application, ALTER ROLE ... NOLOGIN first and watch for errors before you drop anything - dropping is not reversible, and recreating the role gives it a new OID, so grants do not come back.

You need to be a superuser, or a member of both the old role and the new owner, to run REASSIGN OWNED BY.

Do more than postgres drop role helper — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Enter the role you want to drop and the role that should inherit its objects, then list every database that role touches.
Run the audit queries first - the pg_shdepend query tells you exactly which databases still hold dependencies, including ones you forgot about.
Run the generated script database by database, then DROP ROLE; if it still fails, match the DETAIL line against the troubleshooting panel.

Frequently asked questions

Why does Postgres say "role cannot be dropped because some objects depend on it"?

Because the role is still referenced in the shared pg_shdepend catalogue. There are three separate ways that happens. First, ownership: the role owns a table, view, sequence, function or schema, and dropping it would leave that object without an owner. Second, privileges: someone ran GRANT SELECT ON orders TO the_role, so the role appears in an ACL even though it owns nothing. Third, default privileges: someone ran ALTER DEFAULT PRIVILEGES FOR ROLE the_role, which leaves an entry in pg_default_acl that survives long after the objects are gone. The DETAIL line under the error tells you which kind you hit and, crucially, which database - dependencies are recorded per database, so a role can look clean in the database you are connected to and still be blocked by objects in another one.

What is the difference between REASSIGN OWNED BY and DROP OWNED BY?

REASSIGN OWNED BY old_role TO new_role changes the owner of every object the old role owns in the current database. Nothing is deleted and no data is rewritten; it is a catalogue update that finishes quickly even on large schemas. DROP OWNED BY old_role does two very different things depending on when you run it: if the role still owns objects, those objects are dropped, and with CASCADE anything depending on them goes too. If you have already reassigned, the role owns nothing, so DROP OWNED only removes the role's GRANTs and its default-privilege entries - which is exactly the leftover state that blocks DROP ROLE. That is why the safe sequence is REASSIGN OWNED BY first, then DROP OWNED BY, then DROP ROLE. Running DROP OWNED BY first on a production role is how people lose tables.

I ran REASSIGN OWNED and DROP OWNED but DROP ROLE still fails. What now?

Almost always it is another database. Both statements only act on the database you are connected to, while DROP ROLE checks every database at once, so a single leftover GRANT in a reporting replica or an old staging database is enough to block it. Run the pg_shdepend audit query from any database: it lists every database that still references the role, with deptype 'o' for ownership and 'a' for privileges. Connect to each one that appears and repeat the block. A NULL database in that result means a shared object - a database, a tablespace or a role membership - which REASSIGN OWNED cannot reach; fix those with ALTER DATABASE ... OWNER TO, ALTER TABLESPACE ... OWNER TO, or REVOKE for memberships. When the audit query returns zero rows, DROP ROLE will succeed. Chat2DB shows roles, ownership and grants next to the schema tree, which makes the leftover grant easy to spot: download it at https://chat2db.ai/download or use the web version at https://app.chat2db.ai.