role “x” cannot be dropped because some objects depend on it

dbtut

6 years ago

If you want to delete the PostgreSQL user, we can get the error “role x can not be dropped because some objects depend on it”. You can find the display of the error below.

In order to remove the user without receiving this error, you must first revoke the user authority as follows.

REVOKE ALL PRIVILEGES ON DATABASE yourdbname FROM yourusername;

1	REVOKE ALL PRIVILEGES ON DATABASE yourdbname FROM yourusername;

Once the authorities have been revoked with the revoke command, run the drop command again.

If you keep getting the same error, you can see the tables with the owner by using the following script.

select pgn.nspname as schema_name,
       pgc.relname as object_name,
       pgr.rolname as owner,
       case pgc.relkind
         when 'r' then 'TABLE'
         when 'm' then 'MATERIALIZED_VIEW'
         when 'i' then 'INDEX'
         when 'S' then 'SEQUENCE'
         when 'v' then 'VIEW'
         when 'c' then 'TYPE'
         else pgc.relkind::text
       end as object_type
from pg_class pgc
  join pg_roles pgr on pgr.oid = pgc.relowner
  join pg_namespace pgn on pgn.oid = pgc.relnamespace
where pgn.nspname not in ('information_schema', 'pg_catalog')
and pgn.nspname not like 'pg_toast%'
and pgr.rolname = 'yourusername'
order by pgn.nspname, pgc.relname;

select pgn.nspname as schema_name,

pgc.relname as object_name,

pgr.rolname as owner,

case pgc.relkind

when 'r' then 'TABLE'

when 'm' then 'MATERIALIZED_VIEW'

when 'i' then 'INDEX'

when 'S' then 'SEQUENCE'

when 'v' then 'VIEW'

when 'c' then 'TYPE'

else pgc.relkind::text

end as object_type

from pg_class pgc

join pg_roles pgr on pgr.oid = pgc.relowner

join pg_namespace pgn on pgn.oid = pgc.relnamespace

where pgn.nspname not in ('information_schema', 'pg_catalog')

and pgn.nspname not like 'pg_toast%'

and pgr.rolname = 'yourusername'

order by pgn.nspname, pgc.relname;

With the help of the command below, you can set the owner of these tables as the postgres user.

REASSIGN OWNED BY yourusername TO postgres;

1	REASSIGN OWNED BY yourusername TO postgres;