Re: DROP ROLE prevented by dependency

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Friday, January 06, 2012 12:22 PM
> To: Bulgrien, Kevin
> Cc: 'pgsql-general(at)postgresql(dot)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.