--
--


--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';



--
-- Name: merge_quota(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION merge_quota() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
        BEGIN
            UPDATE quota SET current = NEW.current + current WHERE username = NEW.username AND path = NEW.path;
            IF found THEN
                RETURN NULL;
            ELSE
                RETURN NEW;
            END IF;
      END;
      $$;


ALTER FUNCTION public.merge_quota() OWNER TO postgres;

--
-- Name: merge_quota2(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION merge_quota2() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
        BEGIN
            IF NEW.messages < 0 OR NEW.messages IS NULL THEN
                -- ugly kludge: we came here from this function, really do try to insert
                IF NEW.messages IS NULL THEN
                    NEW.messages = 0;
                ELSE
                    NEW.messages = -NEW.messages;
                END IF;
                return NEW;
            END IF;

            LOOP
                UPDATE quota2 SET bytes = bytes + NEW.bytes,
                    messages = messages + NEW.messages
                    WHERE username = NEW.username;
                IF found THEN
                    RETURN NULL;
                END IF;

                BEGIN
                    IF NEW.messages = 0 THEN
                    INSERT INTO quota2 (bytes, messages, username) VALUES (NEW.bytes, NULL, NEW.username);
                    ELSE
                        INSERT INTO quota2 (bytes, messages, username) VALUES (NEW.bytes, -NEW.messages, NEW.username);
                    END IF;
                    return NULL;
                    EXCEPTION WHEN unique_violation THEN
                    -- someone just inserted the record, update it
                END;
            END LOOP;
        END;
        $$;


ALTER FUNCTION public.merge_quota2() OWNER TO postgres;



--
-- Name: admin; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE admin (
    username character varying(255) NOT NULL,
    password character varying(255) DEFAULT ''::character varying NOT NULL,
    created timestamp with time zone DEFAULT now(),
    modified timestamp with time zone DEFAULT now(),
    active boolean DEFAULT true NOT NULL
);


ALTER TABLE public.admin OWNER TO postgres;

--
-- Name: TABLE admin; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE admin IS 'Postfix Admin - Virtual Admins';


--
-- Name: alias; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE alias (
    address character varying(255) NOT NULL,
    goto text NOT NULL,
    domain character varying(255) NOT NULL,
    created timestamp with time zone DEFAULT now(),
    modified timestamp with time zone DEFAULT now(),
    active boolean DEFAULT true NOT NULL
);


ALTER TABLE public.alias OWNER TO postgres;

--
-- Name: TABLE alias; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE alias IS 'Postfix Admin - Virtual Aliases';



--
-- Name: alias_domain; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE alias_domain (
    alias_domain character varying(255) NOT NULL,
    target_domain character varying(255) NOT NULL,
    created timestamp with time zone DEFAULT now(),
    modified timestamp with time zone DEFAULT now(),
    active boolean DEFAULT true NOT NULL
);


ALTER TABLE public.alias_domain OWNER TO postgres;

--
-- Name: TABLE alias_domain; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE alias_domain IS 'Postfix Admin - Domain Aliases';



--
-- Name: config; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE config (
    id integer NOT NULL,
    name character varying(20) NOT NULL,
    value character varying(20) NOT NULL
);


ALTER TABLE public.config OWNER TO postgres;

--
-- Name: config_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE config_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.config_id_seq OWNER TO postgres;

--
-- Name: config_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE config_id_seq OWNED BY config.id;


--
-- Name: domain; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE domain (
    domain character varying(255) NOT NULL,
    description character varying(255) DEFAULT ''::character varying NOT NULL,
    aliases integer DEFAULT 0 NOT NULL,
    mailboxes integer DEFAULT 0 NOT NULL,
    maxquota bigint DEFAULT 0 NOT NULL,
    quota bigint DEFAULT 0 NOT NULL,
    transport character varying(255),
    backupmx boolean DEFAULT false NOT NULL,
    created timestamp with time zone DEFAULT now(),
    modified timestamp with time zone DEFAULT now(),
    active boolean DEFAULT true NOT NULL
);


ALTER TABLE public.domain OWNER TO postgres;

--
-- Name: TABLE domain; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE domain IS 'Postfix Admin - Virtual Domains';


--
-- Name: domain_admins; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE domain_admins (
    username character varying(255) NOT NULL,
    domain character varying(255) NOT NULL,
    created timestamp with time zone DEFAULT now(),
    active boolean DEFAULT true NOT NULL
);


ALTER TABLE public.domain_admins OWNER TO postgres;

--
-- Name: TABLE domain_admins; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE domain_admins IS 'Postfix Admin - Domain Admins';


--
-- Name: fetchmail; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE fetchmail (
    id integer NOT NULL,
    mailbox character varying(255) DEFAULT ''::character varying NOT NULL,
    src_server character varying(255) DEFAULT ''::character varying NOT NULL,
    src_auth character varying(15) NOT NULL,
    src_user character varying(255) DEFAULT ''::character varying NOT NULL,
    src_password character varying(255) DEFAULT ''::character varying NOT NULL,
    src_folder character varying(255) DEFAULT ''::character varying NOT NULL,
    poll_time integer DEFAULT 10 NOT NULL,
    fetchall boolean DEFAULT false NOT NULL,
    keep boolean DEFAULT false NOT NULL,
    protocol character varying(15) NOT NULL,
    extra_options text,
    returned_text text,
    mda character varying(255) DEFAULT ''::character varying NOT NULL,
    date timestamp with time zone DEFAULT now(),
    usessl boolean DEFAULT false NOT NULL,
    CONSTRAINT fetchmail_protocol_check CHECK (((((((protocol)::text = 'POP3'::text) OR ((protocol)::text = 'IMAP'::text)) OR ((protocol)::text = 'POP2'::text)) OR ((protocol)::text = 'ETRN'::text)) OR ((protocol)::text = 'AUTO'::text))),
    CONSTRAINT fetchmail_src_auth_check CHECK (((((((((((((src_auth)::text = 'password'::text) OR ((src_auth)::text = 'kerberos_v5'::text)) OR ((src_auth)::text = 'kerberos'::text)) OR ((src_auth)::text = 'kerberos_v4'::text)) OR ((src_auth)::text = 'gssapi'::text)) OR ((src_auth)::text = 'cram-md5'::text)) OR ((src_auth)::text = 'otp'::text)) OR ((src_auth)::text = 'ntlm'::text)) OR ((src_auth)::text = 'msn'::text)) OR ((src_auth)::text = 'ssh'::text)) OR ((src_auth)::text = 'any'::text)))
);


ALTER TABLE public.fetchmail OWNER TO postgres;

--
-- Name: fetchmail_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE fetchmail_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.fetchmail_id_seq OWNER TO postgres;

--
-- Name: fetchmail_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE fetchmail_id_seq OWNED BY fetchmail.id;


--
-- Name: log; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE log (
    "timestamp" timestamp with time zone DEFAULT now(),
    username character varying(255) DEFAULT ''::character varying NOT NULL,
    domain character varying(255) DEFAULT ''::character varying NOT NULL,
    action character varying(255) DEFAULT ''::character varying NOT NULL,
    data text DEFAULT ''::text NOT NULL
);


ALTER TABLE public.log OWNER TO postgres;

--
-- Name: TABLE log; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE log IS 'Postfix Admin - Log';


--
-- Name: mailbox; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE mailbox (
    username character varying(255) NOT NULL,
    password character varying(255) DEFAULT ''::character varying NOT NULL,
    name character varying(255) DEFAULT ''::character varying NOT NULL,
    maildir character varying(255) DEFAULT ''::character varying NOT NULL,
    quota bigint DEFAULT 0 NOT NULL,
    created timestamp with time zone DEFAULT now(),
    modified timestamp with time zone DEFAULT now(),
    active boolean DEFAULT true NOT NULL,
    domain character varying(255),
    local_part character varying(255) NOT NULL
);


ALTER TABLE public.mailbox OWNER TO postgres;

--
-- Name: TABLE mailbox; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE mailbox IS 'Postfix Admin - Virtual Mailboxes';



--
-- Name: quota; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE quota (
    username character varying(255) NOT NULL,
    path character varying(100) NOT NULL,
    current bigint
);


ALTER TABLE public.quota OWNER TO postgres;

--
-- Name: quota2; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE quota2 (
    username character varying(100) NOT NULL,
    bytes bigint DEFAULT 0 NOT NULL,
    messages integer DEFAULT 0 NOT NULL
);


ALTER TABLE public.quota2 OWNER TO postgres;


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

CREATE TABLE vacation (
    email character varying(255) NOT NULL,
    subject character varying(255) NOT NULL,
    body text DEFAULT ''::text NOT NULL,
    created timestamp with time zone DEFAULT now(),
    active boolean DEFAULT true NOT NULL,
    domain character varying(255)
);


ALTER TABLE public.vacation OWNER TO postgres;

--
-- Name: vacation_notification; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE vacation_notification (
    on_vacation character varying(255) NOT NULL,
    notified character varying(255) NOT NULL,
    notified_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.vacation_notification OWNER TO postgres;

--
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY config ALTER COLUMN id SET DEFAULT nextval('config_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY fetchmail ALTER COLUMN id SET DEFAULT nextval('fetchmail_id_seq'::regclass);


--
-- Name: admin_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY admin
    ADD CONSTRAINT admin_key PRIMARY KEY (username);


--
-- Name: alias_domain_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY alias_domain
    ADD CONSTRAINT alias_domain_pkey PRIMARY KEY (alias_domain);


--
-- Name: alias_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY alias
    ADD CONSTRAINT alias_key PRIMARY KEY (address);


--
-- Name: config_name_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY config
    ADD CONSTRAINT config_name_key UNIQUE (name);


--
-- Name: config_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY config
    ADD CONSTRAINT config_pkey PRIMARY KEY (id);


--
-- Name: domain_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY domain
    ADD CONSTRAINT domain_key PRIMARY KEY (domain);


--
-- Name: fetchmail_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY fetchmail
    ADD CONSTRAINT fetchmail_pkey PRIMARY KEY (id);


--
-- Name: mailbox_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY mailbox
    ADD CONSTRAINT mailbox_key PRIMARY KEY (username);


--
-- Name: quota2_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY quota2
    ADD CONSTRAINT quota2_pkey PRIMARY KEY (username);


--
-- Name: quota_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY quota
    ADD CONSTRAINT quota_pkey PRIMARY KEY (username, path);


--
-- Name: vacation_notification_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY vacation_notification
    ADD CONSTRAINT vacation_notification_pkey PRIMARY KEY (on_vacation, notified);


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

ALTER TABLE ONLY vacation
    ADD CONSTRAINT vacation_pkey PRIMARY KEY (email);


--
-- Name: alias_address_active; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX alias_address_active ON alias USING btree (address, active);


--
-- Name: alias_domain_active; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX alias_domain_active ON alias_domain USING btree (alias_domain, active);


--
-- Name: alias_domain_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX alias_domain_idx ON alias USING btree (domain);


--
-- Name: domain_domain_active; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX domain_domain_active ON domain USING btree (domain, active);


--
-- Name: mailbox_domain_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX mailbox_domain_idx ON mailbox USING btree (domain);


--
-- Name: mailbox_username_active; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX mailbox_username_active ON mailbox USING btree (username, active);


--
-- Name: vacation_email_active; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX vacation_email_active ON vacation USING btree (email, active);


--
-- Name: mergequota; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER mergequota BEFORE INSERT ON quota FOR EACH ROW EXECUTE PROCEDURE merge_quota();


--
-- Name: mergequota2; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER mergequota2 BEFORE INSERT ON quota2 FOR EACH ROW EXECUTE PROCEDURE merge_quota2();


--
-- Name: alias_domain_alias_domain_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY alias_domain
    ADD CONSTRAINT alias_domain_alias_domain_fkey FOREIGN KEY (alias_domain) REFERENCES domain(domain) ON DELETE CASCADE;


--
-- Name: alias_domain_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY alias
    ADD CONSTRAINT alias_domain_fkey FOREIGN KEY (domain) REFERENCES domain(domain);


--
-- Name: alias_domain_target_domain_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY alias_domain
    ADD CONSTRAINT alias_domain_target_domain_fkey FOREIGN KEY (target_domain) REFERENCES domain(domain) ON DELETE CASCADE;


--
-- Name: domain_admins_domain_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY domain_admins
    ADD CONSTRAINT domain_admins_domain_fkey FOREIGN KEY (domain) REFERENCES domain(domain);


--
-- Name: mailbox_domain_fkey1; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY mailbox
    ADD CONSTRAINT mailbox_domain_fkey1 FOREIGN KEY (domain) REFERENCES domain(domain);


--
-- Name: vacation_domain_fkey1; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY vacation
    ADD CONSTRAINT vacation_domain_fkey1 FOREIGN KEY (domain) REFERENCES domain(domain);


--
-- Name: vacation_notification_on_vacation_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY vacation_notification
    ADD CONSTRAINT vacation_notification_on_vacation_fkey FOREIGN KEY (on_vacation) REFERENCES vacation(email) ON DELETE CASCADE;


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

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- Name: admin; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE admin FROM PUBLIC;
REVOKE ALL ON TABLE admin FROM postgres;
GRANT ALL ON TABLE admin TO postgres;


--
-- Name: alias; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE alias FROM PUBLIC;
REVOKE ALL ON TABLE alias FROM postgres;
GRANT ALL ON TABLE alias TO postgres;


--
-- Name: config; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE config FROM PUBLIC;
REVOKE ALL ON TABLE config FROM postgres;
GRANT ALL ON TABLE config TO postgres;


--
-- Name: domain; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE domain FROM PUBLIC;
REVOKE ALL ON TABLE domain FROM postgres;
GRANT ALL ON TABLE domain TO postgres;


--
-- Name: domain_admins; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE domain_admins FROM PUBLIC;
REVOKE ALL ON TABLE domain_admins FROM postgres;
GRANT ALL ON TABLE domain_admins TO postgres;


--
-- Name: fetchmail; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE fetchmail FROM PUBLIC;
REVOKE ALL ON TABLE fetchmail FROM postgres;
GRANT ALL ON TABLE fetchmail TO postgres;


--
-- Name: log; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE log FROM PUBLIC;
REVOKE ALL ON TABLE log FROM postgres;
GRANT ALL ON TABLE log TO postgres;


--
-- Name: mailbox; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE mailbox FROM PUBLIC;
REVOKE ALL ON TABLE mailbox FROM postgres;
GRANT ALL ON TABLE mailbox TO postgres;


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

REVOKE ALL ON TABLE vacation FROM PUBLIC;
REVOKE ALL ON TABLE vacation FROM postgres;
GRANT ALL ON TABLE vacation TO postgres;


--
-- Name: vacation_notification; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE vacation_notification FROM PUBLIC;
REVOKE ALL ON TABLE vacation_notification FROM postgres;
GRANT ALL ON TABLE vacation_notification TO postgres;


--
--

