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

On Sat, 24 May 2025 at 15:43, jian he <jian(dot)universality(at)gmail(dot)com> wrote:
> sorry, I am not fully sure what this means. a minimum sql reproducer
would be
> great.

The initial email contains a fully self-contained example of a regular user
becoming a superuser. The only thing the superuser had to do was

SELECT * FROM untrusted_table

> you may check virtual generated column function privilege regress tests on
>
https://git.postgresql.org/cgit/postgresql.git/tree/src/test/regress/sql/generated_virtual.sql#n284
> (from line 284 to line 303)

These regress tests don't seem to cover the case where a superuser selects
from
the virtual generated column

On Sat, 24 May 2025 at 16:00, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:
> This is same complaint being made against “security invoker” triggers
> existing/being the default. Or the general risk in higher privileged
users
> running security invoker functions written by lesser privileged users.

It falls in the same category, however, previously, triggers or security
invoker
functions would not be called when running

SELECT * FROM untrusted_table

However, with the generated virtual columns introduced, a superuser should
*never* run `SELECT *` against a user table, as that may trigger executions
of
these Security Invoker functions.

For PostgreSQL 17 this is true:

- As a superuser, executing a security invoker function is exploitable
- therefore, selecting from a view is exploitable
- therefore, doing DML on a table is exploitable

PostreSQL 18 adds to this:

- therefore, selecting from a table is exploitable

I think adding more surface area for exploits should be avoided, especially
AFAICT in the discussion before, there is a precedent to fixing this style
of
problem:

On Fri, 16 May 2025 at 19:00, Noah Misch <noah(at)leadboat(dot)com> wrote:
> SELECT is fairly unsafe. We ended up with commit 66e9444 (CVE-2024-7348)
to
> make secure use of SELECT feasible in released branches. It sounds like
this
> v18 feature may need changes like commit 66e9444. In other words, virtual
> generated columns make a table into a hybrid of view and table, so
anything
> odd that we've needed to do to views and foreign tables may apply to
tables
> containing virtual generated columns.

Feike