role ... cannot be dropped because some objects depend on it

I want to drop a ROLE (previously USER/GROUPs) which is still referenced by object(s) in my database. But PostgreSQL has objections:

1

ERROR: role "xyz" cannot be dropped because some objects depend on itDETAIL: access to schema public

Let’s take a look:

1
2
3
4
5
6

db=# \dn+ public
List of schemas
 Name   | Owner    | Access privileges                                  | Description
--------+----------+----------------------------------------------------+------------------------
 public | postgres | {postgres=UC/postgres,=UC/postgres,xyz=U/postgres} | standard public schema
(1 row)

Now I have 2 ¹/² choices:

1. Drop all referenced objects too (bad idea in most cases). DROP OWNED does the grueling job.
2. Reassign the ownership of all referenced objects to another role. REASSIGN OWNED can assist here.

Or - my choice - I could find all objects which are still referenced, maybe I just have to revoke some permissions.

In my case PostgreSQL told me the first referenced object but maybe there are even more. The reason why I’m interested in the details: my project is built and deleted from textfiles containing DDL commands. A Makefile concats the textfiles together and pushes the result into the database. Deleting the project is handled by the same Makefile and textfiles and it seems I forgot to delete entries which I added earlier.

So let’s find the objects:

Tables, Indicies, Views ect.:

1

SELECT relname, relnamespace, reltype, relowner, relacl FROM pg_class WHERE array_to_string(relacl, ',') ~ E'\\mxyz\\M';

Databases:

1

SELECT datname, datacl FROM pg_database WHERE array_to_string(datacl, ',') ~ E'\\mxyz\\M';

Languages:

1

SELECT lanname, lanacl FROM pg_language WHERE array_to_string(lanacl, ',') ~ E'\\mxyz\\M';

Namespaces:

1

SELECT nspname, nspacl FROM pg_namespace WHERE array_to_string(nspacl, ',') ~ E'\\mxyz\\M';

Language Templates:

1

SELECT tmplname, tmplacl FROM pg_pltemplate WHERE array_to_string(tmplacl, ',') ~ E'\\mxyz\\M';

Functions/Procedures:

1

SELECT proname, pronamespace, proacl FROM pg_proc WHERE array_to_string(proacl, ',') ~ E'\\mxyz\\M';

Tablespaces:

1

SELECT spcname, spcacl FROM pg_tablespace WHERE array_to_string(spcacl, ',') ~ E'\\mxyz\\M';

I rewrite the ACL array into a string and use a regexp to search for my role name. The \m and \M mark the start and the end of a word so I can find my exact role name. The double \\ and the E at the begin are just for escaping purposes.