====== PostgreSQL Relational Databases ======

----


The PostgreSQLExternal Link relational database system is a fast, scalable, and stadards-compliant open source database platform. This guide will help you install and configure PostgreSQL on Fedora 14. We assume you've followed the steps detailed in our getting started guide, and that you're logged into your Linode VPS as root via SSH.

These instructions work with the Linode platform. If you don't have a Linode yet, sign up for a Linux VPS and get started today.

===== System Configuration =====

----

Make sure your ''/etc/hosts'' file has proper entries, similar to the ones shown below. Replace "12.34.56.78" with your Linode's public address, "servername" with your short hostname, and "mydomain.com" with your system's domain name.

File: ''/etc/hosts''

<code bash>
127.0.0.1    localhost.localdomain    localhost
12.34.56.78  servername.mydomain.com  servername
</code>
Set your system's hostname by issuing the following commands. Replace "servername" with your system's short hostname.

<code bash>
echo "HOSTNAME=servername" >> /etc/sysconfig/network
hostname "servername"
</code>

===== Install PostgreSQL =====

----

Make sure your system is up to date by issuing the following command:

<code bash>
yum update
</code>

Issue the following command to install PostgreSQL and required dependencies:

<code bash>
yum install postgresql postgresql-server
</code>
The current version of the database server will be installed, along with several supporting packages. Start the database server with the following commands:

<code bash>
chkconfig postgresql on
/etc/init.d/postgresql initdb
service postgresql start
</code>

===== Configure PostgreSQL =====

----

Set the Postgresql Password
Set a password for the "postgres" user by issuing the following command (be sure to subtitute your postgres password for "CHANGME" below):

<code bash>
passwd postgres
su - postgres
psql -d template1 -c "ALTER USER postgres WITH PASSWORD 'CHANGEME';"
</code>
You should pick a password consisting of numbers, letters, and non-alphanumeric characters. As with other account passwords, it should be a minimum of eight characters in length.

===== Create a Database =====

----

Create a database and connect to it with psql by issuing the following commands:

<code bash>
createdb mytestdb
psql mytestdb
</code>

You should see output similar to the following:

<code bash>
-bash-4.1$ psql mytestdb
psql (8.4.5)
Type "help" for help.

mytestdb=#
</code>

This is the PostgreSQL client shell; you may use it to issue SQL statements. To see a list of available commands, use the following command in the shell:

<code bash>
\h
</code>
To get help on a specific command enter it after \h, as shown below for the "SELECT" command:

<code bash>
\h SELECT
</code>
===== Create a Database Table =====

----

To create a table in your test database called "employees", issue the following command:

<code bash>
CREATE TABLE employees (employee_id int, first_name varchar, last_name varchar);
</code>
To insert a record into the table, you would issue a statement like this:

<code bash>
INSERT INTO employees VALUES (1, 'Jack', 'Sprat');
</code>
To see the contents of the "employees" table, you would issue a SELECT statement similar to the following:

<code bash>
SELECT * FROM employees;
</code>
This would produce output similar to the following:

<code bash>
mytestdb=# SELECT * FROM employees;
 employee_id | first_name | last_name
-------------+------------+-----------
           1 | Jack       | Sprat
(1 row)
To exit the psql shell, issue this command:

\q
</code>
===== Create PostgreSQL Users (Roles) =====

----

PostgreSQL refers to users as "roles", which may have different privileges on your databases. If a user is classified as a "superuser" it will have administrative access to the database system. To add a new user to PostgreSQL, issue the following command as the "postgres" user:

<code bash>
createuser alison --pwprompt
</code>
You will be asked to specify several values for the new user. To delete this user, issue the following command:

<code bash>
dropuser alison
</code>
By default, PostgreSQL uses ident authentication. This means database connections will be granted to local system users that own or have privileges on the database being connected to. Such authentication is useful in cases where a particular system user will be running a program (local scripts, CGI/FastCGI processes owned by separate users, etc). However, you may wish to change this behavior to require passwords. To do so, edit the file ''/var/lib/pgsql/data/pg_hba.conf'' as ''root'' or the ''postgres'' user. Find the following line:

File except: ''/var/lib/pgsql/data/pg_hba.conf''

<code bash>
local   all   all   ident
</code>
Change it to the following to use password authentication:
\\ File excerpt: ''/var/lib/pgsql/data/pg_hba.conf''
<code bash>
local   all   all   md5
</code>
As root, restart the Postgresql service:
<code bash>
service postgresql restart
</code>
Resume these instructions as the postgres user:
<code bash>
su - postgres
</code>
To grant all privileges on the table "employees" to a user named "alison", issue the following commands:
<code bash>
psql mytestdb

GRANT ALL ON employees TO alison;
\q
</code>
To use the database "mytestdb" as "alison", issue the following command:
<code bash>
psql -U alison -W mytestdb
</code>
You will be prompted to enter the password for the "alison" user and given psql shell access to the database.