Setting up PostgreSQL for external access

By default PostgreSQL installed configuration only allows connections from the localhost. This is a good thing because the average user most likely has a LAMP style setup where all services are contained within the same server and this will limit its vulnerability to the outside.

The install I am using will need to support multiple connections from many different locations simultaneously. For example we have a couple ASP.NET applications that will access different databases on the server as well as a few Network appliances, for example snort, that will also access databases on the server.

First lets setup the server to start listening on the external interfaces. To do this we will need to access the posgres data directory. As I explained before I moved my data directory from the FreeBSD default location (/usr/local/pgsql/data) to /data/pgsql. In the postgres data directory you will need to edit the file postgresql.conf. Find the setting for listen_addresses and change it from localhost to * or a specific IP address or list of ip addresses.

listen_addresses = '*'                  # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)
max_connections = 80                    # (change requires restart)

You will also notice that I changed the max_connections from 40 to 80, you could also change the port that postgres listens on if you chose but for this I will leave the default.

Next we need to edit the pg_hba.conf file to allow connections from an IP space other than the localhost (127.0.0.1). There may be more secure ways to setup this but between the firewall, ip restrictions, user accounts and md5 I feel pretty confident in my setup (until someone tells me otherwise). Anyway say I have a .NET application on my windows server(10.3.0.100), a database named testdb and the user i will be connection to the database with is named testuser if I wanted to set postgresql to allow this connection I would add the following line to the pg_hba.conf.

host    testdb       testuser 10.3.0.100/32   md5

Once I have made my changes to the configuration files I need to restart postgresql. To do this I run /usr/local/etc/rc.d/postgresql restart and in a few seconds I should be able to test the connection in my .NET application.