CODEWITHSUNDEEP
postgresqlauthenticationpg-dump
ninapavlich
ninapavlich

Reputation: 749

How can I pass a password to pg_dump 10 when . pgpass in home directory is not an option?

I am running a cron job that creates a database dump using the pg_dump command, like so:

export PGPASSWORD="XXXXXXXX"; pg_dump -h localhost -U my_user my_db > /tmp/db_dump.sql

But when I run this command, I get a password authentication error:

pg_dump: [archiver (db)] connection to database "my_db" failed: FATAL:  password authentication failed for user "my_user"
FATAL:  password authentication failed for user "my_user"

I am not able to place a .pgpass file under the user that's running the cron, but I could place it elsewhere and specify the PGPASSFILE like so:

export PGPASSFILE=/path/to/.pgpass; pg_dump -h localhost -U my_user my_db > /tmp/db_dump.sql

But again this returns the same authentication error. I have confirmed that this is not a problem of having the wrong password, because it works when I use the interactive prompt:

pg_dump --password -h localhost -U my_user my_db > /tmp/db_dump.sql
Password: XXXXXXXX

When I look at the documentation, it looks like PGPASSWORD and PGPASSFILE are missing from the environmental variables (Perhaps they were removed?) https://www.postgresql.org/docs/10/app-pgdump.html

If I can't create a .pgpass file located in the home directory of the user running the script, how else can I run the pg_dump command?

For context, I am running pg_dump version 10.6:

pg_dump --version
pg_dump (PostgreSQL) 10.6 (Ubuntu 10.6-0ubuntu0.18.04.1)

Upvotes: 2

Views: 18953

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246298

Your original statement will work if all $, ?, *, " and \ are properly escaped.

Using single quotes rather than double quotes is the better solution, as a_horse_with_no_name suggested in a comment:

export PGPASSWORD='XXXXXXXX'; pg_dump -h localhost -U my_user my_db > /tmp/db_dump.sql

An alternative that is usually better is to use a password file.

Using a password is always nasty because it has to sit around in clear text.

There are alternatives, like using trust authentication (with a very limited pg_hba.conf entry) or certificate authentication.

Upvotes: 14

Related Questions