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
