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)
