Re: Virtual Private Database

On Sat, Apr 10, 2010 at 10:00 AM, Jean-Gérard Pailloncy <jg(at)rilk(dot)com> wrote:
> Hello,
>
> 1) VPD: Virtual Private Database
> I would appreciate to have a new feature in PostgreSQL.
> This is an oracle-like feature that implement "Row Level Security".
> This feature may be emulated by using VIEW/RULE but this is very time
> consuming and error prone.
>
> I would appreciated to have an estimated of the faisability and the cost
> to implement it.
>
> 2) Description
> The feature may be implemented with a simple expression associated to the
> table.
>
> ALTER TABLE table_name ADD FILTER filter_name CHECK(expression);
> ALTER TABLE table_name DROP FILTER filter_name;
>
> Usage/example:
> ALTER TABLE filtered_table ADD FILTER tf_username
> CHECK(filtered_table.creator=user)
> SELECT * FROM filtered_table;
> will really do
> SELECT * FROM filtered_table WHERE filtered_table.creator=user;
>
> Same thing for INSERT, UDPATE, and DELETE
>
> UPDATE filtered_table SET b_column=1 WHERE a_column='a';
> wille really do
> UPDATE filtered_table SET b_column=1 WHERE a_column='a' and
> filtered_table.creator=user;
>
> In practice, the devs will create few function: my_login, my_logout,
> my_filter
> and the simple "filtered_table.creator=user" will be replace by ACL
> encapsulated in the function my_filter and add a triger to check data on
> INSERT, UDPATE.
> We could use veil to build a very efficient filter.
>
> 3) Question
> - Is it doable ?
> - Is it the sound way of doing it ?
> - Is it possible to have it in core ?
> - Is there a pgsql dev interested to implemented it ?
> - Is there other people interested in such feature ?
> - How much this will cost ?
> - With which delay ?
>
>
> Cordialement,
> Jean-Gérard Pailloncy
>
>
> --
> 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
>

This is quite similar to an idea I posted about not long ago called
access control jails (
http://archives.postgresql.org/pgsql-hackers/2010-03/msg00832.php ).
I encountered this very problem writing a simple web application
involving access control. There are a variety of ways to make
implementing access control easier, and I think copying off of Oracle
would be among the best ;-)

Disclaimer: I am not a PostgreSQL hacker, but a newbie with some
experience in other communities, absorbing what he can.

Access control jailing, as I conceived it, would not simply filter per
table, but would make it so all queries would be filtered. If used
correctly, it would even be safe to execute untrusted SQL (though it
might not be advisable).

I looked at the Veil demo application a tiny bit, and the only thing I
drew from it was the impression that it makes things more complicated,
not less :( Then again, it may just be the example demonstrating a
lot of different features at once.

One problem that ought to be addressed for any of these ideas is how
to do connection-local variables. For instance:

> UPDATE filtered_table SET b_column=1 WHERE a_column='a';
> wille really do
> UPDATE filtered_table SET b_column=1 WHERE a_column='a' and
> filtered_table.creator=user;

Here, what is "=user" referring to? I suppose it is a variable that
is set not long after the session starts and only applies to that
session? PostgreSQL has temporary tables and such, but you can't
reference them until they're already created. Hence, I don't think
PostgreSQL elegantly supports free variables that are bound
temporarily per connection. There are GUCs and such, but using them
for this purpose is far from elegant, if I understand correctly.

Another problem is that session-local context doesn't go well with
connection pooling, so you might need some workaround like passing
context IDs back and forth.

That's my own summary of the discussion about access control jails linked above.

By the way, here's a hack to bind a free variable to a session:

CREATE FUNCTION get_user_id() RETURNS INT AS $$
DECLARE
ret INT;
BEGIN
SELECT INTO ret id FROM user_id_tbl;
RETURN ret;
END
$$ LANGUAGE 'plpgsql';

Then, per-session:

CREATE TEMPORARY TABLE user_id_tbl (id INT);
INSERT INTO user_id_tbl VALUES (5);
SELECT get_user_id();

It relies on plpgsql not complaining about user_id_tbl not existing at
creation time. What this trick allows one to do is set the user ID
once (e.g. after connecting), then views and such that call
get_user_id() will have the appropriate user ID without needing to
specify it per-query.

I'm curious: is this trick a good idea? Does connection pooling play
well with temporary tables (and thus this trick)? Could it result in
substantial slowdowns (I don't see why it should, since get_user_id()
needs to be called once per query that uses it)? I guess creating a
temporary table every connection has the potential to be slow.