Postgres protected access

Question

I am trying to configure my Postgres instance so that only the local machine and my machine (over internet) should be able to access the database. The postgresql.conf file has parameter listen_addresses which can be used to set the list of ip addresses to which access is granted.

So I set it as listen_addresses 'localhost,a.b.c.d' which mean it will grant access to localhost and a.b.c.d which is, say my ip address. But in this case it just grant access to localhost.

listen_addresses 'a.b.c.d' also does not work. I double checked the internet ip address but it is correct. I tried

listen_addresses 'localhost'         // works only for local machine.
listen_addresses '*'                 // works for both local and my machine
listen_addresses 'localhost,a.b.c.d' // does not work for my machine
listen_addresses 'a.b.c.d'           // does not work for my machine

So am I missing something here ?

Answer 1

You have understood the concept of listen addresses wrong.

listen_addresses is the list of IP addresses of the interfaces on the server running PostgreSQL, which can be used to connect to the PostgreSQL service.

So, for example, your PostgreSQL server has the IP address 192.168.10.1, you specify:

listen_addresses 'localhost,192.168.10.1';

This means that all clients that can reach IP address 192.168.10.1 can connect to the PostgreSQL server.

If you want to restrict connections by connecting client's IP address, then you have two options:

1. Use PostgreSQL's host-based access mechanism to apply IP restrictions on connections using pg_hba.conf. See client authentication.
2. Add firewall rules that prevent connections to PostgreSQL port from all IP addresses except the ones you want to allow.