# HG changeset patch
# User Pascal Volk <neverseen@users.sourceforge.net>
# Date 1207797852 0
# Node ID 1607f7b2dba9b16f01feeb6770149ec2b22eeb78
# Parent  ebc9b864bc9aaddfbc6b3ce952969a52b5c441cb
* 'update_config_0.3.x-0.4.py'
    - fixed code for view dovecot_user

* 'create_tables.pgsql'
    - updated view dovecot_user (extra field »mail«)

* 'UPGRADE'
    - fixed filename (update_0.3.x-0.4.sh -> upgrade.sh)
    - added hints for view perms and user_query

* 'INSTALL'
    - replaced mail_extra_groups with mail_privileged_group
    - updated user_query

diff -r ebc9b864bc9a -r 1607f7b2dba9 ChangeLog
--- a/ChangeLog	Sun Apr 06 21:36:00 2008 +0000
+++ b/ChangeLog	Thu Apr 10 03:24:12 2008 +0000
@@ -1,4 +1,12 @@
 === 0.0.0 ===
+2008-04-10  Pascal Volk  <neverseen@users.sourceforge.net>
+
+	* update_tables_0.3.x-0.4.py: Replaced view dovecot_user
+	* create_tables.pgsql (VIEW: dovecot_user): Added extra field mail
+	* UPGRADE: fixed filename, hint for view permissions and user_query
+	* INSTALL: Replaced mail_extra_groups with mail_privileged_group, updated
+	user_query
+
 2008-04-06  Pascal Volk  <neverseen@users.sourceforge.net>
 
 	* install.sh: Removed $PF_GID
diff -r ebc9b864bc9a -r 1607f7b2dba9 INSTALL
--- a/INSTALL	Sun Apr 06 21:36:00 2008 +0000
+++ b/INSTALL	Thu Apr 10 03:24:12 2008 +0000
@@ -39,8 +39,8 @@
 
     # set permissions
     GRANT SELECT ON dovecot_password, dovecot_user TO dovecot;
-    GRANT SELECT ON postfix_alias, postfix_maildir, postfix_relocated,
-    postfix_uid, postfix_gid, postfix_transport TO postfix;
+    GRANT SELECT ON postfix_alias, postfix_gid, postfix_maildir,
+    postfix_relocated, postfix_transport, postfix_uid TO postfix;
 
     # leave psql
     \q
@@ -58,7 +58,7 @@
 * /etc/dovecot/dovecot.conf
     # all your other settings
     mail_location = maildir:~/Maildir
-    mail_extra_groups = mail
+    mail_privileged_group = mail
     first_valid_uid = 70000
     first_valid_gid = 70000
     protocol lda {
@@ -92,7 +92,7 @@
     connect = host=localhost dbname=mailsys user=dovecot password=$Dovecot_PASS
     default_pass_scheme = HMAC-MD5
     password_query = SELECT "user", password FROM dovecot_password WHERE "user"= '%u'
-    user_query = SELECT home, uid, gid FROM dovecot_user WHERE userid = '%u'
+    user_query = SELECT home, uid, gid, mail FROM dovecot_user WHERE userid='%u'
 
 Provide a root SETUID copy of Dovecot's deliver agent for Postfix
 
diff -r ebc9b864bc9a -r 1607f7b2dba9 UPGRADE
--- a/UPGRADE	Sun Apr 06 21:36:00 2008 +0000
+++ b/UPGRADE	Thu Apr 10 03:24:12 2008 +0000
@@ -3,7 +3,13 @@
     * stop Postfix and Dovecot
     * backup/dump your database.
     * backup/dump your database!
-    * execute update_0.3.x-0.4.sh
+    * execute upgrade.sh
+    * set permissions for replaced views:
+        GRANT SELECT ON dovecot_user TO your_dovecot_dbuser;
+        GRANT SELECT ON postfix_transport TO your_postfix_dbuser;
+    * update user_query in /etc/dovecot/dovecot-sql.conf:
+        user_query = \
+            SELECT home, uid, gid, mail FROM dovecot_user WHERE userid='%u'
     * edit the pgsql parameters hosts, user, password and dbname in:
         $(postconf -h config_directory)/pgsql-smtpd_sender_login_maps.cf
         $(postconf -h config_directory)/pgsql-transport.cf
diff -r ebc9b864bc9a -r 1607f7b2dba9 create_tables.pgsql
--- a/create_tables.pgsql	Sun Apr 06 21:36:00 2008 +0000
+++ b/create_tables.pgsql	Thu Apr 10 03:24:12 2008 +0000
@@ -90,9 +90,11 @@
 
 CREATE OR REPLACE VIEW dovecot_user AS
     SELECT local_part || '@' || domains.domainname AS userid,
-           domains.domaindir || '/' || uid AS home, uid, gid
+           uid, gid, domains.domaindir || '/' || uid AS home,
+           '~/' || maillocation.maillocation AS mail
       FROM users
-           LEFT JOIN domains USING (gid);
+           LEFT JOIN domains USING (gid)
+           LEFT JOIN maillocation USING (mid);
 
 CREATE OR REPLACE VIEW postfix_gid AS
     SELECT gid, domainname
diff -r ebc9b864bc9a -r 1607f7b2dba9 update_tables_0.3.x-0.4.py
--- a/update_tables_0.3.x-0.4.py	Sun Apr 06 21:36:00 2008 +0000
+++ b/update_tables_0.3.x-0.4.py	Thu Apr 10 03:24:12 2008 +0000
@@ -108,12 +108,16 @@
             LEFT JOIN maillocation USING (mid)""")
 dbh.commit()
 
-# Update VIEW dovecot_user
+# Replace VIEW dovecot_user
+dbc.execute("DROP VIEW dovecot_user")
+dbh.commit()
 dbc.execute("""CREATE OR REPLACE VIEW dovecot_user AS
     SELECT local_part || '@' || domains.domainname AS userid,
-           domains.domaindir || '/' || uid AS home, uid, gid
+           uid, gid, domains.domaindir || '/' || uid AS home,
+           '~/' || maillocation.maillocation AS mail
       FROM users
-           LEFT JOIN domains USING (gid)""")
+           LEFT JOIN domains USING (gid)
+           LEFT JOIN maillocation USING (mid);""")
 dbh.commit()
 
 # fix table users (Part II)