Carnifex

Documenting progress steps for future repeat:

Raspberry Pi

These steps were required to set up the raspberry pi

Install and configure postgresql

1. First, install postgresql as it does not come installed on the RPi by default.

sudo apt-get install postgresql

Currently, the version installed by apt is 9.1. During installation, the postgresql daemon should be started and running as a service.

1. After installation, configuration of the default database and users should be performed. Use the command "psql" from terminal to access the postgresql interface. By default, psql accepts connections by peer only, no password. So to connect, you must be a superuser with name "postgres". "template1" is the name of the administrator database that you will need to connect to. So, to make changes to the default setup, enter:

su -u postgres psql template1

1. This command should enter you into the psql terminal. Here, we want to add a password to the user "postgres". By doing this, we will make it so that we can log in without superuser privileges by using a password instead. The command to change the user is:

ALTER USER with encrypted password '<password here>';

If successful, the terminal should acknowledge with "ALTER ROLE". If you did not receive an acknowledgement, double check the terminal semicolon was entered on the commond. After altering the role, press CTRL+d to exit psql.

1. Next, we have to change the authentication rules for postgresql to expect passwords and not peer privilege. The file "pg_hb.conf" configures the authentication. Open the file for editing:

sudo nano /etc/postgres/9.1/main/pg_hb.conf"

This file contains information about access from different network locations. In this case, we are wanting to change to password authentication for access for the "postgres" user when accessing from localhost. The header of the file contains lots of useful information, but the first uncommented line should be:

local   all     postgres            peer

The "peer" option should be changed to "md5". The line should read:

local   all    postgres         md5

Save and close the file. At this point, the postgresql service needs to be restarted to reload the configuration. Use the command:

sudo /etc/init.d/postgresql restart

After the service has restarted, you can confirm that this change worked by entering the command:

psql -U postgres

You will be prompted for a password. This should be the password you entered using the "ALTER USER" command above. If this works, you should be in the psql terminal. Press CTRL+d to exit.

1. Next, we want to create a psql user with the same username as our user on the terminal. We can add a user with:

createuser -U postgres -d -e -E -l -P -r -s <username>

• The -U flag allows us to specify the username that will be logging into psl to create the user, not the username we are trying to create.
• The -d flag allows the user we create the permission to create databases.
• The -e flag will echo commands back to the terminal to provide feedback that this command worked.
• The -E flag will encrypt the password we create for this new user.
• The -l flag will allow the user to log on.
• The -P flag will make the createuser command give a password prompt to enter the password for the new user.
• The -r flag will allow the user to be able to create new roles.
• The -s flag will make the new user a superuser.
• <username> is where we specify the name of the user to be created. The default username for the RPi is "pi"

This command will prompt for the password for the new user. It will prompt again to make sure passwords match. If both passwords match, it will ask for the password for the user "postgres". If this command is successful, you should see an sql command echoed to the command line.

1. Once again, we need to change the authentication settings. Use:

sudo nano /etc/postgres/9.1/main/pg_hb.conf"

This time find the line that contains:

local     all   all            peer

Change the line so that peer reads md5:

local     all   all            md5

Save and close. Once again, restart the postgresql service:

sudo /etc/init.d/postgresql restart

After the service has restarted, you should be able to access the psql terminal with the command:

psql template1

No username is now required because your username in the terminal is now a known user. It will prompt you for the password you created using the createuser command above.

Create Database

• Now that we can log into psql, we can create a database to store information in. The first step that we need to do is create a tablespace, and to do that, we need to know where the data is stored. The tablespace is where data from our database is stored on the disk. We can find where pqsl is putting data by default by running the command from the psql terminal:

SELECT setting AS "Data Location" FROM pg_settings WHERE name = 'data_directory';

This will output the data directory location. In my example, the location is:

/var/lib/postgresql/9.1/main

To create a new tablespace, use the command:

CREATE TABLESPACE <db_name> OWNER <ownername> LOCATION '<absolute directory>';

For this project, we'll use the following specific command:

CREATE TABLESPACE data_db OWNER postgres LOCATION '/var/lib/postgresql/9.1/main';

• Now that the tablespace is created, we can create the database. We want to specify it to use the tablespace just created. We will specify a small connection limit to prevent errors causing too many connections.

CREATE DATABASE db_dht TABLESPACE data_db CONNECTION LIMIT 5 ENCODING 'UTF8';

Create Database Owner

By default, the owner of the newly created database is the user who created it. It is sometimes advantageous to add users who have limited authority to make limited changes to the database. We'll come back to this if it turns out to be important.

Create Database User

So far, our postgresql has two users. One is "postgresql" which is the default admin account. The second is the added superuser account "pi". We want to create a role and account that will be able to modify the newly created database, but have no other privileges. We'll come back to this later.

Create Table

Next, we will create a table to store the data in. We will name the table "temp" and give it defined columns for "Temperature", "Humidity" and "Date". Temperature and Humidity will be floating point numbers (type real). Date will be a date (type date).

CREATE TABLE data (
        Sequence            serial           PRIMARY KEY,
        Temperature        real             NOT NULL,
        Humidity              real             NOT NULL,
        Date                    timestamp  DEFAULT current_timestamp)
        TABLESPACE data_db;

This will alert us to the fact that we have implicitly created a sequence when we specified serial, and implicitly created an index when we declared that sequence our PRIMARY KEY.