Re: default privileges

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