10.4. Generic PostgreSQL

The PostgreSQL userbase is flexible in that it allows the user to specify a SQL query from the configuration. The default query corresponds to the following schema:

create table mboxes (
 id		 SERIAL PRIMARY KEY,
 mbox		 VARCHAR(255) NOT NULL,
 password	 VARCHAR(50) DEFAULT NULL,
 quotaMB	 INT DEFAULT NULL,
 isForward       INT DEFAULT NULL,
 fwdDest         VARCHAR(80) DEFAULT NULL
);
CREATE UNIQUE INDEX mbox_index ON mboxes(mbox);
GRANT SELECT ON mboxes TO powermail;
	

The default query is:

	  select quotaMB,isForward,fwdDest,password from mboxes where mbox='%s'
	

10.4.1. Configuring PostgreSQL connectivity

The following parameters are available to configure the PostgreSQL userbase:

A typical session setting up PostgreSQL might be:

$ createdb powermail
CREATE DATABASE
$ createuser powermail
Shall the new user be allowed to create databases? (y/n) n
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER
$ psql powermail
powermail=# create table mboxes (
powermail(#  id SERIAL PRIMARY KEY,
powermail(#  mbox VARCHAR(255) NOT NULL,
powermail(#  password VARCHAR(50) DEFAULT NULL,
powermail(#  quotaMB INT DEFAULT NULL,
powermail(#  isForward       INT DEFAULT NULL,
powermail(#  fwdDest         VARCHAR(80) DEFAULT NULL
powermail(# );
NOTICE:  CREATE TABLE will create implicit sequence 'mboxes_id_seq' for SERIAL column 'mboxes.id'
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'mboxes_pkey' for table 'mboxes'
CREATE
powermail=# CREATE UNIQUE INDEX mbox_index ON mboxes(mbox);
CREATE
powermail=# GRANT SELECT ON mboxes TO powermail;
GRANT
powermail=# insert into mboxes (mbox,password,quotaMB) values ('info@example.com','{plain}s3cr3t!',1);
INSERT 142969 1

This gives SELECT rights to a user called 'powermail'. Now add the following to the power.conf configuration file:

	    # echo userbase=postgresql > power.conf
	    # echo postgresql-user=powermail >> power.conf
	  

Note

If you are sure that PostgreSQL is running locally but the connection fails with 'could not connect to server: Connection refused', try adding 'postgresql-host=/tmp' to power.conf

Now try launching powerpop and powersmtp, the user should now exist.

10.4.2. Custom queries & schema description

Any query supplied must return the same values as the default one, and in the same order. The fields are: