Remotely access postgresql database

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.

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

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).