Re: Q on SELECT column list pushdown from view to table

Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
> Am Tue, Mar 25, 2025 at 06:55:34PM -0400 schrieb Tom Lane:
>> Works fine if you don't mess with the view's security_invoker
>> status.

> I know but doing so was kind of the point.

[ shrug... ] It's not going to work.  When the view is inlined into
the calling query, you end up with the exact equivalent of

select public_col from (
    select
        public_col,
        private_col
    from
        t_partially_private
) as v_partially_private;

With the normal security_definer view processing, the reference
to t_partially_private is treated with the privileges of
v_partially_private's owner, and all is well.  But with
security_invoker, the whole thing is treated with the caller's
privileges, and so the reference to private_col fails.

It is intentional that this happens even if the reference to
private_col is subsequently optimized away.  To do otherwise
would make implementation artifacts in the optimizer far too
visible, and there's also a very strong case that it would
violate the SQL standard.

            regards, tom lane