Re: RLS Design

Thom,

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?
>

Since RLS is built on top of the same mechanisms used for Security Barrier
Views, I figured I would check this case against that and, for the heck of
it, regular VIEWs as well. The result is the same error in both cases
(below and attached). I also verified that this issue exists for 9.4beta2
and the current REL9_4_STABLE branch. If this isn't the expected behavior
(I can't imagine that it is), I am certainly willing to dive into it
further and see what I can determine for a solution/recommendation. At any
rate, this appears to be a previously existing issue with WITH CHECK
OPTION. Thoughts?

postgres=# DROP TABLE IF EXISTS colors CASCADE;
NOTICE: table "colors" does not exist, skipping
DROP TABLE
postgres=# DROP ROLE IF EXISTS joe;
DROP ROLE
postgres=# CREATE ROLE joe LOGIN;
CREATE ROLE
postgres=# CREATE TABLE colors (name text, visible bool);
CREATE TABLE
postgres=# CREATE OR REPLACE VIEW v_colors_1 WITH (security_barrier) AS
postgres-# SELECT * FROM colors WHERE (name in ('blue', 'green',
'yellow'))
postgres-# WITH CHECK OPTION;
CREATE VIEW
postgres=# CREATE OR REPLACE VIEW v_colors_2 AS
postgres-# SELECT * FROM colors WHERE (name in ('blue', 'green',
'yellow'))
postgres-# WITH CHECK OPTION;
CREATE VIEW
postgres=# GRANT ALL ON v_colors_1, v_colors_2 TO joe;
GRANT
postgres=# \c - joe
You are now connected to database "postgres" as user "joe".
postgres=> INSERT INTO v_colors_1 (name, visible) VALUES ('blue', false);
ERROR: function with OID 0 does not exist
postgres=> INSERT INTO v_colors_2 (name, visible) VALUES ('blue', false);
ERROR: function with OID 0 does not exist

Thanks,
Adam

--
Adam Brightwell - adam(dot)brightwell(at)crunchydatasolutions(dot)com
Database Engineer - www.crunchydatasolutions.com