( 6, 22, 1, 'rls_regress_user2', 'great science fiction'),
( 7, 33, 2, 'rls_regress_user2', 'great technology book'),
( 8, 44, 1, 'rls_regress_user2', 'great manga');
-VACUUM ANALYZE category;
-VACUUM ANALYZE document;
ALTER TABLE document ENABLE ROW LEVEL SECURITY;
-- user's security level must be higher than or equal to document's
CREATE POLICY p1 ON document
44 | 8 | 1 | rls_regress_user2 | great manga | manga
(4 rows)
-SELECT * FROM document TABLESAMPLE BERNOULLI (50) REPEATABLE(1) WHERE f_leak(dtitle) ORDER BY did;
-NOTICE: f_leak => my first novel
+-- try a sampled version
+SELECT * FROM document TABLESAMPLE BERNOULLI(50) REPEATABLE(0)
+ WHERE f_leak(dtitle) ORDER BY did;
NOTICE: f_leak => my first manga
NOTICE: f_leak => great science fiction
+NOTICE: f_leak => great manga
did | cid | dlevel | dauthor | dtitle
-----+-----+--------+-------------------+-----------------------
- 1 | 11 | 1 | rls_regress_user1 | my first novel
4 | 44 | 1 | rls_regress_user1 | my first manga
6 | 22 | 1 | rls_regress_user2 | great science fiction
+ 8 | 44 | 1 | rls_regress_user2 | great manga
(3 rows)
-- viewpoint from rls_regress_user2
44 | 8 | 1 | rls_regress_user2 | great manga | manga
(8 rows)
-SELECT * FROM document TABLESAMPLE BERNOULLI (50) REPEATABLE(1) WHERE f_leak(dtitle) ORDER BY did;
-NOTICE: f_leak => my first novel
-NOTICE: f_leak => my second novel
+-- try a sampled version
+SELECT * FROM document TABLESAMPLE BERNOULLI(50) REPEATABLE(0)
+ WHERE f_leak(dtitle) ORDER BY did;
NOTICE: f_leak => my first manga
+NOTICE: f_leak => my second manga
NOTICE: f_leak => great science fiction
-NOTICE: f_leak => great technology book
+NOTICE: f_leak => great manga
did | cid | dlevel | dauthor | dtitle
-----+-----+--------+-------------------+-----------------------
- 1 | 11 | 1 | rls_regress_user1 | my first novel
- 2 | 11 | 2 | rls_regress_user1 | my second novel
4 | 44 | 1 | rls_regress_user1 | my first manga
+ 5 | 44 | 2 | rls_regress_user1 | my second manga
6 | 22 | 1 | rls_regress_user2 | great science fiction
- 7 | 33 | 2 | rls_regress_user2 | great technology book
-(5 rows)
+ 8 | 44 | 1 | rls_regress_user2 | great manga
+(4 rows)
EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
QUERY PLAN
(7 rows)
EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
- QUERY PLAN
-----------------------------------------------------------------
- Nested Loop
- Join Filter: (document.cid = category.cid)
- -> Subquery Scan on document
- Filter: f_leak(document.dtitle)
- -> Seq Scan on document document_1
- Filter: (dlevel <= $0)
- InitPlan 1 (returns $0)
- -> Index Scan using uaccount_pkey on uaccount
- Index Cond: (pguser = "current_user"())
+ QUERY PLAN
+----------------------------------------------------------------------
+ Hash Join
+ Hash Cond: (category.cid = document.cid)
-> Seq Scan on category
-(10 rows)
+ -> Hash
+ -> Subquery Scan on document
+ Filter: f_leak(document.dtitle)
+ -> Seq Scan on document document_1
+ Filter: (dlevel <= $0)
+ InitPlan 1 (returns $0)
+ -> Index Scan using uaccount_pkey on uaccount
+ Index Cond: (pguser = "current_user"())
+(11 rows)
-- only owner can change policies
ALTER POLICY p1 ON document USING (true); --fail
QUERY PLAN
----------------------------------------------------
Nested Loop
- Join Filter: (document.cid = category.cid)
-> Subquery Scan on document
Filter: f_leak(document.dtitle)
-> Seq Scan on document document_1
Filter: (dauthor = "current_user"())
- -> Seq Scan on category
+ -> Index Scan using category_pkey on category
+ Index Cond: (cid = document.cid)
(7 rows)
-- interaction of FK/PK constraints
SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid;
did | cid | dlevel | dauthor | dtitle | cid | cname
-----+-----+--------+-------------------+--------------------+-----+------------
- 1 | 11 | 1 | rls_regress_user1 | my first novel | 11 | novel
2 | 11 | 2 | rls_regress_user1 | my second novel | 11 | novel
- 3 | 22 | 2 | rls_regress_user1 | my science fiction | |
- 4 | 44 | 1 | rls_regress_user1 | my first manga | |
- 5 | 44 | 2 | rls_regress_user1 | my second manga | |
+ 1 | 11 | 1 | rls_regress_user1 | my first novel | 11 | novel
| | | | | 33 | technology
+ 5 | 44 | 2 | rls_regress_user1 | my second manga | |
+ 4 | 44 | 1 | rls_regress_user1 | my first manga | |
+ 3 | 22 | 2 | rls_regress_user1 | my science fiction | |
(6 rows)
DELETE FROM category WHERE cid = 33; -- fails with FK violation
ERROR: update or delete on table "category" violates foreign key constraint "document_cid_fkey" on table "document"
-DETAIL: Key (cid)=(33) is still referenced from table "document".
+DETAIL: Key is still referenced from table "document".
-- can insert FK referencing invisible PK
SET SESSION AUTHORIZATION rls_regress_user2;
SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid;
did | cid | dlevel | dauthor | dtitle | cid | cname
-----+-----+--------+-------------------+-----------------------+-----+-----------------
6 | 22 | 1 | rls_regress_user2 | great science fiction | 22 | science fiction
- 7 | 33 | 2 | rls_regress_user2 | great technology book | |
8 | 44 | 1 | rls_regress_user2 | great manga | 44 | manga
+ 7 | 33 | 2 | rls_regress_user2 | great technology book | |
(3 rows)
INSERT INTO document VALUES (10, 33, 1, current_user, 'hoge');
CREATE POLICY p1 ON y1 FOR ALL USING (a % 2 = 0);
CREATE POLICY p2 ON y1 FOR SELECT USING (a > 2);
CREATE POLICY p1 ON y1 FOR SELECT USING (a % 2 = 1); --fail
-ERROR: policy "p1" for relation "y1" already exists
+ERROR: policy "p1" for table "y1" already exists
CREATE POLICY p1 ON y2 FOR ALL USING (a % 2 = 0); --OK
ALTER TABLE y1 ENABLE ROW LEVEL SECURITY;
ALTER TABLE y2 ENABLE ROW LEVEL SECURITY;
(2 rows)
CREATE TABLE test_qual_pushdown (
- abc text
+ abc text
);
INSERT INTO test_qual_pushdown VALUES ('abc'),('def');
SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(abc);
6,1679091c5a880faf6fb5e6087eb1b2dc
8,c9f0f895fb98ab9159f51fd0297e236d
10,d3d9446802a44259755d38e6d163e820
--- Check COPY TO as user without permissions.SET row_security TO OFF;
+-- Check COPY TO as user without permissions. SET row_security TO OFF;
SET SESSION AUTHORIZATION rls_regress_user2;
SET row_security TO OFF;
COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - insufficient to bypass rls
SET row_security TO FORCE;
COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - permission denied
ERROR: permission denied for relation copy_t
+-- Check COPY relation TO; keep it just one row to avoid reordering issues
+RESET SESSION AUTHORIZATION;
+SET row_security TO ON;
+CREATE TABLE copy_rel_to (a integer, b text);
+CREATE POLICY p1 ON copy_rel_to USING (a % 2 = 0);
+ALTER TABLE copy_rel_to ENABLE ROW LEVEL SECURITY;
+GRANT ALL ON copy_rel_to TO rls_regress_user1, rls_regress_exempt_user;
+INSERT INTO copy_rel_to VALUES (1, md5('1'));
+-- Check COPY TO as Superuser/owner.
+RESET SESSION AUTHORIZATION;
+SET row_security TO OFF;
+COPY copy_rel_to TO STDOUT WITH DELIMITER ',';
+1,c4ca4238a0b923820dcc509a6f75849b
+SET row_security TO ON;
+COPY copy_rel_to TO STDOUT WITH DELIMITER ',';
+1,c4ca4238a0b923820dcc509a6f75849b
+SET row_security TO FORCE;
+COPY copy_rel_to TO STDOUT WITH DELIMITER ',';
+-- Check COPY TO as user with permissions.
+SET SESSION AUTHORIZATION rls_regress_user1;
+SET row_security TO OFF;
+COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - insufficient to bypass rls
+ERROR: insufficient privilege to bypass row security.
+SET row_security TO ON;
+COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
+SET row_security TO FORCE;
+COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
+-- Check COPY TO as user with permissions and BYPASSRLS
+SET SESSION AUTHORIZATION rls_regress_exempt_user;
+SET row_security TO OFF;
+COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
+1,c4ca4238a0b923820dcc509a6f75849b
+SET row_security TO ON;
+COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
+SET row_security TO FORCE;
+COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
+-- Check COPY TO as user without permissions. SET row_security TO OFF;
+SET SESSION AUTHORIZATION rls_regress_user2;
+SET row_security TO OFF;
+COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - permission denied
+ERROR: permission denied for relation copy_rel_to
+SET row_security TO ON;
+COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - permission denied
+ERROR: permission denied for relation copy_rel_to
+SET row_security TO FORCE;
+COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - permission denied
+ERROR: permission denied for relation copy_rel_to
-- Check COPY FROM as Superuser/owner.
RESET SESSION AUTHORIZATION;
SET row_security TO OFF;
ERROR: permission denied for relation copy_t
RESET SESSION AUTHORIZATION;
DROP TABLE copy_t;
+DROP TABLE copy_rel_to CASCADE;
+-- Check WHERE CURRENT OF
+SET SESSION AUTHORIZATION rls_regress_user0;
+CREATE TABLE current_check (currentid int, payload text, rlsuser text);
+GRANT ALL ON current_check TO PUBLIC;
+INSERT INTO current_check VALUES
+ (1, 'abc', 'rls_regress_user1'),
+ (2, 'bcd', 'rls_regress_user1'),
+ (3, 'cde', 'rls_regress_user1'),
+ (4, 'def', 'rls_regress_user1');
+CREATE POLICY p1 ON current_check FOR SELECT USING (currentid % 2 = 0);
+CREATE POLICY p2 ON current_check FOR DELETE USING (currentid = 4 AND rlsuser = current_user);
+CREATE POLICY p3 ON current_check FOR UPDATE USING (currentid = 4) WITH CHECK (rlsuser = current_user);
+ALTER TABLE current_check ENABLE ROW LEVEL SECURITY;
+SET SESSION AUTHORIZATION rls_regress_user1;
+-- Can SELECT even rows
+SELECT * FROM current_check;
+ currentid | payload | rlsuser
+-----------+---------+-------------------
+ 2 | bcd | rls_regress_user1
+ 4 | def | rls_regress_user1
+(2 rows)
+
+-- Cannot UPDATE row 2
+UPDATE current_check SET payload = payload || '_new' WHERE currentid = 2 RETURNING *;
+ currentid | payload | rlsuser
+-----------+---------+---------
+(0 rows)
+
+BEGIN;
+DECLARE current_check_cursor SCROLL CURSOR FOR SELECT * FROM current_check;
+-- Returns rows that can be seen according to SELECT policy, like plain SELECT
+-- above (even rows)
+FETCH ABSOLUTE 1 FROM current_check_cursor;
+ currentid | payload | rlsuser
+-----------+---------+-------------------
+ 2 | bcd | rls_regress_user1
+(1 row)
+
+-- Still cannot UPDATE row 2 through cursor
+UPDATE current_check SET payload = payload || '_new' WHERE CURRENT OF current_check_cursor RETURNING *;
+ currentid | payload | rlsuser
+-----------+---------+---------
+(0 rows)
+
+-- Can update row 4 through cursor, which is the next visible row
+FETCH RELATIVE 1 FROM current_check_cursor;
+ currentid | payload | rlsuser
+-----------+---------+-------------------
+ 4 | def | rls_regress_user1
+(1 row)
+
+UPDATE current_check SET payload = payload || '_new' WHERE CURRENT OF current_check_cursor RETURNING *;
+ currentid | payload | rlsuser
+-----------+---------+-------------------
+ 4 | def_new | rls_regress_user1
+(1 row)
+
+SELECT * FROM current_check;
+ currentid | payload | rlsuser
+-----------+---------+-------------------
+ 2 | bcd | rls_regress_user1
+ 4 | def_new | rls_regress_user1
+(2 rows)
+
+-- Plan should be a subquery TID scan
+EXPLAIN (COSTS OFF) UPDATE current_check SET payload = payload WHERE CURRENT OF current_check_cursor;
+ QUERY PLAN
+---------------------------------------------------------------
+ Update on current_check current_check_1
+ -> Subquery Scan on current_check
+ -> LockRows
+ -> Tid Scan on current_check current_check_2
+ TID Cond: CURRENT OF current_check_cursor
+ Filter: (currentid = 4)
+(6 rows)
+
+-- Similarly can only delete row 4
+FETCH ABSOLUTE 1 FROM current_check_cursor;
+ currentid | payload | rlsuser
+-----------+---------+-------------------
+ 2 | bcd | rls_regress_user1
+(1 row)
+
+DELETE FROM current_check WHERE CURRENT OF current_check_cursor RETURNING *;
+ currentid | payload | rlsuser
+-----------+---------+---------
+(0 rows)
+
+FETCH RELATIVE 1 FROM current_check_cursor;
+ currentid | payload | rlsuser
+-----------+---------+-------------------
+ 4 | def | rls_regress_user1
+(1 row)
+
+DELETE FROM current_check WHERE CURRENT OF current_check_cursor RETURNING *;
+ currentid | payload | rlsuser
+-----------+---------+-------------------
+ 4 | def_new | rls_regress_user1
+(1 row)
+
+SELECT * FROM current_check;
+ currentid | payload | rlsuser
+-----------+---------+-------------------
+ 2 | bcd | rls_regress_user1
+(1 row)
+
+COMMIT;
+--
+-- check pg_stats view filtering
+--
+SET row_security TO ON;
+SET SESSION AUTHORIZATION rls_regress_user0;
+ANALYZE current_check;
+-- Stats visible
+SELECT row_security_active('current_check');
+ row_security_active
+---------------------
+ f
+(1 row)
+
+SELECT most_common_vals FROM pg_stats where tablename = 'current_check';
+ most_common_vals
+---------------------
+
+
+ {rls_regress_user1}
+(3 rows)
+
+SET SESSION AUTHORIZATION rls_regress_user1;
+-- Stats not visible
+SELECT row_security_active('current_check');
+ row_security_active
+---------------------
+ t
+(1 row)
+
+SELECT most_common_vals FROM pg_stats where tablename = 'current_check';
+ most_common_vals
+------------------
+(0 rows)
+
+--
+-- Collation support
+--
+BEGIN;
+SET row_security TO FORCE;
+CREATE TABLE coll_t (c) AS VALUES ('bar'::text);
+CREATE POLICY coll_p ON coll_t USING (c < ('foo'::text COLLATE "C"));
+ALTER TABLE coll_t ENABLE ROW LEVEL SECURITY;
+SELECT (string_to_array(polqual, ':'))[7] AS inputcollid FROM pg_policy WHERE polrelid = 'coll_t'::regclass;
+ inputcollid
+------------------
+ inputcollid 950
+(1 row)
+
+SELECT * FROM coll_t;
+ c
+-----
+ bar
+(1 row)
+
+ROLLBACK;
+--
+-- Shared Object Dependencies
+--
+RESET SESSION AUTHORIZATION;
+BEGIN;
+CREATE ROLE alice;
+CREATE ROLE bob;
+CREATE TABLE tbl1 (c) AS VALUES ('bar'::text);
+GRANT SELECT ON TABLE tbl1 TO alice;
+CREATE POLICY P ON tbl1 TO alice, bob USING (true);
+SELECT refclassid::regclass, deptype
+ FROM pg_depend
+ WHERE classid = 'pg_policy'::regclass
+ AND refobjid = 'tbl1'::regclass;
+ refclassid | deptype
+------------+---------
+ pg_class | a
+(1 row)
+
+SELECT refclassid::regclass, deptype
+ FROM pg_shdepend
+ WHERE classid = 'pg_policy'::regclass
+ AND refobjid IN ('alice'::regrole, 'bob'::regrole);
+ refclassid | deptype
+------------+---------
+ pg_authid | r
+ pg_authid | r
+(2 rows)
+
+SAVEPOINT q;
+DROP ROLE alice; --fails due to dependency on POLICY p
+ERROR: role "alice" cannot be dropped because some objects depend on it
+DETAIL: target of policy p on table tbl1
+privileges for table tbl1
+ROLLBACK TO q;
+ALTER POLICY p ON tbl1 TO bob USING (true);
+SAVEPOINT q;
+DROP ROLE alice; --fails due to dependency on GRANT SELECT
+ERROR: role "alice" cannot be dropped because some objects depend on it
+DETAIL: privileges for table tbl1
+ROLLBACK TO q;
+REVOKE ALL ON TABLE tbl1 FROM alice;
+SAVEPOINT q;
+DROP ROLE alice; --succeeds
+ROLLBACK TO q;
+SAVEPOINT q;
+DROP ROLE bob; --fails due to dependency on POLICY p
+ERROR: role "bob" cannot be dropped because some objects depend on it
+DETAIL: target of policy p on table tbl1
+ROLLBACK TO q;
+DROP POLICY p ON tbl1;
+SAVEPOINT q;
+DROP ROLE bob; -- succeeds
+ROLLBACK TO q;
+ROLLBACK; -- cleanup
+--
+-- Converting table to view
+--
+BEGIN;
+SET ROW_SECURITY = FORCE;
+CREATE TABLE t (c int);
+CREATE POLICY p ON t USING (c % 2 = 1);
+ALTER TABLE t ENABLE ROW LEVEL SECURITY;
+SAVEPOINT q;
+CREATE RULE "_RETURN" AS ON SELECT TO t DO INSTEAD
+ SELECT * FROM generate_series(1,5) t0(c); -- fails due to row level security enabled
+ERROR: could not convert table "t" to a view because it has row security enabled
+ROLLBACK TO q;
+ALTER TABLE t DISABLE ROW LEVEL SECURITY;
+SAVEPOINT q;
+CREATE RULE "_RETURN" AS ON SELECT TO t DO INSTEAD
+ SELECT * FROM generate_series(1,5) t0(c); -- fails due to policy p on t
+ERROR: could not convert table "t" to a view because it has row security policies
+ROLLBACK TO q;
+DROP POLICY p ON t;
+CREATE RULE "_RETURN" AS ON SELECT TO t DO INSTEAD
+ SELECT * FROM generate_series(1,5) t0(c); -- succeeds
+ROLLBACK;
--
-- Clean up objects
--
projects / postgresql / blobdiff