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

From Kohei KaiGai
Subject Re: [v9.4] row level security
Date
Msg-id CADyhKSVNMMvmPCQkpaVb96T4F3Eu+uREnak4_5_5RLOuSeJLQg@mail.gmail.com
Whole thread Raw
In response to [v9.4] row level security  (Kohei KaiGai <kaigai@kaigai.gr.jp>)
Responses Re: [v9.4] row level security
List pgsql-hackers
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>

Attachment

pgsql-hackers by date:

Previous
From: Heikki Linnakangas
Date:
Subject: Re: XLogInsert scaling, revisited
Next
From: Kyotaro HORIGUCHI
Date:
Subject: Re: Add visibility map information to pg_freespace.