From: | Mike Rylander <miker(at)purplefrog(dot)com> |
---|---|
To: | Michal Taborsky <michal(at)taborsky(dot)cz> |
Subject: | Re: Row-level security--is it possible? |
Date: | 2004-07-02 17:10:11 |
Message-ID: | cc45rq$2a1$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
<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
From | Date | Subject | |
---|---|---|---|
Next Message | Andy B | 2004-07-02 18:55:03 | Re: Enough RAM for entire Database.. cost aside, is this |
Previous Message | Bruno Wolff III | 2004-07-02 17:06:24 | Re: Row-level security--is it possible? |