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

On Mon, May 26, 2025 at 4:56 PM Feike Steenbergen
<feikesteenbergen(at)gmail(dot)com> wrote:
>
>
>
> 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:
>

I think I understand what you mean.
but still that is not related to the generated column.

calling exploit_generated.exploit by normal user or superuser the
effects are different,
that by definition is not IMMUTABLE.

you can simply do the following:
set role regular;
select exploit_generated.exploit(1);
SELECT rolname, rolsuper from pg_roles WHERE rolname = 'regular';
set role postgres;
select exploit_generated.exploit(1);
SELECT rolname, rolsuper from pg_roles WHERE rolname = 'regular';