pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedvacuumdb: permission denied for schema "pg_temp_7"
33+ messages / 7 participants
[nested] [flat]
Commits from this thread:
2024-10-07 ce6f27857b 8318f2b170 5e0431c32a d4ade0bafb 5bd26e6527 eba8cc1af8 6d047c6a91 vacuumdb: Schema-qualify operator in catalog query's WHERE clause.
2024-09-30 20cfec896c 77f1546819 653ce5b8b7 92cc21d158 88e1153cb3 9410f7cbf4 reindexdb: Skip reindexing temporary tables and indexes.
2024-09-25 1ab67c9dfa 85cb21df67 1ea4d9c001 74eaa0544a 60c618216d 9db4598c9c ef57a71358 vacuumdb: Skip temporary tables in query to build list of relations
* vacuumdb: permission denied for schema "pg_temp_7"
@ 2024-07-06 11:49 vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
0 siblings, 1 reply; 33+ messages in thread
From: vaibhave postgres @ 2024-07-06 11:49 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: vsekar@microsoft.com
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.
Suggested Patch is attached
Attachments:
0001-Skip-temporary-tables-in-vacuumdb.patch (application/octet-stream, 820B) download
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'");
+
/*
* Execute the catalog query. We use the default search_path for this
* query for consistency with table lookups done elsewhere by the user.
--
2.34.1
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
@ 2024-09-20 19:07 ` Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-21 05:56 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
0 siblings, 2 replies; 33+ messages in thread
From: Noah Misch @ 2024-09-20 19:07 UTC (permalink / raw)
To: vaibhave postgres <postgresvaibhave@gmail.com>; nathandbossart@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org; vsekar@microsoft.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.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
@ 2024-09-20 20:59 ` Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
1 sibling, 1 reply; 33+ messages in thread
From: Nathan Bossart @ 2024-09-20 20:59 UTC (permalink / raw)
To: Noah Misch <noah@leadboat.com>; +Cc: vaibhave postgres <postgresvaibhave@gmail.com>; pgsql-bugs@lists.postgresql.org; vsekar@microsoft.com
On Fri, Sep 20, 2024 at 12:07:31PM -0700, Noah Misch wrote:
> 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.
I think the bug actually predates that commit, but it was only broken when
--jobs > 1. Commit e0c2933 just broke the --jobs == 1 case, too.
> That helps, but we'd probably want to do something more general about vacuumdb
> and schema USAGE permission.
Hm. I think filtering out schemas for which you lack USAGE makes sense
when neither --schema nor --table are specified, but if the user lists an
object they can't vacuum, we should probably fail. My current thinking is
that we could still filter when --exclude-schema is used, but I'm curious
what others think.
You might also be interested in this thread about VACUUM and USAGE [0].
> Thanks for the report.
+1
[0] https://postgr.es/m/flat/56596b81-088f-4c0c-9a88-b5f27a7a62e9%40oss.nttdata.com
--
nathan
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
@ 2024-09-20 21:50 ` Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 33+ messages in thread
From: Noah Misch @ 2024-09-20 21:50 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: vaibhave postgres <postgresvaibhave@gmail.com>; pgsql-bugs@lists.postgresql.org; vsekar@microsoft.com
On Fri, Sep 20, 2024 at 03:59:32PM -0500, Nathan Bossart wrote:
> On Fri, Sep 20, 2024 at 12:07:31PM -0700, Noah Misch wrote:
> > 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.
>
> I think the bug actually predates that commit, but it was only broken when
> --jobs > 1. Commit e0c2933 just broke the --jobs == 1 case, too.
Agreed.
> > That helps, but we'd probably want to do something more general about vacuumdb
> > and schema USAGE permission.
>
> Hm. I think filtering out schemas for which you lack USAGE makes sense
> when neither --schema nor --table are specified, but if the user lists an
> object they can't vacuum, we should probably fail. My current thinking is
> that we could still filter when --exclude-schema is used, but I'm curious
> what others think.
That all sounds good to me.
> You might also be interested in this thread about VACUUM and USAGE [0].
> [0] https://postgr.es/m/flat/56596b81-088f-4c0c-9a88-b5f27a7a62e9%40oss.nttdata.com
The outcome is odd, but I'm not worried about it.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
@ 2024-09-20 23:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 05:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
0 siblings, 1 reply; 33+ messages in thread
From: Tom Lane @ 2024-09-20 23:07 UTC (permalink / raw)
To: Noah Misch <noah@leadboat.com>; +Cc: Nathan Bossart <nathandbossart@gmail.com>; vaibhave postgres <postgresvaibhave@gmail.com>; pgsql-bugs@lists.postgresql.org; vsekar@microsoft.com
Noah Misch <noah@leadboat.com> writes:
>>>> That helps, but we'd probably want to do something more general about vacuumdb
>>>> and schema USAGE permission.
I agree a more general fix is needed, but I think excluding temp
tables as suggested is a good idea for performance, independently of
permissions concerns. vacuum_rel() will ignore requests to vacuum
such tables, which is why we've not heard complaints before, but
nonetheless we're wasting server round trips by issuing those
requests.
regards, tom lane
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-09-21 05:59 ` Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-21 06:03 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 33+ messages in thread
From: Fujii Masao @ 2024-09-21 05:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Noah Misch <noah@leadboat.com>; +Cc: Nathan Bossart <nathandbossart@gmail.com>; vaibhave postgres <postgresvaibhave@gmail.com>; pgsql-bugs@lists.postgresql.org; vsekar@microsoft.com
On 2024/09/21 8:07, Tom Lane wrote:
> Noah Misch <noah@leadboat.com> writes:
>>>>> That helps, but we'd probably want to do something more general about vacuumdb
>>>>> and schema USAGE permission.
>
> I agree a more general fix is needed, but I think excluding temp
> tables as suggested is a good idea for performance, independently of
> permissions concerns. vacuum_rel() will ignore requests to vacuum
> such tables, which is why we've not heard complaints before, but
> nonetheless we're wasting server round trips by issuing those
> requests.
+1
It looks like reindexdb has the same issue. It would be good to
update reindexdb to skip temp tables as well to fix this.
+ appendPQExpBufferStr(&catalog_query, " AND c.relpersistence <> 't'");
For the proposed patch, it seems better to use CppAsString2(RELPERSISTENCE_TEMP)
instead of 't'.
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 05:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
@ 2024-09-21 06:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 06:12 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
0 siblings, 1 reply; 33+ messages in thread
From: Tom Lane @ 2024-09-21 06:03 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@oss.nttdata.com>; +Cc: Noah Misch <noah@leadboat.com>; Nathan Bossart <nathandbossart@gmail.com>; vaibhave postgres <postgresvaibhave@gmail.com>; pgsql-bugs@lists.postgresql.org; vsekar@microsoft.com
Fujii Masao <masao.fujii@oss.nttdata.com> writes:
> It looks like reindexdb has the same issue. It would be good to
> update reindexdb to skip temp tables as well to fix this.
Agreed.
> For the proposed patch, it seems better to use CppAsString2(RELPERSISTENCE_TEMP)
> instead of 't'.
+1, if we can easily avoid hard-coding that value we should do so.
It's not that we're going to change the value; it's that it makes
it way easier to grep the source tree for relevant code.
regards, tom lane
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 05:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-21 06:03 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-09-21 06:12 ` vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-23 18:24 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 33+ messages in thread
From: vaibhave postgres @ 2024-09-21 06:12 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Fujii Masao <masao.fujii@oss.nttdata.com>; Noah Misch <noah@leadboat.com>; Nathan Bossart <nathandbossart@gmail.com>; pgsql-bugs@lists.postgresql.org; vsekar@microsoft.com
Thanks for the review and feedback.
On Sat, Sep 21, 2024 at 11:33 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Fujii Masao <masao.fujii@oss.nttdata.com> writes:
> > It looks like reindexdb has the same issue. It would be good to
> > update reindexdb to skip temp tables as well to fix this.
>
> Agreed.
>
> > For the proposed patch, it seems better to use
> CppAsString2(RELPERSISTENCE_TEMP)
> > instead of 't'.
>
> +1, if we can easily avoid hard-coding that value we should do so.
> It's not that we're going to change the value; it's that it makes
> it way easier to grep the source tree for relevant code.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 05:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-21 06:03 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 06:12 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
@ 2024-09-23 18:24 ` Nathan Bossart <nathandbossart@gmail.com>
2024-09-23 18:48 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
0 siblings, 1 reply; 33+ messages in thread
From: Nathan Bossart @ 2024-09-23 18:24 UTC (permalink / raw)
To: vaibhave postgres <postgresvaibhave@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Fujii Masao <masao.fujii@oss.nttdata.com>; Noah Misch <noah@leadboat.com>; pgsql-bugs@lists.postgresql.org; vsekar@microsoft.com
On Sat, Sep 21, 2024 at 11:42:19AM +0530, vaibhave postgres wrote:
> Thanks for the review and feedback.
Are you planning to submit an updated patch? I don't think there's a
tremendous amount of urgency to fix this since it's been broken for so
long, but it'd be good to know whether someone is planning to pick it up.
--
nathan
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 05:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-21 06:03 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 06:12 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-23 18:24 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
@ 2024-09-23 18:48 ` Christophe Pettus <xof@thebuild.com>
2024-09-24 01:08 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
2024-09-24 01:09 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
0 siblings, 2 replies; 33+ messages in thread
From: Christophe Pettus @ 2024-09-23 18:48 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: vaibhave postgres <postgresvaibhave@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Fujii Masao <masao.fujii@oss.nttdata.com>; Noah Misch <noah@leadboat.com>; pgsql-bugs@lists.postgresql.org; vsekar@microsoft.com
> On Sep 23, 2024, at 11:24, Nathan Bossart <nathandbossart@gmail.com> wrote:
> Are you planning to submit an updated patch? I don't think there's a
> tremendous amount of urgency to fix this since it's been broken for so
> long, but it'd be good to know whether someone is planning to pick it up.
I'm happy to pick it up iff the current patch submitter doesn't want to continue with it.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 05:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-21 06:03 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 06:12 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-23 18:24 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-23 18:48 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
@ 2024-09-24 01:08 ` Michael Paquier <michael@paquier.xyz>
2024-09-24 14:20 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
1 sibling, 1 reply; 33+ messages in thread
From: Michael Paquier @ 2024-09-24 01:08 UTC (permalink / raw)
To: Christophe Pettus <xof@thebuild.com>; +Cc: Nathan Bossart <nathandbossart@gmail.com>; vaibhave postgres <postgresvaibhave@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Fujii Masao <masao.fujii@oss.nttdata.com>; Noah Misch <noah@leadboat.com>; pgsql-bugs@lists.postgresql.org; vsekar@microsoft.com
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
Attachments:
v2-0001-vacuumdb-Skip-temporary-tables-in-filtering-query.patch (text/x-diff, 3.9K) download
From a234eb7a0ad24a9d5641168346ce608515f484c2 Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@paquier.xyz>
Date: Tue, 24 Sep 2024 10:00:29 +0900
Subject: [PATCH v2] vacuumdb: Skip temporary tables in filtering query
Running vacuumdb with a non-superuser when another user has created a
temporary table would lead to a mid-flight failure.
There are other issues related to schema-level USAGE, which will be
tackled later.
Author: VaibhaveS
Discussion: https://postgr.es/m/CAM_eQjwfAR=y3G1fGyS1U9FTmc+FyJm9amNfY2QCZBnDDbNPZg@mail.gmail.com
Backpatch-through: 12
---
src/bin/scripts/vacuumdb.c | 40 ++++++++++++++++++++++++--------------
1 file changed, 25 insertions(+), 15 deletions(-)
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 7c33e13e1a..249622068a 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -491,7 +491,6 @@ vacuum_one_database(ConnParams *cparams,
int ntups;
bool failed = false;
bool objects_listed = false;
- bool has_where = false;
const char *initcmd;
const char *stage_commands[] = {
"SET default_statistics_target=1; SET vacuum_cost_delay=0;",
@@ -665,7 +664,10 @@ vacuum_one_database(ConnParams *cparams,
" LEFT JOIN pg_catalog.pg_class t"
" ON c.reltoastrelid OPERATOR(pg_catalog.=) t.oid\n");
- /* Used to match the tables or schemas listed by the user */
+ /*
+ * Used to match the tables or schemas listed by the user, completing the
+ * JOIN clause.
+ */
if (objects_listed)
{
appendPQExpBufferStr(&catalog_query, " LEFT JOIN listed_objects"
@@ -676,14 +678,26 @@ vacuum_one_database(ConnParams *cparams,
appendPQExpBufferStr(&catalog_query, "c.oid\n");
else
appendPQExpBufferStr(&catalog_query, "ns.oid\n");
+ }
+ /*
+ * Exclude temporary tables, beginning the WHERE clause.
+ */
+ appendPQExpBufferStr(&catalog_query,
+ " WHERE c.relpersistence != " CppAsString2(RELPERSISTENCE_TEMP));
+
+ /*
+ * Used to match the tables or schemas listed by the user, for the WHERE
+ * clause.
+ */
+ if (objects_listed)
+ {
if (objfilter & OBJFILTER_SCHEMA_EXCLUDE)
appendPQExpBuffer(&catalog_query,
- " WHERE listed_objects.object_oid IS NULL\n");
+ " AND listed_objects.object_oid IS NULL\n");
else
appendPQExpBuffer(&catalog_query,
- " WHERE listed_objects.object_oid IS NOT NULL\n");
- has_where = true;
+ " AND listed_objects.object_oid IS NOT NULL\n");
}
/*
@@ -695,11 +709,9 @@ vacuum_one_database(ConnParams *cparams,
if ((objfilter & OBJFILTER_TABLE) == 0)
{
appendPQExpBuffer(&catalog_query,
- " %s c.relkind OPERATOR(pg_catalog.=) ANY (array["
+ " AND c.relkind OPERATOR(pg_catalog.=) ANY (array["
CppAsString2(RELKIND_RELATION) ", "
- CppAsString2(RELKIND_MATVIEW) "])\n",
- has_where ? "AND" : "WHERE");
- has_where = true;
+ CppAsString2(RELKIND_MATVIEW) "])\n");
}
/*
@@ -712,25 +724,23 @@ vacuum_one_database(ConnParams *cparams,
if (vacopts->min_xid_age != 0)
{
appendPQExpBuffer(&catalog_query,
- " %s GREATEST(pg_catalog.age(c.relfrozenxid),"
+ " AND GREATEST(pg_catalog.age(c.relfrozenxid),"
" pg_catalog.age(t.relfrozenxid)) "
" OPERATOR(pg_catalog.>=) '%d'::pg_catalog.int4\n"
" AND c.relfrozenxid OPERATOR(pg_catalog.!=)"
" '0'::pg_catalog.xid\n",
- has_where ? "AND" : "WHERE", vacopts->min_xid_age);
- has_where = true;
+ vacopts->min_xid_age);
}
if (vacopts->min_mxid_age != 0)
{
appendPQExpBuffer(&catalog_query,
- " %s GREATEST(pg_catalog.mxid_age(c.relminmxid),"
+ " AND GREATEST(pg_catalog.mxid_age(c.relminmxid),"
" pg_catalog.mxid_age(t.relminmxid)) OPERATOR(pg_catalog.>=)"
" '%d'::pg_catalog.int4\n"
" AND c.relminmxid OPERATOR(pg_catalog.!=)"
" '0'::pg_catalog.xid\n",
- has_where ? "AND" : "WHERE", vacopts->min_mxid_age);
- has_where = true;
+ vacopts->min_mxid_age);
}
/*
--
2.45.2
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 05:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-21 06:03 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 06:12 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-23 18:24 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-23 18:48 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
2024-09-24 01:08 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
@ 2024-09-24 14:20 ` Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-24 14:26 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-24 23:10 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
2024-09-24 23:20 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
0 siblings, 3 replies; 33+ messages in thread
From: Fujii Masao @ 2024-09-24 14:20 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; Christophe Pettus <xof@thebuild.com>; +Cc: Nathan Bossart <nathandbossart@gmail.com>; vaibhave postgres <postgresvaibhave@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Noah Misch <noah@leadboat.com>; pgsql-bugs@lists.postgresql.org; vsekar@microsoft.com
On 2024/09/24 10:08, Michael Paquier wrote:
> 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.
LGTM.
> 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.
Are you suggesting that any objects a user lacks sufficient privileges for
should be silently excluded from vacuuming? This could make vacuumdb appear
successful because no errors occur, but some tables the user intended to
vacuum might be skipped without notice. That seems more problematic to me.
Sorry if I misunderstood your point.
> 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().
Is ownership really necessary in these cases? A similar issue can easily
happen with reindexdb as follows, so I think that should be fixed as well.
=# CREATE ROLE admin WITH LOGIN;
=# GRANT pg_maintain TO admin;
=# CREATE TEMP TABLE tt (i int primary key);
=# \! bin/reindexdb -U admin -j 2 postgres
reindexdb: error: query failed: ERROR: permission denied for schema pg_temp_0
LINE 4: AND c.oid OPERATOR(pg_catalog.=) 'pg_temp_0.tt'::pg_catalo...
^
reindexdb: detail: Query was: SELECT c.relname, ns.nspname
FROM pg_catalog.pg_class c, pg_catalog.pg_namespace ns
WHERE c.relnamespace OPERATOR(pg_catalog.=) ns.oid
AND c.oid OPERATOR(pg_catalog.=) 'pg_temp_0.tt'::pg_catalog.regclass;
We should probably add a condition like "relpersistence != CppAsString2(RELPERSISTENCE_TEMP)"
to the queries in get_parallel_object_list().
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 05:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-21 06:03 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 06:12 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-23 18:24 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-23 18:48 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
2024-09-24 01:08 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
2024-09-24 14:20 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
@ 2024-09-24 14:26 ` Nathan Bossart <nathandbossart@gmail.com>
2024-09-24 14:49 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2 siblings, 1 reply; 33+ messages in thread
From: Nathan Bossart @ 2024-09-24 14:26 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@oss.nttdata.com>; +Cc: Michael Paquier <michael@paquier.xyz>; Christophe Pettus <xof@thebuild.com>; vaibhave postgres <postgresvaibhave@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Noah Misch <noah@leadboat.com>; pgsql-bugs@lists.postgresql.org; vsekar@microsoft.com
On Tue, Sep 24, 2024 at 11:20:43PM +0900, Fujii Masao wrote:
> On 2024/09/24 10:08, Michael Paquier wrote:
>> 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.
>
> Are you suggesting that any objects a user lacks sufficient privileges for
> should be silently excluded from vacuuming? This could make vacuumdb appear
> successful because no errors occur, but some tables the user intended to
> vacuum might be skipped without notice. That seems more problematic to me.
Yeah, this is what I mentioned upthread [0]. If the user doesn't specify
anything in --table or --schema, then it's probably fine to silently skip
objects for which they lack privileges. But if they do explicitly specify
a table or schema that they cannot vacuum, then IMHO it'd be better to
fail.
[0] https://postgr.es/m/Zu3iMzfiGBTbg3iy%40nathan
--
nathan
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 05:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-21 06:03 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 06:12 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-23 18:24 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-23 18:48 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
2024-09-24 01:08 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
2024-09-24 14:20 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-24 14:26 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
@ 2024-09-24 14:49 ` Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-24 14:51 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 33+ messages in thread
From: Fujii Masao @ 2024-09-24 14:49 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; Christophe Pettus <xof@thebuild.com>; vaibhave postgres <postgresvaibhave@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Noah Misch <noah@leadboat.com>; pgsql-bugs@lists.postgresql.org; vsekar@microsoft.com
On 2024/09/24 23:26, Nathan Bossart wrote:
> Yeah, this is what I mentioned upthread [0]. If the user doesn't specify
> anything in --table or --schema, then it's probably fine to silently skip
> objects for which they lack privileges. But if they do explicitly specify
> a table or schema that they cannot vacuum, then IMHO it'd be better to
> fail.
This could be debatable. To be honest, if I run something like vacuumdb mydb,
*I* expect all eligible tables in that database to be vacuumed. If I forget to
grant the necessary privileges to the role, I’d prefer to see errors from
vacuumdb so I can fix the permissions.
If we decide to skip tables without enough privilege, I’d prefer adding
an option like --skip-unprivileged-tables rather than changing the default behavior.
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 05:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-21 06:03 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 06:12 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-23 18:24 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-23 18:48 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
2024-09-24 01:08 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
2024-09-24 14:20 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-24 14:26 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-24 14:49 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
@ 2024-09-24 14:51 ` Nathan Bossart <nathandbossart@gmail.com>
2024-09-24 23:29 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 33+ messages in thread
From: Nathan Bossart @ 2024-09-24 14:51 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@oss.nttdata.com>; +Cc: Michael Paquier <michael@paquier.xyz>; Christophe Pettus <xof@thebuild.com>; vaibhave postgres <postgresvaibhave@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Noah Misch <noah@leadboat.com>; pgsql-bugs@lists.postgresql.org; vsekar@microsoft.com
On Tue, Sep 24, 2024 at 11:49:12PM +0900, Fujii Masao wrote:
> On 2024/09/24 23:26, Nathan Bossart wrote:
>> Yeah, this is what I mentioned upthread [0]. If the user doesn't specify
>> anything in --table or --schema, then it's probably fine to silently skip
>> objects for which they lack privileges. But if they do explicitly specify
>> a table or schema that they cannot vacuum, then IMHO it'd be better to
>> fail.
>
> This could be debatable. To be honest, if I run something like vacuumdb mydb,
> *I* expect all eligible tables in that database to be vacuumed. If I forget to
> grant the necessary privileges to the role, I´d prefer to see errors from
> vacuumdb so I can fix the permissions.
>
> If we decide to skip tables without enough privilege, I´d prefer adding
> an option like --skip-unprivileged-tables rather than changing the default behavior.
I'm okay with that approach.
--
nathan
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 05:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-21 06:03 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 06:12 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-23 18:24 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-23 18:48 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
2024-09-24 01:08 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
2024-09-24 14:20 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-24 14:26 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-24 14:49 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-24 14:51 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
@ 2024-09-24 23:29 ` Michael Paquier <michael@paquier.xyz>
0 siblings, 0 replies; 33+ messages in thread
From: Michael Paquier @ 2024-09-24 23:29 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: Fujii Masao <masao.fujii@oss.nttdata.com>; Christophe Pettus <xof@thebuild.com>; vaibhave postgres <postgresvaibhave@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Noah Misch <noah@leadboat.com>; pgsql-bugs@lists.postgresql.org; vsekar@microsoft.com
On Tue, Sep 24, 2024 at 09:51:14AM -0500, Nathan Bossart wrote:
> On Tue, Sep 24, 2024 at 11:49:12PM +0900, Fujii Masao wrote:
>> On 2024/09/24 23:26, Nathan Bossart wrote:
>>> Yeah, this is what I mentioned upthread [0]. If the user doesn't specify
>>> anything in --table or --schema, then it's probably fine to silently skip
>>> objects for which they lack privileges. But if they do explicitly specify
>>> a table or schema that they cannot vacuum, then IMHO it'd be better to
>>> fail.
>>
>> This could be debatable. To be honest, if I run something like vacuumdb mydb,
>> *I* expect all eligible tables in that database to be vacuumed. If I forget to
>> grant the necessary privileges to the role, I´d prefer to see errors from
>> vacuumdb so I can fix the permissions.
>>
>> If we decide to skip tables without enough privilege, I´d prefer adding
>> an option like --skip-unprivileged-tables rather than changing the default behavior.
>
> I'm okay with that approach.
Okay. I'd be on board with a new option to control the filtering.
The lack of complaints regarding vacuumdb failing with a non-superuser
makes it rather OK to still fail by default.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 05:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-21 06:03 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 06:12 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-23 18:24 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-23 18:48 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
2024-09-24 01:08 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
2024-09-24 14:20 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
@ 2024-09-24 23:10 ` Michael Paquier <michael@paquier.xyz>
2024-10-07 16:16 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2 siblings, 1 reply; 33+ messages in thread
From: Michael Paquier @ 2024-09-24 23:10 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@oss.nttdata.com>; +Cc: Christophe Pettus <xof@thebuild.com>; Nathan Bossart <nathandbossart@gmail.com>; vaibhave postgres <postgresvaibhave@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Noah Misch <noah@leadboat.com>; pgsql-bugs@lists.postgresql.org; vsekar@microsoft.com
On Tue, Sep 24, 2024 at 11:20:43PM +0900, Fujii Masao wrote:
> On 2024/09/24 10:08, Michael Paquier wrote:
>> 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.
>
> LGTM.
Thanks. As I am kind of behind this one, I'll go fix it first. Let's
sort out the permission bits after that one is sorted out. REL_17_0
is out, so this can happen across all branches.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 05:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-21 06:03 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 06:12 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-23 18:24 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-23 18:48 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
2024-09-24 01:08 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
2024-09-24 14:20 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-24 23:10 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
@ 2024-10-07 16:16 ` Nathan Bossart <nathandbossart@gmail.com>
2024-10-07 19:40 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
0 siblings, 1 reply; 33+ messages in thread
From: Nathan Bossart @ 2024-10-07 16:16 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Fujii Masao <masao.fujii@oss.nttdata.com>; Christophe Pettus <xof@thebuild.com>; vaibhave postgres <postgresvaibhave@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Noah Misch <noah@leadboat.com>; pgsql-bugs@lists.postgresql.org; vsekar@microsoft.com
Regarding commit 1ab67c9...
On Wed, Sep 25, 2024 at 08:10:16AM +0900, Michael Paquier wrote:
> Thanks. As I am kind of behind this one, I'll go fix it first. Let's
> sort out the permission bits after that one is sorted out. REL_17_0
> is out, so this can happen across all branches.
For consistency with the surrounding code, I think we should schema-qualify
the operator and add a newline after "WHERE relpersistence != 't'". If
folks agree, I can handle committing the attached patch.
--
nathan
Attachments:
fix_1ab67c9.patch (text/plain, 599B) download
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 249622068a..d07ab7d67e 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -684,7 +684,8 @@ vacuum_one_database(ConnParams *cparams,
* Exclude temporary tables, beginning the WHERE clause.
*/
appendPQExpBufferStr(&catalog_query,
- " WHERE c.relpersistence != " CppAsString2(RELPERSISTENCE_TEMP));
+ " WHERE c.relpersistence OPERATOR(pg_catalog.!=) "
+ CppAsString2(RELPERSISTENCE_TEMP) "\n");
/*
* Used to match the tables or schemas listed by the user, for the WHERE
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 05:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-21 06:03 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 06:12 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-23 18:24 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-23 18:48 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
2024-09-24 01:08 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
2024-09-24 14:20 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-24 23:10 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
2024-10-07 16:16 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
@ 2024-10-07 19:40 ` Noah Misch <noah@leadboat.com>
2024-10-07 19:54 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 33+ messages in thread
From: Noah Misch @ 2024-10-07 19:40 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; Fujii Masao <masao.fujii@oss.nttdata.com>; Christophe Pettus <xof@thebuild.com>; vaibhave postgres <postgresvaibhave@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org; vsekar@microsoft.com
On Mon, Oct 07, 2024 at 11:16:33AM -0500, Nathan Bossart wrote:
> Regarding commit 1ab67c9...
>
> On Wed, Sep 25, 2024 at 08:10:16AM +0900, Michael Paquier wrote:
> > Thanks. As I am kind of behind this one, I'll go fix it first. Let's
> > sort out the permission bits after that one is sorted out. REL_17_0
> > is out, so this can happen across all branches.
>
> For consistency with the surrounding code, I think we should schema-qualify
> the operator and add a newline after "WHERE relpersistence != 't'". If
> folks agree, I can handle committing the attached patch.
Not just code consistency. A code comment requires the schema qualification:
* Since we execute the constructed query with the default search_path
* (which could be unsafe), everything in this query MUST be fully
* qualified.
> --- a/src/bin/scripts/vacuumdb.c
> +++ b/src/bin/scripts/vacuumdb.c
> @@ -684,7 +684,8 @@ vacuum_one_database(ConnParams *cparams,
> * Exclude temporary tables, beginning the WHERE clause.
> */
> appendPQExpBufferStr(&catalog_query,
> - " WHERE c.relpersistence != " CppAsString2(RELPERSISTENCE_TEMP));
> + " WHERE c.relpersistence OPERATOR(pg_catalog.!=) "
> + CppAsString2(RELPERSISTENCE_TEMP) "\n");
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 05:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-21 06:03 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 06:12 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-23 18:24 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-23 18:48 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
2024-09-24 01:08 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
2024-09-24 14:20 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-24 23:10 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
2024-10-07 16:16 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-10-07 19:40 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
@ 2024-10-07 19:54 ` Nathan Bossart <nathandbossart@gmail.com>
2024-10-07 21:51 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 33+ messages in thread
From: Nathan Bossart @ 2024-10-07 19:54 UTC (permalink / raw)
To: Noah Misch <noah@leadboat.com>; +Cc: Michael Paquier <michael@paquier.xyz>; Fujii Masao <masao.fujii@oss.nttdata.com>; Christophe Pettus <xof@thebuild.com>; vaibhave postgres <postgresvaibhave@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org; vsekar@microsoft.com
On Mon, Oct 07, 2024 at 12:40:54PM -0700, Noah Misch wrote:
> On Mon, Oct 07, 2024 at 11:16:33AM -0500, Nathan Bossart wrote:
>> For consistency with the surrounding code, I think we should schema-qualify
>> the operator and add a newline after "WHERE relpersistence != 't'". If
>> folks agree, I can handle committing the attached patch.
>
> Not just code consistency. A code comment requires the schema qualification:
>
> * Since we execute the constructed query with the default search_path
> * (which could be unsafe), everything in this query MUST be fully
> * qualified.
D'oh. I'm listed as the author of the commit that added that comment and
should've remembered it. I'll just go apply the patch now, then.
--
nathan
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 05:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-21 06:03 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 06:12 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-23 18:24 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-23 18:48 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
2024-09-24 01:08 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
2024-09-24 14:20 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-24 23:10 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
2024-10-07 16:16 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-10-07 19:40 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-10-07 19:54 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
@ 2024-10-07 21:51 ` Nathan Bossart <nathandbossart@gmail.com>
2024-10-07 23:22 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 33+ messages in thread
From: Nathan Bossart @ 2024-10-07 21:51 UTC (permalink / raw)
To: Noah Misch <noah@leadboat.com>; +Cc: Michael Paquier <michael@paquier.xyz>; Fujii Masao <masao.fujii@oss.nttdata.com>; Christophe Pettus <xof@thebuild.com>; vaibhave postgres <postgresvaibhave@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org; vsekar@microsoft.com
On Mon, Oct 07, 2024 at 02:54:18PM -0500, Nathan Bossart wrote:
> I'll just go apply the patch now, then.
Committed.
--
nathan
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 05:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-21 06:03 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 06:12 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-23 18:24 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-23 18:48 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
2024-09-24 01:08 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
2024-09-24 14:20 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-24 23:10 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
2024-10-07 16:16 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-10-07 19:40 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-10-07 19:54 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-10-07 21:51 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
@ 2024-10-07 23:22 ` Michael Paquier <michael@paquier.xyz>
0 siblings, 0 replies; 33+ messages in thread
From: Michael Paquier @ 2024-10-07 23:22 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: Noah Misch <noah@leadboat.com>; Fujii Masao <masao.fujii@oss.nttdata.com>; Christophe Pettus <xof@thebuild.com>; vaibhave postgres <postgresvaibhave@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org; vsekar@microsoft.com
On Mon, Oct 07, 2024 at 04:51:14PM -0500, Nathan Bossart wrote:
> Committed.
Oops. Thanks!
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 05:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-21 06:03 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 06:12 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-23 18:24 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-23 18:48 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
2024-09-24 01:08 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
2024-09-24 14:20 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
@ 2024-09-24 23:20 ` Michael Paquier <michael@paquier.xyz>
2024-09-26 14:19 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2 siblings, 1 reply; 33+ messages in thread
From: Michael Paquier @ 2024-09-24 23:20 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@oss.nttdata.com>; +Cc: Christophe Pettus <xof@thebuild.com>; Nathan Bossart <nathandbossart@gmail.com>; vaibhave postgres <postgresvaibhave@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Noah Misch <noah@leadboat.com>; pgsql-bugs@lists.postgresql.org; vsekar@microsoft.com
On Tue, Sep 24, 2024 at 11:20:43PM +0900, Fujii Masao wrote:
> On 2024/09/24 10:08, Michael Paquier wrote:
>> 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.
>
> Are you suggesting that any objects a user lacks sufficient privileges for
> should be silently excluded from vacuuming? This could make vacuumdb appear
> successful because no errors occur, but some tables the user intended to
> vacuum might be skipped without notice. That seems more problematic to me.
>
> Sorry if I misunderstood your point.
I don't really have a strong opinion in favor of skipping the objects
or fail hard, because both have arguments. My point was just that, if
we were to skip the objects, then the query needs a bit of rework and
a SELECT with some extra filters in the CTE felt kind of right. If
the consensus is that errors are better, my point about rewriting the
query is most probably moot.
> Is ownership really necessary in these cases? A similar issue can easily
> happen with reindexdb as follows, so I think that should be fixed as well.
>
> =# CREATE ROLE admin WITH LOGIN;
> =# GRANT pg_maintain TO admin;
> =# CREATE TEMP TABLE tt (i int primary key);
> =# \! bin/reindexdb -U admin -j 2 postgres
> reindexdb: error: query failed: ERROR: permission denied for schema pg_temp_0
> LINE 4: AND c.oid OPERATOR(pg_catalog.=) 'pg_temp_0.tt'::pg_catalo...
> ^
> reindexdb: detail: Query was: SELECT c.relname, ns.nspname
> FROM pg_catalog.pg_class c, pg_catalog.pg_namespace ns
> WHERE c.relnamespace OPERATOR(pg_catalog.=) ns.oid
> AND c.oid OPERATOR(pg_catalog.=) 'pg_temp_0.tt'::pg_catalog.regclass;
>
> We should probably add a condition like "relpersistence != CppAsString2(RELPERSISTENCE_TEMP)"
> to the queries in get_parallel_object_list().
Missed your point. An extra filter based on relpersistence can indeed
make sense for this path.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 05:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-21 06:03 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 06:12 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-23 18:24 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-23 18:48 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
2024-09-24 01:08 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
2024-09-24 14:20 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-24 23:20 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
@ 2024-09-26 14:19 ` Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-27 00:48 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 33+ messages in thread
From: Fujii Masao @ 2024-09-26 14:19 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Christophe Pettus <xof@thebuild.com>; Nathan Bossart <nathandbossart@gmail.com>; vaibhave postgres <postgresvaibhave@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Noah Misch <noah@leadboat.com>; pgsql-bugs@lists.postgresql.org; vsekar@microsoft.com
On 2024/09/25 8:20, Michael Paquier wrote:
>> We should probably add a condition like "relpersistence != CppAsString2(RELPERSISTENCE_TEMP)"
>> to the queries in get_parallel_object_list().
>
> Missed your point. An extra filter based on relpersistence can indeed
> make sense for this path.
Patch attached.
Should reindexdb skip temporary tables or indexes even when specified explicitly
with the -t or -i options? Currently, the patch doesn't change this behavior;
reindexdb will still not skip them if specified.
If we agree to back-patch, it should be applied to v13, as parallel mode was
introduced in that version.
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
Attachments:
v1-0001-reindexdb-Skip-reindexing-temporary-tables-and-in.patch (text/plain, 1.9K) download
From b2e857eeaa9161aa4308df5158b8f11935cca977 Mon Sep 17 00:00:00 2001
From: Fujii Masao <fujii@postgresql.org>
Date: Thu, 26 Sep 2024 21:06:49 +0900
Subject: [PATCH v1] reindexdb: Skip reindexing temporary tables and indexes.
Reindexing temp tables or indexes of other sessions is not allowed.
However, reindexdb in parallel mode previously listed them as
the objects to process, leading to failures.
This commit ensures reindexdb in parallel mode skips temporary tables
and indexes by adding a condition based on the relpersistence column
in pg_class to the object listing queries, preventing these issues.
Note that this commit does not affect reindexdb when temporary tables
or indexes are explicitly specified using the -t or -j options;
reindexdb still does not skip them and can cause an error.
Back-patch to v13 where parallel mode was introduced in reindexdb.
Discussion: https://postgr.es/m/5f37ee56-14fb-44fe-9150-9eb97e10538b@oss.nttdata.com
---
src/bin/scripts/reindexdb.c | 4 ++++
1 file changed, 4 insertions(+)
diff --git a/src/bin/scripts/reindexdb.c b/src/bin/scripts/reindexdb.c
index 904c196fbb..af0738d933 100644
--- a/src/bin/scripts/reindexdb.c
+++ b/src/bin/scripts/reindexdb.c
@@ -649,6 +649,8 @@ get_parallel_object_list(PGconn *conn, ReindexType type,
" AND c.relkind IN ("
CppAsString2(RELKIND_RELATION) ", "
CppAsString2(RELKIND_MATVIEW) ")\n"
+ " AND c.relpersistence != "
+ CppAsString2(RELPERSISTENCE_TEMP) "\n"
" ORDER BY c.relpages DESC;");
break;
@@ -671,6 +673,8 @@ get_parallel_object_list(PGconn *conn, ReindexType type,
" WHERE c.relkind IN ("
CppAsString2(RELKIND_RELATION) ", "
CppAsString2(RELKIND_MATVIEW) ")\n"
+ " AND c.relpersistence != "
+ CppAsString2(RELPERSISTENCE_TEMP) "\n"
" AND ns.nspname IN (");
for (cell = user_list->head; cell; cell = cell->next)
--
2.45.2
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 05:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-21 06:03 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 06:12 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-23 18:24 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-23 18:48 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
2024-09-24 01:08 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
2024-09-24 14:20 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-24 23:20 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
2024-09-26 14:19 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
@ 2024-09-27 00:48 ` Michael Paquier <michael@paquier.xyz>
2024-09-30 17:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
0 siblings, 1 reply; 33+ messages in thread
From: Michael Paquier @ 2024-09-27 00:48 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@oss.nttdata.com>; +Cc: Christophe Pettus <xof@thebuild.com>; Nathan Bossart <nathandbossart@gmail.com>; vaibhave postgres <postgresvaibhave@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Noah Misch <noah@leadboat.com>; pgsql-bugs@lists.postgresql.org; vsekar@microsoft.com
On Thu, Sep 26, 2024 at 11:19:33PM +0900, Fujii Masao wrote:
> Should reindexdb skip temporary tables or indexes even when specified explicitly
> with the -t or -i options? Currently, the patch doesn't change this behavior;
> reindexdb will still not skip them if specified.
>
> If we agree to back-patch, it should be applied to v13, as parallel mode was
> introduced in that version.
Looks sensible to me to apply that to the database and schema queries.
Thanks for the patch.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 05:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-21 06:03 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 06:12 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-23 18:24 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-23 18:48 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
2024-09-24 01:08 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
2024-09-24 14:20 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-24 23:20 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
2024-09-26 14:19 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-27 00:48 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
@ 2024-09-30 17:07 ` Fujii Masao <masao.fujii@oss.nttdata.com>
0 siblings, 0 replies; 33+ messages in thread
From: Fujii Masao @ 2024-09-30 17:07 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Christophe Pettus <xof@thebuild.com>; Nathan Bossart <nathandbossart@gmail.com>; vaibhave postgres <postgresvaibhave@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Noah Misch <noah@leadboat.com>; pgsql-bugs@lists.postgresql.org; vsekar@microsoft.com
On 2024/09/27 9:48, Michael Paquier wrote:
> On Thu, Sep 26, 2024 at 11:19:33PM +0900, Fujii Masao wrote:
>> Should reindexdb skip temporary tables or indexes even when specified explicitly
>> with the -t or -i options? Currently, the patch doesn't change this behavior;
>> reindexdb will still not skip them if specified.
>>
>> If we agree to back-patch, it should be applied to v13, as parallel mode was
>> introduced in that version.
>
> Looks sensible to me to apply that to the database and schema queries.
> Thanks for the patch.
Pushed. Thanks for the review!
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 05:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-21 06:03 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 06:12 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-23 18:24 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-23 18:48 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
@ 2024-09-24 01:09 ` vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-24 01:13 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
1 sibling, 1 reply; 33+ messages in thread
From: vaibhave postgres @ 2024-09-24 01:09 UTC (permalink / raw)
To: Christophe Pettus <xof@thebuild.com>; +Cc: Nathan Bossart <nathandbossart@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Fujii Masao <masao.fujii@oss.nttdata.com>; Noah Misch <noah@leadboat.com>; pgsql-bugs@lists.postgresql.org; vsekar@microsoft.com
Hi,
Yes I plan on continuing with working this.
Thanks.
On Tue, 24 Sept 2024, 00:18 Christophe Pettus, <xof@thebuild.com> wrote:
>
>
> > On Sep 23, 2024, at 11:24, Nathan Bossart <nathandbossart@gmail.com>
> wrote:
> > Are you planning to submit an updated patch? I don't think there's a
> > tremendous amount of urgency to fix this since it's been broken for so
> > long, but it'd be good to know whether someone is planning to pick it up.
>
> I'm happy to pick it up iff the current patch submitter doesn't want to
> continue with it.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 05:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-21 06:03 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 06:12 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-23 18:24 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-23 18:48 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
2024-09-24 01:09 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
@ 2024-09-24 01:13 ` Christophe Pettus <xof@thebuild.com>
2024-09-24 01:35 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-24 14:30 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 2 replies; 33+ messages in thread
From: Christophe Pettus @ 2024-09-24 01:13 UTC (permalink / raw)
To: vaibhave postgres <postgresvaibhave@gmail.com>; +Cc: Nathan Bossart <nathandbossart@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Fujii Masao <masao.fujii@oss.nttdata.com>; Noah Misch <noah@leadboat.com>; PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>; vsekar@microsoft.com
> On Sep 23, 2024, at 18:09, vaibhave postgres <postgresvaibhave@gmail.com> wrote:
> Yes I plan on continuing with working this.
Great!
One related but not identical thing that has come up with vacuumdb is that it terminates if it's not able to connect to any database that it finds in the initial query. This can happen if pg_hba.conf denies the user that is running vacuumdb access to a database that comes up during --all. Some hosting providers (in particular, Google) create restricted databases in the cluster that a customer role can't get access to. This pretty much defeats --analyze-in-stages. My suggested fix was to terminate with an error if the initial connection fails, but continue with a warning if further connections fail.
If it seems reasonable, I'm happy to do it in a separate patch.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 05:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-21 06:03 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 06:12 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-23 18:24 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-23 18:48 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
2024-09-24 01:09 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-24 01:13 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
@ 2024-09-24 01:35 ` vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-24 01:56 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Michael Paquier <michael@paquier.xyz>
1 sibling, 1 reply; 33+ messages in thread
From: vaibhave postgres @ 2024-09-24 01:35 UTC (permalink / raw)
To: Christophe Pettus <xof@thebuild.com>; +Cc: Nathan Bossart <nathandbossart@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Fujii Masao <masao.fujii@oss.nttdata.com>; Noah Misch <noah@leadboat.com>; PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>; vsekar@microsoft.com
Looks like Michael has already sent the updated patch from the discussions
in this thread so far. Let me know if anything else needs to be done.
Thanks.
On Tue, Sep 24, 2024 at 6:43 AM Christophe Pettus <xof@thebuild.com> wrote:
>
>
> > On Sep 23, 2024, at 18:09, vaibhave postgres <postgresvaibhave@gmail.com>
> wrote:
> > Yes I plan on continuing with working this.
>
> Great!
>
> One related but not identical thing that has come up with vacuumdb is that
> it terminates if it's not able to connect to any database that it finds in
> the initial query. This can happen if pg_hba.conf denies the user that is
> running vacuumdb access to a database that comes up during --all. Some
> hosting providers (in particular, Google) create restricted databases in
> the cluster that a customer role can't get access to. This pretty much
> defeats --analyze-in-stages. My suggested fix was to terminate with an
> error if the initial connection fails, but continue with a warning if
> further connections fail.
>
> If it seems reasonable, I'm happy to do it in a separate patch.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 05:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-21 06:03 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 06:12 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-23 18:24 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-23 18:48 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
2024-09-24 01:09 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-24 01:13 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
2024-09-24 01:35 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
@ 2024-09-24 01:56 ` Michael Paquier <michael@paquier.xyz>
0 siblings, 0 replies; 33+ messages in thread
From: Michael Paquier @ 2024-09-24 01:56 UTC (permalink / raw)
To: vaibhave postgres <postgresvaibhave@gmail.com>; +Cc: Christophe Pettus <xof@thebuild.com>; Nathan Bossart <nathandbossart@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Fujii Masao <masao.fujii@oss.nttdata.com>; Noah Misch <noah@leadboat.com>; PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>; vsekar@microsoft.com
On Tue, Sep 24, 2024 at 07:05:41AM +0530, vaibhave postgres wrote:
> Looks like Michael has already sent the updated patch from the discussions
> in this thread so far. Let me know if anything else needs to be done.
Yes, the timing of your reply was interesting ;)
The temporary persistence issue is one side of the coin, and that's
the only part I have sent now as there is also a performance argument
(while being entirely useless to do). The permission parts with USAGE
and MAINTAIN need a bit more thoughts.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 05:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-21 06:03 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 06:12 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-23 18:24 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-23 18:48 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
2024-09-24 01:09 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-24 01:13 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
@ 2024-09-24 14:30 ` Nathan Bossart <nathandbossart@gmail.com>
2024-09-24 15:22 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
1 sibling, 1 reply; 33+ messages in thread
From: Nathan Bossart @ 2024-09-24 14:30 UTC (permalink / raw)
To: Christophe Pettus <xof@thebuild.com>; +Cc: vaibhave postgres <postgresvaibhave@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Fujii Masao <masao.fujii@oss.nttdata.com>; Noah Misch <noah@leadboat.com>; PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>; vsekar@microsoft.com
On Mon, Sep 23, 2024 at 06:13:23PM -0700, Christophe Pettus wrote:
> One related but not identical thing that has come up with vacuumdb is
> that it terminates if it's not able to connect to any database that it
> finds in the initial query. This can happen if pg_hba.conf denies the
> user that is running vacuumdb access to a database that comes up during
> --all. Some hosting providers (in particular, Google) create restricted
> databases in the cluster that a customer role can't get access to. This
> pretty much defeats --analyze-in-stages. My suggested fix was to
> terminate with an error if the initial connection fails, but continue
> with a warning if further connections fail.
I think it'd be fine to continue with a warning when --all is used, but if
you specify a --dbname that you cannot connect to, then I think it should
fail.
--
nathan
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 05:59 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-21 06:03 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 06:12 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-23 18:24 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
2024-09-23 18:48 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
2024-09-24 01:09 ` Re: vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-24 01:13 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Christophe Pettus <xof@thebuild.com>
2024-09-24 14:30 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Nathan Bossart <nathandbossart@gmail.com>
@ 2024-09-24 15:22 ` Christophe Pettus <xof@thebuild.com>
0 siblings, 0 replies; 33+ messages in thread
From: Christophe Pettus @ 2024-09-24 15:22 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: vaibhave postgres <postgresvaibhave@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Fujii Masao <masao.fujii@oss.nttdata.com>; Noah Misch <noah@leadboat.com>; PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>; vsekar@microsoft.com
> On Sep 24, 2024, at 07:30, Nathan Bossart <nathandbossart@gmail.com> wrote:
> I think it'd be fine to continue with a warning when --all is used, but if
> you specify a --dbname that you cannot connect to, then I think it should
> fail.
Yes, that's essentially my proposal. If --all is not specified, or if it cannot make the initial connection to determine which databases are in the instance, it stops with a fatal error just as it does now.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: vacuumdb: permission denied for schema "pg_temp_7"
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Re: vacuumdb: permission denied for schema "pg_temp_7" Noah Misch <noah@leadboat.com>
@ 2024-09-21 05:56 ` Fujii Masao <masao.fujii@oss.nttdata.com>
1 sibling, 0 replies; 33+ messages in thread
From: Fujii Masao @ 2024-09-21 05:56 UTC (permalink / raw)
To: Noah Misch <noah@leadboat.com>; vaibhave postgres <postgresvaibhave@gmail.com>; nathandbossart@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org; vsekar@microsoft.com
On 2024/09/21 4:07, Noah Misch wrote:
> Non-superuser vacuumdb
> must be rare indeed for this to go unnoticed long enough to leave all
> supported branches affected.
Yes.
And more users might notice this in v17 or later, since v17 supports
the maintain privilege. Some users may want to use a role with
the maintain privilege to run vacuumdb. If they forget to grant USAGE
privilege on the temp table's schema to that role, they'll encounter
the same issue.
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
^ permalink raw reply [nested|flat] 33+ messages in thread
end of thread, other threads:[~2024-10-07 23:22 UTC | newest]
Thread overview: 33+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-06 11:49 vacuumdb: permission denied for schema "pg_temp_7" vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-20 19:07 ` Noah Misch <noah@leadboat.com>
2024-09-20 20:59 ` Nathan Bossart <nathandbossart@gmail.com>
2024-09-20 21:50 ` Noah Misch <noah@leadboat.com>
2024-09-20 23:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 05:59 ` Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-21 06:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-09-21 06:12 ` vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-23 18:24 ` Nathan Bossart <nathandbossart@gmail.com>
2024-09-23 18:48 ` Christophe Pettus <xof@thebuild.com>
2024-09-24 01:08 ` Michael Paquier <michael@paquier.xyz>
2024-09-24 14:20 ` Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-24 14:26 ` Nathan Bossart <nathandbossart@gmail.com>
2024-09-24 14:49 ` Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-24 14:51 ` Nathan Bossart <nathandbossart@gmail.com>
2024-09-24 23:29 ` Michael Paquier <michael@paquier.xyz>
2024-09-24 23:10 ` Michael Paquier <michael@paquier.xyz>
2024-10-07 16:16 ` Nathan Bossart <nathandbossart@gmail.com>
2024-10-07 19:40 ` Noah Misch <noah@leadboat.com>
2024-10-07 19:54 ` Nathan Bossart <nathandbossart@gmail.com>
2024-10-07 21:51 ` Nathan Bossart <nathandbossart@gmail.com>
2024-10-07 23:22 ` Michael Paquier <michael@paquier.xyz>
2024-09-24 23:20 ` Michael Paquier <michael@paquier.xyz>
2024-09-26 14:19 ` Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-27 00:48 ` Michael Paquier <michael@paquier.xyz>
2024-09-30 17:07 ` Fujii Masao <masao.fujii@oss.nttdata.com>
2024-09-24 01:09 ` vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-24 01:13 ` Christophe Pettus <xof@thebuild.com>
2024-09-24 01:35 ` vaibhave postgres <postgresvaibhave@gmail.com>
2024-09-24 01:56 ` Michael Paquier <michael@paquier.xyz>
2024-09-24 14:30 ` Nathan Bossart <nathandbossart@gmail.com>
2024-09-24 15:22 ` Christophe Pettus <xof@thebuild.com>
2024-09-21 05:56 ` Fujii Masao <masao.fujii@oss.nttdata.com>
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