Re: pg_dump needs SELECT privileges on irrelevant extension table

On Mon, Mar 20, 2023 at 10:43 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.

They're neither random nor minimum-privilege -- it's the role with the
most privileges available to our end users. They just can't see the
contents of this table.

> Why shouldn't your dump user have enough
> privilege to take this lock?

The table contains information that's confidential to the superuser.
Other users access it through a view.

> 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.

Right. Does a more general fix exist?

> 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.

I was hoping an EXISTS subselect would be cheap enough, but maybe I
don't have enough entries in pg_policy to see a slowdown. Any
suggestions on an order of magnitude so I can characterize it? Or
would you just like to know at what point I start seeing slower
behavior? (Alternatively: are there cheaper ways to write this query?)

Thanks!
--Jacob