Re: pg_dump needs SELECT privileges on irrelevant extension table

Jacob Champion <jchampion(at)timescale(dot)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