newbie authentication/automated backup (pg_dumpall) questions - Mailing list pgsql-general

Hi everyone.

Just got postgres 7.1.3 (debian unstable) going after an upgrade from
7.0.x.  I have things *mostly* working now, with a few questions
regarding authentication.

What I'd like to have is the following two situations dealt with:

 - unsupervised backups using something like pg_dumpall that can run
    from cron either as root or the postgres user (su -c "pg_dumpall...")
 - access to the database through web apps such as message boards or
    similar using the Pg module from a webserver

In 7.0 you could run pg_dumpall as the postgres user, so cron took care
of backups very nicely, and from the webserver running as a different
user (www-data) using Pg::connectdb(...) and passing the postgresql
user/pass (the shell username/password that is).  No one without
postgres shell account access could access the database which is fine by
me.  This all worked fine.

Now 7.1 is here and I'm lost :(  I've never done any real "user
management" using postgres other than setting a password in the shell
for the postgres user.

Currently my situation is this:

/etc/postgres/pg_hba.conf

local         all                                 crypt
local         all     127.0.0.1     255.0.0.0     ident sameuser

With this I can set up a cgi with the line:
Pg::connectdb("dbname=$database user=$dbuser password=$dbpass");

And properly connect via my webserver user (www-data) to postgres just
dandy.

However, what I can't do is automated backups :(  In fact, I can't seem
to run pg_dumpall at all!

-----------------
postgres@master:~$ pg_dumpall
--
-- pg_dumpall (7.1.3)
--
\connect template1
DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database
WHERE datname = 'template0');

Password:
psql: Password authentication failed for user 'postgres'

DELETE FROM pg_group;

Password: [password]
Password: [password]

--
-- Database ufies
--
\connect template1 postgres
CREATE DATABASE "ufies" WITH TEMPLATE = template0 ENCODING =
'SQL_ASCII';
\connect ufies postgres
Connection to database 'ufies' failed.
fe_sendauth: no password supplied

pg_dump failed on ufies, exiting
postgres@master:~$
-----------------

Note that above I only put in the password the second and third time, not
the first time (ufies is the name of the main db BTW).

It was suggested to me on IRC that passing -h 127.0.0.1 would solve my
problems, but I get:

-----------------
postgres@master:~$ pg_dumpall -h 127.0.0.1
--
-- pg_dumpall (7.1.3)  -h 127.0.0.1
--
\connect template1
DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database
WHERE datname = 'template0');

psql: Peer authentication failed for user 'postgres'

DELETE FROM pg_group;

psql: Peer authentication failed for user 'postgres'
psql: Peer authentication failed for user 'postgres'
postgres@master:~$
-----------------


I've looked through the manuals and list archives, but I couldn't find
something similar to this :(  If anyone has any advice (even which FM to
read :) I'd certainly appreciate it!

TIA

Alan


--
Arcterex <arcterex@userfriendly.org>   -==-   http://arcterex.net
"I used to herd dairy cows. Now I herd lusers. Apart from the isolation, I
think I preferred the cows. They were better conversation, easier to milk, and
if they annoyed me enough, I could shoot them and eat them." -Rodger Donaldson