PostgreSQL setup for external connections

PostgreSQL is a fantastic open-source RDBMS which is extremely powerful, once installed on FreeBSD getting it set up is pretty simple. We do want to change some of the defaults though. By default the system will be set up to trust any user using the local socket to connect as any user to any database.

By setting up the initdb flags we can change how PostgreSQL generates the default configuration files. Adding -A md5 tells it to use the md5 authentication mechanism, which requires that passwords sent over the wire are md5 hashed.

Add the following to /etc/rc.conf for the initdb flags, and enable PostgreSQL as a service:

postgresql_initdb_flags="-D /usr/local/pgsql/data -W -A md5"
postgresql_enable="YES"

And then run:

/usr/local/etc/rc.d/postgresql initdb

This will initialise and create the configuration as well as setting up some of the default tables. You will want to navigate to the folder specified in the initdb_flags above:

cd /usr/local/pgsql/data

this is where you will find the files that we are going to modify next to get PostgreSQL set up to listen to connections coming in on a private network (192.168.1.1/24) and where we allow connections coming in from servers on that network. This is strictly for setting up PostgreSQL in a situation where you have multiple servers accessing it as a client on a private network.

After this we need to modify a few files to get it to listen to TCP/IPv4 connections, first we start with postgresql.conf and add:

listen_addresses = '127.0.0.1,192.168.1.1'

Then in pg_hba.conf we add a new line:

host    all     all     192.168.1.1/24      md5

This allows anyone in the 192.168.1.1/24 range to connect to our PostgreSQL instance and to use the databases. This is exactly what we want to allow, now we start PostgreSQL using the rc script:

/usr/local/etc/rc.d/postgresql start