Horton

Re: vacuumdb: permission denied for schema "pg_temp_7"

Noah Misch <noah@leadboat.com>

View thread
From: Noah Misch <noah@leadboat.com>
To: vaibhave postgres <postgresvaibhave@gmail.com>, nathandbossart@gmail.com
Cc: pgsql-bugs@lists.postgresql.org, vsekar@microsoft.com
Date: 2024-09-20T19:07:31Z
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

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.