Let me have a discussion to get preferable interface for row-level security.
My planned feature will perform to append additional conditions to WHERE clause implicitly, to restrict tuples being visible for the current user. For example, when row-level policy "uname = getpgusername()" is configured on the table T1, the following query: select * from T1 where X > 20; should be rewritten to: select * from T1 where (X > 20) AND (uname = getpgusername()); on somewhere in the query processing stage prior to optimizer. I checked the way to set up row-level policy at Oracle. Its document seems to me users specify a function for row-level policy. http://docs.oracle.com/cd/B28359_01/network.111/b28531/vpd.htm#i1008294 I had a short talk with Robert about this topic, and had an impression the policy should be given as a formula of where-clause instead of sql function, for query optimization purpose. However, I missed a simple sql function can be inlined with simplify_function(). So, unless the security policy isn't enough simple, it is harmless to optimization. Example) postgres=# CREATE TABLE t1 (x int, y int, uname text); CREATE TABLE postgres=# CREATE FUNCTION sel_pol_t1 (text) RETURNS bool LANGUAGE sql AS 'SELECT $1 = getpgusername()'; CREATE FUNCTION postgres=# EXPLAIN SELECT * FROM t1 WHERE (x > 20) AND sel_pol_t1(uname); QUERY PLAN ------------------------------------------------------------ Seq Scan on t1 (cost=0.00..33.20 rows=2 width=40) Filter: ((x > 20) AND (uname = (getpgusername())::text)) (2 rows) A simple SQL function sel_pol_t1() is inlined to the where-clause, thus if an index would be configured to uname, index-scan should be an option. So, I'd like to chose simpler implementation with the following interface. ALTER TABLE <tblname> ADD SECURITY POLICY func(<colname>,...) [FOR SELECT|UPDATE|DELETE]; ALTER TABLE <tblname> DROP SECURITY POLICY func(<colname>,...); [FOR SELECT|UPDATE|DELETE]; ALTER TABLE <tblname> DROP SECURITY POLICY ALL; This interface allows to assign multiple functions on a particular table. Then, these functions shall be assigned on where clause of the tables to be scanned on. If available, optimizer will inline the functions for further optimization. Any comments please. Thanks, -- KaiGai Kohei <kai...@kaigai.gr.jp> -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers