PostgreSQL Dependent Privilege Descriptors Still Exist (SQLSTATE 2B000)

When you attempt to revoke a grant option from a role that has already used that option to grant privileges to other roles, PostgreSQL raises:

ERROR:  dependent privileges exist
HINT:  Use CASCADE to revoke them too.
SQLSTATE: 2B000

The condition name is dependent_privilege_descriptors_still_exist. It signals that revoking the grant option would leave behind orphaned privilege grants that were derived from it.

What This Error Means

SQLSTATE class 2B covers privilege-related errors in PostgreSQL's SQL standard error taxonomy. The 2B000 code is specifically raised by the REVOKE command when you attempt to remove a GRANT OPTION FOR privilege from a role that has already exercised that option — i.e., the role has granted the same privilege to one or more other roles.

When a role holds a privilege WITH GRANT OPTION, it is permitted to grant that privilege onward to other roles. If you subsequently try to strip the original GRANT OPTION, PostgreSQL detects that downstream grants exist that were authorized by the option you are revoking. Silently proceeding would leave those grants in an inconsistent state — authorized by a grant option that no longer exists — so PostgreSQL blocks the operation and reports 2B000.

This error does not affect the transaction's ability to continue; it is a statement-level error. The REVOKE statement is rolled back, but the surrounding transaction (if any) can still proceed or be committed.

Common Causes

1. Revoking a grant option after the grantee has re-granted the privilege. A role alice was granted SELECT ON TABLE orders WITH GRANT OPTION. Alice then granted SELECT to bob. Attempting REVOKE GRANT OPTION FOR SELECT ON TABLE orders FROM alice fails because bob's privilege was derived from Alice's grant option.

2. Schema or database-level grant option chains. Grant option cascades are common with schema-level privileges (e.g., USAGE ON SCHEMA) when multiple roles have propagated the privilege through a chain. Revoking the option partway up the chain triggers 2B000.

3. Role inheritance trees where the intermediate grantor is being demoted. During privilege restructuring — such as migrating from a flat role model to a hierarchical one — attempting to reduce a role's rights without first cleaning up its downstream grants raises this error.

How to Fix dependent_privilege_descriptors_still_exist

1. Use CASCADE to revoke the grant option and all dependent grants simultaneously. This is the most direct fix when you intend to remove the entire privilege chain:

   REVOKE GRANT OPTION FOR SELECT ON TABLE orders FROM alice CASCADE;
   

   CASCADE will also revoke the SELECT privilege from bob (and any further grantees in the chain) because their grants depended on Alice's now-removed grant option.

2. Manually revoke downstream grants first, then revoke the grant option. If you want precise control over which downstream grants are removed before revoking the option:

   -- Step 1: remove the downstream grant
   REVOKE SELECT ON TABLE orders FROM bob;
   
   -- Step 2: now revoke the grant option from the original grantee
   REVOKE GRANT OPTION FOR SELECT ON TABLE orders FROM alice;
   

   This approach is preferable when you want to audit exactly which privileges are being removed.

3. Identify dependent grants before acting. Query information_schema.role_table_grants (or pg_catalog.pg_class + pg_catalog.aclitem) to map the full privilege chain before making changes:

   SELECT grantee, privilege_type, is_grantable
   FROM information_schema.role_table_grants
   WHERE table_name = 'orders'
   ORDER BY grantee;
   

   For schema-level privileges, use information_schema.role_usage_grants. This helps you understand the blast radius of a CASCADE revoke.

4. Reconstruct the intended privilege model after cleanup. After cascading the revoke, re-grant only what is appropriate:

   -- Re-grant to bob directly without going through alice
   GRANT SELECT ON TABLE orders TO bob;
   

Additional Information

• This error has been part of PostgreSQL since early versions — the GRANT OPTION FOR mechanism and its cascade behavior are defined in the SQL standard and have been implemented consistently across PostgreSQL releases.
• Related SQLSTATE codes in the same class: there are no other defined 2B sub-codes beyond 2B000 in PostgreSQL's current error catalog; it is the only condition in that class.
• REVOKE ... CASCADE is idempotent with respect to already-absent grants — if a downstream grantee no longer holds the privilege, the cascade simply skips it without error.
• ORMs and connection pool libraries typically surface this as a generic database error with the message dependent privileges exist. Check the underlying driver exception's sqlstate field to confirm it is 2B000 and distinguish it from other privilege errors such as 42501 (insufficient privilege).
• In PostgreSQL, pg_catalog.pg_depend does not track privilege dependencies — privilege chains are stored inline in the ACL arrays on catalog objects. Use information_schema views or parse aclitem[] arrays directly to inspect them.

Frequently Asked Questions

Does REVOKE GRANT OPTION FOR ... CASCADE also revoke the privilege itself from the original grantee?

No. REVOKE GRANT OPTION FOR SELECT ON TABLE t FROM alice CASCADE removes only Alice's ability to grant SELECT further. Alice retains her own SELECT privilege. If you want to revoke the privilege entirely, use REVOKE SELECT ON TABLE t FROM alice CASCADE.

What is the difference between REVOKE SELECT ... CASCADE and REVOKE GRANT OPTION FOR SELECT ... CASCADE?

REVOKE SELECT ... CASCADE removes the SELECT privilege from the named role and cascades to anyone that role granted it to. REVOKE GRANT OPTION FOR SELECT ... CASCADE leaves the named role's own SELECT intact but removes their ability to grant it onward, cascading the removal of any downstream grants that depended on that option.

How do I find out which roles received grants from a specific grantee?

Query the relevant information_schema grants view filtered by grantor:

SELECT grantee, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantor = 'alice'
  AND table_schema = 'public';

Will this error occur if the grantee has not yet used their grant option?

No. If alice holds SELECT WITH GRANT OPTION but has not granted SELECT to anyone else, revoking the grant option succeeds without error and without needing CASCADE.