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=3D# DROP ROLE a485099;
ERROR: role "a485099" cannot be dropped because some objects depend on
it
DETAIL: access to database template1

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

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

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

template1=3D# DROP OWNED BY a485099;
REASSIGN OWNED

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


What am I missing?=20
Thanks, Robert



--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Unable to drop role

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 tryin=
g
> to execute a simple command.
>=20
>=20
> template1=3D# 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=3D# REASSIGN OWNED BY a485099 TO postgres;
> template1=3D# DROP OWNED BY a485099;

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

--=20
Álvaro Herrera
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Unable to drop role

"McGehee, Robert" 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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Unable to drop role

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]=20
Sent: Monday, August 23, 2010 8:11 PM
To: McGehee, Robert
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Unable to drop role=20

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

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

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

regards, tom lane


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Unable to drop role

"McGehee, Robert" 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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Unable to drop role

On Tue, Aug 24, 2010 at 07:36, Tom Lane wrote:
> I can see how "access to" might be read as specifically meaning "CONNECT
> privilege for".  Should we change this message from "access to whate=
ver"
> to "privileges for whatever", or some such wording?

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

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Unable to drop role

Excerpts from Tom Lane's message of mar ago 24 09:36:05 -0400 2010:
> "McGehee, Robert" 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!
>=20
> I wonder whether Robert's confusion doesn't stem from a poor choice
> of message wording:
>=20
> >> template1=3D# DROP ROLE a485099;
> >> ERROR: role "a485099" cannot be dropped because some objects depend=
on it
> >> DETAIL: access to database template1
>=20
> I can see how "access to" might be read as specifically meaning "CONNEC=
T
> 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.

--=20
Álvaro Herrera
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Unable to drop role

Alvaro Herrera 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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [HACKERS] Unable to drop role

On Tue, Aug 24, 2010 at 1:19 PM, Tom Lane wrote:
> Alvaro Herrera writes:
>> Happy to change it to whatever is deemed appropriate. =A0"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? =A0Code 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.

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

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin