Thanks! * Thom Brown (thom@linux.com) wrote: > On 14 September 2014 16:38, Stephen Frost <sfrost@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.
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
And if this did work, but I only violated the USING clause, would this still say the WITH CHECK clause was the cause?