Can't delete role because of unknown object

hi Craig, 

I think this thread could help you:

http://stackoverflow.com/questions/5408156/how-to-drop-a-postgresql-database-if-there-are-active-connections-to-it

Sent from Samsung Mobile

<div>-------- Original message --------</div><div>From: Craig Libscomb <craiglibscomb1972@gmail.com> </div><div>Date:04-22-2014 15:47 (GMT-05:00) </div><div>To: pgsql-general@postgresql.org </div><div>Subject: [GENERAL] Can't delete role because of unknown object </div><div>
</div>The following command:
DROP USER IF EXISTS jpate;

generates the following output:
ERROR: role "jpate" cannot be dropped because some objects depend on it
DETAIL: 1 object in database products

It would be most helpful to know what object in the products database depends on the jpate role, but I am unable to find anything that even begins to offer a clue. What command will show me this mysterious object, please?

Thanks,
Craig

On 22/04/2014 20:47, Craig Libscomb wrote:

The following command:
DROP USER IF EXISTS jpate;

generates the following output:
ERROR: role "jpate" cannot be dropped because some objects depend on it
DETAIL: 1 object in database products

It would be most helpful to know what object in the products database
depends on the jpate role, but I am unable to find anything that even
begins to offer a clue. What command will show me this mysterious
object, please?

I'd hazard a guess that there is another role which is a member of this
one.... connect to the database using psql, and then \du will give you a
list of all roles - in the output from \du, look at the column "Member of".

HTH,

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Apr 22, 2014 at 3:02 PM, droletguillaume
<droletguillaume@gmail.com>wrote:

hi Craig,

I think this thread could help you:

http://stackoverflow.com/questions/5408156/how-to-drop-a-postgresql-database-if-there-are-active-connections-to-it

Sent from Samsung Mobile

As far as I can tell, there are no connections open under this role. In
fact, jpate has been gone for several months

SELECT usename
FROM pg_stat_activity
GROUP BY usename;
usename
------------
postgres
log_write
product_readonly
(3 rows)

What I am looking for is something along the lines of
SELECT object_name
FROM ????
WHERE object_owner = 'jpate';

So I can see what jpate owns.

-------- Original message --------

On Tue, Apr 22, 2014 at 3:06 PM, Raymond O'Donnell <rod@iol.ie> wrote:

On 22/04/2014 20:47, Craig Libscomb wrote:

The following command:
DROP USER IF EXISTS jpate;

generates the following output:
ERROR: role "jpate" cannot be dropped because some objects depend on it
DETAIL: 1 object in database products

It would be most helpful to know what object in the products database
depends on the jpate role, but I am unable to find anything that even
begins to offer a clue. What command will show me this mysterious
object, please?

I'd hazard a guess that there is another role which is a member of this
one.... connect to the database using psql, and then \du will give you a
list of all roles - in the output from \du, look at the column "Member of".

All of the roles have {} under "member of", so I assume that means no
members?

Craig Libscomb <craiglibscomb1972@gmail.com> writes:

The following command:
DROP USER IF EXISTS jpate;

generates the following output:
ERROR: role "jpate" cannot be dropped because some objects depend on it
DETAIL: 1 object in database products

It would be most helpful to know what object in the products database
depends on the jpate role, but I am unable to find anything that even
begins to offer a clue. What command will show me this mysterious object,
please?

You need to connect to that database and try the DROP USER from there.
It's not possible to produce very much info about the problematic
object when not connected to its database. (I suppose we could tell
you the object kind, ie table/function/etc, but not more than that.)

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 22/04/2014 21:26, Craig Libscomb wrote:

On Tue, Apr 22, 2014 at 3:06 PM, Raymond O'Donnell <rod@iol.ie
<mailto:rod@iol.ie>> wrote:

On 22/04/2014 20:47, Craig Libscomb wrote:

The following command:
DROP USER IF EXISTS jpate;

