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

Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)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