On Tue, Jun 6, 2017 at 9:12 PM, Michael Paquier
<michael.paqu...@gmail.com> wrote:
> On Wed, Jun 7, 2017 at 9:52 AM, Joe Conway <m...@joeconway.com> wrote:
>> Thanks Mike. I'll take a close look to verify output correctnes, but I
>> am concerned that the new tests are unnecessarily complex. Any other
>> opinions on that?
>
> Some tests would be good to have. Now, if I read those regression
> tests correctly, this is using 10 relations where two would be enough,
> one as the parent relation and one as a partition. Then policies apply
> on the parent relation. The same kind of policy is defined 4 times,
> and there is bloat with GRANT and ALTER TABLE commands.
I ended up narrowing it down to 4 tables (one parent and 3 partitions)
in order to demonstrate policy sorting and order of RLS/partition
constraint checking. It should be much more straight-forward now, but
let me know if there are any further recommended changes.
One thing that concerns me is the first EXPLAIN plan from regress_rls_dave:
+EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Append
+ InitPlan 1 (returns $0)
+ -> Index Scan using uaccount_pkey on uaccount
+ Index Cond: (pguser = CURRENT_USER)
+ -> Seq Scan on part_document_fiction
+ Filter: ((cid <> 55) AND (cid <> 55) AND (cid < 99) AND
(dlevel <= $0) AND f_leak(dtitle))
+ -> Seq Scan on part_document_satire
+ Filter: ((cid <> 55) AND (cid <> 55) AND (cid < 99) AND
(dlevel <= $0) AND f_leak(dtitle))
+(8 rows)
I would expect that both part_document_satire (cid == 55) and
part_document_nonfiction (cid == 99) would be excluded from the
explain, but only cid < 99 seems to work. Interestingly, when I change
policy pp1r to cid < 55, I see the following:
+EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------
+ Append
+ InitPlan 1 (returns $0)
+ -> Index Scan using uaccount_pkey on uaccount
+ Index Cond: (pguser = CURRENT_USER)
+ -> Seq Scan on part_document_fiction
+ Filter: ((cid < 55) AND (cid <> 55) AND (cid < 99) AND
(dlevel <= $0) AND f_leak(dtitle))
+(6 rows)
Is this a demonstration of a non-immutable function backing the
operator and thus not being able to filter it from the planner, or is
it a bug?
>
> +SELECT * FROM part_document;
> + did | cid | dlevel | dauthor | dtitle
> +-----+-----+--------+-------------------+-------------------------
> + 1 | 11 | 1 | regress_rls_bob | my first novel
> Adding an "ORDER BY did" as well here would make the test output more
> predictable.
Done.
Thanks,
--
Mike Palmiotto
Software Engineer
Crunchy Data Solutions
https://crunchydata.com
From 8c55045bd2d856fe4707582de0270c26d3a4c285 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/expected/rowsecurity.out | 425 ++++++++++++++++++++++++++++++
src/test/regress/sql/rowsecurity.sql | 148 +++++++++++
3 files changed, 575 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/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 7bf2936..792d24e 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -899,6 +899,431 @@ EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
Filter: f_leak(b)
(7 rows)
+--
+-- Partitioned Tables
+--
+SET SESSION AUTHORIZATION regress_rls_alice;
+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 PARTITION OF part_document FOR VALUES FROM ('11') to ('12');
+CREATE TABLE part_document_satire PARTITION OF part_document FOR VALUES FROM ('55') to ('56');
+CREATE TABLE part_document_nonfiction PARTITION OF part_document FOR VALUES FROM ('99') to ('100');
+GRANT ALL ON part_document_fiction TO public;
+GRANT ALL ON part_document_satire TO public;
+GRANT ALL ON part_document_nonfiction TO public;
+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;
+-- Create policy on parent
+-- 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));
+-- Dave is only allowed to see cid == 55 AND cid >= 99
+-- 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);
+-- Dave is not allowed to see satire.
+CREATE POLICY pp1r ON part_document AS RESTRICTIVE TO regress_rls_dave
+ USING (cid <> 55);
+-- 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));
+ERROR: unrecognized row security option "ugly"
+LINE 1: CREATE POLICY pp1 ON part_document AS UGLY
+ ^
+HINT: Only PERMISSIVE or RESTRICTIVE policies are supported currently.
+\dp
+ Access privileges
+ Schema | Name | Type | Access privileges | Column privileges | Policies
+--------------------+--------------------------+-------+---------------------------------------------+-------------------+--------------------------------------------------------------------------------------
+ regress_rls_schema | category | table | regress_rls_alice=arwdDxt/regress_rls_alice+| | p2: +
+ | | | =arwdDxt/regress_rls_alice | | (u): +
+ | | | | | CASE +
+ | | | | | WHEN (CURRENT_USER = 'regress_rls_bob'::name) THEN (cid = ANY (ARRAY[11, 33])) +
+ | | | | | WHEN (CURRENT_USER = 'regress_rls_carol'::name) THEN (cid = ANY (ARRAY[22, 44]))+
+ | | | | | ELSE false +
+ | | | | | END
+ regress_rls_schema | document | table | regress_rls_alice=arwdDxt/regress_rls_alice+| | p1: +
+ | | | =arwdDxt/regress_rls_alice | | (u): (dauthor = CURRENT_USER) +
+ | | | | | p2r (RESTRICTIVE): +
+ | | | | | (u): ((cid <> 44) AND (cid < 50)) +
+ | | | | | to: regress_rls_dave +
+ | | | | | p1r (RESTRICTIVE): +
+ | | | | | (u): (cid <> 44) +
+ | | | | | to: regress_rls_dave
+ regress_rls_schema | part_document | table | regress_rls_alice=arwdDxt/regress_rls_alice+| | pp1: +
+ | | | =arwdDxt/regress_rls_alice | | (u): (dlevel <= ( SELECT uaccount.seclv +
+ | | | | | FROM uaccount +
+ | | | | | WHERE (uaccount.pguser = CURRENT_USER))) +
+ | | | | | pp2r (RESTRICTIVE): +
+ | | | | | (u): ((cid <> 55) AND (cid < 99)) +
+ | | | | | to: regress_rls_dave +
+ | | | | | pp1r (RESTRICTIVE): +
+ | | | | | (u): (cid <> 55) +
+ | | | | | to: regress_rls_dave
+ regress_rls_schema | part_document_fiction | table | regress_rls_alice=arwdDxt/regress_rls_alice+| |
+ | | | =arwdDxt/regress_rls_alice | |
+ regress_rls_schema | part_document_nonfiction | table | regress_rls_alice=arwdDxt/regress_rls_alice+| |
+ | | | =arwdDxt/regress_rls_alice | |
+ regress_rls_schema | part_document_satire | table | regress_rls_alice=arwdDxt/regress_rls_alice+| |
+ | | | =arwdDxt/regress_rls_alice | |
+ regress_rls_schema | t1 | table | regress_rls_alice=arwdDxt/regress_rls_alice+| | p1: +
+ | | | =arwdDxt/regress_rls_alice | | (u): ((a % 2) = 0)
+ regress_rls_schema | t2 | table | regress_rls_alice=arwdDxt/regress_rls_alice+| | p2: +
+ | | | =arwdDxt/regress_rls_alice | | (u): ((a % 2) = 1)
+ regress_rls_schema | t3 | table | regress_rls_alice=arwdDxt/regress_rls_alice+| |
+ | | | =arwdDxt/regress_rls_alice | |
+ regress_rls_schema | uaccount | table | regress_rls_alice=arwdDxt/regress_rls_alice+| |
+ | | | =r/regress_rls_alice | |
+(10 rows)
+
+\d part_document*
+ Table "regress_rls_schema.part_document"
+ Column | Type | Collation | Nullable | Default
+---------+---------+-----------+----------+---------
+ did | integer | | |
+ cid | integer | | |
+ dlevel | integer | | not null |
+ dauthor | name | | |
+ dtitle | text | | |
+Partition key: RANGE (cid)
+Policies:
+ POLICY "pp1"
+ USING ((dlevel <= ( SELECT uaccount.seclv
+ FROM uaccount
+ WHERE (uaccount.pguser = CURRENT_USER))))
+ POLICY "pp1r" AS RESTRICTIVE
+ TO regress_rls_dave
+ USING ((cid <> 55))
+ POLICY "pp2r" AS RESTRICTIVE
+ TO regress_rls_dave
+ USING (((cid <> 55) AND (cid < 99)))
+Number of partitions: 3 (Use \d+ to list them.)
+
+ Table "regress_rls_schema.part_document_fiction"
+ Column | Type | Collation | Nullable | Default
+---------+---------+-----------+----------+---------
+ did | integer | | |
+ cid | integer | | |
+ dlevel | integer | | not null |
+ dauthor | name | | |
+ dtitle | text | | |
+Partition of: part_document FOR VALUES FROM (11) TO (12)
+
+Table "regress_rls_schema.part_document_nonfiction"
+ Column | Type | Collation | Nullable | Default
+---------+---------+-----------+----------+---------
+ did | integer | | |
+ cid | integer | | |
+ dlevel | integer | | not null |
+ dauthor | name | | |
+ dtitle | text | | |
+Partition of: part_document FOR VALUES FROM (99) TO (100)
+
+ Table "regress_rls_schema.part_document_satire"
+ Column | Type | Collation | Nullable | Default
+---------+---------+-----------+----------+---------
+ did | integer | | |
+ cid | integer | | |
+ dlevel | integer | | not null |
+ dauthor | name | | |
+ dtitle | text | | |
+Partition of: part_document FOR VALUES FROM (55) TO (56)
+
+SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename like '%part_document%' ORDER BY policyname;
+ schemaname | tablename | policyname | permissive | roles | cmd | qual | with_check
+--------------------+---------------+------------+-------------+--------------------+-----+--------------------------------------------+------------
+ regress_rls_schema | part_document | pp1 | PERMISSIVE | {public} | ALL | (dlevel <= ( SELECT uaccount.seclv +|
+ | | | | | | FROM uaccount +|
+ | | | | | | WHERE (uaccount.pguser = CURRENT_USER))) |
+ regress_rls_schema | part_document | pp1r | RESTRICTIVE | {regress_rls_dave} | ALL | (cid <> 55) |
+ regress_rls_schema | part_document | pp2r | RESTRICTIVE | {regress_rls_dave} | ALL | ((cid <> 55) AND (cid < 99)) |
+(3 rows)
+
+-- 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;
+NOTICE: f_leak => my first novel
+NOTICE: f_leak => great science fiction
+NOTICE: f_leak => awesome science fiction
+NOTICE: f_leak => my first satire
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-------------------------
+ 1 | 11 | 1 | regress_rls_bob | my first novel
+ 4 | 55 | 1 | regress_rls_bob | my first satire
+ 6 | 11 | 1 | regress_rls_carol | great science fiction
+ 9 | 11 | 1 | regress_rls_dave | awesome science fiction
+(4 rows)
+
+-- try a sampled version
+SELECT * FROM part_document TABLESAMPLE BERNOULLI(50) REPEATABLE(0)
+ WHERE f_leak(dtitle) ORDER BY did;
+NOTICE: f_leak => awesome science fiction
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+------------------+-------------------------
+ 9 | 11 | 1 | regress_rls_dave | awesome science fiction
+(1 row)
+
+-- viewpoint from regress_rls_carol
+SET SESSION AUTHORIZATION regress_rls_carol;
+SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
+NOTICE: f_leak => my first novel
+NOTICE: f_leak => my second novel
+NOTICE: f_leak => great science fiction
+NOTICE: f_leak => awesome science fiction
+NOTICE: f_leak => my first satire
+NOTICE: f_leak => great satire
+NOTICE: f_leak => my science textbook
+NOTICE: f_leak => my history book
+NOTICE: f_leak => great technology book
+NOTICE: f_leak => awesome technology book
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-------------------------
+ 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
+(10 rows)
+
+-- try a sampled version
+SELECT * FROM part_document TABLESAMPLE BERNOULLI(50) REPEATABLE(0)
+ WHERE f_leak(dtitle) ORDER BY did;
+NOTICE: f_leak => awesome science fiction
+NOTICE: f_leak => awesome technology book
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+------------------+-------------------------
+ 9 | 11 | 1 | regress_rls_dave | awesome science fiction
+ 10 | 99 | 2 | regress_rls_dave | awesome technology book
+(2 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
+ QUERY PLAN
+-----------------------------------------------------
+ Append
+ InitPlan 1 (returns $0)
+ -> Index Scan using uaccount_pkey on uaccount
+ Index Cond: (pguser = CURRENT_USER)
+ -> Seq Scan on part_document_fiction
+ Filter: ((dlevel <= $0) AND f_leak(dtitle))
+ -> Seq Scan on part_document_satire
+ Filter: ((dlevel <= $0) AND f_leak(dtitle))
+ -> Seq Scan on part_document_nonfiction
+ Filter: ((dlevel <= $0) AND f_leak(dtitle))
+(10 rows)
+
+-- viewpoint from regress_rls_dave
+SET SESSION AUTHORIZATION regress_rls_dave;
+SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
+NOTICE: f_leak => my first novel
+NOTICE: f_leak => my second novel
+NOTICE: f_leak => great science fiction
+NOTICE: f_leak => awesome science fiction
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-------------------------
+ 1 | 11 | 1 | regress_rls_bob | my first novel
+ 2 | 11 | 2 | regress_rls_bob | my second novel
+ 6 | 11 | 1 | regress_rls_carol | great science fiction
+ 9 | 11 | 1 | regress_rls_dave | awesome science fiction
+(4 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Append
+ InitPlan 1 (returns $0)
+ -> Index Scan using uaccount_pkey on uaccount
+ Index Cond: (pguser = CURRENT_USER)
+ -> Seq Scan on part_document_fiction
+ Filter: ((cid <> 55) AND (cid <> 55) AND (cid < 99) AND (dlevel <= $0) AND f_leak(dtitle))
+ -> Seq Scan on part_document_satire
+ Filter: ((cid <> 55) AND (cid <> 55) AND (cid < 99) AND (dlevel <= $0) AND f_leak(dtitle))
+(8 rows)
+
+-- cid = 55 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
+ERROR: new row violates row-level security policy "pp1r" for table "part_document"
+-- Just to see a pp2r error
+INSERT INTO part_document VALUES (100, 99, 1, 'regress_rls_dave', 'testing sorting of policies'); -- fail
+ERROR: new row violates row-level security policy "pp2r" for table "part_document"
+-- Show that RLS policy does not apply for direct inserts to children
+-- This should fail with constraint violation.
+INSERT INTO part_document_fiction VALUES (100, 55, 1, 'regress_rls_dave', 'testing RLS inheritance'); --
+ERROR: new row for relation "part_document_fiction" violates partition constraint
+DETAIL: Failing row contains (100, 55, 1, regress_rls_dave, testing RLS inheritance).
+-- Turn on RLS and create policy on child to show RLS is checked before constraints
+SET SESSION AUTHORIZATION regress_rls_alice;
+ALTER TABLE part_document_fiction ENABLE ROW LEVEL SECURITY;
+CREATE POLICY pp3 ON part_document_fiction AS RESTRICTIVE TO regress_rls_dave
+ USING (cid <> 55 AND cid < 99);
+-- This should fail with RLS violation now.
+SET SESSION AUTHORIZATION regress_rls_dave;
+INSERT INTO part_document_fiction VALUES (100, 55, 1, 'regress_rls_dave', 'testing RLS inheritance'); --
+ERROR: new row violates row-level security policy for table "part_document_fiction"
+-- only owner can change policies
+ALTER POLICY pp1 ON part_document USING (true); --fail
+ERROR: must be owner of relation part_document
+DROP POLICY pp1 ON part_document; --fail
+ERROR: must be owner of relation part_document
+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;
+NOTICE: f_leak => my first novel
+NOTICE: f_leak => my second novel
+NOTICE: f_leak => my first satire
+NOTICE: f_leak => my science textbook
+NOTICE: f_leak => my history book
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-----------------+---------------------
+ 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
+(5 rows)
+
+-- viewpoint from rls_regres_carol again
+SET SESSION AUTHORIZATION regress_rls_carol;
+SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
+NOTICE: f_leak => great science fiction
+NOTICE: f_leak => great satire
+NOTICE: f_leak => great technology book
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-----------------------
+ 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
+(3 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
+ QUERY PLAN
+---------------------------------------------------------------
+ Append
+ -> Seq Scan on part_document_fiction
+ Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
+ -> Seq Scan on part_document_satire
+ Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
+ -> Seq Scan on part_document_nonfiction
+ Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
+(7 rows)
+
+-- database superuser does bypass RLS policy when enabled
+RESET SESSION AUTHORIZATION;
+SET row_security TO ON;
+SELECT * FROM part_document ORDER BY did;
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-------------------------
+ 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
+(10 rows)
+
+-- database superuser does bypass RLS policy when disabled
+RESET SESSION AUTHORIZATION;
+SET row_security TO OFF;
+SELECT * FROM part_document ORDER BY did;
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-------------------------
+ 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
+(10 rows)
+
+-- 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 ORDER BY did;
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-------------------------
+ 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
+(10 rows)
+
+-- 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 ORDER by did;
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-------------------------
+ 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
+(10 rows)
+
+-- 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 ORDER BY did;
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-------------------------
+ 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
+(10 rows)
+
----- Dependencies -----
SET SESSION AUTHORIZATION regress_rls_alice;
SET row_security TO ON;
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 1b6896e..697e488 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -308,6 +308,154 @@ 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_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 PARTITION OF part_document FOR VALUES FROM ('11') to ('12');
+CREATE TABLE part_document_satire PARTITION OF part_document FOR VALUES FROM ('55') to ('56');
+CREATE TABLE part_document_nonfiction PARTITION OF part_document FOR VALUES FROM ('99') to ('100');
+
+GRANT ALL ON part_document_fiction TO public;
+GRANT ALL ON part_document_satire TO public;
+GRANT ALL ON part_document_nonfiction TO public;
+
+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;
+
+-- Create policy on parent
+-- 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));
+
+-- Dave is only allowed to see cid == 55 AND cid >= 99
+-- 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);
+
+-- Dave is not allowed to see satire.
+CREATE POLICY pp1r ON part_document AS RESTRICTIVE TO regress_rls_dave
+ USING (cid <> 55);
+
+-- 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));
+
+\dp
+\d part_document*
+SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename like '%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;
+
+-- 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;
+
+-- 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);
+
+-- viewpoint from regress_rls_dave
+SET SESSION AUTHORIZATION regress_rls_dave;
+SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
+
+EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
+
+-- cid = 55 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
+
+-- Show that RLS policy does not apply for direct inserts to children
+-- This should fail with constraint violation.
+INSERT INTO part_document_fiction VALUES (100, 55, 1, 'regress_rls_dave', 'testing RLS inheritance'); --
+
+-- Turn on RLS and create policy on child to show RLS is checked before constraints
+SET SESSION AUTHORIZATION regress_rls_alice;
+ALTER TABLE part_document_fiction ENABLE ROW LEVEL SECURITY;
+CREATE POLICY pp3 ON part_document_fiction AS RESTRICTIVE TO regress_rls_dave
+ USING (cid <> 55 AND cid < 99);
+-- This should fail with RLS violation now.
+SET SESSION AUTHORIZATION regress_rls_dave;
+INSERT INTO part_document_fiction VALUES (100, 55, 1, 'regress_rls_dave', 'testing RLS inheritance'); --
+
+-- 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;
+
+-- viewpoint from rls_regres_carol again
+SET SESSION AUTHORIZATION regress_rls_carol;
+SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
+
+EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
+
+-- database superuser does bypass RLS policy when enabled
+RESET SESSION AUTHORIZATION;
+SET row_security TO ON;
+SELECT * FROM part_document ORDER BY did;
+
+-- database superuser does bypass RLS policy when disabled
+RESET SESSION AUTHORIZATION;
+SET row_security TO OFF;
+SELECT * FROM part_document ORDER BY did;
+
+-- 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 ORDER BY did;
+
+-- 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 ORDER by did;
+
+-- 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 ORDER BY did;
+
----- 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
