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

2010/3/22 Robert Haas <robertmhaas(at)gmail(dot)com>:
> 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};

I though about it two, three years ago as lighter solution without
packages. Now we have more almost all what we need. Variables are
supported by parser (with last changes related to integration of main
parser to plpgsql).

just you can write

select * from tab where col = var;

there are not necessary some special syntax. And it is available from
all environments. Implementation for scalar type cannot be dificult
(maybe).

Regards
Pavel Stehule

>
> 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
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>