public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adam Brusselback <[email protected]>
To: Zsolt Parragi <[email protected]>
Cc: [email protected]
Subject: Re: [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW
Date: Thu, 28 May 2026 22:53:15 -0400
Message-ID: <CAMjNa7fCUJMzmT8Cx4i4XAanSBS4gzOJMoMa9XTsPqeLiG7PkQ@mail.gmail.com> (raw)
In-Reply-To: <CAN4CZFOWWf7Lso10DNLmadkuHRt8aABsWNtidjqfpjrXkDU-ZQ@mail.gmail.com>
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>

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW
  In-Reply-To: <CAMjNa7fCUJMzmT8Cx4i4XAanSBS4gzOJMoMa9XTsPqeLiG7PkQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox