Configure PostgreSQL to allow remote connection

By default, PostgreSQL will only allow localhost connection. If you want allow remote connections, you need to configure it.

Configuring postgresql.conf

First we need to find out where our conf file is located. You have two options:
If you are new to Postgres, you would like to use the classic ‘find’ command:

find / -name "postgresql.conf"

And the Pro users would be use:

psql -c "show config_file" postgres config_file

After you find the location of the configuration file, you need to replace the value at ‘listen_addresses’ with ‘*’ or your IP address. For security reasons do not use the ‘*’. By default it’s ‘localhost’.

listen_addresses = '*'

Now the server will accept remote connections from the client IP address / subnet which you have configured. But you will not be able to connect, because we did not configure which users can connect remotely.

Configuring pg_hba.conf

To configure the users which we want to allow to connect remotely, you need to configure the pg_hba.conf file.

First we need out where our pg_hba.conf file is located.

find / -name "pg_hba.conf"

And then add following line to the end of the file

# TYPE  DATABASE    USER    ADDRESS       METHOD
host    all         all     0.0.0.0/0     md5

For Security reasons you should never use 0.0.0.0/0, limit to an IP address or to a Subnet. For example as below:

# TYPE  DATABASE    USER    ADDRESS            METHOD
host    all         all     192.168.50.0/24    md5

Now restart the PostgreSQL server.

PostgreSQL is now ready to accept remote connections.

Don’t forget to open Firewall ports

By default PostgreSQL is using Port 5432. To to open the port on Redhat/CentOS/SUSE:

firewall-cmd --permanent --add-port=5432/tcp
firewall-cmd --reload

Well done! We successfully configure remote connection.