Форум системных администраторов

Код: Выделить всё

# su postgres
$ createdb -p 5432 mail
$ createuser -P mail
Введите пароль для новой роли:
Повторите его:
Должна ли новая роль иметь полномочия суперпользователя? (y - да/n - нет) n
Новая роль должна иметь право создавать базы данных? (y - да/n - нет) n
Новая роль должна иметь право создавать другие роли? (y - да/n - нет) n
$ pg_restore -Umail -W -d mail < dump.sql

Код: Выделить всё

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = public, pg_catalog;

--
-- Name: concat(anyarray); Type: FUNCTION; Schema: public; Owner: mail
--

CREATE FUNCTION concat(VARIADIC anyarray) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT array_to_string($1,'');
$_$;


ALTER FUNCTION public.concat(VARIADIC anyarray) OWNER TO mail;

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

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 mail;

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

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 mail;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: admin; Type: TABLE; Schema: public; Owner: mail; 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,
    superadmin boolean DEFAULT false NOT NULL
);


ALTER TABLE public.admin OWNER TO mail;

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

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


--
-- Name: alias; Type: TABLE; Schema: public; Owner: mail; 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 mail;

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

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


--
-- Name: alias_domain; Type: TABLE; Schema: public; Owner: mail; 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 mail;

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

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


--
-- Name: blacklist; Type: TABLE; Schema: public; Owner: mail; Tablespace: 
--

CREATE TABLE blacklist (
    id integer NOT NULL,
    host character varying(255) NOT NULL,
    sender character varying(255)
);


ALTER TABLE public.blacklist OWNER TO mail;

--
-- Name: blacklist_id_seq; Type: SEQUENCE; Schema: public; Owner: mail
--

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


ALTER TABLE public.blacklist_id_seq OWNER TO mail;

--
-- Name: blacklist_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: mail
--

ALTER SEQUENCE blacklist_id_seq OWNED BY blacklist.id;


--
-- Name: config; Type: TABLE; Schema: public; Owner: mail; 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 mail;

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

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 mail;

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

ALTER SEQUENCE config_id_seq OWNED BY config.id;


--
-- Name: dkim; Type: TABLE; Schema: public; Owner: mail; Tablespace: 
--

CREATE TABLE dkim (
    domain character varying(255) NOT NULL,
    rsa_key character varying(2048) NOT NULL
);


ALTER TABLE public.dkim OWNER TO mail;

--
-- Name: dkim_domains; Type: TABLE; Schema: public; Owner: mail; Tablespace: 
--

CREATE TABLE dkim_domains (
    domains character varying(255) NOT NULL,
    signers character varying(255) NOT NULL,
    active boolean DEFAULT true NOT NULL
);


ALTER TABLE public.dkim_domains OWNER TO mail;

--
-- Name: dnsbl; Type: TABLE; Schema: public; Owner: mail; Tablespace: 
--

CREATE TABLE dnsbl (
    id integer NOT NULL,
    dnsblists character varying(255) NOT NULL,
    bl boolean DEFAULT true NOT NULL
);


ALTER TABLE public.dnsbl OWNER TO mail;

--
-- Name: dnsbl_id_seq; Type: SEQUENCE; Schema: public; Owner: mail
--

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


ALTER TABLE public.dnsbl_id_seq OWNER TO mail;

--
-- Name: dnsbl_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: mail
--

ALTER SEQUENCE dnsbl_id_seq OWNED BY dnsbl.id;


--
-- Name: domain; Type: TABLE; Schema: public; Owner: mail; 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) DEFAULT NULL::character varying,
    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 mail;

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

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


--
-- Name: domain_admins; Type: TABLE; Schema: public; Owner: mail; 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 mail;

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

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


--
-- Name: fetchmail; Type: TABLE; Schema: public; Owner: mail; 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 = ANY (ARRAY[('POP3'::character varying)::text, ('IMAP'::character varying)::text, ('POP2'::character varying)::text, ('ETRN'::character varying)::text, ('AUTO'::character varying)::text]))),
    CONSTRAINT fetchmail_src_auth_check CHECK (((src_auth)::text = ANY (ARRAY[('password'::character varying)::text, ('kerberos_v5'::character varying)::text, ('kerberos'::character varying)::text, ('kerberos_v4'::character varying)::text, ('gssapi'::character varying)::text, ('cram-md5'::character varying)::text, ('otp'::character varying)::text, ('ntlm'::character varying)::text, ('msn'::character varying)::text, ('ssh'::character varying)::text, ('any'::character varying)::text])))
);


