BUG #14060: row security policy does not work for updatable views - Mailing list pgsql-bugs

From adudnik@gmail.com
Subject BUG #14060: row security policy does not work for updatable views
Date
Msg-id 20160402200106.2623.11425@wrigleys.postgresql.org
Whole thread Raw
Responses Re: BUG #14060: row security policy does not work for updatable views
List pgsql-bugs
The following bug has been logged on the website:

Bug reference:      14060
Logged by:          Artur Dudnik
Email address:      adudnik@gmail.com
PostgreSQL version: 9.5.1
Operating system:   Windows
Description:

-- row security policy does not work for updatable views.

-- usage scenario:
--   1. make a table
--   2. enable row security for a role
--   3. create view for restricted table
--   4. grant to restricted role select and update for view and table

-- expected behavior - view and table could select/update same records
-- bug behavior - view return/update all rows (policy ignored) and
security_barrier too


set role postgres;

CREATE TABLE t AS SELECT n, 'secret'||n AS secret FROM generate_series(1,20)
n;

create role test;

grant select, update on t to test;
ALTER TABLE t ENABLE ROW LEVEL SECURITY;
CREATE POLICY t_all ON t TO test USING (n % 2 = 1);
CREATE VIEW t_odd WITH (security_barrier) AS SELECT * FROM t ;
CREATE VIEW t2_odd AS SELECT * FROM t ;
CREATE VIEW t3_odd WITH (security_barrier) AS SELECT * FROM t where n % 2 =
1;

grant select, update on t_odd to test;
grant select, update on t2_odd to test;
grant select, update on t3_odd to test;

set role test;

update t3_odd set secret = '!!!' where n in (2, 1);

select * from t3_odd;

update t_odd set secret = '!!!' where n in (4, 3);

select * from t_odd;

update t2_odd set secret = '!!!' where n in (6, 5);

select * from t2_odd;

set role postgres;

select * from t;

drop view t_odd cascade;
drop view t2_odd cascade;
drop table t cascade;
drop role test;

pgsql-bugs by date:

Previous
From: 德哥
Date:
Subject: Re: BUG #14059: BUG? function in select clause called more times when use offset
Next
From: Stephen Frost
Date:
Subject: Re: BUG #14060: row security policy does not work for updatable views