default privileges

Hi,

I create some default privileges, now i'm trying to drop those roles
but i get this error:
"""
mic=# drop role jcm;
ERROR: role "jcm" cannot be dropped because some objects depend on it
DETAIL: owner of default privileges on new relations belonging to
role jcm in schema public
"""

So i look for default privileges and try to REVOKE privileges for all
roles i found here
"""
mic=# \ddp
Default access privileges
Owner | Schema | Type | Access privileges
----------+--------+-------+-------------------
jcm | public | table |
jcm1 | public | table |
postgres | public | table |
rup | public | table |
(4 rows)
"""

but i still have the same error when trying to drop the role, so the
questions are:
what is the role that owned the default the one in FOR ROLE target or
the one in the "simplified grant", i guess it's the first one... but i
try REVOKE in all combinations and nothing works...

maybe the \ddp needs to show more info? or we need a CASCADE clause in
the DROP ROLE?

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> writes:
> I create some default privileges, now i'm trying to drop those roles
> but i get this error:

> mic=# drop role jcm;
> ERROR: role "jcm" cannot be dropped because some objects depend on it
> DETAIL: owner of default privileges on new relations belonging to
> role jcm in schema public

> So i look for default privileges and try to REVOKE privileges for all
> roles i found here

> mic=# \ddp
> Default access privileges
> Owner | Schema | Type | Access privileges
> ----------+--------+-------+-------------------
> jcm | public | table |
> jcm1 | public | table |
> postgres | public | table |
> rup | public | table |
> (4 rows)

Yeah. The problem here is that once you've created an entry in
pg_default_acl, there is no way to make it go away. You can reduce it
to an empty ACL list, as Jaime evidently did, but the entry is still
there and still has a dependency on the owner. This clearly wasn't
thought out well enough :-(.

I suggest that what we should do is arrange for the entry to be deleted
by an ALTER DEFAULT PRIVILEGES command that restores it to the default
state --- that is, empty for a schema-local default ACL, or equal to the
built-in privilege defaults for a global default ACL. Then, the way to
get rid of it is just to reverse whatever ALTER DEFAULT PRIVILEGES
commands you gave originally. Anything else will require inventing
special commands or special rules.

regards, tom lane

I wrote:
> Yeah. The problem here is that once you've created an entry in
> pg_default_acl, there is no way to make it go away.

Actually that's not true: you can get rid of it with DROP OWNED BY.
This fact is even documented in the ALTER DEFAULT PRIVILEGES manual
page:

If you wish to drop a role that has had its global default
privileges altered, it is necessary to use DROP OWNED BY first,
to get rid of the default privileges entry for the role.

Not sure if this is good enough or we need to provide some more-obvious
way of dealing with it.

regards, tom lane

On Sat, Apr 3, 2010 at 5:27 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I wrote:
>> Yeah.  The problem here is that once you've created an entry in
>> pg_default_acl, there is no way to make it go away.
>
> Actually that's not true: you can get rid of it with DROP OWNED BY.
> This fact is even documented in the ALTER DEFAULT PRIVILEGES manual
> page:
>
>        If you wish to drop a role that has had its global default
>        privileges altered, it is necessary to use DROP OWNED BY first,
>        to get rid of the default privileges entry for the role.
>

ah! i obviously didn't read the manual in detail :)

> Not sure if this is good enough or we need to provide some more-obvious
> way of dealing with it.
>

it's strange that a REVOKE doesn't clean what a GRANT did, and DROP
OWNED BY seems very dangerous (at least if i forgot to make REASSIGN
OWNED first).

we can let it as it is, but at least we can add a HINT for use DROP
OWNED BY having execute REASSIGN OWNED first...
or we can make what seems more reasonable, make the REVOKE clean the mess :)

if you prefer the second way i can try to prepare a patch

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> writes:
> On Sat, Apr 3, 2010 at 5:27 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Not sure if this is good enough or we need to provide some more-obvious
>> way of dealing with it.

> it's strange that a REVOKE doesn't clean what a GRANT did, and DROP
> OWNED BY seems very dangerous (at least if i forgot to make REASSIGN
> OWNED first).

Agreed --- I fixed it so that granting or revoking back to the default
permissions set will remove the entry.

regards, tom lane

On Sun, Apr 4, 2010 at 9:59 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> writes:
>> On Sat, Apr 3, 2010 at 5:27 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Not sure if this is good enough or we need to provide some more-obvious
>>> way of dealing with it.
>
>> it's strange that a REVOKE doesn't clean what a GRANT did, and DROP
>> OWNED BY seems very dangerous (at least if i forgot to make REASSIGN
>> OWNED first).
>
> Agreed --- I fixed it so that granting or revoking back to the default
> permissions set will remove the entry.
>

ah! now is working nice... thanks

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157