pg18: Virtual generated columns are not (yet) safe when superuser selects from them - Mailing list pgsql-hackers

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@postgresql.org:

On Fri, 16 May 2025 at 23:12, Noah Misch <noah@leadboat.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@leadboat.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@leadboat.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.