Re: RLS Design

Thom,

* Thom Brown (thom(at)linux(dot)com) wrote:
> On 19 September 2014 17:32, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > * Thom Brown (thom(at)linux(dot)com) wrote:
> > > On 14 September 2014 16:38, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > > # create policy visible_colours on colours for all to joe using (visible
> > =
> > > true);
> > > CREATE POLICY
> > [...]
> > > > insert into colours (name, visible) values ('transparent',false);
> > > ERROR: new row violates WITH CHECK OPTION for "colours"
> > > DETAIL: Failing row contains (7, transparent, f).
> > >
> > > > select * from pg_policies ;
> > > policyname | tablename | roles | cmd | qual |
> > with_check
> > >
> > -----------------+-----------+-------+-----+------------------+------------
> > > visible_colours | colours | {joe} | ALL | (visible = true) |
> > > (1 row)
> > >
> > > There was no WITH CHECK OPTION.
> >
> > As I hope is clear if you look at the documentation- if the WITH CHECK
> > clause is omitted, then the USING clause is used for both filtering and
> > checking new records, otherwise you'd be able to add records which
> > aren't visible to you.
>
> I can see that now, although I do find the error message somewhat
> confusing. Firstly, it looks like "OPTION" is part of the parameter name,
> which it isn't.

Hmm, the notion of 'with check option' is from the SQL standard, which
is why I felt the error message was appropriate as-is..

> Also, I seem to get an error message with the following:
>
> # create policy nice_colours ON colours for all to joe using (visible =
> true) with check (name in ('blue','green','yellow'));
> CREATE POLICY
>
> \c - joe
>
> > insert into colours (name, visible) values ('blue',false);
> ERROR: function with OID 0 does not exist

Now *that* one is interesting and I'll definitely go take a look at it.
We added quite a few regression tests to try and make sure these things
work.

> And if this did work, but I only violated the USING clause, would this
> still say the WITH CHECK clause was the cause?

WITH CHECK applies for INSERT and UPDATE for the new records going into
the table. You can't actually violate the USING clause for an INSERT
as USING is for filtering records, not checking that records being added
to the table are valid.

To try and clarify- by explicitly setting both USING and WITH CHECK, you
*are* able to INSERT records which are not visible to you. We felt that
was an important capability to support.

Thanks for taking a look at it!

Stephen