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