--
-- don't forget to change the usernames and passwords on each create user line
--
CREATE USER exim PASSWORD 'some password';
CREATE USER eximadmin PASSWORD 'some password';
CREATE TABLE relaytofrom (
    id serial PRIMARY KEY,
    relay_ip cidr,
    mail_from text,
    rcpt_to text,
    block_expires timestamp with time zone NOT NULL,
    record_expires timestamp with time zone NOT NULL,
    blocked_count bigint DEFAULT 1 NOT NULL,
    passed_count bigint DEFAULT 0 NOT NULL,
    aborted_count bigint DEFAULT 0 NOT NULL,
    origin_type varchar(6) DEFAULT 'manual' NOT NULL
      CHECK ((origin_type = 'manual') OR (origin_type = 'auto')),
    create_time timestamp with time zone DEFAULT now() NOT NULL,
    last_update timestamp with time zone
);
CREATE INDEX relaytofrom_index ON relaytofrom (mail_from,rcpt_to);
CREATE INDEX relaytofrom_create ON relaytofrom (create_time);

CREATE TABLE relaytofrom_archive (
    id serial PRIMARY KEY,
    relay_ip cidr,
    mail_from text,
    rcpt_to text,
    block_expires timestamp with time zone NOT NULL,
    record_expires timestamp with time zone NOT NULL,
    blocked_count bigint DEFAULT 1 NOT NULL,
    passed_count bigint DEFAULT 0 NOT NULL,
    aborted_count bigint DEFAULT 0 NOT NULL,
    origin_type varchar(6) DEFAULT 'manual' NOT NULL
        CHECK ((origin_type = 'manual') OR (origin_type = 'auto')),
    create_time timestamp with time zone DEFAULT now() NOT NULL,
    last_update timestamp with time zone
);
CREATE INDEX relaytofrom_archive_index ON relaytofrom_archive (mail_from,rcpt_to);
CREATE INDEX relaytofrom_archive_create ON relaytofrom_archive (create_time);

CREATE TABLE relay_ip_log (
    relay_ip inet NOT NULL,
    create_time timestamp with time zone DEFAULT now() NOT NULL
);

CREATE TABLE relay_ip_whitelist (
    relay_ip inet PRIMARY KEY
);

CREATE VIEW relaytofrom_all
    AS SELECT * FROM relaytofrom UNION SELECT * FROM relaytofrom_archive;

GRANT INSERT,SELECT,UPDATE ON TABLE relaytofrom TO exim;
GRANT SELECT,UPDATE,DELETE ON TABLE relaytofrom TO eximadmin;
GRANT UPDATE ON TABLE relaytofrom_id_seq TO exim;
GRANT INSERT,SELECT ON TABLE relaytofrom_archive TO eximadmin;
GRANT UPDATE ON TABLE relaytofrom_archive_id_seq TO eximadmin;
GRANT SELECT ON TABLE relaytofrom_all TO eximadmin;
GRANT INSERT ON TABLE relay_ip_log TO exim;
GRANT SELECT ON TABLE relay_ip_whitelist TO exim;
GRANT SELECT,DELETE ON TABLE relay_ip_log to eximadmin;
GRANT DELETE,INSERT ON TABLE relay_ip_whitelist to eximadmin;