MIME-Version: 1.0
References: 
 <CAMjNa7eFzTQ5=oZMQiB2bMkez5KP4A77JC7SRjeVEkOrh7cUHw@mail.gmail.com>
 <CACLU5mST1LhC3ibaKGNch_=06S2cmbjR4PnoUSupKs+rtgdeyw@mail.gmail.com>
 <CAMjNa7fJUwcOxf+qV8g+tCQ-3E-YAiKgE_Qs6u-xjdxe12T0SQ@mail.gmail.com>
 <CAMjNa7egcgUMf2tdQ1qeTYj1J1bBvyth3thoZPioujusFsBd4Q@mail.gmail.com>
 <CAOj6k6fDKg4EOpzmO1sJYARwyu8dkmX-BFCEymQc_6okw9ORVw@mail.gmail.com>
 <CAOj6k6e7fw8RjAWXc04_A=sg4=jsU0CK7Qi13fwkPW5hMz6a5w@mail.gmail.com>
 <CAMjNa7esgLwWU8fmPU7V_AM6iLg1dRmpiAufL-Q9jdWpopfTNg@mail.gmail.com>
 <CAMjNa7d8f3sj-1ZsmsqiUPLzjXFtjOgeM7GFKvU_1EugyzJ5jw@mail.gmail.com>
 <CAN4CZFOWWf7Lso10DNLmadkuHRt8aABsWNtidjqfpjrXkDU-ZQ@mail.gmail.com>
In-Reply-To: 
 <CAN4CZFOWWf7Lso10DNLmadkuHRt8aABsWNtidjqfpjrXkDU-ZQ@mail.gmail.com>
From: Adam Brusselback <adambrusselback@gmail.com>
Date: Thu, 28 May 2026 22:53:15 -0400
Message-ID: 
 <CAMjNa7fCUJMzmT8Cx4i4XAanSBS4gzOJMoMa9XTsPqeLiG7PkQ@mail.gmail.com>
Subject: Re: [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW
To: Zsolt Parragi <zsolt.parragi@percona.com>
Cc: pgsql-hackers@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000037e7d90652ebf3d5"
Archived-At: 
 <https://www.postgresql.org/message-id/CAMjNa7fCUJMzmT8Cx4i4XAanSBS4gzOJMoMa9XTsPqeLiG7PkQ%40mail.gmail.com>
Precedence: bulk

--00000000000037e7d90652ebf3d5
Content-Type: text/plain; charset="UTF-8"

Hi Zsolt, and hackers,

On the privilege escalation: Yup, that isn't good. Thank you for catching
that.
In both paths the predicate is concatenated directly into the SQL that
evaluates the view, e.g. in refresh_by_direct_modification:
    SELECT * FROM (<view definition>) mv WHERE (<predicate>)

and in refresh_by_match_merge:
    SELECT ctid, * FROM <matview> WHERE (<predicate>)

There's one plan, executed under one userid. I can't run the (<view
definition>) subquery as the owner and the WHERE (<predicate>) as the
invoker, SPI executes the whole statement in whatever security context is
active when it runs.

So the predicate runs as the owner. The levers left are what the predicate
may contain and who may run it. Here is what I was thinking:
- Predicate functions all leakproof: allow for anyone with the privilege to
refresh today (MAINTAIN or owner). A leakproof predicate in owner context
can't leak the owner's data or do anything the invoker couldn't, so nothing
escalates.
- Predicate contains a non-leakproof function: require ownership (or
superuser). Invoker and owner are then the same trust domain, so
owner-context execution doesn't escalate.

This keeps MAINTAIN working for the common case, predicates over columns
with built-in operators. The tightening only hits custom non-leakproof
predicate functions.

If anyone else has better ideas, i'm all ears.


Your second issue is due to a missing PG_TRY around the
OpenMatViewIncrementalMaintenance()/Close pair in the direct-modification
path (the match/merge site already handles it). An error between open and
close goes past the close and leaves matview_maintenance_depth above zero
for the session, which is what lets plain DELETE/INSERT through afterward.
Will fix.

Thanks,
Adam

--00000000000037e7d90652ebf3d5
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi Zsolt, and hackers,<br><br>On the privilege escalation:=
 Yup, that isn&#39;t good. Thank you for catching that.=C2=A0<br>In both pa=
ths the predicate is concatenated directly into the SQL that evaluates the =
view, e.g. in refresh_by_direct_modification:<br>=C2=A0 =C2=A0 SELECT * FRO=
M (&lt;view definition&gt;) mv WHERE (&lt;predicate&gt;)<br><br>and in refr=
esh_by_match_merge:<br>=C2=A0 =C2=A0 SELECT ctid, * FROM &lt;matview&gt; WH=
ERE (&lt;predicate&gt;)<br><br>There&#39;s one plan, executed under one use=
rid. I can&#39;t run the (&lt;view definition&gt;) subquery as the owner an=
d the WHERE (&lt;predicate&gt;) as the invoker, SPI executes the whole stat=
ement in whatever security context is active when it runs.<br><br>So the pr=
edicate runs as the owner. The levers left are what the predicate may conta=
in and who may run it. Here is what I was thinking:<br>- Predicate function=
s all leakproof: allow for anyone with the privilege to refresh today (MAIN=
TAIN or owner). A leakproof predicate in owner context can&#39;t leak the o=
wner&#39;s data or do anything the invoker couldn&#39;t, so nothing escalat=
es.<br>- Predicate contains a non-leakproof function: require ownership (or=
 superuser). Invoker and owner are then the same trust domain, so owner-con=
text execution doesn&#39;t escalate.<br><br>This keeps MAINTAIN working for=
 the common case, predicates over columns with built-in operators. The tigh=
tening only hits custom non-leakproof predicate functions.<br><br>If anyone=
 else has better ideas, i&#39;m all ears.<br><br><br>Your second issue is d=
ue to a missing PG_TRY around the OpenMatViewIncrementalMaintenance()/Close=
 pair in the direct-modification path (the match/merge site already handles=
 it). An error between open and close goes past the close and leaves matvie=
w_maintenance_depth above zero for the session, which is what lets plain DE=
LETE/INSERT through afterward. Will fix.<br><br>Thanks,<br>Adam</div>

--00000000000037e7d90652ebf3d5--