Re: GRANT ON ALL IN schema - Mailing list pgsql-hackers

Josh Berkus wrote: <blockquote cite="mid:4A7DDB39.7020009@agliodbs.com" type="cite"><pre wrap="">I disagree here.
Whileit'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. </pre></blockquote> The * was reaction to Toms fears of standard adding GRANT ON ALL
withconflicting meaning, but I don't really see that as relevant point anymore (see my submission of the revised
patch).<br/><br /><blockquote cite="mid:4A7DDB39.7020009@agliodbs.com" type="cite"><pre wrap="">The answer as far as
thestandard is concerned is, why not make an
 
effort to get this into the standard? </pre></blockquote> We can try :) do we have somebody in the committee ?<br /><br
/><blockquotecite="mid:4A7DDB39.7020009@agliodbs.com" type="cite"><blockquote type="cite"><blockquote type="cite"><pre
wrap="">Andhow do we want to filter default acls ?     </pre></blockquote><pre wrap="">My opinion is that the best way
todo 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.   </pre></blockquote><pre wrap="">
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
intuitivelyunderstandible
 
to most users and would be, for that reason, less used and less useful. </pre></blockquote> I was discussing this with
Stephenand I agree now that schema based filtering is the best way. The role based filtering I proposed would mean user
wouldhave to have create role privilege to really take advantage of default acls, also it wouldn't really solve the
realworld problems which default acls aims to solve. I also agree on the point that GRANT ON ALL and DEFAULT PRIVILEGES
shouldhave same or similar filter.<br /><br /> So currently I see the next step being rewriting the patch for the ALTER
DEFAULTPRIVILEGES IN SCHEMA schemaname GRANT ... and leaving the functionality itself unchanged (with the exception of
havingVIEW as separate object which I will remove).<br /><br /><pre class="moz-signature" cols="72">-- 
 
Regards
Petr Jelinek (PJMODOS)</pre>