(opens in a new tab)

How to Drop a User or Role in PostgreSQL

August 26, 2026 by Chat2DB Team

DROP USER alice; works about half the time. The other half you get role "alice" cannot be dropped because some objects depend on it, followed by a DETAIL line listing privileges in a database you had forgotten existed. This guide explains why that happens and gives you a repeatable procedure that works every time — including the parts that have to be repeated per database.

Users and roles are the same thing

Since PostgreSQL 8.1 there is one object type: the role. CREATE USER is exactly CREATE ROLE ... LOGIN, and DROP USER is a synonym for DROP ROLE. Everything below applies to both keywords interchangeably.

DROP ROLE alice;
DROP USER alice;   -- identical
 
DROP ROLE IF EXISTS alice;   -- no error if it is already gone
DROP ROLE alice, bob, carol; -- several at once

To drop a role you must be a superuser, or have CREATEROLE and be a member of the target role with ADMIN OPTION.

Two different failures

"role is still referenced" — the role owns objects

DROP ROLE alice;
-- ERROR:  role "alice" cannot be dropped because some objects depend on it
-- DETAIL:  owner of table orders
--          owner of sequence orders_id_seq
--          privileges for table customers

PostgreSQL refuses because dropping the role would leave those objects ownerless. Note the two distinct kinds of dependency in the DETAIL: ownership ("owner of table orders") and granted privileges ("privileges for table customers"). They are fixed by different commands.

"role cannot be dropped because it is a member of..." or active sessions

If the role has open sessions you may also need to disconnect it. Revoke the login right first so it cannot reconnect while you work:

ALTER ROLE alice NOLOGIN;
 
SELECT pg_terminate_backend(pid)
FROM   pg_stat_activity
WHERE  usename = 'alice' AND pid <> pg_backend_pid();

The two commands that fix ownership and privileges

REASSIGN OWNED BY alice TO new_owner changes the owner of every object alice owns in the current database to new_owner. It does not touch privileges granted to alice on other people's objects.

DROP OWNED BY alice does two things: it drops every object alice owns in the current database, and it revokes every privilege granted to alice on objects owned by others. The second half is the part people miss, and it is the half that actually clears "privileges for table customers".

The standard, safe sequence is to run both — reassign first so nothing is destroyed, then DROP OWNED to sweep up the remaining grants:

-- In EACH database where alice may have objects or privileges:
REASSIGN OWNED BY alice TO postgres;
DROP OWNED BY alice;

After REASSIGN OWNED, alice owns nothing, so DROP OWNED BY alice has no objects left to destroy and only revokes grants. That is why this order is safe and the reverse order is not — DROP OWNED alone would delete alice's tables along with her permissions.

If you genuinely want the role's objects gone (a temporary test user, say), skip the reassign:

DROP OWNED BY test_user CASCADE;   -- deletes their tables, views, functions

CASCADE here also drops objects in other schemas that depend on alice's objects. Read the notices.

The step everyone forgets: it is per-database

Roles are cluster-wide. Objects and privileges are per-database. REASSIGN OWNED and DROP OWNED only operate on the database you are currently connected to, plus shared objects.

So if alice has a table in analytics and a grant in reporting, running the cleanup in postgres clears neither, and DROP ROLE keeps failing with a DETAIL line that does not tell you which database the object is in.

Loop over every database:

for db in $(psql -qtAX -d postgres -c \
      "SELECT datname FROM pg_database WHERE datallowconn AND datname <> 'template0'"); do
  echo "--- cleaning $db"
  psql -qX -d "$db" -c "REASSIGN OWNED BY alice TO postgres;" \
                    -c "DROP OWNED BY alice;"
done
 
psql -qX -d postgres -c "DROP ROLE alice;"

Or from psql alone, using \gexec to generate the connect commands:

SELECT format('\c %I', datname)
FROM   pg_database
WHERE  datallowconn AND datname <> 'template0';

Finding what the role still owns

Before you reassign, it is worth knowing what you are moving. Objects owned by a role in the current database:

SELECT n.nspname AS schema,
       c.relname AS object,
       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'
         ELSE c.relkind::text END AS type
FROM   pg_class c
JOIN   pg_namespace n ON n.oid = c.relnamespace
WHERE  c.relowner = 'alice'::regrole
ORDER  BY 1, 3, 2;

Functions, schemas and the database itself are separate catalogs:

SELECT 'function' AS type, n.nspname || '.' || p.proname AS name
FROM   pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE  p.proowner = 'alice'::regrole
UNION ALL
SELECT 'schema', nspname FROM pg_namespace WHERE nspowner = 'alice'::regrole
UNION ALL
SELECT 'database', datname FROM pg_database WHERE datdba = 'alice'::regrole;

Privileges granted to the role show up in the ACL columns:

SELECT table_schema, table_name, privilege_type
FROM   information_schema.table_privileges
WHERE  grantee = 'alice'
ORDER  BY 1, 2;

And default privileges — the ones set with ALTER DEFAULT PRIVILEGES — are their own catalog and a classic source of a stubborn dependency:

SELECT pg_get_userbyid(defaclrole) AS granted_by,
       defaclnamespace::regnamespace AS schema,
       defaclobjtype AS object_type,
       defaclacl
FROM   pg_default_acl;

If alice appears in a defaclacl, DROP OWNED BY alice clears it. If alice is the defaclrole, you have to remove it explicitly:

ALTER DEFAULT PRIVILEGES FOR ROLE alice IN SCHEMA public
  REVOKE ALL ON TABLES FROM some_role;

Group memberships

A role that is a member of other roles, or has members of its own, does not block DROP ROLE — memberships are removed automatically. But it is worth inspecting them before you delete, because dropping a group role silently removes everyone's inherited access:

SELECT r.rolname AS role, m.rolname AS member_of
FROM   pg_auth_members am
JOIN   pg_roles r ON r.oid = am.member
JOIN   pg_roles m ON m.oid = am.roleid
WHERE  r.rolname = 'alice' OR m.rolname = 'alice';

If alice is a group like readers with five members, dropping it revokes their read access instantly. Move the members to a replacement group first.

Objects the role owns in other clusters-wide catalogs

Two ownerships are cluster-wide and must be handled explicitly because REASSIGN OWNED in one database will not reach them from another:

-- A database owned by alice
ALTER DATABASE analytics OWNER TO postgres;
 
-- A tablespace owned by alice
ALTER TABLESPACE fast_ssd OWNER TO postgres;

The complete procedure

Putting it together, here is the checklist that works on the first try:

-- 1. Prevent new logins and disconnect existing ones
ALTER ROLE alice NOLOGIN;
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE  usename = 'alice' AND pid <> pg_backend_pid();
 
-- 2. Reassign cluster-wide ownerships (run once, as superuser)
ALTER DATABASE analytics OWNER TO postgres;   -- if applicable
 
-- 3. In EVERY database (see the shell loop above):
REASSIGN OWNED BY alice TO postgres;
DROP OWNED BY alice;
 
-- 4. Finally
DROP ROLE alice;

If step 4 still fails, the DETAIL line names the remaining object. Nine times out of ten it is a database you did not include in step 3 — check pg_database again, including any database with datallowconn = false that you need to temporarily re-enable.

A note on choosing the new owner

Reassigning everything to postgres is the quick answer, but it means every table in your application is owned by a superuser, which weakens your permission model. A better target is a dedicated, non-login group role that already owns the application's schema:

CREATE ROLE app_owner NOLOGIN;
REASSIGN OWNED BY alice TO app_owner;
GRANT app_owner TO deploy_user;   -- humans get in via membership

Then individual people can come and go without any of this ceremony: they are members of app_owner, they own nothing themselves, and DROP ROLE on their personal account succeeds immediately. Setting that up once is the real fix for the problem this article describes.

Managing roles, grants and ownership across several databases is exactly the kind of thing a GUI makes faster — Chat2DB (opens in a new tab) is a free AI-powered SQL client that lists roles, their memberships and object privileges in one panel, and can generate the REASSIGN OWNED / DROP OWNED scripts for you. It also runs in the browser at app.chat2db.ai (opens in a new tab).