Re: newbie authentication/automated backup (pg_dumpall)

I believe you can define the PGPASSWORD environment variable and pg_dumpall
will use it for hands off authentication. Since you are using crypt
authentication, the password probably has to be crypt()ed, but don't quote
me on that.

Also, I believe specifying IP/mask for "local" types is irrelevant:

> local all 127.0.0.1 255.0.0.0 ident sameuser

"local" means using the Unix domain socket, and not a TCP connection.

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"

> From: Alan <alan(at)ufies(dot)org>
> Date: Wed, 17 Oct 2001 10:28:18 -0700
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] newbie authentication/automated backup (pg_dumpall)
> questions
>
> 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(at)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(at)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(at)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(at)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(at)userfriendly(dot)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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>