Skip to content

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:

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

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';

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

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

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';

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

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.


No Trackbacks


Display comments as Linear | Threaded

No comments

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
To leave a comment you must approve it via e-mail, which will be sent to your address after submission.
Form options