Drop role returns "ERROR: role "user01" cannot be dropped because some objects depend on it"

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

When attempting to drop a role in Greenplum, the following error message is produced:

ERROR: role "user01" cannot be dropped because some objects depend on it.

Environment

Cause

A role cannot be dropped or removed if it is still referenced in any database of the cluster. An error will occur if this is attempted.

Before dropping the role, drop all the objects it owns (or reassign their ownership). Revoke any privileges the role has been granted on other objects.
 

Sometimes the changing of ownership does not update all metadata correctly to reflect this ownership change. It is mainly been seen with functions. For example, the "proowner" column in the pg_proc table will be updated to the new owner, but the pg_shdepend table will not be successully updated.

 

Resolution

Check if there is an inconsistency between the owner defined in pg_class/pg_proc and the owner defined in pg_shdepend. Connect to the database and run the following query:

select datname,dbid,objid,refclassid,refobjid,deptype,rolname, nspname || '.' || relname as objname, relowner, nspname || '.' || proname as funcname, proowner
from pg_shdepend 
left join pg_roles on (pg_shdepend.refobjid = pg_roles.oid)
left join pg_database on (pg_shdepend.dbid = pg_database.oid)
left join pg_class on (pg_class.oid = pg_shdepend.objid)
left join pg_proc on (pg_proc.oid = pg_shdepend.objid)
left join pg_namespace on (pg_namespace.oid in (pg_class.relnamespace, pg_proc.pronamespace))
where relowner <> refobjid and proowner <> refobjid;

If the above query reports any relation or function then:

1. Note the rolname reported in the query.
2. Use alter command to change ownership to a role/user other than what is reported in the rolname.
3. Use alter command again to change the ownership back to rolname.
4. Re-run the above query to verify that pg_shdepend is no longer inconsistent with pg_class/pg_proc.