pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedWhy pg_dump 'trust' authentication fails under root and works under another user?
11+ messages / 4 participants
[nested] [flat]
* Why pg_dump 'trust' authentication fails under root and works under another user?
@ 2011-03-24 07:34 Jaroslav Záruba <jaroslav.zaruba@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Jaroslav Záruba @ 2011-03-24 07:34 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why pg_dump 'trust' authentication fails under root and works under another user?
@ 2011-03-24 07:49 Simon <simon@apricotwebsolutions.com>
parent: Jaroslav Záruba <jaroslav.zaruba@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Simon @ 2011-03-24 07:49 UTC (permalink / raw)
To: pgsql-admin
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.zaruba@gmail.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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why pg_dump 'trust' authentication fails under root and works under another user?
@ 2011-03-24 08:03 Guillaume Lelarge <guillaume@lelarge.info>
parent: Jaroslav Záruba <jaroslav.zaruba@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Guillaume Lelarge @ 2011-03-24 08:03 UTC (permalink / raw)
To: Jaroslav Záruba <jaroslav.zaruba@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why pg_dump 'trust' authentication fails under root and works under another user?
@ 2011-03-24 08:56 Guillaume Lelarge <guillaume@lelarge.info>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 1 reply; 11+ messages in thread
From: Guillaume Lelarge @ 2011-03-24 08:56 UTC (permalink / raw)
To: Jaroslav Záruba <jaroslav.zaruba@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why pg_dump 'trust' authentication fails under root and works under another user?
@ 2011-03-24 09:05 Jaroslav Záruba <jaroslav.zaruba@gmail.com>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 1 reply; 11+ messages in thread
From: Jaroslav Záruba @ 2011-03-24 09:05 UTC (permalink / raw)
To: ; +Cc: pgsql-admin
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@lelarge.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
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why pg_dump 'trust' authentication fails under root and works under another user?
@ 2011-03-24 09:27 Guillaume Lelarge <guillaume@lelarge.info>
parent: Jaroslav Záruba <jaroslav.zaruba@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Guillaume Lelarge @ 2011-03-24 09:27 UTC (permalink / raw)
To: Jaroslav Záruba <jaroslav.zaruba@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why pg_dump 'trust' authentication fails under root and works under another user?
@ 2011-03-24 09:39 Jaroslav Záruba <jaroslav.zaruba@gmail.com>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 3 replies; 11+ messages in thread
From: Jaroslav Záruba @ 2011-03-24 09:39 UTC (permalink / raw)
To: pgsql-admin
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@lelarge.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
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why pg_dump 'trust' authentication fails under root and works under another user?
@ 2011-03-24 09:52 Brett Parker <iDunno@sommitrealweird.co.uk>
parent: Jaroslav Záruba <jaroslav.zaruba@gmail.com>
2 siblings, 0 replies; 11+ messages in thread
From: Brett Parker @ 2011-03-24 09:52 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why pg_dump 'trust' authentication fails under root and works under another user?
@ 2011-03-24 10:04 Jaroslav Záruba <jaroslav.zaruba@gmail.com>
parent: Jaroslav Záruba <jaroslav.zaruba@gmail.com>
2 siblings, 0 replies; 11+ messages in thread
From: Jaroslav Záruba @ 2011-03-24 10:04 UTC (permalink / raw)
To: pgsql-admin
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.zaruba@gmail.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@lelarge.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
>>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why pg_dump 'trust' authentication fails under root and works under another user?
@ 2011-03-24 10:25 Guillaume Lelarge <guillaume@lelarge.info>
parent: Jaroslav Záruba <jaroslav.zaruba@gmail.com>
2 siblings, 1 reply; 11+ messages in thread
From: Guillaume Lelarge @ 2011-03-24 10:25 UTC (permalink / raw)
To: Jaroslav Záruba <jaroslav.zaruba@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why pg_dump 'trust' authentication fails under root and works under another user?
@ 2011-03-24 10:47 Guillaume Lelarge <guillaume@lelarge.info>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 0 replies; 11+ messages in thread
From: Guillaume Lelarge @ 2011-03-24 10:47 UTC (permalink / raw)
To: Jaroslav Záruba <jaroslav.zaruba@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2011-03-24 10:47 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-03-24 07:34 Why pg_dump 'trust' authentication fails under root and works under another user? Jaroslav Záruba <jaroslav.zaruba@gmail.com>
2011-03-24 07:49 ` Simon <simon@apricotwebsolutions.com>
2011-03-24 08:03 ` Guillaume Lelarge <guillaume@lelarge.info>
2011-03-24 08:56 ` Guillaume Lelarge <guillaume@lelarge.info>
2011-03-24 09:05 ` Jaroslav Záruba <jaroslav.zaruba@gmail.com>
2011-03-24 09:27 ` Guillaume Lelarge <guillaume@lelarge.info>
2011-03-24 09:39 ` Jaroslav Záruba <jaroslav.zaruba@gmail.com>
2011-03-24 09:52 ` Brett Parker <iDunno@sommitrealweird.co.uk>
2011-03-24 10:04 ` Jaroslav Záruba <jaroslav.zaruba@gmail.com>
2011-03-24 10:25 ` Guillaume Lelarge <guillaume@lelarge.info>
2011-03-24 10:47 ` Guillaume Lelarge <guillaume@lelarge.info>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox