Re: 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

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

2011/3/24 Jaroslav Záruba <jaroslav(dot)zaruba(at)gmail(dot)com>

> 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

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

2011/3/24 Guillaume Lelarge <guillaume(at)lelarge(dot)info>

> Please, answer to the list.
>

(Wrong button, I'm sorry for that.)

> 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
>

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

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

2011/3/24 Guillaume Lelarge <guillaume(at)lelarge(dot)info>

> 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
>

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

This is what pops up in postgres log whenever I run the command under root,
just when I get prompted for password:
--
could not receive data from client: Connection reset by peer
--

2011/3/24 Jaroslav Záruba <jaroslav(dot)zaruba(at)gmail(dot)com>

> 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
>
> 2011/3/24 Guillaume Lelarge <guillaume(at)lelarge(dot)info>
>
>> 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
>>
>
>

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

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