doc/web/source/installation/postgresql_configuration.rst
author Pascal Volk <user@localhost.localdomain.org>
Sun, 28 Oct 2012 12:59:30 +0000
changeset 631 9b4d0e2c97d5
parent 626 f151defe7078
child 706 6c369b680ab0
permissions -rw-r--r--
install.sh: Don't fail if $SYSCONFDIR doesn't exist.
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
579
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
     1
========================
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
     2
PostgreSQL configuration
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
     3
========================
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
     4
Adjust pg_hba.conf
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
     5
------------------
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
     6
The connection to a PostgreSQL server can be established either through a
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
     7
local Unix-domain socket or a TCP/IP socket. The :file:`pg_hba.conf` file
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
     8
defines which users/groups are allowed to connect from which clients and
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
     9
how they have to authenticate.
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    10
The :file:`pg_hba.conf` file is mostly stored in the database cluster's data
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    11
directory. The data directory is often :file:`/usr/local/pgsql/data` or
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    12
:file:`/var/lib/pgsql/data.` On Debian GNU/Linux systems the
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    13
:file:`pg_hba.conf` is located in :file:`/etc/postgresql/{VERSION}/{CLUSTER}`
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    14
(for example: :file:`/etc/postgresql/9.1/main`).
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    15
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    16
Some information about the :file:`pg_hba.conf` is available in the PostgreSQL
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    17
Wiki/`Client Authentication`_, even more detailed in the pg_hba.conf_
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    18
documentation.
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    19
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    20
For TCP/IP connections
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    21
^^^^^^^^^^^^^^^^^^^^^^
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    22
Add a line like the following to your :file:`pg_hba.conf` if you want to
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    23
connect via a TCP/IP connection to the PostgreSQL server.
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    24
Make sure to adjust the CIDR address if PostgreSQL is running on a
581
9c138471d569 doc/web: Emphasized a few lines of Dovecot/PostgreSQL configuration
Pascal Volk <user@localhost.localdomain.org>
parents: 579
diff changeset
    25
different system:
9c138471d569 doc/web: Emphasized a few lines of Dovecot/PostgreSQL configuration
Pascal Volk <user@localhost.localdomain.org>
parents: 579
diff changeset
    26
9c138471d569 doc/web: Emphasized a few lines of Dovecot/PostgreSQL configuration
Pascal Volk <user@localhost.localdomain.org>
parents: 579
diff changeset
    27
.. code-block:: text
9c138471d569 doc/web: Emphasized a few lines of Dovecot/PostgreSQL configuration
Pascal Volk <user@localhost.localdomain.org>
parents: 579
diff changeset
    28
 :emphasize-lines: 2
579
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    29
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    30
 # IPv4 local connections:
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    31
 host    mailsys     +mailsys    127.0.0.1/32          md5
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    32
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    33
For Unix-domain socket connections
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    34
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    35
If you want to use PostgreSQL's local Unix domain socket for database
581
9c138471d569 doc/web: Emphasized a few lines of Dovecot/PostgreSQL configuration
Pascal Volk <user@localhost.localdomain.org>
parents: 579
diff changeset
    36
connections add a line like the second one to your :file:`pg_hba.conf`:
9c138471d569 doc/web: Emphasized a few lines of Dovecot/PostgreSQL configuration
Pascal Volk <user@localhost.localdomain.org>
parents: 579
diff changeset
    37
9c138471d569 doc/web: Emphasized a few lines of Dovecot/PostgreSQL configuration
Pascal Volk <user@localhost.localdomain.org>
parents: 579
diff changeset
    38
.. code-block:: text
9c138471d569 doc/web: Emphasized a few lines of Dovecot/PostgreSQL configuration
Pascal Volk <user@localhost.localdomain.org>
parents: 579
diff changeset
    39
 :emphasize-lines: 2
579
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    40
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    41
 # "local" is for Unix domain socket connections only
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    42
 local   mailsys     +mailsys                    md5
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    43
 local   all         all                         ident sameuser
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    44
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    45
.. note:: `ident sameuser` will not work, because `dovecot-auth` will be
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    46
   executed by the unprivileged user `doveauth`
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    47
   (see :ref:`System Preparation <doveauth>`), not by the `dovecot` user.
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    48
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    49
Create database users and the database
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    50
--------------------------------------
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    51
You should create some database users for vmm, Dovecot and Postfix as well
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    52
as their group.
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    53
Each of them will get different privileges granted.
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    54
Finally create a new database.
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    55
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    56
Create a database superuser, which will be able to create users and databases,
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    57
if necessary. If you have sudo privileges run:
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    58
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    59
.. code-block:: console
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    60
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    61
 user@host:~$ sudo su - postgres
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    62
 [sudo] password for user:
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    63
 postgres@host:~$ createuser -s -d -r -E -e -P $USERNAME
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    64
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    65
If you are root, omit the :command:`sudo` command. Just execute
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    66
:command:`su - postgres` and create the database superuser.
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    67
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    68
Start :command:`psql` as superuser and connect to the database `template1`:
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    69
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    70
.. code-block:: console
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    71
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    72
 user@host:~$ psql template1
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    73
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    74
Then create users, their group and the empty database:
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    75
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    76
.. code-block:: postgresql-console
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    77
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    78
 template1=# CREATE ROLE vmm LOGIN ENCRYPTED PASSWORD 'DB PASSWORD for vmm';
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    79
 template1=# CREATE ROLE dovecot LOGIN ENCRYPTED password 'DB PASSWORD for Dovecot';
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    80
template1=# CREATE ROLE postfix LOGIN ENCRYPTED password 'DB PASSWORD for Postfix';
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    81
template1=# CREATE ROLE mailsys WITH USER postfix, dovecot, vmm;
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    82
template1=# CREATE DATABASE mailsys WITH OWNER vmm ENCODING 'UTF8';
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    83
template1=# \q
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    84
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    85
    Import tables and functions
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    86
---------------------------
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    87
Now start :command:`psql` and connect as your `vmm` user to the database
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    88
`mailsys`:
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    89
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    90
.. code-block:: console
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    91
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    92
 user@host:~$ psql mailsys vmm -W -h localhost
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    93
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    94
In PostgreSQL's terminal-based front-end import the database layout/tables
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    95
and functions into your database.
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    96
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    97
Dovecot v1.2.x/v2.0.x/v2.1.x
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    98
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    99
.. code-block:: postgresql-console
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
   100
626
f151defe7078 doc/web: Updated documentation for vmm-0.6.1.
Pascal Volk <user@localhost.localdomain.org>
parents: 581
diff changeset
   101
 mailsys=> \i /path/to/vmm-0.6.1/pgsql/create_tables-dovecot-1.2.x.pgsql
579
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
   102
 mailsys=> \q
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
   103
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
   104
Dovecot v1.0.x/v1.1.x
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
   105
^^^^^^^^^^^^^^^^^^^^^
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
   106
.. code-block:: postgresql-console
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
   107
626
f151defe7078 doc/web: Updated documentation for vmm-0.6.1.
Pascal Volk <user@localhost.localdomain.org>
parents: 581
diff changeset
   108
 mailsys=> \i /path/to/vmm-0.6.1/pgsql/create_tables.pgsql
579
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
   109
 mailsys=> \q
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
   110
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
   111
Set database permissions
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
   112
------------------------
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
   113
.. include:: ../pgsql_set_permissionspermissions.rst
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
   114
be0906181a10 doc: Added source of http://vmm.localdomain.org/.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
   115
.. include:: ../ext_references.rst