CREATE TABLE addresses (
username text NOT NULL,
localpart text NOT NULL,
DOMAIN text NOT NULL,
created TIMESTAMP WITH TIME zone DEFAULT now()
);
CREATE TABLE user_accounts (
username text,
password text,
created TIMESTAMP WITH TIME zone
);
CREATE TABLE user_addresses (
username text,
localpart text,
DOMAIN text,
created TIMESTAMP WITH TIME zone
);
CREATE VIEW dovecot_password_query AS
SELECT user_accounts.username AS "user",
user_accounts.password
FROM user_accounts
UNION
SELECT concat(user_addresses.localpart, '@', user_addresses.domain) AS "user",
user_accounts.password
FROM user_addresses,
user_accounts
WHERE (user_addresses.username = user_accounts.username);
CREATE VIEW dovecot_user_query AS
SELECT user_accounts.username AS KEY,
user_accounts.username AS "user",
concat('mdbox:/var/vmail/', user_accounts.username, '/mdbox') AS mail,
concat('/var/vmail/', user_accounts.username) AS home,
5000 AS uid,
5000 AS gid
FROM user_accounts
UNION
SELECT concat(user_addresses.localpart, '@', user_addresses.domain) AS KEY,
user_accounts.username AS "user",
concat('mdbox:/var/vmail/', user_accounts.username, '/mdbox') AS mail,
concat('/var/vmail/', user_accounts.username) AS home,
5000 AS uid,
5000 AS gid
FROM (user_addresses
JOIN user_accounts ON ((user_addresses.username = user_accounts.username)));
CREATE TABLE external_accounts (
username text NOT NULL,
address text NOT NULL,
smtpuser text NOT NULL,
getuser text NOT NULL,
smtppassword text NOT NULL,
getpassword text NOT NULL,
smtpssl INTEGER DEFAULT 1 NOT NULL,
getssl INTEGER DEFAULT 1 NOT NULL
);
CREATE TABLE mail_forwards (
from_address text,
to_address text
);
CREATE VIEW postfix_smtpd_sender_login_maps AS
SELECT user_accounts.username,
concat(user_addresses.localpart, '@', user_addresses.domain) AS address
FROM (user_accounts
LEFT JOIN user_addresses ON ((user_accounts.username = user_addresses.username)))
UNION
SELECT concat(a.localpart, '@', a.domain) AS username,
concat(b.localpart, '@', b.domain) AS address
FROM (user_addresses a
LEFT JOIN user_addresses b ON ((a.username = b.username)));
CREATE TABLE standard_addresses (
alias text
);
CREATE VIEW postfix_virtual_alias_maps AS
SELECT concat(standard_addresses.alias, '@', user_addresses.domain) AS address,
standard_addresses.alias
FROM standard_addresses,
user_addresses
GROUP BY standard_addresses.alias, user_addresses.domain
UNION
SELECT mail_forwards.from_address AS address,
string_agg(mail_forwards.to_address, ', '::text) AS alias
FROM mail_forwards
GROUP BY mail_forwards.from_address;
CREATE VIEW postfix_virtual_mailbox_domains AS
SELECT user_addresses.domain
FROM user_addresses
GROUP BY user_addresses.domain;
CREATE VIEW postfix_virtual_mailbox_maps AS
SELECT concat(user_addresses.localpart, '@', user_addresses.domain) AS email
FROM user_addresses;
CREATE TABLE rfc2142_addresses (
alias text NOT NULL
);
CREATE UNIQUE INDEX addresses_localpart_domain_idx ON addresses USING btree (localpart, DOMAIN);