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.
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
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.
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
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.
mailsys=> \i /path/to/vmm-0.6.1/pgsql/create_tables-dovecot-1.2.x.pgsql
mailsys=> \q
mailsys=> \i /path/to/vmm-0.6.1/pgsql/create_tables.pgsql
mailsys=> \q
There’s a python script which grants permissions to your Dovecot and Postfix database users.
user@host:~$ python /path/to/vmm-0.6.1/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.1/pgsql/set-permissions.py -a -H 127.0.0.1 -U vmm
Password:
user@host:~$