Thread: Oracle's Virtual Private Database functionality

Oracle's Virtual Private Database functionality

From

"Doug Bloebaum"

Date:

11 March 2005, 17:48:58

[ Sorry if this appears twice on the list: sent it via a non-subscribed email the first time around.  Of course, while
waitingfor it to appear I figured out a workaround; see the "Late breaking update" at the end. ] 

In the spirit of "tell us what you're trying to do..."

I'd like to mimic a subset of Oracle's Virtual Private Database functionality (see
http://dba-oracle.com/oracle_news/2004_1_16_vpd_security.htmfor an overview) in Postgres: based on some per-connection
setting,I'd like a query to return a different set of rows.  In VPD, the Oracle engine actually applies a defined
predicate(say, country_code='USA') to every query.  The idea is that a given set of users can only see rows in a table
thatmatch this predicate, while the other rows are invisible to them. 

Now for the "how I tried to do it" part...

I thought I was on my way to doing this in Postgres by making use of schemas and search_path:

CREATE USER user1 UNENCRYPTED PASSWORD 'user1';
CREATE SCHEMA AUTHORIZATION user1;

CREATE SCHEMA canada;
GRANT USAGE ON SCHEMA canada TO user1;
CREATE TABLE canada.row_limiter (country_code VARCHAR(3));
INSERT INTO canada.row_limiter VALUES('CAN');
GRANT SELECT ON canada.row_limiter TO user1;

CREATE SCHEMA usa;
GRANT USAGE ON SCHEMA usa TO user1;
CREATE TABLE usa.row_limiter (country_code VARCHAR(3));
INSERT INTO usa.row_limiter VALUES('USA');
GRANT SELECT ON usa.row_limiter TO user1;

\CONNECT - user1

SET SEARCH_PATH TO '$user',canada;

CREATE TABLE my_data (country_code VARCHAR(3), data NUMERIC);
INSERT INTO my_data VALUES('USA',11);
INSERT INTO my_data VALUES('USA',12);
INSERT INTO my_data VALUES('USA',13);

INSERT INTO my_data VALUES('CAN',21);
INSERT INTO my_data VALUES('CAN',22);
INSERT INTO my_data VALUES('CAN',23);

CREATE VIEW my_data_v AS
SELECT md.*
  FROM my_data md,
       row_limiter rl
 WHERE rl.country_code=md.country_code;

SELECT * FROM my_data_v;

-- Looks great - I only see Canadian data!!
-- country_code | data
----------------+------
-- CAN          |   21
-- CAN          |   22
-- CAN          |   23

SET SEARCH_PATH TO '$user',usa;

SELECT * FROM my_data_v;

-- Darn, I still only see Canadian data :-(
-- country_code | data
----------------+------
-- CAN          |   21
-- CAN          |   22
-- CAN          |   23

\d my_data_v

View definition:
 SELECT md.country_code, md.data
   FROM my_data md, CANADA.row_limiter rl -- <--ah, and here's the reason...
  WHERE rl.country_code::text = md.country_code::text;


It's apparent why: the view determines which table it's going to use at view creation time, not at query time, so this
methodis no good. 

Is there a "right way" to accomplish what I'm trying to do?

Late breaking update:

Looks like a function returning SETOF does the trick, so it must not resolve the table early like the view does:

CREATE OR REPLACE FUNCTION my_data_f() RETURNS SETOF my_data AS '
  SELECT md.country_code,
         md.data
    FROM my_data md, row_limiter rl
   WHERE rl.country_code = md.country_code;
' LANGUAGE sql;

SET SEARCH_PATH TO '$user',usa;
SELECT * FROM my_data_f();
 country_code | data
--------------+------
 USA          |   11
 USA          |   12
 USA          |   13

SET SEARCH_PATH TO '$user',canada;
SELECT * FROM my_data_f();
 country_code | data
--------------+------
 CAN          |   21
 CAN          |   22
 CAN          |   23

Can I rely on this behavior?  Is this the best way to do what I'm after?2 to accomplish what I'm trying to do?

Late breaking update:

Looks like a function returning SETOF does the trick, so it must not resolve the table early like the view does:

CREATE OR REPLACE FUNCTION my_data_f() RETURNS SETOF my_data AS '
  SELECT md.country_code,
         md.data
    FROM my_data md, row_limiter rl
   WHERE rl.country_code = md.country_code;
' LANGUAGE sql;

SET SEARCH_PATH TO '$user',usa;
SELECT * FROM my_data_f();
 country_code | data
--------------+------
 USA          |   11
 USA          |   12
 USA          |   13

SET SEARCH_PATH TO '$user',canada;
SE

Re: Oracle's Virtual Private Database functionality

From

"Qingqing Zhou"

Date:

12 March 2005, 09:05:29

Check out CREATE RULE command,

Regards,
Qingqing

""Doug Bloebaum"" <blabes@myrealbox.com> д���ʼ�
news:1110552518.1f7b963cblabes@myrealbox.com...
> [ Sorry if this appears twice on the list: sent it via a non-subscribed
email the first time around.  Of course, while waiting for it to appear I
figured out a workaround; see the "Late breaking update" at the end. ]
>
> In the spirit of "tell us what you're trying to do..."
>
> I'd like to mimic a subset of Oracle's Virtual Private Database
functionality (see
http://dba-oracle.com/oracle_news/2004_1_16_vpd_security.htm for an
overview) in Postgres: based on some per-connection setting, I'd like a
query to return a different set of rows.  In VPD, the Oracle engine actually
applies a defined predicate (say, country_code='USA') to every query.  The
idea is that a given set of users can only see rows in a table that match
this predicate, while the other rows are invisible to them.
>
> Now for the "how I tried to do it" part...
>
> I thought I was on my way to doing this in Postgres by making use of
schemas and search_path:
>
> CREATE USER user1 UNENCRYPTED PASSWORD 'user1';
> CREATE SCHEMA AUTHORIZATION user1;
>
> CREATE SCHEMA canada;
> GRANT USAGE ON SCHEMA canada TO user1;
> CREATE TABLE canada.row_limiter (country_code VARCHAR(3));
> INSERT INTO canada.row_limiter VALUES('CAN');
> GRANT SELECT ON canada.row_limiter TO user1;
>
> CREATE SCHEMA usa;
> GRANT USAGE ON SCHEMA usa TO user1;
> CREATE TABLE usa.row_limiter (country_code VARCHAR(3));
> INSERT INTO usa.row_limiter VALUES('USA');
> GRANT SELECT ON usa.row_limiter TO user1;
>
> \CONNECT - user1
>
> SET SEARCH_PATH TO '$user',canada;
>
> CREATE TABLE my_data (country_code VARCHAR(3), data NUMERIC);
> INSERT INTO my_data VALUES('USA',11);
> INSERT INTO my_data VALUES('USA',12);
> INSERT INTO my_data VALUES('USA',13);
>
> INSERT INTO my_data VALUES('CAN',21);
> INSERT INTO my_data VALUES('CAN',22);
> INSERT INTO my_data VALUES('CAN',23);
>
> CREATE VIEW my_data_v AS
> SELECT md.*
>   FROM my_data md,
>        row_limiter rl
>  WHERE rl.country_code=md.country_code;
>
> SELECT * FROM my_data_v;
>
> -- Looks great - I only see Canadian data!!
> -- country_code | data
> ----------------+------
> -- CAN          |   21
> -- CAN          |   22
> -- CAN          |   23
>
> SET SEARCH_PATH TO '$user',usa;
>
> SELECT * FROM my_data_v;
>
> -- Darn, I still only see Canadian data :-(
> -- country_code | data
> ----------------+------
> -- CAN          |   21
> -- CAN          |   22
> -- CAN          |   23
>
> \d my_data_v
>
> View definition:
>  SELECT md.country_code, md.data
>    FROM my_data md, CANADA.row_limiter rl -- <--ah, and here's the
reason...
>   WHERE rl.country_code::text = md.country_code::text;
>
>
> It's apparent why: the view determines which table it's going to use at
view creation time, not at query time, so this method is no good.
>
> Is there a "right way" to accomplish what I'm trying to do?
>
> Late breaking update:
>
> Looks like a function returning SETOF does the trick, so it must not
resolve the table early like the view does:
>
> CREATE OR REPLACE FUNCTION my_data_f() RETURNS SETOF my_data AS '
>   SELECT md.country_code,
>          md.data
>     FROM my_data md, row_limiter rl
>    WHERE rl.country_code = md.country_code;
> ' LANGUAGE sql;
>
> SET SEARCH_PATH TO '$user',usa;
> SELECT * FROM my_data_f();
>  country_code | data
> --------------+------
>  USA          |   11
>  USA          |   12
>  USA          |   13
>
> SET SEARCH_PATH TO '$user',canada;
> SELECT * FROM my_data_f();
>  country_code | data
> --------------+------
>  CAN          |   21
>  CAN          |   22
>  CAN          |   23
>
> Can I rely on this behavior?  Is this the best way to do what I'm after?2
to accomplish what I'm trying to do?
>
> Late breaking update:
>
> Looks like a function returning SETOF does the trick, so it must not
resolve the table early like the view does:
>
> CREATE OR REPLACE FUNCTION my_data_f() RETURNS SETOF my_data AS '
>   SELECT md.country_code,
>          md.data
>     FROM my_data md, row_limiter rl
>    WHERE rl.country_code = md.country_code;
> ' LANGUAGE sql;
>
> SET SEARCH_PATH TO '$user',usa;
> SELECT * FROM my_data_f();
>  country_code | data
> --------------+------
>  USA          |   11
>  USA          |   12
>  USA          |   13
>
> SET SEARCH_PATH TO '$user',canada;
> SE
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>