On Tue, Jun 6, 2017 at 4:07 PM, Joe Conway <m...@joeconway.com> wrote:
> On 06/06/2017 11:57 AM, Mike Palmiotto wrote:
>> 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.
>
> The constraints test passes here, so presumably something you borked
> locally. I only see a rowsecurity failure, which is not surprising since
> your patch does not include the changes to expected output ;-)
> Please resend with src/test/regress/expected/rowsecurity.out included.
It was indeed an issue on my end. Attached are the rowsecurity
regression tests and the expected out. Unsurprisingly, all tests pass,
because I said so. :)
Let me know if you want me to make any revisions.
Thanks,
--
Mike Palmiotto
Software Engineer
Crunchy Data Solutions
https://crunchydata.com
From 08432d93ed753a1e5cd4585ccf00e900abbd685f 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 | 812 ++++++++++++++++++++++++++++++
src/test/regress/sql/rowsecurity.sql | 222 ++++++++
3 files changed, 1036 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..1e35498 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -899,6 +899,818 @@ 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_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_fiction AS PERMISSIVE
+ USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user));
+CREATE POLICY pp1_satire ON part_document_satire AS PERMISSIVE
+ USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user));
+CREATE POLICY pp1_nonfiction ON part_document_nonfiction 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));
+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.
+CREATE POLICY pp1_fiction ON part_document_fiction AS UGLY
+ USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user));
+ERROR: unrecognized row security option "ugly"
+LINE 1: CREATE POLICY pp1_fiction ON part_document_fiction AS UGLY
+ ^
+HINT: Only PERMISSIVE or RESTRICTIVE policies are supported currently.
+CREATE POLICY pp1_satire ON part_document_satire AS UGLY
+ USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user));
+ERROR: unrecognized row security option "ugly"
+LINE 1: CREATE POLICY pp1_satire ON part_document_satire AS UGLY
+ ^
+HINT: Only PERMISSIVE or RESTRICTIVE policies are supported currently.
+CREATE POLICY pp1_nonfiction ON part_document_nonfiction AS UGLY
+ USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user));
+ERROR: unrecognized row security option "ugly"
+LINE 1: ...TE POLICY pp1_nonfiction ON part_document_nonfiction AS UGLY
+ ^
+HINT: Only PERMISSIVE or RESTRICTIVE policies are supported currently.
+-- 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
+ 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_category | table | regress_rls_alice=arwdDxt/regress_rls_alice+| |
+ | | | =arwdDxt/regress_rls_alice | |
+ 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 | | | pp1_fiction: +
+ | | | | | (u): (dlevel <= ( SELECT uaccount.seclv +
+ | | | | | FROM uaccount +
+ | | | | | WHERE (uaccount.pguser = CURRENT_USER))) +
+ | | | | | pp2r_fiction (RESTRICTIVE): +
+ | | | | | (u): ((cid <> 55) AND (cid < 99)) +
+ | | | | | to: regress_rls_dave +
+ | | | | | pp1r_fiction (RESTRICTIVE): +
+ | | | | | (u): (cid <> 55) +
+ | | | | | to: regress_rls_dave
+ regress_rls_schema | part_document_fiction_1 | table | regress_rls_alice=arwdDxt/regress_rls_alice+| |
+ | | | =arwdDxt/regress_rls_alice | |
+ regress_rls_schema | part_document_fiction_2 | table | regress_rls_alice=arwdDxt/regress_rls_alice+| |
+ | | | =arwdDxt/regress_rls_alice | |
+ regress_rls_schema | part_document_nonfiction | table | | | pp1_nonfiction: +
+ | | | | | (u): (dlevel <= ( SELECT uaccount.seclv +
+ | | | | | FROM uaccount +
+ | | | | | WHERE (uaccount.pguser = CURRENT_USER))) +
+ | | | | | pp2r_nonfiction (RESTRICTIVE): +
+ | | | | | (u): ((cid <> 55) AND (cid < 99)) +
+ | | | | | to: regress_rls_dave +
+ | | | | | pp1r_nonfiction (RESTRICTIVE): +
+ | | | | | (u): (cid <> 55) +
+ | | | | | to: regress_rls_dave
+ regress_rls_schema | part_document_nonfiction_1 | table | regress_rls_alice=arwdDxt/regress_rls_alice+| |
+ | | | =arwdDxt/regress_rls_alice | |
+ regress_rls_schema | part_document_nonfiction_2 | table | regress_rls_alice=arwdDxt/regress_rls_alice+| |
+ | | | =arwdDxt/regress_rls_alice | |
+ regress_rls_schema | part_document_satire | table | | | pp1_satire: +
+ | | | | | (u): (dlevel <= ( SELECT uaccount.seclv +
+ | | | | | FROM uaccount +
+ | | | | | WHERE (uaccount.pguser = CURRENT_USER))) +
+ | | | | | pp2r_satire (RESTRICTIVE): +
+ | | | | | (u): ((cid <> 55) AND (cid < 99)) +
+ | | | | | to: regress_rls_dave +
+ | | | | | pp1r_satire (RESTRICTIVE): +
+ | | | | | (u): (cid <> 55) +
+ | | | | | to: regress_rls_dave
+ regress_rls_schema | part_document_satire_1 | table | regress_rls_alice=arwdDxt/regress_rls_alice+| |
+ | | | =arwdDxt/regress_rls_alice | |
+ regress_rls_schema | part_document_satire_2 | 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 | |
+(17 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)
+Partition key: RANGE (dlevel)
+Policies:
+ POLICY "pp1_fiction"
+ USING ((dlevel <= ( SELECT uaccount.seclv
+ FROM uaccount
+ WHERE (uaccount.pguser = CURRENT_USER))))
+ POLICY "pp1r_fiction" AS RESTRICTIVE
+ TO regress_rls_dave
+ USING ((cid <> 55))
+ POLICY "pp2r_fiction" AS RESTRICTIVE
+ TO regress_rls_dave
+ USING (((cid <> 55) AND (cid < 99)))
+Number of partitions: 2 (Use \d+ to list them.)
+
+ Table "regress_rls_schema.part_document_fiction_1"
+ Column | Type | Collation | Nullable | Default
+---------+---------+-----------+----------+---------
+ did | integer | | |
+ cid | integer | | |
+ dlevel | integer | | not null |
+ dauthor | name | | |
+ dtitle | text | | |
+Partition of: part_document_fiction FOR VALUES FROM (1) TO (2)
+Policies (row security enabled): (none)
+
+ Table "regress_rls_schema.part_document_fiction_2"
+ Column | Type | Collation | Nullable | Default
+---------+---------+-----------+----------+---------
+ did | integer | | |
+ cid | integer | | |
+ dlevel | integer | | not null |
+ dauthor | name | | |
+ dtitle | text | | |
+Partition of: part_document_fiction FOR VALUES FROM (2) TO (3)
+Policies (row security enabled): (none)
+
+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)
+Partition key: RANGE (dlevel)
+Policies:
+ POLICY "pp1_nonfiction"
+ USING ((dlevel <= ( SELECT uaccount.seclv
+ FROM uaccount
+ WHERE (uaccount.pguser = CURRENT_USER))))
+ POLICY "pp1r_nonfiction" AS RESTRICTIVE
+ TO regress_rls_dave
+ USING ((cid <> 55))
+ POLICY "pp2r_nonfiction" AS RESTRICTIVE
+ TO regress_rls_dave
+ USING (((cid <> 55) AND (cid < 99)))
+Number of partitions: 2 (Use \d+ to list them.)
+
+Table "regress_rls_schema.part_document_nonfiction_1"
+ Column | Type | Collation | Nullable | Default
+---------+---------+-----------+----------+---------
+ did | integer | | |
+ cid | integer | | |
+ dlevel | integer | | not null |
+ dauthor | name | | |
+ dtitle | text | | |
+Partition of: part_document_nonfiction FOR VALUES FROM (1) TO (2)
+Policies (row security enabled): (none)
+
+Table "regress_rls_schema.part_document_nonfiction_2"
+ Column | Type | Collation | Nullable | Default
+---------+---------+-----------+----------+---------
+ did | integer | | |
+ cid | integer | | |
+ dlevel | integer | | not null |
+ dauthor | name | | |
+ dtitle | text | | |
+Partition of: part_document_nonfiction FOR VALUES FROM (2) TO (3)
+Policies (row security enabled): (none)
+
+ 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)
+Partition key: RANGE (dlevel)
+Policies:
+ POLICY "pp1_satire"
+ USING ((dlevel <= ( SELECT uaccount.seclv
+ FROM uaccount
+ WHERE (uaccount.pguser = CURRENT_USER))))
+ POLICY "pp1r_satire" AS RESTRICTIVE
+ TO regress_rls_dave
+ USING ((cid <> 55))
+ POLICY "pp2r_satire" AS RESTRICTIVE
+ TO regress_rls_dave
+ USING (((cid <> 55) AND (cid < 99)))
+Number of partitions: 2 (Use \d+ to list them.)
+
+ Table "regress_rls_schema.part_document_satire_1"
+ Column | Type | Collation | Nullable | Default
+---------+---------+-----------+----------+---------
+ did | integer | | |
+ cid | integer | | |
+ dlevel | integer | | not null |
+ dauthor | name | | |
+ dtitle | text | | |
+Partition of: part_document_satire FOR VALUES FROM (1) TO (2)
+Policies (row security enabled): (none)
+
+ Table "regress_rls_schema.part_document_satire_2"
+ Column | Type | Collation | Nullable | Default
+---------+---------+-----------+----------+---------
+ did | integer | | |
+ cid | integer | | |
+ dlevel | integer | | not null |
+ dauthor | name | | |
+ dtitle | text | | |
+Partition of: part_document_satire FOR VALUES FROM (2) TO (3)
+Policies (row security enabled): (none)
+
+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_fiction | pp1_fiction | PERMISSIVE | {public} | ALL | (dlevel <= ( SELECT uaccount.seclv +|
+ | | | | | | FROM uaccount +|
+ | | | | | | WHERE (uaccount.pguser = CURRENT_USER))) |
+ regress_rls_schema | part_document_nonfiction | pp1_nonfiction | PERMISSIVE | {public} | ALL | (dlevel <= ( SELECT uaccount.seclv +|
+ | | | | | | FROM uaccount +|
+ | | | | | | WHERE (uaccount.pguser = CURRENT_USER))) |
+ regress_rls_schema | part_document_satire | pp1_satire | 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_fiction | pp1r_fiction | RESTRICTIVE | {regress_rls_dave} | ALL | (cid <> 55) |
+ regress_rls_schema | part_document_nonfiction | pp1r_nonfiction | RESTRICTIVE | {regress_rls_dave} | ALL | (cid <> 55) |
+ regress_rls_schema | part_document_satire | pp1r_satire | RESTRICTIVE | {regress_rls_dave} | ALL | (cid <> 55) |
+ regress_rls_schema | part_document | pp2r | RESTRICTIVE | {regress_rls_dave} | ALL | ((cid <> 55) AND (cid < 99)) |
+ regress_rls_schema | part_document_fiction | pp2r_fiction | RESTRICTIVE | {regress_rls_dave} | ALL | ((cid <> 55) AND (cid < 99)) |
+ regress_rls_schema | part_document_nonfiction | pp2r_nonfiction | RESTRICTIVE | {regress_rls_dave} | ALL | ((cid <> 55) AND (cid < 99)) |
+ regress_rls_schema | part_document_satire | pp2r_satire | RESTRICTIVE | {regress_rls_dave} | ALL | ((cid <> 55) AND (cid < 99)) |
+(12 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)
+
+SELECT * FROM part_document NATURAL JOIN part_category 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
+ cid | did | dlevel | dauthor | dtitle | cname
+-----+-----+--------+-------------------+-------------------------+---------
+ 11 | 1 | 1 | regress_rls_bob | my first novel | fiction
+ 55 | 4 | 1 | regress_rls_bob | my first satire | satire
+ 11 | 6 | 1 | regress_rls_carol | great science fiction | fiction
+ 11 | 9 | 1 | regress_rls_dave | awesome science fiction | fiction
+(4 rows)
+
+-- try a sampled version
+SELECT * FROM part_document TABLESAMPLE BERNOULLI(50) REPEATABLE(0)
+ WHERE f_leak(dtitle) ORDER BY did;
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+---------+--------
+(0 rows)
+
+-- 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 => great science fiction
+NOTICE: f_leak => awesome science fiction
+NOTICE: f_leak => my second novel
+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)
+
+SELECT * FROM part_document NATURAL JOIN part_category 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 second novel
+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
+ cid | did | dlevel | dauthor | dtitle | cname
+-----+-----+--------+-------------------+-------------------------+------------
+ 11 | 1 | 1 | regress_rls_bob | my first novel | fiction
+ 11 | 2 | 2 | regress_rls_bob | my second novel | fiction
+ 99 | 3 | 2 | regress_rls_bob | my science textbook | nonfiction
+ 55 | 4 | 1 | regress_rls_bob | my first satire | satire
+ 99 | 5 | 2 | regress_rls_bob | my history book | nonfiction
+ 11 | 6 | 1 | regress_rls_carol | great science fiction | fiction
+ 99 | 7 | 2 | regress_rls_carol | great technology book | nonfiction
+ 55 | 8 | 2 | regress_rls_carol | great satire | satire
+ 11 | 9 | 1 | regress_rls_dave | awesome science fiction | fiction
+ 99 | 10 | 2 | regress_rls_dave | awesome technology book | nonfiction
+(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 technology book
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+------------------+-------------------------
+ 10 | 99 | 2 | regress_rls_dave | awesome technology book
+(1 row)
+
+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_1
+ Filter: ((dlevel <= $0) AND f_leak(dtitle))
+ -> Seq Scan on part_document_fiction_2
+ Filter: ((dlevel <= $0) AND f_leak(dtitle))
+ -> Seq Scan on part_document_satire_1
+ Filter: ((dlevel <= $0) AND f_leak(dtitle))
+ -> Seq Scan on part_document_satire_2
+ Filter: ((dlevel <= $0) AND f_leak(dtitle))
+ -> Seq Scan on part_document_nonfiction_1
+ Filter: ((dlevel <= $0) AND f_leak(dtitle))
+ -> Seq Scan on part_document_nonfiction_2
+ Filter: ((dlevel <= $0) AND f_leak(dtitle))
+(16 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM part_document NATURAL JOIN part_category WHERE f_leak(dtitle);
+ QUERY PLAN
+----------------------------------------------------------------
+ Hash Join
+ Hash Cond: (part_document_fiction_1.cid = part_category.cid)
+ InitPlan 1 (returns $0)
+ -> Index Scan using uaccount_pkey on uaccount
+ Index Cond: (pguser = CURRENT_USER)
+ -> Append
+ -> Seq Scan on part_document_fiction_1
+ Filter: ((dlevel <= $0) AND f_leak(dtitle))
+ -> Seq Scan on part_document_fiction_2
+ Filter: ((dlevel <= $0) AND f_leak(dtitle))
+ -> Seq Scan on part_document_satire_1
+ Filter: ((dlevel <= $0) AND f_leak(dtitle))
+ -> Seq Scan on part_document_satire_2
+ Filter: ((dlevel <= $0) AND f_leak(dtitle))
+ -> Seq Scan on part_document_nonfiction_1
+ Filter: ((dlevel <= $0) AND f_leak(dtitle))
+ -> Seq Scan on part_document_nonfiction_2
+ Filter: ((dlevel <= $0) AND f_leak(dtitle))
+ -> Hash
+ -> Seq Scan on part_category
+(20 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 => great science fiction
+NOTICE: f_leak => awesome science fiction
+NOTICE: f_leak => my second novel
+ 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)
+
+SELECT * FROM part_document NATURAL JOIN part_category 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 second novel
+ cid | did | dlevel | dauthor | dtitle | cname
+-----+-----+--------+-------------------+-------------------------+---------
+ 11 | 1 | 1 | regress_rls_bob | my first novel | fiction
+ 11 | 2 | 2 | regress_rls_bob | my second novel | fiction
+ 11 | 6 | 1 | regress_rls_carol | great science fiction | fiction
+ 11 | 9 | 1 | regress_rls_dave | awesome science fiction | 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_1
+ Filter: ((cid <> 55) AND (cid <> 55) AND (cid < 99) AND (dlevel <= $0) AND f_leak(dtitle))
+ -> Seq Scan on part_document_fiction_2
+ Filter: ((cid <> 55) AND (cid <> 55) AND (cid < 99) AND (dlevel <= $0) AND f_leak(dtitle))
+ -> Seq Scan on part_document_satire_1
+ Filter: ((cid <> 55) AND (cid <> 55) AND (cid < 99) AND (dlevel <= $0) AND f_leak(dtitle))
+ -> Seq Scan on part_document_satire_2
+ Filter: ((cid <> 55) AND (cid <> 55) AND (cid < 99) AND (dlevel <= $0) AND f_leak(dtitle))
+(12 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM part_document NATURAL JOIN part_category WHERE f_leak(dtitle);
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------
+ Hash Join
+ Hash Cond: (part_category.cid = part_document_fiction_1.cid)
+ InitPlan 1 (returns $0)
+ -> Index Scan using uaccount_pkey on uaccount
+ Index Cond: (pguser = CURRENT_USER)
+ -> Seq Scan on part_category
+ -> Hash
+ -> Append
+ -> Seq Scan on part_document_fiction_1
+ Filter: ((cid <> 55) AND (cid <> 55) AND (cid < 99) AND (dlevel <= $0) AND f_leak(dtitle))
+ -> Seq Scan on part_document_fiction_2
+ Filter: ((cid <> 55) AND (cid <> 55) AND (cid < 99) AND (dlevel <= $0) AND f_leak(dtitle))
+ -> Seq Scan on part_document_satire_1
+ Filter: ((cid <> 55) AND (cid <> 55) AND (cid < 99) AND (dlevel <= $0) AND f_leak(dtitle))
+ -> Seq Scan on part_document_satire_2
+ Filter: ((cid <> 55) AND (cid <> 55) AND (cid < 99) AND (dlevel <= $0) AND f_leak(dtitle))
+(16 rows)
+
+-- 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
+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"
+-- 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
+ERROR: permission denied for relation part_document_fiction
+INSERT INTO part_document_nonfiction VALUES (100, 55, 1, 'regress_rls_dave', 'testing sorting of policies'); -- fail
+ERROR: permission denied for relation part_document_nonfiction
+-- 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)
+
+SELECT * FROM part_document NATURAL JOIN part_category 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
+ cid | did | dlevel | dauthor | dtitle | cname
+-----+-----+--------+-----------------+---------------------+------------
+ 11 | 1 | 1 | regress_rls_bob | my first novel | fiction
+ 11 | 2 | 2 | regress_rls_bob | my second novel | fiction
+ 99 | 3 | 2 | regress_rls_bob | my science textbook | nonfiction
+ 55 | 4 | 1 | regress_rls_bob | my first satire | satire
+ 99 | 5 | 2 | regress_rls_bob | my history book | nonfiction
+(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)
+
+SELECT * FROM part_document NATURAL JOIN part_category WHERE f_leak(dtitle) ORDER by did;
+NOTICE: f_leak => great science fiction
+NOTICE: f_leak => great satire
+NOTICE: f_leak => great technology book
+ cid | did | dlevel | dauthor | dtitle | cname
+-----+-----+--------+-------------------+-----------------------+------------
+ 11 | 6 | 1 | regress_rls_carol | great science fiction | fiction
+ 99 | 7 | 2 | regress_rls_carol | great technology book | nonfiction
+ 55 | 8 | 2 | regress_rls_carol | great satire | satire
+(3 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
+ QUERY PLAN
+---------------------------------------------------------------
+ Append
+ -> Seq Scan on part_document_fiction_1
+ Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
+ -> Seq Scan on part_document_fiction_2
+ Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
+ -> Seq Scan on part_document_satire_1
+ Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
+ -> Seq Scan on part_document_satire_2
+ Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
+ -> Seq Scan on part_document_nonfiction_1
+ Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
+ -> Seq Scan on part_document_nonfiction_2
+ Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
+(13 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM part_document NATURAL JOIN part_category WHERE f_leak(dtitle);
+ QUERY PLAN
+---------------------------------------------------------------------------
+ Hash Join
+ Hash Cond: (part_category.cid = part_document_fiction_1.cid)
+ -> Seq Scan on part_category
+ -> Hash
+ -> Append
+ -> Seq Scan on part_document_fiction_1
+ Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
+ -> Seq Scan on part_document_fiction_2
+ Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
+ -> Seq Scan on part_document_satire_1
+ Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
+ -> Seq Scan on part_document_satire_2
+ Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
+ -> Seq Scan on part_document_nonfiction_1
+ Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
+ -> Seq Scan on part_document_nonfiction_2
+ Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
+(17 rows)
+
+-- database superuser does bypass RLS policy when enabled
+RESET SESSION AUTHORIZATION;
+SET row_security TO ON;
+SELECT * FROM part_document;
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-------------------------
+ 1 | 11 | 1 | regress_rls_bob | my first novel
+ 6 | 11 | 1 | regress_rls_carol | great science fiction
+ 9 | 11 | 1 | regress_rls_dave | awesome science fiction
+ 2 | 11 | 2 | regress_rls_bob | my second novel
+ 4 | 55 | 1 | regress_rls_bob | my first satire
+ 8 | 55 | 2 | regress_rls_carol | great satire
+ 3 | 99 | 2 | regress_rls_bob | my science textbook
+ 5 | 99 | 2 | regress_rls_bob | my history book
+ 7 | 99 | 2 | regress_rls_carol | great technology book
+ 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;
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-------------------------
+ 1 | 11 | 1 | regress_rls_bob | my first novel
+ 6 | 11 | 1 | regress_rls_carol | great science fiction
+ 9 | 11 | 1 | regress_rls_dave | awesome science fiction
+ 2 | 11 | 2 | regress_rls_bob | my second novel
+ 4 | 55 | 1 | regress_rls_bob | my first satire
+ 8 | 55 | 2 | regress_rls_carol | great satire
+ 3 | 99 | 2 | regress_rls_bob | my science textbook
+ 5 | 99 | 2 | regress_rls_bob | my history book
+ 7 | 99 | 2 | regress_rls_carol | great technology book
+ 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;
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-------------------------
+ 1 | 11 | 1 | regress_rls_bob | my first novel
+ 6 | 11 | 1 | regress_rls_carol | great science fiction
+ 9 | 11 | 1 | regress_rls_dave | awesome science fiction
+ 2 | 11 | 2 | regress_rls_bob | my second novel
+ 4 | 55 | 1 | regress_rls_bob | my first satire
+ 8 | 55 | 2 | regress_rls_carol | great satire
+ 3 | 99 | 2 | regress_rls_bob | my science textbook
+ 5 | 99 | 2 | regress_rls_bob | my history book
+ 7 | 99 | 2 | regress_rls_carol | great technology book
+ 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;
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-------------------------
+ 1 | 11 | 1 | regress_rls_bob | my first novel
+ 6 | 11 | 1 | regress_rls_carol | great science fiction
+ 9 | 11 | 1 | regress_rls_dave | awesome science fiction
+ 2 | 11 | 2 | regress_rls_bob | my second novel
+ 4 | 55 | 1 | regress_rls_bob | my first satire
+ 8 | 55 | 2 | regress_rls_carol | great satire
+ 3 | 99 | 2 | regress_rls_bob | my science textbook
+ 5 | 99 | 2 | regress_rls_bob | my history book
+ 7 | 99 | 2 | regress_rls_carol | great technology book
+ 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;
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-------------------------
+ 1 | 11 | 1 | regress_rls_bob | my first novel
+ 6 | 11 | 1 | regress_rls_carol | great science fiction
+ 9 | 11 | 1 | regress_rls_dave | awesome science fiction
+ 2 | 11 | 2 | regress_rls_bob | my second novel
+ 4 | 55 | 1 | regress_rls_bob | my first satire
+ 8 | 55 | 2 | regress_rls_carol | great satire
+ 3 | 99 | 2 | regress_rls_bob | my science textbook
+ 5 | 99 | 2 | regress_rls_bob | my history book
+ 7 | 99 | 2 | regress_rls_carol | great technology book
+ 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..372db25 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -308,6 +308,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_fiction AS PERMISSIVE
+ USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user));
+CREATE POLICY pp1_satire ON part_document_satire AS PERMISSIVE
+ USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user));
+CREATE POLICY pp1_nonfiction ON part_document_nonfiction 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 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;
+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
