4

I am trying to backup my PostgreSQL database called crewdb on Ubuntu 18.04 LTS from a script with the following command in it:

pg_dump -h localhost -p 5432 -U postgres -w -C -F p -b -v -f ~/Dropbox\/postgres_backup/crewdb.backup.sql crewdb

I know the script that the above command runs in, itself works. When I run the above command with -W instead of -w I am prompted for a password and the backup goes ahead nicely. I am trying to automate this command within the script and want the backup to go ahead without prompting for a password thus with the -w flag. To this end I created the following file

/home/chh1/.pgpass

When ls -la ~/.pgpass

-rw------- 1 chh1 chh1 74 Oct 15 10:00 .pgpass

Inside the file .pgpass I placed the following text:

# Server:Port:Database:Username:Password

*:*:crewdb:postgres:9Gh#$mq

However when I run the command I get the following error output and the backup fails:

pg_dump -h localhost -p 5432 -U postgres -w -C -F p -b -v -f ~/Dropbox\/postgres_backup/crewdb.backup.sql crewdb

pg_dump: [archiver (db)] connection to database "crewdb" failed: FATAL:
password authentication failed for user "postgres" password retrieved from
file "/home/chh1/.pgpass" FATAL:  password authentication failed for user
"postgres" password retrieved from file "/home/chh1/.pgpass"

I basically followed the following process:

1) Create .pgpass file with content

  *:*:crewdb:postgres:9Gh#$mq

2) set the permissions using command

 sudo chmod 600 .pgpass

3) Set the file owner as the same user using which you logged in :

sudo chown chh1:chh1 .pgpass

4) Set PGPASSFILE environment variable :

 export PGPASSFILE='/home/chh1/.pgpass'

Now when checking with

psql -h localhost -U postgres crewdb

I get a similar error:

psql: FATAL:  password authentication failed for user "postgres"
password retrieved from file "/home/chh1/.pgpass"
FATAL:  password authentication failed for user "postgres"
password retrieved from file "/home/chh1/.pgpass"

The following are the settings in my pg_hba.conf file:

# Database administrative login by Unix domain socket
local   all             postgres                                md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

I do much appreciate if anyone here can put me on the right path!

Christian Hick
  • 145
  • 2
  • 7
  • Can you check that .pgpass is free of any spurious character with `hexdump -C /home/chh1/.pgpass` ? – Daniel Vérité Oct 15 '19 at 15:03
  • Thanks Daniel for your reply. When I run `hexdump -C /home/chh1/.pgpass` I get:` 00000000 23 20 68 6f 73 74 6e 61 6d 65 3a 70 6f 72 74 3a |# hostname:port:| 00000010 64 61 74 61 62 61 73 65 3a 75 73 65 72 6e 61 6d |database:usernam| 00000020 65 3a 70 61 73 73 77 6f 72 64 0a 2a 3a 2a 3a 63 |e:password.*:*:c| 00000030 72 65 77 64 62 3a 70 6f 73 74 67 72 65 73 3a 39 |rewdb:postgres:9| 00000040 47 68 23 24 6d 71 20 0a |Gh#$mq .| 00000048` – Christian Hick Oct 15 '19 at 17:10

1 Answers1

1

The hexadecimal dump of .pgpass shows that there is space character (20) immediately after the password and before the end of line:

47 68 23 24 6d 71 20 0a |Gh#$mq .

This space must be removed otherwise it's taken as part of the password.

Daniel Vérité
  • 3,045
  • 16
  • 19
  • Thanks Daniel, the password now works. I learnt something new with the hexdump command. I had to also use flag -d crewdb and get rid of crewdb at the back of pg_dump command as it was trying to dump default database postgres and not crewdb. – Christian Hick Oct 17 '19 at 02:08