security barrier INSERT
Hi guys, I’m seeing some non-intuitive behavior with the new updateable security barrier views in 9.4. Below is the behavior of 9.4b3:
=# create table widget ( id integer);
CREATE TABLE
=# create view widget_sb WITH (security_barrier=true) AS SELECT * from widget where id = 22;
CREATE VIEW
=# insert into widget_sb (id) values (23);
INSERT 0 1
=# select * from widget;
id
----
23
(1 row)
I think the insert should fail, since the view can only contain widgets with id = 22, and the widget being inserted has id 23. In reality, the insert to the behind table succeeds as shown above, although it still remains absent from the view:
=# select * from widget_sb;
id
----
(0 rows)
IMHO this is nonintuitive, the intuitive behavior of a security_barrier view should be to forbid inserting rows that can’t appear in the view.
Have I missed something fundamental here?
Drew
Drew,
IMHO this is nonintuitive, the intuitive behavior of a security_barrier
view should be to forbid inserting rows that can’t appear in the view.
Isn't that what WITH CHECK OPTION is meant to accomplish?
-Adam
--
Adam Brightwell - adam.brightwell@crunchydatasolutions.com
Database Engineer - www.crunchydatasolutions.com