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? ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly