test.sql

application/octet-stream

Download

-- Setup
DROP TABLE IF EXISTS foo, bar CASCADE;
DROP USER IF EXISTS foo;

CREATE TABLE foo(a int PRIMARY KEY);
INSERT INTO foo SELECT * FROM generate_series(-10, 10);

CREATE USER foo;
GRANT SELECT ON foo TO foo;

-- Self-referencing RLS qual - goes into infinite recursion in planner
ALTER TABLE foo SET ROW LEVEL SECURITY (a < (SELECT max(a) FROM foo));

SET SESSION AUTHORIZATION foo;
EXPLAIN SELECT * FROM foo;
SELECT * FROM foo;
RESET SESSION AUTHORIZATION;

-- Simple RLS qual to prevent user foo from seeing rows with a <= 0
ALTER TABLE foo SET ROW LEVEL SECURITY (a > 0);

SET SESSION AUTHORIZATION foo;
SELECT * FROM foo;
EXPLAIN SELECT * FROM foo WHERE a = 5;

-- User foo can bypass RLS qual with another RLS qual on own table
CREATE TABLE bar(a int);
INSERT INTO bar SELECT * FROM generate_series(-20, 20);
ALTER TABLE bar SET ROW LEVEL SECURITY (a IN (SELECT * FROM foo));
EXPLAIN SELECT * FROM bar;
ALTER TABLE bar SET ROW LEVEL SECURITY (EXISTS(SELECT 1 FROM foo
                                                WHERE foo.a = bar.a));
EXPLAIN SELECT * FROM bar;
SELECT * FROM bar;

-- RLS qual referencing a VIEW fails
CREATE VIEW foo_v AS SELECT * FROM foo;
ALTER TABLE bar SET ROW LEVEL SECURITY (a IN (SELECT * FROM foo_v));
EXPLAIN SELECT * FROM bar;
SELECT * FROM bar;