From: Chao Li <li.evan.chao@gmail.com>
To: Paul A Jungwirth <pj@illuminatedcomputing.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: Fri, 3 Jul 2026 05:52:03 +0800
Message-ID: <20DF964A-C291-491A-9126-62753DCBD7CE@gmail.com> (raw)
In-Reply-To: <CA+renyWYcQ=Yc1zQJ2UzLDF2cdHBHto-dRHw9s9EAA111ApEqQ@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>
> On Jul 3, 2026, at 02:06, Paul A Jungwirth <pj@illuminatedcomputing.com> wrote:
>
>> Is this the right way to do these checks? I'll try to look into it a
>> bit more today, but does this cause problems when an UPDATE *doesn't*
>> create leftovers?
>
> I did some extra testing but couldn't find any problems. I looked at
> INSERT policies when FOR PORTION OF exactly matches a row's bounds (so
> the row is updated but there are no leftovers), and also INSERT
> policies that include sublinks.
>
>> We should update the documentation as well. I think it should point
>> out the contrast between ACLs and RLS here.
>
> Here is a v2 with a couple lines of documentation added.
>
> Yours,
>
> --
> Paul ~{:-)
> pj@illuminatedcomputing.com
> <v2-0001-Fix-RLS-checks-for-FOR-PORTION-OF-leftover-rows.patch>
Thanks for testing and updating the doc. I agree the docs need to be updated.
I’d rephrase this a little bit:
```
RLS policies for inserting temporal leftovers are still checked.
```
Only INSERT policies will be checked, so I changed it to:
```
Row-level security INSERT policies are still checked for these leftover inserts.
```
PFA v3, only this tiny doc change.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/Attachments:
[application/octet-stream] v3-0001-Fix-RLS-checks-for-FOR-PORTION-OF-leftover-rows.patch (9.4K, ../20DF964A-C291-491A-9126-62753DCBD7CE@gmail.com/2-v3-0001-Fix-RLS-checks-for-FOR-PORTION-OF-leftover-rows.patch)
download | inline diff:
From 71f8e0d287b7f900b5f98a9f6c9da55117895195 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 v3] 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>
Reviewed-by: Paul A Jungwirth <pj@illuminatedcomputing.com>
Reviewed-by: Ayush Tiwari <ayushtiwari.slg01@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/delete.sgml | 3 +-
doc/src/sgml/ref/update.sgml | 3 +-
src/backend/rewrite/rowsecurity.c | 45 ++++++++++++
src/test/regress/expected/for_portion_of.out | 75 ++++++++++++++++++++
src/test/regress/sql/for_portion_of.sql | 58 +++++++++++++++
5 files changed, 182 insertions(+), 2 deletions(-)
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..cacf30f25a0 100644--- a/src/backend/rewrite/rowsecurity.c+++ b/src/backend/rewrite/rowsecurity.c@@ -393,6 +393,51 @@ 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.+ */+ 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);++ /*+ * 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);+ }+ }+
/*
* 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 207e370627e..5a0fb84f357 100644--- a/src/test/regress/expected/for_portion_of.out+++ b/src/test/regress/expected/for_portion_of.out@@ -2488,4 +2488,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/sql/for_portion_of.sql b/src/test/regress/sql/for_portion_of.sqlindex a3c41abf7b7..3b1653df074 100644--- a/src/test/regress/sql/for_portion_of.sql+++ b/src/test/regress/sql/for_portion_of.sql@@ -1619,4 +1619,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;
--
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, ayushtiwari.slg01@gmail.com, peter@eisentraut.org
Subject: Re: Fix RLS checks for UPDATE/DELETE FOR PORTION OF leftover rows
In-Reply-To: <20DF964A-C291-491A-9126-62753DCBD7CE@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