On Fri, 25 Sept 2026 at 14:52, Viktor Holmberg <[email protected]> wrote:
>
> On 25 Sep 2026 at 12:48 +0200, Kirill Reshke <[email protected]>, wrote:
>
> On Fri, 25 Sept 2026 at 09:45, shihao zhong <[email protected]> wrote:
>
> In 19, a user with only INSERT and SELECT on a security_barrier view can
> read rows that the view hides, with ON CONFLICT DO SELECT. Before 19 that
> user had no way to reach a hidden row, because DO UPDATE needs UPDATE.
>
> I see two ways to go. Keep the behavior and say it plainly in the
> security_barrier docs. Or check the existing row against the
> view's quals in DO SELECT, the same way RLS does, and raise an error when
> the row is hidden. I have a draft patch for the second, for views with a
> check option.
>
> Which way do people prefer? If it is the second, should it be a 19 open
> item?
>
> Well spotted Shihao. I agree with Kirill that this has to be fixed before
> release, a doc fix is not enough.
> Please share your patch Shihao. I should be able to review within a week.
>
Given the timing, I think we should just reject INSERT ... ON CONFLICT
DO SELECT on a security-barrier view with a "feature not supported"
error.
That particular combination of features is probably not that common,
so it doesn't detract too much from the utility of ON CONFLICT DO
SELECT, and that will give more time to consider the issue properly
for v20, if we want.
Regards,
Dean
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 27964cb83c6..82244d9722d 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -3353,6 +3353,20 @@ rewriteTargetView(Query *parsetree, Relation view)
errmsg("access to non-system view \"%s\" is restricted",
RelationGetRelationName(view))));
+ /*
+ * For now, we don't support INSERT .. ON CONFLICT .. DO SELECT on a
+ * security-barrier view. This would require an executor check to ensure
+ * that conflicting rows satisfy the view quals.
+ */
+ if (parsetree->onConflict &&
+ parsetree->onConflict->action == ONCONFLICT_SELECT &&
+ RelationIsSecurityView(view))
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot insert into view \"%s\"",
+ RelationGetRelationName(view)),
+ errdetail("INSERT ... ON CONFLICT DO SELECT is not supported on security-barrier views."));
+
/*
* The view must be updatable, else fail.
*
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index b4c9bdb0e55..47198f56e4d 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -365,6 +365,26 @@ insert into insertconflicttest as i values (1, 'Orange') on conflict (key) do se
1 | Apple
(1 row)
+--
+-- DO SELECT can bypass view quals, but not for a security-barrier view
+--
+create view insertconflicttestview2 as select * from insertconflicttest where fruit = 'Potato'; -- filters out everything
+select * from insertconflicttestview2;
+ key | fruit
+-----+-------
+(0 rows)
+
+insert into insertconflicttestview2 values (1, 'Orange') on conflict (key) do select returning *; -- bypasses view quals
+ key | fruit
+-----+-------
+ 1 | Apple
+(1 row)
+
+alter view insertconflicttestview2 set (security_barrier=true);
+insert into insertconflicttestview2 values (1, 'Orange') on conflict (key) do select returning *; -- fails
+ERROR: cannot insert into view "insertconflicttestview2"
+DETAIL: INSERT ... ON CONFLICT DO SELECT is not supported on security-barrier views.
+drop view insertconflicttestview2;
drop index key_index;
--
-- Composite key tests
diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql
index d119158549f..2a7294f9d43 100644
--- a/src/test/regress/sql/insert_conflict.sql
+++ b/src/test/regress/sql/insert_conflict.sql
@@ -145,6 +145,16 @@ insert into insertconflicttest as i values (1, 'Apple') on conflict (key) do sel
insert into insertconflicttest as i values (1, 'Orange') on conflict (key) do select where excluded.fruit = 'Apple' returning *;
insert into insertconflicttest as i values (1, 'Orange') on conflict (key) do select where excluded.fruit = 'Orange' returning *;
+--
+-- DO SELECT can bypass view quals, but not for a security-barrier view
+--
+create view insertconflicttestview2 as select * from insertconflicttest where fruit = 'Potato'; -- filters out everything
+select * from insertconflicttestview2;
+insert into insertconflicttestview2 values (1, 'Orange') on conflict (key) do select returning *; -- bypasses view quals
+alter view insertconflicttestview2 set (security_barrier=true);
+insert into insertconflicttestview2 values (1, 'Orange') on conflict (key) do select returning *; -- fails
+drop view insertconflicttestview2;
+
drop index key_index;
--
