--
-- PostgreSQL database dump
--

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- Name: mail; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA mail;


ALTER SCHEMA mail OWNER TO postgres;

SET search_path = mail, pg_catalog;

--
-- Name: domain; Type: DOMAIN; Schema: mail; Owner: postgres
--

CREATE DOMAIN domain AS character varying(64) NOT NULL;


ALTER DOMAIN mail.domain OWNER TO postgres;

--
-- Name: email_address; Type: DOMAIN; Schema: mail; Owner: postgres
--

CREATE DOMAIN email_address AS character varying(129) NOT NULL
	CONSTRAINT valid_address CHECK ((((VALUE)::text ~ '@'::text) OR ((VALUE)::text = ''::text)));


ALTER DOMAIN mail.email_address OWNER TO postgres;

--
-- Name: local_part; Type: DOMAIN; Schema: mail; Owner: postgres
--

CREATE DOMAIN local_part AS character varying(64) NOT NULL;


ALTER DOMAIN mail.local_part OWNER TO postgres;

--
-- Name: password; Type: DOMAIN; Schema: mail; Owner: postgres
--

CREATE DOMAIN password AS text NOT NULL
	CONSTRAINT valid_password CHECK (((VALUE ~ '^\\$1\\$[./0-9A-Za-z]{8}\\$[./0-9A-Za-z]{22}$'::text) OR (VALUE ~ '^[./0-9A-Za-z]{13}$'::text)));


ALTER DOMAIN mail.password OWNER TO postgres;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: accounts; Type: TABLE; Schema: mail; Owner: postgres; Tablespace: 
--

CREATE TABLE accounts (
    "user" local_part NOT NULL,
    domain domain NOT NULL,
    quota integer DEFAULT 10 NOT NULL,
    password password NOT NULL
);


ALTER TABLE mail.accounts OWNER TO postgres;

--
-- Name: TABLE accounts; Type: COMMENT; Schema: mail; Owner: postgres
--

COMMENT ON TABLE accounts IS 'To create a new account, call the function create_account (user, domain, password).';


--
-- Name: COLUMN accounts.quota; Type: COMMENT; Schema: mail; Owner: postgres
--

COMMENT ON COLUMN accounts.quota IS 'In megabytes.';


--
-- Name: COLUMN accounts.password; Type: COMMENT; Schema: mail; Owner: postgres
--

COMMENT ON COLUMN accounts.password IS 'Passwords can be changed more easily with the change_password function.';


--
-- Name: change_password(local_part, domain, text); Type: FUNCTION; Schema: mail; Owner: postgres
--

CREATE FUNCTION change_password(local_part, domain, text) RETURNS accounts
    AS $_$
	UPDATE mail.accounts SET password = pgcrypto.crypt ($3, pgcrypto.gen_salt ('md5')) WHERE "user" = $1 AND domain = $2;
	SELECT * FROM mail.accounts WHERE "user" = $1 AND domain = $2;
$_$
    LANGUAGE sql;


ALTER FUNCTION mail.change_password(local_part, domain, text) OWNER TO postgres;

--
-- Name: create_account(local_part, domain, text); Type: FUNCTION; Schema: mail; Owner: postgres
--

CREATE FUNCTION create_account(local_part, domain, text) RETURNS accounts
    AS $_$
        INSERT INTO mail.accounts ("user", domain, password) VALUES ($1, $2, pgcrypto.crypt ($3, pgcrypto.gen_salt ('md5')));
        SELECT * FROM mail.accounts WHERE "user" = $1 AND domain = $2;
$_$
    LANGUAGE sql;


ALTER FUNCTION mail.create_account(local_part, domain, text) OWNER TO postgres;

--
-- Name: greylist_process(inet, email_address, email_address); Type: FUNCTION; Schema: mail; Owner: postgres
--

CREATE FUNCTION greylist_process(inet, email_address, email_address) RETURNS boolean
    AS $_$
DECLARE
 ip ALIAS FOR $1;
 sender ALIAS FOR $2;
 recipient ALIAS FOR $3;
 present boolean;
BEGIN
 SELECT INTO present now() - date > interval '5 minute'
  FROM mail.greylist
  WHERE relay_ip = ip
   AND envelope_sender = sender
   AND envelope_recipient = recipient;
 IF NOT FOUND THEN
  RAISE DEBUG 'new greylist entry';
  INSERT INTO mail.greylist (relay_ip, envelope_sender, envelope_recipient, date)
   VALUES (ip, sender, recipient, now());
  PRESENT := false;
 END IF;
 RETURN NOT present;
END;
$_$
    LANGUAGE plpgsql;


ALTER FUNCTION mail.greylist_process(inet, email_address, email_address) OWNER TO postgres;

--
-- Name: lookup_alias(local_part, domain); Type: FUNCTION; Schema: mail; Owner: postgres
--

CREATE FUNCTION lookup_alias(local_part, domain) RETURNS text
    AS $_$
DECLARE
  auser ALIAS FOR $1;
  adomain ALIAS FOR $2;
  result character varying(129);
