Re: BUG #13780: Multiple commands not allowed for creating a policy.

The following bug has been logged on the website:

Bug reference: 13780
Logged by: Caleb Meredith
Email address: calebmeredith8(at)gmail(dot)com
PostgreSQL version: 9.5beta1
Operating system: OSX
Description:

When performing a grant I can do:

GRANT INSERT, UPDATE, DELETE ON TABLE person TO user;

But when creating a policy I can't do:

CREATE POLICY user_write ON person FOR INSERT, UPDATE, DELETE TO user
USING (…);

The specific error is:

ERROR: syntax error at or near ","
LINE 1: ...TE POLICY user_write ON person FOR INSERT, UPDATE, ...

This doesn't seem to be intended as the GRANT specification allows it, and
CREATE POLICY supports the ALL command.

On Sun, Nov 22, 2015 at 10:23 PM, <calebmeredith8(at)gmail(dot)com> wrote:

> This doesn't seem to be intended as the GRANT specification allows it, and
> CREATE POLICY supports the ALL command.
>

Yeah, I would tend to agree that the existence of ALL contradicts the
expression constraints that are in place when defining a non-ALL policy as
we could basically ignore either the WITH CHECK or USING clauses defined in
a policy defined depending on the DML or SELECT command used. To be more
exact, for example with an ALL policy that has both a WITH CHECK and USING
defined, we would ignore the USING clause with an INSERT query, right? We
are in beta2 state now, so I don't think anything is going to change, but
Stephen, your thoughts perhaps?
--
Michael

Yes, that makes sense.
On Mon, Nov 23, 2015 at 7:16 AM Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
wrote:

>
>
> On Sun, Nov 22, 2015 at 10:23 PM, <calebmeredith8(at)gmail(dot)com> wrote:
>
>> This doesn't seem to be intended as the GRANT specification allows it, and
>> CREATE POLICY supports the ALL command.
>>
>
> Yeah, I would tend to agree that the existence of ALL contradicts the
> expression constraints that are in place when defining a non-ALL policy as
> we could basically ignore either the WITH CHECK or USING clauses defined in
> a policy defined depending on the DML or SELECT command used. To be more
> exact, for example with an ALL policy that has both a WITH CHECK and USING
> defined, we would ignore the USING clause with an INSERT query, right? We
> are in beta2 state now, so I don't think anything is going to change, but
> Stephen, your thoughts perhaps?
> --
> Michael
>

Caleb,

* calebmeredith8(at)gmail(dot)com (calebmeredith8(at)gmail(dot)com) wrote:
> When performing a grant I can do:
>
> GRANT INSERT, UPDATE, DELETE ON TABLE person TO user;
>
> But when creating a policy I can't do:
>
> CREATE POLICY user_write ON person FOR INSERT, UPDATE, DELETE TO user
> USING (…);
>
> The specific error is:
>
> ERROR: syntax error at or near ","
> LINE 1: ...TE POLICY user_write ON person FOR INSERT, UPDATE, ...
>
> This doesn't seem to be intended as the GRANT specification allows it, and
> CREATE POLICY supports the ALL command.

This is certainly something which we can look at adding support for. I
agree that it'd be nice to have, though I'm curious what your specific
use-case for it is, if you don't mind sharing.

Just to be clear, this is a new feature and not a bug. The CREATE
POLICY documentation is correct- you can currently only specify one
command and the system catalog underneath only allows for one command
per policy.

Thanks!

Stephen

Michael,

* Michael Paquier (michael(dot)paquier(at)gmail(dot)com) wrote:
> On Sun, Nov 22, 2015 at 10:23 PM, <calebmeredith8(at)gmail(dot)com> wrote:
> > This doesn't seem to be intended as the GRANT specification allows it, and
> > CREATE POLICY supports the ALL command.
>
> Yeah, I would tend to agree that the existence of ALL contradicts the
> expression constraints that are in place when defining a non-ALL policy as
> we could basically ignore either the WITH CHECK or USING clauses defined in
> a policy defined depending on the DML or SELECT command used. To be more
> exact, for example with an ALL policy that has both a WITH CHECK and USING
> defined, we would ignore the USING clause with an INSERT query, right? We
> are in beta2 state now, so I don't think anything is going to change, but
> Stephen, your thoughts perhaps?

We could support allowing multiple commands for a policy and would just
need to adjust the checks to make sure that the policy definition makes
sense, but that's all new-feature type of work which would be for 9.6
and not a bug in the current implementation. I'm certainly not against
doing that, but it doesn't seem like a terribly high priority.

Thanks!

Stephen

My use case is I want users to see more data then they can edit. So select
would have a broad USAGE clause and the write commands (insert, update,
delete) would have a narrow USAGE/WITH CLAUSE check.
On Mon, Nov 23, 2015 at 9:59 AM Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Michael,
>
> * Michael Paquier (michael(dot)paquier(at)gmail(dot)com) wrote:
> > On Sun, Nov 22, 2015 at 10:23 PM, <calebmeredith8(at)gmail(dot)com> wrote:
> > > This doesn't seem to be intended as the GRANT specification allows it,
> and
> > > CREATE POLICY supports the ALL command.
> >
> > Yeah, I would tend to agree that the existence of ALL contradicts the
> > expression constraints that are in place when defining a non-ALL policy
> as
> > we could basically ignore either the WITH CHECK or USING clauses defined
> in
> > a policy defined depending on the DML or SELECT command used. To be more
> > exact, for example with an ALL policy that has both a WITH CHECK and
> USING
> > defined, we would ignore the USING clause with an INSERT query, right? We
> > are in beta2 state now, so I don't think anything is going to change, but
> > Stephen, your thoughts perhaps?
>
> We could support allowing multiple commands for a policy and would just
> need to adjust the checks to make sure that the policy definition makes
> sense, but that's all new-feature type of work which would be for 9.6
> and not a bug in the current implementation. I'm certainly not against
> doing that, but it doesn't seem like a terribly high priority.
>
> Thanks!
>
> Stephen
>