[v9.4] row level security - Mailing list pgsql-hackers

The attached patch implements row-level security feature; that allows to
enforce a pre-configured security policy on reference of tables with the
row-level security policy.
It enables to isolate records to be visible from others according to access
control decision, usually done based on current user's credential.
It will make sense to ensure correctness of security for SaaS style
applications that typically share a common table for multiple users but
correctness of access control was ensured with correctness of application
itself.

Here is not functional update since the last commit fest for v9.3 except
for adjustment towards the latest master branch.

So, the explanation below might be bored for someone.

This feature enhances ALTER TABLE statement as follows:
  ALTER TABLE <tablename> SET ROW SECURITY
      FOR <command> TO (<expression>);
  ALTER TABLE <tablename> RESET ROW SECURITY
      FOR <command>;
  <command> := { ALL | SELECT | INSERT | UPDATE | DELETE }

Right now, only "ALL" is supported command, even though syntax reserves
future enhancement allows to set individual security policy for each command.
The <expression> should be an expression that returns a bool value. It can
reference any column in the target table and contain sub-query that reference
another tables.
Then, the pre-configured expression shall be added when the table is referenced.

See below, it gives "(X % 2 = 1)" as security policy, user can see the record
that has odd-number at X. The EXPLAIN output below shows this expression
was automatically attached.

postgres=> ALTER TABLE tbl SET ROW SECURITY FOR ALL TO (x % 2 = 1);
ALTER TABLE
postgres=> EXPLAIN SELECT * FROM tbl WHERE y like '%abc%';
                           QUERY PLAN
-----------------------------------------------------------------
 Subquery Scan on tbl  (cost=0.00..28.52 rows=1 width=36)
   Filter: (tbl.y ~~ '%abc%'::text)
   ->  Seq Scan on tbl tbl_1  (cost=0.00..28.45 rows=6 width=36)
         Filter: ((x % 2) = 1)
(4 rows)

An important point is, reference to a particular relation is replaced
with a sub-
query that has security policy expression and security barrier attribute.
That prevent any (non leakproof) user given condition earlier than
security poliy
itself, thus, it ensures all records user can see is satisfies the
security policy.

On writer-queries, things to do are similar. It adds security policy expression
on the scan phase of UPDATE or DELETE statement. Thus, only visible records
are updatable or deletable.

postgres=> EXPLAIN UPDATE tbl SET y = y || '_update' WHERE y like '%xyz%';
                              QUERY PLAN
-----------------------------------------------------------------------
 Update on tbl  (cost=0.00..28.53 rows=1 width=42)
   ->  Subquery Scan on tbl_1  (cost=0.00..28.53 rows=1 width=42)
         Filter: (tbl_1.y ~~ '%xyz%'::text)
         ->  Seq Scan on tbl tbl_2  (cost=0.00..28.45 rows=6 width=42)
               Filter: ((x % 2) = 1)
(5 rows)

I had a relevant presentation at PGcon last month. I think its slides
are good summary
to know brief background of the long-standing problem.
http://www.pgcon.org/2013/schedule/attachments/273_PGcon2013-kaigai-row-level-security.pdf

Thanks,
--
KaiGai Kohei <kaigai@kaigai.gr.jp>