ALTER TABLE public.fetchmail OWNER TO mail;

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

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 mail;

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

ALTER SEQUENCE fetchmail_id_seq OWNED BY fetchmail.id;


--
-- Name: log; Type: TABLE; Schema: public; Owner: mail; 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 mail;

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

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


--
-- Name: mailbox; Type: TABLE; Schema: public; Owner: mail; 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,
    rate bigint DEFAULT 60 NOT NULL
);


ALTER TABLE public.mailbox OWNER TO mail;

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

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


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

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


ALTER TABLE public.quota OWNER TO mail;

--
-- Name: quota2; Type: TABLE; Schema: public; Owner: mail; 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 mail;

--
-- Name: vacation; Type: TABLE; Schema: public; Owner: mail; 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),
    modified timestamp with time zone DEFAULT now(),
    activefrom timestamp with time zone DEFAULT '2000-01-01 00:00:00+05'::timestamp with time zone,
    activeuntil timestamp with time zone DEFAULT '2000-01-01 00:00:00+05'::timestamp with time zone
);


ALTER TABLE public.vacation OWNER TO mail;

--
-- Name: vacation_notification; Type: TABLE; Schema: public; Owner: mail; 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 mail;

--
-- Name: whitelist; Type: TABLE; Schema: public; Owner: mail; Tablespace: 
--

CREATE TABLE whitelist (
    id integer NOT NULL,
    sender character varying(255) NOT NULL,
    ip character varying(255) NOT NULL,
    host character varying(255) NOT NULL
);


ALTER TABLE public.whitelist OWNER TO mail;

--
-- Name: whitelist_id_seq; Type: SEQUENCE; Schema: public; Owner: mail
--

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


ALTER TABLE public.whitelist_id_seq OWNER TO mail;

--
-- Name: whitelist_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: mail
--

ALTER SEQUENCE whitelist_id_seq OWNED BY whitelist.id;


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

ALTER TABLE ONLY blacklist ALTER COLUMN id SET DEFAULT nextval('blacklist_id_seq'::regclass);


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

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


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

ALTER TABLE ONLY dnsbl ALTER COLUMN id SET DEFAULT nextval('dnsbl_id_seq'::regclass);


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

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


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

ALTER TABLE ONLY whitelist ALTER COLUMN id SET DEFAULT nextval('whitelist_id_seq'::regclass);


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

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


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

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


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

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


--
-- Name: blacklist_pkey; Type: CONSTRAINT; Schema: public; Owner: mail; Tablespace: 
--

ALTER TABLE ONLY blacklist
    ADD CONSTRAINT blacklist_pkey PRIMARY KEY (id);


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

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


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

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


--
-- Name: dkim_domain_key; Type: CONSTRAINT; Schema: public; Owner: mail; Tablespace: 
--

ALTER TABLE ONLY dkim
    ADD CONSTRAINT dkim_domain_key UNIQUE (domain);


--
-- Name: dkim_domains_pkey; Type: CONSTRAINT; Schema: public; Owner: mail; Tablespace: 
--

ALTER TABLE ONLY dkim_domains
    ADD CONSTRAINT dkim_domains_pkey PRIMARY KEY (domains);


--
-- Name: dnsbl_id_key; Type: CONSTRAINT; Schema: public; Owner: mail; Tablespace: 
--

ALTER TABLE ONLY dnsbl
    ADD CONSTRAINT dnsbl_id_key UNIQUE (id);


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

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


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

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


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

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


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

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


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

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


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

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


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

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


--
-- Name: whitelist_pkey; Type: CONSTRAINT; Schema: public; Owner: mail; Tablespace: 
--

ALTER TABLE ONLY whitelist
    ADD CONSTRAINT whitelist_pkey PRIMARY KEY (id);


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

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


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

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


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

CREATE INDEX alias_domain_idx ON alias USING btree (domain);


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

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


--
-- Name: log_domain_timestamp_idx; Type: INDEX; Schema: public; Owner: mail; Tablespace: 
--

CREATE INDEX log_domain_timestamp_idx ON log USING btree (domain, "timestamp");


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

CREATE INDEX mailbox_domain_idx ON mailbox USING btree (domain);


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

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


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

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


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

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


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

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: mail
--

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: mail
--

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: mail
--

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: mail
--

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: mail
--

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: mail
--

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: mail
--

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;


