newbie authentication/automated backup (pg_dumpall) questions

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"

Well, your pretty close to where you want to be (uh,
you're editting the right file :). Usually the
comments in the pg_hba.conf file are more than enough
to get you on the right track, but if you have already
removed all of the comments then that isn't
particularly helpful.

See:
http://www.postgresql.org/idocs/index.php?client-authentication.html

Or install the postgresql-doc package (I would
recommend it) and see:

file:///usr/share/doc/postgresql-doc/html/client-authentication.html

Depending on what you want to do, here's the trick:

First of all, you want to give your local users
access. This is especially useful for scripts like
pg_dumpall. This is accomplished with a local record
like this:

# local access
local all trust

In this example I have chosen to "trust" local users.
That way it doesn't matter what my Unix username is,
if I tell PostgreSQL that I am postgres then I get
full db administrator access. So:

psql processdata -U postgres

gives me the works, even if I am logged on as someone
other than postgres.

You can replace "trust" with "ident" if you want to
only allow users to log onto PostgreSQL using their
Unix username. Or, since you are using Debian you can
use "peer" which does the same thing, but doesn't
require that you be running identd. This option is
not a standard PostgreSQL option, although it was
talked about a while back, and it probably will become
a standard option perhaps with a different name (you
have been warned). See
/usr/share/doc/postgresql/README.Debian.gz for more
information.

If you are only allowing access to the database via
Unix sockets then you are done. However, some
software packages require that you connect via TCP/IP
even when you are on the same machine. In that case
you will need a host record as well. To add an entry
for the localhost simply add:

#localhost
host all 127.0.0.1 255.255.255.255
trust

This gives localhost access to "all" databases, and
once again tells PostgreSQL to "trust" the users. You
might want to consider changing that to "ident" or
even "crypt" depending on what your needs are.

I hope this is helpful,
Jason Earl

--- Alan <alan@ufies.org> wrote:

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

---------------------------(end of
broadcast)---------------------------
TIP 2: you can get off all lists at once with the
unregister command
(send "unregister YourEmailAddressHere" to

majordomo@postgresql.org)

__________________________________________________
Do You Yahoo!?
Make a great connection at Yahoo! Personals.
http://personals.yahoo.com