Re: RLS Design

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