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

Hi,

While evaluating the PostgreSQL 18 beta, I had a thought experiment where I
thought it might be possible to use the new virtual generated columns to
gain
superuser privileges for a regular user.

Attached is a sample exploit, that achieves this, key components:

- the GENERATED column uses a user defined immutable function
- this immutable function cannot ALTER ROLE (needs volatile)
- therefore this immutable function calls a volatile function
- the volatile function can contain any security exploit

The problem I think for PostgreSQL 18 is quite high, as I think it is more
likely that a superuser issues a `SELECT` against any table (graphical DB
clients for example, showing the first N rows in a window)

However, the problem *also* exists for the GENERATED [...] STORED columns,
so
probably all pg versions >= 12? although it is less likely that a superuser
would `INSERT` into those tables?

Here's a transcript of the output of the file that shows it:

You are now connected to database "postgres" as user "regular". CREATE
FUNCTION
CREATE FUNCTION CREATE TABLE INSERT 0 1 i | j ---+--- 1 | 1 (1 row)

You are now connected to database "postgres" as user "postgres". stage |
case
-------------------------------+-------------- Before superuser did a
SELECT |
regular user (1 row)

i | j
---+--- 1 | 1 (1 row)

stage | case
------------------------------+----------- After superuser did a SELECT |
superuser (1 row)

Forwarding this discussion from security(at)postgresql(dot)org:

On Fri, 16 May 2025 at 23:12, Noah Misch <noah(at)leadboat(dot)com> wrote:
>
> On Fri, May 16, 2025 at 07:16:13PM +0200, Feike Steenbergen wrote:
> > On Fri, 16 May 2025 at 19:00, Noah Misch <noah(at)leadboat(dot)com> wrote:
> > > Thanks for the report. Does this attack work if the reader uses COPY
> > instead of SELECT? COPY has been safe, so we should think twice before
> > > making it unsafe.
> >
> > Plain COPY seems safe, that's a very good thing:
> >
> > -- This does not cause the regular user to become superuser COPY
> > exploit_generated.generated_sample TO STDOUT;
> >
> > -- This is safe, with a useful error: COPY
> > exploit_generated.generated_sample(i, j) TO STDOUT;
> >
> > ERROR: column "j" is a generated column DETAIL: Generated columns
cannot be
> > used in COPY.
> >
> > Copy wrapped around a select however is not safe, (not a suprise I
think):
> >
> > -- This is unsafe COPY (SELECT * FROM
exploit_generated.generated_sample) TO
> > STDOUT;
>
> That suggests virtual generated table columns have the same risk as
views, not
> more risk. That is good news.
>
> > > In other words, virtual generated columns make a table into a hybrid
of
> > > view and table, so anything odd that we've needed to do to views and
> > foreign tables may apply to tables containing virtual generated columns.
> >
> > Yeah, that to me is the gist of the issue, that a plain `SELECT`
against any
> > such table can be used to run arbitrary function calls.

On Fri, 16 May 2025 at 23:12, Noah Misch <noah(at)leadboat(dot)com> wrote:
>
> If nothing else, I think the project will need to extend
> restrict_nonsystem_relation_kind so virtual generated columns become one
of
> the things it can block.