# HG changeset patch # User Pascal Volk # Date 1208753761 0 # Node ID 55146c78b3fbf5e1f06720ba53e61d96f0c9a1af # Parent bf9a03c476fcd25dc31c04de80980ff0141ca072 * 'create_tables.pgsql' - Replaced column 'disabled' with columns smpt, pop3, imap and managesieve - updated view, added service columns smpt, pop3, imap and managesieve * 'update_tables_0.3.x-0.4.py' - Updated to consider the points above mentioned * 'vmm.cfg' - Added section »services« with options: smtp, pop3, imap and managesieve * 'update_config_0.3.x-0.4.py' * 'VirtualMailManager/Config.py' - Updated, to add new section »services« * 'VirtualMailManager/Account.py' * 'VirtualMailManager/VirtualMailManager.py' * 'vmm' - Modified, to fit new database structure * 'UPGRADE' * 'INSTALL' - Updated information * 'update_tables_0.4-dev_r24.py' - Added temporary to the repository diff -r bf9a03c476fc -r 55146c78b3fb ChangeLog --- a/ChangeLog Fri Apr 18 03:46:11 2008 +0000 +++ b/ChangeLog Mon Apr 21 04:56:01 2008 +0000 @@ -1,4 +1,29 @@ === 0.0.0 === +2008-04-21 Pascal Volk + + * create_tables.pgsql (table users): + Replaced column 'disabled' with columns smpt, pop3, imap and managesieve + * create_tables.pgsql (view dovecot_password): + updated view, added service columns smpt, pop3, imap and managesieve + * update_tables_0.3.x-0.4.py: + Updated to consider the points above mentioned + * vmm.cfg: + Added section »services« with options: smtp, pop3, imap and managesieve + * update_config_0.3.x-0.4.py: + * VirtualMailManager/Config.py: + Updated, to add new section »services« + * VirtualMailManager/Account.py (Account._switchState(), Account.enable(), + Account.disable(), Account.save(), Account.getInfo()): + * VirtualMailManager/VirtualMailManager.py (VirtualMailManager.user_add(), + VirtualMailManager.user_disable(), VirtualMailManager.user_enable()): + * vmm (usage(), user_disable(), user_enable()): + Modified, to fit new database structure + * UPGRADE: + * INSTALL: + Updated information + * update_tables_0.4-dev_r24.py: + Added temporary to the repository + 2008-04-18 Pascal Volk * VirtualMailManager/Account.py: Implemented getAccountByID() diff -r bf9a03c476fc -r 55146c78b3fb INSTALL --- a/INSTALL Fri Apr 18 03:46:11 2008 +0000 +++ b/INSTALL Mon Apr 21 04:56:01 2008 +0000 @@ -93,7 +93,7 @@ driver = pgsql 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' + password_query = SELECT "user", password FROM dovecot_password WHERE "user"='%u' AND %Ls user_query = SELECT home, uid, gid, 'maildir:'||mail AS mail FROM dovecot_user WHERE userid = '%u' Provide a root SETUID copy of Dovecot's deliver agent for Postfix diff -r bf9a03c476fc -r 55146c78b3fb UPGRADE --- a/UPGRADE Fri Apr 18 03:46:11 2008 +0000 +++ b/UPGRADE Mon Apr 21 04:56:01 2008 +0000 @@ -5,11 +5,14 @@ * backup/dump your database! * execute upgrade.sh * set permissions for replaced views: - GRANT SELECT ON dovecot_user TO your_dovecot_dbuser; + GRANT SELECT ON dovecot_user, dovecot_password 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' + SELECT home, uid, gid, mail FROM dovecot_user WHERE userid='%u' + * update password_query in /etc/dovecot/dovecot-sql.conf: + password_query = \ + SELECT "user", password FROM dovecot_password WHERE "user"='%u' AND %Ls * 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 bf9a03c476fc -r 55146c78b3fb VirtualMailManager/Account.py --- a/VirtualMailManager/Account.py Fri Apr 18 03:46:11 2008 +0000 +++ b/VirtualMailManager/Account.py Mon Apr 21 04:56:01 2008 +0000 @@ -83,15 +83,28 @@ self._setID() self._mid = MailLocation(self._dbh, maillocation=maillocation).getID() - def _switchState(self, state): + def _switchState(self, state, service): if not isinstance(state, bool): return False + if not service in ['smtp', 'pop3', 'imap', 'managesieve', 'all', None]: + raise VMMAccountException(("Unknown service »%s«" % service, + ERR.UNKNOWN_SERVICE)) if self._uid < 1: raise VMMAccountException(("Account doesn't exists", ERR.NO_SUCH_ACCOUNT)) dbc = self._dbh.cursor() - dbc.execute("""UPDATE users SET disabled=%s WHERE local_part=%s\ - AND gid=%s""", state, self._localpart, self._gid) + if service in ['smtp', 'pop3', 'imap', 'managesieve']: + dbc.execute( + "UPDATE users SET %s=%s WHERE local_part='%s' AND gid=%s" + % (service, state, self._localpart, self._gid)) + elif state: + dbc.execute("UPDATE users SET smtp = TRUE, pop3 = TRUE,\ + imap = TRUE, managesieve = TRUE WHERE local_part = %s AND gid = %s", + self._localpart, self._gid) + else: + dbc.execute("UPDATE users SET smtp = FALSE, pop3 = FALSE,\ + imap = FALSE, managesieve = FALSE WHERE local_part = %s AND gid = %s", + self._localpart, self._gid) if dbc.rowcount > 0: self._dbh.commit() dbc.close() @@ -108,19 +121,21 @@ elif directory == 'home': return '%s/%i' % (self._base, self._uid) - def enable(self): - self._switchState(False) + def enable(self, service=None): + self._switchState(True, service) - def disable(self): - self._switchState(True) + def disable(self, service=None): + self._switchState(False, service) - def save(self, maillocation): + def save(self, maillocation, smtp, pop3, imap, managesieve): if self._uid < 1: self._prepare(maillocation) dbc = self._dbh.cursor() dbc.execute("""INSERT INTO users (local_part, passwd, uid, gid,\ - mid, tid) VALUES (%s, %s, %s, %s, %s, %s)""", self._localpart, self._passwd, - self._uid, self._gid, self._mid, self._tid) + mid, tid, smtp, pop3, imap, managesieve)\ + VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""", + self._localpart, self._passwd, self._uid, self._gid, self._mid, + self._tid, smtp, pop3, imap, managesieve) self._dbh.commit() dbc.close() else: @@ -150,20 +165,23 @@ def getInfo(self): dbc = self._dbh.cursor() - dbc.execute("SELECT name, uid, gid, mid, tid, disabled FROM users\ - WHERE local_part=%s AND gid=%s", self._localpart, self._gid) + dbc.execute("SELECT name, uid, gid, mid, tid, smtp, pop3, imap, \ + managesieve FROM users WHERE local_part=%s AND gid=%s", + self._localpart, self._gid) info = dbc.fetchone() dbc.close() if info is None: raise VMMAccountException(("Account doesn't exists", ERR.NO_SUCH_ACCOUNT)) else: - keys = ['name', 'uid', 'gid', 'maildir', 'transport', 'disabled'] + keys = ['name', 'uid', 'gid', 'maildir', 'transport', 'smtp', + 'pop3', 'imap', 'managesieve'] info = dict(zip(keys, info)) - if bool(info['disabled']): - info['disabled'] = 'Yes' - else: - info['disabled'] = 'No' + for service in ['smtp', 'pop3', 'imap', 'managesieve']: + if bool(info[service]): + info[service] = 'enabled' + else: + info[service] = 'disabled' info['address'] = self._addr info['maildir'] = '%s/%s/%s' % (self._base, info['uid'], MailLocation(self._dbh, diff -r bf9a03c476fc -r 55146c78b3fb VirtualMailManager/Config.py --- a/VirtualMailManager/Config.py Fri Apr 18 03:46:11 2008 +0000 +++ b/VirtualMailManager/Config.py Mon Apr 21 04:56:01 2008 +0000 @@ -58,6 +58,12 @@ ['diskusage', 'false'], ['delete', 'false'] ] + self.__serviceopts = [ + ['smtp', 'true'], + ['pop3', 'true'], + ['imap', 'true'], + ['managesieve', 'true'] + ] self.__domdopts = [ ['mode', 504], ['delete', 'false'] @@ -158,6 +164,8 @@ opts = self.__dbopts elif section == 'maildir': opts = self.__mdopts + elif section == 'services': + opts = self.__serviceopts elif section == 'domdir': opts = self.__domdopts elif section == 'bin': diff -r bf9a03c476fc -r 55146c78b3fb VirtualMailManager/VirtualMailManager.py --- a/VirtualMailManager/VirtualMailManager.py Fri Apr 18 03:46:11 2008 +0000 +++ b/VirtualMailManager/VirtualMailManager.py Mon Apr 21 04:56:01 2008 +0000 @@ -460,7 +460,11 @@ def user_add(self, emailaddress, password): acc = self.__getAccount(emailaddress, password) - acc.save(self.__Cfg.get('maildir', 'folder')) + acc.save(self.__Cfg.get('maildir', 'folder'), + self.__Cfg.getboolean('services', 'smtp'), + self.__Cfg.getboolean('services', 'pop3'), + self.__Cfg.getboolean('services', 'imap'), + self.__Cfg.getboolean('services', 'managesieve')) self.__maildirmake(acc.getDir('domain'), acc.getUID(), acc.getGID()) def alias_add(self, aliasaddress, targetaddress): @@ -507,13 +511,13 @@ acc = self.__getAccount(emailaddress) acc.modify('transport', transport) - def user_disable(self, emailaddress): + def user_disable(self, emailaddress, service=None): acc = self.__getAccount(emailaddress) - acc.disable() + acc.disable(service) - def user_enable(self, emailaddress): + def user_enable(self, emailaddress, service=None): acc = self.__getAccount(emailaddress) - acc.enable() + acc.enable(service) def __del__(self): if not self.__dbh is None and self.__dbh._isOpen: diff -r bf9a03c476fc -r 55146c78b3fb VirtualMailManager/constants/ERROR.py --- a/VirtualMailManager/constants/ERROR.py Fri Apr 18 03:46:11 2008 +0000 +++ b/VirtualMailManager/constants/ERROR.py Mon Apr 21 04:56:01 2008 +0000 @@ -37,4 +37,5 @@ NO_SUCH_DOMAIN = 50 TRANSPORT_INIT = 51 UNKNOWN_MAILLOCATION_ID = 52 -UNKNOWN_TRANSPORT_ID = 53 +UNKNOWN_SERVICE = 53 +UNKNOWN_TRANSPORT_ID = 54 diff -r bf9a03c476fc -r 55146c78b3fb VirtualMailManager/constants/EXIT.py --- a/VirtualMailManager/constants/EXIT.py Fri Apr 18 03:46:11 2008 +0000 +++ b/VirtualMailManager/constants/EXIT.py Mon Apr 21 04:56:01 2008 +0000 @@ -5,5 +5,5 @@ # $Id$ MISSING_ARGS = 1 -UNKNOWN_OPTION = 2 +UNKNOWN_COMMAND = 2 USER_INTERRUPT = 3 diff -r bf9a03c476fc -r 55146c78b3fb create_tables.pgsql --- a/create_tables.pgsql Fri Apr 18 03:46:11 2008 +0000 +++ b/create_tables.pgsql Mon Apr 21 04:56:01 2008 +0000 @@ -53,7 +53,10 @@ gid bigint NOT NULL, mid bigint NOT NULL DEFAULT 1, tid bigint NOT NULL DEFAULT 1, - disabled boolean NOT NULL DEFAULT FALSE, + smpt boolean NOT NULL DEFAULT TRUE, + pop3 boolean NOT NULL DEFAULT TRUE, + imap boolean NOT NULL DEFAULT TRUE, + managesieve boolean NOT NULL DEFAULT TRUE, CONSTRAINT pkye_users PRIMARY KEY (local_part, gid), CONSTRAINT ukey_users_uid UNIQUE (uid), CONSTRAINT fkey_users_gid_domains FOREIGN KEY (gid) @@ -84,7 +87,7 @@ CREATE OR REPLACE VIEW dovecot_password AS SELECT local_part || '@' || domains.domainname AS "user", - passwd AS "password" + passwd AS "password", smtp, pop3, imap, managesieve FROM users LEFT JOIN domains USING (gid); diff -r bf9a03c476fc -r 55146c78b3fb update_config_0.3.x-0.4.py --- a/update_config_0.3.x-0.4.py Fri Apr 18 03:46:11 2008 +0000 +++ b/update_config_0.3.x-0.4.py Mon Apr 21 04:56:01 2008 +0000 @@ -10,8 +10,14 @@ cf = ConfigParser() cf.readfp(cff) cff.close() -if not cf.has_option('misc', 'transport'): + +if not cf.has_option('misc', 'transport') or not cf.has_section('services'): cff = file('/usr/local/etc/vmm.cfg', 'w') - cf.set('misc', 'transport', 'dovecot:') + if not cf.has_option('misc', 'transport'): + cf.set('misc', 'transport', 'dovecot:') + if not cf.has_section('services'): + cf.add_section('services') + for service in ['smtp', 'pop3', 'imap', 'managesieve']: + cf.set('services', service, 'true') cf.write(cff) cff.close() diff -r bf9a03c476fc -r 55146c78b3fb update_tables_0.3.x-0.4.py --- a/update_tables_0.3.x-0.4.py Fri Apr 18 03:46:11 2008 +0000 +++ b/update_tables_0.3.x-0.4.py Mon Apr 21 04:56:01 2008 +0000 @@ -70,6 +70,10 @@ dbh.commit() dbc.execute("ALTER TABLE users ADD tid bigint NOT NULL DEFAULT 1") dbh.commit() +for service in ['smtp', 'pop3', 'imap', 'managesieve']: + dbc.execute( + "ALTER TABLE users ADD %s boolean NOT NULL DEFAULT TRUE" % service) + dbh.commit() dbc.execute("ALTER TABLE users ADD CONSTRAINT fkey_users_mid_maillocation \ FOREIGN KEY (mid) REFERENCES maillocation (mid)") dbh.commit() @@ -92,6 +96,14 @@ maillocation) dbh.commit() +dbc.execute("SELECT uid FROM users WHERE disabled") +res = dbc.fetchall() +if len(res): + for uid in res: + dbc.execute("UPDATE users SET smtp = FALSE, pop3 = FALSE, imap = FALSE,\ + managesieve = FALSE WHERE uid = %s", uid[0]) + dbh.commit() + dbc.execute("SELECT gid, tid FROM domains") res = dbc.fetchall() for gid, tid in res: @@ -120,11 +132,23 @@ LEFT JOIN maillocation USING (mid);""") dbh.commit() +# Replace VIEW dovecot_password +dbc.execute("DROP VIEW dovecot_password") +dbh.commit() +dbc.execute("""CREATE OR REPLACE VIEW dovecot_password AS + SELECT local_part || '@' || domains.domainname AS "user", + passwd AS "password", smtp, pop3, imap, managesieve + FROM users + LEFT JOIN domains USING (gid)""") +dbh.commit() + # fix table users (Part II) dbc.execute("ALTER TABLE users DROP home") dbh.commit() dbc.execute("ALTER TABLE users DROP mail") dbh.commit() +dbc.execute("ALTER TABLE users DROP disabled") +dbh.commit() # Replace VIEW postfix_transport diff -r bf9a03c476fc -r 55146c78b3fb update_tables_0.4-dev_r24.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/update_tables_0.4-dev_r24.py Mon Apr 21 04:56:01 2008 +0000 @@ -0,0 +1,51 @@ +#!/usr/bin/env python +# -*- coding: UTF-8 -*- +# Copyright 2008 VEB IT +# See COPYING for distribution information. +# $Id$ + +from ConfigParser import ConfigParser +from pyPgSQL import PgSQL + +cff = file('/usr/local/etc/vmm.cfg', 'r') +cf = ConfigParser() +cf.readfp(cff) +cff.close() + +dbh = PgSQL.connect(database=cf.get('database', 'name'), + user=cf.get('database', 'user'), host=cf.get('database', 'host'), + password=cf.get('database', 'pass'), client_encoding='utf8', + unicode_results=True) +dbc = dbh.cursor() +dbc.execute("SET NAMES 'UTF8'") + +for service in ['smtp', 'pop3', 'imap', 'managesieve']: + dbc.execute( + "ALTER TABLE users ADD %s boolean NOT NULL DEFAULT TRUE" % service) + dbh.commit() + +dbc.execute("SELECT uid FROM users WHERE disabled") +res = dbc.fetchall() +if len(res): + for uid in res: + dbc.execute("UPDATE users SET smtp = FALSE, pop3 = FALSE, imap = FALSE, managesieve = FALSE WHERE uid = %s", uid[0]) + dbh.commit() +dbc.execute("ALTER TABLE users DROP disabled") +dbh.commit() + +dbc.execute("DROP VIEW dovecot_password") +dbh.commit() +dbc.execute("""CREATE OR REPLACE VIEW dovecot_password AS + SELECT local_part || '@' || domains.domainname AS "user", + passwd AS "password", smtp, pop3, imap, managesieve + FROM users + LEFT JOIN domains USING (gid)""") +dbh.commit() +dbh.close() + +# print importnat information +print +print "* set permissions for replaced views:" +print "connect to your database [psql %s] and execute:" % cf.get('database', +'name') +print "GRANT SELECT ON dovecot_password TO your_dovecot_dbuser;" diff -r bf9a03c476fc -r 55146c78b3fb vmm --- a/vmm Fri Apr 18 03:46:11 2008 +0000 +++ b/vmm Mon Apr 21 04:56:01 2008 +0000 @@ -26,9 +26,9 @@ def usage(excode=0, errMsg=None): sys.stderr.write("""\ -Usage: %s OPTION OBJECT ARGS* +Usage: %s COMMAND OBJECT ARGS* short long - option object args (* = optional) + command object args (* = optional) da domainadd domain.tld transport* di domaininfo domain.tld detailed* @@ -39,8 +39,8 @@ un username user@domain.tld 'Users Name' up userpassword user@domain.tld password* ut usertransport user@domain.tld transport - u0 userdisable user@domain.tld - u1 userenable user@domain.tld + u0 userdisable user@domain.tld smtp*|pop3*|imap*|managesieve*|all* + u1 userenable user@domain.tld smtp*|pop3*|imap*|managesieve*|all* ud userdelete user@domain.tld aa aliasadd alias@domain.tld user@domain.tld ai aliasinfo alias@domain.tld @@ -209,15 +209,19 @@ global argc if argc < 3: usage(EXIT.MISSING_ARGS, 'Missing e-mail address.') + elif argc < 4: + vmm.user_enable(sys.argv[2].lower()) else: - vmm.user_enable(sys.argv[2].lower()) + vmm.user_enable(sys.argv[2].lower(), sys.argv[3].lower()) def user_disable(): global argc if argc < 3: usage(EXIT.MISSING_ARGS, 'Missing e-mail address.') + elif argc < 4: + vmm.user_disable(sys.argv[2].lower()) else: - vmm.user_disable(sys.argv[2].lower()) + vmm.user_disable(sys.argv[2].lower(), sys.argv[3].lower()) def user_password(): global argc @@ -319,7 +323,7 @@ print "%s, version %s (%s from %s)\n" % (__prog__, __version__, __revision__, __date__) else: - usage(EXIT.UNKNOWN_OPTION, 'Unknown option: »%s«' % sys.argv[1]) + usage(EXIT.UNKNOWN_COMMAND, 'Unknown command: »%s«' % sys.argv[1]) showWarnings() except (EOFError, KeyboardInterrupt): sys.stderr.write('\nOuch!\n') diff -r bf9a03c476fc -r 55146c78b3fb vmm.cfg --- a/vmm.cfg Fri Apr 18 03:46:11 2008 +0000 +++ b/vmm.cfg Mon Apr 21 04:56:01 2008 +0000 @@ -33,6 +33,19 @@ delete = false # +# Services per user +# +[services] +; allow smtp by default? (Boolean) +smtp = true +; allow pop3 by default? (Boolean) +pop3 = true +; allow imap by default? (Boolean) +imap = true +; allow managesieve by default? (Boolean) +managesieve = true + +# # domain directory settings # [domdir]