473,325 Members | 2,308 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Row-level security--is it possible?

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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #1
5 4859
Michal Taborsky <mi****@taborsky.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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #2
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.

--
Michal Taborsky
http://www.taborsky.cz
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #3
Michal Taborsky <mi****@taborsky.cz> writes:
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.


Have you looked into set-returning functions for this? That would let
you basically put whever logic you need into the function.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #4
On Fri, Jul 02, 2004 at 17:32:07 +0200,
Michal Taborsky <mi****@taborsky.cz> 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.


Then you can create a group table matching up producers and authorized users.
The view should join the base table with the group table on producer and
limit the results to users matching the "current_user". With appropiate
indexes this should be fast.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #5
<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

Nov 23 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Julia Briggs | last post by:
I am struggling to create a PHP function that would take a specified image (JPG, GIF or PNG) from a link, and resize it down to a thumbnail so it will always fit in a 200x250 space. I am hoping...
36
by: rbt | last post by:
Say I have a list that has 3 letters in it: I want to print all the possible 4 digit combinations of those 3 letters: 4^3 = 64 aaaa
20
by: CHIN | last post by:
Hi all.. here s my problem ( maybe some of you saw me on other groups, but i cant find the solution !! ) I have to upload a file to an external site, so, i made a .vbs file , that logins to...
2
by: Bhupesh Naik | last post by:
This is a query regarding my problem to make a spell and grammar check possible in text area of a web page. We have aspx pages which are used to construct letters. The browser based screens...
4
by: SUKRU | last post by:
Hello everybody. Unfortunately I am pretty new to sql-server 2000 I need some help with a Trigger I created. I created a trigger witch takes the id of the affected row and does a update on a...
1
by: AAA | last post by:
hi, I'll explain fastly the program that i'm doing.. the computer asks me to enter the cardinal of a set X ( called "dimX" type integer)where X is a table of one dimension and then to fill it...
25
by: Piotr Nowak | last post by:
Hi, Say i have a server process which listens for some changes in database. When a change occurs i want to refresh my page in browser by notyfinig it. I do not want to refresh my page i.e....
4
by: RSH | last post by:
Okay my math skills aren't waht they used to be... With that being said what Im trying to do is create a matrix that given x number of columns, and y number of possible values i want to generate...
7
by: Robert S. | last post by:
Searching some time now for documents on this but still did not find anything about it: Is it possible to replace the entry screen of MS Office Access 2007 - that one presenting that default...
14
by: bjorklund.emil | last post by:
Hello pythonistas. I'm a newbie to pretty much both programming and Python. I have a task that involves writing a test script for every possible combination of preference settings for a software...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.