Обсуждение: Virtual Private Database
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
On Sat, Apr 10, 2010 at 10:00 AM, Jean-Gérard Pailloncy <jg@rilk.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
On Sat, Apr 10, 2010 at 10:00 AM, Jean-Gérard Pailloncy <jg@rilk.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@postgresql.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; RETURNret;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.
On 4/10/10 7:00 AM, Jean-Gérard Pailloncy 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. See the Veil project, and the SEPostgres project: http://veil.projects.postgresql.org/ http://code.google.com/p/sepgsql/ -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com