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

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

> Isaac Morland <isaac(dot)morland(at)gmail(dot)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.