How to correctly provide password to PostgreSQL when connecting remotely in Windows?

Question

DB: PostgreSQL 9.0 Client: Windows 7 Server Windows 2008, 64bit

I'm trying to connect remotely to a PostgreSQL instance for purposes of performing a pg_dump to my local machine.

Everything works from my client machine, except that I need to provide a password at the password prompt, and I'd ultimately like to batch this with a script.

I've followed the instructions here:

http://www.postgresql.org/docs/current/static/libpq-pgpass.html

But it's not working.

To recap, I've created a file on the server: C:/Users/postgres/AppData/postgresql/pgpass.conf, where PostgreSQL is the db user.

The file has one line with the following data:

\*:5432:\*postgres:[mypassword]

I've also tried replacing each * with [localhost|myip] and [mydatabasename] respectively.

From my client machine, I connect using:

pg_dump -h <myip> -U postgres -w [mydbname] > [mylocaldumpfile]

I'm presuming that I need to provide the -w switch in order to ignore password prompt, at which point it should look in the AppData directory on the server.

It just comes back with:

connection to database failed: fe_sendauth: no password supplied.

As a hack workaround, if there was a way I could tell the Windows batch file on my client machine to inject the password at the PostgreSQL prompt, that would work as well.

Answer 1

It works for me:

Use command line

cd %appdata%
mkdir postgresql
cd postgresql
notepad pgpass.conf

inside pgpass.conf paste your connection string (*:5432:*postgres:[mypassword]) and save the file. To connect to postgres use:

psql/pg_dump -U <username> -h <host> -w <other params you want to use>

Answer 2

I have solved similar problem (only in Linux) to use ip address in pgpass and psql.

.pgpass

127.0.0.1:5432:db:dbuser:123

psql params

psql -d db -U dbuser -h 127.0.0.1 -w

pg_hba conf with default settings:

# IPv4 local connections:
84 host    all         all         127.0.0.1/32          md5

Answer 3

Create pgpass.conf file

Windows > Start > Run
type %APPDATA%
press OK
Create a folder: postgresql
Create a file  : pgpass.conf   (under postgresql folder)

Open pgpass.conf file

Now you should have below file ready, open it via below (making sure it exists):

Windows > Start > Run
type %APPDATA%\postgresql\pgpass.conf
press OK

Paste pgpass.conf file contents

Paste the below

# serverDomainOrIP:PORT:databaseName:userName:password
# 127.0.0.1:5432:myDbName:postgres:myPassword
# *:5432:*:*:myPassword

You can do one of the below:
- Remove # for the 3rd line, and give your password in the place of "myPassword"
OR
- Remove # for the 2nd line, and give ip (or, yourDomain.com), dbname, username & password

Hope that helps.

Answer 4

I've had a similar problem which I didn't manage to resolve - I couldn't get the script to recognise the pgpass.conf file. I however used a work-around of setting the PGPASSWORD environment variable in the batch file I was using (PostgreSQL 9.6).

Inside a batch file:

SET PGPASSWORD=<<password>> pg_dump.exe -h <<host>> -p <<port>> -U <<user>> -Fc -b -v -f <<output file path>> <<database>>

Answer 5

I have gotten it to work with the following:

pgpass.conf:

127.0.0.1:5432:*:username:password

However, I have it stored here:

C:\Users\<user>\AppData\Roaming\postgresql

For some reason, on a previous iteration of Postgres, the database had generated the pgpass file and stored it there. I was running into the same issue you were having, moved it to that directory and it worked. I'm not sure why though.

Then, all you'll need to do is:

pg_dump -h myip mydb > mylocaldumpfile

...ensuring that myip and the ip in pgpass.conf are identical. If they are not, it will prompt you for a password.

Answer 6

You could use pgAdmin III to store the password (in the server's properties). That operation automatically creates a correct pgpass.conf file. You can then schedule a task to run a simple batch file that would read:

"C:\path\to\pg_dump.exe" -U <user> -w <database> > C:\path\to\database.backup

Answer 7

Make sure you are logged in as the user corresponding with the folder where the pgpass.conf file lives.

Answer 8

If you are using UTF-8 encoding, please ensure that you are using without BOM mode.

Otherwise leave the first line as a comment:

# This line may contain hidden BOM bytes
localhost:5432:database:username:password

Also you don't need to escape asterisks \*, just put * to enable wildcard matching.