By default, PostgreSQL will only allow localhost connection. If you want allow remote connections, you need to configure it.
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.
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.