Horton

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

Feike Steenbergen <feikesteenbergen@gmail.com>

View thread
From: Feike Steenbergen <feikesteenbergen@gmail.com>
To: jian he <jian.universality@gmail.com>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>, PostgreSQL mailing lists <pgsql-hackers@postgresql.org>
Date: 2025-05-26T14:51:51Z
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

On Mon, 26 May 2025 at 16:17, jian he <jian.universality@gmail.com> wrote:
> calling exploit_generated.exploit by normal user or superuser the
> effects are different,
> that by definition is not IMMUTABLE.

Yeah, i know this is *wrong* usage of IMMUTABLE, the point is that a rogue
regular user *can* use this pattern to become superuser.

> I think I understand what you mean.
> but still that is not related to the generated column.

It is, as before this feature, it was safe to, as a superuser:

    SELECT * FROM untrusted_table

However, as of now, in pg18 this may lead to any code defined by a
regular user to run in the context of a superuser.

I'm aware that this already exists (pg17) for:

- superuser selecting from a user defined view
- superuser executing a user defined function
- superuser inserting into a user defined table

However, this is *new* behavior, increasing the possibility of exploits.

Certain db clients (I checked DBeaver and pgAdmin4) allow a user to
peek into the table details using their GUI. When connected as a superuser,
that would trigger this exploit.

As a sidenote: It may be useful for the pgAdmin4/DBeaver and other clients
to somehow block this behavior when connected as a superuser anyway?