Horton

Re: pg18: Virtual generated columns are not (yet) safe when superuser selects from them

Isaac Morland <isaac.morland@gmail.com>

View thread
From: Isaac Morland <isaac.morland@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: jian he <jian.universality@gmail.com>, Jeff Davis <pgsql@j-davis.com>, "David G. Johnston" <david.g.johnston@gmail.com>, Robert Haas <robertmhaas@gmail.com>, Feike Steenbergen <feikesteenbergen@gmail.com>, PostgreSQL mailing lists <pgsql-hackers@postgresql.org>
Date: 2025-06-03T13:15:17Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Restrict virtual columns to use built-in functions and types

  2. Fix virtual generated column type checking for ALTER TABLE

  3. Expand virtual generated columns in the planner

On Mon, 2 Jun 2025 at 23:30, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Isaac Morland <isaac.morland@gmail.com> writes:
>

> My fix would
> > be for check constraints, triggers, and view definitions to run as the
> > owner of the object in question (constraint, trigger, or view or
> > materialized view), essentially using the same facility as used to run
> > security definer functions. Then, as an optimization only, skip actually
> > doing the security definer stuff (which I understand to be slow) when it
> > can be proven by the planner to be safe to do so (i.e., no difference in
> > result).
>
> I am interested to know how you think the planner could prove that.
>

The same general way it establishes that any other transformation is OK: it
recognizes patterns that are known to allow the application of an
optimization technique. Inevitably, not just in practice but even in theory
due to well-known basic results in the theory of computability, there will
be situations where optimizations could be applied but which will not be
recognized by the planner.

So for example maybe when a check constraint is defined the system could
check to see if it consists entirely of calls to stable functions provided
with the system and if so marks it as safe to run as the effective user
rather than as the constraint owner. I foresee all sorts of complications
including unforeseen ones but it should be clear that I not proposing to
violate any theorems of Gödel.