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

Jeff Davis <pgsql@j-davis.com>

From: Jeff Davis <pgsql@j-davis.com>

To: Tom Lane <tgl@sss.pgh.pa.us>, "David G. Johnston" <david.g.johnston@gmail.com>

Cc: Robert Haas <robertmhaas@gmail.com>, Feike Steenbergen <feikesteenbergen@gmail.com>, jian he <jian.universality@gmail.com>, PostgreSQL mailing lists <pgsql-hackers@postgresql.org>

Date: 2025-06-03T00:23:44Z

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 →

Restrict virtual columns to use built-in functions and types
- 0cd69b3d7ef3 18.0 landed
Fix virtual generated column type checking for ALTER TABLE
- 49fe1c83ecf3 18.0 landed
Expand virtual generated columns in the planner
- 1e4351af329f 18.0 cited

On Thu, 2025-05-29 at 11:12 -0400, Tom Lane wrote:
> Perhaps a compromise is to invent RunAsUser but only apply it to
> virtual columns for now, leaving the view case as a research
> project.  Then we aren't destroying the performance of any
> existing queries.

Could we instead check that the expression is safe at the time the
generated column is created? For the purposes of this thread, "safe"
means "safe for the one running the SELECT".

If the expression only involves functions and operators that are owned
by the superuser (and/or in pg_catalog), or SECURITY DEFINER, then I
think it's safe. It's not released yet, so we can start out more
conservative (as long as it works for most use cases) and then make it
a more precise check in the future.

There are some details to work out. For instance, what happens if a
function starts out as SECURITY DEFINER and then someone changes it
later?

Regards,
	Jeff Davis