To: Robert Haas <robertmhaas@gmail.com>
cc: Josh Berkus <josh@agliodbs.com>, Petr Jelinek <pjmodos@pjmodos.net>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: GRANT ON ALL IN schema 
In-reply-to: <603c8f070908051221u676e9d68ya42f5099aafb2e8e@mail.gmail.com> 
References: <4A37BF63.50008@pjmodos.net> <4A37E122.8070303@pjmodos.net>
	<4A38A956.8080600@pjmodos.net> <4A4DE104.8090605@pjmodos.net>
	<a301bfd90907170254re7dd52es9fedb007376d8055@mail.gmail.com>
	<4A6059B4.5010004@pjmodos.net> <4A607997.3030305@pjmodos.net>
	<21542.1249492707@sss.pgh.pa.us> <4A79D60D.1090900@agliodbs.com>
	<603c8f070908051221u676e9d68ya42f5099aafb2e8e@mail.gmail.com>
Comments: In-reply-to Robert Haas <robertmhaas@gmail.com>
	message dated "Wed, 05 Aug 2009 15:21:47 -0400"
Date: Wed, 05 Aug 2009 15:40:26 -0400
Message-ID: <28123.1249501226@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Robert Haas <robertmhaas@gmail.com> writes:
> I have one database that is set up with a reporting user (read only on
> everything).  It requires constant maintenance.  Every time an object
> is added or deleted (or dropped and recreated, like a view, which I do
> ALL THE TIME to work around the inability to add/remove columns) the
> permissions get shot to hell.  I finally crontabbed a script that
> fixes it every 20 minutes.  I had another database where I tried to do
> some real permission separation and it was just a huge pain in the
> ass.

> Grant on all isn't gonna fix these problems completely, but it's a
> start.  The DefaultACL stuff is another important step in the right
> direction.

Seems like default ACLs, not grant-on-all, is what you want for that.

The idea of better support for plpgsql-driven granting isn't going
to compete with default ACLs, but it does compete with grant-on-all.
So that's why I'm thinking we ought to take a harder look at that
before adding nonstandard extensions to GRANT.

Josh's position that "this should be standard SQL" is nonsense, or
at least he ought to be making that argument to the standards committee
not us.  It *isn't* standard, and therefore it's up to us to decide how
we want to expose the facility.  What's more, syntax extensions to GRANT
are a pretty risky way to do it: what if the SQL committee sees the
light and SQL:201x includes a GRANT extension, only it conflicts with
ours?

If we want something built-in, maybe providing some prefab plpgsql
functions is the way to go.  But we'd have to arrive at a consensus
on what best practice of that form looks like.

			regards, tom lane