Enforce INSERT RLS checks for FOR PORTION OF leftovers?
Hi,
I found what looks like a discrepancy where UPDATE/DELETE FOR
PORTION OF commands bypass INSERT RLS WITH CHECK
policies when inserting temporal leftover rows. Not sure if it's already
flagged (could not find it in DL).
While it is intentional that ExecForPortionOfLeftovers() skips INSERT ACL
permission checks, the leftover rows are newly inserted rows and should
still satisfy INSERT/ALL RLS policies unless I'm missing something.
Currently, the rewrite phase only
attaches UPDATE/DELETE RLS checks for the target relation, leaving
ExecInsert() without a WCO_RLS_INSERT_CHECK to enforce for the
leftovers.
Maybe we should address this in rowsecurity.c by fetching CMD_INSERT
policies and adding them as WCO_RLS_INSERT_CHECK entries for queries
with a FOR PORTION OF clause?
Something like this:
--- a/src/backend/rewrite/rowsecurity.c
+++ b/src/backend/rewrite/rowsecurity.c
@@ -393,6 +393,34 @@ get_row_security_policies(Query *root, RangeTblEntry
*rte, int rt_index,
}
}+ /*
+ * UPDATE/DELETE FOR PORTION OF commands insert temporal leftovers via
+ * ExecInsert(). Those internal inserts intentionally skip INSERT ACL
+ * permission checks, but they still create new rows and must satisfy any
+ * INSERT/ALL RLS WITH CHECK policies.
+ */
+ if ((commandType == CMD_UPDATE || commandType == CMD_DELETE) &&
+ root->forPortionOf != NULL)
+ {
+ List *insert_permissive_policies;
+ List *insert_restrictive_policies;
+
+ /* This should be the target relation */
+ Assert(rt_index == root->resultRelation);
+
+ 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);
+ }
+Thoughts?
Regards,
Ayush
Hi,
On Sat, 2 May 2026 at 00:23, Ayush Tiwari <ayushtiwari.slg01@gmail.com>
wrote:
Hi,
I found what looks like a discrepancy where UPDATE/DELETE FOR
PORTION OF commands bypass INSERT RLS WITH CHECK
policies when inserting temporal leftover rows. Not sure if it's already
flagged (could not find it in DL).While it is intentional that ExecForPortionOfLeftovers() skips INSERT ACL
permission checks, the leftover rows are newly inserted rows and should
still satisfy INSERT/ALL RLS policies unless I'm missing something.
Sharing a SQL repro example:
CREATE ROLE u;
CREATE TABLE t (id int, valid_at daterange NOT NULL, name text);
ALTER TABLE t ENABLE ROW LEVEL SECURITY;
CREATE POLICY p_all ON t FOR ALL TO u USING (true) WITH CHECK (true);
CREATE POLICY p_ins ON t FOR INSERT TO u WITH CHECK (false);
GRANT SELECT, INSERT, UPDATE, DELETE ON t TO u;
INSERT INTO t VALUES (1, daterange('2018-01-01','2020-01-01'), 'ok');
SET ROLE u;
-- (A) Fails as expected: new row violates row-level security policy
INSERT INTO t VALUES (2, daterange('2018-01-01','2020-01-01'), 'ok');
-- (B) Should fail the same way (creates leftover rows), but silently
succeeds
UPDATE t FOR PORTION OF valid_at FROM '2019-01-01' TO '2019-06-01'
SET name = 'ok' WHERE id = 1;
If this is expected we need to change the documentation of policy
and if it is not, should we go with something like I shared in
upthread, I can send a patch file if required.
Regards,
Ayush