Horton

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

Matthias van de Meent <boekewurm+postgres@gmail.com>

View thread
From: Matthias van de Meent <boekewurm+postgres@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Feike Steenbergen <feikesteenbergen@gmail.com>, jian he <jian.universality@gmail.com>, "David G. Johnston" <david.g.johnston@gmail.com>, PostgreSQL mailing lists <pgsql-hackers@postgresql.org>
Date: 2025-05-29T18:24:03Z
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 Thu, 29 May 2025 at 15:44, Robert Haas <robertmhaas@gmail.com> wrote:
> But so far - apart from this feature - we
> have managed to avoid making it categorically unsafe for the superuser
> to run "SELECT * FROM table"

With CREATE RULE [0], a table owner can redefine what happens during
e.g. SELECT * FROM table. This also includes outputting alternative
data sources, or e.g. calling a user-defined SECURITY INVOKER
function.

PG18 still seems to have support for CREATE RULE, so virtual generated
columns don't create a completely new security issue (blind SELECT *
FROM user_defined_table was already insecure) but rather a new threat
vector to this privilege escalation.

Kind regards,

Matthias van de Meent
Neon (https://neon.tech)

[0] https://www.postgresql.org/docs/18/sql-createrule.html