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.

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 0.0.0.0/0 md5

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

# su - postgres
$ pg_ctl reload
$ exit

You should already have downloaded the files and run the ./fix-permissions.sh script.

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

• Create an empty database and two database users. The database will hold all of the data, and the users will be used by the web site and the email processing script. I call my database "rules", and the users "g4web" and "g4mail". You can call them whatever you like, just be sure to substitute your names where shown in the examples below. (You will probably also want to choose more secure passwords than these.)

  # su - postgres $ psql Welcome to psql 8.1.23, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit postgres=# CREATE DATABASE rules ; CREATE DATABASE postgres=# CREATE USER g4web PASSWORD 'web' ; CREATE ROLE postgres=# CREATE USER g4mail PASSWORD 'mail' ; CREATE ROLE postgres=# \q $ exit

• Create the empty tables within the database. The download comes with a file called "lib/create-tables.pgsql", which creates the empty tables and runs the GRANT commands needed to set the permissions correctly. If you are using different usernames for the web and mail users, you should edit this file before running it. Simply change the usernames at the end of each GRANT command to match the ones you are using.

  For example...

  GRANT SELECT ON phases TO g4mail ;
  GRANT SELECT,INSERT,UPDATE,DELETE ON phases TO g4web ;

  When you have edited all of the GRANT lines in the file, run the script as a PostgreSQL super-user, within the database, like so:

  # su - postgres $ cd /www/secure.domain.xyz/docs/rules/lib (your web site's DocumentRoot) $ psql rules (your database name) Welcome to psql 8.1.23, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit postgres=# \i create-tables.pgsql psql:create-tables.sql:16: ERROR: table "sessions" does not exist (this can be ignored) psql:create-tables.sql:17: ERROR: table "log" does not exist (this can be ignored) psql:create-tables.sql:18: ERROR: table "greylist" does not exist (this can be ignored) psql:create-tables.sql:19: ERROR: table "rules" does not exist (this can be ignored) psql:create-tables.sql:20: ERROR: table "phases" does not exist (this can be ignored) psql:create-tables.sql:33: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "phases_pkey" for table "phases" CREATE TABLE GRANT GRANT psql:create-tables.sql:103: NOTICE: CREATE TABLE will create implicit sequence "rules_id_seq" for serial column "rules.id" psql:create-tables.sql:103: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "rules_pkey" for table "rules" CREATE TABLE CREATE INDEX GRANT GRANT GRANT psql:create-tables.sql:128: NOTICE: CREATE TABLE will create implicit sequence "greylist_id_seq" for serial column "greylist.id" psql:create-tables.sql:128: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "greylist_pkey" for table "greylist" psql:create-tables.sql:128: NOTICE: CREATE TABLE / UNIQUE will create implicit index "greylist_id_key" for table "greylist" CREATE TABLE GRANT GRANT psql:create-tables.sql:145: NOTICE: CREATE TABLE will create implicit sequence "log_id_seq" for serial column "log.id" psql:create-tables.sql:145: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "log_pkey" for table "log" CREATE TABLE GRANT GRANT psql:create-tables.sql:170: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "sessions_pkey" for table "sessions" CREATE TABLE GRANT postgres=# \q $ exit

  Watch the output after the "\i" command to ensure that there are no error messages, particularly if you edited the file before running it. The first time you run it, you can igore the "table does not exist" errors at the beginning of the script. If there were any other problems, edit the file and fix the problems, then run the same "\i" command again.

  The script starts with a set of "DROP TABLE" commands to remove any existing tables, which causes the "table does not exist" error messages you will see the first time you run the script, but which also make it possible to re-run the script if you need to re-initialize the database.

• Insert one or more phases. When you first create the empty tables (above) the "phases" table will be empty. This will prevent the system from working. You must insert at least one phase before the system will work.

  The download comes with a file called "lib/insert-standard-phases.sql". This will install the phases I use on my own server, which are listed above. If you plan to change the phases in some way, you will want to edit this file before running it.

  To create the phases, run this script as a PostgreSQL super-user.

  # su - postgres $ cd /www/secure.domain.xyz/docs/rules/lib (your web site's DocumentRoot) $ psql rules (your database name) Welcome to psql 8.1.23, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit postgres=# \i insert-standard-phases.sql INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 postgres=# \q $ exit

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

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"

Copyright 1995-2024 John M. Simpson KG4ZOW (email) (PGP key)
Last updated 2012-07-24 07:17:12 +0000