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

On Tue, Sep 22, 2015 at 10:36 PM, Charles Clavadetscher
<clavadetscher(at)swisspug(dot)org> wrote:
> 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.

Gosh, I think it would have been better to have a cleaner separation
of USING and WITH CHECK. That sounds far too unnecessarily magical.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company