CREATE SEQUENCE transport_id;CREATE TABLE transport ( tid bigint NOT NULL DEFAULT nextval('transport_id'), transport varchar(270) NOT NULL, -- smtps:[255-char.host.name:50025] CONSTRAINT pkey_transport PRIMARY KEY (tid), CONSTRAINT ukey_transport UNIQUE (transport));INSERT INTO transport(transport) VALUES ('dovecot:');CREATE SEQUENCE maillocation_id;CREATE TABLE maillocation( mid bigint NOT NULL DEFAULT nextval('maillocation_id'), maillocation varchar(20) NOT NULL, CONSTRAINT pkey_maillocation PRIMARY KEY (mid), CONSTRAINT ukey_maillocation UNIQUE (maillocation));INSERT INTO maillocation(maillocation) VALUES ('Maildir');CREATE SEQUENCE domains_gid START WITH 70000 INCREMENT BY 1 MINVALUE 70000 MAXVALUE 4294967294 NO CYCLE;CREATE SEQUENCE users_uid START WITH 70000 INCREMENT BY 1 MINVALUE 70000 MAXVALUE 4294967294 NO CYCLE;CREATE TABLE domains ( gid bigint NOT NULL DEFAULT nextval('domains_gid'), tid bigint NOT NULL DEFAULT 1, domainname varchar(255) NOT NULL, domaindir varchar(40) NOT NULL, CONSTRAINT pkey_domains PRIMARY KEY (gid), CONSTRAINT ukey_domains UNIQUE (domainname), CONSTRAINT fkey_domains_tid_transport FOREIGN KEY (tid) REFERENCES transport (tid));CREATE TABLE alias_domains ( domainname varchar(255) NOT NULL, gid bigint NOT NULL, CONSTRAINT pkey_alias_domains PRIMARY KEY (domainname), CONSTRAINT fkey_alias_domains_gid_domains FOREIGN KEY (gid) REFERENCES domains (gid));CREATE TABLE users ( local_part varchar(64) NOT NULL, passwd varchar(74) NOT NULL, name varchar(128) NULL, uid bigint NOT NULL DEFAULT nextval('users_uid'), gid bigint NOT NULL, mid bigint NOT NULL DEFAULT 1, tid bigint NOT NULL DEFAULT 1, smtp 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) REFERENCES domains (gid), CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid) REFERENCES maillocation (mid), CONSTRAINT fkey_users_tid_transport FOREIGN KEY (tid) REFERENCES transport (tid));CREATE LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION getdomain(name varchar(255)) RETURNS domains AS $$DECLARE dom domains;BEGIN SELECT INTO dom * FROM domains WHERE domainname = name; IF NOT FOUND THEN SELECT INTO dom * FROM domains WHERE gid = (SELECT gid FROM alias_domains WHERE domainname = name); END IF; RETURN dom;END;$$ LANGUAGE plpgsql;CREATE TYPE dovecot_user AS ( userid text, uid bigint, gid bigint, home text, mail text);CREATE OR REPLACE FUNCTION get_dovecot_user( lpart varchar, dpart varchar) RETURNS dovecot_user AS $$DECLARE du dovecot_user;BEGIN SELECT INTO STRICT du local_part || '@' || (getdomain(dpart)).domainname AS userid, uid, gid, (getdomain(dpart)).domaindir || '/' || uid AS home, '~/' || maillocation.maillocation AS mail FROM users LEFT JOIN domains USING (gid) LEFT JOIN maillocation USING (mid) WHERE local_part = lpart AND (getdomain(dpart)).gid = users.gid; RETURN du; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE EXCEPTION 'No such user: %@%', lpart, dpart;END;$$ LANGUAGE plpgsql STABLEEXTERNAL SECURITY INVOKER;-- ----- Example:-- ----- -----| SELECT * FROM get_dovecot_user('a.user', 'a-hoernchen.de');--| userid | uid | gid | home | mail --| -----------------------------+-------+-------+-------------------------+-------------| a.user@xn--a-hrnchen-27a.de | 70039 | 70172 | /srv/mail/a/70172/70039 | ~/Maildir-- ---