From: Tom Lane <tgl@sss.pgh.pa.us>
To: Karsten Hilbert <Karsten.Hilbert@gmx.net>
cc: pgsql-general@lists.postgresql.org
Subject: Re: Q on SELECT column list pushdown from view to table
In-reply-to: <Z-Q4Pm9_LlLe5Mll@hermes.hilbert.loc>
References: <Z-MsW3AzjLH2S7NN@hermes.hilbert.loc> <1321213.1742943334@sss.pgh.pa.us> <Z-Q4Pm9_LlLe5Mll@hermes.hilbert.loc>
Comments: In-reply-to Karsten Hilbert <Karsten.Hilbert@gmx.net>
	message dated "Wed, 26 Mar 2025 18:24:14 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1776633.1743021415.1@sss.pgh.pa.us>
Date: Wed, 26 Mar 2025 16:36:55 -0400
Message-ID: <1776634.1743021415@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/1776634.1743021415%40sss.pgh.pa.us>
Precedence: bulk

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