Summary
=======

# su postgres
$ psql template1
{...}
template1=# create user rave;
CREATE ROLE
template1=# create database portdb with owner rave;
CREATE DATABASE
Template1=# grant all on database portdb to rave;
GRANT
template1=# \q
$ psql -U rave portdb
{ ... }
portdb=> \i /path/to/schema.sql
BEGIN
{ ... }
COMMIT
portdb=> \q
{ Do additional configuration as detailed below.... }


Introduction
============

The Analysis Portal uses a PostgreSQL database to store aggregate port
information.  This document describes how to set up this database.


Server Configuration
====================

The remaining instructions in this document assume you have an
instance of the PostgreSQL database server installed and running on
the RAVE server.  The correct version of the database server software
is available from
https://tools.netsa.cert.org/distrib/rhelas4/download.html.  For
documentation on installing and configuring PostgreSQL, please see
http://www.postgresql.org/docs/manuals.

If you're having problems running PostgreSQL, ensure that postgres.so
is in the dynamic library search path. One way to do this is to add
the directory that contains it to /etc/ld.so.conf.d and run ldconfig.

Loading the Database Schema
---------------------------

After PostgreSQL has been installed, connect using the psql command as
user postgres, as in the following example. 

------------------------------------------------------------------->8---
# su postgres
$ psql template1
{ ... standard output logging in to postgres.  Note that postgres is the
  database superuser ... }
template1=# create database portdb;
CREATE DATABASE
------------------------------------------------------------------->8---

We recommend that you create a user in PostgreSQL specifically for RAVE
services.  The user that runs the RAVE server is a good choice.  For
the rest of this document, we will assume the existence of such a
user, named rave.  

The database user name can be any user name on the system in the next
line.  It's also possible to define any new user name you wish, and to
use the password option to set a password.  We do not recommend this,
as RAVE (acting as a PostgreSQL client) will then need access to the
password, which means the password will have to be stored in plain
text in any case.  See the output of \h create user for details.

After you have created a user to administrate the portdb database, you
must load the schema of the database from the supplied schema.sql
file.  The following example describes the user creation and loading
process.

------------------------------------------------------------------->8---
template1=# create user rave;
CREATE ROLE
template1=# grant all on database portdb to rave;
GRANT
template1=# \q
{ You've now quit psql, and are going to reconnect. }
$ psql -U rave portdb
{ ... }
portdb=> \i /path/to/port-database-schema.sql
BEGIN
...
COMMIT
{ Note: There should be a bunch of stuff between BEGIN and COMMIT,
  including two "NOTICE"s.  But there should be no errors. }
portdb=> \q
$
------------------------------------------------------------------->8---


Database Connectivity
=====================

At this point, the port database schema has been loaded, but contains
no data.

If you changed any of the default names in the above examples, you
must edit the portal.conf file to set the database URL. The database
URL is specified in the "portdb" parameter. To set it, add a line to
your portal.conf file under [databases], of the following type:

------------------------------------------------------------------->8---
[databases]
portdb={type}://[{user}:{pass}@]{host}[:{port}]/{db-name}
------------------------------------------------------------------->8---

The default is "postgresql:///portdb".  That is: Use the current
system user, using no password, to log in to the portdb database on
the postgres instance running on the local machine. (The database will
always be running on the local machine.)

In order to check that the default URL will work, try to become the
rave user, then connect to the database:

------------------------------------------------------------------->8---
# su rave
$ psql portdb
...
------------------------------------------------------------------->8---

This command should succeed, indicating that all is well.


Performance Tuning
==================

In order to allow PostgreSQL to make proper query optimizations based
on the data partitioning scheme the portal uses, enable the
"constraint_exclusion" option.  To do this, edit the postgresql.conf
file underneath the PostgreSQL data directory.  Find the line:

------------------------------------------------------------------->8---
#constraint_exclusion = off
------------------------------------------------------------------->8---

underneath the QUERY TUNING section, and change it to:

------------------------------------------------------------------->8---
constraint_exclusion = on
------------------------------------------------------------------->8---

When constraint exclusion is used, PostgreSQL will no longer look at
partitions that are not relevant to the query at hand--when it is off,
it will look at these partitions even if they cannot possibly contain
any relevant data.  This parameter is set to off by default because it
has drawbacks in certain rare situations, but none of those situations
apply to this installation (and the performance benefits in a
partitioned table are significant.)