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

Hello Peter

> 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.
> """
>
> So basically, USING filters out what you see, CHECK controls what you
> can write.

Yes, for the command that you specified in the FOR clause. This is quite important if you need different conditions for different commands, e.g. see all rows, modify only some.

This may help to better understand how this is meant:
http://www.postgresql.org/message-id/20150711132144.GS12131@tamriel.snowman.net

> 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
>
> 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.

Since the policy is defined for ALL commands and no WITH CHECK is specified then the same condition defined in USING takes effect for all commands, i.e. including INSERT.

From the docs (http://www.postgresql.org/docs/9.5/static/sql-createpolicy.html): "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)."

If you want e.g. to allow users to insert rows without the restriction of being the current_user in column entered_by then you would need separate policies for each command. If you define a policy for INSERT, USING does not make sense. In the thread above there is a similar example to this as well as in the documentation:

http://www.postgresql.org/docs/9.5/static/ddl-rowsecurity.html

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

In your example it means that users can delete only the rows where entered_by = current_user. A WITH CHECK policy does not make sense in this case.

I assume that having USING and WITH CHECK for filtering and controlling added rows was introduced for use cases where these conditions are not the same, i.e. to allow for more flexibility. On the spot I don't have an example, but maybe somebody else can deliver one.

Regards
Charles