Some times ago I created a PostgreSQL user named user1 (PostgreSQL 9.4.9).
I want to drop this user. So I first revoke all permissions on tables, sequences, functions, default privileges and ownership too:
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON SEQUENCES FROM user1;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON TABLES FROM user1;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON FUNCTIONS FROM user1;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM user1;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM user1;
REVOKE ALL ON ALL FUNCTIONS IN SCHEMA public FROM user1;
REASSIGN OWNED BY user1 TO postgres;
However it seems that one object remains linked to this user in 2 databases:
postgres=# DROP ROLE user1;
ERROR: role "user1" cannot be dropped because some objects depend on it
DETAIL: 1 object in database db1
1 object in database db2
It even seems to be a function:
postgres=# \c db1
You are now connected to database "db1" as user "postgres".
db1=# DROP ROLE user1;
ERROR: role "user1" cannot be dropped because some objects depend on it
DETAIL: privileges for function text(boolean)
1 object in database db2
But I can not determine which object is owned or related to user1.
If I pg_dump -s db1 | grep user1
I get no result! Could it be a global object?
How can I identify the missing object?
I have executed the commands in each database (db1 and db2). I do not want to drop objects owned by user1
, just want to reassign or remove grants for this user.
Best Answer
Answer to question asked
To look for the function in the error message and its owner:
Related:
Actual problem
The error message says:
It's not about ownership but about privileges.
The manual for
DROP ROLE
:And for
ALTER DEFAULT PRIVILEGES
:It also looks like you only executed
REASSIGN OWNED
in one DB, but the manual instructs:Bold emphasis mine.
And you restricted your commands with
IN SCHEMA public
. Drop that clause to target the whole DB. But don't bother, there is a ...Simple solution with
DROP OWNED
All the role's objects changed ownership to
postgres
with the first command and are safe now. The wording ofDROP OWNED
is a bit misleading, since it also gets rid of all privileges and default privileges. The manual forDROP OWNED
:Repeat in all relevant DBs, then you can move in for the kill: