Horton

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

Jeff Davis <pgsql@j-davis.com>

View thread
From: Jeff Davis <pgsql@j-davis.com>
To: Robert Haas <robertmhaas@gmail.com>, Bruce Momjian <bruce@momjian.us>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Isaac Morland <isaac.morland@gmail.com>, jian he <jian.universality@gmail.com>, "David G. Johnston" <david.g.johnston@gmail.com>, Feike Steenbergen <feikesteenbergen@gmail.com>, PostgreSQL mailing lists <pgsql-hackers@postgresql.org>
Date: 2025-06-03T17:11:17Z
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 Tue, 2025-06-03 at 11:27 -0400, Robert Haas wrote:
> That's true, but search_path manipulation is still enough to cause
> quite a few problems.

+1. The only defense is to declare the function with "SET search_path",
but until recently, that was a major performance penalty for cheap
functions. And even though it's better now, adding that to all function
declarations is a lot of boilerplate we don't have evidence that it's
standard procedure for many users yet.

> A
> function trust mechanism doesn't necessarily have to do that; a
> particular installation that is unbothered by the security exposure
> can simply have all users trust all other users, and nobody is any
> worse off than today.

True, it avoids breakage, and for that reason has a lot of merit. But I
see a couple problems along that route, as well:

1. The trust mechanism means, as far as I can tell, absolute trust in
the user that wrote the function (if they violate the trust, they can
take over your role). That doesn't seem to be in the spirit of
GRANT/REVOKE, which are much more fine-grained.

2. It's another layer and I think we'd struggle to document how they
fit together, and users would struggle to get it right. If you
absolutely trust someone, should you add them to your role or trust
them to write functions, or both?

But I am open to further discussion. A simpler version that just says
"I don't trust anyone, don't execute any non-superuser-owned code"
would be very useful at a practical level, e.g. for pg_dump.

Regards,
	Jeff Davis