[GIS] Remote connection to PostGIS

ms accesspostgispostgresql

I have recently setup PostgreSQL and PostGIS on a debian server on a local network in my home, which is working perfectly. I am able to access the db from other machines on the same network using the postgis connection in QGIS, which all works very well.

I would also like to access the same machine and postgres/postGIS database remotely from my office by making a connection through QGIS. I have been struggling to understand the steps I need to go through to setup this kind of remote access.

Do I need a static IP for the home server?
Can I use dynamic DNS?
Do I need to setup openVPN? or similar?
I think I also need to forward ports on my router??

All the tutorials and guides I have found only really talk about the remote server being on the same network and not doing this over the internet. Any advice to pointing me towards some straight forward guides?

Best Answer

Postgres configuration

By default the Postgres server is only accessible from the same computer it runs on.

You need to setup the Postgres server to listen not just locally, but on your internet facing network card too. This is done in the postgresql.conf configuration file.

You will also need to enable user authentication from the outside, which is done in the pg_hba.conf file. This can be done differently for individual users and databases, i.e. you can allow password-less access from your home computer but require passwords from everywhere else.

Take a look at this post over at the Askubuntu Stackexchange which explains the necessary steps.

Home network

You don't need a VPN or static IP. A VPN to your home improves security since you don't need to open up your Postgres installation to everyone, and it offers strong encryption.

If you don't use a VPN you will need to open the Postgres port 5432 in your home router and make it point at your home server. How this is done depends on your particular router.

A static IP isn't required. There are services that offer "dynamic DNS" which means that your home server will always be reachable at a domain name similar to somename.dyndnsprovider.org. Or, if your home IP doesn't change a lot (mostly for cable internet users), you write your IP down somewhere.

Related Question