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

From: Doug McNaught <doug(at)mcnaught(dot)org>
To: Michal Taborsky <michal(at)taborsky(dot)cz>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Row-level security--is it possible?
Date: 2004-07-02 14:30:57
Message-ID: 878ye2xyzi.fsf@asmodeus.mcnaught.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Maus 2004-07-02 15:02:30 how can Iconnect a Postgres database using odbc in c language?
Previous Message Maus 2004-07-02 14:27:33 how could I connect a Postgres database in c language using odbc?