On 19 September 2014 17:54, Stephen Frost <[email protected]> wrote:
>
> Thom,
>
> * Thom Brown ([email protected]) wrote:
> > On 19 September 2014 17:32, Stephen Frost <[email protected]> wrote:
> > > * Thom Brown ([email protected]) wrote:
> > > > On 14 September 2014 16:38, Stephen Frost <[email protected]> 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
