PostgreSQL 2B000 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 2B000: dependent privilege descriptors still exist

PostgreSQL error 2B000 occurs when you attempt to drop or modify a role (user) that still has privilege descriptors associated with it — meaning the role either owns objects, holds granted privileges on database objects, or has granted privileges to other roles. PostgreSQL enforces referential integrity on its privilege system and refuses to remove a role until all dependent privilege information is fully cleaned up. This error is most commonly encountered during user offboarding, environment cleanup, or database migrations.

Top 3 Causes

1. The role still holds privileges on database objects

When a role has been granted SELECT, INSERT, UPDATE, or other privileges on tables, sequences, functions, or schemas, dropping it directly will trigger this error.

-- Check what privileges the role holds
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'target_role';

-- Revoke all object-level privileges
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM target_role;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM target_role;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM target_role;
REVOKE ALL PRIVILEGES ON SCHEMA public FROM target_role;
REVOKE ALL PRIVILEGES ON DATABASE mydb FROM target_role;

2. The role has granted privileges to other roles (grantor relationship)

PostgreSQL tracks who granted what to whom. If the role you are trying to drop is recorded as a grantor in pg_auth_members or object ACLs, the system will block the drop.

-- Check role membership and grantor relationships
SELECT 
    r.rolname  AS role_name,
    m.rolname  AS member,
    g.rolname  AS granted_by
FROM pg_auth_members am
JOIN pg_roles r ON r.oid = am.roleid
JOIN pg_roles m ON m.oid = am.member
JOIN pg_roles g ON g.oid = am.grantor
WHERE r.rolname = 'target_role'
   OR m.rolname = 'target_role';

-- Remove the membership link
REVOKE target_role FROM other_role;

3. The role owns database objects

If the role is the owner of any tables, views, schemas, or functions, PostgreSQL will refuse to drop it because those objects would become ownerless.

-- Find objects owned by the role
SELECT 
    'TABLE' AS type, schemaname, tablename AS name
FROM pg_tables
WHERE tableowner = 'target_role'

UNION ALL

SELECT 
    'SCHEMA', '', nspname
FROM pg_namespace
WHERE nspowner = (SELECT oid FROM pg_roles WHERE rolname = 'target_role');

-- Reassign ownership, then drop the role safely
REASSIGN OWNED BY target_role TO postgres;
DROP OWNED BY target_role;
DROP ROLE target_role;

Quick Fix (Recommended All-in-One Solution)

In most production scenarios, the following three-step sequence inside a transaction resolves all variants of this error cleanly:

BEGIN;

-- Step 1: Transfer all owned objects to a safe owner
REASSIGN OWNED BY target_role TO postgres;

-- Step 2: Remove all remaining privileges and default privileges
DROP OWNED BY target_role;

-- Step 3: Drop the role
DROP ROLE IF EXISTS target_role;

COMMIT;

Note: DROP OWNED BY also cleans up default privileges (ALTER DEFAULT PRIVILEGES), which are a hidden but common source of this error.

Prevention Tips

Use role-group based permission management. Instead of granting privileges directly to individual users, create functional group roles (e.g., role_readonly, role_readwrite) and assign users to those groups. When a user is removed, simply revoke group membership — no individual privilege cleanup needed.

-- Create a group role
CREATE ROLE role_readonly NOLOGIN;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO role_readonly;

-- Assign user to group (avoid direct grants)
CREATE ROLE app_user LOGIN PASSWORD 'strongpassword';
GRANT role_readonly TO app_user;

-- Clean removal later
REVOKE role_readonly FROM app_user;
DROP ROLE app_user;  -- No 2B000 error

Run a pre-drop checklist before removing any role. Incorporate the privilege check queries above into your DBA runbooks or automation scripts so dependency issues are caught before they cause errors in production.

Related Errors

2BP01 (dependent_objects_still_exist): Similar to 2B000 but triggered specifically by owned objects rather than privilege descriptors. Usually resolved with REASSIGN OWNED BY.
42501 (insufficient_privilege): Fires when a role lacks the required privilege to perform an action — often the counterpart issue when privilege management goes wrong.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community