Re: Row-level security--is it possible?

<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