Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 4C8C39FB3E4 for ; Mon, 29 Jan 2007 17:03:37 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 68119-05 for ; Mon, 29 Jan 2007 17:03:25 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.225]) by postgresql.org (Postfix) with ESMTP id 866B29FB3D0 for ; Mon, 29 Jan 2007 17:03:28 -0400 (AST) Received: by wr-out-0506.google.com with SMTP id i21so1038377wra for ; Mon, 29 Jan 2007 13:03:25 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=mCNbB0S80TUlSsjckgydKPTCaWGEXIsFAiR3i6ojAZ3Gk3FxawJe0v4wD2skdz6F+hi/bS45dv5cuRW+9Wnfk12/M6Y5hTgH8XSPHG3TidYWuHHuGp6Tc3JJSct3Yhgs8TcE2n9EnUlnKMXm6KbQBDTQIjK7wim5+B4YRQ4JiBs= Received: by 10.90.84.17 with SMTP id h17mr7360636agb.1170104605165; Mon, 29 Jan 2007 13:03:25 -0800 (PST) Received: by 10.90.67.1 with HTTP; Mon, 29 Jan 2007 13:03:25 -0800 (PST) Message-ID: <4544e0330701291303u2ed1d2bclff520f6ecc99e832@mail.gmail.com> Date: Mon, 29 Jan 2007 15:03:25 -0600 From: "Peter Koczan" To: pgsql-admin Subject: pg_dumpall problems MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_70293_6841523.1170104605009" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/266 X-Sequence-Number: 24422 ------=_Part_70293_6841523.1170104605009 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi all, Thanks for your help thus far. I almost have 8.2 ready (hooray no more ancient 7.4). I do have one more problem which is likely the last thing before pushing out live support. pg_dumpall isn't working. Right now, we run pg_dumpall as a non-superuser, let's call it backup, whom we give read access to the appropriate system tables (pg_shadow in 7.4, pg_authid in 8.2), I get this error... pg_dumpall: query failed: ERROR: permission denied for relation pg_authid pg_dumpall: query was: SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcatupdate, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment FROM pg_authid ORDER BY 1 The weird thing is, whenever I run this exact same command from psql as the backup user, it works. template1=> select current_role; current_user -------------- backup (1 row) template1=> \z pg_catalog.pg_authid; Access privileges for database "template1" Schema | Name | Type | Access privileges ------------+-----------+-------+---------------------------------------------- pg_catalog | pg_authid | table | {postgres=arwdxt/postgres,backup=r/postgres} template1=> SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcatupdate, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment FROM pg_authid ORDER BY 1; template1=> SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcatupdate, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment FROM pg_authid ORDER BY 1; (There's data...glorious, glorious data). The easy solution is to make backup a database superuser, but that's a change I'd not like to make right now since I don't understand all the security implications. What's the deal with this error, and is there any nicer solution? Thanks much in advance. Peter ------=_Part_70293_6841523.1170104605009 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi all,

Thanks for your help thus far. I almost have 8.2 ready (hooray no more ancient 7.4). I do have one more problem which is likely the last thing before pushing out live support. pg_dumpall isn't working.

Right now, we run pg_dumpall as a non-superuser, let's call it backup, whom we give read access to the appropriate system tables (pg_shadow in 7.4, pg_authid in 8.2), I get this error...
pg_dumpall: query failed: ERROR:  permission denied for relation pg_authid
pg_dumpall: query was: SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcatupdate, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment FROM pg_authid ORDER BY 1

The weird thing is, whenever I run this exact same command from psql as the backup user, it works.

template1=> select current_role;
 current_user
--------------
 backup
(1 row)
template1=> \z pg_catalog.pg_authid;
                  Access privileges for database "template1"
   Schema   |   Name    | Type  |              Access privileges
------------+-----------+-------+----------------------------------------------
 pg_catalog | pg_authid | table | {postgres=arwdxt/postgres,backup=r/postgres}

template1=> SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcatupdate, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment FROM pg_authid ORDER BY 1;
template1=> SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcatupdate, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment FROM pg_authid ORDER BY 1;
(There's data...glorious, glorious data).

The easy solution is to make backup a database superuser, but that's a change I'd not like to make right now since I don't understand all the security implications. What's the deal with this error, and is there any nicer solution? Thanks much in advance.

Peter

------=_Part_70293_6841523.1170104605009--