Horton

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

Christoph Berg <myon@debian.org>

View thread
From: Christoph Berg <myon@debian.org>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Peter Eisentraut <peter@eisentraut.org>, Feike Steenbergen <feikesteenbergen@gmail.com>, PostgreSQL mailing lists <pgsql-hackers@postgresql.org>
Date: 2025-06-05T15:24:13Z
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 →

  1. Restrict virtual columns to use built-in functions and types

  2. Fix virtual generated column type checking for ALTER TABLE

  3. Expand virtual generated columns in the planner

Re: Robert Haas
> I don't think this is sufficient to fix the problem. We have built-in
> functions that are unsafe. These include LO functions like loread(),
> lowrite(), lo_unlink(); functions that change session state like
> set_config() and setseed(); functions that allow arbitrary query
> execution like query_to_xml(); slot-manipulation functions like
> pg_drop_replication_slot(); and maybe other things.

That was my thought as well - if user defined functions are
disallowed, just put the exploit code into the expression.

Turns out that doesn't work:

=# create table pwn (id int, pwn boolean generated always as (pg_reload_conf()));
ERROR:  42P17: generation expression is not immutable

So the question is, are all built-in *immutable* functions safe?

Extending the idea, perhaps the check could be moved to run-time and
recursively check that only immutable functions are called, including
user-defined immutable functions?

Christoph