Обсуждение: Row-level security--is it possible?
Hello. We are currently facing a design issue, which I am a bit stuck with. We are talking about row-level access regulation. I'll make it clear with an example. Let there be a table of products: CREATE TABLE products ( Product_ID serial, Name text, Producer_ID int4 NOT NULL, PRIMARY KEY (Product_ID) ) We have two users Joe and Pete. The thing is, that Pete is just an intern and should have access only to products from a specific producer, while Joe should have unlimited access. Of course we could resolve it on application level (PHP/Apache), but that I don't want to do. My first idea was to create specific views for every user, like this: CREATE VIEW products_pete AS SELECT * FROM products WHERE Producer_ID=1; and CREATE VIEW products_joe AS SELECT * FROM products; But this is not very usable. My second thought was to create a rule for every user and attach these rules to products table. But alas! I was not able to make Postgres to apply only one of the rules, because SELECT rules do not accept qualification predicates (WHERE clause). Following definition does not work, though I believe it would solve my problem: CREATE RULE "_RETURN_pete" AS ON SELECT TO products WHERE current_user=pete DO INSTEAD SELECT * FROM products WHERE Producer_ID=1; Is there any chance it could be implemented like this? The third option that crossed my mind was to create permission function that would specifically say "yes, you have access to this row". We'd change this function CREATE OR REPLACE FUNCTION product_access(name, int4) RETURNS bool AS ' DECLARE product RECORD; BEGIN SELECT * FROM product WHERE Product_ID=$2; IF $1=\'pete\' THEN -- pete has access to producer with ID 1 IF product.Producer_ID=1 THEN RETURN true; ELSE RETURN false; END IF; ELSIF $1=\'joe\' THEN RETURN true; END IF; -- fail if unknown user RETURN false; END;' LANGUAGE 'plpgsql' IMMUTABLE; Then I'd use this function in a rule like this: CREATE RULE "_RETURN" AS ON SELECT TO products DO INSTEAD SELECT * FROM products WHERE product_access(current_user, id); I haven'r run any tests, but something tells me, that this would be incredibly slow. But it's the only solution that I can think of that should work. Has anyone solved similar issue? Can you point me to some info or how-to? I know that Oracle has this functionality through so called policies, which are similar to this, but they work fast. Thanks for any ideas. If I'll be able to solve this I promise to write some tutorial about it for techdocs (if it does not exist already). -- Michal Taborsky http://www.taborsky.cz
Michal Taborsky <michal@taborsky.cz> writes: > Hello. > > We are currently facing a design issue, which I am a bit stuck > with. We are talking about row-level access regulation. I'll make it > clear with an example. > > Let there be a table of products: > > CREATE TABLE products > ( > Product_ID serial, > Name text, > Producer_ID int4 NOT NULL, > PRIMARY KEY (Product_ID) > ) > > We have two users Joe and Pete. The thing is, that Pete is just an > intern and should have access only to products from a specific > producer, while Joe should have unlimited access. Of course we could > resolve it on application level (PHP/Apache), but that I don't want to > do. My first idea was to create specific views for every user, like > this: > > CREATE VIEW products_pete AS > SELECT * FROM products WHERE Producer_ID=1; > > and > > CREATE VIEW products_joe AS > SELECT * FROM products; > > But this is not very usable. But why not create a "products_restricted" view that uses the CURRENT_USER function to see who's running it? CREATE VIEW products_restricted AS SELECT * FROM products WHERE Producer_ID = get_producer_id(CURRENT_USER); [CURRENT_USER returns a string, so you would need to map it to your producer_id somehow.] -Doug
Doug McNaught wrote: > But why not create a "products_restricted" view that uses the > CURRENT_USER function to see who's running it? > > CREATE VIEW products_restricted AS > SELECT * FROM products WHERE Producer_ID = get_producer_id(CURRENT_USER); > > [CURRENT_USER returns a string, so you would need to map it to your > producer_id somehow.] This would work only for this case (limiting single producer to one user). But we want to have a bit more flexible system, so we'd be able define the restrictions freely (like "only producers 1 and 5 and price less than 100"). I'm sorry I did not mention this. -- Michal Taborsky http://www.taborsky.cz
Michal Taborsky <michal@taborsky.cz> writes: > Doug McNaught wrote: >> But why not create a "products_restricted" view that uses the >> CURRENT_USER function to see who's running it? >> CREATE VIEW products_restricted AS >> SELECT * FROM products WHERE Producer_ID = get_producer_id(CURRENT_USER); >> [CURRENT_USER returns a string, so you would need to map it to your >> producer_id somehow.] > > This would work only for this case (limiting single producer to one > user). But we want to have a bit more flexible system, so we'd be able > define the restrictions freely (like "only producers 1 and 5 and price > less than 100"). I'm sorry I did not mention this. Have you looked into set-returning functions for this? That would let you basically put whever logic you need into the function. -Doug
On Fri, Jul 02, 2004 at 17:32:07 +0200, Michal Taborsky <michal@taborsky.cz> wrote: > Doug McNaught wrote: > >But why not create a "products_restricted" view that uses the > >CURRENT_USER function to see who's running it? > > > >CREATE VIEW products_restricted AS > >SELECT * FROM products WHERE Producer_ID = get_producer_id(CURRENT_USER); > > > >[CURRENT_USER returns a string, so you would need to map it to your > >producer_id somehow.] > > This would work only for this case (limiting single producer to one > user). But we want to have a bit more flexible system, so we'd be able > define the restrictions freely (like "only producers 1 and 5 and price > less than 100"). I'm sorry I did not mention this. Then you can create a group table matching up producers and authorized users. The view should join the base table with the group table on producer and limit the results to users matching the "current_user". With appropiate indexes this should be fast.
<posted & mailed> Michal Taborsky wrote: > Doug McNaught wrote: >> But why not create a "products_restricted" view that uses the >> CURRENT_USER function to see who's running it? >> >> CREATE VIEW products_restricted AS >> SELECT * FROM products WHERE Producer_ID = get_producer_id(CURRENT_USER); >> >> [CURRENT_USER returns a string, so you would need to map it to your >> producer_id somehow.] > > This would work only for this case (limiting single producer to one > user). But we want to have a bit more flexible system, so we'd be able > define the restrictions freely (like "only producers 1 and 5 and price > less than 100"). I'm sorry I did not mention this. > How about something like: CREATE TABLE perms ( user text not null, producer int non null, constraint user_once_per_producer unique (user,producer) ); CREATE FUNCTION prods_for_user () RETURNS SETOF INT AS ' select producer from perms where user = CURRENT_USER; ' LANGUAGE SQL STABLE; INSERT INTO perms ('pete',100); INSERT INTO perms ('joe',100); INSERT INTO perms ('joe',101); ... CREATE VIEW restricted_products AS SELECT * FROM products where producer_id in (select prods_for_user()); -- END Now, mind you, I've not used set returning functions myself so the syntax may be off, but I think you can see the idea there. --miker