Thread: dropping role w/dependent objects
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? I've removed all of the users from this group, turned up server logging to debug5, and searched docs to no avail. TIA. Ed
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
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
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: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 > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >
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
Attachment
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. +