Обсуждение: Unable to drop role

Поиск
Список
Период
Сортировка

Unable to drop role

От
"McGehee, Robert"
Дата:
PostgreSQL developers,
I am unable to drop a role with presumably no object dependencies or
connection access on my PostgreSQL 8.4.2 installation (64-bit OSX). Any
help would be greatly appreciated as I've spent way too much time trying
to execute a simple command.


template1=# DROP ROLE a485099;
ERROR:  role "a485099" cannot be dropped because some objects depend on
it
DETAIL:  access to database template1

template1=# \du a485099;
           List of roles
 Role name | Attributes | Member of
-----------+------------+-----------
 a485099   |            | {}

template1=# REVOKE CONNECT ON DATABASE template1 FROM a485099;
REVOKE

template1=# REASSIGN OWNED BY a485099 TO postgres;
REASSIGN OWNED

template1=# DROP OWNED BY a485099;
REASSIGN OWNED

template1=# DROP ROLE a485099;
ERROR:  role "a485099" cannot be dropped because some objects depend on
it
DETAIL:  access to database template1


What am I missing?
Thanks, Robert



Re: Unable to drop role

От
Alvaro Herrera
Дата:
Excerpts from McGehee, Robert's message of lun ago 23 13:29:47 -0400 2010:
> PostgreSQL developers,
> I am unable to drop a role with presumably no object dependencies or
> connection access on my PostgreSQL 8.4.2 installation (64-bit OSX). Any
> help would be greatly appreciated as I've spent way too much time trying
> to execute a simple command.
>
>
> template1=# DROP ROLE a485099;
> ERROR:  role "a485099" cannot be dropped because some objects depend on
> it
> DETAIL:  access to database template1

So what does \l say about template1?

> template1=# REASSIGN OWNED BY a485099 TO postgres;
> template1=# DROP OWNED BY a485099;

IIRC neither REASSIGN OWNED nor DROP OWNED do anything about access to
databases.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: Unable to drop role

От
Tom Lane
Дата:
"McGehee, Robert" <Robert.McGehee@geodecapital.com> writes:
> template1=# DROP ROLE a485099;
> ERROR:  role "a485099" cannot be dropped because some objects depend on it
> DETAIL:  access to database template1

> template1=# REVOKE CONNECT ON DATABASE template1 FROM a485099;
> REVOKE

CONNECT is not the only possible privilege.  Try REVOKE ALL.

            regards, tom lane

Re: Unable to drop role

От
Tom Lane
Дата:
"McGehee, Robert" <Robert.McGehee@geodecapital.com> writes:
> Thanks Tom and Alvaro for clearing up my confusion.
> \l showed that a485099 had both (C)reate and (T)emporary access.
> Revoking those allowed me to drop the role. Thanks for the help!

I wonder whether Robert's confusion doesn't stem from a poor choice
of message wording:

>> template1=# DROP ROLE a485099;
>> ERROR:  role "a485099" cannot be dropped because some objects depend on it
>> DETAIL:  access to database template1

I can see how "access to" might be read as specifically meaning "CONNECT
privilege for".  Should we change this message from "access to whatever"
to "privileges for whatever", or some such wording?

            regards, tom lane

Re: Unable to drop role

От
"McGehee, Robert"
Дата:
Thanks Tom and Alvaro for clearing up my confusion.

\l showed that a485099 had both (C)reate and (T)emporary access.
Revoking those allowed me to drop the role. Thanks for the help!

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, August 23, 2010 8:11 PM
To: McGehee, Robert
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Unable to drop role

"McGehee, Robert" <Robert.McGehee@geodecapital.com> writes:
> template1=# DROP ROLE a485099;
> ERROR:  role "a485099" cannot be dropped because some objects depend
on it
> DETAIL:  access to database template1

> template1=# REVOKE CONNECT ON DATABASE template1 FROM a485099;
> REVOKE

CONNECT is not the only possible privilege.  Try REVOKE ALL.

            regards, tom lane


