dropping role w/dependent objects

Ed L. wrote:

This is pgsql 8.2.3:

% psql -c "drop role mygroup"
ERROR: role "mygroup" cannot be dropped because some objects
depend on it
DETAIL: 227 objects in this database

How do I identify what these dependent objects are?

Hum, this seems to be a bug. The objects are supposed to be logged in
the DETAIL field of that error, but when the count is too high
apparently the detail is being clobbered and rewritten with a count
instead. I would have expected that it listed some of those objects,
say the first 40.

Note that you can give the objects owned by that role to someone else
with REASSIGN OWNED, and drop the objects with DROP OWNED (note that
they act differently regarding grants; see the docs)

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Wednesday April 4 2007 4:35 pm, Alvaro Herrera wrote:

Note that you can give the objects owned by that role to
someone else with REASSIGN OWNED, and drop the objects with
DROP OWNED (note that they act differently regarding grants;
see the docs)

Yes, but how do identify what they are so that I know if I want
to DROP OWNED them?

TIA.
Ed

On Wednesday April 4 2007 4:39 pm, Ed L. wrote:

On Wednesday April 4 2007 4:35 pm, Alvaro Herrera wrote:

Note that you can give the objects owned by that role to
someone else with REASSIGN OWNED, and drop the objects with
DROP OWNED (note that they act differently regarding grants;
see the docs)

Yes, but how do identify what they are so that I know if I
want to DROP OWNED them?

The REASSIGN OWNED appears to be insufficient:

% psql -c "reassign owned by mygroup to mydba"
REASSIGN OWNED
% psql -c "drop group mygroup"
ERROR: role "mygroup" cannot be dropped because some objects
depend on it
DETAIL: 225 objects in this database

Thanks,
Ed

Ed--

check out REASSIGN OWNED
http://www.postgresql.org/docs/current/static/sql-reassign-owned.html

then use CASCADE option of DROP OWNED to drop dependents
e.g.
DROP OWNED BY FUBAR CASCADE
http://www.postgresql.org/docs/current/static/sql-drop-owned.html

HTH,
Martin --

This email message and any files transmitted with it contain confidential
information intended only for the person(s) to whom this email message is
addressed. If you have received this email message in error, please notify
the sender immediately by telephone or email and destroy the original
message without making a copy. Thank you.

----- Original Message -----
From: "Ed L." <pgsql@bluepolka.net>
To: "Alvaro Herrera" <alvherre@commandprompt.com>
Cc: <pgsql-general@postgresql.org>
Sent: Wednesday, April 04, 2007 6:41 PM
Subject: Re: [GENERAL] dropping role w/dependent objects

On Wednesday April 4 2007 4:41 pm, Ed L. wrote:

On Wednesday April 4 2007 4:39 pm, Ed L. wrote:

On Wednesday April 4 2007 4:35 pm, Alvaro Herrera wrote:

Note that you can give the objects owned by that role to
someone else with REASSIGN OWNED, and drop the objects
with DROP OWNED (note that they act differently regarding
grants; see the docs)

Yes, but how do identify what they are so that I know if I
want to DROP OWNED them?

The REASSIGN OWNED appears to be insufficient:

% psql -c "reassign owned by mygroup to mydba"
REASSIGN OWNED
% psql -c "drop group mygroup"
ERROR: role "mygroup" cannot be dropped because some objects
depend on it
DETAIL: 225 objects in this database

I did a before and after dump to compare. The objects it is
complaining about are GRANTs to that group. Hmm... seems
like you shouldn't have to drop every grant for a group
you're dropping.

Thanks,
Ed

Ed L. wrote:

On Wednesday April 4 2007 4:35 pm, Alvaro Herrera wrote:

Note that you can give the objects owned by that role to
someone else with REASSIGN OWNED, and drop the objects with
DROP OWNED (note that they act differently regarding grants;
see the docs)

Yes, but how do identify what they are so that I know if I want
to DROP OWNED them?

There's no way AFAICT, short of peeking the catalogs (or
information_schema). Try pg_shdepend.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Wednesday April 4 2007 4:48 pm, Alvaro Herrera wrote:

Yes, but how do identify what they are so that I know if I
want to DROP OWNED them?

There's no way AFAICT, short of peeking the catalogs (or
information_schema). Try pg_shdepend.

I guess if the bug were fixed, it'd be a non-issue.

Thanks.
Ed

Ed L. wrote:

On Wednesday April 4 2007 4:39 pm, Ed L. wrote:

On Wednesday April 4 2007 4:35 pm, Alvaro Herrera wrote:

Note that you can give the objects owned by that role to
someone else with REASSIGN OWNED, and drop the objects with
DROP OWNED (note that they act differently regarding grants;
see the docs)

Yes, but how do identify what they are so that I know if I
want to DROP OWNED them?

The REASSIGN OWNED appears to be insufficient:

% psql -c "reassign owned by mygroup to mydba"
REASSIGN OWNED
% psql -c "drop group mygroup"
ERROR: role "mygroup" cannot be dropped because some objects
depend on it
DETAIL: 225 objects in this database

Right. REASSIGN OWNED changes ownership to someone else, but leaves
grants untouched. DROP OWNED revokes the permissions the role might
have. This is the intended behavior.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Ed L. wrote:

On Wednesday April 4 2007 4:48 pm, Alvaro Herrera wrote:

Yes, but how do identify what they are so that I know if I
want to DROP OWNED them?

There's no way AFAICT, short of peeking the catalogs (or
information_schema). Try pg_shdepend.

I guess if the bug were fixed, it'd be a non-issue.

Sure, please submit a patch. It should not be too difficult.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Wednesday April 4 2007 5:02 pm, Alvaro Herrera wrote:

I guess if the bug were fixed, it'd be a non-issue.

Sure, please submit a patch.  It should not be too difficult.

Perhaps this could be added to the TODO list? I won't get to it
anytime soon.

Ed

Ed L. wrote:

On Wednesday April 4 2007 5:02 pm, Alvaro Herrera wrote:

I guess if the bug were fixed, it'd be a non-issue.

Sure, please submit a patch. ?It should not be too difficult.

Perhaps this could be added to the TODO list? I won't get to it
anytime soon.

Yes. What should the TODO text be?

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Wednesday April 4 2007 5:37 pm, Bruce Momjian wrote:

Perhaps this could be added to the TODO list? I won't get
to it anytime soon.

Yes. What should the TODO text be?

See if the attached patch is acceptable. If not, perhaps the
TODO text should be:

Enable end user to identify dependent objects when the following
error is encountered:

ERROR: role "mygroup" cannot be dropped because some objects
depend on it
DETAIL: 227 objects in this database

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.

---------------------------------------------------------------------------

Ed L. wrote:

On Wednesday April 4 2007 5:37 pm, Bruce Momjian wrote:

Perhaps this could be added to the TODO list? I won't get
to it anytime soon.

Yes. What should the TODO text be?

See if the attached patch is acceptable. If not, perhaps the
TODO text should be:

Enable end user to identify dependent objects when the following
error is encountered:

ERROR: role "mygroup" cannot be dropped because some objects
depend on it
DETAIL: 227 objects in this database

[ Attachment, skipping... ]

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +