pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedFix RLS checks for UPDATE/DELETE FOR PORTION OF leftover rows
17+ messages / 5 participants
[nested] [flat]
* Fix RLS checks for UPDATE/DELETE FOR PORTION OF leftover rows
@ 2026-06-30 07:45 Chao Li <li.evan.chao@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Chao Li @ 2026-06-30 07:45 UTC (permalink / raw)
To: pgsql-hackers; +Cc: Peter Eisentraut <peter@eisentraut.org>; Paul A Jungwirth <pj@illuminatedcomputing.com>
Hi,
While revisiting “[8e72d914c] Add UPDATE/DELETE FOR PORTION OF”, I found a new issue where inserting leftover rows may skip row-level security checks.
This is a repro:
1. Prepare a role and a table with an int4range column, then add policies that require the lower bound to be less than 50:
```
evantest=# create role u;
CREATE ROLE
evantest=# create table t (id int, valid_at int4range);
CREATE TABLE
evantest=# alter table t enable row level security;
ALTER TABLE
evantest=# alter table t force row level security;
ALTER TABLE
evantest=# create policy t_sel on t for select to u using (true);
CREATE POLICY
evantest=# create policy t_upd on t for update to u using (lower(valid_at)<50) with check (lower(valid_at)<50);
CREATE POLICY
evantest=# create policy t_del on t for delete to u using (lower(valid_at)<50);
CREATE POLICY
evantest=# create policy t_ins on t for insert to u with check (lower(valid_at)<50);
CREATE POLICY
evantest=# grant select, update, delete, insert on t to u;
GRANT
evantest=# insert into t values (1, '[10,100)');
INSERT 0 1
evantest=# set role u;
SET
```
2. Update the row:
```
evantest=> update t for portion of valid_at from 30 to 70 set id=2;
UPDATE 1
evantest=> select * from t;
id | valid_at
----+----------
2 | [30,70)
1 | [10,30)
1 | [70,100)
(3 rows)
```
Here, the right leftover [70,100) was inserted, even though the INSERT policy requires lower(valid_at) < 50. If the policy were honored, the update should fail because the leftover insert violates policy t_ins.
After tracing the update statement, I think the problem is as follows.
1.In the rewrite phase, get_policies_for_relation() gets only the UPDATE policy, because commandType is CMD_UPDATE:
```
/*
* For SELECT, UPDATE and DELETE, add security quals to enforce the USING
* policies. These security quals control access to existing table rows.
* Restrictive policies are combined together using AND, and permissive
* policies are combined together using OR.
*/
get_policies_for_relation(rel, commandType, user_id, &permissive_policies,
&restrictive_policies);
…..
/*
* For INSERT and UPDATE, add withCheckOptions to verify that any new
* records added are consistent with the security policies. This will use
* each policy's WITH CHECK clause, or its USING clause if no explicit
* WITH CHECK clause is defined.
*/
if (commandType == CMD_INSERT || commandType == CMD_UPDATE)
{
/* This should be the target relation */
Assert(rt_index == root->resultRelation);
add_with_check_options(rel, rt_index,
commandType == CMD_INSERT ?
WCO_RLS_INSERT_CHECK : WCO_RLS_UPDATE_CHECK,
permissive_policies,
restrictive_policies,
withCheckOptions,
hasSubLinks,
false);
```
2. In ExecForPortionOfLeftovers(), before inserting a leftover row, it temporarily saves mtstate->operation and changes it to CMD_INSERT:
```
/*
* Save some mtstate things so we can restore them below. XXX:
* Should we create our own ModifyTableState instead?
*/
oldOperation = mtstate->operation;
mtstate->operation = CMD_INSERT;
oldTcs = mtstate->mt_transition_capture;
```
3. Then it calls ExecInsert() with that state to insert the leftover row:
```
/*
* The standard says that each temporal leftover should execute its
* own INSERT statement, firing all statement and row triggers, but
* skipping insert permission checks. Therefore we give each insert
* its own transition table. If we just push & pop a new trigger level
* for each insert, we get exactly what we need.
*
* We have to make sure that the inserts don't add to the ROW_COUNT
* diagnostic or the command tag, so we pass false for canSetTag.
*/
AfterTriggerBeginQuery();
ExecSetupTransitionCaptureState(mtstate, estate);
fireBSTriggers(mtstate);
ExecInsert(context, resultRelInfo, leftoverSlot, false, NULL, NULL);
fireASTriggers(mtstate);
AfterTriggerEndQuery(estate);
```
4. In ExecInsert(), wco_kind is chosen from mtstate->operation. Since it is now CMD_INSERT, the value becomes WCO_RLS_INSERT_CHECK:
```
/*
* Check any RLS WITH CHECK policies.
*
* Normally we should check INSERT policies. But if the insert is the
* result of a partition key update that moved the tuple to a new
* partition, we should instead check UPDATE policies, because we are
* executing policies defined on the target table, and not those
* defined on the child partitions.
*
* If we're running MERGE, we refer to the action that we're executing
* to know if we're doing an INSERT or UPDATE to a partition table.
*/
if (mtstate->operation == CMD_UPDATE)
wco_kind = WCO_RLS_UPDATE_CHECK;
else if (mtstate->operation == CMD_MERGE)
wco_kind = (mtstate->mt_merge_action->mas_action->commandType == CMD_UPDATE) ?
WCO_RLS_UPDATE_CHECK : WCO_RLS_INSERT_CHECK;
else
wco_kind = WCO_RLS_INSERT_CHECK;
```
5. With this wco_kind, it calls ExecWithCheckOptions():
```
/*
* ExecWithCheckOptions() will skip any WCOs which are not of the kind
* we are looking for at this point.
*/
if (resultRelInfo->ri_WithCheckOptions != NIL)
ExecWithCheckOptions(wco_kind, resultRelInfo, slot, estate);
```
6. In ExecWithCheckOptions(), it loops over all WithCheckOptions built during rewrite:
```
/* Check each of the constraints */
forboth(l1, resultRelInfo->ri_WithCheckOptions,
l2, resultRelInfo->ri_WithCheckOptionExprs)
{
WithCheckOption *wco = (WithCheckOption *) lfirst(l1);
ExprState *wcoExpr = (ExprState *) lfirst(l2);
/*
* Skip any WCOs which are not the kind we are looking for at this
* time.
*/
if (wco->kind != kind)
continue;
```
In this loop, wco->kind is WCO_RLS_UPDATE_CHECK, but kind is WCO_RLS_INSERT_CHECK, so the check is skipped and the right leftover row [70,100) is inserted.
The same problem exists for DELETE as well:
```
evantest=> delete from t for portion of valid_at from 30 to 70;
DELETE 1
evantest=> select * from t;
id | valid_at
----+----------
1 | [10,30)
1 | [70,100)
(2 rows)
```
For DELETE, in code snippet 1 above, add_with_check_options() is not called at all because commandType is CMD_DELETE. Then, in code snippet 5, resultRelInfo->ri_WithCheckOptions is NULL, so ExecWithCheckOptions() is not called.
I checked the docs, and they seem to mention only that INSERT privilege is not required for the hidden insertion of leftover rows. But I think RLS should still be honored, because otherwise the policies are violated. For example, directly inserting [70,100) is blocked by policy t_ins, but a user can work around that by inserting [1,100) and then updating [30,70), which seems like a security hole.
To fix this, I think the rewriter should load INSERT policies for UPDATE and DELETE operations when parse->forPortionOfexists, since UPDATE/DELETE FOR PORTION OF may insert leftover rows.
See the attached patch for details. With the fix, both the update and delete above now fail:
```
evantest=> update t for portion of valid_at from 30 to 70 set id=2;
ERROR: new row violates row-level security policy for table "t"
evantest=> delete from t for portion of valid_at from 30 to 70;
ERROR: new row violates row-level security policy for table "t"
```
BTW, I just saw that the v19 branch has been cut and HEAD is now v20, so it looks like this patch will need to be back-patched if accepted.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Fix RLS checks for UPDATE/DELETE FOR PORTION OF leftover rows
@ 2026-06-30 07:51 Ayush Tiwari <ayushtiwari.slg01@gmail.com>
parent: Chao Li <li.evan.chao@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Ayush Tiwari @ 2026-06-30 07:51 UTC (permalink / raw)
To: Chao Li <li.evan.chao@gmail.com>; +Cc: pgsql-hackers; Peter Eisentraut <peter@eisentraut.org>; Paul A Jungwirth <pj@illuminatedcomputing.com>
Hi,
On Tue, 30 Jun 2026 at 13:16, Chao Li <li.evan.chao@gmail.com> wrote:
> Hi,
>
> While revisiting “[8e72d914c] Add UPDATE/DELETE FOR PORTION OF”, I found a
> new issue where inserting leftover rows may skip row-level security checks.
>
Please see if it is the same as this: PostgreSQL: Enforce INSERT RLS
checks for FOR PORTION OF leftovers?
<https://www.postgresql.org/message-id/flat/CAJTYsWWdeBkoH5g8D-k9LDw9ciqsMxb21EJSiFXAzP4J=XyxOQ@mail....;
If yes, it is already present in the PG 19 open list.
Regards,
Ayush
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Fix RLS checks for UPDATE/DELETE FOR PORTION OF leftover rows
@ 2026-06-30 08:00 Chao Li <li.evan.chao@gmail.com>
parent: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Chao Li @ 2026-06-30 08:00 UTC (permalink / raw)
To: Ayush Tiwari <ayushtiwari.slg01@gmail.com>; +Cc: pgsql-hackers; Peter Eisentraut <peter@eisentraut.org>; Paul A Jungwirth <pj@illuminatedcomputing.com>
> On Jun 30, 2026, at 15:51, Ayush Tiwari <ayushtiwari.slg01@gmail.com> wrote:
>
> Hi,
>
> On Tue, 30 Jun 2026 at 13:16, Chao Li <li.evan.chao@gmail.com> wrote:
> Hi,
>
> While revisiting “[8e72d914c] Add UPDATE/DELETE FOR PORTION OF”, I found a new issue where inserting leftover rows may skip row-level security checks.
>
> Please see if it is the same as this: PostgreSQL: Enforce INSERT RLS checks for FOR PORTION OF leftovers?
>
> If yes, it is already present in the PG 19 open list.
>
> Regards,
> Ayush
Thanks for pointing that out. I didn’t notice that thread.
Yes, that’s the same issue. I saw Paul wrote this there:
```
Skipping the RLS checks to insert the leftovers seems like the correct
behavior to me, since we are skipping the ACL checks (per the
standard). Shouldn't it be consistent?
I think the reason we skip the checks is that semantically, the
leftovers aren't changing anything: they are preserving the history
that is already there.
```
That explains why the ACL checks are skipped as stated in the doc, but I don’t think the same reasoning should apply to RLS checks. As I explained in my patch email, for example, directly inserting [70,100) is blocked by policy t_ins, but a user can work around that by inserting [1,100) and then updating [30,70), which seems like a security hole.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Fix RLS checks for UPDATE/DELETE FOR PORTION OF leftover rows
@ 2026-06-30 08:07 Chao Li <li.evan.chao@gmail.com>
parent: Chao Li <li.evan.chao@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Chao Li @ 2026-06-30 08:07 UTC (permalink / raw)
To: Ayush Tiwari <ayushtiwari.slg01@gmail.com>; +Cc: pgsql-hackers; Peter Eisentraut <peter@eisentraut.org>; Paul A Jungwirth <pj@illuminatedcomputing.com>
> On Jun 30, 2026, at 16:00, Chao Li <li.evan.chao@gmail.com> wrote:
>
>
>
>> On Jun 30, 2026, at 15:51, Ayush Tiwari <ayushtiwari.slg01@gmail.com> wrote:
>>
>> Hi,
>>
>> On Tue, 30 Jun 2026 at 13:16, Chao Li <li.evan.chao@gmail.com> wrote:
>> Hi,
>>
>> While revisiting “[8e72d914c] Add UPDATE/DELETE FOR PORTION OF”, I found a new issue where inserting leftover rows may skip row-level security checks.
>>
>> Please see if it is the same as this: PostgreSQL: Enforce INSERT RLS checks for FOR PORTION OF leftovers?
>>
>> If yes, it is already present in the PG 19 open list.
>>
>> Regards,
>> Ayush
>
> Thanks for pointing that out. I didn’t notice that thread.
>
> Yes, that’s the same issue. I saw Paul wrote this there:
> ```
> Skipping the RLS checks to insert the leftovers seems like the correct
> behavior to me, since we are skipping the ACL checks (per the
> standard). Shouldn't it be consistent?
> I think the reason we skip the checks is that semantically, the
> leftovers aren't changing anything: they are preserving the history
> that is already there.
> ```
>
> That explains why the ACL checks are skipped as stated in the doc, but I don’t think the same reasoning should apply to RLS checks. As I explained in my patch email, for example, directly inserting [70,100) is blocked by policy t_ins, but a user can work around that by inserting [1,100) and then updating [30,70), which seems like a security hole.
>
I just noticed that I forgot the attached again.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Attachments:
[application/octet-stream] v1-0001-Fix-RLS-checks-for-FOR-PORTION-OF-leftover-rows.patch (7.7K, ../../B4648790-5945-4C73-AF5A-8CE8A812527E@gmail.com/2-v1-0001-Fix-RLS-checks-for-FOR-PORTION-OF-leftover-rows.patch)
download | inline diff:
From 22cd4f3ee675ae943b8445df05d9b1d6e5be2a01 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 v1] 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:
Discussion: https://postgr.es/m/6C34A987-AC50-4477-BD71-2D4AFEE1A589@gmail.com
Discussion: https://postgr.es/m/CAJTYsWWdeBkoH5g8D-k9LDw9ciqsMxb21EJSiFXAzP4J=XyxOQ@mail.gmail.com
---
src/backend/rewrite/rowsecurity.c | 45 ++++++++++++
src/test/regress/expected/for_portion_of.out | 75 ++++++++++++++++++++
src/test/regress/sql/for_portion_of.sql | 58 +++++++++++++++
3 files changed, 178 insertions(+)
diff --git a/src/backend/rewrite/rowsecurity.c b/src/backend/rewrite/rowsecurity.c
index 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.out
index 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.sql
index 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)
=
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Fix RLS checks for UPDATE/DELETE FOR PORTION OF leftover rows
@ 2026-07-02 14:52 Paul A Jungwirth <pj@illuminatedcomputing.com>
parent: Chao Li <li.evan.chao@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Paul A Jungwirth @ 2026-07-02 14:52 UTC (permalink / raw)
To: Chao Li <li.evan.chao@gmail.com>; +Cc: Ayush Tiwari <ayushtiwari.slg01@gmail.com>; pgsql-hackers; Peter Eisentraut <peter@eisentraut.org>
On Tue, Jun 30, 2026 at 1:08 AM Chao Li <li.evan.chao@gmail.com> wrote:
>
> > On Jun 30, 2026, at 16:00, Chao Li <li.evan.chao@gmail.com> wrote:
> >
> > Yes, that’s the same issue. I saw Paul wrote this there:
> > ```
> > Skipping the RLS checks to insert the leftovers seems like the correct
> > behavior to me, since we are skipping the ACL checks (per the
> > standard). Shouldn't it be consistent?
> > I think the reason we skip the checks is that semantically, the
> > leftovers aren't changing anything: they are preserving the history
> > that is already there.
> > ```
> >
> > That explains why the ACL checks are skipped as stated in the doc, but I don’t think the same reasoning should apply to RLS checks. As I explained in my patch email, for example, directly inserting [70,100) is blocked by policy t_ins, but a user can work around that by inserting [1,100) and then updating [30,70), which seems like a security hole.
> >
>
> I just noticed that I forgot the attached again.
Thank you for working on this!
I still think it is a weird inconsistency to check RLS policies when
we're skipping ACLs, but there are a couple things I like about it:
- It gives the user more choice. They can use the policy to make
whatever decision they want. So enforcing the policy offers a superset
of the functionality of not enforcing it (but see below).
- RLS is maybe more general than just permissions. Your examples
aren't really about permissions. You can write policies that are more
like CHECK constraints, but with selective enforcement. (OTOH is that
really a good idea?)
One thing I'm concerned about is this: if we apply INSERT policies for
UPDATE FOR PORTION OF, how do we support users who want to forbid
*adding new entities* but allow *changing the history of existing
entities*? By applying INSERT policies, we are blocking them from
both. So maybe this doesn't really offer a superset of functionality
after all. And I think my use case is fairly realistic. More realistic
than wanting to allow updates but not if they update only part of the
history.
If a policy had a way of discerning when an INSERT is for a temporal
leftover, then there would be no problem: the user could decide to let
those through. But I don't think we have a way to do that, do we? If
there might be a way to do it in the future, that would also alleviate
my concern here. I already have patches to expose FOR PORTION OF
parameters in TG_* variables. Maybe a policy could use something
similar.
@@ -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);
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?
We should update the documentation as well. I think it should point
out the contrast between ACLs and RLS here.
Yours,
--
Paul ~{:-)
pj@illuminatedcomputing.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Fix RLS checks for UPDATE/DELETE FOR PORTION OF leftover rows
@ 2026-07-02 18:06 Paul A Jungwirth <pj@illuminatedcomputing.com>
parent: Paul A Jungwirth <pj@illuminatedcomputing.com>
0 siblings, 2 replies; 17+ messages in thread
From: Paul A Jungwirth @ 2026-07-02 18:06 UTC (permalink / raw)
To: Chao Li <li.evan.chao@gmail.com>; +Cc: Ayush Tiwari <ayushtiwari.slg01@gmail.com>; pgsql-hackers; Peter Eisentraut <peter@eisentraut.org>
> 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
Attachments:
[application/octet-stream] v2-0001-Fix-RLS-checks-for-FOR-PORTION-OF-leftover-rows.patch (9.2K, ../../CA+renyWYcQ=Yc1zQJ2UzLDF2cdHBHto-dRHw9s9EAA111ApEqQ@mail.gmail.com/2-v2-0001-Fix-RLS-checks-for-FOR-PORTION-OF-leftover-rows.patch)
download | inline diff:
From ec323ca5626906c275ad8f57a61a1ec94a248eea 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 v2] 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:
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.sgml
index ffdcd7fc4fa..c66569f6533 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.) RLS policies
+ for inserting temporal leftovers are still checked.
</para>
</refsect1>
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index 21a8fd8b037..e79c25a57e3 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.) RLS policies
+ for inserting temporal leftovers are still checked.
</para>
</refsect1>
diff --git a/src/backend/rewrite/rowsecurity.c b/src/backend/rewrite/rowsecurity.c
index 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.out
index 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.sql
index 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.45.0
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Fix RLS checks for UPDATE/DELETE FOR PORTION OF leftover rows
@ 2026-07-02 18:17 Ayush Tiwari <ayushtiwari.slg01@gmail.com>
parent: Paul A Jungwirth <pj@illuminatedcomputing.com>
1 sibling, 0 replies; 17+ messages in thread
From: Ayush Tiwari @ 2026-07-02 18:17 UTC (permalink / raw)
To: Paul A Jungwirth <pj@illuminatedcomputing.com>; +Cc: Chao Li <li.evan.chao@gmail.com>; pgsql-hackers; Peter Eisentraut <peter@eisentraut.org>
Hi,
On Thu, 2 Jul 2026 at 23:36, 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.
>
I had reviewed v1 and it looked good to me, assuming v2
changes just added the RLS policies documentation update, the patch
addresses my concern and looks good to me!
Regards,
Ayush
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Fix RLS checks for UPDATE/DELETE FOR PORTION OF leftover rows
@ 2026-07-02 21:52 Chao Li <li.evan.chao@gmail.com>
parent: Paul A Jungwirth <pj@illuminatedcomputing.com>
1 sibling, 1 reply; 17+ messages in thread
From: Chao Li @ 2026-07-02 21:52 UTC (permalink / raw)
To: Paul A Jungwirth <pj@illuminatedcomputing.com>; +Cc: Ayush Tiwari <ayushtiwari.slg01@gmail.com>; pgsql-hackers; Peter Eisentraut <peter@eisentraut.org>
> 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.sgml
index 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.sgml
index 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.c
index 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.out
index 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.sql
index 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)
=
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Fix RLS checks for UPDATE/DELETE FOR PORTION OF leftover rows
@ 2026-07-02 21:57 Paul A Jungwirth <pj@illuminatedcomputing.com>
parent: Chao Li <li.evan.chao@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Paul A Jungwirth @ 2026-07-02 21:57 UTC (permalink / raw)
To: Chao Li <li.evan.chao@gmail.com>; +Cc: Ayush Tiwari <ayushtiwari.slg01@gmail.com>; pgsql-hackers; Peter Eisentraut <peter@eisentraut.org>
On Thu, Jul 2, 2026 at 2:52 PM Chao Li <li.evan.chao@gmail.com> wrote:
>
> > Here is a v2 with a couple lines of documentation added.
> >
> 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.
+1
--
Paul ~{:-)
pj@illuminatedcomputing.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Fix RLS checks for UPDATE/DELETE FOR PORTION OF leftover rows
@ 2026-07-15 18:51 Dean Rasheed <dean.a.rasheed@gmail.com>
parent: Paul A Jungwirth <pj@illuminatedcomputing.com>
0 siblings, 1 reply; 17+ messages in thread
From: Dean Rasheed @ 2026-07-15 18:51 UTC (permalink / raw)
To: Paul A Jungwirth <pj@illuminatedcomputing.com>; +Cc: Chao Li <li.evan.chao@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; pgsql-hackers; Peter Eisentraut <peter@eisentraut.org>
On Thu, 2 Jul 2026 at 22:57, Paul A Jungwirth
<pj@illuminatedcomputing.com> wrote:
>
> On Thu, Jul 2, 2026 at 2:52 PM Chao Li <li.evan.chao@gmail.com> wrote:
> >
> > PFA v3, only this tiny doc change.
>
> +1
>
This should also update the doc table "Policies Applied by Command
Type" on the CREATE POLICY page. I'd suggest a new row for "UPDATE ...
FOR PORTION OF" immediately after the "UPDATE" row, rather than trying
to reuse the existing row with more conditions (and similarly for
DELETE).
Also, in the rowsecurity regression test, it would be a good idea to
add test cases to the "Test policies applied by command type" section.
Those tests are the confirmations that what that doc table says is
true (the patch's tests aren't really checking that the SELECT policy
is being applied, for example).
Regards,
Dean
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Fix RLS checks for UPDATE/DELETE FOR PORTION OF leftover rows
@ 2026-07-15 21:02 Paul A Jungwirth <pj@illuminatedcomputing.com>
parent: Dean Rasheed <dean.a.rasheed@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Paul A Jungwirth @ 2026-07-15 21:02 UTC (permalink / raw)
To: Dean Rasheed <dean.a.rasheed@gmail.com>; +Cc: Chao Li <li.evan.chao@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; pgsql-hackers; Peter Eisentraut <peter@eisentraut.org>
On Wed, Jul 15, 2026 at 11:51 AM Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>
> This should also update the doc table "Policies Applied by Command
> Type" on the CREATE POLICY page. I'd suggest a new row for "UPDATE ...
> FOR PORTION OF" immediately after the "UPDATE" row, rather than trying
> to reuse the existing row with more conditions (and similarly for
> DELETE).
>
> Also, in the rowsecurity regression test, it would be a good idea to
> add test cases to the "Test policies applied by command type" section.
> Those tests are the confirmations that what that doc table says is
> true (the patch's tests aren't really checking that the SELECT policy
> is being applied, for example).
Thanks for taking a look! Here is a v4 with those additions.
Yours,
--
Paul ~{:-)
pj@illuminatedcomputing.com
Attachments:
[text/x-patch] v4-0001-Fix-RLS-checks-for-FOR-PORTION-OF-leftover-rows.patch (16.6K, ../../CA+renyX5Mk7oq-S2eX1q7656ThtynmN5J9TWp2fSvWuMPivPjA@mail.gmail.com/2-v4-0001-Fix-RLS-checks-for-FOR-PORTION-OF-leftover-rows.patch)
download | inline diff:
From a14e804c2f820f9aab5d685c1f183e8ad4327d10 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 v4] 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/create_policy.sgml | 34 +++++++++
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/expected/rowsecurity.out | 44 +++++++++++-
src/test/regress/sql/for_portion_of.sql | 58 +++++++++++++++
src/test/regress/sql/rowsecurity.sql | 35 ++++++++-
8 files changed, 293 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml
index d8a036739c0..2dff11d47d2 100644
--- a/doc/src/sgml/ref/create_policy.sgml
+++ b/doc/src/sgml/ref/create_policy.sgml
@@ -530,6 +530,27 @@ 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 <footnoteref linkend="rls-select-priv"/> &
+ check new row <footnoteref linkend="rls-select-priv"/>
+ </entry>
+ <entry>
+ Check new row <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 +561,19 @@ 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 <footnoteref linkend="rls-select-priv"/> &
+ check new row <footnoteref linkend="rls-select-priv"/>
+ </entry>
+ <entry>
+ Check new row <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.sgml
index 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.sgml
index 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.c
index 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.out
index 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.out
index 3a5e82c35bd..e62311cfb62 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -330,9 +330,51 @@ NOTICE: DELETE USING on rls_test_tgt.(1,"tgt c","TGT C")
1 | src a | 1 | tgt c | TGT C
(1 row)
+-- setup a temporal target table for FOR PORTION OF, with the same set of
+-- NOTICE-emitting policies as rls_test_tgt
+SET SESSION AUTHORIZATION regress_rls_alice;
+CREATE TABLE rls_test_fpo (a int, valid_at int4range, b text);
+ALTER TABLE rls_test_fpo ENABLE ROW LEVEL SECURITY;
+GRANT SELECT, INSERT, UPDATE, DELETE ON rls_test_fpo TO public;
+CREATE POLICY sel_pol ON rls_test_fpo FOR SELECT
+ USING (rls_test_policy_fn('SELECT USING on rls_test_fpo', rls_test_fpo));
+CREATE POLICY ins_pol ON rls_test_fpo FOR INSERT
+ WITH CHECK (rls_test_policy_fn('INSERT CHECK on rls_test_fpo', rls_test_fpo));
+CREATE POLICY upd_pol ON rls_test_fpo FOR UPDATE
+ USING (rls_test_policy_fn('UPDATE USING on rls_test_fpo', rls_test_fpo))
+ WITH CHECK (rls_test_policy_fn('UPDATE CHECK on rls_test_fpo', rls_test_fpo));
+CREATE POLICY del_pol ON rls_test_fpo FOR DELETE
+ USING (rls_test_policy_fn('DELETE USING on rls_test_fpo', rls_test_fpo));
+INSERT INTO rls_test_fpo VALUES (1, '[1,10)', 'tgt a');
+-- test policies applied to regress_rls_bob
+SET SESSION AUTHORIZATION regress_rls_bob;
+-- UPDATE ... FOR PORTION OF filters with USING, checks the change with CHECK,
+-- and checks the temporal leftovers with the FOR INSERT policy.
+BEGIN;
+UPDATE rls_test_fpo FOR PORTION OF valid_at FROM 3 TO 7 SET b = 'tgt b';
+NOTICE: UPDATE USING on rls_test_fpo.(1,"[1,10)","tgt a")
+NOTICE: SELECT USING on rls_test_fpo.(1,"[1,10)","tgt a")
+NOTICE: UPDATE CHECK on rls_test_fpo.(1,"[3,7)","tgt b")
+NOTICE: SELECT USING on rls_test_fpo.(1,"[3,7)","tgt b")
+NOTICE: INSERT CHECK on rls_test_fpo.(1,"[1,3)","tgt a")
+NOTICE: SELECT USING on rls_test_fpo.(1,"[1,3)","tgt a")
+NOTICE: INSERT CHECK on rls_test_fpo.(1,"[7,10)","tgt a")
+NOTICE: SELECT USING on rls_test_fpo.(1,"[7,10)","tgt a")
+ROLLBACK;
+-- DELETE ... FOR PORTION OF filters with USING
+-- and checks the temporal leftovers with the FOR INSERT policy.
+BEGIN;
+DELETE FROM rls_test_fpo FOR PORTION OF valid_at FROM 3 TO 7;
+NOTICE: DELETE USING on rls_test_fpo.(1,"[1,10)","tgt a")
+NOTICE: SELECT USING on rls_test_fpo.(1,"[1,10)","tgt a")
+NOTICE: INSERT CHECK on rls_test_fpo.(1,"[1,3)","tgt a")
+NOTICE: SELECT USING on rls_test_fpo.(1,"[1,3)","tgt a")
+NOTICE: INSERT CHECK on rls_test_fpo.(1,"[7,10)","tgt a")
+NOTICE: SELECT USING on rls_test_fpo.(1,"[7,10)","tgt a")
+ROLLBACK;
-- Tidy up
RESET SESSION AUTHORIZATION;
-DROP TABLE rls_test_src, rls_test_tgt;
+DROP TABLE rls_test_src, rls_test_tgt, rls_test_fpo;
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.sql
index 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.sql
index 6b3566271df..b3fc1a1fe9a 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -190,9 +190,42 @@ MERGE INTO rls_test_tgt t USING rls_test_src s ON t.a = s.a
WHEN MATCHED THEN DELETE
RETURNING *;
+-- setup a temporal target table for FOR PORTION OF, with the same set of
+-- NOTICE-emitting policies as rls_test_tgt
+SET SESSION AUTHORIZATION regress_rls_alice;
+CREATE TABLE rls_test_fpo (a int, valid_at int4range, b text);
+ALTER TABLE rls_test_fpo ENABLE ROW LEVEL SECURITY;
+GRANT SELECT, INSERT, UPDATE, DELETE ON rls_test_fpo TO public;
+CREATE POLICY sel_pol ON rls_test_fpo FOR SELECT
+ USING (rls_test_policy_fn('SELECT USING on rls_test_fpo', rls_test_fpo));
+CREATE POLICY ins_pol ON rls_test_fpo FOR INSERT
+ WITH CHECK (rls_test_policy_fn('INSERT CHECK on rls_test_fpo', rls_test_fpo));
+CREATE POLICY upd_pol ON rls_test_fpo FOR UPDATE
+ USING (rls_test_policy_fn('UPDATE USING on rls_test_fpo', rls_test_fpo))
+ WITH CHECK (rls_test_policy_fn('UPDATE CHECK on rls_test_fpo', rls_test_fpo));
+CREATE POLICY del_pol ON rls_test_fpo FOR DELETE
+ USING (rls_test_policy_fn('DELETE USING on rls_test_fpo', rls_test_fpo));
+
+INSERT INTO rls_test_fpo VALUES (1, '[1,10)', 'tgt a');
+
+-- test policies applied to regress_rls_bob
+SET SESSION AUTHORIZATION regress_rls_bob;
+
+-- UPDATE ... FOR PORTION OF filters with USING, checks the change with CHECK,
+-- and checks the temporal leftovers with the FOR INSERT policy.
+BEGIN;
+UPDATE rls_test_fpo FOR PORTION OF valid_at FROM 3 TO 7 SET b = 'tgt b';
+ROLLBACK;
+
+-- DELETE ... FOR PORTION OF filters with USING
+-- and checks the temporal leftovers with the FOR INSERT policy.
+BEGIN;
+DELETE FROM rls_test_fpo FOR PORTION OF valid_at FROM 3 TO 7;
+ROLLBACK;
+
-- Tidy up
RESET SESSION AUTHORIZATION;
-DROP TABLE rls_test_src, rls_test_tgt;
+DROP TABLE rls_test_src, rls_test_tgt, rls_test_fpo;
DROP FUNCTION rls_test_tgt_set_c;
DROP FUNCTION rls_test_policy_fn;
--
2.47.3
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Fix RLS checks for UPDATE/DELETE FOR PORTION OF leftover rows
@ 2026-07-15 23:40 Dean Rasheed <dean.a.rasheed@gmail.com>
parent: Paul A Jungwirth <pj@illuminatedcomputing.com>
0 siblings, 1 reply; 17+ messages in thread
From: Dean Rasheed @ 2026-07-15 23:40 UTC (permalink / raw)
To: Paul A Jungwirth <pj@illuminatedcomputing.com>; +Cc: Chao Li <li.evan.chao@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; pgsql-hackers; Peter Eisentraut <peter@eisentraut.org>
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.
+ /*
+ * 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?
Regards,
Dean
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Fix RLS checks for UPDATE/DELETE FOR PORTION OF leftover rows
@ 2026-07-16 00:26 Paul A Jungwirth <pj@illuminatedcomputing.com>
parent: Dean Rasheed <dean.a.rasheed@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Paul A Jungwirth @ 2026-07-16 00:26 UTC (permalink / raw)
To: Dean Rasheed <dean.a.rasheed@gmail.com>; +Cc: Chao Li <li.evan.chao@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; pgsql-hackers; Peter Eisentraut <peter@eisentraut.org>
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
Attachments:
[text/x-patch] v5-0001-Fix-RLS-checks-for-FOR-PORTION-OF-leftover-rows.patch (15.6K, ../../CA+renyWqKvB86R2FBAUh=AWoyt40qMf7yHWzoHaBr15kyy+Tjw@mail.gmail.com/2-v5-0001-Fix-RLS-checks-for-FOR-PORTION-OF-leftover-rows.patch)
download | inline diff:
From 0ff192d81be90ccedcb5ca5d470e2e1b7cce3f08 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 v5] 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/create_policy.sgml | 33 +++++++++
doc/src/sgml/ref/delete.sgml | 3 +-
doc/src/sgml/ref/update.sgml | 3 +-
src/backend/rewrite/rowsecurity.c | 29 ++++++++
src/test/regress/expected/for_portion_of.out | 75 ++++++++++++++++++++
src/test/regress/expected/rowsecurity.out | 40 ++++++++++-
src/test/regress/sql/for_portion_of.sql | 58 +++++++++++++++
src/test/regress/sql/rowsecurity.sql | 35 ++++++++-
8 files changed, 272 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml
index d8a036739c0..6f5877aad57 100644
--- a/doc/src/sgml/ref/create_policy.sgml
+++ b/doc/src/sgml/ref/create_policy.sgml
@@ -530,6 +530,27 @@ 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 new row <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 +561,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 new row <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.sgml
index 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.sgml
index 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.c
index e88a1bc1a89..923d5ae2559 100644
--- a/src/backend/rewrite/rowsecurity.c
+++ b/src/backend/rewrite/rowsecurity.c
@@ -393,6 +393,35 @@ 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);
+
+ /*
+ * SELECT rights are never needed for the temporal leftovers,
+ * because they are not considered by RETURNING.
+ */
+ }
+
/*
* 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.out
index 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.out
index 3a5e82c35bd..d189f9439bb 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -330,9 +330,47 @@ NOTICE: DELETE USING on rls_test_tgt.(1,"tgt c","TGT C")
1 | src a | 1 | tgt c | TGT C
(1 row)
+-- setup a temporal target table for FOR PORTION OF, with the same set of
+-- NOTICE-emitting policies as rls_test_tgt
+SET SESSION AUTHORIZATION regress_rls_alice;
+CREATE TABLE rls_test_fpo (a int, valid_at int4range, b text);
+ALTER TABLE rls_test_fpo ENABLE ROW LEVEL SECURITY;
+GRANT SELECT, INSERT, UPDATE, DELETE ON rls_test_fpo TO public;
+CREATE POLICY sel_pol ON rls_test_fpo FOR SELECT
+ USING (rls_test_policy_fn('SELECT USING on rls_test_fpo', rls_test_fpo));
+CREATE POLICY ins_pol ON rls_test_fpo FOR INSERT
+ WITH CHECK (rls_test_policy_fn('INSERT CHECK on rls_test_fpo', rls_test_fpo));
+CREATE POLICY upd_pol ON rls_test_fpo FOR UPDATE
+ USING (rls_test_policy_fn('UPDATE USING on rls_test_fpo', rls_test_fpo))
+ WITH CHECK (rls_test_policy_fn('UPDATE CHECK on rls_test_fpo', rls_test_fpo));
+CREATE POLICY del_pol ON rls_test_fpo FOR DELETE
+ USING (rls_test_policy_fn('DELETE USING on rls_test_fpo', rls_test_fpo));
+INSERT INTO rls_test_fpo VALUES (1, '[1,10)', 'tgt a');
+-- test policies applied to regress_rls_bob
+SET SESSION AUTHORIZATION regress_rls_bob;
+-- UPDATE ... FOR PORTION OF filters with USING, checks the change with CHECK,
+-- and checks the temporal leftovers with the FOR INSERT policy.
+BEGIN;
+UPDATE rls_test_fpo FOR PORTION OF valid_at FROM 3 TO 7 SET b = 'tgt b';
+NOTICE: UPDATE USING on rls_test_fpo.(1,"[1,10)","tgt a")
+NOTICE: SELECT USING on rls_test_fpo.(1,"[1,10)","tgt a")
+NOTICE: UPDATE CHECK on rls_test_fpo.(1,"[3,7)","tgt b")
+NOTICE: SELECT USING on rls_test_fpo.(1,"[3,7)","tgt b")
+NOTICE: INSERT CHECK on rls_test_fpo.(1,"[1,3)","tgt a")
+NOTICE: INSERT CHECK on rls_test_fpo.(1,"[7,10)","tgt a")
+ROLLBACK;
+-- DELETE ... FOR PORTION OF filters with USING
+-- and checks the temporal leftovers with the FOR INSERT policy.
+BEGIN;
+DELETE FROM rls_test_fpo FOR PORTION OF valid_at FROM 3 TO 7;
+NOTICE: DELETE USING on rls_test_fpo.(1,"[1,10)","tgt a")
+NOTICE: SELECT USING on rls_test_fpo.(1,"[1,10)","tgt a")
+NOTICE: INSERT CHECK on rls_test_fpo.(1,"[1,3)","tgt a")
+NOTICE: INSERT CHECK on rls_test_fpo.(1,"[7,10)","tgt a")
+ROLLBACK;
-- Tidy up
RESET SESSION AUTHORIZATION;
-DROP TABLE rls_test_src, rls_test_tgt;
+DROP TABLE rls_test_src, rls_test_tgt, rls_test_fpo;
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.sql
index 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.sql
index 6b3566271df..b3fc1a1fe9a 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -190,9 +190,42 @@ MERGE INTO rls_test_tgt t USING rls_test_src s ON t.a = s.a
WHEN MATCHED THEN DELETE
RETURNING *;
+-- setup a temporal target table for FOR PORTION OF, with the same set of
+-- NOTICE-emitting policies as rls_test_tgt
+SET SESSION AUTHORIZATION regress_rls_alice;
+CREATE TABLE rls_test_fpo (a int, valid_at int4range, b text);
+ALTER TABLE rls_test_fpo ENABLE ROW LEVEL SECURITY;
+GRANT SELECT, INSERT, UPDATE, DELETE ON rls_test_fpo TO public;
+CREATE POLICY sel_pol ON rls_test_fpo FOR SELECT
+ USING (rls_test_policy_fn('SELECT USING on rls_test_fpo', rls_test_fpo));
+CREATE POLICY ins_pol ON rls_test_fpo FOR INSERT
+ WITH CHECK (rls_test_policy_fn('INSERT CHECK on rls_test_fpo', rls_test_fpo));
+CREATE POLICY upd_pol ON rls_test_fpo FOR UPDATE
+ USING (rls_test_policy_fn('UPDATE USING on rls_test_fpo', rls_test_fpo))
+ WITH CHECK (rls_test_policy_fn('UPDATE CHECK on rls_test_fpo', rls_test_fpo));
+CREATE POLICY del_pol ON rls_test_fpo FOR DELETE
+ USING (rls_test_policy_fn('DELETE USING on rls_test_fpo', rls_test_fpo));
+
+INSERT INTO rls_test_fpo VALUES (1, '[1,10)', 'tgt a');
+
+-- test policies applied to regress_rls_bob
+SET SESSION AUTHORIZATION regress_rls_bob;
+
+-- UPDATE ... FOR PORTION OF filters with USING, checks the change with CHECK,
+-- and checks the temporal leftovers with the FOR INSERT policy.
+BEGIN;
+UPDATE rls_test_fpo FOR PORTION OF valid_at FROM 3 TO 7 SET b = 'tgt b';
+ROLLBACK;
+
+-- DELETE ... FOR PORTION OF filters with USING
+-- and checks the temporal leftovers with the FOR INSERT policy.
+BEGIN;
+DELETE FROM rls_test_fpo FOR PORTION OF valid_at FROM 3 TO 7;
+ROLLBACK;
+
-- Tidy up
RESET SESSION AUTHORIZATION;
-DROP TABLE rls_test_src, rls_test_tgt;
+DROP TABLE rls_test_src, rls_test_tgt, rls_test_fpo;
DROP FUNCTION rls_test_tgt_set_c;
DROP FUNCTION rls_test_policy_fn;
--
2.47.3
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Fix RLS checks for UPDATE/DELETE FOR PORTION OF leftover rows
@ 2026-07-16 01:09 Chao Li <li.evan.chao@gmail.com>
parent: Paul A Jungwirth <pj@illuminatedcomputing.com>
0 siblings, 1 reply; 17+ messages in thread
From: Chao Li @ 2026-07-16 01:09 UTC (permalink / raw)
To: Paul A Jungwirth <pj@illuminatedcomputing.com>; +Cc: Dean Rasheed <dean.a.rasheed@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; pgsql-hackers; Peter Eisentraut <peter@eisentraut.org>
> 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.sgml
index 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.sgml
index 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.sgml
index 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.c
index 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.out
index 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.out
index 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.sql
index 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.sql
index 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)
=
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Fix RLS checks for UPDATE/DELETE FOR PORTION OF leftover rows
@ 2026-07-16 15:35 Paul A Jungwirth <pj@illuminatedcomputing.com>
parent: Chao Li <li.evan.chao@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Paul A Jungwirth @ 2026-07-16 15:35 UTC (permalink / raw)
To: Chao Li <li.evan.chao@gmail.com>; +Cc: Dean Rasheed <dean.a.rasheed@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; pgsql-hackers; Peter Eisentraut <peter@eisentraut.org>
On Wed, Jul 15, 2026 at 6:10 PM Chao Li <li.evan.chao@gmail.com> wrote:
>
> 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 took a look. These all seem like improvements to me.
Yours,
--
Paul ~{:-)
pj@illuminatedcomputing.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Fix RLS checks for UPDATE/DELETE FOR PORTION OF leftover rows
@ 2026-07-20 06:53 Peter Eisentraut <peter@eisentraut.org>
parent: Paul A Jungwirth <pj@illuminatedcomputing.com>
0 siblings, 1 reply; 17+ messages in thread
From: Peter Eisentraut @ 2026-07-20 06:53 UTC (permalink / raw)
To: Paul A Jungwirth <pj@illuminatedcomputing.com>; Chao Li <li.evan.chao@gmail.com>; +Cc: Dean Rasheed <dean.a.rasheed@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; pgsql-hackers
On 16.07.26 17:35, Paul A Jungwirth wrote:
> On Wed, Jul 15, 2026 at 6:10 PM Chao Li <li.evan.chao@gmail.com> wrote:
>>
>> 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 took a look. These all seem like improvements to me.
committed v6
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Fix RLS checks for UPDATE/DELETE FOR PORTION OF leftover rows
@ 2026-07-20 06:59 Chao Li <li.evan.chao@gmail.com>
parent: Peter Eisentraut <peter@eisentraut.org>
0 siblings, 0 replies; 17+ messages in thread
From: Chao Li @ 2026-07-20 06:59 UTC (permalink / raw)
To: Peter Eisentraut <peter@eisentraut.org>; +Cc: Paul A Jungwirth <pj@illuminatedcomputing.com>; Dean Rasheed <dean.a.rasheed@gmail.com>; Ayush Tiwari <ayushtiwari.slg01@gmail.com>; pgsql-hackers
> On Jul 20, 2026, at 14:53, Peter Eisentraut <peter@eisentraut.org> wrote:
>
> On 16.07.26 17:35, Paul A Jungwirth wrote:
>> On Wed, Jul 15, 2026 at 6:10 PM Chao Li <li.evan.chao@gmail.com> wrote:
>>>
>>> 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 took a look. These all seem like improvements to me.
>
> committed v6
Thanks for committing.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2026-07-20 06:59 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-06-30 07:45 Fix RLS checks for UPDATE/DELETE FOR PORTION OF leftover rows Chao Li <li.evan.chao@gmail.com>
2026-06-30 07:51 ` Ayush Tiwari <ayushtiwari.slg01@gmail.com>
2026-06-30 08:00 ` Chao Li <li.evan.chao@gmail.com>
2026-06-30 08:07 ` Chao Li <li.evan.chao@gmail.com>
2026-07-02 14:52 ` Paul A Jungwirth <pj@illuminatedcomputing.com>
2026-07-02 18:06 ` Paul A Jungwirth <pj@illuminatedcomputing.com>
2026-07-02 18:17 ` Ayush Tiwari <ayushtiwari.slg01@gmail.com>
2026-07-02 21:52 ` Chao Li <li.evan.chao@gmail.com>
2026-07-02 21:57 ` Paul A Jungwirth <pj@illuminatedcomputing.com>
2026-07-15 18:51 ` Dean Rasheed <dean.a.rasheed@gmail.com>
2026-07-15 21:02 ` Paul A Jungwirth <pj@illuminatedcomputing.com>
2026-07-15 23:40 ` Dean Rasheed <dean.a.rasheed@gmail.com>
2026-07-16 00:26 ` Paul A Jungwirth <pj@illuminatedcomputing.com>
2026-07-16 01:09 ` Chao Li <li.evan.chao@gmail.com>
2026-07-16 15:35 ` Paul A Jungwirth <pj@illuminatedcomputing.com>
2026-07-20 06:53 ` Peter Eisentraut <peter@eisentraut.org>
2026-07-20 06:59 ` Chao Li <li.evan.chao@gmail.com>
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