VMMWiki: PreparingPostgreSQL

Edit 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/8.4/main).

Some information about the pg_hba.conf is available in 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 other 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

• This is necessary because dovecot-auth will be executed by the unprivileged user nobody, but nobody uses the access data from dovecot-sql.conf.

If the PostgreSQL server is already running, SIGHUP the postmaster for the changes to take effect, or use the command pg_ctl reload. Many Linux distributions provides also an init script that can do this job: /etc/init.d/postgresql [reload | force-reload].

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.

• Only superusers and users with CREATEROLE privilege can create new users.

Start psql and connect to the database template1:

psql template1

Then create users, their group and the empty database:

   1 CREATE USER vmm ENCRYPTED PASSWORD 'password for vmm';
   2 CREATE USER dovecot ENCRYPTED PASSWORD 'password for dovecot';
   3 CREATE USER postfix ENCRYPTED PASSWORD 'password for postfix';
   4 CREATE ROLE mailsys WITH USER vmm, dovecot, postfix;
   5 CREATE DATABASE mailsys WITH OWNER vmm ENCODING 'UTF8';
   6 \q
   7 

Create tables and grant privileges for Dovecot and Postfix

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

psql mailsys vmm -W -h 127.0.0.1

In the PostgreSQL interactive terminal issue the following commands/internal commands:

• import the database structure
• set permissions for the new database users

   1 -- For Dovecot v1.0.x/v1.1.x
   2 \i /path/to/vmm-0.5.2/create_tables.pgsql
   3 -- For Dovecot v1.2.x
   4 \i /path/to/vmm-0.5.2/create_tables-dovecot-1.2.x.pgsql
   5 -- For Dovecot v1.0.x/v1.1.x/v1.2.x
   6 GRANT SELECT ON dovecot_password, dovecot_user TO dovecot;
   7 GRANT SELECT ON postfix_alias, postfix_gid, postfix_maildir, postfix_relocated, postfix_transport, postfix_uid TO postfix;
   8 \q
   9 

CategoryInstallation