Re: newbie authentication/automated backup (pg_dumpall) questions

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(at)ufies(dot)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(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)

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