--- /dev/null
+main.cf
+
+#
+# Mailserver mit PostgreSQL
+#
+virtual_mailbox_domains = pgsql:/etc/postfix/virtual-mailbox-domains.cf
+virtual_mailbox_maps = pgsql:/etc/postfix/virtual-mailbox-maps.cf
+virtual_alias_maps = pgsql:/etc/postfix/virtual-alias-maps.cf
+virtual_uid_maps = static:112
+virtual_gid_maps = static:116
+# mailbox_command = /usr/lib/dovecot/deliver
+# mailbox_command = /usr/bin/spamc -e /usr/lib/dovecot/deliver
+virtual_transport = dovecot
+# dovecot_destination_recipient_limit = 1
+
+virtual-alias-maps.cf
+
+user = vmail
+password = SECRET
+hosts = localhost
+dbname = void
+query = SELECT forward
+ FROM vmail_user u
+ JOIN vmail_domain d ON u.vmail_domain_id=d.id
+ WHERE u.username || '@' || d.name = '%s' AND forward IS NOT NULL AND forward <> '' AND active = 1
+
+virtual-mailbox-domains.cf
+
+user = vmail
+password = SECRET
+hosts = localhost
+dbname = void
+query = SELECT 1
+ FROM vmail_domain
+ WHERE name = '%s'
+
+virtual-mailbox-maps.cf
+
+user = vmail
+password = SECRET
+hosts = localhost
+dbname = void
+query = SELECT 1
+ FROM vmail_user u
+ JOIN vmail_domain d ON u.vmail_domain_id=d.id
+ WHERE lower(u.username) || '@' || lower(d.name) = lower('%s') AND (forward IS NULL OR forward = '') AND active = 1
+
+/etc/dovecot/dovecot-sql.conf.ext
+
+driver = pgsql
+connect = host=localhost dbname=void user=vmail password=hfDcHCxXeVPhahjX
+default_pass_scheme = PLAIN-MD5
+# SELECT home, uid, gid FROM users WHERE username = '%n' AND domain = '%d')
+password_query = SELECT password \
+ FROM vmail_user v \
+ JOIN vmail_domain d ON v.vmail_domain_id=d.id \
+ WHERE v.username='%n' AND d.name='%d' AND password IS NOT NULL AND active = 1
+
+/etc/dovecot/conf.d/10-mail.conf
+
+first_valid_uid = 112
+
--- /dev/null
+<?php
+
+
+$buttons = <<<EOC
+<p style="margin-top: 5px; margin-bottom: 4px; text-align: center;">
+<button onclick="return passwd_delete()">Passwort löschen</button>
+<button onclick="return passwd_set()">Passwort setzen</button>
+</p>
+EOC;
+
+$jscode[] = <<<EOC
+function passwd_delete_callback(data)
+{
+ grid_update(grid);
+ info('Preis gespeichert');
+}
+
+function passwd_delete()
+{
+ var eid = document.getElementById('edit_id');
+ if (!eid || !eid.value.length) return false;
+
+ var source = document.getElementById('source');
+ if (!source) return false;
+
+ var parms = 'source=' + source.innerHTML + '&callback=delete&';
+ parms += 'id=' + eid.value;
+
+ ajax_request('function', parms, passwd_delete_callback);
+
+ return false;
+}
+
+var passwd_popup = false;
+function passwd_setpw_callback(data)
+{
+ grid_update(grid);
+ info('Neues Passwort gespeichert');
+}
+
+function passwd_setpw()
+{
+ passwd_popup.closePopup();
+
+ var pw1 = document.getElementById('pw_pass1');
+ var pw2 = document.getElementById('pw_pass2');
+
+ if (!pw1.value.length || !pw2.value.length || pw1.value != pw2.value) {
+ alert("Die Passwörter stimmen nicht überein!");
+ return false;
+ }
+
+ var source = document.getElementById('source');
+ var pw_id = document.getElementById('pw_id');
+ var pw_pass = document.getElementById('pw_pass1');
+
+ var parms = 'source=' + source.innerHTML + '&callback=setpw';
+ parms += '&id=' + pw_id.value;
+ parms += '&passwd=' + pw_pass.value;
+
+ ajax_request('function', parms, passwd_setpw_callback);
+
+ return false;
+}
+
+function passwd_set()
+{
+ var edit_id = document.getElementById('edit_id');
+
+ if (!edit_id.value.length) return false;
+
+ var width = 245;
+ var height = 163;
+
+ if (!passwd_popup) {
+ var options = {hideOnClick: false, canDragFunc: true };
+ passwd_popup = new Rico.Popup(options);
+ passwd_popup.createWindow('<b>Neues Passwort setzen</b>','',height+'px',width+'px');
+ passwd_popup.contentDiv.style.backgroundColor='#e0e0e0';
+ passwd_popup.contentDiv.innerHTML = [
+ '<div class="form" style="margin-left: 10px; margin-right: 12px; padding-left: 10px; padding-bottom: 7px;">',
+ '<input type="hidden" name="pw_id" id="pw_id">',
+ '<label for="pw_email">E-Mail Adresse:</label><br>',
+ '<input type="text" name="pw_email" id="pw_email" size="23" readonly>',
+ '<div style="height: 5px;"></div>',
+ '<label for="pw_pass1">Neues Passwort:</label><br>',
+ '<input type="password" name="pw_pass1" id="pw_pass1" size="23">',
+ '<div style="height: 5px;"></div>',
+ '<label for="pw_pass2">erneut eingeben:</label><br>',
+ '<input type="password" name="pw_pass2" id="pw_pass2" size="23">',
+ '<div style="height: 5px;"></div>',
+ '<button onclick="return passwd_setpw()">Passwort setzen</button>',
+ '</div>',
+ ].join('');
+ }
+
+ var edit_username = document.getElementById('edit_username');
+ var edit_vmail_domain_id = document.getElementById('edit_vmail_domain_id');
+
+ var pw_id = document.getElementById('pw_id');
+ var pw_email = document.getElementById('pw_email');
+
+ pw_id.value = edit_id.value;
+ pw_email.value = edit_username.value + '@' + edit_vmail_domain_id.options[edit_vmail_domain_id.selectedIndex].innerHTML;
+
+ var x = Math.floor((RicoUtil.windowWidth()-width)/2);
+ var y = Math.floor((RicoUtil.windowHeight()-height)/2);
+ passwd_popup.openPopup(x,y);
+
+ var pw_pass1 = document.getElementById('pw_pass1');
+ pw_pass1.focus();
+
+ return false;
+}
+EOC;
+
+$mask = array(
+ 'table' => 'vmail_user',
+ 'title' => 'Mailboxen und Weiterleitungen',
+ 'join' => array('vmail_domain ON vmail_domain_id = vmail_domain.id'),
+ 'list' => array(
+ 'id' => array(
+ 'name' => 'ID',
+ 'visible' => false,
+ 'sql' => 'vmail_user.id',
+ ),
+ 'email' => array(
+ 'name' => 'E-Mail',
+ 'width' => 330,
+ 'type' => 'text',
+ 'sql' => "username || '@' || name",
+ 'specs' => "filterUI: 't'",
+ ),
+ 'forward' => array(
+ 'name' => 'Forward',
+ 'width' => 330,
+ 'specs' => "filterUI: 't'",
+ ),
+ 'pw' => array(
+ 'name' => 'Pass',
+ 'width' => 40,
+ 'specs' => "ClassName: 'aligncenter'",
+ 'control' => "new Rico.TableColumn.checkbox('t', 'f',0,1)",
+ 'sql' => 'password IS NOT NULL AND length(password) > 0',
+ ),
+ 'active' => array(
+ 'name' => 'on',
+ 'width' => 40,
+ 'specs' => "ClassName: 'aligncenter'",
+ 'control' => "new Rico.TableColumn.checkbox(1, 0,0,1)",
+ ),
+ ),
+ 'edit' => array(
+ 'username' => array(
+ 'name' => 'Usename',
+ 'type' => 'text',
+ 'size' => 21,
+ 'required' => true,
+ ),
+ 'vmail_domain_id' => array(
+ 'name' => 'Domain',
+ 'type' => 'select',
+ 'options' => 'SELECT id,name AS text FROM vmail_domain ORDER BY name',
+ 'option_empty' => '-- select --',
+ 'required' => true,
+ ),
+ 'forward' => array(
+ 'name' => 'Forward',
+ 'type' => 'text',
+ 'size' => 21,
+ 'null' => true,
+ ),
+/*
+ 'password' => array(
+ 'name' => 'Passwort',
+ 'type' => 'passwd',
+ 'size' => 21,
+ 'null' => true,
+ 'func' => 'passwd_encrypt',
+ ),
+*/
+ 'active' => array(
+ 'name' => 'aktiviert',
+ 'type' => 'boolean',
+ ),
+ 'buttons' => array(
+ 'type' => 'html',
+ 'code' => $buttons,
+ 'sql' => false,
+ ),
+ ),
+ 'callbacks' => array(
+ 'delete' => 'cb_delete',
+ 'setpw' => 'cb_setpw',
+ ),
+ );
+
+function passwd_encrypt($pw)
+{
+ return md5($pw);
+}
+
+function cb_delete()
+{
+ global $db;
+
+ $sql = sprintf("UPDATE vmail_user SET password = NULL WHERE id = %d", $_POST['id']);
+
+ $sth = $db->query($sql);
+
+ return true;
+}
+
+function cb_setpw()
+{
+ global $db;
+
+ $sql = sprintf("UPDATE vmail_user SET password = '%s' WHERE id = %d",
+ passwd_encrypt($_POST['passwd']),
+ $_POST['id']);
+
+ $sth = $db->query($sql);
+
+ return true;
+}
+
+?>
--- /dev/null
+CREATE TABLE vmail_domain (
+ id SERIAL,
+ name character varying(100) NOT NULL,
+ sys_user character varying(10) NOT NULL,
+ sys_edit timestamp without time zone NOT NULL
+);
+
+CREATE UNIQUE INDEX vmail_domain_id ON vmail_domain USING btree (id);
+
+CREATE TABLE vmail_user (
+ id SERIAL,
+ vmail_domain_id integer NOT NULL,
+ username character varying(50) NOT NULL,
+ password character varying(50),
+ forward character varying(150),
+ active integer DEFAULT 0 NOT NULL,
+ sys_user character varying(10) NOT NULL,
+ sys_edit timestamp without time zone NOT NULL
+);
+
+CREATE UNIQUE INDEX vmail_user_id ON vmail_user USING btree (id);
+CREATE INDEX vmail_user_vmail_domain_id ON vmail_user USING btree (vmail_domain_id);
+
+REVOKE ALL ON TABLE vmail_user FROM PUBLIC;
+GRANT SELECT ON TABLE vmail_user TO "vmail";
+GRANT ALL ON TABLE vmail_user TO "hallinta";
+
+REVOKE ALL ON SEQUENCE vmail_user_id_seq FROM PUBLIC;
+GRANT ALL ON SEQUENCE vmail_user_id_seq TO "hallinta";
+
+REVOKE ALL ON TABLE vmail_domain FROM PUBLIC;
+GRANT SELECT ON TABLE vmail_domain TO "vmail";
+GRANT ALL ON TABLE vmail_domain TO "hallinta";
+
+REVOKE ALL ON SEQUENCE vmail_domain_id_seq FROM PUBLIC;
+GRANT ALL ON SEQUENCE vmail_domain_id_seq TO "hallinta";
+
--- /dev/null
+DROP TABLE vmail_user ;
+DROP TABLE vmail_domain ;
+CREATE TABLE vmail_domain (
+ id SERIAL,
+ name character varying(100) NOT NULL,
+ sys_user character varying(10) NOT NULL,
+ sys_edit timestamp without time zone NOT NULL
+);
+
+CREATE UNIQUE INDEX vmail_domain_id ON vmail_domain USING btree (id);
+
+CREATE TABLE vmail_user (
+ id SERIAL,
+ vmail_domain_id integer NOT NULL,
+ username character varying(50) NOT NULL,
+ password character varying(50),
+ forward character varying(150),
+ active integer DEFAULT 0 NOT NULL,
+ sys_user character varying(10) NOT NULL,
+ sys_edit timestamp without time zone NOT NULL
+);
+
+CREATE UNIQUE INDEX vmail_user_id ON vmail_user USING btree (id);
+CREATE INDEX vmail_user_vmail_domain_id ON vmail_user USING btree (vmail_domain_id);
+
+REVOKE ALL ON TABLE vmail_user FROM PUBLIC;
+REVOKE ALL ON TABLE vmail_user FROM joey;
+GRANT ALL ON TABLE vmail_user TO joey;
+GRANT ALL ON TABLE vmail_user TO "www-data";
+
+REVOKE ALL ON SEQUENCE vmail_user_id_seq FROM PUBLIC;
+REVOKE ALL ON SEQUENCE vmail_user_id_seq FROM joey;
+GRANT ALL ON SEQUENCE vmail_user_id_seq TO joey;
+GRANT ALL ON SEQUENCE vmail_user_id_seq TO "www-data";
+
+REVOKE ALL ON TABLE vmail_domain FROM PUBLIC;
+REVOKE ALL ON TABLE vmail_domain FROM joey;
+GRANT ALL ON TABLE vmail_domain TO joey;
+GRANT ALL ON TABLE vmail_domain TO "www-data";
+
+REVOKE ALL ON SEQUENCE vmail_domain_id_seq FROM PUBLIC;
+REVOKE ALL ON SEQUENCE vmail_domain_id_seq FROM joey;
+GRANT ALL ON SEQUENCE vmail_domain_id_seq TO joey;
+GRANT ALL ON SEQUENCE vmail_domain_id_seq TO "www-data";
+
+INSERT INTO vmail_domain (name,sys_user,sys_edit) VALUES ('crossvoid.de','joey',now());
+INSERT INTO vmail_user (vmail_domain_id,username,password,sys_user,sys_edit) VALUES (1,'joey',md5('foobarbaz'),'joey',now());
projects / misc / hallinta-vmail / commitdiff