Re: pg_dumpall problems

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "Peter Koczan" <pjkoczan(at)gmail(dot)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.