Commits

Zhang Huangbin committed 0e85ef4

Better PostgreSQL support on FreeBSD.

  • Participants
  • Parent commits c57c299

Comments (0)

Files changed (6)

File iRedMail/conf/amavisd

     # Override default Amavisd MySQL template file
     # openSUSE-12.1 ships Amavisd-new-2.7.0
     export AMAVISD_DB_MYSQL_TMPL="${SAMPLE_DIR}/amavisd-2.7.0.mysql"
+    export AMAVISD_DB_PGSQL_TMPL="${SAMPLE_DIR}/amavisd-2.7.0.pgsql"
 
 elif [ X"${DISTRO}" == X"DEBIAN" -o X"${DISTRO}" == X"UBUNTU" ]; then
     export AMAVISD_BIN='/usr/sbin/amavisd-new'
     export AMAVISD_TEMPDIR="${AMAVISD_MYHOME}/tmp"
 
     export AMAVISD_DB_MYSQL_TMPL="${SAMPLE_DIR}/amavisd-2.7.0.mysql"
+    export AMAVISD_DB_PGSQL_TMPL="${SAMPLE_DIR}/amavisd-2.7.0.pgsql"
 elif [ X"${DISTRO}" == X"FREEBSD" ]; then
     # Override global setting: user/group.
     export AMAVISD_SYS_USER='vscan'
     export ALTERMIME_BIN='/usr/local/bin/altermime'
 
     export AMAVISD_DB_MYSQL_TMPL="${SAMPLE_DIR}/amavisd-2.7.0.mysql"
+    export AMAVISD_DB_PGSQL_TMPL="${SAMPLE_DIR}/amavisd-2.7.0.pgsql"
 fi
 
 # Syslog setting, 'YES' or 'NO'

File iRedMail/functions/cleanup.sh

 {
     [ ! -d ${BACKUP_DIR} ] && mkdir -p ${BACKUP_DIR} &>/dev/null
 
+    # Backup OpenLDAP data
     if [ X"${BACKEND}" == X'OPENLDAP' ]; then
         ECHO_DEBUG "Setup backup script: ${BACKUP_SCRIPT_OPENLDAP}"
 
 EOF
     fi
 
+    # Backup MySQL databases
     if [ X"${BACKEND}" == X'OPENLDAP' -o X"${BACKEND}" == X'MYSQL' ]; then
         ECHO_DEBUG "Setup backup script: ${BACKUP_SCRIPT_MYSQL}"
 
 EOF
     fi
 
+    # Backup PostgreSQL databases
     #if [ X"${BACKEND}" == X'PGSQL' ]; then
     #   ECHO_DEBUG "Setup backup script: ${BACKUP_SCRIPT_PGSQL}"
     #
     #
     #   TODO
     #   Add cron job
+    #   cat >> ${CRON_SPOOL_DIR}/root <<EOF
+## Backup on 03:30 AM
+#30   3   *   *   *   bash ${BACKUP_SCRIPT_PGSQL}
+#EOF
     #fi
 
     echo 'export status_cleanup_backup_scripts="DONE"' >> ${STATUS_FILE}
 }
 
