On 19 September 2014 17:54, Stephen Frost <sfr...@snowman.net> wrote: > > Thom, > > * Thom Brown (t...@linux.com) wrote: > > On 19 September 2014 17:32, Stephen Frost <sfr...@snowman.net> wrote: > > > * Thom Brown (t...@linux.com) wrote: > > > > On 14 September 2014 16:38, Stephen Frost <sfr...@snowman.net> wrote: > > > > # create policy visible_colours on colours for all to joe using (visible > > > = > > > > true); > > > > CREATE POLICY > > > [...] > > > > > insert into colours (name, visible) values ('transparent',false); > > > > ERROR: new row violates WITH CHECK OPTION for "colours" > > > > DETAIL: Failing row contains (7, transparent, f). > > > > > > > > > select * from pg_policies ; > > > > policyname | tablename | roles | cmd | qual | > > > with_check > > > > > > > -----------------+-----------+-------+-----+------------------+------------ > > > > visible_colours | colours | {joe} | ALL | (visible = true) | > > > > (1 row) > > > > > > > > There was no WITH CHECK OPTION. > > > > > > As I hope is clear if you look at the documentation- if the WITH CHECK > > > clause is omitted, then the USING clause is used for both filtering and > > > checking new records, otherwise you'd be able to add records which > > > aren't visible to you. > > > > I can see that now, although I do find the error message somewhat > > confusing. Firstly, it looks like "OPTION" is part of the parameter name, > > which it isn't. > > Hmm, the notion of 'with check option' is from the SQL standard, which > is why I felt the error message was appropriate as-is.. > > > Also, I seem to get an error message with the following: > > > > # create policy nice_colours ON colours for all to joe using (visible = > > true) with check (name in ('blue','green','yellow')); > > CREATE POLICY > > > > \c - joe > > > > > insert into colours (name, visible) values ('blue',false); > > ERROR: function with OID 0 does not exist > > Now *that* one is interesting and I'll definitely go take a look at it. > We added quite a few regression tests to try and make sure these things > work. > > > And if this did work, but I only violated the USING clause, would this > > still say the WITH CHECK clause was the cause? > > WITH CHECK applies for INSERT and UPDATE for the new records going into > the table. You can't actually violate the USING clause for an INSERT > as USING is for filtering records, not checking that records being added > to the table are valid. > > To try and clarify- by explicitly setting both USING and WITH CHECK, you > *are* able to INSERT records which are not visible to you. We felt that > was an important capability to support.
I find it a bit of a limitation that I can't specify both INSERT and UPDATE for a policy. I'd want to be able to specify something like this: CREATE POLICY no_greys_allowed ON colours FOR INSERT, UPDATE WITH CHECK (name NOT IN ('grey','gray')); I would expect this to be rather common to prevent certain values making their way into a table. Instead I'd have to create 2 policies as it stands. In order to debug issues with accessing table data, perhaps it would be useful to output the name of the policy that was violated. If a table had 20 policies on, it could become time-consuming to debug. I keep getting tripped up by overlapping policies. On the one hand, I created a policy to ensure rows being added or selected have a "visible" column set to true. On the other hand, I have a policy that ensures that the name of a colour doesn't appear in a list. Policy 1 is violated until policy 2 is added: (using the table I created in a previous post on this thread...) # create policy must_be_visible ON colours for all to joe using (visible = true) with check (visible = true); CREATE POLICY \c - joe > insert into colours (name, visible) values ('pink',false); ERROR: new row violates WITH CHECK OPTION for "colours" DETAIL: Failing row contains (28, pink, f). \c - thom # create policy no_greys_allowed on colours for insert with check (name not in ('grey','gray')); CREATE POLICY \c - joe # insert into colours (name, visible) values ('pink',false); INSERT 0 1 I expected this to still trigger an error due to the first policy. Am I to infer from this that the policy model is permissive rather than restrictive? I've also attached a few corrections for the docs. Thom
diff --git a/doc/src/sgml/ref/alter_policy.sgml b/doc/src/sgml/ref/alter_policy.sgml index 37615fc..ab717f3 100644 --- a/doc/src/sgml/ref/alter_policy.sgml +++ b/doc/src/sgml/ref/alter_policy.sgml @@ -94,7 +94,7 @@ ALTER POLICY <replaceable class="parameter">name</replaceable> ON <replaceable c security-barrier qualification to queries which use the table automatically. If multiple policies are being applied for a given table then they are all combined and added using OR. The USING - expression applies to records which are being retrived from the table. + expression applies to records which are being retrieved from the table. </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml index 3c5bdc6..eff062c 100644 --- a/doc/src/sgml/ref/create_policy.sgml +++ b/doc/src/sgml/ref/create_policy.sgml @@ -81,7 +81,7 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable referenced table. These issues can be addressed by carefully crafting policies which prevent users from being able to insert, delete, or update records at all which might possibly indicate a value they are not otherwise - able to see, or by using generated values (eg: surrogate keys) instead. + able to see, or by using generated values (e.g.: surrogate keys) instead. </para> <para> @@ -218,7 +218,7 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable records from the relation which pass the <literal>SELECT</literal> policy will be returned, even if other records exist in the relation. The <literal>SELECT</literal> policy only accepts the USING expression - as it only ever applies in cases where records are being retrived from + as it only ever applies in cases where records are being retrieved from the relation. </para> </listitem> @@ -272,7 +272,7 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable for the <literal>DELETE</literal>, and rows which are not visible through the <literal>SELECT</literal> policy may be deleted if they pass the <literal>DELETE</literal> USING policy. The - <literal>DELETE</literal> policy only accept the USING expression as + <literal>DELETE</literal> policy only accepts the USING expression as it only ever applies in cases where records are being extracted from the relation for deletion. </para>
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers