Re: GRANT ON ALL IN schema

Josh Berkus wrote:
> I disagree here. While it's nice to be MySQL-compatible, a glob "*" is
> not at all consistent with other SQL syntax, whereas "ALL" and "GRANT ON
> ALL IN SCHEMA <schema>" are.
>
The * was reaction to Toms fears of standard adding GRANT ON ALL with
conflicting meaning, but I don't really see that as relevant point
anymore (see my submission of the revised patch).

> The answer as far as the standard is concerned is, why not make an
> effort to get this into the standard?
>
We can try :) do we have somebody in the committee ?

>>> And how do we want to filter default acls ?
>>>
>> My opinion is that the best way to do this would be ALTER DEFAULT
>> PRIVILEGES GRANT ..., without any additional filters, it would just
>> affect the role which runs this command. I think this is best solution
>> because ALTER SCHEMA forces creation of many schemas that might not have
>> anything to do with structure of the database (if you want different
>> default privileges for different things). Also having default privileges
>> per role with filters on various things will IMHO create more confusion
>> than good. And finally if somebody wants to have different default
>> privileges for different things than he can just create child roles with
>> different default privileges and use SET SESSION AUTHORIZATION to switch
>> between them.
>>
>
> I'm not sure if I'm agreeing or disagreeing with you here, but I'll say
> that it doesn't help a user have a consistent setup for assigning
> privileges. GRANT ON ALL working per *schema* while ALTER DEFAULT
> working per *role* will just create confusion and not improve the
> managability of privileges in PostgreSQL. We need a DEFAULT and a GRANT
> ALL statement which can be executed on the same scope so that users can
> easily set up a coherent access control scheme.
>
> For my part, I *do* use schema to control my security context for
> database objects; I find that it's a convenience to be able to take
> objects which a role has no permissions on out of its visibility
> (through search_path) as well. And schema-based security mentally maps
> to directory-based permissions, which unix sysadmins instinctively
> understand. So I think that a form of GRANT ALL/DEFAULT which supported
> schema-scoping would be useful to a *lot* more people than one which didn't.
>
> I do understand that other scopes (such as scoping by object owner) are
> equally valid and maybe more consistent with the SQL permissions model.
> However, I think that role-scoping is not as intuitively understandible
> to most users and would be, for that reason, less used and less useful.
>
I was discussing this with Stephen and I agree now that schema based
filtering is the best way. The role based filtering I proposed would
mean user would have to have create role privilege to really take
advantage of default acls, also it wouldn't really solve the real world
problems which default acls aims to solve. I also agree on the point
that GRANT ON ALL and DEFAULT PRIVILEGES should have same or similar filter.

So currently I see the next step being rewriting the patch for the ALTER
DEFAULT PRIVILEGES IN SCHEMA schemaname GRANT ... and leaving the
functionality itself unchanged (with the exception of having VIEW as
separate object which I will remove).

--
Regards
Petr Jelinek (PJMODOS)