--
-- PostgreSQL database dump complete
--

Код: Выделить всё

auth_debug = yes # Пока настройка не завершена до конца мне интересен вывод отладки
auth_debug_passwords = yes
auth_mechanisms = plain login digest-md5 cram-md5 # Определяем механизмы авторизации
auth_verbose = yes
disable_plaintext_auth = no # Конечно голый текст в паролях не безопасен, но как от него отказаться, когда туева хуча клиентских прог не работает с нормальными криптоваными паролями?

# Группы/юзеры
last_valid_gid = 12
last_valid_uid = 12
first_valid_gid = 8
first_valid_uid = 8
mail_gid = mail
mail_privileged_group = mail
mail_uid = mail

# Обязательно проверье пути! При необходимости подправьте пути тут или создайте недостающие папки и поправье права на них.
base_dir = /var/run/dovecot/
info_log_path = /var/log/dovecot/dovecot.log
log_path = /var/log/dovecot/dovecot.log
mail_location = maildir:/srv/data/mail/var/spool/mail/%d/%n 
libexec_dir = /usr/libexec/dovecot
sendmail_path = /usr/sbin/sendmail

login_greeting = Welcome to IMAP/POP3 server
mail_debug = yes

postmaster_address = fakeroot@domain.tld # адрес постмастера
mail_plugins = $mail_plugins autocreate quota imap_quota trash # Цепляем дополнения

quota_full_tempfail = yes
rejection_subject = Rejected: %s
rejection_reason = Ваше сообщение для <%t> автоматически отклонено:%n%r

# Доставлять почту из MTA мы будем по LDA
lda_mailbox_autocreate = yes
lda_mailbox_autosubscribe = yes
lda_original_recipient_header =
protocol lda {
  log_path = /var/log/dovecot/dovecot-lda.log
  info_log_path = /var/log/dovecot/dovecot-lda.log
  auth_socket_path = /var/run/dovecot/auth-master
  mail_plugins = quota
}

# Откуда брать инфу для авторизации пользователей
passdb {
  args = /etc/dovecot/dovecot-sql.conf
  driver = sql
}

userdb {
  args = /etc/dovecot/dovecot-sql.conf
  driver = sql
}

# Настройки плагинов
plugin {
# Trash - удаляет письма при необходимости принять письмо в переполненый ящик. Приоритеты расписываем в инклюде
  trash = /etc/dovecot/dovecot-trash.conf
# Autocreate - создает несуществующие директории в мейлдире
  autocreate = INBOX
  autocreate2 = Sent
  autocreate3 = Trash
  autocreate4 = Drafts
  autocreate5 = Junk
  autocreate6 = Spam
  autosubscribe = INBOX
  autosubscribe2 = Sent
  autosubscribe3 = Trash
  autosubscribe4 = Drafts
  autosubscribe5 = Junk
  autosubscribe6 = Spam

# Quotas - обеспечивает ограничение обьема пользовательской папки
  quota = maildir
  quota_rule2 = Trash:storage=+20%%
  quota_rule3 = Spam:ignore
  quota_rule4 = Drafts:storage=+5%%
  quota_rule5 = Sent:storage=+15%%
  quota_rule6 = Junk:storage=+10%% 
  quota_warning = storage=100%% quota-exceeded 100 %u
  quota_warning2 = storage=95%% quota-warning 95 %u
  quota_warning3 = storage=90%% quota-warning 90 %u
  quota_warning4 = storage=85%% quota-warning 85 %u
  quota_warning5 = storage=80%% quota-warning 80 %u
  quota_warning6 = storage=75%% quota-warning 75 %u
  quota_warning7 = storage=70%% quota-warning 70 %u
  quota_warning8 = storage=65%% quota-warning 65 %u
}

# Ну с этим кажись все ясно - описывем специфику протоколов
protocols = imap pop3
protocol imap {
  mail_plugins = $mail_plugins autocreate quota imap_quota trash
  imap_client_workarounds = tb-extra-mailbox-sep
}

protocol pop3 {
  pop3_client_workarounds = outlook-no-nuls oe-ns-eoh
  pop3_uidl_format = %08Xu%08Xv
}

# Где что слушаем
service imap-login {
  inet_listener imap {
    address = 0.0.0.0
    port = 143
  }
  inet_listener imaps {
    address = 0.0.0.0
    port = 993
    ssl = yes
  }
}

