Обсуждение: pg_dumpall and password access

I'm trying to run pg_dumpall to backup all my users' bases but since I
have configure pg_hba.conf to "passwd" pg_dumpall always fails:

su-2.03$ pg_dumpall >backup.dat # I'm the postgres unix user.
Password: psql: Password authentication failed for user 'postgres'
Password: psql: Password authentication failed for user 'postgres'
Password: psql: Password authentication failed for user 'postgres'
# I type my password on the next line
Password: Password: psql: Password authentication failed for user 'postgres'
Connection to database 'sympa' failed.
fe_sendauth: no password supplied

pg_dump failed on sympa, exiting


I need to have a password identification since I don't want the
"common" users to be able to connect to any database. Beside I cannot
leave the "local" to the "trust" auth scheme because every users are
able to run programs on the machine hosting the database.

Is there any configuration tips or workaround known for my problem ?


-- 
Le cinéma en Lumière : http://www.lumiere.org/
Fingerprint : 4721 651D D309 B302 93E4  5D73 CC52 DF3A E7C7 86CF

Christophe Labouisse <labouiss@cybercable.fr> writes:
> I'm trying to run pg_dumpall to backup all my users' bases but since I
> have configure pg_hba.conf to "passwd" pg_dumpall always fails:

pg_dumpall doesn't work very well with password authentication (and
even if it did, storing the password in a cron script doesn't seem
like a good idea to me).

As long as the dumper will run on the same machine as the database
server, consider using IDENT authorization instead.  You'll need an
identd daemon running, and you'll need to use a TCP connection because
IDENT only works with TCP connections.  The simplest form of pg_hba.conf
entry is

host         all         127.0.0.1     255.255.255.255     ident sameuser

If you have no other users you want to let in, you could use an explicit
ident map instead of 'sameuser' to tighten this up even more.  See the
admin documentation for more info.

I don't recommend using IDENT for connections from untrusted machines,
but on localhost it's as trustworthy as your local sysadmin...
        regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> wrote:
>pg_dumpall doesn't work very well with password authentication (and
>even if it did, storing the password in a cron script doesn't seem
>like a good idea to me).
>
>As long as the dumper will run on the same machine as the database
>server, consider using IDENT authorization instead.

Another solution is to use the patched pg_dumpall at
http://bugs.debian.org/87035 .

Ray
-- 
The Internet interprets attempts at proprietary control as damage and routes
around it.Eric S. Raymond

At 19.29 3/4/01 -0400, you wrote:
>Christophe Labouisse <labouiss@cybercable.fr> writes:
> > I'm trying to run pg_dumpall to backup all my users' bases but since I
> > have configure pg_hba.conf to "passwd" pg_dumpall always fails:
>
>pg_dumpall doesn't work very well with password authentication (and
>even if it did, storing the password in a cron script doesn't seem
>like a good idea to me).
From the cron script you can execute somethin like this.
        su -l postgres -c pg_dumpall <parameters>

and then, "pg_dumpall" will be executed by the postgres user.