Installing PostgreSQL on Raspberry PI 2

Continuing with my web server project, it needs to be data driven.  For now, I am going to use PostgreSQL as my data store.  That may change, but for now it will suffice.

There are a number of things that we need to do before we can start using the database.  I used the PostgreSQL manual to configure it.

Installation

Let’s update our repository and then install PostgreSQL.

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

This install will create a new use postgres that does not have a password.  You can test if you can connect to the server by logging in as the postgres user and then running psql.

> sudo -i -u postgres
> psql

To exit out of the psql program you need to enter \q.

Authentication

Now that we can connect with the default user, we need to start configuring the access controls.  The access control is setup in the pg_hba.conf file.  It lists the hosts, users and database that users can connect to and from which host.  It should be in the default install directory /etc/postgresql/9.1/main.  To setup an example, lets allow access to the default Raspberry PI user: pi.  Open the file for editing with root permissions.

> sudo nano /etc/postgresql/9.1/main/pg_hba.conf

Add the following line to the end of the file:

local all pi password

What we are telling postgres to allow is local access to the user pi for all databases using password as the authentication method.  We still don’t have login rights to the database server yet.  Let’s setup the pi user with credentials, login rights and superuser permissions. These are for sake of the example. In a production environment, you would have more roles that have more specific privileges.

> create role pi password 'test';
> alter role pi login;
> alter role pi superuser;
> \du;

The last command \du; will list the roles and their permissions.  You should now see the pi user with superuser privileges.  If you omit the login assignment, you will see a message stating that the user does not have login rights.  It will not appear once you have granted the user login rights.

One final step before we can connect as the pi user.  You will need to have a database that it can connect to, so lets create a test database.

> sudo -i -u postgres
> psql
> create database test_database;
> \q;
> exit
>
> psql -d test_database

If all went well, you should see a new prompt: test_database=#

Now you are ready to start using postgres locally.  In another post I will show how to permit access to the server from a remote machine.