* Joseph Adams (joeyadams3.14159@gmail.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.
Note that, with any of this, you need to consider pooled database
connections. Unfortunately, it's still pretty expensive to establish a
new database connection to PG.
Thanks,
Stephen