Accessing PostgreSQL Server From Remote Machine

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.

  1. Edit the pg_hba.conf file to enable client connections as shown below. The important thing is the use of 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               trust
    host    all             all                   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               trust
    host    replication     postgres        ::1/128                 trust
  2. 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.
  3. Restart the postgreSQL server either using postgres service or first killing the process and then starting using pg_ctl script.
  4. 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
  5. Then restart the iptables service using service iptables restart
  6. Finally test it with psql client by executing following command ./psql -U postgres -h -p 5432

2 thoughts on “Accessing PostgreSQL Server From Remote Machine”

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: