On Mon, Jun 5, 2017 at 10:36 AM, Robert Haas <robertmh...@gmail.com> wrote:
> On Mon, Jun 5, 2017 at 10:20 AM, Joe Conway <m...@joeconway.com> wrote:
>> Unless Robert objects, I'll work with Mike to get a fix posted and
>> committed in the next day or two.
>
> That would be great. Thanks.
I have the updated patch with rowsecurity regression tests and rebased
on master. I've run these and verified locally by feeding
rowsecurity.sql to psql, but have yet to get the full regression suite
passing -- it's failing on the constraints regtest and then gets stuck
in recovery. Undoubtedly something to do with my
configuration/environment over here. I'm working through those issues
right now. In the meantime, if you want to see the regression tests as
they stand, please see the attached patch.
Thanks,
--
Mike Palmiotto
Software Engineer
Crunchy Data Solutions
https://crunchydata.com
From 48a9586881872d4b8c9ca77e0c0da48db611e326 Mon Sep 17 00:00:00 2001
From: Mike Palmiotto <mike.palmio...@crunchydata.com>
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
