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 ownerFROM   pg_procWHERE  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'snot about ownership but aboutprivileges.

The manual forDROP 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 forALTER 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 useDROP OWNED BY toget rid of the default privileges entry for the role.

It also looks like you only executedREASSIGN OWNED in one DB, butthe manual instructs:

BecauseREASSIGN OWNED does not affect objects within other databases, it is usually necessary toexecute 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 withIN SCHEMA public. Drop that clause to target the whole DB. But don't bother, there is a ...

Simple solution withDROP OWNED

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

All the role's objects changed ownership topostgres 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:

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;

• This finds nothing. YetDROP ROLE myuser throws an error because there are still objects that the user owns...

  Cerin

  – Cerin

  2023-09-14 18:22:55 +00:00

  CommentedSep 14, 2023 at 18:22
• @Cerin What is "this"? TheSELECT query at the top, which only looks for routines (functions and procedures) matching the OP's problem. OrREASSIGN OWNED +DROP OWNED as suggested, which takes care ofall objects and privileges in the current DB? (Has to be executed in all DBs of the same DB cluster that may be involved.)

  Erwin Brandstetter

  – Erwin Brandstetter

  2023-09-14 21:47:05 +00:00

  CommentedSep 14, 2023 at 21:47
• 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

  Eugen Konkov

  – Eugen Konkov

  2024-02-20 15:27:22 +00:00

  CommentedFeb 20, 2024 at 15:27

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 tableSELECT     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.relnamespaceUNION 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

• 2
  I still do not find the missing object with this.

  Nicolas Payart

  – Nicolas Payart

  2016-11-15 23:05:51 +00:00

  CommentedNov 15, 2016 at 23:05
• @NicolasPayart: Are you executing the query in the right database?

  Erwin Brandstetter

  – Erwin Brandstetter

  2016-11-16 04:01:54 +00:00

  CommentedNov 16, 2016 at 4:01
• The answer with more other approaches:dba.stackexchange.com/q/4286/91706

  Eugen Konkov

  – Eugen Konkov

  2024-02-20 16:16:18 +00:00

  CommentedFeb 20, 2024 at 16:16

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

\c db1

and

\c db2

Then try running theREVOKE ALL PRIVILEGES andREASSIGN OWNED/DROP OWNED BY statements again.

• 3
  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.

  András Váczi

  – András Váczi

  2019-02-05 08:55:47 +00:00

  CommentedFeb 5, 2019 at 8:55

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 itDETAIL: privileges for table bloat

REVOKE UPDATE on bloat FROM alice;

REVOKE

DROP USER alice;

DROP ROLE

• postgresql
• permissions