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: jian he <jian.universality@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, 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-03T03:03: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 22:52, jian he <jian.universality@gmail.com> wrote:

Do we consider INSERT associated with user defined function a security
> bug?  for
> example, the following, INSERT with a check constraint.
>

[....]

If so, then it's a very old issue...
>

A very old issue for INSERT/UPDATE/DELETE, but until this patch not an
issue for SELECT from a table (although if I understand correctly earlier
discussion SELECT from a view can already be a problem).

That being said I would like to see it corrected everywhere. 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).

Of course in just a few sentences I proposed a research project that could
probably generate dozens of PhD theses, and I'm not volunteering to do any
of this at least not in the near future. But I hope I made clear what is
different about this proposal from the existing situation.