46

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?

  • 1
    Follow steps mentioned in javabypatel.blogspot.in/2015/07/… and change the port number present in postgresql.conf file. after changing port restart PostgreSQL server. – Jayesh Aug 11 '15 at 3:49
  • Url posted by @Jayesh did the trick. Followed instructions and succesfully made one of my development computers connect to another (from Windows with pgAdmin4 to Ubuntu 18.04 postgresql 10.9) – EAmez Jul 15 at 10:46
78

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.

  • 4
    In particular, you should enable SSL. – Craig Ringer Feb 19 '14 at 14:38
  • Okey, I tried this, but when I try to connect using pgAdmin from my computer, I get "Server not listening". I added to iptables, and when I run iptables -L the following shows: ACCEPT tcp -- anywhere anywhere tcp dpt:postgresql When checking the IP and PORT on this site (yougetsignal.com/tools/open-ports), it says the port is closed – Øyvind Feb 19 '14 at 14:46
  • 2
    is the server listening? check with netstat -nlt|grep :5432 – user224465 Feb 19 '14 at 14:50
  • 1
    I would insert the host row in a more strict way: host <database> <user> <remote_client_IPaddress>/24 md5 – gc5 Feb 25 '15 at 15:58
  • For Postgresql version 9.5 you may need to restart the server before the listen_addresses will take effect. – Heather92065 Nov 7 '16 at 19:00
26

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

  • 4
    This definitely did the trick. The above answer definitely did not work. – Mike Aug 6 '17 at 18:43
  • Please @Mike express what is correct: host all all all md5 will work fine? It is correct? any security problem? – Peter Krauss May 7 '18 at 22:48
  • @peterkrauss Yes, host all all all md5 worked for me. Security problem? Of course it is, but for what I was doing it was just fine. (Internal network) – Mike May 14 '18 at 21:57
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).

0

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 user@your-server.com
#or
ssh -L 5432:127.0.0.1:5432 user@your-server.com

(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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.