doc/web/source/installation/postgresql_configuration.rst
changeset 579 be0906181a10
child 581 9c138471d569
equal deleted inserted replaced
578:20141b967c0b 579:be0906181a10
       
     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