Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 347D69FB368 for ; Tue, 30 Jan 2007 19:34:17 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 01083-02 for ; Tue, 30 Jan 2007 19:34:09 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.226]) by postgresql.org (Postfix) with ESMTP id 713E79FB447 for ; Tue, 30 Jan 2007 19:34:08 -0400 (AST) Received: by wx-out-0506.google.com with SMTP id i28so26667wxd for ; Tue, 30 Jan 2007 15:34:07 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=CElhOdYqEzyeueD0ZztZnMCfOBY0Drspt1Sb+Vj8iWfW+ihAZ3cCEKNzvYF0rKVr441hlIIo0fktFH6OivGw92LiBWt1zjnXF9a7wjq0WCjBeqaY++wFrUZPPPSM2boczdLMT08/NC3ZhHKgZ0O4fQls3hc1lNY8OxQyE/QnA4Q= Received: by 10.90.90.16 with SMTP id n16mr127540agb.1170200047393; Tue, 30 Jan 2007 15:34:07 -0800 (PST) Received: by 10.90.67.1 with HTTP; Tue, 30 Jan 2007 15:34:07 -0800 (PST) Message-ID: <4544e0330701301534r5cb09722i5665f2f406dd29fc@mail.gmail.com> Date: Tue, 30 Jan 2007 17:34:07 -0600 From: "Peter Koczan" To: "Tom Lane" Subject: Re: pg_dumpall problems Cc: pgsql-admin In-Reply-To: <20835.1170110152@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_86758_28041809.1170200047361" References: <4544e0330701291303u2ed1d2bclff520f6ecc99e832@mail.gmail.com> <20835.1170110152@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/283 X-Sequence-Number: 24439 ------=_Part_86758_28041809.1170200047361 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline I should be a little more specific. The way we currently do backups is that this backup user dumps user/group data (i.e. the --globals-only flag) for pg_dumpall, and we pg_dump individual databases. We grant read access to this backup user for all non-system tables/views/etc. (and usage for non-system schemas) in user databases so it can read and dump the data. We also grant it read access on select system tables to make pg_dumpall work. We do this for two reasons. First, we like to follow the generally good security practice of minimum access, and second, (as a result of the first), we don't want to tickle a bug in our backup system that magically corrupts our data (which is possible if this user has more than read access). So, is there any remedy to my problem (see below) short of granting superuser access? Is this a bug (which I would then report on the appropriate channels)? As for Tom's suggestion, there's no way to specify the database in pg_dumpall, only the server, and the same bug occurs if I run as the user on the same server and cluster with the same major version. Peter On 1/29/07, Tom Lane wrote: > > "Peter Koczan" writes: > > Right now, we run pg_dumpall as a non-superuser, > > [ raised eyebrow... ] That's never been a recommended procedure. > > > The weird thing is, whenever I run this exact same command from psql as > the > > backup user, it works. > > Maybe you're not trying it in the same database pg_dumpall is? > > regards, tom lane > Original post for all those to see: ------------------------------------------------- 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. ------=_Part_86758_28041809.1170200047361 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline I should be a little more specific. The way we currently do backups is that this backup user dumps user/group data (i.e. the --globals-only flag) for pg_dumpall, and we pg_dump individual databases.

We grant read access to this backup user for all non-system tables/views/etc. (and usage for non-system schemas) in user databases so it can read and dump the data. We also grant it read access on select system tables to make pg_dumpall work. We do this for two reasons. First, we like to follow the generally good security practice of minimum access, and second, (as a result of the first), we don't want to tickle a bug in our backup system that magically corrupts our data (which is possible if this user has more than read access).

So, is there any remedy to my problem (see below) short of granting superuser access? Is this a bug (which I would then report on the appropriate channels)?

As for Tom's suggestion, there's no way to specify the database in pg_dumpall, only the server, and the same bug occurs if I run as the user on the same server and cluster with the same major version.

Peter

On 1/29/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Peter Koczan" <pjkoczan@gmail.com> writes:
> Right now, we run pg_dumpall as a non-superuser,

[ raised eyebrow... ]  That's never been a recommended procedure.

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

Maybe you're not trying it in the same database pg_dumpall is?

                        regards, tom lane

Original post for all those to see:
-------------------------------------------------
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.
------=_Part_86758_28041809.1170200047361--