From: Josh Berkus <josh@agliodbs.com>
To: Petr Jelinek <pjmodos@pjmodos.net>
Cc: Stephen Frost <sfrost@snowman.net>
Cc: Andrew Dunstan <andrew@dunslane.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Nikhil Sontakke <nikhil.sontakke@enterprisedb.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: GRANT ON ALL IN schema
Date: Sat, 08 Aug 2009 13:08:25 -0700
Message-ID: <4A7DDB39.7020009@agliodbs.com> (raw)
In-Reply-To: <4A7CE01D.7060604@pjmodos.net>
References: <4A38A956.8080600@pjmodos.net>
<4A4DE104.8090605@pjmodos.net>
<a301bfd90907170254re7dd52es9fedb007376d8055@mail.gmail.com>
<4A6059B4.5010004@pjmodos.net>
<4A607997.3030305@pjmodos.net>
<603c8f070907191628r6929055coe7627726a33ed143@mail.gmail.com>
<a301bfd90907192312s17e1b16cy3bcd6e0876960c04@mail.gmail.com>
<603c8f070908050932m19e7db65u9dab6d8001c5a237@mail.gmail.com>
<20810.1249490458@sss.pgh.pa.us>
<4A79B7E5.1020509@dunslane.net>
<20090806152039.GO23840@tamriel.snowman.net>
<4A7C77B9.1050008@pjmodos.net>
<4A7CE01D.7060604@pjmodos.net>
> Well, since I've written the patch I am for it :) Probably with that
> GRANT ON * and GRANT ON schema.* as it has indeed very low probability
> that something like that will be in standard with different meaning and
> also it's mysql compatible (which is the only db currently having this
> feature I think), even if that's very little plus.
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 answer as far as the standard is concerned is, why not make an
effort to get this into the standard?
>> 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.
--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: josh@agliodbs.com, pjmodos@pjmodos.net, sfrost@snowman.net, andrew@dunslane.net, tgl@sss.pgh.pa.us, robertmhaas@gmail.com, nikhil.sontakke@enterprisedb.com
Subject: Re: GRANT ON ALL IN schema
In-Reply-To: <4A7DDB39.7020009@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox