7 |
7 |
8 * = http://pypgsql.sourceforge.net/ (Debian: python-pgsql) |
8 * = http://pypgsql.sourceforge.net/ (Debian: python-pgsql) |
9 |
9 |
10 |
10 |
11 Configuring PostgreSQL |
11 Configuring PostgreSQL |
|
12 (for more details see: http://vmm.localdomain.org/PreparingPostgreSQL) |
12 |
13 |
13 * /etc/postgresql/8.2/main/pg_hba.conf |
14 * /etc/postgresql/8.4/main/pg_hba.conf |
|
15 [ if you prefer to connect via TCP/IP ] |
14 # IPv4 local connections: |
16 # IPv4 local connections: |
15 host mailsys +mailsys 127.0.0.1/32 md5 |
17 host mailsys +mailsys 127.0.0.1/32 md5 |
|
18 [ if you want to connect through a local Unix-domain socket ] |
|
19 # "local" is for Unix domain socket connections only |
|
20 local mailsys +mailsys md5 |
16 |
21 |
17 # reload configuration |
22 # reload configuration |
18 /etc/init.d/postgresql-8.2 force-reload |
23 /etc/init.d/postgresql-8.4 force-reload |
19 |
24 |
20 * Create a DB user if necessary: |
25 * Create a DB user if necessary: |
21 DB Superuser: |
26 DB Superuser: |
22 createuser -s -d -r -E -e -P $USERNAME |
27 createuser -s -d -r -E -e -P $USERNAME |
23 DB User: |
28 DB User: |
24 createuser -d -E -e -P $USERNAME |
29 createuser -d -E -e -P $USERNAME |
25 |
30 |
26 * Create Database and db users for Postfix and Dovecot |
31 * Create Database and db users for vmm, Postfix and Dovecot |
27 connecting to PostgreSQL: |
32 connecting to PostgreSQL: |
28 psql template1 |
33 psql template1 |
29 |
34 |
30 # create database |
35 # create users, group and the database |
31 CREATE DATABASE mailsys ENCODING 'UTF8'; |
36 CREATE USER vmm ENCRYPTED PASSWORD 'DB PASSWORD for vmm'; |
|
37 CREATE USER dovecot ENCRYPTED password 'DB PASSWORD for Dovecot'; |
|
38 CREATE USER postfix ENCRYPTED password 'DB PASSWORD for Postfix'; |
|
39 CREATE ROLE mailsys WITH USER postfix, dovecot, vmm; |
|
40 CREATE DATABASE mailsys WITH OWNER vmm ENCODING 'UTF8'; |
|
41 \q |
|
42 |
32 # connect to the new database |
43 # connect to the new database |
33 \c mailsys |
44 psql mailsys vmm -W -h 127.0.0.1 |
34 # either import the database structure for Dovecot v1.0.x/v1.1.x |
45 # either import the database structure for Dovecot v1.0.x/v1.1.x |
35 \i /path/to/create_tables.pgsql |
46 \i /path/to/create_tables.pgsql |
36 # or import the database structure for Dovecot v1.2.x |
47 # or import the database structure for Dovecot v1.2.x |
37 \i /path/to/create_tables-dovecot-1.2.x.pgsql |
48 \i /path/to/create_tables-dovecot-1.2.x.pgsql |
38 |
|
39 # create users and group |
|
40 CREATE USER postfix ENCRYPTED password 'DB PASSWORD for Postfix'; |
|
41 CREATE USER dovecot ENCRYPTED password 'DB PASSWORD for Dovecot'; |
|
42 CREATE ROLE mailsys WITH USER postfix, dovecot; |
|
43 |
49 |
44 # set permissions |
50 # set permissions |
45 GRANT SELECT ON dovecot_password, dovecot_user TO dovecot; |
51 GRANT SELECT ON dovecot_password, dovecot_user TO dovecot; |
46 GRANT SELECT ON postfix_alias, postfix_gid, postfix_maildir, |
52 GRANT SELECT ON postfix_alias, postfix_gid, postfix_maildir, |
47 postfix_relocated, postfix_transport, postfix_uid TO postfix; |
53 postfix_relocated, postfix_transport, postfix_uid TO postfix; |