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;
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,
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'
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;