Re: vacuumdb: permission denied for schema "pg_temp_7"

On Sat, Jul 06, 2024 at 05:19:39PM +0530, vaibhave postgres wrote:
> Repo steps
>
> 1. Create a temporary table
>
> sample => CREATE TEMPORARY TABLE temp_employees (
> > id SERIAL PRIMARY KEY,
> > name VARCHAR(100),
> > position VARCHAR(50),
> > salary NUMERIC(10, 2)
> > );
> > CREATE TABLE
> > sample => \dt pg_temp_*.*
> > List of relations
> > Schema | Name | Type | Owner
> > -----------+----------------+-------+----------
> > pg_temp_7 | temp_employees | table | vaibhave
> > (1 row)
>
>
> 2. Run vacuumdb
>
> vacuumdb: vacuuming database "sample"
> > vacuumdb: error: processing of database " sample " failed: ERROR:
> > permission denied for schema pg_temp_7
>
>
> Temporary tables can only be accessed within the session which created
> them. They should be skipped during vacuumdb.

This happens when a non-superuser runs vacuumdb while a different user has a
temp table. This isn't specific to temp tables; it arises for any schema on
which the vacuumdb user lacks USAGE privilege.

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.

> Suggested Patch is attached

> From ca78eb35b59cc398a37d36c27373dd64eb3a8f77 Mon Sep 17 00:00:00 2001
> From: VaibhaveS <vaibhavedavey(at)gmail(dot)com>
> Date: Sat, 6 Jul 2024 17:15:33 +0530
> Subject: [PATCH] Skip temporary tables in vacuumdb.
>
> ---
> src/bin/scripts/vacuumdb.c | 5 +++++
> 1 file changed, 5 insertions(+)
>
> diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
> index 7138c6e97e..3dbda53b72 100644
> --- a/src/bin/scripts/vacuumdb.c
> +++ b/src/bin/scripts/vacuumdb.c
> @@ -733,6 +733,11 @@ vacuum_one_database(ConnParams *cparams,
> has_where = true;
> }
>
> + /*
> + * Exclude temporary tables
> + */
> + appendPQExpBufferStr(&catalog_query, " AND c.relpersistence <> 't'");

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

Thanks for the report.