PostgreSQL configuration

Adjust pg_hba.conf

The connection to a PostgreSQL server can be established either through a local Unix-domain socket or a TCP/IP socket. The pg_hba.conf file defines which users/groups are allowed to connect from which clients and how they have to authenticate. The pg_hba.conf file is mostly stored in the database cluster’s data directory. The data directory is often /usr/local/pgsql/data or /var/lib/pgsql/data. On Debian GNU/Linux systems the pg_hba.conf is located in /etc/postgresql/VERSION/CLUSTER (for example: /etc/postgresql/9.1/main).

Some information about the pg_hba.conf is available in the PostgreSQL Wiki/Client Authentication, even more detailed in the pg_hba.conf documentation.

For TCP/IP connections

Add a line like the following to your pg_hba.conf if you want to connect via a TCP/IP connection to the PostgreSQL server. Make sure to adjust the CIDR address if PostgreSQL is running on a different system:

# IPv4 local connections:
host    mailsys     +mailsys    127.0.0.1/32          md5

For Unix-domain socket connections

If you want to use PostgreSQL’s local Unix domain socket for database connections add a line like the second one to your pg_hba.conf:

# "local" is for Unix domain socket connections only
local   mailsys     +mailsys                    md5
local   all         all                         ident sameuser

Note

ident sameuser will not work, because dovecot-auth will be executed by the unprivileged user doveauth (see System Preparation), not by the dovecot user.

Create database users and the database

You should create some database users for vmm, Dovecot and Postfix as well as their group. Each of them will get different privileges granted. Finally create a new database.

Create a database superuser, which will be able to create users and databases, if necessary. If you have sudo privileges run:

user@host:~$ sudo su - postgres
[sudo] password for user:
postgres@host:~$ createuser -s -d -r -E -e -P $USERNAME

If you are root, omit the sudo command. Just execute su - postgres and create the database superuser.

Start psql as superuser and connect to the database template1:

user@host:~$ psql template1

Then create users, their group and the empty database:

template1=# CREATE ROLE vmm LOGIN ENCRYPTED PASSWORD 'DB PASSWORD for vmm';
template1=# CREATE ROLE dovecot LOGIN ENCRYPTED password 'DB PASSWORD for Dovecot';
template1=# CREATE ROLE postfix LOGIN ENCRYPTED password 'DB PASSWORD for Postfix';
template1=# CREATE ROLE mailsys WITH USER postfix, dovecot, vmm;
template1=# CREATE DATABASE mailsys WITH OWNER vmm ENCODING 'UTF8';
template1=# \q

Import tables and functions

Now start psql and connect as your vmm user to the database mailsys:

user@host:~$ psql mailsys vmm -W -h localhost

In PostgreSQL’s terminal-based front-end import the database layout/tables and functions into your database.

Dovecot v2.0.x

mailsys=> \i /path/to/vmm-0.7.0/pgsql/create_tables.pgsql
mailsys=> \q

Set database permissions

There’s a python script which grants permissions to your Dovecot and Postfix database users.

user@host:~$ python /path/to/vmm-0.6.2/pgsql/set-permissions.py -h
Usage: set-permissions.py OPTIONS

Set permissions for Dovecot and Postfix in the vmm database.

Options:
  -h, --help            show this help message and exit
  -a, --askpass         Prompt for the database password.
  -H HOST, --host=HOST  Hostname or IP address of the database server. Leave
                        blank in order to use the default Unix-domain socket.
  -n NAME, --name=NAME  Specifies the name of the database to connect to.
                        Default: mailsys
  -p PASS, --pass=PASS  Password for the database connection.
  -P PORT, --port=PORT  Specifies the TCP port or the local Unix-domain socket
                        file extension on which the server is listening for
                        connections. Default: 5432
  -U USER, --user=USER  Connect to the database as the user USER instead of
                        the default: root
  -D USER, --dovecot=USER
                        Database user name of the Dovecot database user.
                        Default: dovecot
  -M USER, --postfix=USER
                        Database user name of the Postfix (MTA)  database
                        user. Default: postfix
user@host:~$ python /path/to/vmm-0.6.2/pgsql/set-permissions.py -a -H 127.0.0.1 -U vmm
Password:
user@host:~$