Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 303B19FA43C for ; Wed, 27 Dec 2006 21:26:58 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 71958-01 for ; Wed, 27 Dec 2006 21:26:41 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id DD4049FA2EC for ; Wed, 27 Dec 2006 21:26:45 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id kBS1QglO028234; Wed, 27 Dec 2006 20:26:42 -0500 (EST) To: L Bayuk cc: pgsql-admin@postgresql.org Subject: Re: 8.2.0 upgrade issue: loss of CONNECT rights In-reply-to: <20061228004546.GB364@mail.mindspring.com> References: <20061228004546.GB364@mail.mindspring.com> Comments: In-reply-to L Bayuk message dated "Wed, 27 Dec 2006 19:45:46 -0500" Date: Wed, 27 Dec 2006 20:26:42 -0500 Message-ID: <28233.1167269202@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/344 X-Sequence-Number: 24136 L Bayuk writes: > Another thing to watch out for when upgrading to 8.2.0: > I had some 8.1.x databases with restricted rights, like REVOKE CREATE ON > DATABASE. After pg_dumpall and reload into 8.2.0, I lost CONNECT rights on > those databases. So only the superuser account can connect. The reason > seems to be that my dump file had REVOKE ALL ON DATABASE, then added the > specific GRANT rights. At 8.2.0, REVOKE ALL includes revoking CONNECT > rights even though they were always granted at 8.1.5. Hmmm ... I think we could hack 8.2's pg_dumpall to compensate for that, ie throw in a GRANT CONNECT ... TO PUBLIC when dumping from a pre-8.2 server. But of course this wouldn't do anything for databases dumped with older versions of pg_dumpall. regards, tom lane