Re: vacuumdb: permission denied for schema "pg_temp_7"

Noah Misch <noah@leadboat.com>

From: Noah Misch <noah@leadboat.com>

To: Nathan Bossart <nathandbossart@gmail.com>

Cc: vaibhave postgres <postgresvaibhave@gmail.com>, pgsql-bugs@lists.postgresql.org, vsekar@microsoft.com

Date: 2024-09-20T21:50:30Z

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 →

vacuumdb: Schema-qualify operator in catalog query's WHERE clause.
- eba8cc1af8ec 16.5 landed
- d4ade0bafb75 13.17 landed
- ce6f27857bba 14.14 landed
- 8318f2b170d8 18.0 landed
- 6d047c6a9192 15.9 landed
- 5e0431c32a8f 12.21 landed
- 5bd26e652780 17.1 landed
reindexdb: Skip reindexing temporary tables and indexes.
- 9410f7cbf4ff 13.17 landed
- 88e1153cb3c6 14.14 landed
- 92cc21d158f3 15.9 landed
- 653ce5b8b79c 16.5 landed
- 77f154681981 17.1 landed
- 20cfec896c6a 18.0 landed
vacuumdb: Skip temporary tables in query to build list of relations
- ef57a713580f 12.21 landed
- 9db4598c9c98 13.17 landed
- 60c618216ddb 14.14 landed
- 74eaa0544abf 15.9 landed
- 1ea4d9c001e6 16.5 landed
- 85cb21df673f 17.1 landed
- 1ab67c9dfaad 18.0 landed
Use catalog query to discover tables to process in vacuumdb
- e0c2933a767c 12.0 cited

On Fri, Sep 20, 2024 at 03:59:32PM -0500, Nathan Bossart wrote:
> 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.

Agreed.

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

That all sounds good to me.

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

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

The outcome is odd, but I'm not worried about it.