CODEWITHSUNDEEP
postgresqlpsqlpg-dump
speed bump
speed bump

Reputation: 451

Postgresql: Able to authenticate using psql but unable to backup with pg_dump

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.

Upvotes: 0

Views: 371

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246298

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!

Upvotes: 1

Related Questions