pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: 唯一★ <270246512@qq.com>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: 回复:  回复:  回复: BUG #16101: tables in the DB is not available after pg_restore
Date: Sun, 17 Nov 2019 12:00:31 -0500
Message-ID: <23243.1574010031@sss.pgh.pa.us> (raw)
In-Reply-To: <tencent_8F1706C3F7A62AE0C279A4169C37F36D3607@qq.com>
References: <16101-5a569e5aac6aa84e@postgresql.org>
	<20191108111339.xlopjkbwfhxfvwt3@development>
	<tencent_5865E10D689BCC05DFD0BC291ED869BEAA05@qq.com>
	<20191113133029.kagiy6vjjeoguatp@development>
	<tencent_9DD805D110A0A91F987ADEAB25EE2D69BB05@qq.com>
	<20191113151803.6ye47vh3vyp5ufah@development>
	<tencent_9BBC6D521745C3B6A62D60F2798F96A5B707@qq.com>
	<tencent_8F1706C3F7A62AE0C279A4169C37F36D3607@qq.com>

"=?gb18030?B?zqjSu6Hv?=" <270246512@qq.com> writes:
> Do you have update for this issue?

You've done nothing to convince anyone that this isn't local
misconfiguration or process error on your part.

In particular, I still like the theory I offered in

https://www.postgresql.org/message-id/5802.1573657223%40sss.pgh.pa.us

that the permissions on the public schema don't allow your
non-superuser role to access anything in that schema.

Looking closer at the "pg_restore -v" trace you posted in

https://www.postgresql.org/message-id/tencent_5865E10D689BCC05DFD0BC291ED869BEAA05%40qq.com

bolsters this theory, because I see

pg_restore: dropping COMMENT SCHEMA public
pg_restore: dropping SCHEMA public
pg_restore: creating SCHEMA "public"
pg_restore: creating COMMENT "SCHEMA public"

but there's never any later

pg_restore: creating ACL "SCHEMA public"

which there ought to be, and there is when I try to reproduce this.
That means the public schema is ending up with default permissions,
which grant no access to anyone but the owner.

Perhaps this happened because you did the dump or the restore
with -x (--no-privileges).  Or possibly that schema's privileges
were manually modified at some earlier point.

In any case, it's fairly hard to believe that you're giving us
a completely accurate statement of facts, because the restore
trace also includes errors like

pg_restore: dropping TABLE pgbench_accounts
pg_restore: [archiver (db)] Error from TOC entry 199; 1259 47945 TABLE pgbench_accounts cm
pg_restore: [archiver (db)] could not execute query: ERROR:  table "pgbench_accounts" does not exist
Command was: DROP TABLE public.pgbench_accounts;

It seems very unlikely that you could have gotten that if you
were restoring a dump you'd just created from the same database.
So there are additional moving parts here that you have not
mentioned.

			regards, tom lane





view thread (3+ messages)

Message-ID: <23243.1574010031@sss.pgh.pa.us>
Permalink:  ../23243.1574010031@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/23243.1574010031@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: tgl@sss.pgh.pa.us, 270246512@qq.com, tomas.vondra@2ndquadrant.com
  Subject: Re: 回复:  回复:  回复: BUG #16101: tables in the DB is not available after pg_restore
  In-Reply-To: <23243.1574010031@sss.pgh.pa.us>
  References: <16101-5a569e5aac6aa84e@postgresql.org> <20191108111339.xlopjkbwfhxfvwt3@development> <tencent_5865E10D689BCC05DFD0BC291ED869BEAA05@qq.com> <20191113133029.kagiy6vjjeoguatp@development> <tencent_9DD805D110A0A91F987ADEAB25EE2D69BB05@qq.com> <20191113151803.6ye47vh3vyp5ufah@development> <tencent_9BBC6D521745C3B6A62D60F2798F96A5B707@qq.com> <tencent_8F1706C3F7A62AE0C279A4169C37F36D3607@qq.com> <23243.1574010031@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox