Re: unclear about row-level security USING vs. CHECK - Mailing list pgsql-hackers

Peter,

* Peter Eisentraut (peter_e@gmx.net) wrote:
> I'm testing the new row-level security feature.  I'm not clear on the
> difference between the USING and CHECK clauses in the CREATE POLICY
> statement.
>
> The documentation says:
>
> """
> A policy grants the ability to SELECT, INSERT, UPDATE, or DELETE rows
> which match the relevant policy expression. Existing table rows are
> checked against the expression specified via USING, while new rows that
> would be created via INSERT or UPDATE are checked against the expression
> specified via WITH CHECK. When a USING expression returns true for a
> given row then that row is visible to the user, while if a false or null
> is returned then the row is not visible. When a WITH CHECK expression
> returns true for a row then that row is added, while if a false or null
> is returned then an error occurs.
> """

The CREATE POLICY documentation discusses how lack of a WITH CHECK
policy means the USING expression is used:

"""
Policies can be applied for specific commands or for specific roles. The
default for newly created policies is that they apply for all commands
and roles, unless otherwise specified. If multiple policies apply to a
given query, they will be combined using OR (although ON CONFLICT DO
UPDATE and INSERT policies are not combined in this way, but rather
enforced as noted at each stage of ON CONFLICT execution). Further, for
commands which can have both USING and WITH CHECK policies (ALL and
UPDATE), if no WITH CHECK policy is defined then the USING policy will
be used for both what rows are visible (normal USING case) and which
rows will be allowed to be added (WITH CHECK case).
"""

> So basically, USING filters out what you see, CHECK controls what you
> can write.

Right.

> But then this doesn't work correctly:
>
> CREATE TABLE test1 (content text, entered_by text);
> ALTER TABLE test1 ENABLE ROW LEVEL SECURITY;
> CREATE POLICY test1_policy ON test1 FOR ALL TO PUBLIC USING (entered_by
> = current_user);
> GRANT ALL ON TABLE test1 TO PUBLIC;
>
> CREATE USER foo1;
> SET SESSION AUTHORIZATION foo1;
> INSERT INTO test1 VALUES ('blah', 'foo2');  -- fails

You didn't specify a WITH CHECK policy and so the USING policy of
(entered_by = current_user) was used, as described above in the CREATE
POLICY documentation.

> This is a typical you-can-only-see-your-own-rows setup, which works for
> the reading case, but it evidently also controls writes.  So I'm not
> sure what the CHECK clause is supposed to add on top of that.

It could any number of additional checks; in this example perhaps
'content' which is being updated or newly added must have include
'Copyright 2015' or some such.

> (Btw., what's the meaning of a policy for DELETE?)

The DELETE policy controls what records a user is able to delete.

* Peter Eisentraut (peter_e@gmx.net) wrote:
> On 9/23/15 2:52 PM, Stephen Frost wrote:
> >> 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.
>
> I quoted the documentation at the beginning of the thread.  That's all I
> could find about it.

Hopefully the above helps.  There's a lot of information in the
individual POLICY commands, especially in CREATE POLICY.  Perhaps some
of that needs to be brought into the overall RLS section, but I'm not
sure we really want to duplicate it all.

> > 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).
>
> That makes sense, but then the current behavior that I mentioned at the
> beginning of the thread is wrong.  If you think these clauses are
> clearly separate, then they should be, er, clearly separate.

They're not seperate as implemented and documented.  The current
discussion is about if we wish to change that.

> Maybe the syntax can be tweaked a little, like USING AND CHECK or
> whatever.  Not that USING and CHECK are terribly intuitive in this
> context anyway.

Ah, so that would be a fourth option along the lines of:

CREATE POLICY p1 ON t1
USING AND WITH CHECK (<expression>);

That'd certainly be straight-forward to implement.  Would we then
require the user to explicitly state the WITH CHECK piece, where it
applies, then?

Thanks!

Stephen