generates the following output:
ERROR: role "jpate" cannot be dropped because some objects depend

on it

DETAIL: 1 object in database products

It would be most helpful to know what object in the products database
depends on the jpate role, but I am unable to find anything that even
begins to offer a clue. What command will show me this mysterious
object, please?

I'd hazard a guess that there is another role which is a member of this
one.... connect to the database using psql, and then \du will give you a
list of all roles - in the output from \du, look at the column
"Member of".

All of the roles have {} under "member of", so I assume that means no
members?

Yes, that's correct. 'Twas worth a look. :-)

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Craig Libscomb
Sent: Tuesday, April 22, 2014 4:27 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Can't delete role because of unknown object

On Tue, Apr 22, 2014 at 3:06 PM, Raymond O'Donnell <rod@iol.ie> wrote:
On 22/04/2014 20:47, Craig Libscomb wrote:

The following command:
DROP USER IF EXISTS jpate;

generates the following output:
ERROR:  role "jpate" cannot be dropped because some objects depend on it
DETAIL:  1 object in database products

It would be most helpful to know what object in the products database
depends on the jpate role, but I am unable to find anything that even
begins to offer a clue. What command will show me this mysterious
object, please?

I'd hazard a guess that there is another role which is a member of this
one.... connect to the database using psql, and then \du will give you a
list of all roles - in the output from \du, look at the column "Member of".

All of the roles have {} under "member of", so I assume that means no members?
 

HTH,

Ray.

You could try:

SELECT C.relname, C.reltype
FROM pg_class C, pg_authid O
WHERE O.rolname = 'jpate'
AND C.relowner = O.oid;

Regards,
Igor Neyman

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Apr 22, 2014 at 3:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Craig Libscomb <craiglibscomb1972@gmail.com> writes:

The following command:
DROP USER IF EXISTS jpate;

generates the following output:
ERROR: role "jpate" cannot be dropped because some objects depend on it
DETAIL: 1 object in database products

It would be most helpful to know what object in the products database
depends on the jpate role, but I am unable to find anything that even
begins to offer a clue. What command will show me this mysterious object,
please?

You need to connect to that database and try the DROP USER from there.
It's not possible to produce very much info about the problematic
object when not connected to its database. (I suppose we could tell
you the object kind, ie table/function/etc, but not more than that.)

*sigh*, yep, I was connected to the wrong database. After connecting
the error told me the dependency was privileges on the public schema.
REVOKEing those then allowed me to DROP the user.

Thanks!

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Apr 22, 2014 at 3:39 PM, Igor Neyman <ineyman@perceptron.com> wrote:

From: pgsql-general-owner@postgresql.org [mailto:

pgsql-general-owner@postgresql.org] On Behalf Of Craig Libscomb

Sent: Tuesday, April 22, 2014 4:27 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Can't delete role because of unknown object

On Tue, Apr 22, 2014 at 3:06 PM, Raymond O'Donnell <rod@iol.ie> wrote:
On 22/04/2014 20:47, Craig Libscomb wrote:

The following command:
DROP USER IF EXISTS jpate;

generates the following output:
ERROR: role "jpate" cannot be dropped because some objects depend on it
DETAIL: 1 object in database products

It would be most helpful to know what object in the products database
depends on the jpate role, but I am unable to find anything that even
begins to offer a clue. What command will show me this mysterious
object, please?

I'd hazard a guess that there is another role which is a member of this
one.... connect to the database using psql, and then \du will give you a
list of all roles - in the output from \du, look at the column "Member

of".

All of the roles have {} under "member of", so I assume that means no

members?

HTH,

Ray.

You could try:

SELECT C.relname, C.reltype
FROM pg_class C, pg_authid O
WHERE O.rolname = 'jpate'
AND C.relowner = O.oid;

Dummy me, I was connected to the wrong database.

This returned 0 rows though. Even tried it with a different user. But
punching in postgres gave me lots of info. Thanks for the hint.