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

Michal Taborsky <michal(at)taborsky(dot)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