Pivotal Greenplum 4.3.x
When attempting to drop a role in Greenplum, you see the following error message:
ERROR: role "user01" cannot be dropped because some objects depend on it.
A role cannot be removed if it is still referenced in any database of the cluster; an error will be raised if it is attempted. Before dropping the role, you must drop all the objects it owns (or reassign their ownership) and 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 has mainly been seen with functions. The "proowner" column in the pg_proc table will be updated to the new owner, but the pg_shdepend table may not have been updated.
To check if there is an inconsistency between the owner defined in pg_class/pg_proc and 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:
- Note the "rolname" reported in the query
- Use "alter" command to change ownership to a role/user other than what is reported in the "rolname"
- Use "alter" command again to change the ownership back to rolname
- Re-run the above query to verify that pg_shdepend is no longer inconsistent with pg_class/pg_proc