Check out CREATE RULE command,
Regards,
Qingqing
Doug Bloebaum [EMAIL PROTECTED] дÈëÓʼþ
news:[EMAIL PROTECTED]
[ 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
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
