ads' corner

Let's take a look:

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.:

SELECT relname, relnamespace, reltype, relowner, relacl

FROM pg_class

WHERE array_to_string(relacl, ',') ~ E'\\mxyz\\M';

Databases:

SELECT datname, datacl

FROM pg_database

WHERE array_to_string(datacl, ',') ~ E'\\mxyz\\M';

Languages:

SELECT lanname, lanacl

FROM pg_language

WHERE array_to_string(lanacl, ',') ~ E'\\mxyz\\M';

Namespaces:

SELECT nspname, nspacl

FROM pg_namespace

WHERE array_to_string(nspacl, ',') ~ E'\\mxyz\\M';

Language Templates:

SELECT tmplname, tmplacl

FROM pg_pltemplate

WHERE array_to_string(tmplacl, ',') ~ E'\\mxyz\\M';

Functions/Procedures:

SELECT proname, pronamespace, proacl

FROM pg_proc

WHERE array_to_string(proacl, ',') ~ E'\\mxyz\\M';

Tablespaces:

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.