Find objects linked to a PostgreSQL role

Question

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.

• Brian D: Related question that this does not seem to address, but would be useful to address as other posts link to this one: When the Error is of the form: Could not drop the role. ERROR: role "[username]" cannot be dropped because some objects depend on it DETAIL: [#] objects in database [dbname]. How do you identify these objects prior to reassigning and/or dropping them?
• Brian D: ignore the above comment. i'm an idiot. If you use the pgadmin4 GUI to right-click on a role and select delete/drop, you get only the above error message. But if you actually run the query drop role [username], then you get the full report of all the privileges that are causing the error in the pgadmin Messages pane. :facepalm:

Answer 1

Answer to question asked

To look for the function in the error message and its owner:

SELECT oid::regprocedure AS function
 , pg_get_userbyid(proowner) AS owner
FROM pg_proc
WHERE oid = 'text(boolean)'::regprocedure;

Related:

• DROP FUNCTION without knowing the number/type of parameters?

Actual problem

The error message says:

DETAIL: privileges for function text(boolean)

It's not about ownership but about privileges.

The manual for DROP ROLE:

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.

And for ALTER DEFAULT PRIVILEGES:

If you wish to drop a role for which the default privileges have been altered, it is necessary to reverse the changes in its default privileges or use DROP OWNED BY to get rid of the default privileges entry for the role.

It also looks like you only executed REASSIGN OWNED in one DB, but the manual instructs:

Because REASSIGN OWNED does not affect objects within other databases, it is usually necessary to execute this command in each database that contains objects owned by a role that is to be removed.

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

REASSIGN OWNED BY user1 TO postgres;
DROP OWNED BY user1;

All the role's objects changed ownership to postgres with the first command and are safe now. The wording of DROP OWNED is a bit misleading, since it also gets rid of all privileges and default privileges. The manual for DROP OWNED:

DROP OWNED drops all the objects within the current database that are owned by one of the specified roles. Any privileges granted to the given roles on objects in the current database and on shared objects (databases, tablespaces) will also be revoked.

Repeat in all relevant DBs, then you can move in for the kill:

DROP ROLE user1;

• Cerin: This finds nothing. Yet DROP ROLE myuser throws an error because there are still objects that the user owns...
• Erwin Brandstetter: @Cerin What is "this"? The SELECT query at the top, which only looks for routines (functions and procedures) matching the OP's problem. Or REASSIGN OWNED + DROP OWNED as suggested, which takes care of all objects and privileges in the current DB? (Has to be executed in all DBs of the same DB cluster that may be involved.)
• Eugen Konkov: I want to link this answer. With help of that query can list objects access to which were granted to your role: dba.stackexchange.com/a/285595/91706

Answer 2

The query below lists objects with owners. For all privileges we actually need more.

--r = ordinary table, i = index, S = sequence, v = view, m = materialized view, c = composite type, t = TOAST table, f = foreign table
SELECT 
 n.nspname AS schema_name,
 c.relname AS rel_name,
 c.relkind AS rel_kind,
 pg_get_userbyid(c.relowner) AS owner_name
 FROM pg_class c
 JOIN pg_namespace n ON n.oid = c.relnamespace
UNION ALL
-- functions (or procedures)
SELECT
 n.nspname AS schema_name,
 p.proname,
 'p',
 pg_get_userbyid(p.proowner)
 FROM pg_proc p
 JOIN pg_namespace n ON n.oid = p.pronamespace

• Nicolas Payart: I still do not find the missing object with this.
• Erwin Brandstetter: @NicolasPayart: Are you executing the query in the right database?
• Eugen Konkov: The answer with more other approaches: dba.stackexchange.com/q/4286/91706

Answer 3

You need to first connect to the database. In your instance that would be

\c db1

and

\c db2

Then try running the REVOKE ALL PRIVILEGES and REASSIGN OWNED/DROP OWNED BY statements again.

• András Váczi: Hey, thanks for your first answer. However, before posting, please think about what does this add to the existing answers, and describe this in your answer, too.

Answer 4

DROP ROLE also depends on privileges of the ROLE - see DETAIL in the below:

DROP USER alice;

ERROR: role "alice" cannot be dropped because some objects depend on it DETAIL: privileges for table bloat

REVOKE UPDATE on bloat FROM alice;

REVOKE

DROP USER alice;

DROP ROLE