Carnifex - Revision history

Laz: Laz moved page Skunkworks 1 to Carnifex: Project Name Decided.

2015-01-06T13:42:34Z

Laz moved page Skunkworks 1 to Carnifex: Project Name Decided.

← Older revision		Revision as of 13:42, 6 January 2015
(No difference)

Laz at 20:13, 28 December 2014

2014-12-28T20:13:24Z

← Older revision		Revision as of 20:13, 28 December 2014
Line 87:		Line 87:
	TABLESPACE data_db;</nowiki>		TABLESPACE data_db;</nowiki>

	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.		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.

			At this point (hopefully), I have a table all set up and ready to accept data. Now I have to figure out first how to push data into it, then pull data out and display it on my page hosted on my RPi.

Laz: /* Raspberry Pi */

2014-12-28T20:10:09Z

Raspberry Pi

← Older revision		Revision as of 20:10, 28 December 2014
Line 57:		Line 57:
	<nowiki>psql template1</nowiki>		<nowiki>psql template1</nowiki>
	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.		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:
			<nowiki>SELECT setting AS "Data Location" FROM pg_settings WHERE name = 'data_directory';</nowiki>
			This will output the data directory location. In my example, the location is:
			<nowiki>/var/lib/postgresql/9.1/main</nowiki>
			To create a new tablespace, use the command:
			<nowiki>CREATE TABLESPACE <db_name> OWNER <ownername> LOCATION '<absolute directory>';</nowiki>
			For this project, we'll use the following specific command:
			<nowiki>CREATE TABLESPACE data_db OWNER postgres LOCATION '/var/lib/postgresql/9.1/main';</nowiki>

			*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.
			<nowiki>CREATE DATABASE db_dht TABLESPACE data_db CONNECTION LIMIT 5 ENCODING 'UTF8';</nowiki>

			===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).
			<nowiki>CREATE TABLE data (
			Sequence serial PRIMARY KEY,
			Temperature real NOT NULL,
			Humidity real NOT NULL,
			Date timestamp DEFAULT current_timestamp)
			TABLESPACE data_db;</nowiki>

			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.

Laz: /* Raspberry Pi */

2014-12-28T18:14:12Z

Raspberry Pi

← Older revision		Revision as of 18:14, 28 December 2014
Line 3:		Line 3:
	==Raspberry Pi==		==Raspberry Pi==
	These steps were required to set up the raspberry pi		These steps were required to set up the raspberry pi
	===Install postgresql===		===Install and configure postgresql===
	*First, install postgresql as it does not come installed on the RPi by default.		#First, install postgresql as it does not come installed on the RPi by default.
	<nowiki>sudo apt-get install postgresql</nowiki>		<nowiki>sudo apt-get install postgresql</nowiki>

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

	*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:		#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:
	<nowiki>su -u postgres psql template1</nowiki>		<nowiki>su -u postgres psql template1</nowiki>

	*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:		#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:

	<nowiki>ALTER USER with encrypted password '<password here>';</nowiki>		<nowiki>ALTER USER with encrypted password '<password here>';</nowiki>
Line 18:		Line 18:
	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.		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.

	*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:		#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:
	<nowiki>sudo nano /etc/postgres/9.1/main/pg_hb.conf"</nowiki>		<nowiki>sudo nano /etc/postgres/9.1/main/pg_hb.conf"</nowiki>

	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:		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:
	<nowiki>local postgres peer</nowiki>		<nowiki>local all postgres peer</nowiki>
	The "peer" option should be changed to "md5". The line should read:		The "peer" option should be changed to "md5". The line should read:
	<nowiki>local postgres md5</nowiki>		<nowiki>local all postgres md5</nowiki>
	Save and close the file. At this point, the postgresql service needs to be restarted to reload the configuration. Use the command:		Save and close the file. At this point, the postgresql service needs to be restarted to reload the configuration. Use the command:
	<nowiki>sudo /etc/init.d/postgresql restart</nowiki>.		<nowiki>sudo /etc/init.d/postgresql restart</nowiki>

	After the service has restarted, you can confirm that this change worked by entering the command:		After the service has restarted, you can confirm that this change worked by entering the command:
Line 33:		Line 33:
	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.		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.

	*~~next~~ create user		#Next, we want to create a psql user with the same username as our user on the terminal. We can add a user with:
			<nowiki>createuser -U postgres -d -e -E -l -P -r -s <username></nowiki>
			*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.

			#Once again, we need to change the authentication settings. Use:
			<nowiki>sudo nano /etc/postgres/9.1/main/pg_hb.conf"</nowiki>
			This time find the line that contains:
			<nowiki>local all all peer</nowiki>
			Change the line so that peer reads md5:
			<nowiki>local all all md5</nowiki>
			Save and close. Once again, restart the postgresql service:
			<nowiki>sudo /etc/init.d/postgresql restart</nowiki>
			After the service has restarted, you should be able to access the psql terminal with the command:
			<nowiki>psql template1</nowiki>
			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.

Laz: Created page with "Documenting progress steps for future repeat: ==Raspberry Pi== These steps were required to set up the raspberry pi ===Install postgresql=== *First, install postgresql as it..."

2014-12-28T17:44:15Z

Created page with "Documenting progress steps for future repeat: ==Raspberry Pi== These steps were required to set up the raspberry pi ===Install postgresql=== *First, install postgresql as it..."

New page

Documenting progress steps for future repeat:

==Raspberry Pi==
These steps were required to set up the raspberry pi
===Install postgresql===
*First, install postgresql as it does not come installed on the RPi by default.
<nowiki>sudo apt-get install postgresql</nowiki>

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

*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:
<nowiki>su -u postgres psql template1</nowiki>

*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:

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

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.

*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:
<nowiki>sudo nano /etc/postgres/9.1/main/pg_hb.conf"</nowiki>

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:
<nowiki>local postgres peer</nowiki>
The "peer" option should be changed to "md5". The line should read:
<nowiki>local postgres md5</nowiki>
Save and close the file. At this point, the postgresql service needs to be restarted to reload the configuration. Use the command:
<nowiki>sudo /etc/init.d/postgresql restart</nowiki>.

After the service has restarted, you can confirm that this change worked by entering the command:
<nowiki>psql -U postgres</nowiki>

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.

*next create user