Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | 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.
1 | REASSIGN OWNED BY yourusername TO postgres; |
