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 .. code-block:: text

    28  :emphasize-lines: 2

    29 

    30  # IPv4 local connections:

    31  host    mailsys     +mailsys    127.0.0.1/32          md5

    32 

    33 For Unix-domain socket connections

    34 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

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

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

    37 

    38 .. code-block:: text

    39  :emphasize-lines: 2

    40 

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

    42  local   mailsys     +mailsys                    md5

    43  local   all         all                         ident sameuser

    44 

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

    46    executed by the unprivileged user `doveauth`

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

    48 

    49 Create database users and the database

    50 --------------------------------------

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

    52 as their group.

    53 Each of them will get different privileges granted.

    54 Finally create a new database.

    55 

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

    57 if necessary. If you have sudo privileges run:

    58 

    59 .. code-block:: console

    60 

    61  user@host:~$ sudo su - postgres

    62  [sudo] password for user:

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

    64 

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

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

    67 

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

    69 

    70 .. code-block:: console

    71 

    72  user@host:~$ psql template1

    73 

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

    75 

    76 .. code-block:: postgresql-console

    77 

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

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

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

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

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

    83  template1=# \q

    84 

    85 Import tables and functions

    86 ---------------------------

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

    88 `mailsys`:

    89 

    90 .. code-block:: console

    91 

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

    93 

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

    95 and functions into your database.

    96 

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

    98 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^

    99 .. code-block:: postgresql-console

   100 

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

   102  mailsys=> \q

   103 

   104 Dovecot v1.0.x/v1.1.x

   105 ^^^^^^^^^^^^^^^^^^^^^

   106 .. code-block:: postgresql-console

   107 

   108  mailsys=> \i /path/to/vmm-0.6.2/pgsql/create_tables.pgsql

   109  mailsys=> \q

   110 

   111 Set database permissions

   112 ------------------------

   113 .. include:: ../pgsql_set_permissionspermissions.rst

   114 

   115 .. include:: ../ext_references.rst