# Вешаем сокет quota-warning посредством которого будем запускать скрипт высылающий уведомления о приближении обьема мейлдиры к критическому
service quota-warning {
  executable = script /srv/data/mail/overquoted
  user = dovecot
  unix_listener quota-warning {
    user = dovecot
    mode = 0777
  }
}

service imap {
  process_limit = 1024
  vsz_limit = 256 M
}

# lmtp я пока не использую, но в будущем планирую расширяться, так что он мне может пригодиться, так что пусть будет
service lmtp {
  inet_listener lmtp {
    address = 192.168.50.1
    port = 26
  }
}

service pop3-login {
  inet_listener pop3 {
    address = 0.0.0.0
    port = 110
  }
  inet_listener pop3s {
    address = 0.0.0.0
    port = 995
    ssl = yes
  }
}

service pop3 {
  process_limit = 1024
}

# Цепляем сертификаты (я ведь говорил о шифровании в самом начале). Изголяться с openssl было некогда, так что я заюзал стандартные сертификаты из rpm-пакета
ssl_cert = </etc/pki/dovecot/certs/dovecot.pem
ssl_key = </etc/pki/dovecot/private/dovecot.pem


# Сокеты авторизации. MTA у меня будет цепляться именно через них и соответственно авторизатором здесь будет выступать dovecot. Будьте внимательны с правами сокетов!!!
service auth {
   unix_listener auth-client {
     mode = 0600
     user = exim
     group = exim
   }
   unix_listener auth-master {
     mode = 0600
     user = mail
   }
   unix_listener auth-userdb {
     mode = 0600
     user = mail
   }
}

Код: Выделить всё

driver = pgsql # Обьясняем ленивому коту что работаем в паре со слоном (зоопарк блин!)
connect = host=pgpool.domain.tld dbname=mail user=mail password=пароль_пользователя_БД # Параметры базы
default_pass_scheme = PLAIN # В каком виде будем хранить пароли в базе. К сожалению если хранить в БД криптованые пароли не работает CRAM-MD5, а мне его наличие критично, так что буду хранить пассы в виде голого текста :(
# Извлекаем пароль пользователя из БД
password_query = SELECT username AS user,password FROM mailbox WHERE username = '%u' AND active='1'
# Извлекаем параметрыпользователя из БД
# Правильный запрос для MySQL - может кому пригодится
#user_query = SELECT '/srv/data/mail/var/spool/mail/%d/%u' as home, 'maildir:/srv/data/mail/var/spool/mail/%d/%u' as mail, 8 AS uid, 12 AS gid, concat('dirsize:storage=', quota) AS quota FROM mailbox WHERE username = '%u' AND active = '1'
# Запрос для PgSQL. Как видно из примеров в обоих случаях происходит подстановка значения ячейки quota под соответствующее системное значение
user_query = SELECT '/srv/data/mail/var/spool/mail/%d/%u' as home, 'maildir:/srv/data/mail/var/spool/mail/%d/%u' as mail, 8 AS uid, 12 AS gid, '*:bytes=' || quota AS quota_rule FROM mailbox WHERE username = '%u' AND active = '1'

Код: Выделить всё

1 Trash
2 Spam
3 Sent
4 Draft

Код: Выделить всё

INSERT INTO mailbox VALUES ('fakeroot@domain.tld', 'qwerty', 'Raven', 'domain.tld/fakeroot@tdomain.tld/', 1024000000, '2012-04-24 20:09:03.863104+06', '2012-04-24 20:09:03.863104+06', true, 'domain.tld', 'fakeroot', 100);

Код: Выделить всё

#!/bin/bash

PERCENT=$1
USER=$2

SUBJECT="Вы исчерпали квоту почтового ящика на $PERCENT%"
SUBJECT="=?UTF-8?B?$(echo "$SUBJECT" | base64 -w0)?="

cat << EOF | /usr/libexec/dovecot/dovecot-lda -d $USER -o "plugin/quota=maildir:User quota:noenforcing"
MIME-Version: 1.0
From: Administrator <noreply@torrents.kg>
Subject: ${SUBJECT}
Content-Type: text/plain; charset=UTF-8;format=flowed
Content-Transfer-Encoding: 8bit

Доступное пространство Вашего почтового ящика исчерпано на $PERCENT%.
EOF