"ROW LEVEL SECURITY" and INCREMENTAL MATERIALIZED VIEW.

If ROW LEVEL SECURITY is set for the source table after creating the INCREMENTAL MATELIALIZED VIEW, the search results by that are not reflected. After setting ROW LEVEL SECURITY (similar to normal MATERIALIZED VIEW), you need to execute REFRESH MATERILALIZED VIEW and reflect the result. (Not limited to this, but in general cases where search results change by means other than DML)

I propose to add this note to the document (rules.sgml).

execute log.

[ec2-user@ip-10-0-1-10 rls]$ psql testdb -e -f rls.sql
CREATE USER user_a;
CREATE ROLE
CREATE TABLE test (id int, data text);
CREATE TABLE
GRANT ALL ON TABLE test TO user_a;
GRANT
GRANT ALL ON SCHEMA public  TO user_a;
GRANT
SET ROLE user_a;
SET
INSERT INTO test VALUES (1,'A'),(2,'B'),(3,'C');
INSERT 0 3
SELECT * FROM test;
 id | data
----+------
  1 | A
  2 | B
  3 | C
(3 rows)

CREATE VIEW test_v AS SELECT * FROM test;
CREATE VIEW
CREATE MATERIALIZED VIEW test_mv AS SELECT * FROM test;
SELECT 3
CREATE INCREMENTAL MATERIALIZED VIEW test_imv AS SELECT * FROM test;
SELECT 3
SELECT * FROM test_v;
 id | data
----+------
  1 | A
  2 | B
  3 | C
(3 rows)

SELECT * FROM test_mv;
 id | data
----+------
  1 | A
  2 | B
  3 | C
(3 rows)

SELECT * FROM test_imv;
 id | data
----+------
  3 | C
  1 | A
  2 | B
(3 rows)

RESET ROLE;
RESET
CREATE POLICY test_AAA ON test FOR SELECT TO user_a USING (data = 'A');
CREATE POLICY
ALTER TABLE test ENABLE ROW LEVEL SECURITY ;
ALTER TABLE
SET ROLE user_a;
SET
SELECT * FROM test_v;
 id | data
----+------
  1 | A
(1 row)

SELECT * FROM test_mv;
 id | data
----+------
  1 | A
  2 | B
  3 | C
(3 rows)

SELECT * FROM test_imv;
 id | data
----+------
  3 | C
  1 | A
  2 | B
(3 rows)

REFRESH MATERIALIZED VIEW test_mv;
REFRESH MATERIALIZED VIEW
REFRESH MATERIALIZED VIEW test_imv;
REFRESH MATERIALIZED VIEW
SELECT * FROM test_mv;
 id | data
----+------
  1 | A
(1 row)

SELECT * FROM test_imv;
 id | data
----+------
  1 | A
(1 row)

RESET ROLE;
RESET
REVOKE ALL ON TABLE test FROM user_a;
REVOKE
REVOKE ALL ON TABLE test_v FROM user_a;
REVOKE
REVOKE ALL ON TABLE test_mv FROM user_a;
REVOKE
REVOKE ALL ON TABLE test_imv FROM user_a;
REVOKE
REVOKE ALL ON SCHEMA public FROM user_a;
REVOKE
DROP TABLE test CASCADE;
psql:rls.sql:40: NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to view test_v
drop cascades to materialized view test_mv
drop cascades to materialized view test_imv
DROP TABLE
DROP USER user_a;
DROP ROLE
[ec2-user@ip-10-0-1-10 rls]$

sraoss / pgsql-ivm

"ROW LEVEL SECURITY" and INCREMENTAL MATERIALIZED VIEW. #71