Re: [PATCH] DefaultACLs

Josh Berkus wrote:
>> But if I understood Tom's suggestions correctly then his approach does
>> not solve this at all since every one of those users with CREATE TABLE
>> privileges would have to also set same DEFAULT PRIVILEGES and the dba
>> would have no say in the matter.
>>
>
> This latter approach benefits nobody. If default's can't be set by the
> DBA centrally, the feature is useless.
>
I agree, however I assume I understood Tom properly since he didn't reply.

So I've been working on solution with which I am happy with (does not
mean anybody else will be also though).
I created a new version with syntax devised by Tom and one which is user
centric, but also one with which DBA can control default privileges.
The attached patch adds syntax in this format:
ALTER DEFAULT PRIVILEGES [ IN SCHEMA schema_name(s) ] [ FOR ROLE
role_name(s) ] GRANT privileges ON object_type TO role(s);
Obviously it sets default privileges for new objects of given object
type created by role(s) specified using FOR ROLE (is that syntax ok?)
clause and inside specified schema(s).
If user omits IN SCHEMA it applies database wide. Database wide settings
are used only if there is nothing specified for current schema (ie no
cascading/inheritance).
If FOR ROLE is omitted then the privileges are set for current role.
Only superusers and users with ADMIN privilege (we might want to add
specific privilege for this but ADMIN seems suitable to me) granted on
the role can use FOR ROLE clause.
The order of FOR ROLE and IN SCHEMA clauses does not matter.

Some of my thoughts on the changed behavior of the patch:
There is no need to be schema owner anymore in this implementation since
the privileges are handled quite differently.
There are no longer issues about who should be grantor (discussed on IRC
only, there was problem that schema owner as grantor didn't seem logical
and we didn't know owner at the time we created default privileges).
Also there is no longer a problem with what should be template for
privileges because we now know the owner of the object at default
privileges creation time (which we didn't before as it was all schema
based) so we can use standard template as used by GRANT.
The whole thing is more consistent with GRANT.
The patch is also a bit smaller :)
It's not as easy to do the original idea of setting default privileges
for schema for all users with CREATE privilege on schema but it can
still be done, one just have to update default privileges every time
somebody is granted that privilege, and DBA can still have control over
it all.

Hopefully this will at least inspire some more discussion on the matter.

--
Regards
Petr Jelinek (PJMODOS)