From: Noah Misch <noah@leadboat.com>
To: vaibhave postgres <postgresvaibhave@gmail.com>
To: nathandbossart@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Cc: vsekar@microsoft.com
Subject: Re: vacuumdb: permission denied for schema "pg_temp_7"
Date: Fri, 20 Sep 2024 12:07:31 -0700
Message-ID: <20240920190731.37.nmisch@google.com> (raw)
In-Reply-To: <CAM_eQjwfAR=y3G1fGyS1U9FTmc+FyJm9amNfY2QCZBnDDbNPZg@mail.gmail.com>
References: <CAM_eQjwfAR=y3G1fGyS1U9FTmc+FyJm9amNfY2QCZBnDDbNPZg@mail.gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noah@leadboat.com, postgresvaibhave@gmail.com, nathandbossart@gmail.com, pgsql-bugs@lists.postgresql.org, vsekar@microsoft.com
Subject: Re: vacuumdb: permission denied for schema "pg_temp_7"
In-Reply-To: <20240920190731.37.nmisch@google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox