* Robert Haas (robertmhaas@gmail.com) wrote:
> On Tue, Sep 22, 2015 at 10:36 PM, Charles Clavadetscher
> <clavadetscher@swisspug.org> wrote:
> > Since the policy is defined for ALL commands and no WITH CHECK is specified then the same condition defined in
USINGtakes 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
haveboth USING and WITH CHECK policies (ALL and UPDATE), if no WITH CHECK policy is defined then the USING policy will
beused 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_bythen you would need separate policies for each command. If you define a policy for INSERT, USING does not
makesense. 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
doesnot 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.
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.
I'm not against changing that if people feel strongly about it, but I
certainly find it extremely handy.
If that wasn't what you were referring to then please clarify as I
didn't follow.
Thanks!
Stephen