Re: [HACKERS] Unable to drop role

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

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(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

"McGehee, Robert" <Robert(dot)McGehee(at)geodecapital(dot)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

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(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Monday, August 23, 2010 8:11 PM
To: McGehee, Robert
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] Unable to drop role

"McGehee, Robert" <Robert(dot)McGehee(at)geodecapital(dot)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

"McGehee, Robert" <Robert(dot)McGehee(at)geodecapital(dot)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

On Tue, Aug 24, 2010 at 07:36, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.

Excerpts from Tom Lane's message of mar ago 24 09:36:05 -0400 2010:
> "McGehee, Robert" <Robert(dot)McGehee(at)geodecapital(dot)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(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre(at)commandprompt(dot)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

On Tue, Aug 24, 2010 at 1:19 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)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

Alvaro Herrera <alvherre(at)commandprompt(dot)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

Excerpts from Tom Lane's message of jue ago 26 14:59:43 -0400 2010:
> Alvaro Herrera <alvherre(at)commandprompt(dot)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(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support