Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 6ABC19FA492 for ; Wed, 27 Dec 2006 20:45:57 -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 66907-02 for ; Wed, 27 Dec 2006 20:45:47 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id A19B19FA4BB for ; Wed, 27 Dec 2006 20:45:51 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from elasmtp-kukur.atl.sa.earthlink.net (elasmtp-kukur.atl.sa.earthlink.net [209.86.89.65]) by svr4.postgresql.org (Postfix) with ESMTP id D6FDD5AF8BE for ; Thu, 28 Dec 2006 00:45:50 +0000 (GMT) Received: from [4.239.186.39] (helo=mindspring.com) by elasmtp-kukur.atl.sa.earthlink.net with asmtp (TLSv1:AES256-SHA:256) (Exim 4.34) id 1GzjP3-00054j-5y for pgsql-admin@postgresql.org; Wed, 27 Dec 2006 19:45:50 -0500 Received: (from lbayuk@localhost) by mindspring.com (8.13.7/8.13.7) id kBS0jkQj000382 for pgsql-admin@postgresql.org; Wed, 27 Dec 2006 19:45:46 -0500 Date: Wed, 27 Dec 2006 19:45:46 -0500 From: L Bayuk To: pgsql-admin@postgresql.org Subject: 8.2.0 upgrade issue: loss of CONNECT rights Message-ID: <20061228004546.GB364@mail.mindspring.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.4.2.2i X-ELNK-Trace: 7a0205cf9d42df0a5e89bb4777695beb69025943178dce0bf43481ca4a0697e8f94628fc7b8fd469350badd9bab72f9c350badd9bab72f9c350badd9bab72f9c X-Originating-IP: 4.239.186.39 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.136 tagged_above=0 required=5 tests=BAYES_50, FORGED_RCVD_HELO, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200612/343 X-Sequence-Number: 24135 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. For example, here are my database rights under 8.1.5: {postgres=CT/postgres,=T/postgres,lbayuk=C/postgres} So public has TEMP, I have CREATE (and TEMP, via public). My dump file had something like this: CREATE DATABASE test WITH ... REVOKE ALL ON DATABASE test FROM PUBLIC; REVOKE ALL ON DATABASE test FROM postgres; GRANT ALL ON DATABASE test TO postgres; GRANT TEMPORARY ON DATABASE test TO PUBLIC; GRANT CREATE ON DATABASE test TO lbayuk; After the upgrade to 8.2.0 the database rights are: {postgres=CTc/postgres,=T/postgres,lbayuk=C/postgres} So now me and PUBLIC cannot connect because we don't have the new 'c' right. I tried using both 8.1.5 pg_dumpall and 8.2.0 pg_dumpall to dump the 8.1.5 database, and both had the problem. To fix: GRANT CONNECT ON DATABASE ... TO PUBLIC;