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)
