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

Re: [v9.4] row level security

From: Kohei KaiGai <kaigai(at)kaigai(dot)gr(dot)jp>
To: PgHacker <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [v9.4] row level security
Date: 2013-07-08 10:19:57
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
Sorry, this included a garbage chunk when I made a patch.

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

2013/7/8 Kohei KaiGai <kaigai(at)kaigai(dot)gr(dot)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(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:
>>       FOR <command> TO (<expression>);
>>       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);
>> 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.
>> Thanks,
>> --
>> KaiGai Kohei <kaigai(at)kaigai(dot)gr(dot)jp>
> --
> KaiGai Kohei <kaigai(at)kaigai(dot)gr(dot)jp>

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

Attachment: pgsql-v9.4-row-level-security.v3.patch
Description: application/octet-stream (158.3 KB)

In response to


pgsql-hackers by date

Next:From: Ants AasmaDate: 2013-07-08 10:21:17
Subject: Re: XLogInsert scaling, revisited
Previous:From: mohsen soodkhah mohammadiDate: 2013-07-08 10:19:43
Subject: LogSwitch

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