7

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?

6

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.

  • 1
    The only thing I would add to this answer is to enable SSL for the PostgreSQL connection, especially if you're not using a VPN connection. In QGIS you can set the connection to Postgres to require SSL, and you can enforce that only SSL connections are allowed in the pg_hba.conf file using a line such as: hostssl all all all md5 – RustProof Labs Dec 23 '15 at 14:23
3

Short awnser:

  1. you need to use dynamic DNS on your home router. I use DtDNS because they offer five free hostnames.
  2. you need to forward 5432 port on your router to your local debian server.

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.