Mail_Server:Postgres_Authentication

Introduction

For small email systems, it's often more than adequate to have each mail account 'attached' to a real user (e.g., bob@example.com is for a user called bob who has an entry in /etc/passwd). However, this solution limits you when you want to have lots of users with just email accounts and nothing else, like if you wanted to give your friends webmail accounts.

If you start hosting email for other domains, or have lots of users, the limitation of having every user in /etc/passwd quickly becomes an major issue. The solution for this is virtual users, which are accounts that the mail system knows about but which do not exist in /etc/passwd.

However, account information still has to be stored somewhere. Normally, this data is stored in a relational database. This gives a mail administrator several advantages and more flexibility over his or her accounts. One advantage is that it becomes easy to build a web interface to allow users to administer their own accounts in an easy fashion.

One database that can be used for this task is [http://www.postgresql.org PostgreSQL]. However, postfix does not know to talk to PostgreSQL in a default installation. The rest of this article will cover enabling PostgreSQL support in Postfix and setting up the database.

Setting up PostgreSQL Support in Postfix

Debian

If you are running Debian, simply running the command:

# apt-get install postfix-pgsql

as the root user will install PostgreSQL support for Postfix.

Ubuntu

If you are running the Ubuntu, running the command:

$ sudo apt-get install postfix-pgsql

as any user will install PostgreSQL support for Postfix. You need to use our provided sources.list file, or add the universe repository manually.

Others

Other distributions may include PostgreSQL support for Postfix. If they do, add a section like the ones above describing how to install it, or noting that it is enabled out-of-the-box. To check, run the command:

postconf -m

If the output lists:

pgsql

Then support for PostgreSQL is available. Failing that, you'll have to build Postfix from source.

Building Postfix from Source

NB:

The official Postfix+PostgreSQL documentation (http://www.postfix.org/PGSQL_README.html)
The official Postfix Installation Guide (http://wwww.postfix.org/INSTALL.html)

These are the official guide for enabling PostgreSQL support with Postfix and installing Postfix from source code. The instructions here are adapted from the above and intended as a guideline only. These instructions assume you have familiarity with compiling software from source code. You should also read the above documents to be sure you understand any other steps you need to take care on your system to make Postfix properly work.

Install PostgreSQL. Make sure the development headers are installed as well. If you install PostgreSQL from source, they will be.
Download the Postfix source from one of the mirrors (http://www.postfix.org/download.html).
Unpack the source with:

$ tar -xzf postfix-2.1.5.tar.gz

Note that Postfix 2.1.5 was the release when I wrote this. If the file you download is different, substitute it's name in place of postfix-2.1.5.tar.gz.

Prepare the source to include PostgreSQL support:

5.           $ make tidy

6.           $ make -f Makefile.init makefiles \

7.           >'CCARGS=-DHAS_PGSQL -I<path to PostgreSQL includes>' \

>'AUXLIBS=-L<path to PostgreSQL libraries> -lpq'

Note that this will include only PostgreSQL support. If you need additional options, you should read the installation guide linked above. By default, the PostgreSQL headers are at /usr/local/include/pgsql and the libraries at /usr/local/lib.

Build the source code:

$ make

Create a user and group for the postfix account. This must be done as root. A possible set of commands you can use are:

10.       # groupadd postfix

11.       # useradd -g postfix -d /nonexistent -s /bin/false postfix

# groupadd postdrop

Install the postfix software. This must be done as root:

# make install

Configuring the PostgreSQL Database

Creating the Database and User Account

First, a database account must be created in PostgreSQL for use by postfix:

$ createuser -U postgres -ADP postfix

It will prompt you for a password for this user. Remember it, as it must be supplied later. For the rest of this article, we'll assume the password is postfix. Also, if your PostgreSQL administrator account isn't postgres (the default), replace it in the above command. If the administrator account has a password, you will be prompted for it.

Create the database used to hold the mail tables:

$ createdb -U postgres -O postfix mail

If your PostgreSQL administrator account isn't postgres (the default), replace it in the above command. If the administrator account has a password, you will be prompted for it.

Database Tables

Three tables need to be created in the database:

transport: Which contains all the domains what what transport should be used for them.
users: Which contains all the virtual users with account information
virtual: Which contains all the address mappings, e.g., mail for bill@example.com should go to robert@example.com.

Transport Table Details

The transport table has the following layout:

Column	Type	Modifiers
pkey	serial
domain	text	NOT NULL
transport	varchar(128)
Primary Key: pkey

The various columns are:

pkey: The id for every row in the table. This is automatically generated by the database and you do not have to worry about it.
domain: The domain name for this transport (e.g., example.com)
transport: The transport type, e.g., virtual.

User Table Details

The user table has the following layout:

Column	Type	Modifiers
pkey	serial
id	text	NOT NULL
address	text
crypt	varchar(128)
uid	serial
gid	integer	NOT NULL
home	varchar(256)	NOT NULL DEFAULT '/'
domain	text
maildir	varchar(256)	NOT NULL
absolute_maildir	text
Primary Key: pkey

The various columns are:

pkey: The id for every row in the table. This is automatically generated by the database and you do not have to worry about it.
id: what the user will have to enter when prompted for a password. It doesn't have to be the same as the email address, but life will be simpler if it is.
address: The user's primary email address.
crypt: The user's encrypted password.
uid: The user's ID number. The database automatically generates this for you, as the important part is that the number is here is unique, not the actual value itself.
home: Contains the location of the virtual spool directory. If postfix is running in a chroot, this should be /. If it's not running in a chroot, it should probably be /var/spool/postfix/virtual/ or similar.
domain: Contains the primary domain name for this user. This is an optional field.
maildir: Contains the path to the user's mail directory. This is appended to the value in the home field. Normally, it is something like: /domain name/username.
absolute_maildir: Contains the location of the user's mail directory from /. You will only need this if Postfix is running in a chroot.

Virtual Table Details

The virtual table has the following layout:

Column	Type	Modifiers
pkey	serial
address	text	NOT NULL
goto	text	NOT NULL
Primary Key: pkey

The various columns are:

pkey: The id for every row in the table. This is automatically generated by the database and you do not have to worry about it.
address: The virtual email address.
goto: The destination email address.

Creating the Tables

The following steps will create the tables:

Connect to the database using the psql tool:

$ psql -U postfix mail

If you are prompted for the postfix account password, enter it.

Create the transport table. If you copy and paste the SQL below at the prompt, psql will handle it correctly. Simply press ENTER/RETURN afterwards and the table will be created:

3.           CREATE TABLE transport (

4.             pkey serial,

5.             domain text NOT NULL,

6.             transport varchar(128),

7.             PRIMARY KEY ( pkey )

);

Create the users table, in the exact same fashion to creating the transport table above:

9.           CREATE TABLE users (

10.         pkey serial,

11.         id text NOT NULL,

12.         address text,

13.         crypt varchar(128),

14.         uid serial NOT NULL,

15.         gid integer NOT NULL,

16.         home varchar(256) NOT NULL DEFAULT '/',

17.         domain text,

18.         maildir varchar(256) NOT NULL,

19.         absolute_maildir text,

20.         PRIMARY KEY ( pkey )

);

Finally, create the virtual table:

22.       CREATE TABLE virtual (

23.         pkey serial,

24.         address text NOT NULL,

25.         goto text NOT NULL,

26.         PRIMARY KEY ( pkey )

);

Configuring Postfix to use PostgreSQL

The next step is to configure Postfix to access the data stored in the PostgreSQL database. This is done by creating several text files. These text files should go into /etc/postfix, or wherever your Postfix configuration is kept. All of the files follow more or less the same format.

`pgsql_virt.cf`

This file sets up the mailbox maps and should look like:

# The database username

user=postfix

# The database password

password=postfix

# The database name

dbname=mail

# The table containing the user data

table=users

#the field containing the users maildir location

select_field=maildir

#the field containing the users email address. It's used in the query

# SELECT * FROM [table] WHERE [this field] = <email address>

where_field=address

# The database host.  You can have more than one host here, which is

# useful if you want to have a backup database.

hosts=127.0.0.1

Change the parameters as necessary to match your configuration.

`transport.cf`

This file sets up the transport maps and should look like:

user=postfix

password=postfix

dbname=mail

table=transport

select_field=transport

where_field=domain

hosts=127.0.0.1

Change the parameters as necessary to match your configuration.

`virtual.cf`

This file sets up the virtual maps and should look like:

user=postfix

password=postfix

dbname=mail

table=virtual

select_field=goto

where_field=address

hosts=127.0.0.1

`uids.cf`

This file sets up the uid maps and should look like:

user=postfix

password=postfix

dbname=mail

table=users

select_field=uid

where_field=address

hosts=127.0.0.1

`gids.cf`

This file sets up the gid maps and should look like:

user=postfix

password=postfix

dbname=mail

table=users

select_field=gid

where_field=address

hosts=127.0.0.1

`main.cf`

In main.cf add or edit the following lines as appropriate.

transport_maps=pgsql:/etc/postfix/transport.cf

virtual_mailbox_maps=pgsql:/etc/postfix/pgsql_virt.cf

virtual_uid_maps=pgsql:/etc/postfix/uids.cf

virtual_gid_maps=pgsql:/etc/postfix/gids.cf

virtual_mailbox_base=/var/spool/postfix/virtual/

mydestination = $mydomain, $myhostname, $transport_maps

virtual_maps =pgsql:/etc/postfix/virtual.cf

relay_domains = $mydestination

Create Domains to Receive Mail From

Postfix is now setup to gather its data from the database. However, data has to be put into the database in order for it to actually be useful. The first thing that has to be created is a domain entry. To do this:

Connect to the database using the psql tool:

$ psql -U postfix mail

If you are prompted for the postfix account password, enter it.

Add the entry to the transport table:

mail=# INSERT INTO transport (domain, transport) VALUES ('example.com', 'virtual');

This creates an entry for domain example.com; replace example.com in the above with whatever domain you want to create.

Pick a GID for this domain. It should be not already in use on your system, preferably. To see what GIDs are in use, look at /etc/group.
Create the directory for this domain under the virtual directory in Postfix's spool directory (normally /var/spool/postfix, modify as appropriate):

5.           # mkdir /var/spool/postfix/virtual/example

6.           # chown postfix:<gid> /var/spool/postfix/virtual/example

# chmod 775 /var/spool/postfix/virtual/example

Replace gid in the above with GID you chose in the previous step.

Create Users to Receive Mail

Now, users entries in the newly created domain have to be added:

Connect to the database using the psql tool:

$ psql -U postfix mail

If you are prompted for the postfix account password, enter it.

Add an entry to the users table by filling out the value of the gid field in the SQL below and then running it in psql. If you copy and paste the modified text and then press ENTER/RETURN, psql will do the right thing.:

3.           INSERT INTO users (id, domain, address, crypt, gid, maildir, absolute_maildir)

4.           VALUES (

5.                   'bob@example.com',

6.                   'example.com',

7.                   'bob@example.com',

8.                   'T/c7XRT543rW2', -- The user's encrypted password

9.                   <gid>,  -- The GID from the domain creation

10.               '/example/bob/',

        '/var/spool/postfix/virtual/example/bob/');

Generating the User's password

To generate the user's password, run the following command:

$ perl -e "print crypt ('<password>', '<salt>');"

where password is the user's desired password and salt is any two alphanumeric characters. The encrypted password will be printed to standard out.

Configuring Courier

In order for virtual users to be able to access their mail, Courier must be configured to lookup the data in the database as well. Fortunately, this is much easier than the Postfix configuration. Create a file called authpgsqlrc in the Courier configuration directory (normally /etc/courier or /usr/lib/courier/etc for custom installs). It should have the following contents:

PGSQL_HOST 127.0.0.1

PGSQL_PORT 5432

PGSQL_USERNAME postfix

PGSQL_PASSWORD postfix

PGSQL_DATABASE mail

PGSQL_USER_TABLE users

PGSQL_CRYPT_PWFIELD crypt

PGSQL_UID_FIELD uid

PGSQL_GID_FIELD gid

PGSQL_LOGIN_FIELD id

PGSQL_HOME_FIELD home

PGSQL_MAILDIR_FIELD absolute_maildir

Finally, make sure authpgsql is included on the authmodulelist line in the authdaemonrc file. If it is not, add it and then restart the Courier authdaemon.

God soul science fiction humanity human life sense of life life after death time machine