Re: Unable to drop role

От
Alex Hunsaker
Дата:
On Tue, Aug 24, 2010 at 07:36, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I can see how "access to" might be read as specifically meaning "CONNECT
> privilege for".  Should we change this message from "access to whatever"
> to "privileges for whatever", or some such wording?

+1, There have been a few times I found that message not very um helpful.

Re: Unable to drop role

От
Alvaro Herrera
Дата:
Excerpts from Tom Lane's message of mar ago 24 09:36:05 -0400 2010:
> "McGehee, Robert" <Robert.McGehee@geodecapital.com> writes:
> > Thanks Tom and Alvaro for clearing up my confusion.
> > \l showed that a485099 had both (C)reate and (T)emporary access.
> > Revoking those allowed me to drop the role. Thanks for the help!
>
> I wonder whether Robert's confusion doesn't stem from a poor choice
> of message wording:
>
> >> template1=# DROP ROLE a485099;
> >> ERROR:  role "a485099" cannot be dropped because some objects depend on it
> >> DETAIL:  access to database template1
>
> I can see how "access to" might be read as specifically meaning "CONNECT
> privilege for".  Should we change this message from "access to whatever"
> to "privileges for whatever", or some such wording?

Code is here:
            else if (deptype == SHARED_DEPENDENCY_ACL)
                appendStringInfo(descs, _("access to %s"), objdesc);
in StoreObjectDescription().

Happy to change it to whatever is deemed appropriate.  "privileges for %s"
sounds good; I'll do that unless somebody comes up with a better idea
which outvotes this one.

Backpatch all the way to 8.1?  Code doesn't exist prior to that.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: Unable to drop role

От
Tom Lane
Дата:
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Happy to change it to whatever is deemed appropriate.  "privileges for %s"
> sounds good; I'll do that unless somebody comes up with a better idea
> which outvotes this one.

> Backpatch all the way to 8.1?  Code doesn't exist prior to that.

I'd vote for fixing it in HEAD and perhaps 9.0, but not earlier.
Changing this will cause problems for translators, and it doesn't
seem important enough to mess up stable-branch translations.

            regards, tom lane

Re: [HACKERS] Unable to drop role

От
Robert Haas
Дата:
On Tue, Aug 24, 2010 at 1:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>> Happy to change it to whatever is deemed appropriate.  "privileges for %s"
>> sounds good; I'll do that unless somebody comes up with a better idea
>> which outvotes this one.
>
>> Backpatch all the way to 8.1?  Code doesn't exist prior to that.
>
> I'd vote for fixing it in HEAD and perhaps 9.0, but not earlier.
> Changing this will cause problems for translators, and it doesn't
> seem important enough to mess up stable-branch translations.

+1.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

Re: Unable to drop role

От
Tom Lane
Дата:
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Code is here:
>             else if (deptype == SHARED_DEPENDENCY_ACL)
>                 appendStringInfo(descs, _("access to %s"), objdesc);
> in StoreObjectDescription().

> Happy to change it to whatever is deemed appropriate.  "privileges for %s"
> sounds good; I'll do that unless somebody comes up with a better idea
> which outvotes this one.

If you're not able to commit this in the next couple of hours, please
let me know and I'll do it.  RC1 wraps tonight.

            regards, tom lane

Re: Unable to drop role

От
Alvaro Herrera
Дата:
Excerpts from Tom Lane's message of jue ago 26 14:59:43 -0400 2010:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > Code is here:
> >             else if (deptype == SHARED_DEPENDENCY_ACL)
> >                 appendStringInfo(descs, _("access to %s"), objdesc);
> > in StoreObjectDescription().
>
> > Happy to change it to whatever is deemed appropriate.  "privileges for %s"
> > sounds good; I'll do that unless somebody comes up with a better idea
> > which outvotes this one.
>
> If you're not able to commit this in the next couple of hours, please
> let me know and I'll do it.  RC1 wraps tonight.

Ok, I'll commit it soon, thanks for the notice.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support