Re: unclear about row-level security USING vs. CHECK

* Peter Eisentraut (peter_e(at)gmx(dot)net) wrote:
> On 9/23/15 11:05 AM, Stephen Frost wrote:
> > That the USING policy is used if WITH CHECK isn't defined? That was
> > simply done to make policy management simple as in quite a few cases
> > only one policy is needed. If a WITH CHECK was always required then
> > you'd be constantly writing:
> >
> > CREATE POLICY p1 ON t1
> > USING (entered_by = current_user)
> > WITH CHECK (entered_by = current_user);
> >
> > With potentially quite lengthy expressions.
>
> That might be reasonable, but the documentation is completely wrong
> about that.

Really? I feel pretty confident that it's at least mentioned. I
agree that it should be made more clear.

> That said, why even have USING and CHECK as separate clauses? Can't you
> just create different policies if you want them different?
>
> Hypothetical example:
>
> CREATE POLICY p1 ON t1 FOR SELECT CHECK (extract(year from entered_on) =
> extract(year from current_timestamp));
> CREATE POLICY p2 ON t2 FOR INSERT, UPDATE, DELETE CHECK (entered_by =
> current_user);

USING is about visibility of existing records, WITH CHECK is in regards
to new rows being added to the relation (either through an INSERT or an
UPDATE). It would be possible to change WITH CHECK for INSERT to be
USING, but that doesn't work for UPDATE as there are many use-cases
where you want a different policy for the UPDATE visibility vs. the
resulting record.

To say it another way, you may be allowed to update lots of records but
the resulting records have to pass a different policy to be allowed.

Thanks!

Stephen