This document uses Ubuntu 18.10 in conjunction with PostgreSQL 10 with PostGIS. Similar concepts should apply to other installations. It assumes PostgreSQL has been installed with the PostGIS extensions already.
Initializing the database to run on server startup
Either open the terminal in your Linux server or open an SSH session to your server and log in using an appropriate username and run the following command:
sudo update-rc.d postgresql enable
Enabling remote connections to the PostgreSQL cluster
Using the Nano text editor, make changes to the postgresql.conf file by typing:
sudo nano /etc/postgresql/10/main/postgresql.conf
Scroll down to the Connection Settings part of the file using your keyboard and add:
listen_addresses = ‘*’
Use CNTR-X to exit, save when prompted.
Next, use nano to change the pg_hba.conf file. This file allows access to the database cluster for users with encrypted passwords.
sudo nano /etc/postgresql/10/main/pg_hba.conf
Change or add all the red text below
# TYPE DATABASE USER ADDRESS METHOD
# “local” is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 0.0.0.0/0
md5
# IPv6 local connections:
host all all ::0/0 md5
# All IPs
host all all all md5
Again, exit and save.
Restart the PostgreSQL database cluster:
sudo systemctl restart postgresql
Configuring for an internal network
In most business situations where all employees are under one roof or use a VPN to connect into a business network it is possible to connect directly to the GIS server using its local IP address.
By default, PostgreSQL listens to requests on port 5432. All that is required to connect to a PostGIS database on a network then is the following:
- IP address of the physical or virtual server
- Name of the Database
- Login credentials
Configuring for an externally facing server
Servers that need to be accessible over the internet for remote workers or workers not able to connect in via a VPN connection need to be routed accordingly by your network hardware. This means that all requests to port 5432 are routed to the PostGIS server. If more than one server is on the local network, it will be necessary to specify a different port than default for PostgreSQL.
After that is completed, it will be necessary to also ensure that your network has a static IP address or you are using a DNS forwarding service. This is beyond the scope of this document.