Enforce view row visibility for INSERT ... ON CONFLICT target rows.

The WHERE condition of an automatically updatable view restricts the
rows that an UPDATE, DELETE, or MERGE through the view can affect to
those that are visible through it.  INSERT ... ON CONFLICT DO UPDATE
through such a view did not follow this rule.  The conflicting row is
found through the arbiter index rather than by scanning the view, and
the view's WHERE condition was never applied to it.  DO UPDATE therefore
updated the row whether or not the view showed it, and evaluated its SET
expressions, its WHERE clause, and RETURNING against it; ON CONFLICT DO
SELECT read it the same way.  This odd set of behaviors was documented,
but it created a security issue, described further down.

Fix by having rewriteTargetView() emit the view's WHERE quals as a
WithCheckOption of new kind WCO_VIEW_CONFLICT_CHECK, which
ExecOnConflictUpdate() and ExecOnConflictSelect() check against the
conflicting row, innermost view first and before the ON CONFLICT WHERE
clause is evaluated.  Using a new WCO kind for this is perhaps a little
unorthodox, but doing so provides exactly what is needed here: it raises
an error when a conflicting row doesn't satisfy the view's quals.  This
makes sense because ON CONFLICT DO UPDATE guarantees that each row
proposed for insertion is either inserted or updated, neither of which
is feasible here.  The arbiter index belongs to the base relation, so
the conflicting row makes insertion the wrong thing to do.  The view's
WHERE condition puts the conflicting row beyond the reach of an update
through the view, but it doesn't make sense to act as if there's no row
to update, because when that happens we're supposed to take the
insertion path instead (that's what upsert promises).  This situation is
fundamentally irreconcilable, so throwing an error is all we can do.

This fixes a security bug.  A user whose privileges on a view allow an
upsert through it, but who holds none on its base relation, can modify
every row that the view hides, one at a time, by guessing their keys, as
if the view had no WHERE condition.  The same user can read those rows,
through the conflict action's WHERE clause, its SET expressions, and
RETURNING.  This historical behavior is hard to defend in general, and
impossible to defend in cases where the target view happens to be a
security barrier view.

The problem dates to commit 168d5805e, which added ON CONFLICT and
documented its original behavior with updatable views.  ON CONFLICT DO
SELECT was added to Postgres 19 by commit 88327092f, and it inherited
that behavior.

An upsert through a view that relied on reaching rows the view hides now
fails.  This should be called out as an incompatibility in the release
notes for the next set of minor releases.

Reported-by: Qing Xu <[email protected]>
Reported-by: Carl Andersen <[email protected]>
Reported-by: Zsolt Parragi <[email protected]>
Reported-by: shihao zhong <[email protected]>
Author: Peter Geoghegan <[email protected]>
Author: Zsolt Parragi <[email protected]>
Reviewed-by: Robert Haas <[email protected]>
Security: CVE-2026-16237
Backpatch-through: 14

Branch
------
REL_16_STABLE

Details
-------
https://git.postgresql.org/pg/commitdiff/629f54970896a84e80574d4aa17c3909e25bc8af
Author: Peter Geoghegan <[email protected]>

Modified Files
--------------
doc/src/sgml/ref/create_view.sgml             |  11 +-
src/backend/executor/execMain.c               |   6 +
src/backend/executor/nodeModifyTable.c        |  12 ++
src/backend/rewrite/rewriteHandler.c          |  76 +++++---
src/include/nodes/parsenodes.h                |   6 +-
src/test/regress/expected/rowsecurity.out     |  18 ++
src/test/regress/expected/updatable_views.out | 249 ++++++++++++++++++++++++++
src/test/regress/sql/rowsecurity.sql          |  15 ++
src/test/regress/sql/updatable_views.sql      | 202 +++++++++++++++++++++
9 files changed, 569 insertions(+), 26 deletions(-)

Reply via email to