DROP ROLE prevented by dependency

"Bulgrien, Kevin" <Kevin.Bulgrien@GDSATCOM.com> writes:

psql 8.3.16
I've done some searching of mailing list, etc., but have either not searched
the right way, or something. If anyone could throw a bone on this it would
be appreciated. I have no idea what the procedure is for figuring out how
to drop this role:

DROP ROLE myuser;
ERROR: role "myuser" cannot be dropped because some objects depend on it
DETAIL: 1 objects in database postgres

Try the command while connected to the postgres database. It can't give
you more detail than that from where you are, for lack of visibility
into the other database's system catalogs.

Also, read up on DROP OWNED BY.

regards, tom lane

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, January 06, 2012 12:22 PM
To: Bulgrien, Kevin
Cc: 'pgsql-general@postgresql.org'
Subject: Re: [GENERAL] DROP ROLE prevented by dependency

I have no idea what the procedure is for figuring out how
to drop this role:

DROP ROLE myuser;
ERROR: role "myuser" cannot be dropped because some
objects depend on it
DETAIL: 1 objects in database postgres

Try the command while connected to the postgres database. It
can't give you more detail than that from where you are, for
lack of visibility into the other database's system catalogs.

Hah. I was connecting to template1 to do the DROP, so I changed to
postgres and got:

DROP ROLE myuser;
ERROR: role "myuser" cannot be dropped because some objects depend on it
DETAIL: 1 objects in database template1

That jostled loose a clue. That seemed to imply that ROLE myuser
had some setup (GRANT/REVOKE) done in both postgres AND template1.

After undoing all custom ROLE setup in both template1 (GRANT/REVOKE)
in postgres AND template1, the ROLE dropped fine.

Apparently the database that is connected when ROLE GRANTS and
REVOKES are done is tied to the database - so it can only be undone
by connecting to the same database. I have to be consistant about
doing the role management consistently in the same database
context. Normally I do all the database owner ROLE stuff when
connected to template1, but somehow that user had some setup
in both template1 and postgres. I vaguely remember connected to
postgres some time ago when trying to figure out how to dump the
global data not associated with an application database. I must
have "forgotten" which context I was in when playing around with
the ROLE.

It's fixed now. Thanks for the tip! I think I learned
something important in the process.

Kevin Bulgrien

This message and/or attachments may include information subject to GD Corporate Policy 07-105 and is intended to be accessed only by authorized personnel of General Dynamics and approved service providers. Use, storage and transmission are governed by General Dynamics and its policies. Contractual restrictions apply to third parties. Recipients should refer to the policies or contract to determine proper handling. Unauthorized review, use, disclosure or distribution is prohibited. If you are not an intended recipient, please contact the sender and destroy all copies of the original message.