Row-level security--is it possible? - Mailing list pgsql-general

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