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

David G. Johnston <david.g.johnston@gmail.com>

From: "David G. Johnston" <david.g.johnston@gmail.com>

To: jian he <jian.universality@gmail.com>

Cc: Feike Steenbergen <feikesteenbergen@gmail.com>, PostgreSQL mailing lists <pgsql-hackers@postgresql.org>

Date: 2025-05-24T14:00:22Z

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 →

Restrict virtual columns to use built-in functions and types
- 0cd69b3d7ef3 18.0 landed
Fix virtual generated column type checking for ALTER TABLE
- 49fe1c83ecf3 18.0 landed
Expand virtual generated columns in the planner
- 1e4351af329f 18.0 cited

On Saturday, May 24, 2025, jian he <jian.universality@gmail.com> wrote:

> On Sat, May 24, 2025 at 2:39 PM Feike Steenbergen
> <feikesteenbergen@gmail.com> wrote:
> >
> > The loophole is this:
> >
> > - the generated virtual column can use a user-defined function
> > - when running SELECT against that column by a superuser
> >   the function is called within the context of a superuser
> > - this in turn allows the regular user to run any code within
> >   the context of superuser
>
> sorry, I am not fully sure what this means.
> a minimum sql reproducer would be great.
>

This is same complaint being made against “security invoker” triggers
existing/being the default.  Or the general risk in higher privileged users
running security invoker functions written by lesser privileged users.

The features conform to our existing security model design.  Discussions
are happening as pertains to that model and the OP should chime in there to
contribute to the overall position of the project and not relegate the
complaint to any one particular feature.

David J.