Horton

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

Robert Haas <robertmhaas@gmail.com>

View thread
From: Robert Haas <robertmhaas@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Isaac Morland <isaac.morland@gmail.com>, jian he <jian.universality@gmail.com>, Jeff Davis <pgsql@j-davis.com>, "David G. Johnston" <david.g.johnston@gmail.com>, Feike Steenbergen <feikesteenbergen@gmail.com>, PostgreSQL mailing lists <pgsql-hackers@postgresql.org>
Date: 2025-06-03T12:58:58Z
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, Jun 2, 2025 at 11:30 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > That being said I would like to see it corrected everywhere.
>
> Yeah, one approach we could take here is to try to move the goalposts
> for this whole topic, understanding that that will mean incompatible
> changes as well as some performance loss.  I'm not sure how many users
> would be happy to take that tradeoff, but some would.  Maybe two
> different operating modes would make it an easier sell?

I still believe that the answer here is some kind of function trust mechanism:

https://www.postgresql.org/message-id/flat/20180809190443.GA14011%40momjian.us#3dda365965c7d95007e58b7551161442
https://www.postgresql.org/message-id/CA%2BTgmoaHpmz9-7ybB17B2qpDoqsi7%3DOWigc-3VBctb6B_x8bKA%40mail.gmail.com
(pretty sure there's also a proposal from Noah, can't find the most
current version of it at the moment)

The problem with just up and changing the behavior is that it will
probably break some use cases (e.g. an ON INSERT/UPDATE trigger that
sets some column to current_user) and, worse still, it probably will
just result in a bunch of security holes in the opposite direction,
where the person inserting into the table is trying to hack the
account of the table owner rather than the other way around. Jeff
Davis has mentioned this hazard before: any time we switch user IDs to
execute code, there are possible attacks in BOTH directions. I haven't
seen a proposal other than function trust (in one of several proposed
variations) that can close all of those holes.

-- 
Robert Haas
EDB: http://www.enterprisedb.com