Re: Virtual Private Database

On Sat, Apr 10, 2010 at 10:00 AM, Jean-Gérard Pailloncy <jg(at)rilk(dot)com> wrote:
> 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 ?

This is very similar to the design I've been thinking about for
row-level security.

Here is a pointer to a previous email thread on the topic of row-level security.

http://archives.postgresql.org/pgsql-hackers/2009-12/msg01095.php

Before row-level security can be implemented, we'd need to fix the
problem described here:

http://archives.postgresql.org/pgsql-hackers/2009-10/msg01346.php

With respect to sponsoring development of new features, it can
certainly be done. Any such feature could not at this point be added
any sooner than PostgreSQL 9.1, and I'd recommend that if you want to
see it in 9.1 you should try to get a contract with someone in place
in the next few months. To get a price, you'd need to contact a
PostgreSQL support/development company or an individual developer.
The following web page might give you some ideas where to start
looking.

http://www.postgresql.org/support/professional_support

There's sort of an understanding that we don't talk about contracts or
pricing on this list, so that the content remains technical rather
than commercial.

...Robert