Re: vacuumdb: permission denied for schema "pg_temp_7"

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