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