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: Peter Eisentraut <peter@eisentraut.org>
Cc: Feike Steenbergen <feikesteenbergen@gmail.com>, PostgreSQL mailing lists <pgsql-hackers@postgresql.org>
Date: 2025-06-05T14:38: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 Thu, Jun 5, 2025 at 6:49 AM Peter Eisentraut <peter@eisentraut.org> wrote:
> I propose to address this by not allowing the use of user-defined
> functions in generation expressions for now.  The attached patch
> implements this.  This assumes that all built-in functions are
> trustworthy, for this purpose, which seems likely true and likely desirable.
>
> I think the feature is still useful like that, and this approach
> provides a path to add new functionality in the future that grows this
> set of allowed functions, for example by allowing some configurable set
> of "trusted" functions or whatever.

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.

Even if it worked, I think it's an unappealing solution -- we've
worked really hard at extensibility and making decisions based on
object properties rather than what's built-in and what's provided by a
user or an extension. But I also don't think it works.

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