Message-ID: <4A79D60D.1090900@agliodbs.com>
Date: Wed, 05 Aug 2009 11:57:17 -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: Tom Lane <tgl@sss.pgh.pa.us>
CC: Petr Jelinek <pjmodos@pjmodos.net>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: GRANT ON ALL IN schema
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>
In-Reply-To: <21542.1249492707@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

Tom,

> I took a quick look at this version of the patch.  Other than the
> already-mentioned question of whether we really want to create a
> distinction between tables and views in GRANT, there's not that
> much there to criticize.

It's pretty common to have a database where there are some users who
have permissions on views but not on the base tables.  So that would be
an argument for separating the two.

On the other hand, it's not a very persuasive argument; in general, such
databases have complex enough security rules that GRANT ALL ON is too
simple for them.

So, overall, I'd tend to say that we're better off including views and
tables in the same GRANT ALL.  The purpose of this is to be a simple
approach for simple cases, no?

>  I do have a feeling that the implementation
> is a bit too narrowly focused on the "stuff IN SCHEMA foo" case;
> if we were ever to add other filtering options it seems like we'd
> have to rip all this code out and start over.  But I don't have any
> immediate ideas on what it should look like instead.

Well, schemas do make a good grouping set for objects of different
security contexts; they are certainly more reliable than name fragments
(as would be supported by a regex scheme).  The main defect of schemas
is the well-documented issues with managing search_path.

> Other than that I don't have much to say.  I wonder though if this
> approach isn't sort of a dead-end, and we should instead look at
> making it easier to build sql or plpgsql functions for doing bulk
> grants with arbitrary selection conditions.

Right now we have a situation where most web developers aren't using
ROLEs *at all* because they are too complex for them to bother with.  I
literally couldn't count the number of production applications I've run
across which connect to Postgres as the superuser.  We need a
dead-simple approach for the entry-level DB users, and I haven't heard
one which is simpler or more approachable than the GRANT ALL + SET
DEFAULT approach.  With that approach, setting up a 3-role, table only
database to have the right security is only 6 statements.

I agree that we should also provide examples of how to do this by script
in the docs, and maybe even some tools on pgFoundry.  But those cover
the sophisticated users.  For the simple users, we need a dead-simple tool.

-- 
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com