Thread: [ADMIN] Cannot drop role

I've seen one or two mentions of this problem with no applicable solution.
I have a case where I cannot drop a role because "...5 objects depend on
it" (according to pgAdmin 3).  When I connect to the database that holds
those objects (they are not named) and try to drop the role I get an error
message:

  [Error Code: 0, SQL State: XX000]  ERROR: could not find tuple for
default ACL 2243075

Even after trying a number of queries that are purported to list grants
and ownerships for given ids I can turn up nothing that references the
role by name.

At this point I suspect there's something corrupted in the system catalog.
How can I find the table entry (or entries) that reference that
identifier?  The fact that the message is issued suggests a link somewhere
but I cannot locate it.

Many thanks!

Steve


--

Steven Hirsch wrote:

> At this point I suspect there's something corrupted in the system catalog.

Yes, clearly.

> How can I find the table entry (or entries) that reference that identifier?
> The fact that the message is issued suggests a link somewhere but I cannot
> locate it.

Look in pg_shdepend.  Probably somebody deleted rows from pg_default_acl
(and who knows what else) rather than using DDL commands, which is what
they should have done.

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services