# 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:~$