How Do I Enable remote access to PostgreSQL database server?

By default, PostgreSQL database server remote access disabled for security reasons. However, some time you need to provide the remote access to database server from home computer or from web server.

Step # 1: Login over ssh if server is outside your IDC

Login over ssh to remote PostgreSQL database server:
$ ssh

Step # 2: Enable client authentication

Once connected, you need edit the PostgreSQL configuration file, edit the PostgreSQL configuration file /var/lib/pgsql/data/pg_hba.conf (or /etc/postgresql/8.2/main/pg_hba.conf for latest 8.2 version) using a text editor such as vi.

Login as postgres user using su / sudo command, enter:
$ su - postgres
Edit the file:
$ vi /var/lib/pgsql/data/pg_hba.conf
$ vi /etc/postgresql/8.2/main/pg_hba.conf
Append the following configuration lines to give access to network:
host all all trust
Save and close the file. Make sure you replace with actual network IP address range of the clients system in your own network.

Step # 2: Enable networking for PostgreSQL

You need to enable TCP / IP networking. Use either step #3 or #3a as per your PostgreSQL database server version.

Step # 3: Allow TCP/IP socket

If you are using PostgreSQL version 8.x or newer use the following instructions or skip to Step # 3a for older version (7.x or older).

You need to open PostgreSQL configuration file /var/lib/pgsql/data/postgresql.conf or /etc/postgresql/8.2/main/postgresql.conf.
# vi /etc/postgresql/8.2/main/postgresql.conf
# vi /var/lib/pgsql/data/postgresql.conf
Find configuration line that read as follows:
Next set IP address(es) to listen on; you can use comma-separated list of addresses; defaults to ‘localhost’, and ‘*’ is all ip address:
Or just bind to and IP address
Save and close the file. Skip to step # 4.

Step #3a – Information for old version 7.x or older

Following configuration only required for PostgreSQL version 7.x or older. Open config file, enter:
# vi /var/lib/pgsql/data/postgresql.conf
Bind and open TCP/IP port by setting tcpip_socket to true. Set / modify tcpip_socket to true:
tcpip_socket = true
Save and close the file.

Step # 4: Restart PostgreSQL Server

Type the following command:
# /etc/init.d/postgresql restart

Step # 5: Iptables firewall rules

Make sure iptables is not blocking communication, open port 5432 (append rules to your iptables scripts or file /etc/sysconfig/iptables):

iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d  --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s --sport 5432 -d 0/0 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

Restart firewall:
# /etc/init.d/iptables restart

Step # 6: Test your setup

Use psql command from client system. Connect to remote server using IP address and login using vivek username and sales database, enter:
$ psql -h -U vivek -d sales

Further readings:

=> man page: pgsql and configuration file

🐧 Get the latest tutorials on Linux, Open Source & DevOps via RSS feed or Weekly email newsletter.

