Re: permission denied for pg_temp_XX when vacuuming

Tobias Lott <tobias(dot)lott(at)devoteam(dot)com> writes:
> Yes that's strange. A lot of pg_XX tables are skipped, but some of these
> pg_temp schemas cause errors.
> Could it be connected to a migration of the database (from an instance
> running PostgreSQL 9.6 to an instance running PostgreSQL 12) done a few
> weeks ago?

I wouldn't have expected a migration to bring any temp tables forward.

Have you had any crashes on this instance (post-migration)? A possible
theory is that crashed backend(s) left behind some temp tables, and then
if vacuumdb's backend process re-uses the backend ID (which determines
the NN in pg_temp_NN) of one of those sessions, it would think those
temp tables are its own and try to vacuum them. Or at least I think
it might. That still fails to explain the permissions errors in any
detail, but at least it offers a reason why vacuumdb would even be
going anywhere near a temp table.

regards, tom lane