Thread: cannot drop user

cannot drop user

From
"Michael P. Soulier"
Date:
Hi,

I've dropped a db and now I'm trying to drop the user that owns all of it, but
I can't.

dropdb: database removal failed: ERROR:  database "tugdb" does not exist
[2014-10-28 13:23:40,462] INFO:Dropping user...
dropuser: removal of role "tugdbuser" failed: ERROR:  role "tugdbuser" cannot
be dropped because some objects depend on it
DETAIL:  owner of table taps
owner of table siptrunks
owner of table siptrunkroutingrules
owner of sequence sipoptions_id_seq
owner of table sipoptions
owner of table sip_clients
owner of table schema
owner of table proxies
owner of sequence minetoptions_id_seq
owner of table minetoptions
owner of table minet_clients
owner of sequence metrics_id_seq
owner of table metrics
owner of sequence iptranslations_id_seq
owner of table iptranslations
owner of sequence instances_id_seq
owner of table instances
owner of table icps
owner of table dntaps
owner of table django_session
owner of sequence django_content_type_id_seq
owner of table django_content_type
owner of table cres
owner of table config_overrides
owner of table clusterzones
owner of sequence clusters_id_seq
owner of table clusters
owner of table clusternodes
owner of sequence auth_user_user_permissions_id_seq
owner of table auth_user_user_permissions
owner of sequence auth_user_id_seq
owner of sequence auth_user_groups_id_seq
owner of table auth_user_groups
owner of table auth_user
owner of sequence auth_permission_id_seq
owner of table auth_permission
owner of sequence auth_message_id_seq
owner of table auth_message
owner of sequence auth_group_permissions_id_seq
owner of table auth_group_permissions
owner of sequence auth_group_id_seq
owner of table auth_group
owner of table applications
owner of table alarmdevents

The tugdb database is gone but these artifacts are all from it. How is that
possible if the db is gone?

I can't find anything owned by tugdbuser, and I don't understand how to
troubleshoot this. Help appreciated.

This is postgres 8.4 on CentOS 6.

Mike

Attachment

Re: cannot drop user

From
Jerry Sievers
Date:
"Michael P. Soulier" <msoulier@digitaltorque.ca> writes:

> Hi,
>
> I've dropped a db and now I'm trying to drop the user that owns all of it, but
> I can't.
>
> dropdb: database removal failed: ERROR:  database "tugdb" does not exist
> [2014-10-28 13:23:40,462] INFO:Dropping user...
> dropuser: removal of role "tugdbuser" failed: ERROR:  role "tugdbuser" cannot
> be dropped because some objects depend on it
> DETAIL:  owner of table taps
> owner of table siptrunks

That user owns objects in whatever DB you're sitting in meanwhile trying
to DROP ROLE.  (template1?)

This is evident below since  you're getting full object names in the
dependency messages.

> owner of table siptrunkroutingrules
> owner of sequence sipoptions_id_seq
> owner of table sipoptions
> owner of table sip_clients
> owner of table schema
> owner of table proxies
> owner of sequence minetoptions_id_seq
> owner of table minetoptions
> owner of table minet_clients
> owner of sequence metrics_id_seq
> owner of table metrics
> owner of sequence iptranslations_id_seq
> owner of table iptranslations
> owner of sequence instances_id_seq
> owner of table instances
> owner of table icps
> owner of table dntaps
> owner of table django_session
> owner of sequence django_content_type_id_seq
> owner of table django_content_type
> owner of table cres
> owner of table config_overrides
> owner of table clusterzones
> owner of sequence clusters_id_seq
> owner of table clusters
> owner of table clusternodes
> owner of sequence auth_user_user_permissions_id_seq
> owner of table auth_user_user_permissions
> owner of sequence auth_user_id_seq
> owner of sequence auth_user_groups_id_seq
> owner of table auth_user_groups
> owner of table auth_user
> owner of sequence auth_permission_id_seq
> owner of table auth_permission
> owner of sequence auth_message_id_seq
> owner of table auth_message
> owner of sequence auth_group_permissions_id_seq
> owner of table auth_group_permissions
> owner of sequence auth_group_id_seq
> owner of table auth_group
> owner of table applications
> owner of table alarmdevents
>
> The tugdb database is gone but these artifacts are all from it. How is that
> possible if the db is gone?
>
> I can't find anything owned by tugdbuser, and I don't understand how to
> troubleshoot this. Help appreciated.
>
> This is postgres 8.4 on CentOS 6.
>
> Mike

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800


Re: cannot drop user

From
Tom Lane
Date:
"Michael P. Soulier" <msoulier@digitaltorque.ca> writes:
> I've dropped a db and now I'm trying to drop the user that owns all of it, but
> I can't.

> dropdb: database removal failed: ERROR:  database "tugdb" does not exist
> [2014-10-28 13:23:40,462] INFO:Dropping user...
> dropuser: removal of role "tugdbuser" failed: ERROR:  role "tugdbuser" cannot
> be dropped because some objects depend on it
> DETAIL:  owner of table taps
> owner of table siptrunks
> ... etc

> The tugdb database is gone but these artifacts are all from it. How is that
> possible if the db is gone?

Those DETAIL lines are complaining about objects that are in the database
you're currently attached to; the details about object names and so on
would not be available otherwise.  So I suspect at some point you
accidentally loaded a pg_dump script or suchlike into some other database
besides the tugdb one ...

DROP OWNED BY might be the easiest way to clean up the mess.

            regards, tom lane


Re: cannot drop user

From
"Michael P. Soulier"
Date:
On 28/10/14 Jerry Sievers said:

> That user owns objects in whatever DB you're sitting in meanwhile trying
> to DROP ROLE.  (template1?)

I'm just running the dropuser command, so if it uses template1, then yes.

I'm not sure how they would get there. This is only happening on one box, so
perhaps it has odd history.

Thanks,
Mike

Attachment

Re: cannot drop user

From
"Michael P. Soulier"
Date:
On Oct 28, 2014, at 1:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Those DETAIL lines are complaining about objects that are in the database
> you're currently attached to; the details about object names and so on
> would not be available otherwise.  So I suspect at some point you
> accidentally loaded a pg_dump script or suchlike into some other database
> besides the tugdb one ...
>
> DROP OWNED BY might be the easiest way to clean up the mess.

Found it, thanks.

Mike