Horton

Re: vacuumdb: permission denied for schema "pg_temp_7"

Nathan Bossart <nathandbossart@gmail.com>

View thread
From: Nathan Bossart <nathandbossart@gmail.com>
To: Noah Misch <noah@leadboat.com>
Cc: vaibhave postgres <postgresvaibhave@gmail.com>, pgsql-bugs@lists.postgresql.org, vsekar@microsoft.com
Date: 2024-09-20T20:59:32Z
Lists: pgsql-bugs

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. vacuumdb: Schema-qualify operator in catalog query's WHERE clause.

  2. reindexdb: Skip reindexing temporary tables and indexes.

  3. vacuumdb: Skip temporary tables in query to build list of relations

  4. Use catalog query to discover tables to process in vacuumdb

On Fri, Sep 20, 2024 at 12:07:31PM -0700, Noah Misch wrote:
> v12 introduced this regression.  I suspect it started when commit e0c2933 "Use
> catalog query to discover tables to process in vacuumdb" switched vacuumdb
> from a simple "VACUUM;" command to per-table commands.  Non-superuser vacuumdb
> must be rare indeed for this to go unnoticed long enough to leave all
> supported branches affected.

I think the bug actually predates that commit, but it was only broken when
--jobs > 1.  Commit e0c2933 just broke the --jobs == 1 case, too.

> That helps, but we'd probably want to do something more general about vacuumdb
> and schema USAGE permission.

Hm.  I think filtering out schemas for which you lack USAGE makes sense
when neither --schema nor --table are specified, but if the user lists an
object they can't vacuum, we should probably fail.  My current thinking is
that we could still filter when --exclude-schema is used, but I'm curious
what others think.

You might also be interested in this thread about VACUUM and USAGE [0].

> Thanks for the report.

+1

[0] https://postgr.es/m/flat/56596b81-088f-4c0c-9a88-b5f27a7a62e9%40oss.nttdata.com

-- 
nathan