I want to drop a ROLE (previously USER/GROUPs) which is still referenced by object(s) in my database. But PostgreSQL has objections:
| |
Let’s take a look:
| |
Now I have 2 ¹/² choices:
- Drop all referenced objects too (bad idea in most cases). DROP OWNED does the grueling job.
- 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.:
| |
Databases:
| |
Languages:
| |
Namespaces:
| |
Language Templates:
| |
Functions/Procedures:
| |
Tablespaces:
| |
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.
Share: