From 48a9586881872d4b8c9ca77e0c0da48db611e326 Mon Sep 17 00:00:00 2001 From: Mike Palmiotto Date: Wed, 24 May 2017 16:54:49 +0000 Subject: [PATCH] Add RLS support to partitioned tables This is needed to get RLS policies to apply to the parent partitioned table. Without this change partitioned tables are skipped. --- src/backend/rewrite/rewriteHandler.c | 3 +- src/test/regress/sql/rowsecurity.sql | 223 +++++++++++++++++++++++++++++++++++ 2 files changed, 225 insertions(+), 1 deletion(-) diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c index 35ff8bb..6cd73c1 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -1835,7 +1835,8 @@ fireRIRrules(Query *parsetree, List *activeRIRs, bool forUpdatePushedDown) /* Only normal relations can have RLS policies */ if (rte->rtekind != RTE_RELATION || - rte->relkind != RELKIND_RELATION) + (rte->relkind != RELKIND_RELATION && + rte->relkind != RELKIND_PARTITIONED_TABLE)) continue; rel = heap_open(rte->relid, NoLock); diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql index 1b6896e..c4ba136 100644 --- a/src/test/regress/sql/rowsecurity.sql +++ b/src/test/regress/sql/rowsecurity.sql @@ -44,6 +44,7 @@ GRANT EXECUTE ON FUNCTION f_leak(text) TO public; -- BASIC Row-Level Security Scenario SET SESSION AUTHORIZATION regress_rls_alice; + CREATE TABLE uaccount ( pguser name primary key, seclv int @@ -308,6 +309,228 @@ SET row_security TO OFF; SELECT * FROM t1 WHERE f_leak(b); EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b); +-- +-- Partitioned Tables +-- + +SET SESSION AUTHORIZATION regress_rls_alice; + +CREATE TABLE part_category ( + cid int primary key, + cname text +); +GRANT ALL ON part_category TO public; +INSERT INTO part_category VALUES + (11, 'fiction'), + (55, 'satire'), + (99, 'nonfiction'); + +CREATE TABLE part_document ( + did int, + cid int, + dlevel int not null, + dauthor name, + dtitle text +) PARTITION BY RANGE (cid); +GRANT ALL ON part_document TO public; + +-- Create partitions for document categories +CREATE TABLE part_document_fiction ( + LIKE part_document INCLUDING ALL +) PARTITION BY RANGE (dlevel); + +CREATE TABLE part_document_satire ( + LIKE part_document INCLUDING ALL +) PARTITION BY RANGE (dlevel); + +CREATE TABLE part_document_nonfiction ( + LIKE part_document INCLUDING ALL +) PARTITION BY RANGE (dlevel); + +ALTER TABLE part_document ATTACH PARTITION part_document_fiction FOR VALUES FROM ('11') TO ('12'); +ALTER TABLE part_document ATTACH PARTITION part_document_satire FOR VALUES FROM ('55') TO ('56'); +ALTER TABLE part_document ATTACH PARTITION part_document_nonfiction FOR VALUES FROM ('99') TO ('100'); + +-- Create partitions for document levels +CREATE TABLE part_document_fiction_1 (LIKE part_document_fiction INCLUDING ALL); +CREATE TABLE part_document_fiction_2 (LIKE part_document_fiction INCLUDING ALL); +CREATE TABLE part_document_satire_1 (LIKE part_document_satire INCLUDING ALL); +CREATE TABLE part_document_satire_2 (LIKE part_document_satire INCLUDING ALL); +CREATE TABLE part_document_nonfiction_1 (LIKE part_document_nonfiction INCLUDING ALL); +CREATE TABLE part_document_nonfiction_2 (LIKE part_document_nonfiction INCLUDING ALL); + +GRANT ALL ON part_document_fiction_1 TO public; +GRANT ALL ON part_document_fiction_2 TO public; +GRANT ALL ON part_document_satire_1 TO public; +GRANT ALL ON part_document_satire_2 TO public; +GRANT ALL ON part_document_nonfiction_1 TO public; +GRANT ALL ON part_document_nonfiction_2 TO public; + +ALTER TABLE part_document_fiction ATTACH PARTITION part_document_fiction_1 FOR VALUES FROM ('1') TO ('2'); +ALTER TABLE part_document_fiction ATTACH PARTITION part_document_fiction_2 FOR VALUES FROM ('2') TO ('3'); +ALTER TABLE part_document_satire ATTACH PARTITION part_document_satire_1 FOR VALUES FROM ('1') TO ('2'); +ALTER TABLE part_document_satire ATTACH PARTITION part_document_satire_2 FOR VALUES FROM ('2') TO ('3'); +ALTER TABLE part_document_nonfiction ATTACH PARTITION part_document_nonfiction_1 FOR VALUES FROM ('1') TO ('2'); +ALTER TABLE part_document_nonfiction ATTACH PARTITION part_document_nonfiction_2 FOR VALUES FROM ('2') TO ('3'); + +INSERT INTO part_document VALUES + ( 1, 11, 1, 'regress_rls_bob', 'my first novel'), + ( 2, 11, 2, 'regress_rls_bob', 'my second novel'), + ( 3, 99, 2, 'regress_rls_bob', 'my science textbook'), + ( 4, 55, 1, 'regress_rls_bob', 'my first satire'), + ( 5, 99, 2, 'regress_rls_bob', 'my history book'), + ( 6, 11, 1, 'regress_rls_carol', 'great science fiction'), + ( 7, 99, 2, 'regress_rls_carol', 'great technology book'), + ( 8, 55, 2, 'regress_rls_carol', 'great satire'), + ( 9, 11, 1, 'regress_rls_dave', 'awesome science fiction'), + (10, 99, 2, 'regress_rls_dave', 'awesome technology book'); + +ALTER TABLE part_document ENABLE ROW LEVEL SECURITY; +ALTER TABLE part_document_fiction ENABLE ROW LEVEL SECURITY; +ALTER TABLE part_document_fiction_1 ENABLE ROW LEVEL SECURITY; +ALTER TABLE part_document_fiction_2 ENABLE ROW LEVEL SECURITY; +ALTER TABLE part_document_satire ENABLE ROW LEVEL SECURITY; +ALTER TABLE part_document_satire_1 ENABLE ROW LEVEL SECURITY; +ALTER TABLE part_document_satire_2 ENABLE ROW LEVEL SECURITY; +ALTER TABLE part_document_nonfiction ENABLE ROW LEVEL SECURITY; +ALTER TABLE part_document_nonfiction_1 ENABLE ROW LEVEL SECURITY; +ALTER TABLE part_document_nonfiction_2 ENABLE ROW LEVEL SECURITY; + +-- user's security level must be higher than or equal to document's +CREATE POLICY pp1 ON part_document AS PERMISSIVE + USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user)); +CREATE POLICY pp1_fiction ON part_document AS PERMISSIVE + USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user)); +CREATE POLICY pp1_satire ON part_document AS PERMISSIVE + USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user)); +CREATE POLICY pp1_nonfiction ON part_document AS PERMISSIVE + USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user)); + +-- try to create a policy of bogus type +CREATE POLICY pp1 ON part_document AS UGLY + USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user)); +CREATE POLICY pp1_fiction ON part_document_fiction AS UGLY + USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user)); +CREATE POLICY pp1_satire ON part_document_satire AS UGLY + USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user)); +CREATE POLICY pp1_nonfiction ON part_document_nonfiction AS UGLY + USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user)); + +-- but Dave isn't allowed to read any documents with cid 55 +-- this is to make sure that we sort the policies by name first +-- when applying WITH CHECK, a later INSERT by Dave should fail due +-- to pp1r first +CREATE POLICY pp2r ON part_document AS RESTRICTIVE TO regress_rls_dave + USING (cid <> 55 AND cid < 99); +CREATE POLICY pp2r_fiction ON part_document_fiction AS RESTRICTIVE TO regress_rls_dave + USING (cid <> 55 AND cid < 99); +CREATE POLICY pp2r_satire ON part_document_satire AS RESTRICTIVE TO regress_rls_dave + USING (cid <> 55 AND cid < 99); +CREATE POLICY pp2r_nonfiction ON part_document_nonfiction AS RESTRICTIVE TO regress_rls_dave + USING (cid <> 55 AND cid < 99); + +-- and Dave is only allowed to see nonfiction. +-- this should fail before partition constraints +CREATE POLICY pp1r ON part_document AS RESTRICTIVE TO regress_rls_dave + USING (cid <> 55); +CREATE POLICY pp1r_fiction ON part_document_fiction AS RESTRICTIVE TO regress_rls_dave + USING (cid <> 55); +CREATE POLICY pp1r_satire ON part_document_satire AS RESTRICTIVE TO regress_rls_dave + USING (cid <> 55); +CREATE POLICY pp1r_nonfiction ON part_document_nonfiction AS RESTRICTIVE TO regress_rls_dave + USING (cid <> 55); + +\dp +\d part_document +SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename = 'part_document' ORDER BY policyname; + +-- viewpoint from regress_rls_bob +SET SESSION AUTHORIZATION regress_rls_bob; +SET row_security TO ON; +SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did; +SELECT * FROM part_document NATURAL JOIN part_category WHERE f_leak(dtitle) ORDER BY did; + +-- try a sampled version +SELECT * FROM part_document TABLESAMPLE BERNOULLI(50) REPEATABLE(0) + WHERE f_leak(dtitle) ORDER BY did; + +-- viewpoint from regress_rls_carol +SET SESSION AUTHORIZATION regress_rls_carol; +SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did; +SELECT * FROM part_document NATURAL JOIN part_category WHERE f_leak(dtitle) ORDER BY did; + +-- try a sampled version +SELECT * FROM part_document TABLESAMPLE BERNOULLI(50) REPEATABLE(0) + WHERE f_leak(dtitle) ORDER BY did; + +EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); +EXPLAIN (COSTS OFF) SELECT * FROM part_document NATURAL JOIN part_category WHERE f_leak(dtitle); + +-- viewpoint from regress_rls_dave +SET SESSION AUTHORIZATION regress_rls_dave; +SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did; +SELECT * FROM part_document NATURAL JOIN part_category WHERE f_leak(dtitle) ORDER BY did; + +EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); +EXPLAIN (COSTS OFF) SELECT * FROM part_document NATURAL JOIN part_category WHERE f_leak(dtitle); + +-- 99 would technically fail for both pp2r and pp1r, but we should get an error +-- back from pp1r for this because it sorts first +INSERT INTO part_document VALUES (100, 55, 1, 'regress_rls_dave', 'testing sorting of policies'); -- fail +-- Just to see a pp2r error +INSERT INTO part_document VALUES (100, 99, 1, 'regress_rls_dave', 'testing sorting of policies'); -- fail + +-- We should get an RLS error here even though inserting documents +-- with cid 55 into fiction/nonfiction partitions is a constraint violation. +-- RLS policies are checked before constraints. +INSERT INTO part_document_fiction VALUES (100, 55, 1, 'regress_rls_dave', 'testing sorting of policies'); -- fail +INSERT INTO part_document_nonfiction VALUES (100, 55, 1, 'regress_rls_dave', 'testing sorting of policies'); -- fail + +-- only owner can change policies +ALTER POLICY pp1 ON part_document USING (true); --fail +DROP POLICY pp1 ON part_document; --fail + +SET SESSION AUTHORIZATION regress_rls_alice; +ALTER POLICY pp1 ON part_document USING (dauthor = current_user); + +-- viewpoint from regress_rls_bob again +SET SESSION AUTHORIZATION regress_rls_bob; +SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did; +SELECT * FROM part_document NATURAL JOIN part_category WHERE f_leak(dtitle) ORDER by did; + +-- viewpoint from rls_regres_carol again +SET SESSION AUTHORIZATION regress_rls_carol; +SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did; +SELECT * FROM part_document NATURAL JOIN part_category WHERE f_leak(dtitle) ORDER by did; + +EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); +EXPLAIN (COSTS OFF) SELECT * FROM part_document NATURAL JOIN part_category WHERE f_leak(dtitle); + +-- database superuser does bypass RLS policy when enabled +RESET SESSION AUTHORIZATION; +SET row_security TO ON; +SELECT * FROM part_document; + +-- database superuser does bypass RLS policy when disabled +RESET SESSION AUTHORIZATION; +SET row_security TO OFF; +SELECT * FROM part_document; + +-- database non-superuser with bypass privilege can bypass RLS policy when disabled +SET SESSION AUTHORIZATION regress_rls_exempt_user; +SET row_security TO OFF; +SELECT * FROM part_document; + +-- RLS policy does not apply to table owner when RLS enabled. +SET SESSION AUTHORIZATION regress_rls_alice; +SET row_security TO ON; +SELECT * FROM part_document; + +-- RLS policy does not apply to table owner when RLS disabled. +SET SESSION AUTHORIZATION regress_rls_alice; +SET row_security TO OFF; +SELECT * FROM part_document; + ----- Dependencies ----- SET SESSION AUTHORIZATION regress_rls_alice; SET row_security TO ON; -- 2.7.4