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

Sorry, this included a garbage chunk when I made a patch.

The attached one is corrected revision. Please see this patch instead.
Thanks,

2013/7/8 Kohei KaiGai <kaigai@kaigai.gr.jp>:
> 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@kaigai.gr.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@kaigai.gr.jp>
>
>
>
> --
> KaiGai Kohei <kaigai@kaigai.gr.jp>



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