Home / ubuntu / Questions / 423165
Accepted
Øyvind
Asked: 2014-02-20 06:14:49 +0800 CST

Remotely access postgresql database

  • 772

I need to access a postgresql database from a remote machine on a VPS at DigitalOcean running 12.10 and postgresql 9.1.

How do I do this? I noticed port 5432 is closed, how do I open this?

remote-access

6 Answers

  1. Best Answer

    To open the port 5432 edit your /etc/postgresql/9.1/main/postgresql.conf and change

    listen_addresses='localhost'

    to

    listen_addresses='*'

    and restart your DBMS

    invoke-rc.d postgresql restart

    now you can connect with

    $ psql -h hostname -U username -d database

    if you are unable to authentify yourself, then you need to give your user access rights to your database

    Edit your

    /etc/postgresql/9.1/main/pg_hba.conf

    and add

    host all all all md5

    (This is for a wide open access. For stricter control, consult the pg_hba.conf documentation and adjust according to your needs).

    Hereafter you need also a reload

    invoke-rc.d postgresql reload

    I don't need to mention that this is a basic configuration, now you should think about modify your firewall and improve the security of your DBMS.

    • 98

  2. This does not work anymore, if it ever did :

    host all all * md5

    The correct possible lines for this are :

    host all all 0.0.0.0/0 md5 #ipv4 range

    host all all ::0/0 md5 #ipv6 range

    host all all all md5 #all ip

    Source

    • 30

  3. For the message "server not listening", that happen to me was, that i don't erase of # on the archive postgresql.conf i mean:

    #listen_addresses='localhost'

    to:

    listen_addresses='*'

    (Sorry for my english).

    • 4

  4. The highest-voted and accepted answer has serious security-impolications. This method is disabled by default for good reasons.

    Better use local port forwarding with ssh:

    ssh -L local_port:localhost:foreign_port user@server

    Start the port forwarding:

    ssh -L 5432:localhost:5432 [email protected]
#or
ssh -L 5432:127.0.0.1:5432 [email protected]

    (Change local and foreign ports to fit your configuration).

    Then you can directly connect to the database from your local computer:

    psql -U db_user -p local_port -l
    • 4

  5. Following configuration, you need to set:

    In /etc/postgresql/10/main/postgresql.conf

    # Connection Settings -

listen_addresses = '*'          # what IP address(es) to listen on;

    In /etc/postgresql/10/main/pg_hba.conf

    # IPv4 local connections:
host    all             all             0.0.0.0/0           md5

    Restart your server:

    sudo /etc/init.d/postgresql restart
    • 3

  6. To open your port 5432, you need to run this command

    sudo ufw allow 5432/tcp
    • 0