Re: Proposal: access control jails (and introduction as aspiring GSoC student)

On Mon, Mar 22, 2010 at 9:39 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Joseph Adams (joeyadams3(dot)14159(at)gmail(dot)com) wrote:
>> I propose adding application-level access control to PostgreSQL via a
>> jails concept.  In a nutshell, a jail is created as part of the
>> database definition (typically exposing a free variable for the
>> current user).  When a jail is activated for a session, the only
>> accesses allowed are those indicated in the jail itself.  A jail
>> cannot be exited without closing the session.  If used properly, jails
>> make it possible to safely execute untrusted SQL code (though one may
>> not want to, citing the principle of least privilege).
>
> I guess my initial reaction to this is that you can use roles, views,
> and pl/pgsql (security definer) functions to achieve this.  This does
> have an interesting intersection with row-level security concepts and
> that's definitely a project that I'd like to see happen at some point in
> PG.  Not sure if you've considered this, but you can do a 'set role' at
> the start of a session and then use CURRENT_ROLE in view definitions and
> in other places.  You can also make it so that the user who is logging
> in (eg 'www-data') doesn't have any rights to anything, except the
> ability to 'set role' to other roles.

Sometimes it would be nice to conditionalize queries on a value other
than the authenticated role. I really wish we had some kind of SQL
variable support. Talking out of my rear end:

CREATE VARIABLE name AS type [GLOBAL | LOCAL]; -- local variables are
per-backend
SET VARIABLE name = value;
SELECT * FROM foo WHERE col = ${name};

We have had one previous request for the ability to limit the list of
queries that the user can issue to a fixed set, but it's not clear to
me that that's really all that useful. If that's what you want, you
might be better off putting an intermediate layer between the client
and the database - e.g. this kind of restriction could be implemented
in the connection pooler...

...Robert