Oracle's Virtual Private Database functionality - Mailing list pgsql-general
From | Doug Bloebaum |
---|---|
Subject | Oracle's Virtual Private Database functionality |
Date | |
Msg-id | 1110552518.1f7b963cblabes@myrealbox.com Whole thread Raw |
List | pgsql-general |
[ 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
pgsql-general by date: