Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iWNuo-0002TQ-Us for pgsql-bugs@arkaria.postgresql.org; Sun, 17 Nov 2019 17:00:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iWNum-0007Oe-HF for pgsql-bugs@arkaria.postgresql.org; Sun, 17 Nov 2019 17:00:44 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iWNum-0007OX-5X for pgsql-bugs@lists.postgresql.org; Sun, 17 Nov 2019 17:00:44 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1iWNuf-0008JH-2l for pgsql-bugs@lists.postgresql.org; Sun, 17 Nov 2019 17:00:42 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id xAHH0VNa023244; Sun, 17 Nov 2019 12:00:31 -0500 From: Tom Lane To: "=?gb18030?B?zqjSu6Hv?=" <270246512@qq.com> cc: "=?gb18030?B?VG9tYXMgVm9uZHJh?=" , "=?gb18030?B?cGdzcWwtYnVncw==?=" Subject: Re: =?gb18030?B?u9i4tKO6ICC72Li0o7ogILvYuLSjuiBCVUcgIzE2?= =?gb18030?B?MTAxOiB0YWJsZXMgaW4gdGhlIERCIGlzIG5vdCBh?= =?gb18030?B?dmFpbGFibGUgYWZ0ZXIgcGdfcmVzdG9yZQ==?= In-reply-to: References: <16101-5a569e5aac6aa84e@postgresql.org> <20191108111339.xlopjkbwfhxfvwt3@development> <20191113133029.kagiy6vjjeoguatp@development> <20191113151803.6ye47vh3vyp5ufah@development> Comments: In-reply-to "=?gb18030?B?zqjSu6Hv?=" <270246512@qq.com> message dated "Sun, 17 Nov 2019 22:40:49 +0800" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <23242.1574010031.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Sun, 17 Nov 2019 12:00:31 -0500 Message-ID: <23243.1574010031@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk "=3D?gb18030?B?zqjSu6Hv?=3D" <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_5865E10D689BCC05DFD0BC291ED8= 69BEAA05%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 pgb= ench_accounts cm pg_restore: [archiver (db)] could not execute query: ERROR: table "pgbenc= h_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