BUG #14682: row level security not work with partitioned table
Started by Nonameover 8 years ago20 messages
The following bug has been logged on the website:
Bug reference: 14682
Logged by: Fakhroutdinov Evgenievich
Email address: fte@nct.ru
PostgreSQL version: 10beta1
Operating system: macOS Sierra 10.12.5
Description:
create table test (
id bigserial not null,
tm timestamp not null,
user_name text not null,
rem text
) partition by range (tm);
create table test_1q (like test including all);
create table test_2q (like test including all);
alter table test attach partition test_1q for values from ('2017-01-01') to
('2017-04-01');
alter table test attach partition test_2q for values from ('2017-04-01') to
('2017-07-01');
CREATE ROLE bob; -- Normal user
CREATE ROLE alice; -- Normal user
insert into test(tm,user_name,rem)
values
('2017-01-09 22:15:15','bob','bla-bla'),
('2017-02-09 22:15:15','alice','bla-bla'),
('2017-03-09 22:15:15','bob','bla-bla'),
('2017-04-09 22:15:15','alice','bla-bla'),
('2017-05-09 22:15:15','bob','bla-bla'),
('2017-06-09 22:15:15','alice','bla-bla');
ALTER TABLE test ENABLE ROW LEVEL SECURITY;
ALTER TABLE test_1q ENABLE ROW LEVEL SECURITY;
ALTER TABLE test_2q ENABLE ROW LEVEL SECURITY;
CREATE POLICY view_test ON test FOR SELECT USING (current_user =
user_name);
CREATE POLICY view_test_1q ON test_1q FOR SELECT USING (current_user =
user_name);
CREATE POLICY view_test_2q ON test_2q FOR SELECT USING (current_user =
user_name);
GRANT SELECT ON test TO public;
GRANT SELECT ON test_1q TO public;
GRANT SELECT ON test_2q TO public;
set role to bob;
select * from test;
id | tm | user_name | rem
----+---------------------+-----------+---------
1 | 2017-01-09 22:15:15 | bob | bla-bla
2 | 2017-02-09 22:15:15 | alice | bla-bla
3 | 2017-03-09 22:15:15 | bob | bla-bla
4 | 2017-04-09 22:15:15 | alice | bla-bla
5 | 2017-05-09 22:15:15 | bob | bla-bla
6 | 2017-06-09 22:15:15 | alice | bla-bla
(6 rows)
select * from test_1q;
id | tm | user_name | rem
----+---------------------+-----------+---------
1 | 2017-01-09 22:15:15 | bob | bla-bla
3 | 2017-03-09 22:15:15 | bob | bla-bla
(2 rows)
select * from test_2q;
id | tm | user_name | rem
----+---------------------+-----------+---------
5 | 2017-05-09 22:15:15 | bob | bla-bla
(1 row)
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: BUG #14682: row level security not work with partitioned table
On Thu, Jun 1, 2017 at 2:59 AM, <fte@nct.ru> wrote:
The following bug has been logged on the website:
Bug reference: 14682
Logged by: Fakhroutdinov Evgenievich
Email address: fte@nct.ru
PostgreSQL version: 10beta1
Operating system: macOS Sierra 10.12.5
Description:create table test (
id bigserial not null,
tm timestamp not null,
user_name text not null,
rem text
) partition by range (tm);create table test_1q (like test including all);
create table test_2q (like test including all);alter table test attach partition test_1q for values from ('2017-01-01') to
('2017-04-01');
alter table test attach partition test_2q for values from ('2017-04-01') to
('2017-07-01');CREATE ROLE bob; -- Normal user
CREATE ROLE alice; -- Normal userinsert into test(tm,user_name,rem)
values
('2017-01-09 22:15:15','bob','bla-bla'),
('2017-02-09 22:15:15','alice','bla-bla'),
('2017-03-09 22:15:15','bob','bla-bla'),
('2017-04-09 22:15:15','alice','bla-bla'),
('2017-05-09 22:15:15','bob','bla-bla'),
('2017-06-09 22:15:15','alice','bla-bla');ALTER TABLE test ENABLE ROW LEVEL SECURITY;
ALTER TABLE test_1q ENABLE ROW LEVEL SECURITY;
ALTER TABLE test_2q ENABLE ROW LEVEL SECURITY;CREATE POLICY view_test ON test FOR SELECT USING (current_user =
user_name);
CREATE POLICY view_test_1q ON test_1q FOR SELECT USING (current_user =
user_name);
CREATE POLICY view_test_2q ON test_2q FOR SELECT USING (current_user =
user_name);GRANT SELECT ON test TO public;
GRANT SELECT ON test_1q TO public;
GRANT SELECT ON test_2q TO public;set role to bob;
select * from test;
id | tm | user_name | rem
----+---------------------+-----------+---------
1 | 2017-01-09 22:15:15 | bob | bla-bla
2 | 2017-02-09 22:15:15 | alice | bla-bla
3 | 2017-03-09 22:15:15 | bob | bla-bla
4 | 2017-04-09 22:15:15 | alice | bla-bla
5 | 2017-05-09 22:15:15 | bob | bla-bla
6 | 2017-06-09 22:15:15 | alice | bla-bla
(6 rows)select * from test_1q;
id | tm | user_name | rem
----+---------------------+-----------+---------
1 | 2017-01-09 22:15:15 | bob | bla-bla
3 | 2017-03-09 22:15:15 | bob | bla-bla
(2 rows)select * from test_2q;
id | tm | user_name | rem
----+---------------------+-----------+---------
5 | 2017-05-09 22:15:15 | bob | bla-bla
(1 row)
This is indeed a bug. fireRIRrules is currently skipping the RLS
policy check when relkind == PARTITIONED_TABLES, so RLS policies are
not applied. The attached patch fixes the behavior.
Thanks,
--
Mike Palmiotto
Software Engineer
Crunchy Data Solutions
https://crunchydata.com
Attachments:
0001-Add-RLS-support-to-partitioned-tables.patchtext/x-patch; charset=US-ASCII; name=0001-Add-RLS-support-to-partitioned-tables.patchDownload
From 2351a758d65ffada23f234c3d92cfa0e5de66dec Mon Sep 17 00:00:00 2001
From: Mike Palmiotto <mike.palmiotto@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 ++-
1 file changed, 2 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);
--
2.7.4
Re: BUG #14682: row level security not work with partitioned table
On Thu, Jun 1, 2017 at 11:13 AM, Mike Palmiotto
<mike.palmiotto@crunchydata.com> wrote:
This is indeed a bug. fireRIRrules is currently skipping the RLS
policy check when relkind == PARTITIONED_TABLES, so RLS policies are
not applied. The attached patch fixes the behavior.
I would expect RLS to trigger as well in this context. Note that there
should be regression tests to avoid this failure again in the future.
I have added an open item.
--
Michael
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: BUG #14682: row level security not work with partitioned table
On 2017/06/02 3:13, Mike Palmiotto wrote:
On Thu, Jun 1, 2017 at 2:59 AM, <fte@nct.ru> wrote:
The following bug has been logged on the website:
Bug reference: 14682
Logged by: Fakhroutdinov Evgenievich
Email address: fte@nct.ru
PostgreSQL version: 10beta1
Operating system: macOS Sierra 10.12.5
Description:create table test (
id bigserial not null,
tm timestamp not null,
user_name text not null,
rem text
) partition by range (tm);create table test_1q (like test including all);
create table test_2q (like test including all);alter table test attach partition test_1q for values from ('2017-01-01') to
('2017-04-01');
alter table test attach partition test_2q for values from ('2017-04-01') to
('2017-07-01');CREATE ROLE bob; -- Normal user
CREATE ROLE alice; -- Normal userinsert into test(tm,user_name,rem)
values
('2017-01-09 22:15:15','bob','bla-bla'),
('2017-02-09 22:15:15','alice','bla-bla'),
('2017-03-09 22:15:15','bob','bla-bla'),
('2017-04-09 22:15:15','alice','bla-bla'),
('2017-05-09 22:15:15','bob','bla-bla'),
('2017-06-09 22:15:15','alice','bla-bla');ALTER TABLE test ENABLE ROW LEVEL SECURITY;
ALTER TABLE test_1q ENABLE ROW LEVEL SECURITY;
ALTER TABLE test_2q ENABLE ROW LEVEL SECURITY;CREATE POLICY view_test ON test FOR SELECT USING (current_user =
user_name);
CREATE POLICY view_test_1q ON test_1q FOR SELECT USING (current_user =
user_name);
CREATE POLICY view_test_2q ON test_2q FOR SELECT USING (current_user =
user_name);GRANT SELECT ON test TO public;
GRANT SELECT ON test_1q TO public;
GRANT SELECT ON test_2q TO public;set role to bob;
select * from test;
id | tm | user_name | rem
----+---------------------+-----------+---------
1 | 2017-01-09 22:15:15 | bob | bla-bla
2 | 2017-02-09 22:15:15 | alice | bla-bla
3 | 2017-03-09 22:15:15 | bob | bla-bla
4 | 2017-04-09 22:15:15 | alice | bla-bla
5 | 2017-05-09 22:15:15 | bob | bla-bla
6 | 2017-06-09 22:15:15 | alice | bla-bla
(6 rows)select * from test_1q;
id | tm | user_name | rem
----+---------------------+-----------+---------
1 | 2017-01-09 22:15:15 | bob | bla-bla
3 | 2017-03-09 22:15:15 | bob | bla-bla
(2 rows)select * from test_2q;
id | tm | user_name | rem
----+---------------------+-----------+---------
5 | 2017-05-09 22:15:15 | bob | bla-bla
(1 row)This is indeed a bug. fireRIRrules is currently skipping the RLS
policy check when relkind == PARTITIONED_TABLES, so RLS policies are
not applied. The attached patch fixes the behavior.
Thanks Mike for creating the patch. Agree with Michael that a test would
be nice.
Thanks,
Amit
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: BUG #14682: row level security not work with partitioned table
On Thu, Jun 1, 2017 at 10:18 PM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
On 2017/06/02 3:13, Mike Palmiotto wrote:
This is indeed a bug. fireRIRrules is currently skipping the RLS
policy check when relkind == PARTITIONED_TABLES, so RLS policies are
not applied. The attached patch fixes the behavior.Thanks Mike for creating the patch. Agree with Michael that a test would
be nice.
I've got some regression tests in the making. I'll post back here once
I've wrapped those up.
Thanks,
--
Mike Palmiotto
Software Engineer
Crunchy Data Solutions
https://crunchydata.com
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: BUG #14682: row level security not work with partitioned table
On Fri, Jun 02, 2017 at 09:28:16AM +0900, Michael Paquier wrote:
On Thu, Jun 1, 2017 at 11:13 AM, Mike Palmiotto
<mike.palmiotto@crunchydata.com> wrote:This is indeed a bug. fireRIRrules is currently skipping the RLS
policy check when relkind == PARTITIONED_TABLES, so RLS policies are
not applied. The attached patch fixes the behavior.I would expect RLS to trigger as well in this context. Note that there
should be regression tests to avoid this failure again in the future.
I have added an open item.
[Action required within three days. This is a generic notification.]
The above-described topic is currently a PostgreSQL 10 open item. Robert,
since you committed the patch believed to have created it, you own this open
item. If some other commit is more relevant or if this does not belong as a
v10 open item, please let us know. Otherwise, please observe the policy on
open item ownership[1]/messages/by-id/20170404140717.GA2675809@tornado.leadboat.com and send a status update within three calendar days of
this message. Include a date for your subsequent status update. Testers may
discover new open items at any time, and I want to plan to get them all fixed
well in advance of shipping v10. Consequently, I will appreciate your efforts
toward speedy resolution. Thanks.
[1]: /messages/by-id/20170404140717.GA2675809@tornado.leadboat.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: BUG #14682: row level security not work with partitioned table
On 06/04/2017 03:33 PM, Noah Misch wrote:
On Fri, Jun 02, 2017 at 09:28:16AM +0900, Michael Paquier wrote:
On Thu, Jun 1, 2017 at 11:13 AM, Mike Palmiotto
<mike.palmiotto@crunchydata.com> wrote:This is indeed a bug. fireRIRrules is currently skipping the RLS
policy check when relkind == PARTITIONED_TABLES, so RLS policies are
not applied. The attached patch fixes the behavior.I would expect RLS to trigger as well in this context. Note that there
should be regression tests to avoid this failure again in the future.
I have added an open item.[Action required within three days. This is a generic notification.]
The above-described topic is currently a PostgreSQL 10 open item. Robert,
since you committed the patch believed to have created it, you own this open
item.
Unless Robert objects, I'll work with Mike to get a fix posted and
committed in the next day or two.
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
Re: BUG #14682: row level security not work with partitioned table
On Mon, Jun 5, 2017 at 10:20 AM, Joe Conway <mail@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.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14682: row level security not work with partitioned table
On Mon, Jun 5, 2017 at 10:36 AM, Robert Haas <robertmhaas@gmail.com> wrote:
On Mon, Jun 5, 2017 at 10:20 AM, Joe Conway <mail@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
Attachments:
0001-Add-RLS-support-to-partitioned-tables.patchtext/x-patch; charset=US-ASCII; name=0001-Add-RLS-support-to-partitioned-tables.patchDownload
From 48a9586881872d4b8c9ca77e0c0da48db611e326 Mon Sep 17 00:00:00 2001
From: Mike Palmiotto <mike.palmiotto@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
Re: BUG #14682: row level security not work with partitioned table
On 06/06/2017 11:57 AM, Mike Palmiotto wrote:
On Mon, Jun 5, 2017 at 10:36 AM, Robert Haas <robertmhaas@gmail.com> wrote:
On Mon, Jun 5, 2017 at 10:20 AM, Joe Conway <mail@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.
Thanks,
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
Re: BUG #14682: row level security not work with partitioned table
On Tue, Jun 6, 2017 at 4:07 PM, Joe Conway <mail@joeconway.com> wrote:
On 06/06/2017 11:57 AM, Mike Palmiotto wrote:
On Mon, Jun 5, 2017 at 10:36 AM, Robert Haas <robertmhaas@gmail.com> wrote:
On Mon, Jun 5, 2017 at 10:20 AM, Joe Conway <mail@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
Attachments:
0001-Add-RLS-support-to-partitioned-tables.patchtext/x-patch; charset=US-ASCII; name=0001-Add-RLS-support-to-partitioned-tables.patchDownload
From 08432d93ed753a1e5cd4585ccf00e900abbd685f Mon Sep 17 00:00:00 2001
From: Mike Palmiotto <mike.palmiotto@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
Re: BUG #14682: row level security not work with partitioned table
On 06/06/2017 02:44 PM, Mike Palmiotto wrote:
On Tue, Jun 6, 2017 at 4:07 PM, Joe Conway <mail@joeconway.com> wrote:
On 06/06/2017 11:57 AM, Mike Palmiotto wrote:
On Mon, Jun 5, 2017 at 10:36 AM, Robert Haas <robertmhaas@gmail.com> wrote:
On Mon, Jun 5, 2017 at 10:20 AM, Joe Conway <mail@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. 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?
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
Re: [BUGS] BUG #14682: row level security not work with partitioned table
On Wed, Jun 7, 2017 at 9:52 AM, Joe Conway <mail@joeconway.com> wrote:
On 06/06/2017 02:44 PM, Mike Palmiotto wrote:
On Tue, Jun 6, 2017 at 4:07 PM, Joe Conway <mail@joeconway.com> wrote:
On 06/06/2017 11:57 AM, Mike Palmiotto wrote:
On Mon, Jun 5, 2017 at 10:36 AM, Robert Haas <robertmhaas@gmail.com> wrote:
On Mon, Jun 5, 2017 at 10:20 AM, Joe Conway <mail@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. 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.
+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.
--
Michael--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: BUG #14682: row level security not work with partitioned table
On Tue, Jun 6, 2017 at 9:12 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:
On Wed, Jun 7, 2017 at 9:52 AM, Joe Conway <mail@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
Attachments:
0001-Add-RLS-support-to-partitioned-tables.patchtext/x-patch; charset=US-ASCII; name=0001-Add-RLS-support-to-partitioned-tables.patchDownload
From 8c55045bd2d856fe4707582de0270c26d3a4c285 Mon Sep 17 00:00:00 2001
From: Mike Palmiotto <mike.palmiotto@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
Re: BUG #14682: row level security not work with partitioned table
On 06/07/2017 06:49 AM, Mike Palmiotto wrote:
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.
Thanks, will take a look towards the end of the day.
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
Re: BUG #14682: row level security not work with partitioned table
On Wed, Jun 7, 2017 at 9:49 AM, Mike Palmiotto
<mike.palmiotto@crunchydata.com> wrote:
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?
Assuming my digging is correct, there's some other explanation for
this not working as expected...
postgres=> select po.oprname, pp.proname, pp.provolatile from pg_proc
pp join pg_operator po on pp.proname::text = po.oprcode::text where
po.oprname = '<>' and pp.proname like 'int%ne';
oprname | proname | provolatile
---------+-------------+-------------
<> | int4ne | i
<> | int2ne | i
<> | int24ne | i
<> | int42ne | i
<> | int8ne | i
<> | int84ne | i
<> | int48ne | i
<> | interval_ne | i
<> | int28ne | i
<> | int82ne | i
(10 rows)
Thoughts?
Thanks,
--
Mike Palmiotto
Software Engineer
Crunchy Data Solutions
https://crunchydata.com
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: BUG #14682: row level security not work with partitioned table
On Wed, Jun 07, 2017 at 08:45:20AM -0700, Joe Conway wrote:
On 06/07/2017 06:49 AM, Mike Palmiotto wrote:
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.Thanks, will take a look towards the end of the day.
This PostgreSQL 10 open item is past due for your status update. Kindly send
a status update within 24 hours, and include a date for your subsequent status
update. Refer to the policy on open item ownership:
/messages/by-id/20170404140717.GA2675809@tornado.leadboat.com
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: BUG #14682: row level security not work with partitioned table
On 06/08/2017 11:09 PM, Noah Misch wrote:
On Wed, Jun 07, 2017 at 08:45:20AM -0700, Joe Conway wrote:
On 06/07/2017 06:49 AM, Mike Palmiotto wrote:
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.Thanks, will take a look towards the end of the day.
This PostgreSQL 10 open item is past due for your status update. Kindly send
a status update within 24 hours, and include a date for your subsequent status
update. Refer to the policy on open item ownership:
/messages/by-id/20170404140717.GA2675809@tornado.leadboat.com
I started reviewing the latest patch last night and will try to finish
up this afternoon (west coast USA time).
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
Re: [BUGS] BUG #14682: row level security not work with partitioned table
On 06/09/2017 06:16 AM, Joe Conway wrote:
On 06/08/2017 11:09 PM, Noah Misch wrote:
On Wed, Jun 07, 2017 at 08:45:20AM -0700, Joe Conway wrote:
On 06/07/2017 06:49 AM, Mike Palmiotto wrote:
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.Thanks, will take a look towards the end of the day.
This PostgreSQL 10 open item is past due for your status update. Kindly send
a status update within 24 hours, and include a date for your subsequent status
update. Refer to the policy on open item ownership:
/messages/by-id/20170404140717.GA2675809@tornado.leadboat.comI started reviewing the latest patch last night and will try to finish
up this afternoon (west coast USA time).
I left the actual (2 line) code change untouched, but I tweaked the
regression test changes a bit. If there are no complaints I will push
tomorrow (Saturday).
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
Attachments:
add-rls-support-to-partitioned-tables-01.patchtext/x-patch; name=add-rls-support-to-partitioned-tables-01.patchDownload
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
*************** fireRIRrules(Query *parsetree, List *act
*** 1835,1841 ****
/* Only normal relations can have RLS policies */
if (rte->rtekind != RTE_RELATION ||
! rte->relkind != RELKIND_RELATION)
continue;
rel = heap_open(rte->relid, NoLock);
--- 1835,1842 ----
/* Only normal relations can have RLS policies */
if (rte->rtekind != RTE_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..d382a9f 100644
*** a/src/test/regress/expected/rowsecurity.out
--- b/src/test/regress/expected/rowsecurity.out
*************** EXPLAIN (COSTS OFF) SELECT * FROM t1 WHE
*** 899,904 ****
--- 899,1332 ----
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
+ CREATE POLICY pp1r ON part_document AS RESTRICTIVE TO regress_rls_dave
+ USING (cid < 55);
+ \d+ part_document
+ Table "regress_rls_schema.part_document"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+ ---------+---------+-----------+----------+---------+----------+--------------+-------------
+ did | integer | | | | plain | |
+ cid | integer | | | | plain | |
+ dlevel | integer | | not null | | plain | |
+ dauthor | name | | | | plain | |
+ dtitle | text | | | | extended | |
+ 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))
+ Partitions: part_document_fiction FOR VALUES FROM (11) TO (12),
+ part_document_nonfiction FOR VALUES FROM (99) TO (100),
+ part_document_satire 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) |
+ (2 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)
+
+ 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_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)
+
+ 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 (dlevel <= $0) AND f_leak(dtitle))
+ (6 rows)
+
+ -- pp1 ERROR
+ INSERT INTO part_document VALUES (100, 11, 5, 'regress_rls_dave', 'testing pp1'); -- fail
+ ERROR: new row violates row-level security policy for table "part_document"
+ -- pp1r ERROR
+ INSERT INTO part_document VALUES (100, 99, 1, 'regress_rls_dave', 'testing pp1r'); -- fail
+ ERROR: new row violates row-level security policy "pp1r" for table "part_document"
+ -- Show that RLS policy does not apply for direct inserts to children
+ -- This should fail with RLS POLICY pp1r violation.
+ INSERT INTO part_document VALUES (100, 55, 1, 'regress_rls_dave', 'testing RLS with partitions'); -- fail
+ ERROR: new row violates row-level security policy "pp1r" for table "part_document"
+ -- But this should succeed.
+ INSERT INTO part_document_satire VALUES (100, 55, 1, 'regress_rls_dave', 'testing RLS with partitions'); -- success
+ -- We still cannot see the row using the parent
+ 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)
+
+ -- But we can if we look directly
+ SELECT * FROM part_document_satire WHERE f_leak(dtitle) ORDER BY did;
+ NOTICE: f_leak => my first satire
+ NOTICE: f_leak => great satire
+ NOTICE: f_leak => testing RLS with partitions
+ did | cid | dlevel | dauthor | dtitle
+ -----+-----+--------+-------------------+-----------------------------
+ 4 | 55 | 1 | regress_rls_bob | my first satire
+ 8 | 55 | 2 | regress_rls_carol | great satire
+ 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
+ (3 rows)
+
+ -- 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_satire ENABLE ROW LEVEL SECURITY;
+ CREATE POLICY pp3 ON part_document_satire AS RESTRICTIVE
+ USING (cid < 55);
+ -- This should fail with RLS violation now.
+ SET SESSION AUTHORIZATION regress_rls_dave;
+ INSERT INTO part_document_satire VALUES (101, 55, 1, 'regress_rls_dave', 'testing RLS with partitions'); -- fail
+ ERROR: new row violates row-level security policy for table "part_document_satire"
+ -- And now we cannot see directly into the partition either, due to RLS
+ SELECT * FROM part_document_satire WHERE f_leak(dtitle) ORDER BY did;
+ did | cid | dlevel | dauthor | dtitle
+ -----+-----+--------+---------+--------
+ (0 rows)
+
+ -- The parent looks same as before
+ -- viewpoint from 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 (dlevel <= $0) AND f_leak(dtitle))
+ (6 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 => 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 => testing RLS with partitions
+ 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
+ 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
+ (11 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)
+
+ -- 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
+ 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
+ (11 rows)
+
+ SELECT * FROM part_document_satire ORDER by did;
+ did | cid | dlevel | dauthor | dtitle
+ -----+-----+--------+-------------------+-----------------------------
+ 4 | 55 | 1 | regress_rls_bob | my first satire
+ 8 | 55 | 2 | regress_rls_carol | great satire
+ 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
+ (3 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
+ 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
+ (11 rows)
+
+ SELECT * FROM part_document_satire ORDER by did;
+ did | cid | dlevel | dauthor | dtitle
+ -----+-----+--------+-------------------+-----------------------------
+ 4 | 55 | 1 | regress_rls_bob | my first satire
+ 8 | 55 | 2 | regress_rls_carol | great satire
+ 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
+ (3 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
+ 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
+ (11 rows)
+
+ SELECT * FROM part_document_satire ORDER by did;
+ did | cid | dlevel | dauthor | dtitle
+ -----+-----+--------+-------------------+-----------------------------
+ 4 | 55 | 1 | regress_rls_bob | my first satire
+ 8 | 55 | 2 | regress_rls_carol | great satire
+ 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
+ (3 rows)
+
+ -- When RLS disabled, other users get ERROR.
+ SET SESSION AUTHORIZATION regress_rls_dave;
+ SET row_security TO OFF;
+ SELECT * FROM part_document ORDER by did;
+ ERROR: query would be affected by row-level security policy for table "part_document"
+ SELECT * FROM part_document_satire ORDER by did;
+ ERROR: query would be affected by row-level security policy for table "part_document_satire"
----- 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..80537ff 100644
*** a/src/test/regress/sql/rowsecurity.sql
--- b/src/test/regress/sql/rowsecurity.sql
*************** SET row_security TO OFF;
*** 308,313 ****
--- 308,455 ----
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
+ CREATE POLICY pp1r ON part_document AS RESTRICTIVE TO regress_rls_dave
+ USING (cid < 55);
+
+ \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;
+ EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
+
+ -- viewpoint from regress_rls_carol
+ 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);
+
+ -- 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);
+
+ -- pp1 ERROR
+ INSERT INTO part_document VALUES (100, 11, 5, 'regress_rls_dave', 'testing pp1'); -- fail
+ -- pp1r ERROR
+ INSERT INTO part_document VALUES (100, 99, 1, 'regress_rls_dave', 'testing pp1r'); -- fail
+
+ -- Show that RLS policy does not apply for direct inserts to children
+ -- This should fail with RLS POLICY pp1r violation.
+ INSERT INTO part_document VALUES (100, 55, 1, 'regress_rls_dave', 'testing RLS with partitions'); -- fail
+ -- But this should succeed.
+ INSERT INTO part_document_satire VALUES (100, 55, 1, 'regress_rls_dave', 'testing RLS with partitions'); -- success
+ -- We still cannot see the row using the parent
+ SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
+ -- But we can if we look directly
+ SELECT * FROM part_document_satire WHERE f_leak(dtitle) ORDER BY did;
+
+ -- 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_satire ENABLE ROW LEVEL SECURITY;
+ CREATE POLICY pp3 ON part_document_satire AS RESTRICTIVE
+ USING (cid < 55);
+ -- This should fail with RLS violation now.
+ SET SESSION AUTHORIZATION regress_rls_dave;
+ INSERT INTO part_document_satire VALUES (101, 55, 1, 'regress_rls_dave', 'testing RLS with partitions'); -- fail
+ -- And now we cannot see directly into the partition either, due to RLS
+ SELECT * FROM part_document_satire WHERE f_leak(dtitle) ORDER BY did;
+ -- The parent looks same as before
+ -- viewpoint from 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);
+
+ -- viewpoint from regress_rls_carol
+ 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);
+
+ -- 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;
+ SELECT * FROM part_document_satire 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;
+ SELECT * FROM part_document_satire 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;
+ SELECT * FROM part_document_satire ORDER by did;
+
+ -- When RLS disabled, other users get ERROR.
+ SET SESSION AUTHORIZATION regress_rls_dave;
+ SET row_security TO OFF;
+ SELECT * FROM part_document ORDER by did;
+ SELECT * FROM part_document_satire ORDER by did;
+
----- Dependencies -----
SET SESSION AUTHORIZATION regress_rls_alice;
SET row_security TO ON;
Re: BUG #14682: row level security not work with partitioned table
On 06/09/2017 02:52 PM, Joe Conway wrote:
On 06/09/2017 06:16 AM, Joe Conway wrote:
On 06/08/2017 11:09 PM, Noah Misch wrote:
On Wed, Jun 07, 2017 at 08:45:20AM -0700, Joe Conway wrote:
On 06/07/2017 06:49 AM, Mike Palmiotto wrote:
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.Thanks, will take a look towards the end of the day.
This PostgreSQL 10 open item is past due for your status update. Kindly send
a status update within 24 hours, and include a date for your subsequent status
update. Refer to the policy on open item ownership:
/messages/by-id/20170404140717.GA2675809@tornado.leadboat.comI started reviewing the latest patch last night and will try to finish
up this afternoon (west coast USA time).I left the actual (2 line) code change untouched, but I tweaked the
regression test changes a bit. If there are no complaints I will push
tomorrow (Saturday).
I waited until Sunday, but pushed none-the-less.
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development