> -----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
accessedonly by authorized personnel of General Dynamics and approved service providers. Use, storage and transmission
aregoverned by General Dynamics and its policies. Contractual restrictions apply to third parties. Recipients should
referto 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.
