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
