Horton

Re: vacuumdb: permission denied for schema "pg_temp_7"

Michael Paquier <michael@paquier.xyz>

View thread
From: Michael Paquier <michael@paquier.xyz>
To: Christophe Pettus <xof@thebuild.com>
Cc: Nathan Bossart <nathandbossart@gmail.com>, vaibhave postgres <postgresvaibhave@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, Fujii Masao <masao.fujii@oss.nttdata.com>, Noah Misch <noah@leadboat.com>, pgsql-bugs@lists.postgresql.org, vsekar@microsoft.com
Date: 2024-09-24T01:08:43Z
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

Attachments

On Mon, Sep 23, 2024 at 11:48:21AM -0700, Christophe Pettus wrote:
> I'm happy to pick it up iff the current patch submitter doesn't want
> to continue with it.

Somewhat missed this thread, thanks for the latest activity.

If we apply a restriction on the temporary persistence, then we know
that vacuumdb will always have a WHERE clause so we can simplify the
code and remove the business with has_where like in the attached.

About the permission restrictions depending on the objects listed, the
filtering query uses currently a list of VALUES in a CTE.  Perhaps it
would be more elegant to switch that to a SELECT with some
has_schema_privilege() for the cases where OBJFILTER_SCHEMA is
used?

There permission checks with USAGE and MAINTAIN are broader, so I'd
choose to add a skip on the temp persistence first and backpatch it
down to 12 as there is also a performance argument.  Then tackle the
rest by reworking the VALUES part in the CTE.

The REINDEX one is really something that we need to care about?  One
needs database ownership for a database-level REINDEX, or schema-level
ownership for the schema-level one.  Stricter restrictions apply
compared to vacuum_rel().

Side note: we could use more CppAsString2() for relpersistence in
src/bin/, like in pg_amcheck, if we go this way.
--
Michael