Troubleshooting PostgreSQL Connection Problems

I’ve been using PostgreSQL for years as the back-end database for my company’s intranet, and though I haven’t had to reinstall it once in the past five years on the production server, I inevitably need to reinstall it on my development workstation at least once or twice a year. Of course when the time comes for a reinstall, the steps I’ve taken to make it work have been completely forgotten. Performing a basic install of PostgreSQL which communicates only over the loopback interface (127.0.0.1) is simple, and so I won’t explain that here. Because the platform’s configuration is spread across several files, network configuration can get a bit confusing. This article will explain how to open up PostgreSQL to communicate with other computers on your network.

Open Your Firewall

Displays my Windows XP firewall settings for PostgreSQLThe first step you should take to make PostgreSQL available to computers on your network is verify that the port on which it communicates is open on the server’s firewall. By default, PostgreSQL communicates on port 5432. The picture I’ve included shows my firewall rule for PostgreSQL. As you can see, I open the port for my subnet only. If you change PostgreSQL’s port number using the postgresql.conf, you’ll have to update this firewall settings as well.

Edit postgresql.conf

The next step in opening up your PostgreSQL installation is to modify some settings in the file postgresql.conf, the primary configuration file for PostgreSQL. In a standard Windows installation of PostgreSQL, you can find this file under C:\Program Files\Postgresql\<version>\data\. By default PostgreSQL will only listen on the loopback interface, so you’ll need to tell it to listen on additional network interfaces. You do this by adding a listen_address command to the configuration file. Below are several examples of this command’s format.

listen_address = '*'           # Listen on all network interfaces
listen_address = '192.168.1.1' # Listen on the interface having this address
listen_address = 'localhost, 192.168.1.1' #Listen on specified address and loopback
listen_address = 'dev.domain.org'  # Specify a host name instead of an IP

If you choose to use the second or third example, be sure to substitute your own IP address. If your database server is assigned a dynamic address via DHCP, specify your machine’s host name instead of it’s IP address to avoid problems when your address changes.
[ad name=”content-ad”]

Edit pg_hba.conf

At this point remote machines on your network should successfully connect to PostgreSQL, but they may fail authentication. A second configuration file, called pg_hba.conf, controls client authentication for PostgreSQL and is used to specify which users accounts can connect to which databases. This file is found in the same location as postgresql.conf. At the bottom of this file is a tabular section which contains the default configuration

host    all    all    127.0.0.1/32    md5

The word “host” specifies that this command is a host record. Host records specify which which users can connect to which database over which database connections. The first “all” in this command specifies the name of the database; in this command we specify that all databases in our PostgreSQL installation will be affected by this rule. The second “all” specifies that all users in our PostgreSQL installation are affected by this rule. When we add our own command, we will replace this with a specific user account. The fourth field in this command, “127.0.0.1/24” is a CIDR IP address class. This is used to specify an IP address or range of addresses affected by the record. The part before the slash is an IP address, either the IP address of the connecting machine or one of many machines in a range; the second part is the number of significant bits in the IP address, which allows you to specify a range of IP addresses. Finally the fifth field specifies the type of encryption, which in this example is MD5. There are several legal values for this field including md5, ldap, kerberos, and crypt. A complete list is specified in a comment within the configuration file.

Let’s create an example rule. My database server’s IP address is 192.168.1.1. During installation of Postgresql I kept the default username of the root user (postgres). I have a database called TEST_DATABASE (you create these on the server using the createdb command), and I want everyone on my subnet to have access. My subnet is 255.255.255.0, so 192.168.1.1-255 should be able to connect to the database. I will use the following command:

host    TEST_DATABASE    postgres    192.168.1.1/24    md5

Let’s try one more example. The database is still named TEST_DATABASE, but my server’s IP address is 10.2.0.20. I’ve created a user called “http” which will be used by my web server to access the database, and for security purposes I only want that username to have access from 10.2.0.21, my web server. The following rule will provide this access (notice that to specify a single IP address, you may omit the second part of the CIDR address command.

host    TEST_DATABASE    http    10.2.0.21    md5