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