Oracle's Virtual Private Database functionality

From: "Doug Bloebaum" <DBloebau(at)luxotticaRetail(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Oracle's Virtual Private Database functionality
Date: 2005-03-09 18:52:28
Message-ID: s22effbc.035@smtp.luxotticaretail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ian Pilcher 2005-03-09 18:59:06 Re: Can't start PostgreSQL on Fedora Core3
Previous Message Bricklen Anderson 2005-03-09 18:44:56 Re: PostgreSQL still for Linux only?