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.universality@gmail.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.g.johnston@gmail.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@leadboat.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.