Remote connection to a Postgres database running on a Eucalyptus instance.

This post describes how to setup a remote connection to a Postgres database running on a Eucalyptus instance.

All the steps covered in the post are generic and mostly independent from each other:

·       creating the Eucalyptus security group,

·       creating an instance from an image (CentOS 6.4),

·       installing the Postgres database,

·       creating a PostGIS extension,

·       setting up a remote connection to the Postgres server

·       connecting from a remote client (terminal or QGIS),

so feel free to read only those steps that are relevant to your own setup.

Step # 1: Create a security group:

euca-create-group  rmtpg -d remote_postgres_open_5432_22

euca-authorize rmtpg -p 22 -s 0.0.0.0/0 -P tcp

euca-authorize rmtpg -p 5432 -s 0.0.0.0/0 -P tcp

Step # 2: Create an instance:

euca-run-instances -n 1 -t m1.xlarge -k your.key -g rmtpg emi-1234ABC

Step # 3: SSH to your instance:

ssh -i your.key root_or_ec2-user@euca-123-123-123-123.eucalyptus.edu

Step # 4: Install Postgres

vi /etc/yum.repos.d/CentOS-Base.repo

     For more information : https://wiki.postgresql.org/wiki/YUM_Installation

Add the below line for [base] and [updates] sections:

exclude=postgresql*

vi /etc/yum.repos.d/CentOS-Base.repo ; add the line

yum localinstall http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-1.noarch.rpm

yum install -y postgresql94-server

yum install  postgresql94-contrib

Step # 5: Install PostGIS (optional)

yum install postgis2_94

Step # 6: Initialize the database

service postgresql-9.4 initdb

Step # 7: Configure remote access

vi /var/lib/pgsql/9.4/data/pg_hba.conf 

edit file /var/lib/pgsql/data/pg_hba.conf and change authentication method from ident to trust .

Step # 8: Start the postgres

service postgresql-9.4 start

Step # 9: Login

su - postgres

Step # 10: Create a new Database

createdb yourdatabasename

Step # 11: Create a user

createuser youruser

Step # 12: Grant permissions to a user

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public to youruser;

Step # 13: Create PostGIS extension to your database

yourdatabasename=# create extension postgis;

(from terminal: su - postgres; psql; \c yourdatabasename; create extension postgis;)

Step # 14: Verify that the extension is there

\d             (you will see the spatial_ref_sys table name)

Step # 15: Enable client authentication for the specific IP

vi /var/lib/pgsql/9.4/data/pg_hba.conf 

append the line below with the network address of the machine from which you want to connect / with your QGIS client:

host all all 123.123.123.123/24 trust

Step # 16: Configure listen addresses

vi /var/lib/pgsql/9.4/data/postgresql.conf 

listen_addresses='*'

Step # 17: Restart PostgreSQL Server

service postgresql-9.4 restart

Step # 18: Update the iptables

iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d 123.123.123.123 --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT

     Postgres is using port 5432, you need to open it.

Step # 20: Restart firewall

 /etc/init.d/iptables restart

Step # 21: Test your setup from the terminal

psql –h euca-123-123-123-123.eucalyptus.edu -U youruser -d yourdatabasename

Step # 22: From the QGIS client, set the following:

Open the QGIS browser, Select PostGIS, New, 

postgis port="5432" username=“youruser” password=“yourpassword" host="euca-123-123-123-123.eucalyptus.edu" database=“yourdatabasename" name=“connectionname"

Connect

Notes regarding the remote Postgres connection are in part related to this post:

http://www.cyberciti.biz/tips/postgres-allow-remote-access-tcp-connection.html

Related topics:
Eucalyptus: http://en.wikipedia.org/wiki/Eucalyptus_(software) and https://www.eucalyptus.com
PostGIS: http://postgis.net/
QGIS: http://www.qgis.org/en/site/
Postgres: http://www.postgresql.org/
CentOS: https://www.centos.org/