diff options
Diffstat (limited to 'src/test/regress/sql/rowsecurity.sql')
| -rw-r--r-- | src/test/regress/sql/rowsecurity.sql | 45 |
1 files changed, 42 insertions, 3 deletions
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql index 7fcefe45026..5e2f4ef8848 100644 --- a/src/test/regress/sql/rowsecurity.sql +++ b/src/test/regress/sql/rowsecurity.sql @@ -10,6 +10,7 @@ SET client_min_messages TO 'warning'; DROP USER IF EXISTS regress_rls_alice; DROP USER IF EXISTS regress_rls_bob; DROP USER IF EXISTS regress_rls_carol; +DROP USER IF EXISTS regress_rls_dave; DROP USER IF EXISTS regress_rls_exempt_user; DROP ROLE IF EXISTS regress_rls_group1; DROP ROLE IF EXISTS regress_rls_group2; @@ -22,6 +23,7 @@ RESET client_min_messages; CREATE USER regress_rls_alice NOLOGIN; CREATE USER regress_rls_bob NOLOGIN; CREATE USER regress_rls_carol NOLOGIN; +CREATE USER regress_rls_dave NOLOGIN; CREATE USER regress_rls_exempt_user BYPASSRLS NOLOGIN; CREATE ROLE regress_rls_group1 NOLOGIN; CREATE ROLE regress_rls_group2 NOLOGIN; @@ -80,14 +82,35 @@ INSERT INTO document VALUES ( 5, 44, 2, 'regress_rls_bob', 'my second manga'), ( 6, 22, 1, 'regress_rls_carol', 'great science fiction'), ( 7, 33, 2, 'regress_rls_carol', 'great technology book'), - ( 8, 44, 1, 'regress_rls_carol', 'great manga'); + ( 8, 44, 1, 'regress_rls_carol', 'great manga'), + ( 9, 22, 1, 'regress_rls_dave', 'awesome science fiction'), + (10, 33, 2, 'regress_rls_dave', 'awesome technology book'); 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 +CREATE POLICY p1 ON document AS PERMISSIVE USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user)); +-- try to create a policy of bogus type +CREATE POLICY p1 ON document AS UGLY + USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user)); + +-- but Dave isn't allowed to anything at cid 50 or above +-- this is to make sure that we sort the policies by name first +-- when applying WITH CHECK, a later INSERT by Dave should fail due +-- to p1r first +CREATE POLICY p2r ON document AS RESTRICTIVE TO regress_rls_dave + USING (cid <> 44 AND cid < 50); + +-- and Dave isn't allowed to see manga documents +CREATE POLICY p1r ON document AS RESTRICTIVE TO regress_rls_dave + USING (cid <> 44); + +\dp +\d document +SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename = 'document' ORDER BY policyname; + -- viewpoint from regress_rls_bob SET SESSION AUTHORIZATION regress_rls_bob; SET row_security TO ON; @@ -110,6 +133,20 @@ SELECT * FROM document TABLESAMPLE BERNOULLI(50) REPEATABLE(0) EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle); EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle); +-- viewpoint from regress_rls_dave +SET SESSION AUTHORIZATION regress_rls_dave; +SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did; +SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did; + +EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle); +EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle); + +-- 44 would technically fail for both p2r and p1r, but we should get an error +-- back from p1r for this because it sorts first +INSERT INTO document VALUES (100, 44, 1, 'regress_rls_dave', 'testing sorting of policies'); -- fail +-- Just to see a p2r error +INSERT INTO document VALUES (100, 55, 1, 'regress_rls_dave', 'testing sorting of policies'); -- fail + -- only owner can change policies ALTER POLICY p1 ON document USING (true); --fail DROP POLICY p1 ON document; --fail @@ -147,7 +184,7 @@ DELETE FROM category WHERE cid = 33; -- fails with FK violation -- can insert FK referencing invisible PK SET SESSION AUTHORIZATION regress_rls_carol; SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid; -INSERT INTO document VALUES (10, 33, 1, current_user, 'hoge'); +INSERT INTO document VALUES (11, 33, 1, current_user, 'hoge'); -- UNIQUE or PRIMARY KEY constraint violation DOES reveal presence of row SET SESSION AUTHORIZATION regress_rls_bob; @@ -517,6 +554,7 @@ SELECT * FROM b1; SET SESSION AUTHORIZATION regress_rls_alice; DROP POLICY p1 ON document; +DROP POLICY p1r ON document; CREATE POLICY p1 ON document FOR SELECT USING (true); CREATE POLICY p2 ON document FOR INSERT WITH CHECK (dauthor = current_user); @@ -1577,6 +1615,7 @@ RESET client_min_messages; DROP USER regress_rls_alice; DROP USER regress_rls_bob; DROP USER regress_rls_carol; +DROP USER regress_rls_dave; DROP USER regress_rls_exempt_user; DROP ROLE regress_rls_group1; DROP ROLE regress_rls_group2; |