Right Outer Join

24 August 2012

Configure PostgreSQL for remote connections

Filed under: JasperReports — mdahlman @ 16:46

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.

I found useful articles online:

But these articles 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 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 so 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 by default. So I made no modifications to this file.

Advertisements

1 Comment »

  1. Hei, thanks for you post. I am setting up a server PostgreSQL on a synology NAS. I can Access to the NAS file from remote (with quickconnect) but seems like I am not able to open my LibreOfficebase.
    Since my NAs is behind a router do I have to access to it by VPN or have a Static IP address to my router with port forwarding?

    Comment by datahjelposlo — 20 July 2017 @ 02:02


RSS feed for comments on this post. TrackBack URI

Go on... leave a reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: