Today I was trying to remotely connect to PostgreSQL server and it took me some time to configure it. So, in this short blog I am sharing the procedure to make it work.
- Edit the pg_hba.conf file to enable client connections as shown below. The important thing is the use of 0.0.0.0/0 to allow client access from any IP address. You can also define a subset of ip here.
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust host all all 0.0.0.0/0 trust # IPv6 local connections: host all all ::1/128 trust # Allow replication connections from localhost, by a user with the # replication privilege. #local replication postgres md5 host replication postgres 0.0.0.0/0 trust host replication postgres ::1/128 trust
- Edit the postgresql.conf file and change listen_addresses = ‘*’ . This means any client can access the server. By default value is local. You can also specify a comma separated list of ip address also.
- Restart the postgreSQL server either using postgres service or first killing the process and then starting using pg_ctl script.
- The last and most important thing is to edit the /etc/sysconfig/iptables file and add entry to allow access to 5432 port. This can be done as shown below. Please add the line shown below in iptables file.
-A INPUT -m state –state NEW -m tcp -p tcp –dport 5432 -j ACCEPT
- Then restart the iptables service using service iptables restart
- Finally test it with psql client by executing following command ./psql -U postgres -h 192.168.0.10 -p 5432