Re: [v9.4] row level security

I was pointed out that the previous patch was not applied cleanly because of
enhancement on pg_class system catalog, and related pg_dump portion was
getting broken.

The attached patch fixes this matters. Please reference this patch instead.
Thanks,

2013/6/13 Kohei KaiGai <kaigai(at)kaigai(dot)gr(dot)jp>:
> 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(at)kaigai(dot)gr(dot)jp>

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