BEGIN
  SELECT target INTO result
    FROM mail.aliases
    WHERE "user" = auser AND domain = adomain;
  IF FOUND THEN
    RAISE DEBUG 'found';
    RETURN result;
  END IF;
  RAISE DEBUG 'not found';
  IF auser != '*' THEN
    PERFORM 1
      FROM mail.accounts
      WHERE "user" = auser AND domain = adomain;
    IF FOUND THEN
      RAISE DEBUG 'account has priority';
      RETURN NULL;
    END IF;
    RETURN mail.lookup_alias ('*', adomain);
  END IF;
  RAISE DEBUG 'no alias';
  RETURN NULL;
END;
$_$
    LANGUAGE plpgsql;


ALTER FUNCTION mail.lookup_alias(local_part, domain) OWNER TO postgres;

--
-- Name: aliases; Type: TABLE; Schema: mail; Owner: postgres; Tablespace: 
--

CREATE TABLE aliases (
    "user" local_part NOT NULL,
    domain domain NOT NULL,
    target text NOT NULL
);


ALTER TABLE mail.aliases OWNER TO postgres;

--
-- Name: COLUMN aliases."user"; Type: COMMENT; Schema: mail; Owner: postgres
--

COMMENT ON COLUMN aliases."user" IS 'Set to * for a catch-all alias';


--
-- Name: COLUMN aliases.target; Type: COMMENT; Schema: mail; Owner: postgres
--

COMMENT ON COLUMN aliases.target IS 'Unqualified values are for system mail; e.g., a system account or mailing list';


--
-- Name: courier_auth; Type: VIEW; Schema: mail; Owner: postgres
--

CREATE VIEW courier_auth AS
    SELECT accounts."user" AS username, accounts.domain, accounts.password AS cryptpw, '' AS clearpw, '8' AS uid, '8' AS gid, ((('/srv/mail/'::text || (accounts.domain)::text) || '/'::text) || (accounts."user")::text) AS home, (((('/srv/mail/'::text || (accounts.domain)::text) || '/'::text) || (accounts."user")::text) || '/'::text) AS maildir, ((((accounts.quota * 1024) * 1024))::text || 'S'::text) AS quota, '' AS fullname, '' AS options FROM accounts;


ALTER TABLE mail.courier_auth OWNER TO postgres;

--
-- Name: dovecot_auth; Type: VIEW; Schema: mail; Owner: postgres
--

CREATE VIEW dovecot_auth AS
    SELECT (((accounts."user")::text || '@'::text) || (accounts.domain)::text) AS "user", accounts.password, ((('/srv/mail/'::text || (accounts.domain)::text) || '/'::text) || (accounts."user")::text) AS userdb_home, '8' AS userdb_uid, '8' AS userdb_gid FROM accounts;


ALTER TABLE mail.dovecot_auth OWNER TO postgres;

--
-- Name: exim_accounts; Type: VIEW; Schema: mail; Owner: postgres
--

CREATE VIEW exim_accounts AS
    SELECT DISTINCT accounts."user", accounts.domain, (((('/srv/mail/'::text || (accounts.domain)::text) || '/'::text) || (accounts."user")::text) || '/'::text) AS maildir FROM accounts ORDER BY accounts."user", accounts.domain, (((('/srv/mail/'::text || (accounts.domain)::text) || '/'::text) || (accounts."user")::text) || '/'::text);


ALTER TABLE mail.exim_accounts OWNER TO postgres;

--
-- Name: exim_auth; Type: VIEW; Schema: mail; Owner: postgres
--

CREATE VIEW exim_auth AS
    SELECT (((accounts."user")::text || '@'::text) || (accounts.domain)::text) AS email, accounts.password FROM accounts;


ALTER TABLE mail.exim_auth OWNER TO postgres;

--
-- Name: exim_domains; Type: VIEW; Schema: mail; Owner: postgres
--

CREATE VIEW exim_domains AS
    SELECT accounts.domain FROM accounts UNION SELECT aliases.domain FROM aliases;


ALTER TABLE mail.exim_domains OWNER TO postgres;

--
-- Name: greylist; Type: TABLE; Schema: mail; Owner: postgres; Tablespace: 
--

CREATE TABLE greylist (
    relay_ip inet NOT NULL,
    envelope_sender email_address NOT NULL,
    envelope_recipient email_address NOT NULL,
    date timestamp without time zone NOT NULL
);


ALTER TABLE mail.greylist OWNER TO postgres;

--
-- Name: greylist_disable; Type: TABLE; Schema: mail; Owner: postgres; Tablespace: 
--

CREATE TABLE greylist_disable (
    address email_address NOT NULL
);


ALTER TABLE mail.greylist_disable OWNER TO postgres;

--
-- Name: TABLE greylist_disable; Type: COMMENT; Schema: mail; Owner: postgres
--

COMMENT ON TABLE greylist_disable IS 'Recipients in this table will not have greylisting performed for their incoming messages.';


--
-- Name: vacation; Type: TABLE; Schema: mail; Owner: postgres; Tablespace: 
--

CREATE TABLE vacation (
    "user" local_part NOT NULL,
    domain domain NOT NULL,
    message text NOT NULL
);


ALTER TABLE mail.vacation OWNER TO postgres;

