Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 5B95A633120
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Sat,  8 Aug 2009 17:09:37 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 57256-09
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Sat,  8 Aug 2009 20:09:26 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from outmail129155.authsmtp.com (outmail129155.authsmtp.com
	[62.13.129.155])
	by mail.postgresql.org (Postfix) with ESMTP id 8DA0C632E88
	for <pgsql-hackers@postgresql.org>;
	Sat,  8 Aug 2009 17:09:26 -0300 (ADT)
Received: from mail-c187.authsmtp.com (mail-c187.authsmtp.com [62.13.128.33])
	by punt7.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id
	n78K8SQF006571; Sat, 8 Aug 2009 21:08:28 +0100 (BST)
Received: from Sidney-Stratton.local (adsl-63-195-55-98.dsl.snfc21.pacbell.net
	[63.195.55.98]) (authenticated bits=0)
	by mail.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id n78K8PR6049405;
	Sat, 8 Aug 2009 21:08:26 +0100 (BST)
Message-ID: <4A7DDB39.7020009@agliodbs.com>
Date: Sat, 08 Aug 2009 13:08:25 -0700
From: Josh Berkus <josh@agliodbs.com>
Organization: PostgreSQL Experts Inc.
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US;
	rv:1.9.1b3pre) Gecko/20090223 Thunderbird/3.0b2
MIME-Version: 1.0
To: Petr Jelinek <pjmodos@pjmodos.net>
CC: Stephen Frost <sfrost@snowman.net>, Andrew Dunstan <andrew@dunslane.net>,
	Tom Lane <tgl@sss.pgh.pa.us>, Robert Haas <robertmhaas@gmail.com>,
	Nikhil Sontakke <nikhil.sontakke@enterprisedb.com>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: GRANT ON ALL IN schema
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>
In-Reply-To: <4A7CE01D.7060604@pjmodos.net>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
X-Server-Quench: 3cb94344-8457-11de-8e8d-001185d377ca
X-AuthRoute: OCdyZgscClZXSx8a IioLCC5HRQ8+YBZL BAkGMA9GIUINWEQO
	c1ACfh16LVJbHwkB CnYJWl5UWFdzUS1z aBRQZABDZ09QVg11
	Uk1LR01SWltvCWcJ ZnwYUh17cwVDNnpw Z0UsXSMJVUB5cUJg
	SkpREnAHZDM1dWhK WBRFdwNVcQtPKhxC bQMuGhFYa3VsHgsT
	PCIJBAV5Eg92Dhgd ZSdFBHY2CWctViU3 Rx0HFF1f
X-Authentic-SMTP: 61633136333939.squirrel.dmpriest.net.uk:1849/Kp
X-Report-SPAM: If SPAM / abuse - report it at: http://www.authsmtp.com/abuse
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200908/662
X-Sequence-Number: 143305


> 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