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 a role, user, or other database object that still has associated privilege descriptors tied to it. Essentially, PostgreSQL is protecting you from leaving orphaned permission references in the system catalog. You must remove or reassign all dependent privileges before the drop operation will succeed.

Top 3 Causes

1. Granted Privileges Still Exist on the Role

The most common cause. If the role you're trying to drop still holds GRANT-based privileges on tables, schemas, sequences, or functions, PostgreSQL will refuse to remove it.

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

-- Revoke all privileges before dropping
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 DATABASE mydb FROM target_role;

2. Role Membership Dependencies

If the target role is a member of another role — or has other roles as members — those membership links count as privilege descriptors and must be removed first.

-- Find membership relationships for the role
SELECT r.rolname AS parent_role, m.member::regrole AS child_role
FROM pg_auth_members m
JOIN pg_roles r ON r.oid = m.roleid
WHERE m.member = (SELECT oid FROM pg_roles WHERE rolname = 'target_role');

-- Remove the membership links
REVOKE target_role FROM parent_role;
REVOKE child_role FROM target_role;

3. Role is the Owner of Database Objects

If the target role owns any tables, schemas, databases, or functions, it holds implicit privilege descriptors as the owner. Ownership must be transferred before dropping the role.

-- Reassign all owned objects to another role
REASSIGN OWNED BY target_role TO postgres;

-- Drop remaining privilege descriptors
DROP OWNED BY target_role;

-- Now safely drop the role
DROP ROLE IF EXISTS target_role;

Quick Fix: Recommended Step-by-Step Solution

-- Step 1: Transfer ownership of all objects
REASSIGN OWNED BY target_role TO postgres;

-- Step 2: Clean up any remaining grants and dependencies
DROP OWNED BY target_role;

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

Warning: DROP OWNED BY will permanently delete objects owned by the role that weren't reassigned. Always run REASSIGN OWNED BY first if you want to keep those objects.

Prevention Tips

1. Run a dependency check before any role removal:

-- Pre-drop checklist query
SELECT 'TABLE GRANT' AS dependency_type,
       grantee,
       table_schema || '.' || table_name AS object_name,
       privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'target_role'
UNION ALL
SELECT 'ROLE MEMBERSHIP',
       r.rolname,
       m.member::regrole::text,
       'MEMBER'
FROM pg_auth_members m
JOIN pg_roles r ON r.oid = m.roleid
WHERE m.member = (SELECT oid FROM pg_roles WHERE rolname = 'target_role');

2. Use role groups instead of direct grants. Assign privileges to group roles (readonly_role, readwrite_role) and only grant group membership to individual users. This way, dropping a user only requires removing one membership link, not hunting down dozens of individual privilege entries scattered across your database.

Related Errors

2BP01 (dependent_objects_still_exist): Triggered when dropping an object that has dependent objects. Very similar to 2B000 — consider whether CASCADE is appropriate.
42501 (insufficient_privilege): The most common privilege-related error; often surfaces alongside 2B000 during permission cleanup.

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