Howto install Postgresql and PostGIS on Ubuntu Trusty 14.04 with secure tunnel for connections

By Jon Saints - 13 Aug 2014

Install Ubuntu 14.04

Start with a fresh install of Ubuntu 14.04 Trusty. (AWS or vagrant might be a good way.)

Install Postgresql

On the server run the commands below.

sudo apt-get update
sudo apt-get install -y postgresql postgresql-contrib

Create a database and a user to access it

I prefer to create one user per database for security.

Replace DATABASE_NAME_HERE and USER_NAME_HERE with the values that you want to use

# this will prompt you for a database password
sudo -u postgres createuser -P USER_NAME_HERE
sudo -u postgres createdb -O USER_NAME_HERE DATABASE_NAME_HERE

Test connecting to Postgresql


Postgresql will ask you for your password. Then you should see:

psql (9.3.5)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.


To exit type:


Optional: Add PostGIS support to the database

sudo apt-get install -y postgis postgresql-9.3-postgis-2.1
sudo -u postgres psql -c "CREATE EXTENSION postgis; CREATE EXTENSION postgis_topology;" DATABASE_NAME_HERE

Connect to the database securely from PgAdmin3

Now, on your desktop computer install pgAdmin3 and connect securely to the database via an SSH tunnel.

First, open a terminal on your Desktop computer (these instructions are for Mac or Linux)

Open a tunnel using an ssh password or PEM file. You will need to know the username you use to SSH to the server and the server address or ip address.


If you are using a PEM file, then you can add -i option to the SSH command


This will open a secure tunnel between your desktop and the database server. To exit the tunnel, just press Cntrl+C.

Next, open pgAdmin3 and create a new connection using these settings:

Name: (describe what this connection is for here)
Port: 5433

When you are done using the database close PgAdmin3 and in the terminal where the tunnel is running press Cntrl+c to exit the tunnel.

Discussion & Comments