Skip site navigation (1) Skip section navigation (2)

Re: Review of Row Level Security

From: Kohei KaiGai <kaigai(at)kaigai(dot)gr(dot)jp>
To: Stephen Frost <sfrost(at)snowman(dot)net>, Simon Riggs <simon(at)2ndquadrant(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Kevin Grittner <kgrittn(at)mail(dot)com>
Subject: Re: Review of Row Level Security
Date: 2013-01-15 22:28:40
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
The attached patch is row-security v9.

According to the upthread discussion, I adjusted the syntax as follows:

  ALTER TABLE <table> SET ROW SECURITY FOR <cmd> TO (<expression>);

It seems to me "FOR <cmd>" might be omissible as synonym of "FOR ALL".
User needs to input this clause everytime, so they may feel it troublesome.

As previous version doing, references to a table with row-security policy is
replaced by a simple sub-query that scans the target table with configured
row-security policy.

postgres=> ALTER TABLE t1 SET ROW SECURITY FOR ALL TO (a % 2 = 0);
postgres=> ALTER TABLE t2 SET ROW SECURITY FOR ALL TO (a % 2 = 1);
postgres=> EXPLAIN SELECT * FROM t1 WHERE f_leak(b);
                                QUERY PLAN
 Result  (cost=0.00..50.82 rows=413 width=36)
   ->  Append  (cost=0.00..50.82 rows=413 width=36)
         ->  Subquery Scan on t1  (cost=0.00..0.01 rows=1 width=36)
               Filter: f_leak(t1.b)
               ->  Seq Scan on t1 t1_1  (cost=0.00..0.00 rows=1 width=36)
                     Filter: ((a % 2) = 0)
         ->  Subquery Scan on t2  (cost=0.00..28.51 rows=2 width=36)
               Filter: f_leak(t2.b)
               ->  Seq Scan on t2 t2_1  (cost=0.00..28.45 rows=6 width=36)
                     Filter: ((a % 2) = 1)
         ->  Seq Scan on t3  (cost=0.00..22.30 rows=410 width=36)
               Filter: f_leak(b)
(12 rows)

In case of UPDATE or DELETE, row-security also prevent to modify
rows that does not satisfy the configured security policy.

postgres=> EXPLAIN UPDATE t1 SET b=b || '_update' WHERE b like '%abc%';
                             QUERY PLAN
 Update on t1  (cost=0.00..54.04 rows=51 width=42)
   ->  Subquery Scan on t1_1  (cost=0.00..0.02 rows=1 width=42)
         Filter: (t1_1.b ~~ '%abc%'::text)
         ->  Seq Scan on t1 t1_2  (cost=0.00..0.00 rows=1 width=42)
               Filter: ((a % 2) = 0)
   ->  Subquery Scan on t2  (cost=0.00..28.53 rows=1 width=42)
         Filter: (t2.b ~~ '%abc%'::text)
         ->  Seq Scan on t2 t2_1  (cost=0.00..28.45 rows=6 width=42)
               Filter: ((a % 2) = 1)
   ->  Seq Scan on t3  (cost=0.00..25.50 rows=49 width=42)
         Filter: (b ~~ '%abc%'::text)
(11 rows)

One significant change to the planner is, planner had to accept cases
that result relation is not identical with source relation being replaced
to row-security subquery. E.g, constructed plan for UPDATE may scans
tuples from a sub-query with rtindex=5 then update the relation with
rtindex=1. Some existing code assumes result relation is also source
relation, so it was my headache during the development.
Even though the current implementation is working for all the test cases
in regression test as I expected, I'm not 100% certain whether this
implementation is the best way. So, it's welcome if we can have better
and stable implementation than my proposition.

KaiGai Kohei <kaigai(at)kaigai(dot)gr(dot)jp>

Attachment: pgsql-v9.3-row-level-security.v9.patch
Description: application/octet-stream (156.6 KB)

In response to


pgsql-hackers by date

Next:From: Andres FreundDate: 2013-01-15 22:30:22
Subject: Re: Curious buildfarm failures (fwd)
Previous:From: Stephen FrostDate: 2013-01-15 22:24:37

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group