Home > Software design >  Postgresql: Able to authenticate using psql but unable to backup with pg_dump
Postgresql: Able to authenticate using psql but unable to backup with pg_dump

Time:04-05

I have trouble backing up a PostgreSQL database. When I run the following command:

psql -d cdb -U "$(whoami)"

The result:

cdb=# \c
You are now connected to database "cdb" as user "gub".

So no problems with that. However when I run

pg_dump -h localhost -p 5432 -U "$(whoami)" -d cdb -w -C -F p -b -f /home/"$(whoami)"/cdb.sql

I get the following error message:

pg_dump: error: connection to database "cdb" failed: fe_sendauth: no password supplied

I have the following auth settings:

cdb=# select * from pg_hba_file_rules;
 line_number | type  |   database    | user_name  |  address  |                 netmask                 | auth_method | options | error 
------------- ------- --------------- ------------ ----------- ----------------------------------------- ------------- --------- -------
          89 | local | {all}         | {postgres} |           |                                         | peer        |         | 
          94 | local | {all}         | {all}      |           |                                         | peer        |         | 
          96 | host  | {all}         | {all}      | 127.0.0.1 | 255.255.255.255                         | md5         |         | 
          98 | host  | {all}         | {all}      | ::1       | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | md5         |         | 
         101 | local | {replication} | {all}      |           |                                         | peer        |         | 
         102 | host  | {replication} | {all}      | 127.0.0.1 | 255.255.255.255                         | md5         |         | 
         103 | host  | {replication} | {all}      | ::1       | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | md5         |         | 

I am not sure why this is not working as auth_method peer should not require a password. Can someone please help? postgresql.conf has not been changed and setup is: psql (PostgreSQL) 12.9 (Ubuntu 12.9-0ubuntu0.20.04.1)

My goal is to backup without the need of a password.

CodePudding user response:

With psql, you are using a local connection via domain sockets, since you didn't specify -h localhost, so the first or second pg_hba.conf line applies, and you can connect without a password.

With pg_dump, you are using a TCP connection, and the third or fourth line applies, and you have to supply a password.

Omit -h localhost!

  • Related