http:// / scripts / rules / postgresql.shtml

Setting up a PostgreSQL database

This page documents how to set up a PostgreSQL database for use with the qmail rules interface.

Installing PostgreSQL

PostgreSQL is usually included in the operating system's repositories. If this is the case, you should install it from there, so that the server can be upgraded easily when security issues are found.

For example, under the RedHat-flavoured distributions (i.e. RedHat Enterprise, Fedora, CentOS, etc.) you can run a command like this to install the server:

# yum install postgresql-server

The specific package name may vary (I remember seeing a package called "pgsql-server" somewhere in the past) but the procedure should be the same.

If your system doesn't have a package repository from which to install the software, you can always install it from the source code.

After installing the software, make sure the server is running, and that your system is configured to automatically start the server when the machine boots up.

Configuring PostgreSQL security

We need to configure PostgreSQL to require passwords in order to access the database. To do this, we need to edit the /var/lib/pgsql/data/pg_hba.conf file. The lines below should be added BEFORE any "wildcard" lines (i.e. those lines which specify "all" as the database or user name.) This will configure things so that the postgres user can access the database without a password, but all other users will need to supply a password, and then will only have the permissions specified by GRANT commands.

# TYPE DATABASE USER CIDR-ADDRESS METHOD local rules postgres ident local rules all md5 host rules all md5

After editing the file, you will need to tell the PostgreSQL server to reload the security information:

# su - postgres
$ pg_ctl reload
$ exit

Creating the database and users

You should already have downloaded the files and run the ./ script.

To set up the database using PostgreSQL, do the following:

Once this is done, the database will be ready for the web interface to be used to start creating rules.

The Perl DBD::Pg Module

In order for Perl to connect to a PostgreSQL database, you need to make sure that the DBD::Pg module is installed correctly.

On a RedHat/CentOS system, you can install it as an OS package, using this command:

# yum install perl-DBD-Pg

Other operating systems may have similar OS packages available. If all else fails, you can always install it from CPAN:

# perl -MCPAN -e install DBD::Pg

Building the module from source like this will require that you have the PostgreSQL developer packages installed on your system.

The G4DBCONN Value

When configuring the web interface and the mail script, you will need to set the value of the G4DBCONN environment variable. For a PostgreSQL database, the value needs to look like this:

SetEnv G4DBCONN "dbi:Pg(PrintError=>0):dbname=rules"

If the database is physically stored on a different machine, you may need to include parameters which specify the hostname and/or port number. If so, the value will look like this:

SetEnv G4DBCONN "dbi:Pg(PrintError=>0):dbname=rules;host=HOST;port=PORT"