+cleanup_pgsql_force_password()
+{
+    ECHO_DEBUG "Force all users to connect PGSQL server with password."
+
+    if [ X"${DISTRO}" == X'UBUNTU' ]; then
+        perl -pi -e 's#^(local.*)peer#${1}md5#' ${PGSQL_CONF_PG_HBA}
+    elif [ X"${DISTRO}" == X'FREEBSD' ]; then
+        # FreeBSD
+        perl -pi -e 's#^(local.*)trust#${1}md5#' ${PGSQL_CONF_PG_HBA}
+        perl -pi -e 's#^(host.*)trust#${1}md5#' ${PGSQL_CONF_PG_HBA}
+    fi
+}
+
 cleanup()
 {
     cat > /etc/${PROG_NAME_LOWERCASE}-release <<EOF
     check_status_before_run cleanup_remove_mod_python
     [ X"${KERNEL_NAME}" == X"Linux" ] && check_status_before_run cleanup_replace_iptables_rule
     [ X"${DISTRO}" == X"RHEL" ] && check_status_before_run cleanup_replace_mysql_config
+    check_status_before_run cleanup_backup_scripts
+    [ X"${BACKEND}" == X'PGSQL' ] && check_status_before_run cleanup_pgsql_force_password
     [ X"${DISTRO}" != X'GENTOO' ] && check_status_before_run cleanup_start_postfix_now
     [ X"${DISTRO}" == X"FREEBSD" -o X"${DISTRO}" == X'GENTOO' ] && check_status_before_run cleanup_amavisd_preconfig
-    check_status_before_run cleanup_backup_scripts
 
     # Start Postfix to deliver emails.
     [ X"${DISTRO}" == X'GENTOO' ] && ${DIR_RC_SCRIPTS}/postfix restart >/dev/null

File iRedMail/functions/cluebringer.sh

 
             # Append cluebringer default sql template.
             gunzip -c /usr/share/doc/postfix-cluebringer/database/policyd-db.pgsql.gz >> ${tmp_sql}
-
-            cat >> ${tmp_sql} <<EOF
-GRANT SELECT,INSERT,UPDATE,DELETE ON access_control,amavis_rules,checkhelo,checkhelo_blacklist,checkhelo_tracking,checkhelo_whitelist,checkspf,greylisting,greylisting_autoblacklist,greylisting_autowhitelist,greylisting_tracking,greylisting_whitelist,policies,policy_group_members,policy_groups,policy_members,quotas,quotas_limits,quotas_tracking,session_tracking TO ${CLUEBRINGER_DB_USER};
-GRANT SELECT,UPDATE,USAGE ON access_control_id_seq,amavis_rules_id_seq,checkhelo_blacklist_id_seq,checkhelo_whitelist_id_seq,checkspf_id_seq,greylisting_autoblacklist_id_seq,greylisting_autowhitelist_id_seq,greylisting_whitelist_id_seq,policy_group_members_id_seq,policy_groups_id_seq,policy_members_id_seq,quotas_id_seq,quotas_limits_id_seq TO ${CLUEBRINGER_DB_USER};
-EOF
         fi
 
     elif [ X"${DISTRO}" == X"FREEBSD" ]; then
         # accounting.tsql is shipped in 2.0.11.
         [ -f accounting.tsql ] && bash convert ${policyd_sql_type} accounting.tsql >> ${tmp_sql}
         unset policyd_sql_type
+
+    fi
+
+    if [ X"${BACKEND}" == X'PGSQL' ]; then
+        cat >> ${tmp_sql} <<EOF
+GRANT SELECT,INSERT,UPDATE,DELETE ON access_control,amavis_rules,checkhelo,checkhelo_blacklist,checkhelo_tracking,checkhelo_whitelist,checkspf,greylisting,greylisting_autoblacklist,greylisting_autowhitelist,greylisting_tracking,greylisting_whitelist,policies,policy_group_members,policy_groups,policy_members,quotas,quotas_limits,quotas_tracking,session_tracking TO ${CLUEBRINGER_DB_USER};
+GRANT SELECT,UPDATE,USAGE ON access_control_id_seq,amavis_rules_id_seq,checkhelo_blacklist_id_seq,checkhelo_whitelist_id_seq,checkspf_id_seq,greylisting_autoblacklist_id_seq,greylisting_autowhitelist_id_seq,greylisting_whitelist_id_seq,policy_group_members_id_seq,policy_groups_id_seq,policy_members_id_seq,quotas_id_seq,quotas_limits_id_seq TO ${CLUEBRINGER_DB_USER};
+EOF
     fi
 
     # Initial cluebringer db.
 EOF
     fi
 
-    #rm -f ${tmp_sql} 2>/dev/null
+    rm -f ${tmp_sql} 2>/dev/null
     unset tmp_sql
 
     # Set correct permission.
 
     backup_file ${CLUEBRINGER_WEBUI_CONF}
 
+    [ X"${DISTRO}" == X'FREEBSD' ] && \
+        cp /usr/local/share/policyd2/contrib/httpd/cluebringer-httpd.conf ${CLUEBRINGER_WEBUI_CONF}
+
     # Make Cluebringer accessible via HTTPS.
     perl -pi -e 's#(</VirtualHost>)#Alias /cluebringer "$ENV{CLUEBRINGER_HTTPD_ROOT}/"\n${1}#' ${HTTPD_SSL_CONF}
 
     # Configure webui.
-    perl -pi -e 's#(.DB_DSN=).*#${1}"mysql:host=$ENV{MYSQL_SERVER};dbname=${CLUEBRINGER_DB_NAME}";#' ${CLUEBRINGER_WEBUI_CONF}
-    perl -pi -e 's#(.DB_USER=).*#${1}"$ENV{CLUEBRINGER_DB_USER}";#' ${CLUEBRINGER_WEBUI_CONF}
-    perl -pi -e 's#(.DB_PASS=).*#${1}"$ENV{CLUEBRINGER_DB_PASSWD}";#' ${CLUEBRINGER_WEBUI_CONF}
+    if [ X"${DISTRO}" == X'UBUNTU' ]; then
+        perl -pi -e 's#(.DB_DSN=).*#${1}"mysql:host=$ENV{MYSQL_SERVER};dbname=${CLUEBRINGER_DB_NAME}";#' ${CLUEBRINGER_WEBUI_CONF}
+        perl -pi -e 's#(.DB_USER=).*#${1}"$ENV{CLUEBRINGER_DB_USER}";#' ${CLUEBRINGER_WEBUI_CONF}
+        perl -pi -e 's#(.DB_PASS=).*#${1}"$ENV{CLUEBRINGER_DB_PASSWD}";#' ${CLUEBRINGER_WEBUI_CONF}
+    fi
 
     cat > ${CLUEBRINGER_HTTPD_CONF} <<EOF
 ${CONF_MSG}

File iRedMail/functions/postgresql.sh

 # -------------------------------------------------------
 # -------------------- PostgreSQL -----------------------
 # -------------------------------------------------------
+
+# NOTE: iRedMail will force all clients to send encrypted password
+#       after configuration completed and SQL data imported.
+# Reference: functions/cleanup.sh, function cleanup_pgsql_force_password().
+
 pgsql_initialize()
 {
     ECHO_INFO "Configure PostgreSQL database server." 
         freebsd_enable_service_in_rc_conf 'postgresql_enable' 'YES'
 
         ${PGSQL_RC_SCRIPT} initdb &>/dev/null
-
-        #echo '/usr/local/bin/postgres "-D" "/usr/local/pgsql/data"' > ${PGSQL_DATA_DIR}/postmaster.opts
-        #chown ${PGSQL_SYS_USER}:${PGSQL_SYS_GROUP} ${PGSQL_DATA_DIR}/postmaster.opts
-        #chmod 0600 ${PGSQL_DATA_DIR}/postmaster.opts
     fi
 
     backup_file ${PGSQL_CONF_PG_HBA} ${PGSQL_CONF_POSTGRESQL}
 
-    #ECHO_DEBUG "Force all users to connect PGSQL server with password."
-    #perl -pi -e 's#^(local.*)peer#${1}md5#' ${PGSQL_CONF_PG_HBA}
-
     ECHO_DEBUG "Listen on only localhost"
     perl -pi -e 's#.*(listen_addresses.=.)(.).*#${1}${2}localhost${2}#' ${PGSQL_CONF_POSTGRESQL}
 
     perl -pi -e 's#.*(client_min_messages =).*#${1} error#' ${PGSQL_CONF_POSTGRESQL}
 
     ECHO_DEBUG "Copy iRedMail SSL cert/key with strict permission."
-    # SSL is enabled by default.
     backup_file ${PGSQL_DATA_DIR}/server.{crt,key}
     rm -f ${PGSQL_DATA_DIR}/server.{crt,key} >/dev/null
     cp -f ${SSL_CERT_FILE} ${PGSQL_SSL_CERT} >/dev/null
     ln -s ${PGSQL_SSL_CERT} ${PGSQL_DATA_DIR}/server.crt >/dev/null
     ln -s ${PGSQL_SSL_KEY} ${PGSQL_DATA_DIR}/server.key >/dev/null
 
+    ECHO_DEBUG "Copy iRedMail SSL cert/key with strict permission."
+    # SSL is enabled by default on Ubuntu.
+    [ X"${DISTRO}" == X'FREEBSD' ] && \
+        perl -pi -e 's/^#(ssl.=.)off(.*)#${1}on${2}#' ${PGSQL_CONF_POSTGRESQL}
+
     ECHO_DEBUG "Start PostgreSQL server"
     if [ X"${DISTRO}" == X'FREEBSD' ]; then
         ${PGSQL_RC_SCRIPT} start  #&>/dev/null

File iRedMail/functions/roundcubemail.sh

     if [ X"${BACKEND}" == X"PGSQL" ]; then
         # Patch to escape single quote while updating password
         cd ${RCM_HTTPD_ROOT}
-        patch -p0 <${PATCH_DIR}/roundcubemail/password_driver_pgsql.patch >/dev/null
+        patch -p0 <${PATCH_DIR}/roundcubemail/password_driver_pgsql.patch &>/dev/null
 
         # Re-generate config.inc.php because it's hard to use perl to update
         # 'password_query' setting.

File iRedMail/samples/amavisd-2.7.0.pgsql

+-- local users
+CREATE TABLE users (
+  id         serial  PRIMARY KEY,  -- unique id
+  priority   integer NOT NULL DEFAULT '7',  -- sort field, 0 is low prior.
+  policy_id  integer NOT NULL DEFAULT '1' CHECK (policy_id >= 0),
+                                         -- JOINs with policy.id
+  email      bytea   NOT NULL UNIQUE,    -- email address, non-rfc2822-quoted
+  fullname   varchar(255) DEFAULT NULL  -- not used by amavisd-new
+  -- local   char(1)      -- Y/N  (optional field, see note further down)
+);
+
+-- any e-mail address (non- rfc2822-quoted), external or local,
+-- used as senders in wblist
+CREATE TABLE mailaddr (
+  id         serial  PRIMARY KEY,
+  priority   integer NOT NULL DEFAULT '7',  -- 0 is low priority
+  email      bytea   NOT NULL UNIQUE
+);
+
+-- per-recipient whitelist and/or blacklist,
+-- puts sender and recipient in relation wb  (white or blacklisted sender)
+CREATE TABLE wblist (
+  rid        integer NOT NULL CHECK (rid >= 0),  -- recipient: users.id
+  sid        integer NOT NULL CHECK (sid >= 0),  -- sender: mailaddr.id
+  wb         varchar(10) NOT NULL,  -- W or Y / B or N / space=neutral / score
+  PRIMARY KEY (rid,sid)
+);
+
+CREATE TABLE policy (
+  id  serial PRIMARY KEY,           -- 'id' this is the _only_ required field
+  policy_name      varchar(32),     -- not used by amavisd-new, a comment
+
+  virus_lover           char(1) default NULL,     -- Y/N
+  spam_lover            char(1) default NULL,     -- Y/N
+  unchecked_lovers_maps char(1) default NULL,     -- Y/N
+  banned_files_lover    char(1) default NULL,     -- Y/N
+  bad_header_lover      char(1) default NULL,     -- Y/N
+
+  bypass_virus_checks   char(1) default NULL,     -- Y/N
+  bypass_spam_checks    char(1) default NULL,     -- Y/N
+  bypass_banned_checks  char(1) default NULL,     -- Y/N
+  bypass_header_checks  char(1) default NULL,     -- Y/N
+
+  virus_quarantine_to      varchar(64) default NULL,
+  spam_quarantine_to       varchar(64) default NULL,
+  banned_quarantine_to     varchar(64) default NULL,
+  unchecked_quarantine_to  varchar(64) default NULL,
+  bad_header_quarantine_to varchar(64) default NULL,
+  clean_quarantine_to      varchar(64) default NULL,
+  archive_quarantine_to    varchar(64) default NULL,
+
+  spam_tag_level  real default NULL, -- higher score inserts spam info headers
+  spam_tag2_level real default NULL, -- inserts 'declared spam' header fields
+  spam_tag3_level real default NULL, -- inserts 'blatant spam' header fields
+  spam_kill_level real default NULL, -- higher score triggers evasive actions
+                                     -- e.g. reject/drop, quarantine, ...
+                                     -- (subject to final_spam_destiny setting)
+  spam_dsn_cutoff_level        real default NULL,
+  spam_quarantine_cutoff_level real default NULL,
+
+  addr_extension_virus      varchar(64) default NULL,
+  addr_extension_spam       varchar(64) default NULL,
+  addr_extension_banned     varchar(64) default NULL,
+  addr_extension_bad_header varchar(64) default NULL,
+
+  warnvirusrecip      char(1)     default NULL, -- Y/N
+  warnbannedrecip     char(1)     default NULL, -- Y/N
+  warnbadhrecip       char(1)     default NULL, -- Y/N
+  newvirus_admin      varchar(64) default NULL,
+  virus_admin         varchar(64) default NULL,
+  banned_admin        varchar(64) default NULL,
+  bad_header_admin    varchar(64) default NULL,
+  spam_admin          varchar(64) default NULL,
+  spam_subject_tag    varchar(64) default NULL,
+  spam_subject_tag2   varchar(64) default NULL,
+  spam_subject_tag3   varchar(64) default NULL,
+  message_size_limit  integer     default NULL, -- max size in bytes, 0 disable
+  banned_rulenames    varchar(64) default NULL, -- comma-separated list of ...
+        -- names mapped through %banned_rules to actual banned_filename tables
+  disclaimer_options  varchar(64) default NULL,
+  forward_method      varchar(64) default NULL,
+  sa_userconf         varchar(64) default NULL,
+  sa_username         varchar(64) default NULL
+);
+
+
+-- R/W part of the dataset (optional)
+--   May reside in the same or in a separate database as lookups database;
+--   REQUIRES SUPPORT FOR TRANSACTIONS; specified in @storage_sql_dsn
+--
+--  Please create additional indexes on keys when needed, or drop suggested
+--  ones as appropriate to optimize queries needed by a management application.
+--  See your database documentation for further optimization hints.
+
+-- provide unique id for each e-mail address, avoids storing copies
+CREATE TABLE maddr (
+  partition_tag integer   DEFAULT 0,   -- see $partition_tag
+  id         serial       PRIMARY KEY,
+  email      bytea        NOT NULL,    -- full e-mail address
+  domain     varchar(255) NOT NULL,    -- only domain part of the email address
+                                       -- with subdomain fields in reverse
+  CONSTRAINT part_email UNIQUE (partition_tag,email)
+);
+
+-- information pertaining to each processed message as a whole;
+-- NOTE: records with NULL msgs.content should be ignored by utilities,
+--   as such records correspond to messages just being processes, or were lost
+CREATE TABLE msgs (
+  partition_tag integer     DEFAULT 0,  -- see $partition_tag
+  mail_id     bytea         NOT NULL,   -- long-term unique mail id, dflt 12 ch
+  secret_id   bytea         DEFAULT '', -- authorizes release of mail_id, 12 ch
+  am_id       varchar(20)   NOT NULL,   -- id used in the log
+  time_num    integer NOT NULL CHECK (time_num >= 0),
+                                        -- rx_time: seconds since Unix epoch
+  time_iso timestamp WITH TIME ZONE NOT NULL,-- rx_time: ISO8601 UTC ascii time
+  sid         integer NOT NULL CHECK (sid >= 0), -- sender: maddr.id
+  policy      varchar(255)  DEFAULT '', -- policy bank path (like macro %p)
+  client_addr varchar(255)  DEFAULT '', -- SMTP client IP address (IPv4 or v6)
+  size        integer NOT NULL CHECK (size >= 0), -- message size in bytes
+  originating char(1) DEFAULT ' ' NOT NULL,  -- sender from inside or auth'd
+  content     char(1),                   -- content type: V/B/U/S/Y/M/H/O/T/C
+    -- virus/banned/unchecked/spam(kill)/spammy(tag2)/
+    -- /bad-mime/bad-header/oversized/mta-err/clean
+    -- is NULL on partially processed mail
+    -- (prior to 2.7.0 the CC_SPAMMY was logged as 's', now 'Y' is used;
+    --- to avoid a need for case-insenstivity in queries)
+  quar_type  char(1),                   -- quarantined as: ' '/F/Z/B/Q/M/L
+                                        --  none/file/zipfile/bsmtp/sql/
+                                        --  /mailbox(smtp)/mailbox(lmtp)
+  quar_loc   varchar(255)  DEFAULT '',  -- quarantine location (e.g. file)
+  dsn_sent   char(1),                   -- was DSN sent? Y/N/q (q=quenched)
+  spam_level real,                      -- SA spam level (no boosts)
+  message_id varchar(255)  DEFAULT '',  -- mail Message-ID header field
+  from_addr  varchar(255)  DEFAULT '',  -- mail From header field,    UTF8
+  subject    varchar(255)  DEFAULT '',  -- mail Subject header field, UTF8
+  host       varchar(255)  NOT NULL,    -- hostname where amavisd is running
+  PRIMARY KEY (partition_tag,mail_id)
+--FOREIGN KEY (sid) REFERENCES maddr(id) ON DELETE RESTRICT
+);
+CREATE INDEX msgs_idx_sid      ON msgs (sid);
+CREATE INDEX msgs_idx_mess_id  ON msgs (message_id); -- useful with pen pals
+CREATE INDEX msgs_idx_time_iso ON msgs (time_iso);
+CREATE INDEX msgs_idx_time_num ON msgs (time_num);   -- optional
+
+-- per-recipient information related to each processed message;
+-- NOTE: records in msgrcpt without corresponding msgs.mail_id record are
+--  orphaned and should be ignored and eventually deleted by external utilities
+CREATE TABLE msgrcpt (
+  partition_tag integer DEFAULT 0,  -- see $partition_tag
+  mail_id    bytea    NOT NULL,     -- (must allow duplicates)
+  rseqnum    integer  DEFAULT 0   NOT NULL, -- recip's enumeration within msg
+  rid        integer  NOT NULL,     -- recipient: maddr.id (duplicates allowed)
+  is_local   char(1)  DEFAULT ' ' NOT NULL, -- recip is: Y=local, N=foreign
+  content    char(1)  DEFAULT ' ' NOT NULL, -- content type V/B/U/S/Y/M/H/O/T/C
+  ds         char(1)  NOT NULL,     -- delivery status: P/R/B/D/T
+                                    -- pass/reject/bounce/discard/tempfail
+  rs         char(1)  NOT NULL,     -- release status: initialized to ' '
+  bl         char(1)  DEFAULT ' ',  -- sender blacklisted by this recip
+  wl         char(1)  DEFAULT ' ',  -- sender whitelisted by this recip
+  bspam_level real,                 -- per-recipient (total) spam level
+  smtp_resp  varchar(255) DEFAULT '', -- SMTP response given to MTA
+  PRIMARY KEY (partition_tag,mail_id,rseqnum)
+--FOREIGN KEY (rid)     REFERENCES maddr(id)     ON DELETE RESTRICT,
+--FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE
+);
+CREATE INDEX msgrcpt_idx_mail_id  ON msgrcpt (mail_id);
+CREATE INDEX msgrcpt_idx_rid      ON msgrcpt (rid);
+
+-- mail quarantine in SQL, enabled by $*_quarantine_method='sql:'
+-- NOTE: records in quarantine without corresponding msgs.mail_id record are
+--  orphaned and should be ignored and eventually deleted by external utilities
+CREATE TABLE quarantine (
+  partition_tag integer  DEFAULT 0,      -- see $partition_tag
+  mail_id    bytea   NOT NULL,           -- long-term unique mail id
+  chunk_ind  integer NOT NULL CHECK (chunk_ind >= 0), -- chunk number, 1..
+  mail_text  bytea   NOT NULL,           -- store mail as chunks of octects
+  PRIMARY KEY (partition_tag,mail_id,chunk_ind)
+--FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE
+);
+
+-- field msgrcpt.rs is primarily intended for use by quarantine management
+-- software; the value assigned by amavisd is a space;
+-- a short _preliminary_ list of possible values:
+--   'V' => viewed (marked as read)
+--   'R' => released (delivered) to this recipient
+--   'p' => pending (a status given to messages when the admin received the
+--                   request but not yet released; targeted to banned parts)
+--   'D' => marked for deletion; a cleanup script may delete it