Re: RLS Design

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