--
-- Name: accounts_pkey; Type: CONSTRAINT; Schema: mail; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY accounts
    ADD CONSTRAINT accounts_pkey PRIMARY KEY ("user", domain);


--
-- Name: aliases_pkey; Type: CONSTRAINT; Schema: mail; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY aliases
    ADD CONSTRAINT aliases_pkey PRIMARY KEY ("user", domain);


--
-- Name: greylist_disable_address_key; Type: CONSTRAINT; Schema: mail; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY greylist_disable
    ADD CONSTRAINT greylist_disable_address_key UNIQUE (address);


--
-- Name: greylist_pkey; Type: CONSTRAINT; Schema: mail; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY greylist
    ADD CONSTRAINT greylist_pkey PRIMARY KEY (relay_ip, envelope_sender, envelope_recipient);


--
-- Name: vacation_pkey; Type: CONSTRAINT; Schema: mail; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY vacation
    ADD CONSTRAINT vacation_pkey PRIMARY KEY ("user", domain);


--
-- Name: mail; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA mail FROM PUBLIC;
REVOKE ALL ON SCHEMA mail FROM postgres;
GRANT ALL ON SCHEMA mail TO postgres;
GRANT USAGE ON SCHEMA mail TO PUBLIC;


--
-- Name: accounts; Type: ACL; Schema: mail; Owner: postgres
--

REVOKE ALL ON TABLE accounts FROM PUBLIC;
REVOKE ALL ON TABLE accounts FROM postgres;
GRANT ALL ON TABLE accounts TO postgres;
GRANT SELECT ON TABLE accounts TO maildaemon;
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE accounts TO mailadmin;


--
-- Name: aliases; Type: ACL; Schema: mail; Owner: postgres
--

REVOKE ALL ON TABLE aliases FROM PUBLIC;
REVOKE ALL ON TABLE aliases FROM postgres;
GRANT ALL ON TABLE aliases TO postgres;
GRANT SELECT ON TABLE aliases TO maildaemon;
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE aliases TO mailadmin;


--
-- Name: courier_auth; Type: ACL; Schema: mail; Owner: postgres
--

REVOKE ALL ON TABLE courier_auth FROM PUBLIC;
REVOKE ALL ON TABLE courier_auth FROM postgres;
GRANT ALL ON TABLE courier_auth TO postgres;
GRANT SELECT ON TABLE courier_auth TO maildaemon;


--
-- Name: dovecot_auth; Type: ACL; Schema: mail; Owner: postgres
--

REVOKE ALL ON TABLE dovecot_auth FROM PUBLIC;
REVOKE ALL ON TABLE dovecot_auth FROM postgres;
GRANT ALL ON TABLE dovecot_auth TO postgres;
GRANT SELECT ON TABLE dovecot_auth TO maildaemon;


--
-- Name: exim_accounts; Type: ACL; Schema: mail; Owner: postgres
--

REVOKE ALL ON TABLE exim_accounts FROM PUBLIC;
REVOKE ALL ON TABLE exim_accounts FROM postgres;
GRANT ALL ON TABLE exim_accounts TO postgres;
GRANT SELECT ON TABLE exim_accounts TO maildaemon;


--
-- Name: exim_auth; Type: ACL; Schema: mail; Owner: postgres
--

REVOKE ALL ON TABLE exim_auth FROM PUBLIC;
REVOKE ALL ON TABLE exim_auth FROM postgres;
GRANT ALL ON TABLE exim_auth TO postgres;
GRANT SELECT ON TABLE exim_auth TO maildaemon;


--
-- Name: exim_domains; Type: ACL; Schema: mail; Owner: postgres
--

REVOKE ALL ON TABLE exim_domains FROM PUBLIC;
REVOKE ALL ON TABLE exim_domains FROM postgres;
GRANT ALL ON TABLE exim_domains TO postgres;
GRANT SELECT ON TABLE exim_domains TO maildaemon;


--
-- Name: greylist; Type: ACL; Schema: mail; Owner: postgres
--

REVOKE ALL ON TABLE greylist FROM PUBLIC;
REVOKE ALL ON TABLE greylist FROM postgres;
GRANT ALL ON TABLE greylist TO postgres;
GRANT SELECT,INSERT ON TABLE greylist TO maildaemon;
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE greylist TO mailadmin;


--
-- Name: greylist_disable; Type: ACL; Schema: mail; Owner: postgres
--

REVOKE ALL ON TABLE greylist_disable FROM PUBLIC;
REVOKE ALL ON TABLE greylist_disable FROM postgres;
GRANT ALL ON TABLE greylist_disable TO postgres;
GRANT SELECT ON TABLE greylist_disable TO maildaemon;
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE greylist_disable TO mailadmin;


--
-- Name: vacation; Type: ACL; Schema: mail; Owner: postgres
--

REVOKE ALL ON TABLE vacation FROM PUBLIC;
REVOKE ALL ON TABLE vacation FROM postgres;
GRANT ALL ON TABLE vacation TO postgres;
GRANT SELECT ON TABLE vacation TO maildaemon;
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE vacation TO mailadmin;


--
-- PostgreSQL database dump complete
--