Nowadays in many cases, one can end up having a PostgreSQL server running as a standalone service in the cloud. Henceforth, one therefore needs to set up the database server for remote access. The critical step in such setup is the security of your PostgreSQL server. Here it is shown as step by step process how to secure your server by limiting access to it.
- Add firewall settings to the server using iptables.
-A INPUT -s <ip_address_that_needs_access>/32 -p tcp -m tcp --dport 5432 -j ACCEPT
-A OUTPUT -d <ip_address_that_needs_access>/32 -p tcp -m tcp --sport 5432 -j ACCEPT​
This allows tcp accessonly for the mentioned IP address directing to the postgresql port 5432 of the server.
- Change listen_addresses = 'localhost' in the postgresql.conf file to:
listen_addresses = '*' # vim /etc/postgresql/9.4/main/postgresql.conf
This allows remote access to the server.
- Add the following line at the end of the file pg_hba.conf.
host <DB_NAME> <DB_USER> <ip_address_that_needs_access>/32 md5 # vim /etc/postgresql/9.4/main/pg_hba.conf
This restricts the postgresql server access. The above line indicates that only <ip_address_that_needs_access> can access the database <DB_NAME> having user as <DB_USER> .
That's it. Having redundancy in security is never bad. Secure your PostgreSQL server !