From: Chao Li <li.evan.chao@gmail.com>
To: Paul A Jungwirth <pj@illuminatedcomputing.com>
Cc: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Cc: Peter Eisentraut <peter@eisentraut.org>
Subject: Re: Fix RLS checks for UPDATE/DELETE FOR PORTION OF leftover rows
Date: Thu, 16 Jul 2026 09:09:40 +0800
Message-ID: <49247968-DB60-4FF3-892F-CF910E63ADBA@gmail.com> (raw)
In-Reply-To: <CA+renyWqKvB86R2FBAUh=AWoyt40qMf7yHWzoHaBr15kyy+Tjw@mail.gmail.com>
References: <6C34A987-AC50-4477-BD71-2D4AFEE1A589@gmail.com>
<CAJTYsWWJeKKY=Db3R2yP7X8CeHoEu1tFcrRHKRgAf4Fv5-P11g@mail.gmail.com>
<07EF5F4D-4AA3-4C21-8868-9ACC0D8B6655@gmail.com>
<B4648790-5945-4C73-AF5A-8CE8A812527E@gmail.com>
<CA+renyWnvqWPPdfvV1GmGr9wzG5S6c3Th=gpn2ShN0RLhR-Fsg@mail.gmail.com>
<CA+renyWYcQ=Yc1zQJ2UzLDF2cdHBHto-dRHw9s9EAA111ApEqQ@mail.gmail.com>
<20DF964A-C291-491A-9126-62753DCBD7CE@gmail.com>
<CA+renyU=1A7+_zyPdaa6TersUDPrX13ufDW-qdi1wGt_zxXCfA@mail.gmail.com>
<CAEZATCWEsMTVTA4mOPidvPE4wEv=oVzurR+vSAPmeT4RS7FqCg@mail.gmail.com>
<CA+renyX5Mk7oq-S2eX1q7656ThtynmN5J9TWp2fSvWuMPivPjA@mail.gmail.com>
<CAEZATCXW3O9_cniVFtw76zFO5p9D06P1bwMvG8RU4_YFkm0iwA@mail.gmail.com>
<CA+renyWqKvB86R2FBAUh=AWoyt40qMf7yHWzoHaBr15kyy+Tjw@mail.gmail.com>
> On Jul 16, 2026, at 08:26, Paul A Jungwirth <pj@illuminatedcomputing.com> wrote:
>
> On Wed, Jul 15, 2026 at 4:40 PM Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>>
>> On Wed, 15 Jul 2026 at 22:02, Paul A Jungwirth
>> <pj@illuminatedcomputing.com> wrote:
>>>
>>> Thanks for taking a look! Here is a v4 with those additions.
>>>
>>
>> + <entry><command>UPDATE ... FOR PORTION OF</command></entry>
>> + <entry>
>> + Filter existing row <footnoteref linkend="rls-select-priv"/> &
>> + check new row <footnoteref linkend="rls-select-priv"/>
>> + </entry>
>> + <entry>
>>
>> The footnote "rls-select-priv" reads "If read access is required to
>> either the existing or new row (for example, a WHERE or RETURNING
>> clause that refers to columns from the relation)." However, FOR
>> PORTION OF is guaranteed to require read access to the table, so as
>> with a few other entries in that column, the link to this footnote is
>> not needed -- it will unconditionally apply the SELECT policy to the
>> existing and new rows. Similarly for DELETE ... FOR PORTION OF.
>
> Just to confirm: you're saying that because FOR PORTION OF always
> filters rows, the "if" in the footnote is misleading? I can see that
> argument, so I removed the footnote after "filter existing row" and
> "check new row" from the new entries. Do you think I should add a
> different footnote explaining that it is unconditional?
>
>> + /*
>> + * As with regular INSERT/UPDATE above, if SELECT rights are needed
>> + * for the statement, ensure the leftover row remains visible.
>> + */
>> + if (perminfo->requiredPerms & ACL_SELECT)
>> + {
>> + List *select_permissive_policies;
>> + List *select_restrictive_policies;
>> +
>> + get_policies_for_relation(rel, CMD_SELECT, user_id,
>> + &select_permissive_policies,
>> + &select_restrictive_policies);
>> + add_with_check_options(rel, rt_index,
>> + WCO_RLS_INSERT_CHECK,
>> + select_permissive_policies,
>> + select_restrictive_policies,
>> + withCheckOptions,
>> + hasSubLinks,
>> + true);
>> + }
>>
>> Thinking some more about this, I'm not so sure it's right. For a plain
>> INSERT, we apply SELECT policies if SELECT permissions are required,
>> which only happens if there is a RETURNING clause. The reason is to
>> ensure the user has rights to see the row returned by the RETURNING
>> clause. However, for the auxiliary INSERTS for leftover portions in
>> UPDATE/DELETE ... FOR PORTION OF, even if there is a RETURNING clause,
>> it will only return the updated/deleted row, not the leftover rows.
>> The command never reads the leftover rows, so why do we need the
>> leftover rows to satisfy the SELECT policies?
>
> I think you are right. I replaced that section with a comment.
>
> v5 attached.
>
> Yours,
>
> --
> Paul ~{:-)
> pj@illuminatedcomputing.com
> <v5-0001-Fix-RLS-checks-for-FOR-PORTION-OF-leftover-rows.patch>
Thanks Dean for the reviews.
Thanks Paul for updating the patch.
Actually, when I received v4, I was also working on addressing Dean’s comments.
I reviewed both v4 and v5, made some small adjustments, and here is v6:
* For the create_policy doc change, I changed “check new row” to “check leftover rows”.
* For the “SELECT rights” comment, I moved it into the block comment, because its position in v5 looked like a placeholder.
* For the tests, v4 and v5 added the new cases in the MERGE section. I added the test in a more direct section.
* For the tests, v4 and v5 granted INSERT privilege. V6 intentionally does not, because the test runs UPDATE/DELETE FOR PORTION OF, and INSERT privilege is not required for inserting leftover rows by design.
I also updated the commit message to add Paul as a co-author and Dean as a reviewer.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/Attachments:
[application/octet-stream] v6-0001-Fix-RLS-checks-for-FOR-PORTION-OF-leftover-rows.patch (17.4K, ../49247968-DB60-4FF3-892F-CF910E63ADBA@gmail.com/2-v6-0001-Fix-RLS-checks-for-FOR-PORTION-OF-leftover-rows.patch)
download | inline diff:
From 83e9c5bf761661068819f25ab91a6fd3c1297a27 Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <lic@highgo.com>
Date: Tue, 30 Jun 2026 15:22:29 +0800
Subject: [PATCH v6] Fix RLS checks for FOR PORTION OF leftover rows
UPDATE/DELETE FOR PORTION OF may insert leftover rows to preserve the
parts of the old row that are outside the target range. Those inserts go
through ExecInsert(), which checks RLS policies using
WCO_RLS_INSERT_CHECK.
However, the rewriter only added RLS WITH CHECK options for the original
statement command. For UPDATE, that meant only WCO_RLS_UPDATE_CHECK
options were available, so ExecInsert() skipped them. For DELETE, no RLS
WITH CHECK options were added at all. As a result, leftover rows could be
inserted even when they violated INSERT RLS policies.
Fix this by adding INSERT RLS WITH CHECK options for UPDATE/DELETE FOR
PORTION OF target relations. Also add regression coverage for both UPDATE
and DELETE, including cases where allowed leftovers still succeed and
disallowed leftovers are rejected.
Author: Chao Li <lic@highgo.com>
Co-authored-by: Paul A Jungwirth <pj@illuminatedcomputing.com>
Reviewed-by: Paul A Jungwirth <pj@illuminatedcomputing.com>
Reviewed-by: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
Reviewed-by: Dean Rasheed <dean.a.rasheed@gmail.com>
Discussion: https://postgr.es/m/6C34A987-AC50-4477-BD71-2D4AFEE1A589@gmail.com
Discussion: https://postgr.es/m/CAJTYsWWdeBkoH5g8D-k9LDw9ciqsMxb21EJSiFXAzP4J=XyxOQ@mail.gmail.com
---
doc/src/sgml/ref/create_policy.sgml | 32 +++++++++
doc/src/sgml/ref/delete.sgml | 3 +-
doc/src/sgml/ref/update.sgml | 3 +-
src/backend/rewrite/rowsecurity.c | 25 +++++++
src/test/regress/expected/for_portion_of.out | 75 ++++++++++++++++++++
src/test/regress/expected/rowsecurity.out | 39 +++++++++-
src/test/regress/sql/for_portion_of.sql | 58 +++++++++++++++
src/test/regress/sql/rowsecurity.sql | 33 ++++++++-
8 files changed, 264 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgmlindex d8a036739c0..0a1699185c9 100644--- a/doc/src/sgml/ref/create_policy.sgml+++ b/doc/src/sgml/ref/create_policy.sgml@@ -530,6 +530,26 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
<entry>Check new row</entry>
<entry>—</entry>
</row>
+ <row>+ <entry><command>UPDATE ... FOR PORTION OF</command></entry>+ <entry>+ Filter existing row & check new row+ </entry>+ <entry>+ Check leftover rows <footnote id="rls-fpo-leftover">+ <para>+ <literal>FOR PORTION OF</literal> re-inserts the portions of the+ affected row that fall outside the targeted range, to preserve them.+ These leftover rows are checked against the <command>INSERT</command>+ policy's <literal>WITH CHECK</literal> expression, even though no+ <literal>INSERT</literal> privilege is required to store them.+ </para>+ </footnote>+ </entry>+ <entry>Filter existing row</entry>+ <entry>Check new row</entry>+ <entry>—</entry>+ </row>
<row>
<entry><command>DELETE</command></entry>
<entry>
@@ -540,6 +560,18 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
<entry>—</entry>
<entry>Filter existing row</entry>
</row>
+ <row>+ <entry><command>DELETE ... FOR PORTION OF</command></entry>+ <entry>+ Filter existing row+ </entry>+ <entry>+ Check leftover rows <footnoteref linkend="rls-fpo-leftover"/>+ </entry>+ <entry>—</entry>+ <entry>—</entry>+ <entry>Filter existing row</entry>+ </row>
<row>
<entry><command>INSERT ... ON CONFLICT</command></entry>
<entry>
diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgmlindex ffdcd7fc4fa..3ab1f60525a 100644--- a/doc/src/sgml/ref/delete.sgml+++ b/doc/src/sgml/ref/delete.sgml@@ -97,7 +97,8 @@ DELETE FROM [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ *
When <literal>FOR PORTION OF</literal> is used, the secondary inserts do
not require <literal>INSERT</literal> privilege on the table. (This is
because conceptually no new information is being added; the inserted rows
- only preserve existing data about the untargeted time period.)+ only preserve existing data about the untargeted time period.) Row-level+ security INSERT policies are still checked for these leftover inserts.
</para>
</refsect1>
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgmlindex 21a8fd8b037..3175671d475 100644--- a/doc/src/sgml/ref/update.sgml+++ b/doc/src/sgml/ref/update.sgml@@ -101,7 +101,8 @@ UPDATE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
When <literal>FOR PORTION OF</literal> is used, the secondary inserts do
not require <literal>INSERT</literal> privilege on the table. (This is
because conceptually no new information is being added; the inserted rows
- only preserve existing data about the untargeted time period.)+ only preserve existing data about the untargeted time period.) Row-level+ security INSERT policies are still checked for these leftover inserts.
</para>
</refsect1>
diff --git a/src/backend/rewrite/rowsecurity.c b/src/backend/rewrite/rowsecurity.cindex e88a1bc1a89..46d88f79894 100644--- a/src/backend/rewrite/rowsecurity.c+++ b/src/backend/rewrite/rowsecurity.c@@ -393,6 +393,31 @@ get_row_security_policies(Query *root, RangeTblEntry *rte, int rt_index,
}
}
+ /*+ * UPDATE/DELETE FOR PORTION OF may insert leftover rows to preserve the+ * portions of the old row not covered by the target range. Those hidden+ * inserts go through ExecInsert(), so they need the same INSERT RLS WITH+ * CHECK options as ordinary INSERTs. SELECT rights are never needed for+ * the leftover rows, because they are not considered by RETURNING.+ */+ if (root->forPortionOf != NULL && rt_index == root->resultRelation &&+ (commandType == CMD_UPDATE || commandType == CMD_DELETE))+ {+ List *insert_permissive_policies;+ List *insert_restrictive_policies;++ get_policies_for_relation(rel, CMD_INSERT, user_id,+ &insert_permissive_policies,+ &insert_restrictive_policies);+ add_with_check_options(rel, rt_index,+ WCO_RLS_INSERT_CHECK,+ insert_permissive_policies,+ insert_restrictive_policies,+ withCheckOptions,+ hasSubLinks,+ false);+ }+
/*
* FOR MERGE, we fetch policies for UPDATE, DELETE and INSERT (and ALL)
* and set them up so that we can enforce the appropriate policy depending
diff --git a/src/test/regress/expected/for_portion_of.out b/src/test/regress/expected/for_portion_of.outindex a050fc2dadf..271282c2d3b 100644--- a/src/test/regress/expected/for_portion_of.out+++ b/src/test/regress/expected/for_portion_of.out@@ -2561,4 +2561,79 @@ SELECT * FROM fpo_cursed;
(1 row)
DROP TABLE fpo_cursed;
+-- UPDATE/DELETE FOR PORTION OF leftover rows must satisfy RLS INSERT checks.+CREATE ROLE regress_fpo_rls;+CREATE TABLE fpo_rls (+ id int,+ valid_at int4range+);+ALTER TABLE fpo_rls ENABLE ROW LEVEL SECURITY;+CREATE POLICY fpo_rls_select ON fpo_rls+ FOR SELECT TO regress_fpo_rls+ USING (true);+CREATE POLICY fpo_rls_update ON fpo_rls+ FOR UPDATE TO regress_fpo_rls+ USING (lower(valid_at) < 50)+ WITH CHECK (lower(valid_at) < 50);+CREATE POLICY fpo_rls_delete ON fpo_rls+ FOR DELETE TO regress_fpo_rls+ USING (lower(valid_at) < 50);+CREATE POLICY fpo_rls_insert ON fpo_rls+ FOR INSERT TO regress_fpo_rls+ WITH CHECK (lower(valid_at) < 50);+GRANT SELECT, UPDATE, DELETE ON fpo_rls TO regress_fpo_rls;+INSERT INTO fpo_rls VALUES (1, '[10,100)');+SET ROLE regress_fpo_rls;+UPDATE fpo_rls+ FOR PORTION OF valid_at FROM 30 TO 100+ SET id = 2;+RESET ROLE;+SELECT * FROM fpo_rls ORDER BY valid_at;+ id | valid_at +----+----------+ 1 | [10,30)+ 2 | [30,100)+(2 rows)++TRUNCATE fpo_rls;+INSERT INTO fpo_rls VALUES (1, '[10,100)');+SET ROLE regress_fpo_rls;+DELETE FROM fpo_rls+ FOR PORTION OF valid_at FROM 30 TO 100;+RESET ROLE;+SELECT * FROM fpo_rls ORDER BY valid_at;+ id | valid_at +----+----------+ 1 | [10,30)+(1 row)++TRUNCATE fpo_rls;+INSERT INTO fpo_rls VALUES (1, '[10,100)');+SET ROLE regress_fpo_rls;+UPDATE fpo_rls+ FOR PORTION OF valid_at FROM 30 TO 70+ SET id = 2;+ERROR: new row violates row-level security policy for table "fpo_rls"+RESET ROLE;+SELECT * FROM fpo_rls ORDER BY valid_at;+ id | valid_at +----+----------+ 1 | [10,100)+(1 row)++TRUNCATE fpo_rls;+INSERT INTO fpo_rls VALUES (1, '[10,100)');+SET ROLE regress_fpo_rls;+DELETE FROM fpo_rls+ FOR PORTION OF valid_at FROM 30 TO 70;+ERROR: new row violates row-level security policy for table "fpo_rls"+RESET ROLE;+SELECT * FROM fpo_rls ORDER BY valid_at;+ id | valid_at +----+----------+ 1 | [10,100)+(1 row)++DROP TABLE fpo_rls;+DROP ROLE regress_fpo_rls;
RESET datestyle;
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.outindex 3a5e82c35bd..f42085e53a0 100644--- a/src/test/regress/expected/rowsecurity.out+++ b/src/test/regress/expected/rowsecurity.out@@ -68,6 +68,20 @@ CREATE POLICY upd_pol ON rls_test_tgt FOR UPDATE
WITH CHECK (rls_test_policy_fn('UPDATE CHECK on rls_test_tgt', rls_test_tgt));
CREATE POLICY del_pol ON rls_test_tgt FOR DELETE
USING (rls_test_policy_fn('DELETE USING on rls_test_tgt', rls_test_tgt));
+-- setup temporal target table (for FOR PORTION OF operations)+CREATE TABLE rls_test_fpo_tgt (a int, b text, valid_at int4range);+ALTER TABLE rls_test_fpo_tgt ENABLE ROW LEVEL SECURITY;+GRANT SELECT, UPDATE, DELETE ON rls_test_fpo_tgt TO public;+INSERT INTO rls_test_fpo_tgt VALUES (1, 'fpo a', '[1,10)');+CREATE POLICY sel_pol ON rls_test_fpo_tgt FOR SELECT+ USING (rls_test_policy_fn('SELECT USING on rls_test_fpo_tgt', rls_test_fpo_tgt));+CREATE POLICY ins_pol ON rls_test_fpo_tgt FOR INSERT+ WITH CHECK (rls_test_policy_fn('INSERT CHECK on rls_test_fpo_tgt', rls_test_fpo_tgt));+CREATE POLICY upd_pol ON rls_test_fpo_tgt FOR UPDATE+ USING (rls_test_policy_fn('UPDATE USING on rls_test_fpo_tgt', rls_test_fpo_tgt))+ WITH CHECK (rls_test_policy_fn('UPDATE CHECK on rls_test_fpo_tgt', rls_test_fpo_tgt));+CREATE POLICY del_pol ON rls_test_fpo_tgt FOR DELETE+ USING (rls_test_policy_fn('DELETE USING on rls_test_fpo_tgt', rls_test_fpo_tgt));
-- test policies applied to regress_rls_bob
SET SESSION AUTHORIZATION regress_rls_bob;
-- SELECT, COPY ... TO, and TABLE should only apply SELECT USING policy clause
@@ -155,6 +169,19 @@ NOTICE: SELECT USING on rls_test_tgt.(1,"tgt d","TGT D")
1 | tgt d | TGT D
(1 row)
+-- UPDATE ... FOR PORTION OF should also apply SELECT USING policy clauses to+-- the old and new rows, and INSERT CHECK policy clauses to leftover rows.+BEGIN;+UPDATE rls_test_fpo_tgt+ FOR PORTION OF valid_at FROM 3 TO 7+ SET b = 'fpo b';+NOTICE: UPDATE USING on rls_test_fpo_tgt.(1,"fpo a","[1,10)")+NOTICE: SELECT USING on rls_test_fpo_tgt.(1,"fpo a","[1,10)")+NOTICE: UPDATE CHECK on rls_test_fpo_tgt.(1,"fpo b","[3,7)")+NOTICE: SELECT USING on rls_test_fpo_tgt.(1,"fpo b","[3,7)")+NOTICE: INSERT CHECK on rls_test_fpo_tgt.(1,"fpo a","[1,3)")+NOTICE: INSERT CHECK on rls_test_fpo_tgt.(1,"fpo a","[7,10)")+ROLLBACK;
-- DELETE without WHERE or RETURNING should only apply DELETE USING policy clause
BEGIN; DELETE FROM rls_test_tgt; ROLLBACK;
NOTICE: DELETE USING on rls_test_tgt.(1,"tgt d","TGT D")
@@ -170,6 +197,16 @@ NOTICE: SELECT USING on rls_test_tgt.(1,"tgt d","TGT D")
1 | tgt d | TGT D
(1 row)
+-- DELETE ... FOR PORTION OF should also apply SELECT USING policy clauses to+-- the old row, and INSERT CHECK policy clauses to leftover rows.+BEGIN;+DELETE FROM rls_test_fpo_tgt+ FOR PORTION OF valid_at FROM 3 TO 7;+NOTICE: DELETE USING on rls_test_fpo_tgt.(1,"fpo a","[1,10)")+NOTICE: SELECT USING on rls_test_fpo_tgt.(1,"fpo a","[1,10)")+NOTICE: INSERT CHECK on rls_test_fpo_tgt.(1,"fpo a","[1,3)")+NOTICE: INSERT CHECK on rls_test_fpo_tgt.(1,"fpo a","[7,10)")+ROLLBACK;
-- INSERT ... ON CONFLICT DO NOTHING with an arbiter clause should apply
-- INSERT CHECK and SELECT USING policy clauses (to new value, whether it
-- conflicts or not)
@@ -332,7 +369,7 @@ NOTICE: DELETE USING on rls_test_tgt.(1,"tgt c","TGT C")
-- Tidy up
RESET SESSION AUTHORIZATION;
-DROP TABLE rls_test_src, rls_test_tgt;+DROP TABLE rls_test_src, rls_test_tgt, rls_test_fpo_tgt;
DROP FUNCTION rls_test_tgt_set_c;
DROP FUNCTION rls_test_policy_fn;
-- BASIC Row-Level Security Scenario
diff --git a/src/test/regress/sql/for_portion_of.sql b/src/test/regress/sql/for_portion_of.sqlindex a1ee1d5e501..f48644347d1 100644--- a/src/test/regress/sql/for_portion_of.sql+++ b/src/test/regress/sql/for_portion_of.sql@@ -1676,4 +1676,62 @@ ROLLBACK;
SELECT * FROM fpo_cursed;
DROP TABLE fpo_cursed;
+-- UPDATE/DELETE FOR PORTION OF leftover rows must satisfy RLS INSERT checks.+CREATE ROLE regress_fpo_rls;+CREATE TABLE fpo_rls (+ id int,+ valid_at int4range+);+ALTER TABLE fpo_rls ENABLE ROW LEVEL SECURITY;+CREATE POLICY fpo_rls_select ON fpo_rls+ FOR SELECT TO regress_fpo_rls+ USING (true);+CREATE POLICY fpo_rls_update ON fpo_rls+ FOR UPDATE TO regress_fpo_rls+ USING (lower(valid_at) < 50)+ WITH CHECK (lower(valid_at) < 50);+CREATE POLICY fpo_rls_delete ON fpo_rls+ FOR DELETE TO regress_fpo_rls+ USING (lower(valid_at) < 50);+CREATE POLICY fpo_rls_insert ON fpo_rls+ FOR INSERT TO regress_fpo_rls+ WITH CHECK (lower(valid_at) < 50);+GRANT SELECT, UPDATE, DELETE ON fpo_rls TO regress_fpo_rls;++INSERT INTO fpo_rls VALUES (1, '[10,100)');+SET ROLE regress_fpo_rls;+UPDATE fpo_rls+ FOR PORTION OF valid_at FROM 30 TO 100+ SET id = 2;+RESET ROLE;+SELECT * FROM fpo_rls ORDER BY valid_at;++TRUNCATE fpo_rls;+INSERT INTO fpo_rls VALUES (1, '[10,100)');+SET ROLE regress_fpo_rls;+DELETE FROM fpo_rls+ FOR PORTION OF valid_at FROM 30 TO 100;+RESET ROLE;+SELECT * FROM fpo_rls ORDER BY valid_at;++TRUNCATE fpo_rls;+INSERT INTO fpo_rls VALUES (1, '[10,100)');+SET ROLE regress_fpo_rls;+UPDATE fpo_rls+ FOR PORTION OF valid_at FROM 30 TO 70+ SET id = 2;+RESET ROLE;+SELECT * FROM fpo_rls ORDER BY valid_at;++TRUNCATE fpo_rls;+INSERT INTO fpo_rls VALUES (1, '[10,100)');+SET ROLE regress_fpo_rls;+DELETE FROM fpo_rls+ FOR PORTION OF valid_at FROM 30 TO 70;+RESET ROLE;+SELECT * FROM fpo_rls ORDER BY valid_at;++DROP TABLE fpo_rls;+DROP ROLE regress_fpo_rls;+
RESET datestyle;
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sqlindex 6b3566271df..0bd24d1028b 100644--- a/src/test/regress/sql/rowsecurity.sql+++ b/src/test/regress/sql/rowsecurity.sql@@ -85,6 +85,22 @@ CREATE POLICY upd_pol ON rls_test_tgt FOR UPDATE
CREATE POLICY del_pol ON rls_test_tgt FOR DELETE
USING (rls_test_policy_fn('DELETE USING on rls_test_tgt', rls_test_tgt));
+-- setup temporal target table (for FOR PORTION OF operations)+CREATE TABLE rls_test_fpo_tgt (a int, b text, valid_at int4range);+ALTER TABLE rls_test_fpo_tgt ENABLE ROW LEVEL SECURITY;+GRANT SELECT, UPDATE, DELETE ON rls_test_fpo_tgt TO public;+INSERT INTO rls_test_fpo_tgt VALUES (1, 'fpo a', '[1,10)');++CREATE POLICY sel_pol ON rls_test_fpo_tgt FOR SELECT+ USING (rls_test_policy_fn('SELECT USING on rls_test_fpo_tgt', rls_test_fpo_tgt));+CREATE POLICY ins_pol ON rls_test_fpo_tgt FOR INSERT+ WITH CHECK (rls_test_policy_fn('INSERT CHECK on rls_test_fpo_tgt', rls_test_fpo_tgt));+CREATE POLICY upd_pol ON rls_test_fpo_tgt FOR UPDATE+ USING (rls_test_policy_fn('UPDATE USING on rls_test_fpo_tgt', rls_test_fpo_tgt))+ WITH CHECK (rls_test_policy_fn('UPDATE CHECK on rls_test_fpo_tgt', rls_test_fpo_tgt));+CREATE POLICY del_pol ON rls_test_fpo_tgt FOR DELETE+ USING (rls_test_policy_fn('DELETE USING on rls_test_fpo_tgt', rls_test_fpo_tgt));+
-- test policies applied to regress_rls_bob
SET SESSION AUTHORIZATION regress_rls_bob;
@@ -114,6 +130,14 @@ UPDATE rls_test_tgt SET b = 'tgt b';
UPDATE rls_test_tgt SET b = 'tgt c' WHERE a = 1;
UPDATE rls_test_tgt SET b = 'tgt d' RETURNING *;
+-- UPDATE ... FOR PORTION OF should also apply SELECT USING policy clauses to+-- the old and new rows, and INSERT CHECK policy clauses to leftover rows.+BEGIN;+UPDATE rls_test_fpo_tgt+ FOR PORTION OF valid_at FROM 3 TO 7+ SET b = 'fpo b';+ROLLBACK;+
-- DELETE without WHERE or RETURNING should only apply DELETE USING policy clause
BEGIN; DELETE FROM rls_test_tgt; ROLLBACK;
@@ -121,6 +145,13 @@ BEGIN; DELETE FROM rls_test_tgt; ROLLBACK;
BEGIN; DELETE FROM rls_test_tgt WHERE a = 1; ROLLBACK;
DELETE FROM rls_test_tgt RETURNING *;
+-- DELETE ... FOR PORTION OF should also apply SELECT USING policy clauses to+-- the old row, and INSERT CHECK policy clauses to leftover rows.+BEGIN;+DELETE FROM rls_test_fpo_tgt+ FOR PORTION OF valid_at FROM 3 TO 7;+ROLLBACK;+
-- INSERT ... ON CONFLICT DO NOTHING with an arbiter clause should apply
-- INSERT CHECK and SELECT USING policy clauses (to new value, whether it
-- conflicts or not)
@@ -192,7 +223,7 @@ MERGE INTO rls_test_tgt t USING rls_test_src s ON t.a = s.a
-- Tidy up
RESET SESSION AUTHORIZATION;
-DROP TABLE rls_test_src, rls_test_tgt;+DROP TABLE rls_test_src, rls_test_tgt, rls_test_fpo_tgt;
DROP FUNCTION rls_test_tgt_set_c;
DROP FUNCTION rls_test_policy_fn;
--
2.50.1 (Apple Git-155)
=
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: li.evan.chao@gmail.com, pj@illuminatedcomputing.com, dean.a.rasheed@gmail.com, ayushtiwari.slg01@gmail.com, peter@eisentraut.org
Subject: Re: Fix RLS checks for UPDATE/DELETE FOR PORTION OF leftover rows
In-Reply-To: <49247968-DB60-4FF3-892F-CF910E63ADBA@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox