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?
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It only takes a minute to sign up.Sign up to join this community
To open the port 5432 edit your
/etc/postgresql/9.1/main/postgresql.conf and change
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
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
For the message "server not listening", that happen to me was, that i don't erase of # on the archive postgresql.conf i mean:
(Sorry for my english).
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 -L local_port:localhost:foreign_port user@server
Start the port forwarding:
ssh -L 5432:localhost:5432 firstname.lastname@example.org #or ssh -L 5432:127.0.0.1:5432 email@example.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