Обсуждение: Why pg_dump 'trust' authentication fails under root and works under another user?

Hello

I have pg_dump command in cron that accesses PostgreSQL under user postgres and does not provide a password.

pg_dump --username=postgres ...

I have set in pg_hba.conf that user postgres should be trusted for all databases from localhost.

local all postgres trust

When I run the command under regular user it works.
When I run it under root (sudo -i) it requires password, and therefore the crontab task fails to authenticate.

(I'm looking at both users' 'export' command output, and there seems to be nothing related to PostgreSQL.)

Could someone explain to me what is happening here?

Regards
  J. Záruba

Le 24/03/2011 08:34, Jaroslav Záruba a écrit :
> Hello
>
> I have pg_dump command in cron that accesses PostgreSQL under user postgres
> and does not provide a password.
>
> pg_dump --username=postgres ...
>
> I have set in pg_hba.conf that user postgres should be trusted for all
> databases from localhost.
>
> local all postgres trust
>
> When I run the command under regular user it works.
> When I run it under root (sudo -i) it requires password, and therefore the
> crontab task fails to authenticate.
>

Are you sure the password is not asked by the sudo command, rather than
by the pg_dump one?


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

Please, answer to the list.

Le 24/03/2011 09:13, Jaroslav Záruba a écrit :
> Yes, it is the password for PostgreSQL.
>
> pg_dump: [archiver (db)] connection to database "db123" failed: FATAL:
>  password authentication failed for user "postgres"
> FATAL:  password authentication failed for user "postgres"
>

The error message says the authentication method used isn't trust. Can
you tell us the complete content of your pg_hba.conf (without the
comments of course), and the exact command lines (the one that work and
the one that doesn't work).


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

local all     postgres        trust

host all postgres 192.168.1.0/24 md5

local all all ident

host all all 127.0.0.1/32 md5

host all all ::1/128 md5

Le 24/03/2011 10:05, Jaroslav Záruba a écrit :
> My pg_hba.conf looks like this:
>
> local all     postgres        trust
> host all postgres 192.168.1.0/24 md5
> local all all ident
> host all all 127.0.0.1/32 md5
> host all all ::1/128 md5
>
>

Still lacks the complete pg_dump command lines (both of them, the good
and the bad one).


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

This is what pops up in postgres log whenever I run the command under root, just when I get prompted for password:

Le 24/03/2011 10:39, Jaroslav Záruba a écrit :
> The command line is still the same, when executed under one user it does not
> ask for password, when executed under another (root that is) it does:
> pg_dump --ignore-version --host=localhost --port=5432 --username=postgres
> --file="$BACKUP_FILE" --format=p --column-inserts --encoding=UTF-8 db123
>

The "--host=localhost" option says you're using TCP/IP connection (so
the "host lines" of pg_hba.conf), so it's asking for md5 authentication.
Hence the password.

If one user is not asked for a password, it could be that this user has
a .pgpass file telling pg_dump which password to use.


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

On 24 Mar 10:39, Jaroslav Záruba wrote:
> The command line is still the same, when executed under one user it does not
> ask for password, when executed under another (root that is) it does:
> pg_dump --ignore-version --host=localhost --port=5432 --username=postgres
> --file="$BACKUP_FILE" --format=p --column-inserts --encoding=UTF-8 db123

If you *really* want root to be able to login to the database as the
postgres user using ident authentication, then you're going to need to
add a line to pg_hba.conf using an ident map, then in the ident map
you're going to have to allow root to map to postgres.

root, per default, does not have a user in postgres. Run your script as
the postgres user if you *really* want super user access.

Thanks,
--
Brett Parker                     http://www.sommitrealweird.co.uk/
PGP Fingerprint 1A9E C066 EDEE 6746 36CB  BD7F 479E C24F 95C7 1D61

Le 24/03/2011 11:32, Jaroslav Záruba a écrit :
> You nailed it! :)
>
> My regular user has .pgpass, root does not.
> When I remove the --host (and --port) arguments it works like charm. :)
>
> Thanks a lot, Guillaume!
>

You're welcome :)


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

I don't know why postgres is prompting for a password. But you could try putting the following in your crontab:

su postgres -c 'pg_dump ...'

Simon