Jonatan Evald Buus wrote: > Essentially what I'd like to do is implement row-level security (what Oracle > calls "Virtual Private Database") but as far as I can find the last time > this was discussed is several years ago and the general consensus ended up > being "use veil". > Veil seems overly like an complicated approach for something that (in > theroy) should be possible with a dynamic query rewrite using search and > replace prior to execution. I've never used veil myself, but I can't believe it's less effort to re-invent the wheel on this. The query-rewrite is what the views are doing. > Oracle's implementation seems quiete elegant for this, please see > http://www.oracle.com/technology/pub/articles/10gdba/week14_10gdba.html or > http://www.devshed.com/c/a/Oracle/RowLevel-Security-with-Virtual-Private-Database/for > examples. > > One other approach that I could think of, would be to create a view for > every table and use the view for accessing the data: > CREATE VIEW Transaction_Vw AS > SELECT * FROM Transaction_Tbl Txn > INNER JOIN User_Tbl U ON Txn.userid = U.id > WHERE U.name = 'CURRENT_USER' > However, can usage this view be enforced by the database by removing SELECT > priviliges from Transaction_Tbl? Yes, of course. This also seems to be what veil does for you. Beware - views are basically macros that rewrite your query and you can get unexpected results when e.g. inserting multiple rows at once. See mailing-list archives for examples. > Also, I suspect that performance would go down the drain if complex joins > are used? There could potentially be a lot of unnecessary joins to User_Tbl > if multiple "secure view" where joined together. > Is the planner capable of taking this into account and auto-magically > optimize the query? There's some overhead associated with rewriting the query, but the planner should cope just fine then. Which isn't to say there aren't cases where you couldn't write a better query from scratch, but the planner optimises rewritten queries as well as any other. > Are there any other alternatives to implementing row-level security that can > be enforced at the database level? Use veil. Someone else has gone to the trouble to think this through. If you write your own solution you'll only have 1 user (you). Even if veil is only used by its author there will at least be 2 of you. I frequently use a "my" schema with views that map to the relevant base tables ("my.clients" etc). However, that's not for applications that require some guarantee of security database-wide, it's as much about simplifying my queries. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
