Configure PostgreSQL for remote connections

I want my PostgreSQL instance to accept remote connections. By default it does not. That’s a nice secure default setting. But this is a development server inside a corporate firewall. I have no concerns whatsoever about security. There is no sensitive data. I want anyone to be able to connect to postgres using a valid username, password, and IP address.

There are useful articles online:

http://www.cyberciti.biz/tips/postgres-allow-remote-access-tcp-connection.html

http://kb.parallels.com/en/1133

http://stackoverflow.com/questions/4328679/how-to-configure-postgresql-so-it-accepts-loginpassword-auth

But these are not exactly what I wanted. They are old (PostgreSQL 7 and 8). They assume Linux or Mac OS as the platform. I wanted the exact instructions for Windows.

Converting the instructions for PostgreSQL 8 on Linux to PostgreSQL 9 on Windows isn’t so hard… but I wanted to document it clearly once for myself and for anyone else facing a similar requirement.

Open PostgreSQL up to the world

1. Edit this file:
C:\Program Files\PostgreSQL\9.1\data\pg_hba.conf

Add this line to the end to that PostgreSQL will allow connections from absolutely any address with password authentication:

host all all 0.0.0.0/0 md5

2. Restart the postgres service. In my case it was called “postgresql-x64-9.1″.

3. Add a rule in the Windows Firewall to allow incoming connections on port 5432.
Yes, you could run PostgreSQL on some other port… but why would you do that?

The same things can be done from the pgAdmin III utility if you’re inclined towards GUIs instead of the command line.

Now you can connect remotely. For example, using a JDBC url like this:
jdbc:postgresql://172.17.5.118:5432/samples

Hopefully this will save someone else the time of figuring out the steps in Windows. That leaves more time for thinking about how to use PostgreSQL.

—-

Appendix

Apparently in the past it was required to edit this file:
C:\Program Files\PostgreSQL\9.1\data\postgresql.conf

listen_addresses = '*'

But I found that it already included this line to listen for anyone. So I made no modifications to this file.