🐧 46 comments so far... add one
CategoryList of Unix and Linux commands
Disk space analyzersdf ncdu pydf
File Managementcat tree
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network UtilitiesNetHogs dig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04
46 comments… add one
  • Sam Oct 9, 2007 @ 15:09


    Just tried out this and worked like wonder.

  • Raju Oct 9, 2007 @ 15:11

    Keep up the great blogging!

  • Swapneel Nov 14, 2007 @ 2:07

    Just wanted to point out that with PostgreSQL 8.x the option tcpip_socket is no longer valid.

    The correct parameter is,

    listen_address = ‘localhost’

    which needs to be changed to,

    listen_address = ‘*’

    and PostgreSQL daemon restarted.

  • vibhor goyal Jan 22, 2008 @ 7:26

    am working on postgres8.2.4

    iptables -A INPUT -p tcp -s 0/0 –sport 1024:65535 -d –dport 5432 -m state –state NEW,ESTABLISHED -j ACCEPT
    iptables -A OUTPUT -p tcp -s –sport 5432 -d 0/0 –dport 1024:65535 -m state –state ESTABLISHED -j ACCEPT

    these additions gave me error on restarting, asking me to restore the iptables.

    I removed the iptables from the above lines and it worked.

    as am novice in this field, so am not sure if what i did was correct.

  • sashbeer bhandari Feb 26, 2008 @ 14:07

    how to improve speed and performance of database server on linux

  • Xionicfire Apr 9, 2008 @ 20:07

    “How Do I Enable remote access to PostgreSQL database server? ”

    Still does not answer the question…

    the answer just points on to giving access to a small network, but what if you want to give access to ANY IP not just your network, basically REAL remote access.

    whats the command for that

  • 🐧 nixCraft Apr 9, 2008 @ 20:50

    ‘*’ = ALL, so use

    host all all * md5

    Refer to man page.

    but what if you want to give access to ANY IP not just your network, basically REAL remote access.
    This is a bad idea, giving out access to whole world is really a bad idea. Do you wanna anyone come and try to connect your database server?

  • Olmec Sinclair Sep 9, 2008 @ 0:08

    I am technical, but database and server admin is mot my area. I am writing a linux desktop application that reads data from postgres DB. I would like this application to able to use a central database (on my server).

    I see vivek (#7 above) recommends not opening up postgres to all the world. What is recommendation for a distributed application? Is there some way for the application to identify itself to postgres? Since each instance of the application will be an a separate network.

    Thanks for the info.

  • Goran Sep 27, 2008 @ 19:08

    During instalation process I can not log in. What I have to enter in the username and password fields to be able to conect?
    I tried as username: postgres and for password postgrespass and postgresuser, but received a message :”invalid password or username”!?!?!

    • Ankit Jun 5, 2014 @ 14:19

      Username= postgres
      password which did you write during of installation

  • niraj Feb 19, 2009 @ 13:09

    very helpfull for newbie

  • frankpeng Mar 15, 2009 @ 0:50

    How is this:
    1, Listen to the world *
    2, Use md5
    3, only those database allowed
    4, only specific user.
    I will try this.

  • Louis Apr 3, 2009 @ 9:09

    Hi all,

    Now, i m facing problem in log in to postgresql by webmin interface. Problem is that all a long I used to login postgresql DB by webmin interface and able to login smoothly . But last few days ago , i ‘ve changed the root (super user) password and not able to log in at all . I believe password has been wrong for over 3 times and IP address has been blocked .
    Hence , I ‘ve tried to login from different IP address and also the same thing encountering.
    I ‘m using Postgresql very 7 + . Kindly pls help me to solve my problem by how to enable such thing back to normal . There is no IP talbes restrictions .

    Looking forward to your solutions..

  • Louis Apr 3, 2009 @ 9:11

    Hi all,

    Now, when i tried to log in , below is the error for yours references :

    12204 SSL port specified is not allowed.

    An error occurred while trying to retrieve your URL.
    This error could have been caused by:

    Bad / misspelled URL
    Your access permissions
    Your network connection and/or transient conditions on the Internet
    An error on the source web server


    Microsoft Proxy Server v2.0

    And also , this postgresql is running on Linux Debian , port number 10000 for it.


  • Louis Apr 3, 2009 @ 9:15

    Hi all ,

    Here is the error message for my issue,

    12204 SSL port specified is not allowed.
    An error occurred while trying to retrieve your URL.
    This error could have been caused by:
    Bad / misspelled URL
    Your access permissions
    Your network connection and/or transient conditions on the Internet
    An error on the source web server

    Hence, its running on Linux Debian and port 10000 ,

  • Bradley D. Thornton Apr 4, 2009 @ 23:42

    Okay, that’s great for remote access via ssh. My problem is that there is a PostgreSQL Server in one datacenter, and a php based website in another data center. I’m told that you can’t enable PostgreSQL support in PHP w/o actually installing PostgreSQL Server on that machine, but I don’t want PostgreSQL on the webserver, and I have a customer that want’s to use it via his website w/PHP.

    This is what I’ve gotten so far from

    In order to enable PostgreSQL support, –with-pgsql[=DIR] is required when you compile PHP. DIR is the PostgreSQL base install directory, defaults to /usr/local/pgsql. If shared object module is available, PostgreSQL module may be loaded using extension directive in php.ini or dl() function.

    and from I get:

    To use PostgreSQL support, you need PostgreSQL 6.5 or later, PostgreSQL 8.0 or later to enable all PostgreSQL module features.

    So my question is, can I, or how can I enable PostgreSQL support in PHP w/o installing the actual database server on that particular machine?



    • 🐧 nixCraft Apr 5, 2009 @ 2:25

      No Need to install PostgreSQL on same server. To connect via php, simply specifiy server IP, username and password. You also need to open port using iptables. Also, remote connection between two servers in the same data center is recommended. If you have a two different data center consider security (which can be overcome using SSL connection between db server and php app server) and latency.


  • eddie Apr 29, 2009 @ 10:26

    I am still having problems connecting. my pg_hba.conf file is thus
    # “local” is for Unix domain socket connections only
    local all all * trust
    IPv4 local connections:
    host all all * trust
    # IPv6 local connections:
    host all all ::1/128 ident sameuser

    my error message is this
    psql -h -d template1
    psql: FATAL: missing or erroneous pg_hba.conf file
    HINT: See server log for details.

    what am I doing wrong cause it was working before..

  • eddie Apr 29, 2009 @ 11:27
  • Thiago Lopes Feb 2, 2010 @ 20:01


    In CentOS :

    Verify /var/lib/pgsql/data/postgresql.conf :

    Not only change:

    #tcpip_socket = false

    tcpip_socket = true

    But too:

    #port = 5432

    port = 5432

    In /var/lib/pgsql/data/pg_hba.conf add:

    host name_base name_user md5


    host name_base name_user fixed_ip md5


  • SimeON Feb 24, 2010 @ 13:58

    Hello All,

    I have postgre installed via ports and the only config file I have is :

    Any idea of what could it be ?

  • srikanth Jul 1, 2010 @ 15:58

    thanks dude…

  • Mike Oct 19, 2010 @ 19:16

    Thanks for the doc: it resolved my issue.

    There is a small syntax error in your listen_addresses field (step #3): the list should be comma (NOT space) separated.

  • mark Oct 22, 2010 @ 8:22

    these instructions should be in the postgres documentation! great job, thanks

  • Joris Nov 11, 2010 @ 20:48

    No more need to use IPTABLES, just type some: « sudo ufw allow 5432 » and you’re done 😉
    Many thanks!

  • Jonathan Martell Nov 27, 2010 @ 22:46

    Great tip!

  • Joe Dec 9, 2010 @ 21:39

    Works like a charm! Thank you!

  • Jared Dec 29, 2010 @ 16:33


  • Marcio Wesley Borges Feb 14, 2011 @ 19:58

    Nice article.
    Note that the listen_addresses must be comma separated as:

  • Bandhalaraja Nov 24, 2011 @ 11:54

    i unable to access data base form client said computer.2 days before i can access data base in my client computer(Windows xp) but now not able , because i reinstall postgres in my data base server machine(SUSE Linux) . regularly i give host ip address in my client computer but now i give mean not access .
    Please tell me solution to access data base from server machine (Linux) to client computer (Win xp)


    • Arjunan Feb 22, 2012 @ 10:40

      Hi..Change the postgresql.conf. file as follow
      host all all samenet trust

      Restart the Services.

      • Arjunan Feb 22, 2012 @ 10:42

        Hi..Change the pg_hba.conf file as follow
        host all all samenet trust

        Restart the Services.

  • Dave Neathery Jan 9, 2012 @ 15:26

    I am trying to access postgresql from a C program running on Win XP. The database server is Linux and I can connect with it using SSH and psql but I have had not success connecting using PQconnectdb. I get the following error:
    libintl-8.dll was not found. I’m using Microsoft Visual C++ .NET 2003. That .dll is not on the computer and I don’t know how to get one.


  • parag May 24, 2012 @ 7:09

    Thanzx it really helped me …

  • hangover Jun 26, 2012 @ 8:25

    On step 3, in


    directive, IPs must be comma separated.

  • Foo Bar Aug 3, 2012 @ 9:48

    Just wanted to point out that please remember that the iptables-rule mentioned here is opening up your postgres-server to the whole world!!

    Please update it to acceppt connections from only known servers:

    iptables -A INPUT -s -m state –state NEW,ESTABLISHED -p tcp –dport 5432 -j ACCEPT

  • ingdestino Jul 15, 2013 @ 21:05

    Clear and useful explanation: thanks a lot!

  • JP Sep 25, 2014 @ 12:20

    Thanks , it worked , just remember to uncommnet listen_address

  • Valerie Oct 28, 2014 @ 19:03

    Thank you so much. You saved my sanity.

  • Kristian Kirilov Apr 27, 2015 @ 7:32

    Thanks a lot!!!

  • Sandor Apr 29, 2015 @ 12:56

    Thanks! You’ve helped me! Thanks!

  • Mihajlo Nov 5, 2015 @ 8:08

    Nice, thanks!
    Worked like a charm, also I didn’t have to mess with firewall.
    Only difference at mine setup is that all mentioned pg files were in “/usr/local/var/postgres/”

  • Vikrant Korde Jan 27, 2016 @ 10:43

    Really crisp steps. It solved my problem.
    – I didn’t change anything in /u01/AlfrescoDMS/alf_data/postgresql/pg_hba.conf
    – I changed file /u01/AlfrescoDMS/alf_data/postgresql/postgresql.conf
    – Added listen_addresses=’*’
    – In the conf file this line (tcpip_socket = true) was not present, so i didn;t add this line.

    It worked like a charm..
    Vikrant Korde.

  • Peeyush Jun 14, 2016 @ 7:48

    To allow connection with all the Hosts in `etc/postgresql/8.2/main/pg_hba.conf` add this line:-

    host all all md5

  • Sahil Aug 1, 2016 @ 7:34


  • Rizki Ardian Sep 14, 2016 @ 6:57

    Thank you so much !

Leave a Reply

Your email address will not be published.

Use HTML <pre>...</pre> for code samples. Still have questions? Post it on our forum