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
2 thoughts on “Accessing PostgreSQL Server From Remote Machine”
Reblogged this on Zeeshan Akhter.
You probably don’t want remote connections using ‘trust’ authentication as there is no password checking, and anyone can connect by merely giving a valid username. See http://www.postgresql.org/docs/8.2/static/auth-methods.html#AUTH-TRUST .