Setting up the database

Creating the database

The database definition script (the database.sql file that can be downloaded with the distribution) should be used to create the tables used by this system. If you task your PostgreSQL database administrator with the following instructions, you won't go far wrong:

  1. Create a database user with 'createdb' privileges. To do this, your system administrator must log into the postgres account at the command line shell and then use the createuser program as follows, entering the chosen password when prompted:

    #> su postgres
    %> createuser -A -d -E -P chosen_username

    The default username and password used with this distribution are "helpdesk" and "helpdesk" but you should choose your own username/password for security reasons. If so then make sure that you change that username and password in the DB connection script (found in system/opendb.php) before continuing with the installation.

  2. Using the newly created database account, you must create a new database called helpdesk, entering the password chosen above when prompted:

    %> createdb -U chosen_username -W helpdesk

    As above, you can use a different database name if you wish, but if you do you will have to change the DB connection script to reflect this (and in the next step you will have to replace "helpdesk" with your chosen database name).

  3. Execute the sql script for the appropriate release to create all the system's tables using the same account as above. This will enter some initial values for some tables and set up integrity constraints. Make sure you specify the full path of the database.sql script if it is not in the current working directory.

    %> psql -U chosen_username -W helpdesk -f database.sql

    As the database definition file includes drop table statements, you will get a list of errors explaining that the tables and views do not exist. This is expected. Any other errors should be classed as fatal and as such the database will not be in the correct form.

A note on security

It is important to note that with PostgreSQL, the default security settings are not at all secure. If you do not have the experience of a database administrator at your disposal you will have no guarantee that the data in your database is secure. For those who do not have the liberty that a DBA affords, then you can always follow my example and hope for the best. No guarantees!

In the postgresql.conf that can be found in the root of your database cluster file system (usually at /var/lib/pgsql/data), make sure that the following two lines are present (and come before all others).

local          all            postgres       trust
local          helpdesk       all            password

The first ensures that the standard user (in this case "postgres") has full access to the database. Without this first, your database might not correctly start at boot time. The second grants your database scripts (assuming you have called the database "helpdesk") access to your database and nothing else. It also ensures authentication.

Of course, the above came from a system that had no other databases present in the cluster, and so caused no problems. Always think twice before setting this yourself.