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.