1 ========================

     2 PostgreSQL configuration

     3 ========================

     4 Adjust pg_hba.conf

     5 ------------------

     6 The connection to a PostgreSQL server can be established either through a

     7 local Unix-domain socket or a TCP/IP socket. The :file:`pg_hba.conf` file

     8 defines which users/groups are allowed to connect from which clients and

     9 how they have to authenticate.

    10 The :file:`pg_hba.conf` file is mostly stored in the database cluster's data

    11 directory. The data directory is often :file:`/usr/local/pgsql/data` or

    12 :file:`/var/lib/pgsql/data.` On Debian GNU/Linux systems the

    13 :file:`pg_hba.conf` is located in :file:`/etc/postgresql/{VERSION}/{CLUSTER}`

    14 (for example: :file:`/etc/postgresql/9.1/main`).

    15 

    16 Some information about the :file:`pg_hba.conf` is available in the PostgreSQL

    17 Wiki/`Client Authentication`_, even more detailed in the pg_hba.conf_

    18 documentation.

    19 

    20 For TCP/IP connections

    21 ^^^^^^^^^^^^^^^^^^^^^^

    22 Add a line like the following to your :file:`pg_hba.conf` if you want to

    23 connect via a TCP/IP connection to the PostgreSQL server.

    24 Make sure to adjust the CIDR address if PostgreSQL is running on a

    25 different system::

    26 

    27  # IPv4 local connections:

    28  host    mailsys     +mailsys    127.0.0.1/32          md5

    29 

    30 For Unix-domain socket connections

    31 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

    32 If you want to use PostgreSQL's local Unix domain socket for database

    33 connections add a line like the second one to your :file:`pg_hba.conf`::

    34 

    35  # "local" is for Unix domain socket connections only

    36  local   mailsys     +mailsys                    md5

    37  local   all         all                         ident sameuser

    38 

    39 .. note:: `ident sameuser` will not work, because `dovecot-auth` will be

    40    executed by the unprivileged user `doveauth`

    41    (see :ref:`System Preparation <doveauth>`), not by the `dovecot` user.

    42 

    43 Create database users and the database

    44 --------------------------------------

    45 You should create some database users for vmm, Dovecot and Postfix as well

    46 as their group.

    47 Each of them will get different privileges granted.

    48 Finally create a new database.

    49 

    50 Create a database superuser, which will be able to create users and databases,

    51 if necessary. If you have sudo privileges run:

    52 

    53 .. code-block:: console

    54 

    55  user@host:~$ sudo su - postgres

    56  [sudo] password for user:

    57  postgres@host:~$ createuser -s -d -r -E -e -P $USERNAME

    58 

    59 If you are root, omit the :command:`sudo` command. Just execute

    60 :command:`su - postgres` and create the database superuser.

    61 

    62 Start :command:`psql` as superuser and connect to the database `template1`:

    63 

    64 .. code-block:: console

    65 

    66  user@host:~$ psql template1

    67 

    68 Then create users, their group and the empty database:

    69 

    70 .. code-block:: postgresql-console

    71 

    72  template1=# CREATE ROLE vmm LOGIN ENCRYPTED PASSWORD 'DB PASSWORD for vmm';

    73  template1=# CREATE ROLE dovecot LOGIN ENCRYPTED password 'DB PASSWORD for Dovecot';

    74  template1=# CREATE ROLE postfix LOGIN ENCRYPTED password 'DB PASSWORD for Postfix';

    75  template1=# CREATE ROLE mailsys WITH USER postfix, dovecot, vmm;

    76  template1=# CREATE DATABASE mailsys WITH OWNER vmm ENCODING 'UTF8';

    77  template1=# \q

    78 

    79 Import tables and functions

    80 ---------------------------

    81 Now start :command:`psql` and connect as your `vmm` user to the database

    82 `mailsys`:

    83 

    84 .. code-block:: console

    85 

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

    87 

    88 In PostgreSQL's terminal-based front-end import the database layout/tables

    89 and functions into your database.

    90 

    91 Dovecot v1.2.x/v2.0.x/v2.1.x

    92 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^

    93 .. code-block:: postgresql-console

    94 

    95  mailsys=> \i /path/to/vmm-0.6.0/pgsql/create_tables-dovecot-1.2.x.pgsql

    96  mailsys=> \q

    97 

    98 Dovecot v1.0.x/v1.1.x

    99 ^^^^^^^^^^^^^^^^^^^^^

   100 .. code-block:: postgresql-console

   101 

   102  mailsys=> \i /path/to/vmm-0.6.0/pgsql/create_tables.pgsql

   103  mailsys=> \q

   104 

   105 Set database permissions

   106 ------------------------

   107 .. include:: ../pgsql_set_permissionspermissions.rst

   108 

   109 .. include:: ../ext_references.rst