SEO

I am trying to drop a user but it complained with the following message:

ERROR: role "joe" cannot be dropped because some objects depend on it
DETAIL: 1 objects in database Jan_test
1 objects in database Jan

this user does not own any tables in those two databases. Is there any command I can use to check all the dependencies on this user?

Thanks,
Jessica

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

On 10/31/07, Jessica Richard <rjessil@yahoo.com> wrote:
> I am trying to drop a user but it complained with the following message:
>
> ERROR: role "joe" cannot be dropped because some objects depend on it
> DETAIL: 1 objects in database Jan_test
> 1 objects in database Jan
>
> this user does not own any tables in those two databases. Is there any
> command I can use to check all the dependencies on this user?

What version of pgsql are you running? In 8.2 I get a more useful
error message for a table that is owned by a user, i.e.:

ERROR: role ... cannot be dropped ...
DETAIL: owner of table <tablename>

Have you looked in the logs to see if something like that shows up in them?

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Jessica Richard wrote:
> I am trying to drop a user but it complained with the following message:
>
> ERROR: role "joe" cannot be dropped because some objects depend on it
> DETAIL: 1 objects in database Jan_test
> 1 objects in database Jan
>
> this user does not own any tables in those two databases. Is there any
> command I can use to check all the dependencies on this user?

Connect to those databases and issue the command again to see the
detail. When you are connected to a different database, you don't have
access to the other database catalogs, thus we cannot give you the
object names (only the fact that they exist).

--
Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1", W 73º 13' 56.4"
"PHP is what I call the "Dumb Monkey" language. [A]ny dumb monkey can code
something in PHP. Python takes actual thought to produce something useful."
(J. Drake)

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Jessica Richard <rjessil@yahoo.com> writes:
> I am trying to drop a user but it complained with the following message:
> ERROR: role "joe" cannot be dropped because some objects depend on it
> DETAIL: 1 objects in database Jan_test
> 1 objects in database Jan

> this user does not own any tables in those two databases. Is there any command I can use to check all the dependencies on this user?

Try to do the DROP ROLE within those databases. You'll get more detail
about owned objects within the current database, but from outside that
database the info isn't reachable ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Doesn't ownership also apply to views and stored procs...

On 11/1/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>
> On 10/31/07, Jessica Richard <rjessil@yahoo.com> wrote:
> > I am trying to drop a user but it complained with the following message:
> >
> > ERROR: role "joe" cannot be dropped because some objects depend on it
> > DETAIL: 1 objects in database Jan_test
> > 1 objects in database Jan
> >
> > this user does not own any tables in those two databases. Is there any
> > command I can use to check all the dependencies on this user?
>
> What version of pgsql are you running? In 8.2 I get a more useful
> error message for a table that is owned by a user, i.e.:
>
> ERROR: role ... cannot be dropped ...
> DETAIL: owner of table <tablename>
>
> Have you looked in the logs to see if something like that shows up in
> them?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

Instead of using drop user username, I used drop user "username" with double quotes, then the error message told me more detail about a specific user defined function. Then I was able to drop that function, then drop this user.

My next question,

How do I check the ownership of the functions. I can't tell the difference between system functions and the user defined functions.

I am sure in the future there will be times that I need to check the ownership of the functions but not allowed to drop users to see check the dependencies.


Thanks a lot,
Jessica


Tom Lane <tgl@sss.pgh.pa.us> wrote: Jessica Richard writes:
> I am trying to drop a user but it complained with the following message:
> ERROR: role "joe" cannot be dropped because some objects depend on it
> DETAIL: 1 objects in database Jan_test
> 1 objects in database Jan

> this user does not own any tables in those two databases. Is there any command I can use to check all the dependencies on this user?

Try to do the DROP ROLE within those databases. You'll get more detail
about owned objects within the current database, but from outside that
database the info isn't reachable ...

regards, tom lane


__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Jessica Richard <rjessil@yahoo.com> writes:
> I am sure in the future there will be times that I need to check the ownership of the functions but not allowed to drop users to see check the dependencies.

There's no need for a separate operation for that. Do

begin;
drop user foo;
rollback;

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Jessica Richard wrote:
> Instead of using drop user username, I used drop user "username" with
> double quotes, then the error message told me more detail about a
> specific user defined function. Then I was able to drop that function,
> then drop this user.

FYI you can use the commands DROP OWNED and REASSIGN OWNED to drop the
objects by a given role, and respectively assign them to someone else.
(8.2 only).

--
Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1", W 73º 13' 56.4"
"Ninguna manada de bestias tiene una voz tan horrible como la humana" (Orual)

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly