Tom, * Tom Lane (t...@sss.pgh.pa.us) wrote: > broken that way as rowsecurity.sql, which is (still) creating roles > named "alice" and "bob", but it's not acceptable like this.
Attached is a patch to fix all of the role usage in rowsecurity.sql (I believe, feel free to let me know if there's anything else). In particular, all of the roles are changed to begin with 'regress_', and they are all now created with NOLOGIN. I'll plan to push this in the next day or so. I'll also work up a patch for the rest of the CREATE USER/ROLE usage in the core regression tests. This will result in a bit of not-strictly-necessary code churn, but having consistency in the code base really is valuable where we have an agreed upon policy as to what all the tests should be doing, for new (and even old) developers to follow. Comments and concerns welcome, of course. Thanks! Stephen
From 1c09f28d20582f62b386004b67a415f170af5fe6 Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfr...@snowman.net>
Date: Sun, 10 Apr 2016 13:34:19 -0400
Subject: [PATCH] Prefix RLS regression test roles with 'regress_'
To avoid any possible overlap with existing roles on a system when
doing a 'make installcheck', use role names which start with
'regress_'.
Pointed out by Tom.
---
src/test/regress/expected/rowsecurity.out | 752 +++++++++++++++---------------
src/test/regress/sql/rowsecurity.sql | 452 +++++++++---------
2 files changed, 602 insertions(+), 602 deletions(-)
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 067aa8d..c76f6b7 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -4,43 +4,43 @@
-- Clean up in case a prior regression run failed
-- Suppress NOTICE messages when users/groups don't exist
SET client_min_messages TO 'warning';
-DROP USER IF EXISTS rls_regress_user0;
-DROP USER IF EXISTS rls_regress_user1;
-DROP USER IF EXISTS rls_regress_user2;
-DROP USER IF EXISTS rls_regress_exempt_user;
-DROP ROLE IF EXISTS rls_regress_group1;
-DROP ROLE IF EXISTS rls_regress_group2;
-DROP SCHEMA IF EXISTS rls_regress_schema CASCADE;
+DROP USER IF EXISTS regress_rls_user0;
+DROP USER IF EXISTS regress_rls_user1;
+DROP USER IF EXISTS regress_rls_user2;
+DROP USER IF EXISTS regress_rls_exempt_user;
+DROP ROLE IF EXISTS regress_rls_group1;
+DROP ROLE IF EXISTS regress_rls_group2;
+DROP SCHEMA IF EXISTS regress_rls_schema CASCADE;
RESET client_min_messages;
-- initial setup
-CREATE USER rls_regress_user0;
-CREATE USER rls_regress_user1;
-CREATE USER rls_regress_user2;
-CREATE USER rls_regress_exempt_user BYPASSRLS;
-CREATE ROLE rls_regress_group1 NOLOGIN;
-CREATE ROLE rls_regress_group2 NOLOGIN;
-GRANT rls_regress_group1 TO rls_regress_user1;
-GRANT rls_regress_group2 TO rls_regress_user2;
-CREATE SCHEMA rls_regress_schema;
-GRANT ALL ON SCHEMA rls_regress_schema to public;
-SET search_path = rls_regress_schema;
+CREATE USER regress_rls_user0 NOLOGIN;
+CREATE USER regress_rls_user1 NOLOGIN;
+CREATE USER regress_rls_user2 NOLOGIN;
+CREATE USER regress_rls_exempt_user BYPASSRLS NOLOGIN;
+CREATE ROLE regress_rls_group1 NOLOGIN;
+CREATE ROLE regress_rls_group2 NOLOGIN;
+GRANT regress_rls_group1 TO regress_rls_user1;
+GRANT regress_rls_group2 TO regress_rls_user2;
+CREATE SCHEMA regress_rls_schema;
+GRANT ALL ON SCHEMA regress_rls_schema to public;
+SET search_path = regress_rls_schema;
-- setup of malicious function
CREATE OR REPLACE FUNCTION f_leak(text) RETURNS bool
COST 0.0000001 LANGUAGE plpgsql
AS 'BEGIN RAISE NOTICE ''f_leak => %'', $1; RETURN true; END';
GRANT EXECUTE ON FUNCTION f_leak(text) TO public;
-- BASIC Row-Level Security Scenario
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE TABLE uaccount (
pguser name primary key,
seclv int
);
GRANT SELECT ON uaccount TO public;
INSERT INTO uaccount VALUES
- ('rls_regress_user0', 99),
- ('rls_regress_user1', 1),
- ('rls_regress_user2', 2),
- ('rls_regress_user3', 3);
+ ('regress_rls_user0', 99),
+ ('regress_rls_user1', 1),
+ ('regress_rls_user2', 2),
+ ('regress_rls_user3', 3);
CREATE TABLE category (
cid int primary key,
cname text
@@ -60,20 +60,20 @@ CREATE TABLE document (
);
GRANT ALL ON document TO public;
INSERT INTO document VALUES
- ( 1, 11, 1, 'rls_regress_user1', 'my first novel'),
- ( 2, 11, 2, 'rls_regress_user1', 'my second novel'),
- ( 3, 22, 2, 'rls_regress_user1', 'my science fiction'),
- ( 4, 44, 1, 'rls_regress_user1', 'my first manga'),
- ( 5, 44, 2, 'rls_regress_user1', 'my second manga'),
- ( 6, 22, 1, 'rls_regress_user2', 'great science fiction'),
- ( 7, 33, 2, 'rls_regress_user2', 'great technology book'),
- ( 8, 44, 1, 'rls_regress_user2', 'great manga');
+ ( 1, 11, 1, 'regress_rls_user1', 'my first novel'),
+ ( 2, 11, 2, 'regress_rls_user1', 'my second novel'),
+ ( 3, 22, 2, 'regress_rls_user1', 'my science fiction'),
+ ( 4, 44, 1, 'regress_rls_user1', 'my first manga'),
+ ( 5, 44, 2, 'regress_rls_user1', 'my second manga'),
+ ( 6, 22, 1, 'regress_rls_user2', 'great science fiction'),
+ ( 7, 33, 2, 'regress_rls_user2', 'great technology book'),
+ ( 8, 44, 1, 'regress_rls_user2', 'great manga');
ALTER TABLE document ENABLE ROW LEVEL SECURITY;
-- user's security level must be higher than or equal to document's
CREATE POLICY p1 ON document
USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user));
--- viewpoint from rls_regress_user1
-SET SESSION AUTHORIZATION rls_regress_user1;
+-- viewpoint from regress_rls_user1
+SET SESSION AUTHORIZATION regress_rls_user1;
SET row_security TO ON;
SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
NOTICE: f_leak => my first novel
@@ -82,10 +82,10 @@ NOTICE: f_leak => great science fiction
NOTICE: f_leak => great manga
did | cid | dlevel | dauthor | dtitle
-----+-----+--------+-------------------+-----------------------
- 1 | 11 | 1 | rls_regress_user1 | my first novel
- 4 | 44 | 1 | rls_regress_user1 | my first manga
- 6 | 22 | 1 | rls_regress_user2 | great science fiction
- 8 | 44 | 1 | rls_regress_user2 | great manga
+ 1 | 11 | 1 | regress_rls_user1 | my first novel
+ 4 | 44 | 1 | regress_rls_user1 | my first manga
+ 6 | 22 | 1 | regress_rls_user2 | great science fiction
+ 8 | 44 | 1 | regress_rls_user2 | great manga
(4 rows)
SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did;
@@ -95,10 +95,10 @@ NOTICE: f_leak => great science fiction
NOTICE: f_leak => great manga
cid | did | dlevel | dauthor | dtitle | cname
-----+-----+--------+-------------------+-----------------------+-----------------
- 11 | 1 | 1 | rls_regress_user1 | my first novel | novel
- 44 | 4 | 1 | rls_regress_user1 | my first manga | manga
- 22 | 6 | 1 | rls_regress_user2 | great science fiction | science fiction
- 44 | 8 | 1 | rls_regress_user2 | great manga | manga
+ 11 | 1 | 1 | regress_rls_user1 | my first novel | novel
+ 44 | 4 | 1 | regress_rls_user1 | my first manga | manga
+ 22 | 6 | 1 | regress_rls_user2 | great science fiction | science fiction
+ 44 | 8 | 1 | regress_rls_user2 | great manga | manga
(4 rows)
-- try a sampled version
@@ -109,13 +109,13 @@ NOTICE: f_leak => great science fiction
NOTICE: f_leak => great manga
did | cid | dlevel | dauthor | dtitle
-----+-----+--------+-------------------+-----------------------
- 4 | 44 | 1 | rls_regress_user1 | my first manga
- 6 | 22 | 1 | rls_regress_user2 | great science fiction
- 8 | 44 | 1 | rls_regress_user2 | great manga
+ 4 | 44 | 1 | regress_rls_user1 | my first manga
+ 6 | 22 | 1 | regress_rls_user2 | great science fiction
+ 8 | 44 | 1 | regress_rls_user2 | great manga
(3 rows)
--- viewpoint from rls_regress_user2
-SET SESSION AUTHORIZATION rls_regress_user2;
+-- viewpoint from regress_rls_user2
+SET SESSION AUTHORIZATION regress_rls_user2;
SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
NOTICE: f_leak => my first novel
NOTICE: f_leak => my second novel
@@ -127,14 +127,14 @@ NOTICE: f_leak => great technology book
NOTICE: f_leak => great manga
did | cid | dlevel | dauthor | dtitle
-----+-----+--------+-------------------+-----------------------
- 1 | 11 | 1 | rls_regress_user1 | my first novel
- 2 | 11 | 2 | rls_regress_user1 | my second novel
- 3 | 22 | 2 | rls_regress_user1 | my science fiction
- 4 | 44 | 1 | rls_regress_user1 | my first manga
- 5 | 44 | 2 | rls_regress_user1 | my second manga
- 6 | 22 | 1 | rls_regress_user2 | great science fiction
- 7 | 33 | 2 | rls_regress_user2 | great technology book
- 8 | 44 | 1 | rls_regress_user2 | great manga
+ 1 | 11 | 1 | regress_rls_user1 | my first novel
+ 2 | 11 | 2 | regress_rls_user1 | my second novel
+ 3 | 22 | 2 | regress_rls_user1 | my science fiction
+ 4 | 44 | 1 | regress_rls_user1 | my first manga
+ 5 | 44 | 2 | regress_rls_user1 | my second manga
+ 6 | 22 | 1 | regress_rls_user2 | great science fiction
+ 7 | 33 | 2 | regress_rls_user2 | great technology book
+ 8 | 44 | 1 | regress_rls_user2 | great manga
(8 rows)
SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did;
@@ -148,14 +148,14 @@ NOTICE: f_leak => great technology book
NOTICE: f_leak => great manga
cid | did | dlevel | dauthor | dtitle | cname
-----+-----+--------+-------------------+-----------------------+-----------------
- 11 | 1 | 1 | rls_regress_user1 | my first novel | novel
- 11 | 2 | 2 | rls_regress_user1 | my second novel | novel
- 22 | 3 | 2 | rls_regress_user1 | my science fiction | science fiction
- 44 | 4 | 1 | rls_regress_user1 | my first manga | manga
- 44 | 5 | 2 | rls_regress_user1 | my second manga | manga
- 22 | 6 | 1 | rls_regress_user2 | great science fiction | science fiction
- 33 | 7 | 2 | rls_regress_user2 | great technology book | technology
- 44 | 8 | 1 | rls_regress_user2 | great manga | manga
+ 11 | 1 | 1 | regress_rls_user1 | my first novel | novel
+ 11 | 2 | 2 | regress_rls_user1 | my second novel | novel
+ 22 | 3 | 2 | regress_rls_user1 | my science fiction | science fiction
+ 44 | 4 | 1 | regress_rls_user1 | my first manga | manga
+ 44 | 5 | 2 | regress_rls_user1 | my second manga | manga
+ 22 | 6 | 1 | regress_rls_user2 | great science fiction | science fiction
+ 33 | 7 | 2 | regress_rls_user2 | great technology book | technology
+ 44 | 8 | 1 | regress_rls_user2 | great manga | manga
(8 rows)
-- try a sampled version
@@ -167,10 +167,10 @@ NOTICE: f_leak => great science fiction
NOTICE: f_leak => great manga
did | cid | dlevel | dauthor | dtitle
-----+-----+--------+-------------------+-----------------------
- 4 | 44 | 1 | rls_regress_user1 | my first manga
- 5 | 44 | 2 | rls_regress_user1 | my second manga
- 6 | 22 | 1 | rls_regress_user2 | great science fiction
- 8 | 44 | 1 | rls_regress_user2 | great manga
+ 4 | 44 | 1 | regress_rls_user1 | my first manga
+ 5 | 44 | 2 | regress_rls_user1 | my second manga
+ 6 | 22 | 1 | regress_rls_user2 | great science fiction
+ 8 | 44 | 1 | regress_rls_user2 | great manga
(4 rows)
EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
@@ -206,10 +206,10 @@ ALTER POLICY p1 ON document USING (true); --fail
ERROR: must be owner of relation document
DROP POLICY p1 ON document; --fail
ERROR: must be owner of relation document
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
ALTER POLICY p1 ON document USING (dauthor = current_user);
--- viewpoint from rls_regress_user1 again
-SET SESSION AUTHORIZATION rls_regress_user1;
+-- viewpoint from regress_rls_user1 again
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
NOTICE: f_leak => my first novel
NOTICE: f_leak => my second novel
@@ -218,11 +218,11 @@ NOTICE: f_leak => my first manga
NOTICE: f_leak => my second manga
did | cid | dlevel | dauthor | dtitle
-----+-----+--------+-------------------+--------------------
- 1 | 11 | 1 | rls_regress_user1 | my first novel
- 2 | 11 | 2 | rls_regress_user1 | my second novel
- 3 | 22 | 2 | rls_regress_user1 | my science fiction
- 4 | 44 | 1 | rls_regress_user1 | my first manga
- 5 | 44 | 2 | rls_regress_user1 | my second manga
+ 1 | 11 | 1 | regress_rls_user1 | my first novel
+ 2 | 11 | 2 | regress_rls_user1 | my second novel
+ 3 | 22 | 2 | regress_rls_user1 | my science fiction
+ 4 | 44 | 1 | regress_rls_user1 | my first manga
+ 5 | 44 | 2 | regress_rls_user1 | my second manga
(5 rows)
SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER by did;
@@ -233,24 +233,24 @@ NOTICE: f_leak => my first manga
NOTICE: f_leak => my second manga
cid | did | dlevel | dauthor | dtitle | cname
-----+-----+--------+-------------------+--------------------+-----------------
- 11 | 1 | 1 | rls_regress_user1 | my first novel | novel
- 11 | 2 | 2 | rls_regress_user1 | my second novel | novel
- 22 | 3 | 2 | rls_regress_user1 | my science fiction | science fiction
- 44 | 4 | 1 | rls_regress_user1 | my first manga | manga
- 44 | 5 | 2 | rls_regress_user1 | my second manga | manga
+ 11 | 1 | 1 | regress_rls_user1 | my first novel | novel
+ 11 | 2 | 2 | regress_rls_user1 | my second novel | novel
+ 22 | 3 | 2 | regress_rls_user1 | my science fiction | science fiction
+ 44 | 4 | 1 | regress_rls_user1 | my first manga | manga
+ 44 | 5 | 2 | regress_rls_user1 | my second manga | manga
(5 rows)
-- viewpoint from rls_regres_user2 again
-SET SESSION AUTHORIZATION rls_regress_user2;
+SET SESSION AUTHORIZATION regress_rls_user2;
SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
NOTICE: f_leak => great science fiction
NOTICE: f_leak => great technology book
NOTICE: f_leak => great manga
did | cid | dlevel | dauthor | dtitle
-----+-----+--------+-------------------+-----------------------
- 6 | 22 | 1 | rls_regress_user2 | great science fiction
- 7 | 33 | 2 | rls_regress_user2 | great technology book
- 8 | 44 | 1 | rls_regress_user2 | great manga
+ 6 | 22 | 1 | regress_rls_user2 | great science fiction
+ 7 | 33 | 2 | regress_rls_user2 | great technology book
+ 8 | 44 | 1 | regress_rls_user2 | great manga
(3 rows)
SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER by did;
@@ -259,9 +259,9 @@ NOTICE: f_leak => great technology book
NOTICE: f_leak => great manga
cid | did | dlevel | dauthor | dtitle | cname
-----+-----+--------+-------------------+-----------------------+-----------------
- 22 | 6 | 1 | rls_regress_user2 | great science fiction | science fiction
- 33 | 7 | 2 | rls_regress_user2 | great technology book | technology
- 44 | 8 | 1 | rls_regress_user2 | great manga | manga
+ 22 | 6 | 1 | regress_rls_user2 | great science fiction | science fiction
+ 33 | 7 | 2 | regress_rls_user2 | great technology book | technology
+ 44 | 8 | 1 | regress_rls_user2 | great manga | manga
(3 rows)
EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
@@ -286,42 +286,42 @@ EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dt
(7 rows)
-- interaction of FK/PK constraints
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE POLICY p2 ON category
- USING (CASE WHEN current_user = 'rls_regress_user1' THEN cid IN (11, 33)
- WHEN current_user = 'rls_regress_user2' THEN cid IN (22, 44)
+ USING (CASE WHEN current_user = 'regress_rls_user1' THEN cid IN (11, 33)
+ WHEN current_user = 'regress_rls_user2' THEN cid IN (22, 44)
ELSE false END);
ALTER TABLE category ENABLE ROW LEVEL SECURITY;
-- cannot delete PK referenced by invisible FK
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid;
did | cid | dlevel | dauthor | dtitle | cid | cname
-----+-----+--------+-------------------+--------------------+-----+------------
- 2 | 11 | 2 | rls_regress_user1 | my second novel | 11 | novel
- 1 | 11 | 1 | rls_regress_user1 | my first novel | 11 | novel
+ 2 | 11 | 2 | regress_rls_user1 | my second novel | 11 | novel
+ 1 | 11 | 1 | regress_rls_user1 | my first novel | 11 | novel
| | | | | 33 | technology
- 5 | 44 | 2 | rls_regress_user1 | my second manga | |
- 4 | 44 | 1 | rls_regress_user1 | my first manga | |
- 3 | 22 | 2 | rls_regress_user1 | my science fiction | |
+ 5 | 44 | 2 | regress_rls_user1 | my second manga | |
+ 4 | 44 | 1 | regress_rls_user1 | my first manga | |
+ 3 | 22 | 2 | regress_rls_user1 | my science fiction | |
(6 rows)
DELETE FROM category WHERE cid = 33; -- fails with FK violation
ERROR: update or delete on table "category" violates foreign key constraint "document_cid_fkey" on table "document"
DETAIL: Key is still referenced from table "document".
-- can insert FK referencing invisible PK
-SET SESSION AUTHORIZATION rls_regress_user2;
+SET SESSION AUTHORIZATION regress_rls_user2;
SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid;
did | cid | dlevel | dauthor | dtitle | cid | cname
-----+-----+--------+-------------------+-----------------------+-----+-----------------
- 6 | 22 | 1 | rls_regress_user2 | great science fiction | 22 | science fiction
- 8 | 44 | 1 | rls_regress_user2 | great manga | 44 | manga
- 7 | 33 | 2 | rls_regress_user2 | great technology book | |
+ 6 | 22 | 1 | regress_rls_user2 | great science fiction | 22 | science fiction
+ 8 | 44 | 1 | regress_rls_user2 | great manga | 44 | manga
+ 7 | 33 | 2 | regress_rls_user2 | great technology book | |
(3 rows)
INSERT INTO document VALUES (10, 33, 1, current_user, 'hoge');
-- UNIQUE or PRIMARY KEY constraint violation DOES reveal presence of row
-SET SESSION AUTHORIZATION rls_regress_user1;
-INSERT INTO document VALUES (8, 44, 1, 'rls_regress_user1', 'my third manga'); -- Must fail with unique violation, revealing presence of did we can't see
+SET SESSION AUTHORIZATION regress_rls_user1;
+INSERT INTO document VALUES (8, 44, 1, 'regress_rls_user1', 'my third manga'); -- Must fail with unique violation, revealing presence of did we can't see
ERROR: duplicate key value violates unique constraint "document_pkey"
SELECT * FROM document WHERE did = 8; -- and confirm we can't see it
did | cid | dlevel | dauthor | dtitle
@@ -329,9 +329,9 @@ SELECT * FROM document WHERE did = 8; -- and confirm we can't see it
(0 rows)
-- RLS policies are checked before constraints
-INSERT INTO document VALUES (8, 44, 1, 'rls_regress_user2', 'my third manga'); -- Should fail with RLS check violation, not duplicate key violation
+INSERT INTO document VALUES (8, 44, 1, 'regress_rls_user2', 'my third manga'); -- Should fail with RLS check violation, not duplicate key violation
ERROR: new row violates row-level security policy for table "document"
-UPDATE document SET did = 8, dauthor = 'rls_regress_user2' WHERE did = 5; -- Should fail with RLS check violation, not duplicate key violation
+UPDATE document SET did = 8, dauthor = 'regress_rls_user2' WHERE did = 5; -- Should fail with RLS check violation, not duplicate key violation
ERROR: new row violates row-level security policy for table "document"
-- database superuser does bypass RLS policy when enabled
RESET SESSION AUTHORIZATION;
@@ -339,15 +339,15 @@ SET row_security TO ON;
SELECT * FROM document;
did | cid | dlevel | dauthor | dtitle
-----+-----+--------+-------------------+-----------------------
- 1 | 11 | 1 | rls_regress_user1 | my first novel
- 2 | 11 | 2 | rls_regress_user1 | my second novel
- 3 | 22 | 2 | rls_regress_user1 | my science fiction
- 4 | 44 | 1 | rls_regress_user1 | my first manga
- 5 | 44 | 2 | rls_regress_user1 | my second manga
- 6 | 22 | 1 | rls_regress_user2 | great science fiction
- 7 | 33 | 2 | rls_regress_user2 | great technology book
- 8 | 44 | 1 | rls_regress_user2 | great manga
- 10 | 33 | 1 | rls_regress_user2 | hoge
+ 1 | 11 | 1 | regress_rls_user1 | my first novel
+ 2 | 11 | 2 | regress_rls_user1 | my second novel
+ 3 | 22 | 2 | regress_rls_user1 | my science fiction
+ 4 | 44 | 1 | regress_rls_user1 | my first manga
+ 5 | 44 | 2 | regress_rls_user1 | my second manga
+ 6 | 22 | 1 | regress_rls_user2 | great science fiction
+ 7 | 33 | 2 | regress_rls_user2 | great technology book
+ 8 | 44 | 1 | regress_rls_user2 | great manga
+ 10 | 33 | 1 | regress_rls_user2 | hoge
(9 rows)
SELECT * FROM category;
@@ -365,15 +365,15 @@ SET row_security TO OFF;
SELECT * FROM document;
did | cid | dlevel | dauthor | dtitle
-----+-----+--------+-------------------+-----------------------
- 1 | 11 | 1 | rls_regress_user1 | my first novel
- 2 | 11 | 2 | rls_regress_user1 | my second novel
- 3 | 22 | 2 | rls_regress_user1 | my science fiction
- 4 | 44 | 1 | rls_regress_user1 | my first manga
- 5 | 44 | 2 | rls_regress_user1 | my second manga
- 6 | 22 | 1 | rls_regress_user2 | great science fiction
- 7 | 33 | 2 | rls_regress_user2 | great technology book
- 8 | 44 | 1 | rls_regress_user2 | great manga
- 10 | 33 | 1 | rls_regress_user2 | hoge
+ 1 | 11 | 1 | regress_rls_user1 | my first novel
+ 2 | 11 | 2 | regress_rls_user1 | my second novel
+ 3 | 22 | 2 | regress_rls_user1 | my science fiction
+ 4 | 44 | 1 | regress_rls_user1 | my first manga
+ 5 | 44 | 2 | regress_rls_user1 | my second manga
+ 6 | 22 | 1 | regress_rls_user2 | great science fiction
+ 7 | 33 | 2 | regress_rls_user2 | great technology book
+ 8 | 44 | 1 | regress_rls_user2 | great manga
+ 10 | 33 | 1 | regress_rls_user2 | hoge
(9 rows)
SELECT * FROM category;
@@ -386,20 +386,20 @@ SELECT * FROM category;
(4 rows)
-- database non-superuser with bypass privilege can bypass RLS policy when disabled
-SET SESSION AUTHORIZATION rls_regress_exempt_user;
+SET SESSION AUTHORIZATION regress_rls_exempt_user;
SET row_security TO OFF;
SELECT * FROM document;
did | cid | dlevel | dauthor | dtitle
-----+-----+--------+-------------------+-----------------------
- 1 | 11 | 1 | rls_regress_user1 | my first novel
- 2 | 11 | 2 | rls_regress_user1 | my second novel
- 3 | 22 | 2 | rls_regress_user1 | my science fiction
- 4 | 44 | 1 | rls_regress_user1 | my first manga
- 5 | 44 | 2 | rls_regress_user1 | my second manga
- 6 | 22 | 1 | rls_regress_user2 | great science fiction
- 7 | 33 | 2 | rls_regress_user2 | great technology book
- 8 | 44 | 1 | rls_regress_user2 | great manga
- 10 | 33 | 1 | rls_regress_user2 | hoge
+ 1 | 11 | 1 | regress_rls_user1 | my first novel
+ 2 | 11 | 2 | regress_rls_user1 | my second novel
+ 3 | 22 | 2 | regress_rls_user1 | my science fiction
+ 4 | 44 | 1 | regress_rls_user1 | my first manga
+ 5 | 44 | 2 | regress_rls_user1 | my second manga
+ 6 | 22 | 1 | regress_rls_user2 | great science fiction
+ 7 | 33 | 2 | regress_rls_user2 | great technology book
+ 8 | 44 | 1 | regress_rls_user2 | great manga
+ 10 | 33 | 1 | regress_rls_user2 | hoge
(9 rows)
SELECT * FROM category;
@@ -412,20 +412,20 @@ SELECT * FROM category;
(4 rows)
-- RLS policy does not apply to table owner when RLS enabled.
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
SET row_security TO ON;
SELECT * FROM document;
did | cid | dlevel | dauthor | dtitle
-----+-----+--------+-------------------+-----------------------
- 1 | 11 | 1 | rls_regress_user1 | my first novel
- 2 | 11 | 2 | rls_regress_user1 | my second novel
- 3 | 22 | 2 | rls_regress_user1 | my science fiction
- 4 | 44 | 1 | rls_regress_user1 | my first manga
- 5 | 44 | 2 | rls_regress_user1 | my second manga
- 6 | 22 | 1 | rls_regress_user2 | great science fiction
- 7 | 33 | 2 | rls_regress_user2 | great technology book
- 8 | 44 | 1 | rls_regress_user2 | great manga
- 10 | 33 | 1 | rls_regress_user2 | hoge
+ 1 | 11 | 1 | regress_rls_user1 | my first novel
+ 2 | 11 | 2 | regress_rls_user1 | my second novel
+ 3 | 22 | 2 | regress_rls_user1 | my science fiction
+ 4 | 44 | 1 | regress_rls_user1 | my first manga
+ 5 | 44 | 2 | regress_rls_user1 | my second manga
+ 6 | 22 | 1 | regress_rls_user2 | great science fiction
+ 7 | 33 | 2 | regress_rls_user2 | great technology book
+ 8 | 44 | 1 | regress_rls_user2 | great manga
+ 10 | 33 | 1 | regress_rls_user2 | hoge
(9 rows)
SELECT * FROM category;
@@ -438,20 +438,20 @@ SELECT * FROM category;
(4 rows)
-- RLS policy does not apply to table owner when RLS disabled.
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
SET row_security TO OFF;
SELECT * FROM document;
did | cid | dlevel | dauthor | dtitle
-----+-----+--------+-------------------+-----------------------
- 1 | 11 | 1 | rls_regress_user1 | my first novel
- 2 | 11 | 2 | rls_regress_user1 | my second novel
- 3 | 22 | 2 | rls_regress_user1 | my science fiction
- 4 | 44 | 1 | rls_regress_user1 | my first manga
- 5 | 44 | 2 | rls_regress_user1 | my second manga
- 6 | 22 | 1 | rls_regress_user2 | great science fiction
- 7 | 33 | 2 | rls_regress_user2 | great technology book
- 8 | 44 | 1 | rls_regress_user2 | great manga
- 10 | 33 | 1 | rls_regress_user2 | hoge
+ 1 | 11 | 1 | regress_rls_user1 | my first novel
+ 2 | 11 | 2 | regress_rls_user1 | my second novel
+ 3 | 22 | 2 | regress_rls_user1 | my science fiction
+ 4 | 44 | 1 | regress_rls_user1 | my first manga
+ 5 | 44 | 2 | regress_rls_user1 | my second manga
+ 6 | 22 | 1 | regress_rls_user2 | great science fiction
+ 7 | 33 | 2 | regress_rls_user2 | great technology book
+ 8 | 44 | 1 | regress_rls_user2 | great manga
+ 10 | 33 | 1 | regress_rls_user2 | hoge
(9 rows)
SELECT * FROM category;
@@ -466,7 +466,7 @@ SELECT * FROM category;
--
-- Table inheritance and RLS policy
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
SET row_security TO ON;
CREATE TABLE t1 (a int, junk1 text, b text) WITH OIDS;
ALTER TABLE t1 DROP COLUMN junk1; -- just a disturbing factor
@@ -483,7 +483,7 @@ CREATE POLICY p1 ON t1 FOR ALL TO PUBLIC USING (a % 2 = 0); -- be even number
CREATE POLICY p2 ON t2 FOR ALL TO PUBLIC USING (a % 2 = 1); -- be odd number
ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
ALTER TABLE t2 ENABLE ROW LEVEL SECURITY;
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM t1;
a | b
---+-----
@@ -703,7 +703,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
(7 rows)
-- non-superuser with bypass privilege can bypass RLS policy when disabled
-SET SESSION AUTHORIZATION rls_regress_exempt_user;
+SET SESSION AUTHORIZATION regress_rls_exempt_user;
SET row_security TO OFF;
SELECT * FROM t1 WHERE f_leak(b);
NOTICE: f_leak => aaa
@@ -745,7 +745,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
(7 rows)
----- Dependencies -----
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
SET row_security TO ON;
CREATE TABLE dependee (x integer, y integer);
CREATE TABLE dependent (x integer, y integer);
@@ -768,76 +768,76 @@ EXPLAIN (COSTS OFF) SELECT * FROM dependent; -- After drop, should be unqualifie
--
-- Simple recursion
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE TABLE rec1 (x integer, y integer);
CREATE POLICY r1 ON rec1 USING (x = (SELECT r.x FROM rec1 r WHERE y = r.y));
ALTER TABLE rec1 ENABLE ROW LEVEL SECURITY;
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM rec1; -- fail, direct recursion
ERROR: infinite recursion detected in policy for relation "rec1"
--
-- Mutual recursion
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE TABLE rec2 (a integer, b integer);
ALTER POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2 WHERE b = y));
CREATE POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1 WHERE y = b));
ALTER TABLE rec2 ENABLE ROW LEVEL SECURITY;
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM rec1; -- fail, mutual recursion
ERROR: infinite recursion detected in policy for relation "rec1"
--
-- Mutual recursion via views
--
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
CREATE VIEW rec1v AS SELECT * FROM rec1;
CREATE VIEW rec2v AS SELECT * FROM rec2;
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
ALTER POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2v WHERE b = y));
ALTER POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1v WHERE y = b));
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM rec1; -- fail, mutual recursion via views
ERROR: infinite recursion detected in policy for relation "rec1"
--
-- Mutual recursion via .s.b views
--
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
-- Suppress NOTICE messages when doing a cascaded drop.
SET client_min_messages TO 'warning';
DROP VIEW rec1v, rec2v CASCADE;
RESET client_min_messages;
CREATE VIEW rec1v WITH (security_barrier) AS SELECT * FROM rec1;
CREATE VIEW rec2v WITH (security_barrier) AS SELECT * FROM rec2;
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2v WHERE b = y));
CREATE POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1v WHERE y = b));
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM rec1; -- fail, mutual recursion via s.b. views
ERROR: infinite recursion detected in policy for relation "rec1"
--
-- recursive RLS and VIEWs in policy
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE TABLE s1 (a int, b text);
INSERT INTO s1 (SELECT x, md5(x::text) FROM generate_series(-10,10) x);
CREATE TABLE s2 (x int, y text);
INSERT INTO s2 (SELECT x, md5(x::text) FROM generate_series(-6,6) x);
-GRANT SELECT ON s1, s2 TO rls_regress_user1;
+GRANT SELECT ON s1, s2 TO regress_rls_user1;
CREATE POLICY p1 ON s1 USING (a in (select x from s2 where y like '%2f%'));
CREATE POLICY p2 ON s2 USING (x in (select a from s1 where b like '%22%'));
CREATE POLICY p3 ON s1 FOR INSERT WITH CHECK (a = (SELECT a FROM s1));
ALTER TABLE s1 ENABLE ROW LEVEL SECURITY;
ALTER TABLE s2 ENABLE ROW LEVEL SECURITY;
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
CREATE VIEW v2 AS SELECT * FROM s2 WHERE y like '%af%';
SELECT * FROM s1 WHERE f_leak(b); -- fail (infinite recursion)
ERROR: infinite recursion detected in policy for relation "s1"
INSERT INTO s1 VALUES (1, 'foo'); -- fail (infinite recursion)
ERROR: infinite recursion detected in policy for relation "s1"
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
DROP POLICY p3 on s1;
ALTER POLICY p2 ON s2 USING (x % 2 = 0);
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM s1 WHERE f_leak(b); -- OK
NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
@@ -864,9 +864,9 @@ EXPLAIN (COSTS OFF) SELECT * FROM only s1 WHERE f_leak(b);
Filter: ((x % 2) = 0)
(12 rows)
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
ALTER POLICY p1 ON s1 USING (a in (select x from v2)); -- using VIEW in RLS policy
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM s1 WHERE f_leak(b); -- OK
NOTICE: f_leak => 0267aaf632e87a63288a08331f22c7c3
NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
@@ -921,12 +921,12 @@ EXPLAIN (COSTS OFF) SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like
Filter: ((x % 2) = 0)
(15 rows)
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
ALTER POLICY p2 ON s2 USING (x in (select a from s1 where b like '%d2%'));
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM s1 WHERE f_leak(b); -- fail (infinite recursion via view)
ERROR: infinite recursion detected in policy for relation "s1"
--- prepared statement with rls_regress_user0 privilege
+-- prepared statement with regress_rls_user0 privilege
PREPARE p1(int) AS SELECT * FROM t1 WHERE a <= $1;
EXECUTE p1(2);
a | b
@@ -1036,7 +1036,7 @@ EXPLAIN (COSTS OFF) EXECUTE p2(2);
(7 rows)
-- also, case when privilege switch from superuser
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SET row_security TO ON;
EXECUTE p2(2);
a | b
@@ -1061,7 +1061,7 @@ EXPLAIN (COSTS OFF) EXECUTE p2(2);
--
-- UPDATE / DELETE and Row-level security
--
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
EXPLAIN (COSTS OFF) UPDATE t1 SET b = b || b WHERE f_leak(b);
QUERY PLAN
-------------------------------------------
@@ -1358,7 +1358,7 @@ SELECT * FROM t1 ORDER BY a,b;
4 | defdef
(11 rows)
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SET row_security TO ON;
EXPLAIN (COSTS OFF) DELETE FROM only t1 WHERE f_leak(b);
QUERY PLAN
@@ -1418,16 +1418,16 @@ NOTICE: f_leak => yyyyyy
--
-- S.b. view on top of Row-level security
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE TABLE b1 (a int, b text);
INSERT INTO b1 (SELECT x, md5(x::text) FROM generate_series(-10,10) x);
CREATE POLICY p1 ON b1 USING (a % 2 = 0);
ALTER TABLE b1 ENABLE ROW LEVEL SECURITY;
-GRANT ALL ON b1 TO rls_regress_user1;
-SET SESSION AUTHORIZATION rls_regress_user1;
+GRANT ALL ON b1 TO regress_rls_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
CREATE VIEW bv1 WITH (security_barrier) AS SELECT * FROM b1 WHERE a > 0 WITH CHECK OPTION;
-GRANT ALL ON bv1 TO rls_regress_user2;
-SET SESSION AUTHORIZATION rls_regress_user2;
+GRANT ALL ON bv1 TO regress_rls_user2;
+SET SESSION AUTHORIZATION regress_rls_user2;
EXPLAIN (COSTS OFF) SELECT * FROM bv1 WHERE f_leak(b);
QUERY PLAN
---------------------------------------------
@@ -1485,7 +1485,7 @@ EXPLAIN (COSTS OFF) DELETE FROM bv1 WHERE a = 6 AND f_leak(b);
DELETE FROM bv1 WHERE a = 6 AND f_leak(b);
NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
SELECT * FROM b1;
a | b
-----+----------------------------------
@@ -1515,24 +1515,24 @@ SELECT * FROM b1;
--
-- INSERT ... ON CONFLICT DO UPDATE and Row-level security
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
DROP POLICY p1 ON document;
CREATE POLICY p1 ON document FOR SELECT USING (true);
CREATE POLICY p2 ON document FOR INSERT WITH CHECK (dauthor = current_user);
CREATE POLICY p3 ON document FOR UPDATE
USING (cid = (SELECT cid from category WHERE cname = 'novel'))
WITH CHECK (dauthor = current_user);
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
-- Exists...
SELECT * FROM document WHERE did = 2;
did | cid | dlevel | dauthor | dtitle
-----+-----+--------+-------------------+-----------------
- 2 | 11 | 2 | rls_regress_user1 | my second novel
+ 2 | 11 | 2 | regress_rls_user1 | my second novel
(1 row)
-- ...so violates actual WITH CHECK OPTION within UPDATE (not INSERT, since
-- alternative UPDATE path happens to be taken):
-INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user2', 'my first novel')
+INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_user2', 'my first novel')
ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, dauthor = EXCLUDED.dauthor;
ERROR: new row violates row-level security policy for table "document"
-- Violates USING qual for UPDATE policy p3.
@@ -1540,66 +1540,66 @@ ERROR: new row violates row-level security policy for table "document"
-- UPDATE path is taken, but UPDATE fails purely because *existing* row to be
-- updated is not a "novel"/cid 11 (row is not leaked, even though we have
-- SELECT privileges sufficient to see the row in this instance):
-INSERT INTO document VALUES (33, 22, 1, 'rls_regress_user1', 'okay science fiction'); -- preparation for next statement
-INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'Some novel, replaces sci-fi') -- takes UPDATE path
+INSERT INTO document VALUES (33, 22, 1, 'regress_rls_user1', 'okay science fiction'); -- preparation for next statement
+INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_user1', 'Some novel, replaces sci-fi') -- takes UPDATE path
ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle;
ERROR: new row violates row-level security policy (USING expression) for table "document"
-- Fine (we UPDATE, since INSERT WCOs and UPDATE security barrier quals + WCOs
-- not violated):
-INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'my first novel')
+INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_user1', 'my first novel')
ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
did | cid | dlevel | dauthor | dtitle
-----+-----+--------+-------------------+----------------
- 2 | 11 | 2 | rls_regress_user1 | my first novel
+ 2 | 11 | 2 | regress_rls_user1 | my first novel
(1 row)
-- Fine (we INSERT, so "cid = 33" ("technology") isn't evaluated):
-INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'some technology novel')
+INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_user1', 'some technology novel')
ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *;
did | cid | dlevel | dauthor | dtitle
-----+-----+--------+-------------------+-----------------------
- 78 | 11 | 1 | rls_regress_user1 | some technology novel
+ 78 | 11 | 1 | regress_rls_user1 | some technology novel
(1 row)
-- Fine (same query, but we UPDATE, so "cid = 33", ("technology") is not the
-- case in respect of *existing* tuple):
-INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'some technology novel')
+INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_user1', 'some technology novel')
ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *;
did | cid | dlevel | dauthor | dtitle
-----+-----+--------+-------------------+-----------------------
- 78 | 33 | 1 | rls_regress_user1 | some technology novel
+ 78 | 33 | 1 | regress_rls_user1 | some technology novel
(1 row)
-- Same query a third time, but now fails due to existing tuple finally not
-- passing quals:
-INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'some technology novel')
+INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_user1', 'some technology novel')
ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *;
ERROR: new row violates row-level security policy (USING expression) for table "document"
-- Don't fail just because INSERT doesn't satisfy WITH CHECK option that
-- originated as a barrier/USING() qual from the UPDATE. Note that the UPDATE
-- path *isn't* taken, and so UPDATE-related policy does not apply:
-INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'technology book, can only insert')
+INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'regress_rls_user1', 'technology book, can only insert')
ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
did | cid | dlevel | dauthor | dtitle
-----+-----+--------+-------------------+----------------------------------
- 79 | 33 | 1 | rls_regress_user1 | technology book, can only insert
+ 79 | 33 | 1 | regress_rls_user1 | technology book, can only insert
(1 row)
-- But this time, the same statement fails, because the UPDATE path is taken,
-- and updating the row just inserted falls afoul of security barrier qual
-- (enforced as WCO) -- what we might have updated target tuple to is
-- irrelevant, in fact.
-INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'technology book, can only insert')
+INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'regress_rls_user1', 'technology book, can only insert')
ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
ERROR: new row violates row-level security policy (USING expression) for table "document"
-- Test default USING qual enforced as WCO
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
DROP POLICY p1 ON document;
DROP POLICY p2 ON document;
DROP POLICY p3 ON document;
CREATE POLICY p3_with_default ON document FOR UPDATE
USING (cid = (SELECT cid from category WHERE cname = 'novel'));
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
-- Just because WCO-style enforcement of USING quals occurs with
-- existing/target tuple does not mean that the implementation can be allowed
-- to fail to also enforce this qual against the final tuple appended to
@@ -1611,17 +1611,17 @@ SET SESSION AUTHORIZATION rls_regress_user1;
-- a USING qual for the purposes of RLS in general, as opposed to an explicit
-- USING qual that is ordinarily a security barrier. We leave it up to the
-- UPDATE to make this fail:
-INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'technology book, can only insert')
+INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'regress_rls_user1', 'technology book, can only insert')
ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
ERROR: new row violates row-level security policy for table "document"
-- UPDATE path is taken here. Existing tuple passes, since it's cid
-- corresponds to "novel", but default USING qual is enforced against
-- post-UPDATE tuple too (as always when updating with a policy that lacks an
-- explicit WCO), and so this fails:
-INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'my first novel')
+INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'technology'), 1, 'regress_rls_user1', 'my first novel')
ON CONFLICT (did) DO UPDATE SET cid = EXCLUDED.cid, dtitle = EXCLUDED.dtitle RETURNING *;
ERROR: new row violates row-level security policy for table "document"
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
DROP POLICY p3_with_default ON document;
--
-- Test ALL policies with ON CONFLICT DO UPDATE (much the same as existing UPDATE
@@ -1630,37 +1630,37 @@ DROP POLICY p3_with_default ON document;
CREATE POLICY p3_with_all ON document FOR ALL
USING (cid = (SELECT cid from category WHERE cname = 'novel'))
WITH CHECK (dauthor = current_user);
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
-- Fails, since ALL WCO is enforced in insert path:
-INSERT INTO document VALUES (80, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user2', 'my first novel')
+INSERT INTO document VALUES (80, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_user2', 'my first novel')
ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33;
ERROR: new row violates row-level security policy for table "document"
-- Fails, since ALL policy USING qual is enforced (existing, target tuple is in
-- violation, since it has the "manga" cid):
-INSERT INTO document VALUES (4, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'my first novel')
+INSERT INTO document VALUES (4, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_user1', 'my first novel')
ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle;
ERROR: new row violates row-level security policy (USING expression) for table "document"
-- Fails, since ALL WCO are enforced:
-INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'my first novel')
- ON CONFLICT (did) DO UPDATE SET dauthor = 'rls_regress_user2';
+INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_user1', 'my first novel')
+ ON CONFLICT (did) DO UPDATE SET dauthor = 'regress_rls_user2';
ERROR: new row violates row-level security policy for table "document"
--
-- ROLE/GROUP
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE TABLE z1 (a int, b text);
CREATE TABLE z2 (a int, b text);
-GRANT SELECT ON z1,z2 TO rls_regress_group1, rls_regress_group2,
- rls_regress_user1, rls_regress_user2;
+GRANT SELECT ON z1,z2 TO regress_rls_group1, regress_rls_group2,
+ regress_rls_user1, regress_rls_user2;
INSERT INTO z1 VALUES
(1, 'aaa'),
(2, 'bbb'),
(3, 'ccc'),
(4, 'ddd');
-CREATE POLICY p1 ON z1 TO rls_regress_group1 USING (a % 2 = 0);
-CREATE POLICY p2 ON z1 TO rls_regress_group2 USING (a % 2 = 1);
+CREATE POLICY p1 ON z1 TO regress_rls_group1 USING (a % 2 = 0);
+CREATE POLICY p2 ON z1 TO regress_rls_group2 USING (a % 2 = 1);
ALTER TABLE z1 ENABLE ROW LEVEL SECURITY;
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM z1 WHERE f_leak(b);
NOTICE: f_leak => bbb
NOTICE: f_leak => ddd
@@ -1719,7 +1719,7 @@ EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
Filter: ((a % 2) = 0)
(9 rows)
-SET ROLE rls_regress_group1;
+SET ROLE regress_rls_group1;
SELECT * FROM z1 WHERE f_leak(b);
NOTICE: f_leak => bbb
NOTICE: f_leak => ddd
@@ -1775,7 +1775,7 @@ EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
Filter: ((a % 2) = 0)
(9 rows)
-SET SESSION AUTHORIZATION rls_regress_user2;
+SET SESSION AUTHORIZATION regress_rls_user2;
SELECT * FROM z1 WHERE f_leak(b);
NOTICE: f_leak => aaa
NOTICE: f_leak => ccc
@@ -1831,7 +1831,7 @@ EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
Filter: ((a % 2) = 1)
(9 rows)
-SET ROLE rls_regress_group2;
+SET ROLE regress_rls_group2;
SELECT * FROM z1 WHERE f_leak(b);
NOTICE: f_leak => aaa
NOTICE: f_leak => ccc
@@ -1891,11 +1891,11 @@ EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
-- Views should follow policy for view owner.
--
-- View and Table owner are the same.
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE VIEW rls_view AS SELECT * FROM z1 WHERE f_leak(b);
-GRANT SELECT ON rls_view TO rls_regress_user1;
+GRANT SELECT ON rls_view TO regress_rls_user1;
-- Query as role that is not owner of view or table. Should return all records.
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM rls_view;
NOTICE: f_leak => aaa
NOTICE: f_leak => bbb
@@ -1917,7 +1917,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
(2 rows)
-- Query as view/table owner. Should return all records.
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
SELECT * FROM rls_view;
NOTICE: f_leak => aaa
NOTICE: f_leak => bbb
@@ -1940,12 +1940,12 @@ EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
DROP VIEW rls_view;
-- View and Table owners are different.
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
CREATE VIEW rls_view AS SELECT * FROM z1 WHERE f_leak(b);
-GRANT SELECT ON rls_view TO rls_regress_user0;
+GRANT SELECT ON rls_view TO regress_rls_user0;
-- Query as role that is not owner of view but is owner of table.
-- Should return records based on view owner policies.
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
SELECT * FROM rls_view;
NOTICE: f_leak => bbb
NOTICE: f_leak => ddd
@@ -1966,7 +1966,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
-- Query as role that is not owner of table but is owner of view.
-- Should return records based on view owner policies.
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM rls_view;
NOTICE: f_leak => bbb
NOTICE: f_leak => ddd
@@ -1986,14 +1986,14 @@ EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
(4 rows)
-- Query as role that is not the owner of the table or view without permissions.
-SET SESSION AUTHORIZATION rls_regress_user2;
+SET SESSION AUTHORIZATION regress_rls_user2;
SELECT * FROM rls_view; --fail - permission denied.
ERROR: permission denied for relation rls_view
EXPLAIN (COSTS OFF) SELECT * FROM rls_view; --fail - permission denied.
ERROR: permission denied for relation rls_view
-- Query as role that is not the owner of the table or view with permissions.
-SET SESSION AUTHORIZATION rls_regress_user1;
-GRANT SELECT ON rls_view TO rls_regress_user2;
+SET SESSION AUTHORIZATION regress_rls_user1;
+GRANT SELECT ON rls_view TO regress_rls_user2;
SELECT * FROM rls_view;
NOTICE: f_leak => bbb
NOTICE: f_leak => ddd
@@ -2012,30 +2012,30 @@ EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
Filter: ((a % 2) = 0)
(4 rows)
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
DROP VIEW rls_view;
--
-- Command specific
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE TABLE x1 (a int, b text, c text);
GRANT ALL ON x1 TO PUBLIC;
INSERT INTO x1 VALUES
- (1, 'abc', 'rls_regress_user1'),
- (2, 'bcd', 'rls_regress_user1'),
- (3, 'cde', 'rls_regress_user2'),
- (4, 'def', 'rls_regress_user2'),
- (5, 'efg', 'rls_regress_user1'),
- (6, 'fgh', 'rls_regress_user1'),
- (7, 'fgh', 'rls_regress_user2'),
- (8, 'fgh', 'rls_regress_user2');
+ (1, 'abc', 'regress_rls_user1'),
+ (2, 'bcd', 'regress_rls_user1'),
+ (3, 'cde', 'regress_rls_user2'),
+ (4, 'def', 'regress_rls_user2'),
+ (5, 'efg', 'regress_rls_user1'),
+ (6, 'fgh', 'regress_rls_user1'),
+ (7, 'fgh', 'regress_rls_user2'),
+ (8, 'fgh', 'regress_rls_user2');
CREATE POLICY p0 ON x1 FOR ALL USING (c = current_user);
CREATE POLICY p1 ON x1 FOR SELECT USING (a % 2 = 0);
CREATE POLICY p2 ON x1 FOR INSERT WITH CHECK (a % 2 = 1);
CREATE POLICY p3 ON x1 FOR UPDATE USING (a % 2 = 0);
CREATE POLICY p4 ON x1 FOR DELETE USING (a < 8);
ALTER TABLE x1 ENABLE ROW LEVEL SECURITY;
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM x1 WHERE f_leak(b) ORDER BY a ASC;
NOTICE: f_leak => abc
NOTICE: f_leak => bcd
@@ -2045,12 +2045,12 @@ NOTICE: f_leak => fgh
NOTICE: f_leak => fgh
a | b | c
---+-----+-------------------
- 1 | abc | rls_regress_user1
- 2 | bcd | rls_regress_user1
- 4 | def | rls_regress_user2
- 5 | efg | rls_regress_user1
- 6 | fgh | rls_regress_user1
- 8 | fgh | rls_regress_user2
+ 1 | abc | regress_rls_user1
+ 2 | bcd | regress_rls_user1
+ 4 | def | regress_rls_user2
+ 5 | efg | regress_rls_user1
+ 6 | fgh | regress_rls_user1
+ 8 | fgh | regress_rls_user2
(6 rows)
UPDATE x1 SET b = b || '_updt' WHERE f_leak(b) RETURNING *;
@@ -2062,15 +2062,15 @@ NOTICE: f_leak => fgh
NOTICE: f_leak => fgh
a | b | c
---+----------+-------------------
- 1 | abc_updt | rls_regress_user1
- 2 | bcd_updt | rls_regress_user1
- 4 | def_updt | rls_regress_user2
- 5 | efg_updt | rls_regress_user1
- 6 | fgh_updt | rls_regress_user1
- 8 | fgh_updt | rls_regress_user2
+ 1 | abc_updt | regress_rls_user1
+ 2 | bcd_updt | regress_rls_user1
+ 4 | def_updt | regress_rls_user2
+ 5 | efg_updt | regress_rls_user1
+ 6 | fgh_updt | regress_rls_user1
+ 8 | fgh_updt | regress_rls_user2
(6 rows)
-SET SESSION AUTHORIZATION rls_regress_user2;
+SET SESSION AUTHORIZATION regress_rls_user2;
SELECT * FROM x1 WHERE f_leak(b) ORDER BY a ASC;
NOTICE: f_leak => cde
NOTICE: f_leak => fgh
@@ -2080,12 +2080,12 @@ NOTICE: f_leak => fgh_updt
NOTICE: f_leak => fgh_updt
a | b | c
---+----------+-------------------
- 2 | bcd_updt | rls_regress_user1
- 3 | cde | rls_regress_user2
- 4 | def_updt | rls_regress_user2
- 6 | fgh_updt | rls_regress_user1
- 7 | fgh | rls_regress_user2
- 8 | fgh_updt | rls_regress_user2
+ 2 | bcd_updt | regress_rls_user1
+ 3 | cde | regress_rls_user2
+ 4 | def_updt | regress_rls_user2
+ 6 | fgh_updt | regress_rls_user1
+ 7 | fgh | regress_rls_user2
+ 8 | fgh_updt | regress_rls_user2
(6 rows)
UPDATE x1 SET b = b || '_updt' WHERE f_leak(b) RETURNING *;
@@ -2097,12 +2097,12 @@ NOTICE: f_leak => fgh_updt
NOTICE: f_leak => fgh_updt
a | b | c
---+---------------+-------------------
- 3 | cde_updt | rls_regress_user2
- 7 | fgh_updt | rls_regress_user2
- 2 | bcd_updt_updt | rls_regress_user1
- 4 | def_updt_updt | rls_regress_user2
- 6 | fgh_updt_updt | rls_regress_user1
- 8 | fgh_updt_updt | rls_regress_user2
+ 3 | cde_updt | regress_rls_user2
+ 7 | fgh_updt | regress_rls_user2
+ 2 | bcd_updt_updt | regress_rls_user1
+ 4 | def_updt_updt | regress_rls_user2
+ 6 | fgh_updt_updt | regress_rls_user1
+ 8 | fgh_updt_updt | regress_rls_user2
(6 rows)
DELETE FROM x1 WHERE f_leak(b) RETURNING *;
@@ -2114,21 +2114,21 @@ NOTICE: f_leak => fgh_updt_updt
NOTICE: f_leak => fgh_updt_updt
a | b | c
---+---------------+-------------------
- 3 | cde_updt | rls_regress_user2
- 7 | fgh_updt | rls_regress_user2
- 2 | bcd_updt_updt | rls_regress_user1
- 4 | def_updt_updt | rls_regress_user2
- 6 | fgh_updt_updt | rls_regress_user1
- 8 | fgh_updt_updt | rls_regress_user2
+ 3 | cde_updt | regress_rls_user2
+ 7 | fgh_updt | regress_rls_user2
+ 2 | bcd_updt_updt | regress_rls_user1
+ 4 | def_updt_updt | regress_rls_user2
+ 6 | fgh_updt_updt | regress_rls_user1
+ 8 | fgh_updt_updt | regress_rls_user2
(6 rows)
--
-- Duplicate Policy Names
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE TABLE y1 (a int, b text);
CREATE TABLE y2 (a int, b text);
-GRANT ALL ON y1, y2 TO rls_regress_user1;
+GRANT ALL ON y1, y2 TO regress_rls_user1;
CREATE POLICY p1 ON y1 FOR ALL USING (a % 2 = 0);
CREATE POLICY p2 ON y1 FOR SELECT USING (a > 2);
CREATE POLICY p1 ON y1 FOR SELECT USING (a % 2 = 1); --fail
@@ -2140,7 +2140,7 @@ ALTER TABLE y2 ENABLE ROW LEVEL SECURITY;
-- Expression structure with SBV
--
-- Create view as table owner. RLS should NOT be applied.
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE VIEW rls_sbv WITH (security_barrier) AS
SELECT * FROM y1 WHERE f_leak(b);
EXPLAIN (COSTS OFF) SELECT * FROM rls_sbv WHERE (a = 1);
@@ -2152,7 +2152,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM rls_sbv WHERE (a = 1);
DROP VIEW rls_sbv;
-- Create view as role that does not own table. RLS should be applied.
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
CREATE VIEW rls_sbv WITH (security_barrier) AS
SELECT * FROM y1 WHERE f_leak(b);
EXPLAIN (COSTS OFF) SELECT * FROM rls_sbv WHERE (a = 1);
@@ -2168,11 +2168,11 @@ DROP VIEW rls_sbv;
--
-- Expression structure
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
INSERT INTO y2 (SELECT x, md5(x::text) FROM generate_series(0,20) x);
CREATE POLICY p2 ON y2 USING (a % 3 = 0);
CREATE POLICY p3 ON y2 USING (a % 4 = 0);
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM y2 WHERE f_leak(b);
NOTICE: f_leak => cfcd208495d565ef66e7dff9f98764da
NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
@@ -2330,12 +2330,12 @@ SET client_min_messages TO 'warning';
DROP TABLE t1 CASCADE;
RESET client_min_messages;
CREATE TABLE t1 (a integer);
-GRANT SELECT ON t1 TO rls_regress_user1, rls_regress_user2;
-CREATE POLICY p1 ON t1 TO rls_regress_user1 USING ((a % 2) = 0);
-CREATE POLICY p2 ON t1 TO rls_regress_user2 USING ((a % 4) = 0);
+GRANT SELECT ON t1 TO regress_rls_user1, regress_rls_user2;
+CREATE POLICY p1 ON t1 TO regress_rls_user1 USING ((a % 2) = 0);
+CREATE POLICY p2 ON t1 TO regress_rls_user2 USING ((a % 4) = 0);
ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
--- Prepare as rls_regress_user1
-SET ROLE rls_regress_user1;
+-- Prepare as regress_rls_user1
+SET ROLE regress_rls_user1;
PREPARE role_inval AS SELECT * FROM t1;
-- Check plan
EXPLAIN (COSTS OFF) EXECUTE role_inval;
@@ -2345,8 +2345,8 @@ EXPLAIN (COSTS OFF) EXECUTE role_inval;
Filter: ((a % 2) = 0)
(2 rows)
--- Change to rls_regress_user2
-SET ROLE rls_regress_user2;
+-- Change to regress_rls_user2
+SET ROLE regress_rls_user2;
-- Check plan- should be different
EXPLAIN (COSTS OFF) EXECUTE role_inval;
QUERY PLAN
@@ -2355,8 +2355,8 @@ EXPLAIN (COSTS OFF) EXECUTE role_inval;
Filter: ((a % 4) = 0)
(2 rows)
--- Change back to rls_regress_user1
-SET ROLE rls_regress_user1;
+-- Change back to regress_rls_user1
+SET ROLE regress_rls_user1;
-- Check plan- should be back to original
EXPLAIN (COSTS OFF) EXECUTE role_inval;
QUERY PLAN
@@ -2373,9 +2373,9 @@ DROP TABLE t1 CASCADE;
CREATE TABLE t1 (a integer, b text);
CREATE POLICY p1 ON t1 USING (a % 2 = 0);
ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
-GRANT ALL ON t1 TO rls_regress_user1;
+GRANT ALL ON t1 TO regress_rls_user1;
INSERT INTO t1 (SELECT x, md5(x::text) FROM generate_series(0,20) x);
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
WITH cte1 AS (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1;
NOTICE: f_leak => cfcd208495d565ef66e7dff9f98764da
NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
@@ -2468,7 +2468,7 @@ SELECT polname, relname
--
-- Check INSERT SELECT
--
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
CREATE TABLE t2 (a integer, b text);
INSERT INTO t2 (SELECT * FROM t1);
EXPLAIN (COSTS OFF) INSERT INTO t2 (SELECT * FROM t1);
@@ -2541,10 +2541,10 @@ SELECT * FROM t4;
--
-- RLS with JOIN
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE TABLE blog (id integer, author text, post text);
CREATE TABLE comment (blog_id integer, message text);
-GRANT ALL ON blog, comment TO rls_regress_user1;
+GRANT ALL ON blog, comment TO regress_rls_user1;
CREATE POLICY blog_1 ON blog USING (id % 2 = 0);
ALTER TABLE blog ENABLE ROW LEVEL SECURITY;
INSERT INTO blog VALUES
@@ -2560,7 +2560,7 @@ INSERT INTO comment VALUES
(5, 'what?'),
(4, 'insane!'),
(2, 'who did it?');
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
-- Check RLS JOIN with Non-RLS.
SELECT id, author, message FROM blog JOIN comment ON id = blog_id;
id | author | message
@@ -2577,10 +2577,10 @@ SELECT id, author, message FROM comment JOIN blog ON id = blog_id;
2 | bob | who did it?
(2 rows)
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE POLICY comment_1 ON comment USING (blog_id < 4);
ALTER TABLE comment ENABLE ROW LEVEL SECURITY;
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
-- Check RLS JOIN RLS
SELECT id, author, message FROM blog JOIN comment ON id = blog_id;
id | author | message
@@ -2594,14 +2594,14 @@ SELECT id, author, message FROM comment JOIN blog ON id = blog_id;
2 | bob | who did it?
(1 row)
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
DROP TABLE blog, comment;
--
-- Default Deny Policy
--
RESET SESSION AUTHORIZATION;
DROP POLICY p2 ON t1;
-ALTER TABLE t1 OWNER TO rls_regress_user0;
+ALTER TABLE t1 OWNER TO regress_rls_user0;
-- Check that default deny does not apply to superuser.
RESET SESSION AUTHORIZATION;
SELECT * FROM t1;
@@ -2638,7 +2638,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM t1;
(1 row)
-- Check that default deny does not apply to table owner.
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
SELECT * FROM t1;
a | b
----+----------------------------------
@@ -2673,7 +2673,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM t1;
(1 row)
-- Check that default deny applies to non-owner/non-superuser when RLS on.
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SET row_security TO ON;
SELECT * FROM t1;
a | b
@@ -2687,7 +2687,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM t1;
One-Time Filter: false
(2 rows)
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM t1;
a | b
---+---
@@ -2709,7 +2709,7 @@ ERROR: table "copy_t" does not exist
CREATE TABLE copy_t (a integer, b text);
CREATE POLICY p1 ON copy_t USING (a % 2 = 0);
ALTER TABLE copy_t ENABLE ROW LEVEL SECURITY;
-GRANT ALL ON copy_t TO rls_regress_user1, rls_regress_exempt_user;
+GRANT ALL ON copy_t TO regress_rls_user1, regress_rls_exempt_user;
INSERT INTO copy_t (SELECT x, md5(x::text) FROM generate_series(0,10) x);
-- Check COPY TO as Superuser/owner.
RESET SESSION AUTHORIZATION;
@@ -2740,7 +2740,7 @@ COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ',';
9,45c48cce2e2d7fbdea1afc51c7c6ad26
10,d3d9446802a44259755d38e6d163e820
-- Check COPY TO as user with permissions.
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SET row_security TO OFF;
COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - would be affected by RLS
ERROR: query would be affected by row-level security policy for table "copy_t"
@@ -2753,7 +2753,7 @@ COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
8,c9f0f895fb98ab9159f51fd0297e236d
10,d3d9446802a44259755d38e6d163e820
-- Check COPY TO as user with permissions and BYPASSRLS
-SET SESSION AUTHORIZATION rls_regress_exempt_user;
+SET SESSION AUTHORIZATION regress_rls_exempt_user;
SET row_security TO OFF;
COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
0,cfcd208495d565ef66e7dff9f98764da
@@ -2781,7 +2781,7 @@ COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
9,45c48cce2e2d7fbdea1afc51c7c6ad26
10,d3d9446802a44259755d38e6d163e820
-- Check COPY TO as user without permissions. SET row_security TO OFF;
-SET SESSION AUTHORIZATION rls_regress_user2;
+SET SESSION AUTHORIZATION regress_rls_user2;
SET row_security TO OFF;
COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - would be affected by RLS
ERROR: query would be affected by row-level security policy for table "copy_t"
@@ -2794,7 +2794,7 @@ SET row_security TO ON;
CREATE TABLE copy_rel_to (a integer, b text);
CREATE POLICY p1 ON copy_rel_to USING (a % 2 = 0);
ALTER TABLE copy_rel_to ENABLE ROW LEVEL SECURITY;
-GRANT ALL ON copy_rel_to TO rls_regress_user1, rls_regress_exempt_user;
+GRANT ALL ON copy_rel_to TO regress_rls_user1, regress_rls_exempt_user;
INSERT INTO copy_rel_to VALUES (1, md5('1'));
-- Check COPY TO as Superuser/owner.
RESET SESSION AUTHORIZATION;
@@ -2805,14 +2805,14 @@ SET row_security TO ON;
COPY copy_rel_to TO STDOUT WITH DELIMITER ',';
1,c4ca4238a0b923820dcc509a6f75849b
-- Check COPY TO as user with permissions.
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SET row_security TO OFF;
COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - would be affected by RLS
ERROR: query would be affected by row-level security policy for table "copy_rel_to"
SET row_security TO ON;
COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
-- Check COPY TO as user with permissions and BYPASSRLS
-SET SESSION AUTHORIZATION rls_regress_exempt_user;
+SET SESSION AUTHORIZATION regress_rls_exempt_user;
SET row_security TO OFF;
COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
1,c4ca4238a0b923820dcc509a6f75849b
@@ -2820,7 +2820,7 @@ SET row_security TO ON;
COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
1,c4ca4238a0b923820dcc509a6f75849b
-- Check COPY TO as user without permissions. SET row_security TO OFF;
-SET SESSION AUTHORIZATION rls_regress_user2;
+SET SESSION AUTHORIZATION regress_rls_user2;
SET row_security TO OFF;
COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - permission denied
ERROR: permission denied for relation copy_rel_to
@@ -2834,7 +2834,7 @@ COPY copy_t FROM STDIN; --ok
SET row_security TO ON;
COPY copy_t FROM STDIN; --ok
-- Check COPY FROM as user with permissions.
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SET row_security TO OFF;
COPY copy_t FROM STDIN; --fail - would be affected by RLS.
ERROR: query would be affected by row-level security policy for table "copy_t"
@@ -2843,11 +2843,11 @@ COPY copy_t FROM STDIN; --fail - COPY FROM not supported by RLS.
ERROR: COPY FROM not supported with row-level security
HINT: Use INSERT statements instead.
-- Check COPY FROM as user with permissions and BYPASSRLS
-SET SESSION AUTHORIZATION rls_regress_exempt_user;
+SET SESSION AUTHORIZATION regress_rls_exempt_user;
SET row_security TO ON;
COPY copy_t FROM STDIN; --ok
-- Check COPY FROM as user without permissions.
-SET SESSION AUTHORIZATION rls_regress_user2;
+SET SESSION AUTHORIZATION regress_rls_user2;
SET row_security TO OFF;
COPY copy_t FROM STDIN; --fail - permission denied.
ERROR: permission denied for relation copy_t
@@ -2858,25 +2858,25 @@ RESET SESSION AUTHORIZATION;
DROP TABLE copy_t;
DROP TABLE copy_rel_to CASCADE;
-- Check WHERE CURRENT OF
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE TABLE current_check (currentid int, payload text, rlsuser text);
GRANT ALL ON current_check TO PUBLIC;
INSERT INTO current_check VALUES
- (1, 'abc', 'rls_regress_user1'),
- (2, 'bcd', 'rls_regress_user1'),
- (3, 'cde', 'rls_regress_user1'),
- (4, 'def', 'rls_regress_user1');
+ (1, 'abc', 'regress_rls_user1'),
+ (2, 'bcd', 'regress_rls_user1'),
+ (3, 'cde', 'regress_rls_user1'),
+ (4, 'def', 'regress_rls_user1');
CREATE POLICY p1 ON current_check FOR SELECT USING (currentid % 2 = 0);
CREATE POLICY p2 ON current_check FOR DELETE USING (currentid = 4 AND rlsuser = current_user);
CREATE POLICY p3 ON current_check FOR UPDATE USING (currentid = 4) WITH CHECK (rlsuser = current_user);
ALTER TABLE current_check ENABLE ROW LEVEL SECURITY;
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
-- Can SELECT even rows
SELECT * FROM current_check;
currentid | payload | rlsuser
-----------+---------+-------------------
- 2 | bcd | rls_regress_user1
- 4 | def | rls_regress_user1
+ 2 | bcd | regress_rls_user1
+ 4 | def | regress_rls_user1
(2 rows)
-- Cannot UPDATE row 2
@@ -2892,7 +2892,7 @@ DECLARE current_check_cursor SCROLL CURSOR FOR SELECT * FROM current_check;
FETCH ABSOLUTE 1 FROM current_check_cursor;
currentid | payload | rlsuser
-----------+---------+-------------------
- 2 | bcd | rls_regress_user1
+ 2 | bcd | regress_rls_user1
(1 row)
-- Still cannot UPDATE row 2 through cursor
@@ -2905,20 +2905,20 @@ UPDATE current_check SET payload = payload || '_new' WHERE CURRENT OF current_ch
FETCH RELATIVE 1 FROM current_check_cursor;
currentid | payload | rlsuser
-----------+---------+-------------------
- 4 | def | rls_regress_user1
+ 4 | def | regress_rls_user1
(1 row)
UPDATE current_check SET payload = payload || '_new' WHERE CURRENT OF current_check_cursor RETURNING *;
currentid | payload | rlsuser
-----------+---------+-------------------
- 4 | def_new | rls_regress_user1
+ 4 | def_new | regress_rls_user1
(1 row)
SELECT * FROM current_check;
currentid | payload | rlsuser
-----------+---------+-------------------
- 2 | bcd | rls_regress_user1
- 4 | def_new | rls_regress_user1
+ 2 | bcd | regress_rls_user1
+ 4 | def_new | regress_rls_user1
(2 rows)
-- Plan should be a subquery TID scan
@@ -2939,7 +2939,7 @@ EXPLAIN (COSTS OFF) UPDATE current_check SET payload = payload WHERE CURRENT OF
FETCH ABSOLUTE 1 FROM current_check_cursor;
currentid | payload | rlsuser
-----------+---------+-------------------
- 2 | bcd | rls_regress_user1
+ 2 | bcd | regress_rls_user1
(1 row)
DELETE FROM current_check WHERE CURRENT OF current_check_cursor RETURNING *;
@@ -2950,19 +2950,19 @@ DELETE FROM current_check WHERE CURRENT OF current_check_cursor RETURNING *;
FETCH RELATIVE 1 FROM current_check_cursor;
currentid | payload | rlsuser
-----------+---------+-------------------
- 4 | def | rls_regress_user1
+ 4 | def | regress_rls_user1
(1 row)
DELETE FROM current_check WHERE CURRENT OF current_check_cursor RETURNING *;
currentid | payload | rlsuser
-----------+---------+-------------------
- 4 | def_new | rls_regress_user1
+ 4 | def_new | regress_rls_user1
(1 row)
SELECT * FROM current_check;
currentid | payload | rlsuser
-----------+---------+-------------------
- 2 | bcd | rls_regress_user1
+ 2 | bcd | regress_rls_user1
(1 row)
COMMIT;
@@ -2970,7 +2970,7 @@ COMMIT;
-- check pg_stats view filtering
--
SET row_security TO ON;
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
ANALYZE current_check;
-- Stats visible
SELECT row_security_active('current_check');
@@ -2986,10 +2986,10 @@ SELECT attname, most_common_vals FROM pg_stats
-----------+---------------------
currentid |
payload |
- rlsuser | {rls_regress_user1}
+ rlsuser | {regress_rls_user1}
(3 rows)
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
-- Stats not visible
SELECT row_security_active('current_check');
row_security_active
@@ -3011,14 +3011,14 @@ BEGIN;
CREATE TABLE coll_t (c) AS VALUES ('bar'::text);
CREATE POLICY coll_p ON coll_t USING (c < ('foo'::text COLLATE "C"));
ALTER TABLE coll_t ENABLE ROW LEVEL SECURITY;
-GRANT SELECT ON coll_t TO rls_regress_user0;
+GRANT SELECT ON coll_t TO regress_rls_user0;
SELECT (string_to_array(polqual, ':'))[7] AS inputcollid FROM pg_policy WHERE polrelid = 'coll_t'::regclass;
inputcollid
------------------
inputcollid 950
(1 row)
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
SELECT * FROM coll_t;
c
-----
@@ -3031,11 +3031,11 @@ ROLLBACK;
--
RESET SESSION AUTHORIZATION;
BEGIN;
-CREATE ROLE alice;
-CREATE ROLE bob;
+CREATE ROLE regress_rls_user4;
+CREATE ROLE regress_rls_user5;
CREATE TABLE tbl1 (c) AS VALUES ('bar'::text);
-GRANT SELECT ON TABLE tbl1 TO alice;
-CREATE POLICY P ON tbl1 TO alice, bob USING (true);
+GRANT SELECT ON TABLE tbl1 TO regress_rls_user4;
+CREATE POLICY P ON tbl1 TO regress_rls_user4, regress_rls_user5 USING (true);
SELECT refclassid::regclass, deptype
FROM pg_depend
WHERE classid = 'pg_policy'::regclass
@@ -3048,7 +3048,7 @@ SELECT refclassid::regclass, deptype
SELECT refclassid::regclass, deptype
FROM pg_shdepend
WHERE classid = 'pg_policy'::regclass
- AND refobjid IN ('alice'::regrole, 'bob'::regrole);
+ AND refobjid IN ('regress_rls_user4'::regrole, 'regress_rls_user5'::regrole);
refclassid | deptype
------------+---------
pg_authid | r
@@ -3056,29 +3056,29 @@ SELECT refclassid::regclass, deptype
(2 rows)
SAVEPOINT q;
-DROP ROLE alice; --fails due to dependency on POLICY p
-ERROR: role "alice" cannot be dropped because some objects depend on it
+DROP ROLE regress_rls_user4; --fails due to dependency on POLICY p
+ERROR: role "regress_rls_user4" cannot be dropped because some objects depend on it
DETAIL: target of policy p on table tbl1
privileges for table tbl1
ROLLBACK TO q;
-ALTER POLICY p ON tbl1 TO bob USING (true);
+ALTER POLICY p ON tbl1 TO regress_rls_user5 USING (true);
SAVEPOINT q;
-DROP ROLE alice; --fails due to dependency on GRANT SELECT
-ERROR: role "alice" cannot be dropped because some objects depend on it
+DROP ROLE regress_rls_user4; --fails due to dependency on GRANT SELECT
+ERROR: role "regress_rls_user4" cannot be dropped because some objects depend on it
DETAIL: privileges for table tbl1
ROLLBACK TO q;
-REVOKE ALL ON TABLE tbl1 FROM alice;
+REVOKE ALL ON TABLE tbl1 FROM regress_rls_user4;
SAVEPOINT q;
-DROP ROLE alice; --succeeds
+DROP ROLE regress_rls_user4; --succeeds
ROLLBACK TO q;
SAVEPOINT q;
-DROP ROLE bob; --fails due to dependency on POLICY p
-ERROR: role "bob" cannot be dropped because some objects depend on it
+DROP ROLE regress_rls_user5; --fails due to dependency on POLICY p
+ERROR: role "regress_rls_user5" cannot be dropped because some objects depend on it
DETAIL: target of policy p on table tbl1
ROLLBACK TO q;
DROP POLICY p ON tbl1;
SAVEPOINT q;
-DROP ROLE bob; -- succeeds
+DROP ROLE regress_rls_user5; -- succeeds
ROLLBACK TO q;
ROLLBACK; -- cleanup
--
@@ -3114,12 +3114,12 @@ ROLLBACK;
--
-- Non-target relations are only subject to SELECT policies
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE TABLE r1 (a int);
CREATE TABLE r2 (a int);
INSERT INTO r1 VALUES (10), (20);
INSERT INTO r2 VALUES (10), (20);
-GRANT ALL ON r1, r2 TO rls_regress_user1;
+GRANT ALL ON r1, r2 TO regress_rls_user1;
CREATE POLICY p1 ON r1 USING (true);
ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
CREATE POLICY p1 ON r2 FOR SELECT USING (true);
@@ -3127,7 +3127,7 @@ CREATE POLICY p2 ON r2 FOR INSERT WITH CHECK (false);
CREATE POLICY p3 ON r2 FOR UPDATE USING (false);
CREATE POLICY p4 ON r2 FOR DELETE USING (false);
ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM r1;
a
----
@@ -3191,13 +3191,13 @@ SELECT * FROM r2;
20
(2 rows)
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
DROP TABLE r1;
DROP TABLE r2;
--
-- FORCE ROW LEVEL SECURITY applies RLS to owners too
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
SET row_security = on;
CREATE TABLE r1 (a int);
INSERT INTO r1 VALUES (10), (20);
@@ -3242,7 +3242,7 @@ DROP TABLE r1;
--
-- FORCE ROW LEVEL SECURITY does not break RI
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
SET row_security = on;
CREATE TABLE r1 (a int PRIMARY KEY);
CREATE TABLE r2 (a int REFERENCES r1);
@@ -3332,7 +3332,7 @@ DROP TABLE r1;
-- Test INSERT+RETURNING applies SELECT policies as
-- WithCheckOptions (meaning an error is thrown)
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
SET row_security = on;
CREATE TABLE r1 (a int);
CREATE POLICY p1 ON r1 FOR SELECT USING (false);
@@ -3361,7 +3361,7 @@ DROP TABLE r1;
-- Test UPDATE+RETURNING applies SELECT policies as
-- WithCheckOptions (meaning an error is thrown)
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
SET row_security = on;
CREATE TABLE r1 (a int);
CREATE POLICY p1 ON r1 FOR SELECT USING (a < 20);
@@ -3397,8 +3397,8 @@ DROP TABLE r1;
RESET SESSION AUTHORIZATION;
CREATE TABLE dep1 (c1 int);
CREATE TABLE dep2 (c1 int);
-CREATE POLICY dep_p1 ON dep1 TO rls_regress_user1 USING (c1 > (select max(dep2.c1) from dep2));
-ALTER POLICY dep_p1 ON dep1 TO rls_regress_user1,rls_regress_user2;
+CREATE POLICY dep_p1 ON dep1 TO regress_rls_user1 USING (c1 > (select max(dep2.c1) from dep2));
+ALTER POLICY dep_p1 ON dep1 TO regress_rls_user1,regress_rls_user2;
-- Should return one
SELECT count(*) = 1 FROM pg_depend
WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1')
@@ -3412,7 +3412,7 @@ ALTER POLICY dep_p1 ON dep1 USING (true);
-- Should return one
SELECT count(*) = 1 FROM pg_shdepend
WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1')
- AND refobjid = (SELECT oid FROM pg_authid WHERE rolname = 'rls_regress_user1');
+ AND refobjid = (SELECT oid FROM pg_authid WHERE rolname = 'regress_rls_user1');
?column?
----------
t
@@ -3421,7 +3421,7 @@ SELECT count(*) = 1 FROM pg_shdepend
-- Should return one
SELECT count(*) = 1 FROM pg_shdepend
WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1')
- AND refobjid = (SELECT oid FROM pg_authid WHERE rolname = 'rls_regress_user2');
+ AND refobjid = (SELECT oid FROM pg_authid WHERE rolname = 'regress_rls_user2');
?column?
----------
t
@@ -3438,35 +3438,35 @@ SELECT count(*) = 0 FROM pg_depend
-- DROP OWNED BY testing
RESET SESSION AUTHORIZATION;
-CREATE ROLE dob_role1;
-CREATE ROLE dob_role2;
+CREATE ROLE regress_rls_dob_role1;
+CREATE ROLE regress_rls_dob_role2;
CREATE TABLE dob_t1 (c1 int);
-CREATE POLICY p1 ON dob_t1 TO dob_role1 USING (true);
-DROP OWNED BY dob_role1;
+CREATE POLICY p1 ON dob_t1 TO regress_rls_dob_role1 USING (true);
+DROP OWNED BY regress_rls_dob_role1;
DROP POLICY p1 ON dob_t1; -- should fail, already gone
ERROR: policy "p1" for table "dob_t1" does not exist
-CREATE POLICY p1 ON dob_t1 TO dob_role1,dob_role2 USING (true);
-DROP OWNED BY dob_role1;
+CREATE POLICY p1 ON dob_t1 TO regress_rls_dob_role1,regress_rls_dob_role2 USING (true);
+DROP OWNED BY regress_rls_dob_role1;
DROP POLICY p1 ON dob_t1; -- should succeed
-DROP USER dob_role1;
-DROP USER dob_role2;
+DROP USER regress_rls_dob_role1;
+DROP USER regress_rls_dob_role2;
--
-- Clean up objects
--
RESET SESSION AUTHORIZATION;
-- Suppress NOTICE messages when doing a cascaded drop.
SET client_min_messages TO 'warning';
-DROP SCHEMA rls_regress_schema CASCADE;
+DROP SCHEMA regress_rls_schema CASCADE;
RESET client_min_messages;
-DROP USER rls_regress_user0;
-DROP USER rls_regress_user1;
-DROP USER rls_regress_user2;
-DROP USER rls_regress_exempt_user;
-DROP ROLE rls_regress_group1;
-DROP ROLE rls_regress_group2;
+DROP USER regress_rls_user0;
+DROP USER regress_rls_user1;
+DROP USER regress_rls_user2;
+DROP USER regress_rls_exempt_user;
+DROP ROLE regress_rls_group1;
+DROP ROLE regress_rls_group2;
-- Arrange to have a few policies left over, for testing
-- pg_dump/pg_restore
-CREATE SCHEMA rls_regress_schema;
+CREATE SCHEMA regress_rls_schema;
CREATE TABLE rls_tbl (c1 int);
ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY;
CREATE POLICY p1 ON rls_tbl USING (c1 > 5);
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 18d0a38..38d60e2 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -7,31 +7,31 @@
-- Suppress NOTICE messages when users/groups don't exist
SET client_min_messages TO 'warning';
-DROP USER IF EXISTS rls_regress_user0;
-DROP USER IF EXISTS rls_regress_user1;
-DROP USER IF EXISTS rls_regress_user2;
-DROP USER IF EXISTS rls_regress_exempt_user;
-DROP ROLE IF EXISTS rls_regress_group1;
-DROP ROLE IF EXISTS rls_regress_group2;
+DROP USER IF EXISTS regress_rls_user0;
+DROP USER IF EXISTS regress_rls_user1;
+DROP USER IF EXISTS regress_rls_user2;
+DROP USER IF EXISTS regress_rls_exempt_user;
+DROP ROLE IF EXISTS regress_rls_group1;
+DROP ROLE IF EXISTS regress_rls_group2;
-DROP SCHEMA IF EXISTS rls_regress_schema CASCADE;
+DROP SCHEMA IF EXISTS regress_rls_schema CASCADE;
RESET client_min_messages;
-- initial setup
-CREATE USER rls_regress_user0;
-CREATE USER rls_regress_user1;
-CREATE USER rls_regress_user2;
-CREATE USER rls_regress_exempt_user BYPASSRLS;
-CREATE ROLE rls_regress_group1 NOLOGIN;
-CREATE ROLE rls_regress_group2 NOLOGIN;
+CREATE USER regress_rls_user0 NOLOGIN;
+CREATE USER regress_rls_user1 NOLOGIN;
+CREATE USER regress_rls_user2 NOLOGIN;
+CREATE USER regress_rls_exempt_user BYPASSRLS NOLOGIN;
+CREATE ROLE regress_rls_group1 NOLOGIN;
+CREATE ROLE regress_rls_group2 NOLOGIN;
-GRANT rls_regress_group1 TO rls_regress_user1;
-GRANT rls_regress_group2 TO rls_regress_user2;
+GRANT regress_rls_group1 TO regress_rls_user1;
+GRANT regress_rls_group2 TO regress_rls_user2;
-CREATE SCHEMA rls_regress_schema;
-GRANT ALL ON SCHEMA rls_regress_schema to public;
-SET search_path = rls_regress_schema;
+CREATE SCHEMA regress_rls_schema;
+GRANT ALL ON SCHEMA regress_rls_schema to public;
+SET search_path = regress_rls_schema;
-- setup of malicious function
CREATE OR REPLACE FUNCTION f_leak(text) RETURNS bool
@@ -41,17 +41,17 @@ GRANT EXECUTE ON FUNCTION f_leak(text) TO public;
-- BASIC Row-Level Security Scenario
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE TABLE uaccount (
pguser name primary key,
seclv int
);
GRANT SELECT ON uaccount TO public;
INSERT INTO uaccount VALUES
- ('rls_regress_user0', 99),
- ('rls_regress_user1', 1),
- ('rls_regress_user2', 2),
- ('rls_regress_user3', 3);
+ ('regress_rls_user0', 99),
+ ('regress_rls_user1', 1),
+ ('regress_rls_user2', 2),
+ ('regress_rls_user3', 3);
CREATE TABLE category (
cid int primary key,
@@ -73,14 +73,14 @@ CREATE TABLE document (
);
GRANT ALL ON document TO public;
INSERT INTO document VALUES
- ( 1, 11, 1, 'rls_regress_user1', 'my first novel'),
- ( 2, 11, 2, 'rls_regress_user1', 'my second novel'),
- ( 3, 22, 2, 'rls_regress_user1', 'my science fiction'),
- ( 4, 44, 1, 'rls_regress_user1', 'my first manga'),
- ( 5, 44, 2, 'rls_regress_user1', 'my second manga'),
- ( 6, 22, 1, 'rls_regress_user2', 'great science fiction'),
- ( 7, 33, 2, 'rls_regress_user2', 'great technology book'),
- ( 8, 44, 1, 'rls_regress_user2', 'great manga');
+ ( 1, 11, 1, 'regress_rls_user1', 'my first novel'),
+ ( 2, 11, 2, 'regress_rls_user1', 'my second novel'),
+ ( 3, 22, 2, 'regress_rls_user1', 'my science fiction'),
+ ( 4, 44, 1, 'regress_rls_user1', 'my first manga'),
+ ( 5, 44, 2, 'regress_rls_user1', 'my second manga'),
+ ( 6, 22, 1, 'regress_rls_user2', 'great science fiction'),
+ ( 7, 33, 2, 'regress_rls_user2', 'great technology book'),
+ ( 8, 44, 1, 'regress_rls_user2', 'great manga');
ALTER TABLE document ENABLE ROW LEVEL SECURITY;
@@ -88,8 +88,8 @@ ALTER TABLE document ENABLE ROW LEVEL SECURITY;
CREATE POLICY p1 ON document
USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user));
--- viewpoint from rls_regress_user1
-SET SESSION AUTHORIZATION rls_regress_user1;
+-- viewpoint from regress_rls_user1
+SET SESSION AUTHORIZATION regress_rls_user1;
SET row_security TO ON;
SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did;
@@ -98,8 +98,8 @@ SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did;
SELECT * FROM document TABLESAMPLE BERNOULLI(50) REPEATABLE(0)
WHERE f_leak(dtitle) ORDER BY did;
--- viewpoint from rls_regress_user2
-SET SESSION AUTHORIZATION rls_regress_user2;
+-- viewpoint from regress_rls_user2
+SET SESSION AUTHORIZATION regress_rls_user2;
SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did;
@@ -114,16 +114,16 @@ EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dt
ALTER POLICY p1 ON document USING (true); --fail
DROP POLICY p1 ON document; --fail
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
ALTER POLICY p1 ON document USING (dauthor = current_user);
--- viewpoint from rls_regress_user1 again
-SET SESSION AUTHORIZATION rls_regress_user1;
+-- viewpoint from regress_rls_user1 again
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER by did;
-- viewpoint from rls_regres_user2 again
-SET SESSION AUTHORIZATION rls_regress_user2;
+SET SESSION AUTHORIZATION regress_rls_user2;
SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER by did;
@@ -131,32 +131,32 @@ EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
-- interaction of FK/PK constraints
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE POLICY p2 ON category
- USING (CASE WHEN current_user = 'rls_regress_user1' THEN cid IN (11, 33)
- WHEN current_user = 'rls_regress_user2' THEN cid IN (22, 44)
+ USING (CASE WHEN current_user = 'regress_rls_user1' THEN cid IN (11, 33)
+ WHEN current_user = 'regress_rls_user2' THEN cid IN (22, 44)
ELSE false END);
ALTER TABLE category ENABLE ROW LEVEL SECURITY;
-- cannot delete PK referenced by invisible FK
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid;
DELETE FROM category WHERE cid = 33; -- fails with FK violation
-- can insert FK referencing invisible PK
-SET SESSION AUTHORIZATION rls_regress_user2;
+SET SESSION AUTHORIZATION regress_rls_user2;
SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid;
INSERT INTO document VALUES (10, 33, 1, current_user, 'hoge');
-- UNIQUE or PRIMARY KEY constraint violation DOES reveal presence of row
-SET SESSION AUTHORIZATION rls_regress_user1;
-INSERT INTO document VALUES (8, 44, 1, 'rls_regress_user1', 'my third manga'); -- Must fail with unique violation, revealing presence of did we can't see
+SET SESSION AUTHORIZATION regress_rls_user1;
+INSERT INTO document VALUES (8, 44, 1, 'regress_rls_user1', 'my third manga'); -- Must fail with unique violation, revealing presence of did we can't see
SELECT * FROM document WHERE did = 8; -- and confirm we can't see it
-- RLS policies are checked before constraints
-INSERT INTO document VALUES (8, 44, 1, 'rls_regress_user2', 'my third manga'); -- Should fail with RLS check violation, not duplicate key violation
-UPDATE document SET did = 8, dauthor = 'rls_regress_user2' WHERE did = 5; -- Should fail with RLS check violation, not duplicate key violation
+INSERT INTO document VALUES (8, 44, 1, 'regress_rls_user2', 'my third manga'); -- Should fail with RLS check violation, not duplicate key violation
+UPDATE document SET did = 8, dauthor = 'regress_rls_user2' WHERE did = 5; -- Should fail with RLS check violation, not duplicate key violation
-- database superuser does bypass RLS policy when enabled
RESET SESSION AUTHORIZATION;
@@ -171,19 +171,19 @@ SELECT * FROM document;
SELECT * FROM category;
-- database non-superuser with bypass privilege can bypass RLS policy when disabled
-SET SESSION AUTHORIZATION rls_regress_exempt_user;
+SET SESSION AUTHORIZATION regress_rls_exempt_user;
SET row_security TO OFF;
SELECT * FROM document;
SELECT * FROM category;
-- RLS policy does not apply to table owner when RLS enabled.
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
SET row_security TO ON;
SELECT * FROM document;
SELECT * FROM category;
-- RLS policy does not apply to table owner when RLS disabled.
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
SET row_security TO OFF;
SELECT * FROM document;
SELECT * FROM category;
@@ -191,7 +191,7 @@ SELECT * FROM category;
--
-- Table inheritance and RLS policy
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
SET row_security TO ON;
@@ -232,7 +232,7 @@ CREATE POLICY p2 ON t2 FOR ALL TO PUBLIC USING (a % 2 = 1); -- be odd number
ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
ALTER TABLE t2 ENABLE ROW LEVEL SECURITY;
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM t1;
EXPLAIN (COSTS OFF) SELECT * FROM t1;
@@ -266,13 +266,13 @@ SELECT * FROM t1 WHERE f_leak(b);
EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
-- non-superuser with bypass privilege can bypass RLS policy when disabled
-SET SESSION AUTHORIZATION rls_regress_exempt_user;
+SET SESSION AUTHORIZATION regress_rls_exempt_user;
SET row_security TO OFF;
SELECT * FROM t1 WHERE f_leak(b);
EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
----- Dependencies -----
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
SET row_security TO ON;
CREATE TABLE dependee (x integer, y integer);
@@ -293,42 +293,42 @@ EXPLAIN (COSTS OFF) SELECT * FROM dependent; -- After drop, should be unqualifie
--
-- Simple recursion
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE TABLE rec1 (x integer, y integer);
CREATE POLICY r1 ON rec1 USING (x = (SELECT r.x FROM rec1 r WHERE y = r.y));
ALTER TABLE rec1 ENABLE ROW LEVEL SECURITY;
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM rec1; -- fail, direct recursion
--
-- Mutual recursion
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE TABLE rec2 (a integer, b integer);
ALTER POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2 WHERE b = y));
CREATE POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1 WHERE y = b));
ALTER TABLE rec2 ENABLE ROW LEVEL SECURITY;
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM rec1; -- fail, mutual recursion
--
-- Mutual recursion via views
--
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
CREATE VIEW rec1v AS SELECT * FROM rec1;
CREATE VIEW rec2v AS SELECT * FROM rec2;
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
ALTER POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2v WHERE b = y));
ALTER POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1v WHERE y = b));
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM rec1; -- fail, mutual recursion via views
--
-- Mutual recursion via .s.b views
--
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
-- Suppress NOTICE messages when doing a cascaded drop.
SET client_min_messages TO 'warning';
@@ -337,24 +337,24 @@ RESET client_min_messages;
CREATE VIEW rec1v WITH (security_barrier) AS SELECT * FROM rec1;
CREATE VIEW rec2v WITH (security_barrier) AS SELECT * FROM rec2;
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2v WHERE b = y));
CREATE POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1v WHERE y = b));
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM rec1; -- fail, mutual recursion via s.b. views
--
-- recursive RLS and VIEWs in policy
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE TABLE s1 (a int, b text);
INSERT INTO s1 (SELECT x, md5(x::text) FROM generate_series(-10,10) x);
CREATE TABLE s2 (x int, y text);
INSERT INTO s2 (SELECT x, md5(x::text) FROM generate_series(-6,6) x);
-GRANT SELECT ON s1, s2 TO rls_regress_user1;
+GRANT SELECT ON s1, s2 TO regress_rls_user1;
CREATE POLICY p1 ON s1 USING (a in (select x from s2 where y like '%2f%'));
CREATE POLICY p2 ON s2 USING (x in (select a from s1 where b like '%22%'));
@@ -363,35 +363,35 @@ CREATE POLICY p3 ON s1 FOR INSERT WITH CHECK (a = (SELECT a FROM s1));
ALTER TABLE s1 ENABLE ROW LEVEL SECURITY;
ALTER TABLE s2 ENABLE ROW LEVEL SECURITY;
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
CREATE VIEW v2 AS SELECT * FROM s2 WHERE y like '%af%';
SELECT * FROM s1 WHERE f_leak(b); -- fail (infinite recursion)
INSERT INTO s1 VALUES (1, 'foo'); -- fail (infinite recursion)
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
DROP POLICY p3 on s1;
ALTER POLICY p2 ON s2 USING (x % 2 = 0);
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM s1 WHERE f_leak(b); -- OK
EXPLAIN (COSTS OFF) SELECT * FROM only s1 WHERE f_leak(b);
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
ALTER POLICY p1 ON s1 USING (a in (select x from v2)); -- using VIEW in RLS policy
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM s1 WHERE f_leak(b); -- OK
EXPLAIN (COSTS OFF) SELECT * FROM s1 WHERE f_leak(b);
SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%';
EXPLAIN (COSTS OFF) SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%';
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
ALTER POLICY p2 ON s2 USING (x in (select a from s1 where b like '%d2%'));
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM s1 WHERE f_leak(b); -- fail (infinite recursion via view)
--- prepared statement with rls_regress_user0 privilege
+-- prepared statement with regress_rls_user0 privilege
PREPARE p1(int) AS SELECT * FROM t1 WHERE a <= $1;
EXECUTE p1(2);
EXPLAIN (COSTS OFF) EXECUTE p1(2);
@@ -411,7 +411,7 @@ EXECUTE p2(2);
EXPLAIN (COSTS OFF) EXECUTE p2(2);
-- also, case when privilege switch from superuser
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SET row_security TO ON;
EXECUTE p2(2);
EXPLAIN (COSTS OFF) EXECUTE p2(2);
@@ -419,7 +419,7 @@ EXPLAIN (COSTS OFF) EXECUTE p2(2);
--
-- UPDATE / DELETE and Row-level security
--
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
EXPLAIN (COSTS OFF) UPDATE t1 SET b = b || b WHERE f_leak(b);
UPDATE t1 SET b = b || b WHERE f_leak(b);
@@ -471,7 +471,7 @@ RESET SESSION AUTHORIZATION;
SET row_security TO OFF;
SELECT * FROM t1 ORDER BY a,b;
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SET row_security TO ON;
EXPLAIN (COSTS OFF) DELETE FROM only t1 WHERE f_leak(b);
EXPLAIN (COSTS OFF) DELETE FROM t1 WHERE f_leak(b);
@@ -482,19 +482,19 @@ DELETE FROM t1 WHERE f_leak(b) RETURNING oid, *, t1;
--
-- S.b. view on top of Row-level security
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE TABLE b1 (a int, b text);
INSERT INTO b1 (SELECT x, md5(x::text) FROM generate_series(-10,10) x);
CREATE POLICY p1 ON b1 USING (a % 2 = 0);
ALTER TABLE b1 ENABLE ROW LEVEL SECURITY;
-GRANT ALL ON b1 TO rls_regress_user1;
+GRANT ALL ON b1 TO regress_rls_user1;
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
CREATE VIEW bv1 WITH (security_barrier) AS SELECT * FROM b1 WHERE a > 0 WITH CHECK OPTION;
-GRANT ALL ON bv1 TO rls_regress_user2;
+GRANT ALL ON bv1 TO regress_rls_user2;
-SET SESSION AUTHORIZATION rls_regress_user2;
+SET SESSION AUTHORIZATION regress_rls_user2;
EXPLAIN (COSTS OFF) SELECT * FROM bv1 WHERE f_leak(b);
SELECT * FROM bv1 WHERE f_leak(b);
@@ -509,13 +509,13 @@ UPDATE bv1 SET b = 'yyy' WHERE a = 4 AND f_leak(b);
EXPLAIN (COSTS OFF) DELETE FROM bv1 WHERE a = 6 AND f_leak(b);
DELETE FROM bv1 WHERE a = 6 AND f_leak(b);
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
SELECT * FROM b1;
--
-- INSERT ... ON CONFLICT DO UPDATE and Row-level security
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
DROP POLICY p1 ON document;
CREATE POLICY p1 ON document FOR SELECT USING (true);
@@ -524,14 +524,14 @@ CREATE POLICY p3 ON document FOR UPDATE
USING (cid = (SELECT cid from category WHERE cname = 'novel'))
WITH CHECK (dauthor = current_user);
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
-- Exists...
SELECT * FROM document WHERE did = 2;
-- ...so violates actual WITH CHECK OPTION within UPDATE (not INSERT, since
-- alternative UPDATE path happens to be taken):
-INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user2', 'my first novel')
+INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_user2', 'my first novel')
ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, dauthor = EXCLUDED.dauthor;
-- Violates USING qual for UPDATE policy p3.
@@ -539,38 +539,38 @@ INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel')
-- UPDATE path is taken, but UPDATE fails purely because *existing* row to be
-- updated is not a "novel"/cid 11 (row is not leaked, even though we have
-- SELECT privileges sufficient to see the row in this instance):
-INSERT INTO document VALUES (33, 22, 1, 'rls_regress_user1', 'okay science fiction'); -- preparation for next statement
-INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'Some novel, replaces sci-fi') -- takes UPDATE path
+INSERT INTO document VALUES (33, 22, 1, 'regress_rls_user1', 'okay science fiction'); -- preparation for next statement
+INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_user1', 'Some novel, replaces sci-fi') -- takes UPDATE path
ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle;
-- Fine (we UPDATE, since INSERT WCOs and UPDATE security barrier quals + WCOs
-- not violated):
-INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'my first novel')
+INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_user1', 'my first novel')
ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
-- Fine (we INSERT, so "cid = 33" ("technology") isn't evaluated):
-INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'some technology novel')
+INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_user1', 'some technology novel')
ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *;
-- Fine (same query, but we UPDATE, so "cid = 33", ("technology") is not the
-- case in respect of *existing* tuple):
-INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'some technology novel')
+INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_user1', 'some technology novel')
ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *;
-- Same query a third time, but now fails due to existing tuple finally not
-- passing quals:
-INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'some technology novel')
+INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_user1', 'some technology novel')
ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *;
-- Don't fail just because INSERT doesn't satisfy WITH CHECK option that
-- originated as a barrier/USING() qual from the UPDATE. Note that the UPDATE
-- path *isn't* taken, and so UPDATE-related policy does not apply:
-INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'technology book, can only insert')
+INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'regress_rls_user1', 'technology book, can only insert')
ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
-- But this time, the same statement fails, because the UPDATE path is taken,
-- and updating the row just inserted falls afoul of security barrier qual
-- (enforced as WCO) -- what we might have updated target tuple to is
-- irrelevant, in fact.
-INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'technology book, can only insert')
+INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'regress_rls_user1', 'technology book, can only insert')
ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
-- Test default USING qual enforced as WCO
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
DROP POLICY p1 ON document;
DROP POLICY p2 ON document;
DROP POLICY p3 ON document;
@@ -578,7 +578,7 @@ DROP POLICY p3 ON document;
CREATE POLICY p3_with_default ON document FOR UPDATE
USING (cid = (SELECT cid from category WHERE cname = 'novel'));
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
-- Just because WCO-style enforcement of USING quals occurs with
-- existing/target tuple does not mean that the implementation can be allowed
-- to fail to also enforce this qual against the final tuple appended to
@@ -590,17 +590,17 @@ SET SESSION AUTHORIZATION rls_regress_user1;
-- a USING qual for the purposes of RLS in general, as opposed to an explicit
-- USING qual that is ordinarily a security barrier. We leave it up to the
-- UPDATE to make this fail:
-INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'technology book, can only insert')
+INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'regress_rls_user1', 'technology book, can only insert')
ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
-- UPDATE path is taken here. Existing tuple passes, since it's cid
-- corresponds to "novel", but default USING qual is enforced against
-- post-UPDATE tuple too (as always when updating with a policy that lacks an
-- explicit WCO), and so this fails:
-INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'my first novel')
+INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'technology'), 1, 'regress_rls_user1', 'my first novel')
ON CONFLICT (did) DO UPDATE SET cid = EXCLUDED.cid, dtitle = EXCLUDED.dtitle RETURNING *;
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
DROP POLICY p3_with_default ON document;
--
@@ -611,28 +611,28 @@ CREATE POLICY p3_with_all ON document FOR ALL
USING (cid = (SELECT cid from category WHERE cname = 'novel'))
WITH CHECK (dauthor = current_user);
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
-- Fails, since ALL WCO is enforced in insert path:
-INSERT INTO document VALUES (80, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user2', 'my first novel')
+INSERT INTO document VALUES (80, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_user2', 'my first novel')
ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33;
-- Fails, since ALL policy USING qual is enforced (existing, target tuple is in
-- violation, since it has the "manga" cid):
-INSERT INTO document VALUES (4, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'my first novel')
+INSERT INTO document VALUES (4, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_user1', 'my first novel')
ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle;
-- Fails, since ALL WCO are enforced:
-INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'my first novel')
- ON CONFLICT (did) DO UPDATE SET dauthor = 'rls_regress_user2';
+INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_user1', 'my first novel')
+ ON CONFLICT (did) DO UPDATE SET dauthor = 'regress_rls_user2';
--
-- ROLE/GROUP
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE TABLE z1 (a int, b text);
CREATE TABLE z2 (a int, b text);
-GRANT SELECT ON z1,z2 TO rls_regress_group1, rls_regress_group2,
- rls_regress_user1, rls_regress_user2;
+GRANT SELECT ON z1,z2 TO regress_rls_group1, regress_rls_group2,
+ regress_rls_user1, regress_rls_user2;
INSERT INTO z1 VALUES
(1, 'aaa'),
@@ -640,12 +640,12 @@ INSERT INTO z1 VALUES
(3, 'ccc'),
(4, 'ddd');
-CREATE POLICY p1 ON z1 TO rls_regress_group1 USING (a % 2 = 0);
-CREATE POLICY p2 ON z1 TO rls_regress_group2 USING (a % 2 = 1);
+CREATE POLICY p1 ON z1 TO regress_rls_group1 USING (a % 2 = 0);
+CREATE POLICY p2 ON z1 TO regress_rls_group2 USING (a % 2 = 1);
ALTER TABLE z1 ENABLE ROW LEVEL SECURITY;
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM z1 WHERE f_leak(b);
EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
@@ -658,7 +658,7 @@ EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
PREPARE plancache_test3 AS WITH q AS (SELECT * FROM z2) SELECT * FROM q,z1 WHERE f_leak(z1.b);
EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
-SET ROLE rls_regress_group1;
+SET ROLE regress_rls_group1;
SELECT * FROM z1 WHERE f_leak(b);
EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
@@ -666,7 +666,7 @@ EXPLAIN (COSTS OFF) EXECUTE plancache_test;
EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
-SET SESSION AUTHORIZATION rls_regress_user2;
+SET SESSION AUTHORIZATION regress_rls_user2;
SELECT * FROM z1 WHERE f_leak(b);
EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
@@ -674,7 +674,7 @@ EXPLAIN (COSTS OFF) EXECUTE plancache_test;
EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
-SET ROLE rls_regress_group2;
+SET ROLE regress_rls_group2;
SELECT * FROM z1 WHERE f_leak(b);
EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
@@ -686,69 +686,69 @@ EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
-- Views should follow policy for view owner.
--
-- View and Table owner are the same.
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE VIEW rls_view AS SELECT * FROM z1 WHERE f_leak(b);
-GRANT SELECT ON rls_view TO rls_regress_user1;
+GRANT SELECT ON rls_view TO regress_rls_user1;
-- Query as role that is not owner of view or table. Should return all records.
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM rls_view;
EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
-- Query as view/table owner. Should return all records.
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
SELECT * FROM rls_view;
EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
DROP VIEW rls_view;
-- View and Table owners are different.
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
CREATE VIEW rls_view AS SELECT * FROM z1 WHERE f_leak(b);
-GRANT SELECT ON rls_view TO rls_regress_user0;
+GRANT SELECT ON rls_view TO regress_rls_user0;
-- Query as role that is not owner of view but is owner of table.
-- Should return records based on view owner policies.
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
SELECT * FROM rls_view;
EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
-- Query as role that is not owner of table but is owner of view.
-- Should return records based on view owner policies.
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM rls_view;
EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
-- Query as role that is not the owner of the table or view without permissions.
-SET SESSION AUTHORIZATION rls_regress_user2;
+SET SESSION AUTHORIZATION regress_rls_user2;
SELECT * FROM rls_view; --fail - permission denied.
EXPLAIN (COSTS OFF) SELECT * FROM rls_view; --fail - permission denied.
-- Query as role that is not the owner of the table or view with permissions.
-SET SESSION AUTHORIZATION rls_regress_user1;
-GRANT SELECT ON rls_view TO rls_regress_user2;
+SET SESSION AUTHORIZATION regress_rls_user1;
+GRANT SELECT ON rls_view TO regress_rls_user2;
SELECT * FROM rls_view;
EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
DROP VIEW rls_view;
--
-- Command specific
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE TABLE x1 (a int, b text, c text);
GRANT ALL ON x1 TO PUBLIC;
INSERT INTO x1 VALUES
- (1, 'abc', 'rls_regress_user1'),
- (2, 'bcd', 'rls_regress_user1'),
- (3, 'cde', 'rls_regress_user2'),
- (4, 'def', 'rls_regress_user2'),
- (5, 'efg', 'rls_regress_user1'),
- (6, 'fgh', 'rls_regress_user1'),
- (7, 'fgh', 'rls_regress_user2'),
- (8, 'fgh', 'rls_regress_user2');
+ (1, 'abc', 'regress_rls_user1'),
+ (2, 'bcd', 'regress_rls_user1'),
+ (3, 'cde', 'regress_rls_user2'),
+ (4, 'def', 'regress_rls_user2'),
+ (5, 'efg', 'regress_rls_user1'),
+ (6, 'fgh', 'regress_rls_user1'),
+ (7, 'fgh', 'regress_rls_user2'),
+ (8, 'fgh', 'regress_rls_user2');
CREATE POLICY p0 ON x1 FOR ALL USING (c = current_user);
CREATE POLICY p1 ON x1 FOR SELECT USING (a % 2 = 0);
@@ -758,11 +758,11 @@ CREATE POLICY p4 ON x1 FOR DELETE USING (a < 8);
ALTER TABLE x1 ENABLE ROW LEVEL SECURITY;
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM x1 WHERE f_leak(b) ORDER BY a ASC;
UPDATE x1 SET b = b || '_updt' WHERE f_leak(b) RETURNING *;
-SET SESSION AUTHORIZATION rls_regress_user2;
+SET SESSION AUTHORIZATION regress_rls_user2;
SELECT * FROM x1 WHERE f_leak(b) ORDER BY a ASC;
UPDATE x1 SET b = b || '_updt' WHERE f_leak(b) RETURNING *;
DELETE FROM x1 WHERE f_leak(b) RETURNING *;
@@ -770,11 +770,11 @@ DELETE FROM x1 WHERE f_leak(b) RETURNING *;
--
-- Duplicate Policy Names
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE TABLE y1 (a int, b text);
CREATE TABLE y2 (a int, b text);
-GRANT ALL ON y1, y2 TO rls_regress_user1;
+GRANT ALL ON y1, y2 TO regress_rls_user1;
CREATE POLICY p1 ON y1 FOR ALL USING (a % 2 = 0);
CREATE POLICY p2 ON y1 FOR SELECT USING (a > 2);
@@ -788,14 +788,14 @@ ALTER TABLE y2 ENABLE ROW LEVEL SECURITY;
-- Expression structure with SBV
--
-- Create view as table owner. RLS should NOT be applied.
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE VIEW rls_sbv WITH (security_barrier) AS
SELECT * FROM y1 WHERE f_leak(b);
EXPLAIN (COSTS OFF) SELECT * FROM rls_sbv WHERE (a = 1);
DROP VIEW rls_sbv;
-- Create view as role that does not own table. RLS should be applied.
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
CREATE VIEW rls_sbv WITH (security_barrier) AS
SELECT * FROM y1 WHERE f_leak(b);
EXPLAIN (COSTS OFF) SELECT * FROM rls_sbv WHERE (a = 1);
@@ -804,12 +804,12 @@ DROP VIEW rls_sbv;
--
-- Expression structure
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
INSERT INTO y2 (SELECT x, md5(x::text) FROM generate_series(0,20) x);
CREATE POLICY p2 ON y2 USING (a % 3 = 0);
CREATE POLICY p3 ON y2 USING (a % 4 = 0);
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM y2 WHERE f_leak(b);
EXPLAIN (COSTS OFF) SELECT * FROM y2 WHERE f_leak(b);
@@ -845,26 +845,26 @@ RESET client_min_messages;
CREATE TABLE t1 (a integer);
-GRANT SELECT ON t1 TO rls_regress_user1, rls_regress_user2;
+GRANT SELECT ON t1 TO regress_rls_user1, regress_rls_user2;
-CREATE POLICY p1 ON t1 TO rls_regress_user1 USING ((a % 2) = 0);
-CREATE POLICY p2 ON t1 TO rls_regress_user2 USING ((a % 4) = 0);
+CREATE POLICY p1 ON t1 TO regress_rls_user1 USING ((a % 2) = 0);
+CREATE POLICY p2 ON t1 TO regress_rls_user2 USING ((a % 4) = 0);
ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
--- Prepare as rls_regress_user1
-SET ROLE rls_regress_user1;
+-- Prepare as regress_rls_user1
+SET ROLE regress_rls_user1;
PREPARE role_inval AS SELECT * FROM t1;
-- Check plan
EXPLAIN (COSTS OFF) EXECUTE role_inval;
--- Change to rls_regress_user2
-SET ROLE rls_regress_user2;
+-- Change to regress_rls_user2
+SET ROLE regress_rls_user2;
-- Check plan- should be different
EXPLAIN (COSTS OFF) EXECUTE role_inval;
--- Change back to rls_regress_user1
-SET ROLE rls_regress_user1;
+-- Change back to regress_rls_user1
+SET ROLE regress_rls_user1;
-- Check plan- should be back to original
EXPLAIN (COSTS OFF) EXECUTE role_inval;
@@ -878,11 +878,11 @@ CREATE POLICY p1 ON t1 USING (a % 2 = 0);
ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
-GRANT ALL ON t1 TO rls_regress_user1;
+GRANT ALL ON t1 TO regress_rls_user1;
INSERT INTO t1 (SELECT x, md5(x::text) FROM generate_series(0,20) x);
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
WITH cte1 AS (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1;
EXPLAIN (COSTS OFF) WITH cte1 AS (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1;
@@ -914,7 +914,7 @@ SELECT polname, relname
--
-- Check INSERT SELECT
--
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
CREATE TABLE t2 (a integer, b text);
INSERT INTO t2 (SELECT * FROM t1);
EXPLAIN (COSTS OFF) INSERT INTO t2 (SELECT * FROM t1);
@@ -928,11 +928,11 @@ SELECT * FROM t4;
--
-- RLS with JOIN
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE TABLE blog (id integer, author text, post text);
CREATE TABLE comment (blog_id integer, message text);
-GRANT ALL ON blog, comment TO rls_regress_user1;
+GRANT ALL ON blog, comment TO regress_rls_user1;
CREATE POLICY blog_1 ON blog USING (id % 2 = 0);
@@ -953,23 +953,23 @@ INSERT INTO comment VALUES
(4, 'insane!'),
(2, 'who did it?');
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
-- Check RLS JOIN with Non-RLS.
SELECT id, author, message FROM blog JOIN comment ON id = blog_id;
-- Check Non-RLS JOIN with RLS.
SELECT id, author, message FROM comment JOIN blog ON id = blog_id;
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE POLICY comment_1 ON comment USING (blog_id < 4);
ALTER TABLE comment ENABLE ROW LEVEL SECURITY;
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
-- Check RLS JOIN RLS
SELECT id, author, message FROM blog JOIN comment ON id = blog_id;
SELECT id, author, message FROM comment JOIN blog ON id = blog_id;
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
DROP TABLE blog, comment;
--
@@ -977,7 +977,7 @@ DROP TABLE blog, comment;
--
RESET SESSION AUTHORIZATION;
DROP POLICY p2 ON t1;
-ALTER TABLE t1 OWNER TO rls_regress_user0;
+ALTER TABLE t1 OWNER TO regress_rls_user0;
-- Check that default deny does not apply to superuser.
RESET SESSION AUTHORIZATION;
@@ -985,16 +985,16 @@ SELECT * FROM t1;
EXPLAIN (COSTS OFF) SELECT * FROM t1;
-- Check that default deny does not apply to table owner.
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
SELECT * FROM t1;
EXPLAIN (COSTS OFF) SELECT * FROM t1;
-- Check that default deny applies to non-owner/non-superuser when RLS on.
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SET row_security TO ON;
SELECT * FROM t1;
EXPLAIN (COSTS OFF) SELECT * FROM t1;
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM t1;
EXPLAIN (COSTS OFF) SELECT * FROM t1;
@@ -1009,7 +1009,7 @@ CREATE POLICY p1 ON copy_t USING (a % 2 = 0);
ALTER TABLE copy_t ENABLE ROW LEVEL SECURITY;
-GRANT ALL ON copy_t TO rls_regress_user1, rls_regress_exempt_user;
+GRANT ALL ON copy_t TO regress_rls_user1, regress_rls_exempt_user;
INSERT INTO copy_t (SELECT x, md5(x::text) FROM generate_series(0,10) x);
@@ -1021,21 +1021,21 @@ SET row_security TO ON;
COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ',';
-- Check COPY TO as user with permissions.
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SET row_security TO OFF;
COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - would be affected by RLS
SET row_security TO ON;
COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
-- Check COPY TO as user with permissions and BYPASSRLS
-SET SESSION AUTHORIZATION rls_regress_exempt_user;
+SET SESSION AUTHORIZATION regress_rls_exempt_user;
SET row_security TO OFF;
COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
SET row_security TO ON;
COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
-- Check COPY TO as user without permissions. SET row_security TO OFF;
-SET SESSION AUTHORIZATION rls_regress_user2;
+SET SESSION AUTHORIZATION regress_rls_user2;
SET row_security TO OFF;
COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - would be affected by RLS
SET row_security TO ON;
@@ -1049,7 +1049,7 @@ CREATE POLICY p1 ON copy_rel_to USING (a % 2 = 0);
ALTER TABLE copy_rel_to ENABLE ROW LEVEL SECURITY;
-GRANT ALL ON copy_rel_to TO rls_regress_user1, rls_regress_exempt_user;
+GRANT ALL ON copy_rel_to TO regress_rls_user1, regress_rls_exempt_user;
INSERT INTO copy_rel_to VALUES (1, md5('1'));
@@ -1061,21 +1061,21 @@ SET row_security TO ON;
COPY copy_rel_to TO STDOUT WITH DELIMITER ',';
-- Check COPY TO as user with permissions.
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SET row_security TO OFF;
COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - would be affected by RLS
SET row_security TO ON;
COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
-- Check COPY TO as user with permissions and BYPASSRLS
-SET SESSION AUTHORIZATION rls_regress_exempt_user;
+SET SESSION AUTHORIZATION regress_rls_exempt_user;
SET row_security TO OFF;
COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
SET row_security TO ON;
COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
-- Check COPY TO as user without permissions. SET row_security TO OFF;
-SET SESSION AUTHORIZATION rls_regress_user2;
+SET SESSION AUTHORIZATION regress_rls_user2;
SET row_security TO OFF;
COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - permission denied
SET row_security TO ON;
@@ -1099,14 +1099,14 @@ COPY copy_t FROM STDIN; --ok
\.
-- Check COPY FROM as user with permissions.
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SET row_security TO OFF;
COPY copy_t FROM STDIN; --fail - would be affected by RLS.
SET row_security TO ON;
COPY copy_t FROM STDIN; --fail - COPY FROM not supported by RLS.
-- Check COPY FROM as user with permissions and BYPASSRLS
-SET SESSION AUTHORIZATION rls_regress_exempt_user;
+SET SESSION AUTHORIZATION regress_rls_exempt_user;
SET row_security TO ON;
COPY copy_t FROM STDIN; --ok
1 abc
@@ -1116,7 +1116,7 @@ COPY copy_t FROM STDIN; --ok
\.
-- Check COPY FROM as user without permissions.
-SET SESSION AUTHORIZATION rls_regress_user2;
+SET SESSION AUTHORIZATION regress_rls_user2;
SET row_security TO OFF;
COPY copy_t FROM STDIN; --fail - permission denied.
SET row_security TO ON;
@@ -1127,16 +1127,16 @@ DROP TABLE copy_t;
DROP TABLE copy_rel_to CASCADE;
-- Check WHERE CURRENT OF
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE TABLE current_check (currentid int, payload text, rlsuser text);
GRANT ALL ON current_check TO PUBLIC;
INSERT INTO current_check VALUES
- (1, 'abc', 'rls_regress_user1'),
- (2, 'bcd', 'rls_regress_user1'),
- (3, 'cde', 'rls_regress_user1'),
- (4, 'def', 'rls_regress_user1');
+ (1, 'abc', 'regress_rls_user1'),
+ (2, 'bcd', 'regress_rls_user1'),
+ (3, 'cde', 'regress_rls_user1'),
+ (4, 'def', 'regress_rls_user1');
CREATE POLICY p1 ON current_check FOR SELECT USING (currentid % 2 = 0);
CREATE POLICY p2 ON current_check FOR DELETE USING (currentid = 4 AND rlsuser = current_user);
@@ -1144,7 +1144,7 @@ CREATE POLICY p3 ON current_check FOR UPDATE USING (currentid = 4) WITH CHECK (r
ALTER TABLE current_check ENABLE ROW LEVEL SECURITY;
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
-- Can SELECT even rows
SELECT * FROM current_check;
@@ -1179,7 +1179,7 @@ COMMIT;
-- check pg_stats view filtering
--
SET row_security TO ON;
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
ANALYZE current_check;
-- Stats visible
SELECT row_security_active('current_check');
@@ -1187,7 +1187,7 @@ SELECT attname, most_common_vals FROM pg_stats
WHERE tablename = 'current_check'
ORDER BY 1;
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
-- Stats not visible
SELECT row_security_active('current_check');
SELECT attname, most_common_vals FROM pg_stats
@@ -1201,9 +1201,9 @@ BEGIN;
CREATE TABLE coll_t (c) AS VALUES ('bar'::text);
CREATE POLICY coll_p ON coll_t USING (c < ('foo'::text COLLATE "C"));
ALTER TABLE coll_t ENABLE ROW LEVEL SECURITY;
-GRANT SELECT ON coll_t TO rls_regress_user0;
+GRANT SELECT ON coll_t TO regress_rls_user0;
SELECT (string_to_array(polqual, ':'))[7] AS inputcollid FROM pg_policy WHERE polrelid = 'coll_t'::regclass;
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
SELECT * FROM coll_t;
ROLLBACK;
@@ -1212,11 +1212,11 @@ ROLLBACK;
--
RESET SESSION AUTHORIZATION;
BEGIN;
-CREATE ROLE alice;
-CREATE ROLE bob;
+CREATE ROLE regress_rls_user4;
+CREATE ROLE regress_rls_user5;
CREATE TABLE tbl1 (c) AS VALUES ('bar'::text);
-GRANT SELECT ON TABLE tbl1 TO alice;
-CREATE POLICY P ON tbl1 TO alice, bob USING (true);
+GRANT SELECT ON TABLE tbl1 TO regress_rls_user4;
+CREATE POLICY P ON tbl1 TO regress_rls_user4, regress_rls_user5 USING (true);
SELECT refclassid::regclass, deptype
FROM pg_depend
WHERE classid = 'pg_policy'::regclass
@@ -1224,29 +1224,29 @@ SELECT refclassid::regclass, deptype
SELECT refclassid::regclass, deptype
FROM pg_shdepend
WHERE classid = 'pg_policy'::regclass
- AND refobjid IN ('alice'::regrole, 'bob'::regrole);
+ AND refobjid IN ('regress_rls_user4'::regrole, 'regress_rls_user5'::regrole);
SAVEPOINT q;
-DROP ROLE alice; --fails due to dependency on POLICY p
+DROP ROLE regress_rls_user4; --fails due to dependency on POLICY p
ROLLBACK TO q;
-ALTER POLICY p ON tbl1 TO bob USING (true);
+ALTER POLICY p ON tbl1 TO regress_rls_user5 USING (true);
SAVEPOINT q;
-DROP ROLE alice; --fails due to dependency on GRANT SELECT
+DROP ROLE regress_rls_user4; --fails due to dependency on GRANT SELECT
ROLLBACK TO q;
-REVOKE ALL ON TABLE tbl1 FROM alice;
+REVOKE ALL ON TABLE tbl1 FROM regress_rls_user4;
SAVEPOINT q;
-DROP ROLE alice; --succeeds
+DROP ROLE regress_rls_user4; --succeeds
ROLLBACK TO q;
SAVEPOINT q;
-DROP ROLE bob; --fails due to dependency on POLICY p
+DROP ROLE regress_rls_user5; --fails due to dependency on POLICY p
ROLLBACK TO q;
DROP POLICY p ON tbl1;
SAVEPOINT q;
-DROP ROLE bob; -- succeeds
+DROP ROLE regress_rls_user5; -- succeeds
ROLLBACK TO q;
ROLLBACK; -- cleanup
@@ -1286,13 +1286,13 @@ ROLLBACK;
--
-- Non-target relations are only subject to SELECT policies
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
CREATE TABLE r1 (a int);
CREATE TABLE r2 (a int);
INSERT INTO r1 VALUES (10), (20);
INSERT INTO r2 VALUES (10), (20);
-GRANT ALL ON r1, r2 TO rls_regress_user1;
+GRANT ALL ON r1, r2 TO regress_rls_user1;
CREATE POLICY p1 ON r1 USING (true);
ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
@@ -1303,7 +1303,7 @@ CREATE POLICY p3 ON r2 FOR UPDATE USING (false);
CREATE POLICY p4 ON r2 FOR DELETE USING (false);
ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;
-SET SESSION AUTHORIZATION rls_regress_user1;
+SET SESSION AUTHORIZATION regress_rls_user1;
SELECT * FROM r1;
SELECT * FROM r2;
@@ -1319,14 +1319,14 @@ DELETE FROM r1 USING r2 WHERE r1.a = r2.a + 2 RETURNING *; -- OK
SELECT * FROM r1;
SELECT * FROM r2;
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
DROP TABLE r1;
DROP TABLE r2;
--
-- FORCE ROW LEVEL SECURITY applies RLS to owners too
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
SET row_security = on;
CREATE TABLE r1 (a int);
INSERT INTO r1 VALUES (10), (20);
@@ -1360,7 +1360,7 @@ DROP TABLE r1;
--
-- FORCE ROW LEVEL SECURITY does not break RI
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
SET row_security = on;
CREATE TABLE r1 (a int PRIMARY KEY);
CREATE TABLE r2 (a int REFERENCES r1);
@@ -1455,7 +1455,7 @@ DROP TABLE r1;
-- Test INSERT+RETURNING applies SELECT policies as
-- WithCheckOptions (meaning an error is thrown)
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
SET row_security = on;
CREATE TABLE r1 (a int);
@@ -1485,7 +1485,7 @@ DROP TABLE r1;
-- Test UPDATE+RETURNING applies SELECT policies as
-- WithCheckOptions (meaning an error is thrown)
--
-SET SESSION AUTHORIZATION rls_regress_user0;
+SET SESSION AUTHORIZATION regress_rls_user0;
SET row_security = on;
CREATE TABLE r1 (a int);
@@ -1519,8 +1519,8 @@ RESET SESSION AUTHORIZATION;
CREATE TABLE dep1 (c1 int);
CREATE TABLE dep2 (c1 int);
-CREATE POLICY dep_p1 ON dep1 TO rls_regress_user1 USING (c1 > (select max(dep2.c1) from dep2));
-ALTER POLICY dep_p1 ON dep1 TO rls_regress_user1,rls_regress_user2;
+CREATE POLICY dep_p1 ON dep1 TO regress_rls_user1 USING (c1 > (select max(dep2.c1) from dep2));
+ALTER POLICY dep_p1 ON dep1 TO regress_rls_user1,regress_rls_user2;
-- Should return one
SELECT count(*) = 1 FROM pg_depend
@@ -1532,12 +1532,12 @@ ALTER POLICY dep_p1 ON dep1 USING (true);
-- Should return one
SELECT count(*) = 1 FROM pg_shdepend
WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1')
- AND refobjid = (SELECT oid FROM pg_authid WHERE rolname = 'rls_regress_user1');
+ AND refobjid = (SELECT oid FROM pg_authid WHERE rolname = 'regress_rls_user1');
-- Should return one
SELECT count(*) = 1 FROM pg_shdepend
WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1')
- AND refobjid = (SELECT oid FROM pg_authid WHERE rolname = 'rls_regress_user2');
+ AND refobjid = (SELECT oid FROM pg_authid WHERE rolname = 'regress_rls_user2');
-- Should return zero
SELECT count(*) = 0 FROM pg_depend
@@ -1547,21 +1547,21 @@ SELECT count(*) = 0 FROM pg_depend
-- DROP OWNED BY testing
RESET SESSION AUTHORIZATION;
-CREATE ROLE dob_role1;
-CREATE ROLE dob_role2;
+CREATE ROLE regress_rls_dob_role1;
+CREATE ROLE regress_rls_dob_role2;
CREATE TABLE dob_t1 (c1 int);
-CREATE POLICY p1 ON dob_t1 TO dob_role1 USING (true);
-DROP OWNED BY dob_role1;
+CREATE POLICY p1 ON dob_t1 TO regress_rls_dob_role1 USING (true);
+DROP OWNED BY regress_rls_dob_role1;
DROP POLICY p1 ON dob_t1; -- should fail, already gone
-CREATE POLICY p1 ON dob_t1 TO dob_role1,dob_role2 USING (true);
-DROP OWNED BY dob_role1;
+CREATE POLICY p1 ON dob_t1 TO regress_rls_dob_role1,regress_rls_dob_role2 USING (true);
+DROP OWNED BY regress_rls_dob_role1;
DROP POLICY p1 ON dob_t1; -- should succeed
-DROP USER dob_role1;
-DROP USER dob_role2;
+DROP USER regress_rls_dob_role1;
+DROP USER regress_rls_dob_role2;
--
-- Clean up objects
@@ -1571,19 +1571,19 @@ RESET SESSION AUTHORIZATION;
-- Suppress NOTICE messages when doing a cascaded drop.
SET client_min_messages TO 'warning';
-DROP SCHEMA rls_regress_schema CASCADE;
+DROP SCHEMA regress_rls_schema CASCADE;
RESET client_min_messages;
-DROP USER rls_regress_user0;
-DROP USER rls_regress_user1;
-DROP USER rls_regress_user2;
-DROP USER rls_regress_exempt_user;
-DROP ROLE rls_regress_group1;
-DROP ROLE rls_regress_group2;
+DROP USER regress_rls_user0;
+DROP USER regress_rls_user1;
+DROP USER regress_rls_user2;
+DROP USER regress_rls_exempt_user;
+DROP ROLE regress_rls_group1;
+DROP ROLE regress_rls_group2;
-- Arrange to have a few policies left over, for testing
-- pg_dump/pg_restore
-CREATE SCHEMA rls_regress_schema;
+CREATE SCHEMA regress_rls_schema;
CREATE TABLE rls_tbl (c1 int);
ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY;
CREATE POLICY p1 ON rls_tbl USING (c1 > 5);
--
2.5.0
signature.asc
Description: Digital signature
