PostGIS is here!

We now have a PostgreSQL/PostGIS server! 

I am setting this up as a development environment for web applications on behalf of Richard Kingston.

PostgreSQL/PostGIS has a massive learning curve, so I expect to learn a lot in the process. (For now I am quite happy that it is only for development, not as a production environment.) The following is a quick documentation of what I did to set up the PostgreSQL/PostGIS server.

Step 1: Install PostgreSQL/PostGIS

I installed PostgreSQL 8.3 and PostGIS 1.5.3 on Ubuntu Server 9.10, and it couldn’t be easier – just type at the command prompt:

sudo apt-get install postgresql-8.3-postgis postgis php5-pgsql

Done!

We will need a text editor, and I can’t be bothered with vi, so I install Midnight Commander (mc):

sudo apt-get install mc

Oh, and I install pgAdmin on my Desktop PC, plus the Shapefile loader for PostGIS. This will make working with databases and loading spatial data a lot easier later on.

Step 2: Change password for user postgres

The PostgreSQL installation automatically created two user acconts: a Linux user named ‘postgres’, and a PostgreSQL database user (in PostgreSQL speak: a role) also named ‘postgres’. Both are created with blank passwords. Of course we change the passwords.

First we change the password for the database user postgres on the database template1:

sudo -u postgres psql template1
ALTER USER postgres with encrypted password '<newpassword>';
\q

Then we change the password for the Linux user postgres:

sudo passwd -d postgres
sudo su postgres -c passwd

Step 3: Configure PostgreSQL

We need to change some configuration settings for the PostgreSQL server. All the following settings are in /etc/postgresql/8.3/main/postresql.conf, so we open it for editing:

cd /etc/postgresql/8.3/main
sudo cp postresql.conf postresql.conf.original
sudo mc -e postresql.conf

By default PostgreSQL only listens for local connection requests (127.0.0.1). If we want to connect from clients on other machines, e.g. pgAdmin on my desktop PC, we need to listen for connections on other network adapters:

listen_addresses = '*'

For security we set it to encrypt passwords:

password_encryption = on

Exit Midnight Commander, then restart the PostgreSQL server:

sudo /etc/init.d/postgresql-8.3 restart

Step 4: Set access rights

By default PostgreSQL only allows access from the local machine.  I want to allow access to the databases from remote clients like pgAdmin on user’s PCs. However, this should be only through SSL and from PCs within the university network.

We need to edit access settings in /etc/postgresql/8.3/main/pg_hba.conf, so open it for editing:

cd /etc/postgresql/8.3/main
sudo cp pg_hba.conf pg_hba.conf.original
sudo mc -e pg_hba.conf

At the end of the file we add a line specifying that SSL access is allowed from within the university subnet.

# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
hostssl   all      all  150.67.0.0/16  md5

Now I should be able to access the PostgreSQL server from pgAdmin on my Desktop PC … just a second … Yep, works!

Step 5: Make a database template

Every time you create a database in PostgreSQL you will have to specify a number of parameters (data types, spatial reference systems, etc). To facilitate database creation, we can define a database template, and later on we just base new databases on the template. To create a template for geospatial data we do:

Switch user to postgres:

sudo su postgres

Create a new template database and load the PostGIS settings into it:

createdb postgistemplate
createlang plpgsql postgistemplate
psql -d postgistemplate -f /usr/share/postgresql-8.3-postgis/lwpostgis.sql
psql -d postgistemplate -f /usr/share/postgresql-8.3-postgis/spatial_ref_sys.sql

Test the template:

psql -d postgistemplate -c ""SELECT postgis_full_version();""

Exit user postgres:

exit

Now we have a template ‘postgistemplate’ which contains everything a geospatial database needs. We will base future geospatial databases on this template.

Step 6: Set up a multi user PostgreSQL environment

Until now we only have the default user account ‘postgres’ to access the database server, but I want all of our developers to have an individual account, so they can all use the server, but not mess with each others databases. Here the PostGreSQL concept of roles (users and groups) comes in handy.

First I create a PostgreSQL group role for staff. I can accually do this from my Desktop PC:

  1. Using pgAdmin, connect to the server as user postgres
  2. Add group role ‘staff’. Privileges: Inherit, Create objects, Create roles

Create PostgreSQL role for each member of staff:

  1. Using pgAdmin, connect to the server as user postgres
  2. Add a new login role <some_user_name>. Privileges: Inherit, Create objects, Create roles. Role Membership: staff

Problems and solutions

When a user logs on using pgAdmin and tries to create a database based on postgistemplate they get: “ERROR: permission denied to copy postgistemplate”.

I thought this was caused by insufficient access rights to the database postgistemplate, tables geometry_columns and  spatial_ref_sys. So I use pgAdmin to set for both tables: Privileges > Add group staff, ALL. However this does not resolve the problem!

Fortunately Nomad Labs’ instructions on creating a PostGIS template help: the database used as a template by non-admin users must be explicitly designated as a template. This is set in the system database ‘postgres’, catalog ‘PostgreSQL(pg_catalog)’, table ‘pg_database’. Here all databases are listed. I go to the database ‘postgistemplate’ and set ‘datistemplate’ = TRUE – And voila, it works!

Advertisements

3 thoughts on “PostGIS is here!

  1. I’m curious as to why you installed 8.3, when 8.4 was first released in July 2009? Now that 9 is out… maybe time to upgrade?

    1. Hi, thanks for the comment. You are right, I could have used the newer version of PostgreSQL. However, one requirement was to keep the system setup as simple and quick as possible, rather than go for the latest version. I knew 8.3 would work fine for our purpose and it was easy to install, so I did go for this version. I might upgrade if any functional requirements or security issues come up, but so far we are happy with what we have.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s