public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Karsten Hilbert <[email protected]>
Cc: [email protected]
Subject: Re: Q on SELECT column list pushdown from view to table
Date: Wed, 26 Mar 2025 16:36:55 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>

Karsten Hilbert <[email protected]> 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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Q on SELECT column list pushdown from view to table
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox