Обсуждение: pg_dumpall problems

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

"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

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


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.

"Peter Koczan" <pjkoczan@gmail.com> writes:
> 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)?

It's not a bug.

> 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.

You still haven't responded to my query: did you try it in the same
database that pg_dumpall is connecting to?  My guess is that you have
munged the permissions on pg_shadow or pg_authid without understanding
that that will only take effect in the one database you do it in.
pg_dumpall is connecting to either "postgres" or "template1" depending
on version; what's the permissions situation in that database?

            regards, tom lane

I misunderstood your question. I apologize. Granting access on "postgres" worked like a charm. Thank you.

Peter