From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jacob Champion <jchampion@timescale.com>
cc: PostgreSQL Bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: pg_dump needs SELECT privileges on irrelevant extension table
In-reply-to: <00d46a48-3324-d9a0-49bf-e7f0f11d1038@timescale.com>
References: <00d46a48-3324-d9a0-49bf-e7f0f11d1038@timescale.com>
Comments: In-reply-to Jacob Champion <jchampion@timescale.com>
	message dated "Mon, 20 Mar 2023 09:44:24 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <954418.1679334206.1@sss.pgh.pa.us>
Date: Mon, 20 Mar 2023 13:43:26 -0400
Message-ID: <954419.1679334206@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/954419.1679334206%40sss.pgh.pa.us>
Precedence: bulk

Jacob Champion <jchampion@timescale.com> writes:
> We have a situation where we need to revoke SELECT on a table that
> belongs to our extension, and we also need to let less privileged users
> dump the extension's external config tables.

In general, we don't expect that random minimum-privilege users can do
a database-wide pg_dump, so I'm not entirely sure that I buy that this
is a case we should cater to.  Why shouldn't your dump user have enough
privilege to take this lock?

I'd be more willing to consider the proposed patch if it weren't such
a hack --- as you say, it doesn't fix the problem when the table has
policies, so it's hardly a general-purpose solution.  I fear that it's
also fairly expensive: adding sub-selects to the query we must do
before we can lock any tables is not appetizing, because making that
window wider adds to the risk of deadlocks, dump failures, etc.

			regards, tom lane