Date: Fri, 20 Sep 2024 12:07:31 -0700
From: Noah Misch <noah@leadboat.com>
To: vaibhave postgres <postgresvaibhave@gmail.com>,
	nathandbossart@gmail.com
Cc: pgsql-bugs@lists.postgresql.org, vsekar@microsoft.com
Subject: Re: vacuumdb: permission denied for schema "pg_temp_7"
Message-ID: <20240920190731.37.nmisch@google.com>
References: 
 <CAM_eQjwfAR=y3G1fGyS1U9FTmc+FyJm9amNfY2QCZBnDDbNPZg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAM_eQjwfAR=y3G1fGyS1U9FTmc+FyJm9amNfY2QCZBnDDbNPZg@mail.gmail.com>
User-Agent: Mutt/2.2.12 (2023-09-09)
Archived-At: 
 <https://www.postgresql.org/message-id/20240920190731.37.nmisch%40google.com>
Precedence: bulk

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