Re: RLS Design

All,

Attached is a patch for RLS that was create against master at
01363beae52700c7425cb2d2452177133dad3e93 and is ready for review.

Overview:

This patch provides the capability to create multiple named row level
security policies for a table on a per command basis and assign them to be
applied to specific roles/users.

It contains the following changes:

* Syntax:

CREATE POLICY <name> ON <table>
[ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
[ TO { PUBLIC | <role> [, <role> ] } ]
USING (<condition>)

Creates a RLS policy named <name> on <table>. Specifying a command is
optional, but the default is ALL. Specifying a role is options, but the
default is PUBLIC. If PUBLIC and other roles are specified, ONLY PUBLIC is
applied and a warning is raised.

ALTER POLICY <name> ON <table>
[ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
[ TO { PUBLIC | <role> [, <role> ] } ]
USING (<condition>)

Alter a RLS policy named <name> on <table>. Specifying a command is
optional, if provided then the policy's command is changed otherwise it is
left as-is. Specifying a role is optional, if provided then the policy's
role is changed otherwise it is left as-is. The <condition> must always be
provided and is therefore always replaced.

DROP POLICY <name> ON <table>

Drop a RLS policy named <name> on <table>.

* Plancache Invalidation: If a relation has a row-security policy and
row-security is enabled then the invalidation will occur when either the
row_security GUC is changed OR when a the current user changes. This
invalidation ONLY takes place for cached plans where the target relation
has a row security policy.

* Security Qual Expression: All row-security policies are OR'ed together.
In the case where another security qual is added, such as in the case of a
Security Barrier Views, the the row-security policies are AND'ed with those
quals.

Example:

If a table has policies p1 and p2 and a security barrier view is created
for that table called rls_sbv, then SELECT * FROM rls_sbv WHERE
<some_condition> would result in the following expression: <some_condition>
AND (p1 OR p2)

* row_security GUC - enable/disable row level security.

* BYPASSRLS and NOBYPASSRLS role attribute - allows user to bypass RLS if
row_security GUC is set to OFF. If a user sets row_security to OFF and
does not have this attribute, then an error is raised when attempting to
query a relation with a RLS policy.

* psql \d <table> support: psql describe support for listing policy
information per table.

* pg_policies system view: lists all row-security policy information.

Any feedback, comments or suggestions would be greatly appreciated.

Thanks,
Adam

On Mon, Aug 18, 2014 at 10:19 PM, Brightwell, Adam <
adam(dot)brightwell(at)crunchydatasolutions(dot)com> wrote:

> All,
>
> Attached is a patch for RLS that incorporates the following changes:
>
> * Syntax:
> - CREATE POLICY <policy_name> ON <table_name> FOR <command> USING (
> <qual> )
> - ALTER POLICY <policy_name> ON <table_name> FOR <command> USING (
> <qual> )
> - DROP POLICY <policy_name> ON <table_name> FOR <command>
>
> * "row_security" GUC Setting - enable/disable row level security.
>
> * BYPASSRLS and NOBYPASSRLS role attribute - allows user to bypass RLS if
> row_security GUC is set to OFF.
>
> There are still some remaining issues but we hope to have those resolved
> soon.
>
> Any comments or suggestions would be greatly appreciated.
>
> Thanks,
> Adam
>
>
> On Mon, Jul 21, 2014 at 11:38 AM, Robert Haas <robertmhaas(at)gmail(dot)com>
> wrote:
>
>> On Fri, Jul 18, 2014 at 7:01 PM, Brightwell, Adam
>> <adam(dot)brightwell(at)crunchydatasolutions(dot)com> wrote:
>> >> I think we do want a way to modify policies. However, we tend to
>> >> avoid syntax that involves unnatural word order, as this certainly
>> >> does. Maybe it's better to follow the example of CREATE RULE and
>> >> CREATE TRIGGER and do something this instead:
>> >>
>> >> CREATE POLICY policy_name ON table_name USING quals;
>> >> ALTER POLICY policy_name ON table_name USING quals;
>> >> DROP POLICY policy_name ON table_name;
>> >>
>> >> The advantage of this is that you can regard "policy_name ON
>> >> table_name" as the identifier for the policy throughout the system.
>> >> You need some kind of identifier of that sort anyway to support
>> >> COMMENT ON, SECURITY LABEL, and ALTER EXTENSION ADD/DROP for policies.
>> >
>> > Sounds good. I certainly think it makes a lot of sense to include the
>> ALTER
>> > functionality, if for no other reason than ease of use.
>> >
>> > Another item to consider, though I believe it can come later, is
>> per-action
>> > policies. Following the above suggested syntax, perhaps that might look
>> > like the following?
>> >
>> > CREATE POLICY policy_name ON table_name FOR action USING quals;
>> > ALTER POLICY policy_name ON table_name FOR action USING quals;
>> > DROP POLICY policy_name ON table_name FOR action;
>>
>> That seems reasonable. You need to give some thought to what happens
>> if the user types:
>>
>> CREATE POLICY pol1 ON tab1 FOR SELECT USING q1;
>> ALTER POLICY pol1 ON tab1 FOR INSERT USING q2;
>>
>> I guess you end up with q1 as the SELECT policy and q2 as the INSERT
>> policy. Similarly, had you typed:
>>
>> CREATE POLICY pol1 ON tab1 USING q1;
>> ALTER POLICY pol1 ON tab1 FOR INSERT USING q2;
>>
>> ...then I guess you end up with q2 for INSERTs and q1 for everything
>> else. I'm wondering if it might be better, though, not to allow the
>> quals to be specified in CREATE POLICY, or else to allow multiple
>> actions. Otherwise, getting pg_dump to DTRT might be complicated.
>>
>> Perhaps:
>>
>> CREATE POLICY pol1 ON tab1 ( [ [ FOR operation [ OR operation ] ... ]
>> USING quals ] ... );
>> where operation = SELECT | INSERT | UPDATE | DELETE
>>
>> So that you can write things like:
>>
>> CREATE POLICY pol1 ON tab1 (USING a = 1);
>> CREATE POLICY pol2 ON tab2 (FOR INSERT USING a = 1, FOR UPDATE USING b
>> = 1, FOR DELETE USING c = 1);
>>
>> And then, for ALTER, just allow one change at a time, syntax as you
>> proposed. That way each policy can be dumped as a single CREATE
>> statement.
>>
>> > I was also giving some thought to the use of "POLICY", perhaps I am
>> wrong,
>> > but it does seem it could be at risk of becoming ambiguous down the
>> road. I
>> > can't think of any specific examples at the moment, but my concern is
>> what
>> > happens if we wanted to add another "type" of policy, whatever that
>> might
>> > be, later? Would it make more sense to go ahead and qualify this a
>> little
>> > more with "ROW SECURITY POLICY"?
>>
>> I think that's probably over-engineering. I'm not aware of anything
>> else we might add that would be likely to be called a policy, and if
>> we did add something we could probably call it something else instead.
>> And long command names are annoying.
>>
>> --
>> Robert Haas
>> EnterpriseDB: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>
>
>
> --
> Adam Brightwell - adam(dot)brightwell(at)crunchydatasolutions(dot)com
> Database Engineer - www.crunchydatasolutions.com
>

--
Adam Brightwell - adam(dot)brightwell(at)crunchydatasolutions(dot)com
Database Engineer - www.crunchydatasolutions.com