Защита от спама с помощью Captcha (DBmail + PgSQL)

Рассматривается защита от спама с помощью Captcha

Содержание:

Введение
1) Требования.
2) Принципе работы.
3) Выводы и критика.
4) Настройка.

Введение.

и решил я написать эту статью

данная технология мне понравилась, решил поставить на не скольких серверах, или хотя бы на одном тестовом, хотел бы услышать мнение какие-то...

1. Требования

имеется ввиду что у нас есть сервер!
(или корпоративный, то ли VPS(VDS), или какой-то другой) самое важное не хостинг, хостинг не нужен вообще!
VPS можно купить на 7$

на сервере настроен: Exim, Dbmail, PgSQL

можно использовать так же:
1) Postfix, Sendmail, Qmail или другой MTA;
2) можно защиту от спама какую-то Assassin или DSpam, вообще не важно;
3) данную технологию можно реализовать без DBMail, а только Exim в котором будут соответствующие SQL запросы + dovecot, так как хранить почту в виде РСУБД особого смысла нету;
4) Естественно: MySQL, SQLite, так же обсуждали возможность прицепить Oracle к DBMail.

сразу нужно сказать: любой админ сможет реализовать данную технологию

сама технология защиты email от спама с помощью catpcha со всем не новая, некоторые сервисы ее используют

2. Принципе работы.
(Примерный смысл действий)

Есть ситуация, тогда мы используем фильтры от спама на уровне MTA, или SpamAssassin http://ru.wikipedia.org/wiki/SpamAssassin, или DSpam http://ru.wikipedia.org/wiki/DSPAM, или Greylist http://ru.wikipedia.org/wiki/Серый_список. Последний считается один из самых эффективных... спама проходит около 5 писем за неделю http://ru.wikipedia.org/wiki/Серый_список

Когда к нам отправляют почту могут возникнуть проблему при приеме письма, данное письмо может не пройти через фильтры MTA, по многим причинам, например по причине анти-спам программа определит это письмо как спам.

так же:
1. У хоста отправителя нет записи в обратной зоне или не правильное настроенного DNS или в имени хоста есть слова из списка (ADSL|PPP|.....)
2. Проверка SPF вернула fail или в политике указано +all
3. Письмо было отправленно не с МХ
4. Адрес хоста отправителя находится в определенном RBL

Отсюда, когда к нас приходит письмо, срабатывает триггер SQL, который отправляет сразу нашему отправителю письмо с просьбой зайти на по указанной ссылку и ввести капчу, чтобы ассоциировать данного отправителя как не спамера...

Можно сделать так чтобы вводить капчу только один раз, чтобы при первом проходе капчи данный email адрес был в белом списке данного пользователя которому отправляется письмо и приход письма был незамедлительный, так же пользователь может добавить не только email адрес но и домен...

Эфектиность: любое письмо которое к нам идет, дойдет в любом случае если отправитель введет Captcha (или другие вопросы, защиты от ботов)

3. Выводы и критика
(которые я заметил)

1) Если мы использует Greylist, если письмо к нам не дошло, в логах отправителя будет написано разъяснение почему данное письмо не дошло, имеется ввиду что сервер отправителя правильно настроен и работает, то в случае ошибке при отправке, на сервер отправителя будет отчет по какой причине письмо не дошло, должен быть скрипт который отправит статут ошибки тому email который отправляет!
например на ukr.net http://www.ukr.net/mta/std3.html ;

Code:

The original message was received at Tue, 10 Jul 2007 11:13:55 +0300
from localhost [127.0.0.1]

----- The following addresses had permanent fatal errors -----
<user@ukr.net>
(reason: 451 http://ukr.net/mta/std3.html?<IP_сервера>)

----- Transcript of session follows -----
... while talking to mxs.ukr.net.:
>>> DATA
<<< 451 http://ukr.net/mta/std3.html?<IP_сервера>
<user@ukr.net>... Deferred: 451 http://ukr.net/mta/std3.html?<IP_сервера>
<<< 503 valid RCPT command must precede DATA
Message could not be delivered for zero seconds
Message will be deleted from queue

Преимущества и недостатки Greylist:

Quote

Преимущества

* Настройка серых списков требует лишь небольших затрат труда от администратора сервера и совсем никаких усилий от пользователей электронной почты.
* Реализация серых списков почти не использует ресурсов сервера.
* В настоящее время метод очень эффективен (порой, более 95 %)
* Низкая вероятность ложных срабатываний, когда отклоняется письмо, не являющееся спамом.

Недостатки

* Сервер отправляющей стороны должен быть готов переслать письмо в случае вре́менной ошибки, что может быть не так в случае нестандартных реализаций почтовых серверов. Таким образом, могут ошибочно отсеиваться рассылки с новостных сайтов и тому подобное.
* В стандарте SMTP рекомендуется, чтобы интервалы между повторными попытками послать письмо были не меньше 30 минут. Это означает, что письмо, отосланное с большинства почтовых серверов, дойдёт до адресата не раньше чем через полчаса (а то и позже), что может быть неприемлемо в случае срочной корреспонденции. Этот недостаток компенсируется тем, что задержка вносится только при посылке первого письма с ранее неизвестного адреса. Однако база данных известных адресов, как правило, время от времени очищается от устаревших записей. Если какой-либо корреспондент пишет письма редко, то письма от него всегда будут приходить с задержкой.
* Крупные почтовые службы используют несколько серверов, с разными IP-адресами, и возможна ситуация, когда несколько серверов по очереди пытаются отправить одно и то же письмо (с интервалами в 30 минут). Это может привести к очень большим задержкам при доставке писем.
* Спамерские программы могут совершенствоваться. Поддержка повторной посылки сообщения реализуется довольно легко и в значительной степени нивелирует данный вид защиты. Ключевым показателем в этой борьбе является соотношение характерного времени попадания спамера в чёрные списки tb и типичного времени задержки «серых» списков tg. При t_b \gg t_g серые списки в перспективе бесполезны; при t_b \ll t_g серые списки труднопреодолимы для спамеров.

2) При использовании Greylist, можно сделать персональный Whitelist и даже персональный Greylist, по необходимости Blacklist (капчу не использовать вообще) ;

3) Обучить DSpam;

4) теоретически возможен обход captcha.

4. Настройка

а) изменить структуру таблиц DBMail, добавить некоторые свои для нужд.
б) сделать страницу на которой будет водиться Captcha.
в) админка для пользователей при которой пользователя будут авторизироваться, редактировать свой whitelist и другие настройки.
г) скрипт для добавление акаунтов, так как изменилась структура таблиц, и может быть еще несколько системных скриптов, например удаление спама и устаревших писем (на SQL думаю разобраться можно)

a) Примерна структура таблиц измененная от той которой по-умолчанию в DBmail (для ознакомление, кто автор не знаю)

Code:

--
-- 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: plperlu; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: dbmail
--

CREATE PROCEDURAL LANGUAGE plperlu;

ALTER PROCEDURAL LANGUAGE plperlu OWNER TO dbmail;

--
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: dbmail
--

CREATE PROCEDURAL LANGUAGE plpgsql;

ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO dbmail;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: captcha; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE captcha (
key character varying(32) NOT NULL,
message_idnr integer NOT NULL,
captcha character varying(16),
ip inet,
ua character varying(255),
"time" timestamp without time zone DEFAULT now() NOT NULL,
confirmed boolean DEFAULT false NOT NULL,
"from" character varying(255),
"to" character varying(255)
);

ALTER TABLE public.captcha OWNER TO dbmail;

--
-- Name: COLUMN captcha.ua; Type: COMMENT; Schema: public; Owner: dbmail
--

COMMENT ON COLUMN captcha.ua IS 'user agent';

--
-- Name: dbmail_acl; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE dbmail_acl (
user_id bigint NOT NULL,
mailbox_id bigint NOT NULL,
lookup_flag smallint DEFAULT 0::smallint NOT NULL,
read_flag smallint DEFAULT 0::smallint NOT NULL,
seen_flag smallint DEFAULT 0::smallint NOT NULL,
write_flag smallint DEFAULT 0::smallint NOT NULL,
insert_flag smallint DEFAULT 0::smallint NOT NULL,
post_flag smallint DEFAULT 0::smallint NOT NULL,
create_flag smallint DEFAULT 0::smallint NOT NULL,
delete_flag smallint DEFAULT 0::smallint NOT NULL,
administer_flag smallint DEFAULT 0::smallint NOT NULL
);

ALTER TABLE public.dbmail_acl OWNER TO dbmail;

--
-- Name: dbmail_alias_idnr_seq; Type: SEQUENCE; Schema: public; Owner: dbmail
--

CREATE SEQUENCE dbmail_alias_idnr_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

ALTER TABLE public.dbmail_alias_idnr_seq OWNER TO dbmail;

--
-- Name: dbmail_aliases; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE dbmail_aliases (
alias_idnr bigint DEFAULT nextval('dbmail_alias_idnr_seq'::regclass) NOT NULL,
alias character varying(100) NOT NULL,
deliver_to character varying(250) NOT NULL,
client_idnr bigint DEFAULT 0::bigint NOT NULL
);

ALTER TABLE public.dbmail_aliases OWNER TO dbmail;

--
-- Name: dbmail_auto_notifications; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE dbmail_auto_notifications (
user_idnr bigint NOT NULL,
notify_address character varying(100)
);

ALTER TABLE public.dbmail_auto_notifications OWNER TO dbmail;

--
-- Name: dbmail_auto_replies; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE dbmail_auto_replies (
user_idnr bigint NOT NULL,
start_date timestamp without time zone NOT NULL,
stop_date timestamp without time zone NOT NULL,
reply_body text
);

ALTER TABLE public.dbmail_auto_replies OWNER TO dbmail;

--
-- Name: dbmail_ccfield_idnr_seq; Type: SEQUENCE; Schema: public; Owner: dbmail
--

CREATE SEQUENCE dbmail_ccfield_idnr_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

ALTER TABLE public.dbmail_ccfield_idnr_seq OWNER TO dbmail;

--
-- Name: dbmail_ccfield; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE dbmail_ccfield (
physmessage_id bigint NOT NULL,
id bigint DEFAULT nextval('dbmail_ccfield_idnr_seq'::regclass) NOT NULL,
ccname character varying(100) DEFAULT ''::character varying NOT NULL,
ccaddr character varying(100) DEFAULT ''::character varying NOT NULL
);

ALTER TABLE public.dbmail_ccfield OWNER TO dbmail;

--
-- Name: dbmail_datefield_idnr_seq; Type: SEQUENCE; Schema: public; Owner: dbmail
--

CREATE SEQUENCE dbmail_datefield_idnr_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

ALTER TABLE public.dbmail_datefield_idnr_seq OWNER TO dbmail;

--
-- Name: dbmail_datefield; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE dbmail_datefield (
physmessage_id bigint NOT NULL,
id bigint DEFAULT nextval('dbmail_datefield_idnr_seq'::regclass) NOT NULL,
datefield timestamp without time zone DEFAULT '1970-01-01 00:00:00'::timestamp without time zone NOT NULL
);

ALTER TABLE public.dbmail_datefield OWNER TO dbmail;

--
-- Name: dbmail_envelope_idnr_seq; Type: SEQUENCE; Schema: public; Owner: dbmail
--

CREATE SEQUENCE dbmail_envelope_idnr_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

ALTER TABLE public.dbmail_envelope_idnr_seq OWNER TO dbmail;

--
-- Name: dbmail_envelope; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE dbmail_envelope (
physmessage_id bigint NOT NULL,
id bigint DEFAULT nextval('dbmail_envelope_idnr_seq'::regclass) NOT NULL,
envelope text DEFAULT ''::text NOT NULL
);

ALTER TABLE public.dbmail_envelope OWNER TO dbmail;

--
-- Name: dbmail_fromfield_idnr_seq; Type: SEQUENCE; Schema: public; Owner: dbmail
--

CREATE SEQUENCE dbmail_fromfield_idnr_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

ALTER TABLE public.dbmail_fromfield_idnr_seq OWNER TO dbmail;

--
-- Name: dbmail_fromfield; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE dbmail_fromfield (
physmessage_id bigint NOT NULL,
id bigint DEFAULT nextval('dbmail_fromfield_idnr_seq'::regclass) NOT NULL,
fromname character varying(100) DEFAULT ''::character varying NOT NULL,
fromaddr character varying(100) DEFAULT ''::character varying NOT NULL
);

ALTER TABLE public.dbmail_fromfield OWNER TO dbmail;

--
-- Name: dbmail_headername_idnr_seq; Type: SEQUENCE; Schema: public; Owner: dbmail
--

CREATE SEQUENCE dbmail_headername_idnr_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

ALTER TABLE public.dbmail_headername_idnr_seq OWNER TO dbmail;

--
-- Name: dbmail_headername; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE dbmail_headername (
id bigint DEFAULT nextval('dbmail_headername_idnr_seq'::regclass) NOT NULL,
headername character varying(100) DEFAULT ''::character varying NOT NULL
);

ALTER TABLE public.dbmail_headername OWNER TO dbmail;

--
-- Name: dbmail_headervalue_idnr_seq; Type: SEQUENCE; Schema: public; Owner: dbmail
--

CREATE SEQUENCE dbmail_headervalue_idnr_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

ALTER TABLE public.dbmail_headervalue_idnr_seq OWNER TO dbmail;

--
-- Name: dbmail_headervalue; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE dbmail_headervalue (
headername_id bigint NOT NULL,
physmessage_id bigint NOT NULL,
id bigint DEFAULT nextval('dbmail_headervalue_idnr_seq'::regclass) NOT NULL,
headervalue text DEFAULT ''::text NOT NULL
);

ALTER TABLE public.dbmail_headervalue OWNER TO dbmail;

--
-- Name: dbmail_mailbox_idnr_seq; Type: SEQUENCE; Schema: public; Owner: dbmail
--

CREATE SEQUENCE dbmail_mailbox_idnr_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

ALTER TABLE public.dbmail_mailbox_idnr_seq OWNER TO dbmail;

--
-- Name: dbmail_mailboxes; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE dbmail_mailboxes (
mailbox_idnr bigint DEFAULT nextval('dbmail_mailbox_idnr_seq'::regclass) NOT NULL,
owner_idnr bigint,
name character varying(100) NOT NULL,
seen_flag smallint DEFAULT 0::smallint NOT NULL,
answered_flag smallint DEFAULT 0::smallint NOT NULL,
deleted_flag smallint DEFAULT 0::smallint NOT NULL,
flagged_flag smallint DEFAULT 0::smallint NOT NULL,
recent_flag smallint DEFAULT 0::smallint NOT NULL,
draft_flag smallint DEFAULT 0::smallint NOT NULL,
no_inferiors smallint DEFAULT 0::smallint NOT NULL,
no_select smallint DEFAULT 0::smallint NOT NULL,
permission smallint DEFAULT 2::smallint NOT NULL
);

ALTER TABLE public.dbmail_mailboxes OWNER TO dbmail;

--
-- Name: dbmail_messageblk_idnr_seq; Type: SEQUENCE; Schema: public; Owner: dbmail
--

CREATE SEQUENCE dbmail_messageblk_idnr_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

ALTER TABLE public.dbmail_messageblk_idnr_seq OWNER TO dbmail;

--
-- Name: dbmail_messageblks; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE dbmail_messageblks (
messageblk_idnr bigint DEFAULT nextval('dbmail_messageblk_idnr_seq'::regclass) NOT NULL,
physmessage_id bigint,
messageblk bytea NOT NULL,
blocksize bigint DEFAULT 0::bigint NOT NULL,
is_header smallint DEFAULT 0::smallint NOT NULL
);

ALTER TABLE public.dbmail_messageblks OWNER TO dbmail;

--
-- Name: dbmail_message_idnr_seq; Type: SEQUENCE; Schema: public; Owner: dbmail
--

CREATE SEQUENCE dbmail_message_idnr_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

ALTER TABLE public.dbmail_message_idnr_seq OWNER TO dbmail;

--
-- Name: dbmail_messages; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE dbmail_messages (
message_idnr bigint DEFAULT nextval('dbmail_message_idnr_seq'::regclass) NOT NULL,
mailbox_idnr bigint,
physmessage_id bigint,
seen_flag smallint DEFAULT 0::smallint NOT NULL,
answered_flag smallint DEFAULT 0::smallint NOT NULL,
deleted_flag smallint DEFAULT 0::smallint NOT NULL,
flagged_flag smallint DEFAULT 0::smallint NOT NULL,
recent_flag smallint DEFAULT 0::smallint NOT NULL,
draft_flag smallint DEFAULT 0::smallint NOT NULL,
unique_id character varying(70) NOT NULL,
status smallint DEFAULT 0::smallint NOT NULL
);

ALTER TABLE public.dbmail_messages OWNER TO dbmail;

--
-- Name: dbmail_seq_pbsp_id; Type: SEQUENCE; Schema: public; Owner: dbmail
--

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

ALTER TABLE public.dbmail_seq_pbsp_id OWNER TO dbmail;

--
-- Name: dbmail_pbsp; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE dbmail_pbsp (
idnr bigint DEFAULT nextval('dbmail_seq_pbsp_id'::regclass) NOT NULL,
since timestamp without time zone DEFAULT '1970-01-01 00:00:00'::timestamp without time zone NOT NULL,
ipnumber inet DEFAULT '0.0.0.0'::inet NOT NULL
);

ALTER TABLE public.dbmail_pbsp OWNER TO dbmail;

--
-- Name: dbmail_physmessage_id_seq; Type: SEQUENCE; Schema: public; Owner: dbmail
--

CREATE SEQUENCE dbmail_physmessage_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

ALTER TABLE public.dbmail_physmessage_id_seq OWNER TO dbmail;

--
-- Name: dbmail_physmessage; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE dbmail_physmessage (
id bigint DEFAULT nextval('dbmail_physmessage_id_seq'::regclass) NOT NULL,
messagesize bigint DEFAULT 0::bigint NOT NULL,
rfcsize bigint DEFAULT 0::bigint NOT NULL,
internal_date timestamp without time zone
);

ALTER TABLE public.dbmail_physmessage OWNER TO dbmail;

--
-- Name: dbmail_referencesfield_idnr_seq; Type: SEQUENCE; Schema: public; Owner: dbmail
--

CREATE SEQUENCE dbmail_referencesfield_idnr_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

ALTER TABLE public.dbmail_referencesfield_idnr_seq OWNER TO dbmail;

--
-- Name: dbmail_referencesfield; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE dbmail_referencesfield (
physmessage_id bigint NOT NULL,
id bigint DEFAULT nextval('dbmail_referencesfield_idnr_seq'::regclass) NOT NULL,
referencesfield character varying(255) DEFAULT ''::character varying NOT NULL
);

ALTER TABLE public.dbmail_referencesfield OWNER TO dbmail;

--
-- Name: dbmail_replycache; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE dbmail_replycache (
to_addr character varying(100) DEFAULT ''::character varying NOT NULL,
from_addr character varying(100) DEFAULT ''::character varying NOT NULL,
handle character varying(100) DEFAULT ''::character varying,
lastseen timestamp without time zone NOT NULL
);

ALTER TABLE public.dbmail_replycache OWNER TO dbmail;

--
-- Name: dbmail_replytofield_idnr_seq; Type: SEQUENCE; Schema: public; Owner: dbmail
--

CREATE SEQUENCE dbmail_replytofield_idnr_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

ALTER TABLE public.dbmail_replytofield_idnr_seq OWNER TO dbmail;

--
-- Name: dbmail_replytofield; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE dbmail_replytofield (
physmessage_id bigint NOT NULL,
id bigint DEFAULT nextval('dbmail_replytofield_idnr_seq'::regclass) NOT NULL,
replytoname character varying(100) DEFAULT ''::character varying NOT NULL,
replytoaddr character varying(100) DEFAULT ''::character varying NOT NULL
);

ALTER TABLE public.dbmail_replytofield OWNER TO dbmail;

--
-- Name: dbmail_sievescripts_idnr_seq; Type: SEQUENCE; Schema: public; Owner: dbmail
--

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

ALTER TABLE public.dbmail_sievescripts_idnr_seq OWNER TO dbmail;

--
-- Name: dbmail_sievescripts; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE dbmail_sievescripts (
id bigint DEFAULT nextval('dbmail_sievescripts_idnr_seq'::regclass) NOT NULL,
owner_idnr bigint NOT NULL,
active smallint DEFAULT 0::smallint NOT NULL,
name character varying(100) DEFAULT ''::character varying NOT NULL,
script text DEFAULT ''::text NOT NULL
);

ALTER TABLE public.dbmail_sievescripts OWNER TO dbmail;

--
-- Name: dbmail_subjectfield_idnr_seq; Type: SEQUENCE; Schema: public; Owner: dbmail
--

CREATE SEQUENCE dbmail_subjectfield_idnr_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

ALTER TABLE public.dbmail_subjectfield_idnr_seq OWNER TO dbmail;

--
-- Name: dbmail_subjectfield; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE dbmail_subjectfield (
physmessage_id bigint NOT NULL,
id bigint DEFAULT nextval('dbmail_subjectfield_idnr_seq'::regclass) NOT NULL,
subjectfield character varying(255) DEFAULT ''::character varying NOT NULL
);

ALTER TABLE public.dbmail_subjectfield OWNER TO dbmail;

--
-- Name: dbmail_subscription; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE dbmail_subscription (
user_id bigint NOT NULL,
mailbox_id bigint NOT NULL
);

ALTER TABLE public.dbmail_subscription OWNER TO dbmail;

--
-- Name: dbmail_tofield_idnr_seq; Type: SEQUENCE; Schema: public; Owner: dbmail
--

CREATE SEQUENCE dbmail_tofield_idnr_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

ALTER TABLE public.dbmail_tofield_idnr_seq OWNER TO dbmail;

--
-- Name: dbmail_tofield; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE dbmail_tofield (
physmessage_id bigint NOT NULL,
id bigint DEFAULT nextval('dbmail_tofield_idnr_seq'::regclass) NOT NULL,
toname character varying(100) DEFAULT ''::character varying NOT NULL,
toaddr character varying(100) DEFAULT ''::character varying NOT NULL
);

ALTER TABLE public.dbmail_tofield OWNER TO dbmail;

--
-- Name: dbmail_usermap; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE dbmail_usermap (
login character varying(100) NOT NULL,
sock_allow character varying(100) NOT NULL,
sock_deny character varying(100) NOT NULL,
userid character varying(100) NOT NULL
);

ALTER TABLE public.dbmail_usermap OWNER TO dbmail;

--
-- Name: dbmail_user_idnr_seq; Type: SEQUENCE; Schema: public; Owner: dbmail
--

CREATE SEQUENCE dbmail_user_idnr_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

ALTER TABLE public.dbmail_user_idnr_seq OWNER TO dbmail;

--
-- Name: dbmail_users; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE dbmail_users (
user_idnr bigint DEFAULT nextval('dbmail_user_idnr_seq'::regclass) NOT NULL,
userid character varying(100) NOT NULL,
passwd character varying(34) NOT NULL,
client_idnr bigint DEFAULT 0::bigint NOT NULL,
maxmail_size bigint DEFAULT 0::bigint NOT NULL,
curmail_size bigint DEFAULT 0::bigint NOT NULL,
maxsieve_size bigint DEFAULT 0::bigint NOT NULL,
cursieve_size bigint DEFAULT 0::bigint NOT NULL,
encryption_type character varying(20) DEFAULT ''::character varying NOT NULL,
last_login timestamp without time zone DEFAULT '1979-11-03 22:05:58'::timestamp without time zone NOT NULL
);

ALTER TABLE public.dbmail_users OWNER TO dbmail;

--
-- Name: dbmail_whitelist_id_seq; Type: SEQUENCE; Schema: public; Owner: dbmail
--

CREATE SEQUENCE dbmail_whitelist_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

ALTER TABLE public.dbmail_whitelist_id_seq OWNER TO dbmail;

--
-- Name: dbmail_whitelist; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE dbmail_whitelist (
id integer DEFAULT nextval('dbmail_whitelist_id_seq'::regclass) NOT NULL,
user_idnr bigint,
mail character(100)
);

ALTER TABLE public.dbmail_whitelist OWNER TO dbmail;

--
-- Name: enum_dtype; Type: TYPE; Schema: public; Owner: dbmail
--

CREATE TYPE enum_dtype AS ENUM (
'LOCAL',
'VIRTUAL',
'RELAY'
);

ALTER TYPE public.enum_dtype OWNER TO dbmail;

--
-- Name: domains_seq; Type: SEQUENCE; Schema: public; Owner: dbmail
--

CREATE SEQUENCE domains_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

ALTER TABLE public.domains_seq OWNER TO dbmail;

--
-- Name: domains; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE domains (
unic_id integer DEFAULT nextval('domains_seq'::regclass) NOT NULL,
domainname character varying(128) DEFAULT ''::character varying NOT NULL,
type enum_dtype DEFAULT 'LOCAL'::enum_dtype NOT NULL
);

ALTER TABLE public.domains OWNER TO dbmail;

--
-- Name: email_templates_id_seq; Type: SEQUENCE; Schema: public; Owner: dbmail
--

CREATE SEQUENCE email_templates_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

ALTER TABLE public.email_templates_id_seq OWNER TO dbmail;

--
-- Name: email_templates; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE email_templates (
id bigint DEFAULT nextval('email_templates_id_seq'::regclass) NOT NULL,
subject character varying,
template character varying,
type character varying(32) DEFAULT 'text/plain'::character varying NOT NULL,
owner_idnr bigint
);

ALTER TABLE public.email_templates OWNER TO dbmail;

--
-- Name: user_prefs; Type: TABLE; Schema: public; Owner: dbmail; Tablespace:
--

CREATE TABLE user_prefs (
user_idnr bigint NOT NULL,
whitelist boolean DEFAULT true,
template bigint NOT NULL,
spambox bigint NOT NULL,
check_to boolean DEFAULT false NOT NULL,
autowl boolean DEFAULT true
);

ALTER TABLE public.user_prefs OWNER TO dbmail;

--
-- Name: COLUMN user_prefs.autowl; Type: COMMENT; Schema: public; Owner: dbmail
--

COMMENT ON COLUMN user_prefs.autowl IS 'автодобавление в wl получателей исходящей почты';

--
-- Name: add_recp2wl(character varying, character varying); Type: FUNCTION; Schema: public; Owner: dbmail
--

CREATE FUNCTION add_recp2wl(_sender character varying, _recipient character varying) RETURNS SETOF bigint
AS $$declare
sender_ID bigint;
begin
if not (substring(_sender from '@(.*)')=substring(_recipient from '@(.*)'))
then
select into sender_ID user_idnr FROM dbmail_users WHERE userid=_sender;
if(select count (*)::integer::boolean from user_prefs WHERE user_idnr=sender_ID and autowl=true)
then
if not (select count(*)::integer::boolean from dbmail_whitelist WHERE user_idnr=sender_ID and mail = _recipient)
then
insert into dbmail_whitelist(user_idnr,mail) values (sender_ID,_recipient);
end if;
end if;
end if;
return next 1;
end;
$$
LANGUAGE plpgsql;

ALTER FUNCTION public.add_recp2wl(_sender character varying, _recipient character varying) OWNER TO dbmail;

--
-- Name: captcha(bigint, character varying, character varying, character varying, bigint); Type: FUNCTION; Schema: public; Owner: dbmail
--

CREATE FUNCTION captcha(bigint, character varying, character varying, character varying, bigint) RETURNS character varying
AS $_X$

use strict;
use POSIX;
use Sys::Hostname;
use MIME::Lite;
use MIME::QuotedPrint;

my ($user,$from,$to,$subj,$tmpl)=@_;

my $key=join'',map $$_[rand@$_],([0..9,'a'..'f'])x32;

my $url="http://www..ru/confirm/$key/";
#warn "key=$key url=$url";
# тут бы еще проверить на всякий что ключа такого нет

my $date = POSIX::strftime("%Y-%m-%d %H:%M:%S", localtime);
my $hostname = hostname;

#my $parts=spi_exec_query(<<SQL);
# select template, subject, type
# from email_templates, user_specific
# where user_specific.user_id = $user
# and email_templates.id = user_specific.template_id
#SQL
my $parts=spi_exec_query(<<SQL);
select template, subject, type
from email_templates
where id = $tmpl
SQL

#TODO мультипарт из кусков;
my $_tmpl = $parts->{rows}[0]{template};
my $_subj = $parts->{rows}[0]{subject};

for($_tmpl,$_subj){
s/__FROM__/$from/g;
s/__TO__/$to/g;
s/__KEY__/$key/g;
s/__SUBJ__/$subj/g;
s/__SUBJ~(\d+)__/substr($subj,0,$1)/ge;
s/__DATE__/$date/g;
s/__HOSTNAME__/$hostname/g;
s/__URL__/$url/g;
}

utf8::encode $_ for $_tmpl,$_subj;

for($to){my$x;
/</ ? s/<.*?\@/<mail.robot\@/
: s/^.*?\@/mail.robot\@/;
s/^(.*?)</utf8::encode $x=$1;'=?utf-8?Q?'.encode_qp($x,'').'?= <'/e;
}
my $mail = MIME::Lite->new(
From => $to,
To => $from,
Subject => '=?utf-8?Q?'.encode_qp($_subj,'').'?=',
Encoding => 'base64',
Type => 'text/plain; charset=utf-8',
Data => $_tmpl,
);

#warn "\n".$mail->as_string;
#$mail->send_by_sendmail(SetSender=>0);
$mail->send;
#use Data::Dumper;
#warn "env=".Dumper \%ENV;
#open MAIL, "|/usr/sbin/sendmail -i $from" or warn $!;
#print MAIL $mail->as_string;
#close MAIL;

return $key;
$_X$
LANGUAGE plperlu;

ALTER FUNCTION public.captcha(bigint, character varying, character varying, character varying, bigint) OWNER TO dbmail;

--
-- Name: captcha(character varying, inet, character varying, character varying); Type: FUNCTION; Schema: public; Owner: dbmail
--

CREATE FUNCTION captcha(_key character varying, _ip inet, _ua character varying, _captcha character varying) RETURNS SETOF bigint
AS $$declare
row captcha;
mb bigint;
owner bigint;
id bigint;
begin
select into row * from captcha where "key"=_key and captcha=_captcha and ip=_ip and ua=_ua and confirmed=false;
if(row.message_idnr is null) then return; end if;

-- а не склеить ли их в один запрос...
select into mb mailbox_idnr from dbmail_messages where message_idnr=row.message_idnr;
select into owner owner_idnr from dbmail_mailboxes where mailbox_idnr=mb;
select into mb mailbox_idnr from dbmail_mailboxes where owner_idnr=owner and name='INBOX';

for id in select message_idnr from captcha where "from"=row."from" and "to"=row."to" and confirmed=false loop
-- update captcha set confirmed=true where message_idnr=id;
-- update dbmail_messages set mailbox_idnr=mb where message_idnr = id;
return next id;
end loop;

insert into dbmail_whitelist(user_idnr,mail) values (owner,row."from");

end;
$$
LANGUAGE plpgsql;

ALTER FUNCTION public.captcha(_key character varying, _ip inet, _ua character varying, _captcha character varying) OWNER TO dbmail;

--
-- Name: on_message(); Type: FUNCTION; Schema: public; Owner: dbmail
--

CREATE FUNCTION on_message() RETURNS trigger
AS $$declare
mb dbmail_mailboxes;
sb bigint;
_from varchar;
_fromuser varchar;
_fromdomain varchar;
_envelope_from varchar;
_to varchar;
_toname varchar;
_subj varchar;
_key varchar;
_tmpl bigint;
begin
perform warn('on_message ' || new.message_idnr);

select into mb * from dbmail_mailboxes where mailbox_idnr=new.mailbox_idnr;
perform warn(mb.owner_idnr || '/' || mb.name || '=' || mb.mailbox_idnr);
if( mb.name != 'INBOX' )then
perform warn('not inbox');
return new;
end if;

select into _to toaddr from dbmail_tofield where physmessage_id=new.physmessage_id;
select into _toname toname from dbmail_tofield where physmessage_id=new.physmessage_id;
-- <костыль>
if not (select count(*)::integer::boolean from dbmail_aliases where deliver_to=mb.owner_idnr::varchar and alias=_to) then
select into _to ccaddr from dbmail_ccfield where physmessage_id=new.physmessage_id;
select into _toname ccname from dbmail_ccfield where physmessage_id=new.physmessage_id;
if not (select count(*)::integer::boolean from dbmail_aliases where deliver_to=mb.owner_idnr::varchar and alias=_to) then
_toname := '';
select into _to userid from dbmail_users where user_idnr=mb.owner_idnr;
end if;
end if;
-- </костыль>
select into _from fromaddr from dbmail_fromfield where physmessage_id=new.physmessage_id;
select into _fromdomain substring(_from from '@(.*)');
select into _fromuser substring(_from from '(.*?)@');

if not (select count(*)::integer::boolean from user_prefs where user_idnr=mb.owner_idnr and whitelist=true) then
return new; -- Если вайтлист выключен, то записать во входящие
end if;

select into _envelope_from headervalue from dbmail_headervalue where (physmessage_id=new.physmessage_id) and (headername_id = 197);
select into sb spambox from user_prefs where user_idnr=mb.owner_idnr;
perform warn('F='||_from||' T='||_to||' D='||_fromdomain||' U='||_fromuser);

if( select id::boolean from dbmail_whitelist where (user_idnr=mb.owner_idnr) and (
mail=_from
or mail='@'||_fromdomain
or mail=_fromdomain
or _from like '%.'||mail
) limit 1 )then
return new; -- Если в локальном вайтлисте - записать в инбокс.
end if;

if (substring(_to from '@(.*)')=_fromdomain)
then -- Если внутри домена
if not (substring(_envelope_from from '@(.*)')=_fromdomain)
then -- и адрес на конверте не совпадает
update dbmail_messages set mailbox_idnr=sb where message_idnr=new.message_idnr; --то в спам
end if;
return new; -- а если совпадает, то в инбокс.
end if;

if( select id::boolean from dbmail_whitelist where (user_idnr=3) and (substring(_envelope_from from '@(.*)')=_fromdomain)and (
mail=_from
or mail='@'||_fromdomain
or mail=_fromdomain
or _from like '%.'||mail
) limit 1 )then
return new; -- Если в глобальном вайтлисте, и адрес на конверте совпадает - записать в инбокс.
end if;

if (_fromuser='mail.robot') then -- Если якобы от 'mail.robot'
if (select count(*)::integer::boolean from domains where domainname=_fromdomain limit 1) then -- и из локального дмена
if not (substring(_envelope_from from '@(.*)')=_fromdomain)
then -- Если адрес на конверте не совпадает
update dbmail_messages set mailbox_idnr=sb where message_idnr=new.message_idnr; --то в спам
end if;
return new; -- иначе во инбокс
end if;
end if;

perform warn('!-1-!');

update dbmail_messages set mailbox_idnr=sb where message_idnr=new.message_idnr; -- все остальное в спам

select into _subj subjectfield from dbmail_subjectfield where physmessage_id=new.physmessage_id;

select into _tmpl template from user_prefs where user_idnr=mb.owner_idnr;
perform warn('TMPL='||_tmpl);

if (_toname='') then _toname=_to; end if;
select into _key captcha(mb.owner_idnr,_from,_toname||' <'||_to||'>',_subj,_tmpl);
insert into "captcha" ("key",message_idnr,"from","to") values (_key,new.message_idnr,_from,_to);

-- if( _from ~ '<' ) then select into _from substring( _from from '<(.*?>'); end if;
--perform warn('F='||_from);

return new;
end;
$$
LANGUAGE plpgsql;

ALTER FUNCTION public.on_message() OWNER TO dbmail;

--
-- Name: spam2inbox(); Type: FUNCTION; Schema: public; Owner: dbmail
--

CREATE FUNCTION spam2inbox() RETURNS trigger
AS $$declare
mb bigint;
id bigint;
_to varchar;

begin

select into mb mailbox_idnr from dbmail_mailboxes where owner_idnr=new.user_idnr and name='INBOX';

select into _to userid from dbmail_users where user_idnr=new.user_idnr;

for id in select message_idnr from captcha where "from"=new.mail
and "to"=_to and confirmed=false
loop
update captcha set confirmed=true where message_idnr=id;
update dbmail_messages set mailbox_idnr=mb where message_idnr = id;
end loop;
return new;
end;$$
LANGUAGE plpgsql;

ALTER FUNCTION public.spam2inbox() OWNER TO dbmail;

--
-- Name: warn(character varying); Type: FUNCTION; Schema: public; Owner: dbmail
--

CREATE FUNCTION warn(character varying) RETURNS void
AS $_X$
warn "----------------------------------------------------------------------->>$_[0]<<";
$_X$
LANGUAGE plperlu;

ALTER FUNCTION public.warn(character varying) OWNER TO dbmail;

--
-- Name: captcha_pkey; Type: CONSTRAINT; Schema: public; Owner: dbmail; Tablespace:
--

ALTER TABLE ONLY captcha
ADD CONSTRAINT captcha_pkey PRIMARY KEY (key);

--
-- Name: dbmail_acl_pkey; Type: CONSTRAINT; Schema: public; Owner: dbmail; Tablespace:
--

ALTER TABLE ONLY dbmail_acl
ADD CONSTRAINT dbmail_acl_pkey PRIMARY KEY (user_id, mailbox_id);

--
-- Name: dbmail_aliases_alias_key; Type: CONSTRAINT; Schema: public; Owner: dbmail; Tablespace:
--

ALTER TABLE ONLY dbmail_aliases
ADD CONSTRAINT dbmail_aliases_alias_key UNIQUE (alias);

--
-- Name: dbmail_aliases_pkey; Type: CONSTRAINT; Schema: public; Owner: dbmail; Tablespace:
--

ALTER TABLE ONLY dbmail_aliases
ADD CONSTRAINT dbmail_aliases_pkey PRIMARY KEY (alias_idnr);

--
-- Name: dbmail_auto_notifications_pkey; Type: CONSTRAINT; Schema: public; Owner: dbmail; Tablespace:
--

ALTER TABLE ONLY dbmail_auto_notifications
ADD CONSTRAINT dbmail_auto_notifications_pkey PRIMARY KEY (user_idnr);

--
-- Name: dbmail_auto_replies_pkey; Type: CONSTRAINT; Schema: public; Owner: dbmail; Tablespace:
--

ALTER TABLE ONLY dbmail_auto_replies
ADD CONSTRAINT dbmail_auto_replies_pkey PRIMARY KEY (user_idnr);

--
-- Name: dbmail_ccfield_pkey; Type: CONSTRAINT; Schema: public; Owner: dbmail; Tablespace:
--

ALTER TABLE ONLY dbmail_ccfield
ADD CONSTRAINT dbmail_ccfield_pkey PRIMARY KEY (id);

--
-- Name: dbmail_datefield_pkey; Type: CONSTRAINT; Schema: public; Owner: dbmail; Tablespace:
--

ALTER TABLE ONLY dbmail_datefield
ADD CONSTRAINT dbmail_datefield_pkey PRIMARY KEY (id);

--
-- Name: dbmail_envelope_pkey; Type: CONSTRAINT; Schema: public; Owner: dbmail; Tablespace:
--

ALTER TABLE ONLY dbmail_envelope
ADD CONSTRAINT dbmail_envelope_pkey PRIMARY KEY (id);

--
-- Name: dbmail_fromfield_pkey; Type: CONSTRAINT; Schema: public; Owner: dbmail; Tablespace:
--

ALTER TABLE ONLY dbmail_fromfield
ADD CONSTRAINT dbmail_fromfield_pkey PRIMARY KEY (id);

--
-- Name: dbmail_headername_pkey; Type: CONSTRAINT; Schema: public; Owner: dbmail; Tablespace:
--

ALTER TABLE ONLY dbmail_headername
ADD CONSTRAINT dbmail_headername_pkey PRIMARY KEY (id);

--
-- Name: dbmail_headervalue_pkey; Type: CONSTRAINT; Schema: public; Owner: dbmail; Tablespace:
--

ALTER TABLE ONLY dbmail_headervalue
ADD CONSTRAINT dbmail_headervalue_pkey PRIMARY KEY (id);

--
-- Name: dbmail_mailboxes_pkey; Type: CONSTRAINT; Schema: public; Owner: dbmail; Tablespace:
--

ALTER TABLE ONLY dbmail_mailboxes
ADD CONSTRAINT dbmail_mailboxes_pkey PRIMARY KEY (mailbox_idnr);

--
-- Name: dbmail_messageblks_pkey; Type: CONSTRAINT; Schema: public; Owner: dbmail; Tablespace:
--

ALTER TABLE ONLY dbmail_messageblks
ADD CONSTRAINT dbmail_messageblks_pkey PRIMARY KEY (messageblk_idnr);

--
-- Name: dbmail_messages_pkey; Type: CONSTRAINT; Schema: public; Owner: dbmail; Tablespace:
--

ALTER TABLE ONLY dbmail_messages
ADD CONSTRAINT dbmail_messages_pkey PRIMARY KEY (message_idnr);

--
-- Name: dbmail_pbsp_pkey; Type: CONSTRAINT; Schema: public; Owner: dbmail; Tablespace:
--

ALTER TABLE ONLY dbmail_pbsp
ADD CONSTRAINT dbmail_pbsp_pkey PRIMARY KEY (idnr);

--
-- Name: dbmail_physmessage_pkey; Type: CONSTRAINT; Schema: public; Owner: dbmail; Tablespace:
--

ALTER TABLE ONLY dbmail_physmessage
ADD CONSTRAINT dbmail_physmessage_pkey PRIMARY KEY (id);

--
-- Name: dbmail_referencesfield_pkey; Type: CONSTRAINT; Schema: public; Owner: dbmail; Tablespace:
--

ALTER TABLE ONLY dbmail_referencesfield
ADD CONSTRAINT dbmail_referencesfield_pkey PRIMARY KEY (id);

--
-- Name: dbmail_replytofield_pkey; Type: CONSTRAINT; Schema: public; Owner: dbmail; Tablespace:
--

ALTER TABLE ONLY dbmail_replytofield
ADD CONSTRAINT dbmail_replytofield_pkey PRIMARY KEY (id);

--
-- Name: dbmail_sievescripts_pkey; Type: CONSTRAINT; Schema: public; Owner: dbmail; Tablespace:
--

ALTER TABLE ONLY dbmail_sievescripts
ADD CONSTRAINT dbmail_sievescripts_pkey PRIMARY KEY (id);

--
-- Name: dbmail_subjectfield_pkey; Type: CONSTRAINT; Schema: public; Owner: dbmail; Tablespace:
--

ALTER TABLE ONLY dbmail_subjectfield
ADD CONSTRAINT dbmail_subjectfield_pkey PRIMARY KEY (id);

--
-- Name: dbmail_subscription_pkey; Type: CONSTRAINT; Schema: public; Owner: dbmail; Tablespace:
--

ALTER TABLE ONLY dbmail_subscription
ADD CONSTRAINT dbmail_subscription_pkey PRIMARY KEY (user_id, mailbox_id);

--
-- Name: dbmail_tofield_pkey; Type: CONSTRAINT; Schema: public; Owner: dbmail; Tablespace:
--

ALTER TABLE ONLY dbmail_tofield
ADD CONSTRAINT dbmail_tofield_pkey PRIMARY KEY (id);

--
-- Name: dbmail_users_pkey; Type: CONSTRAINT; Schema: public; Owner: dbmail; Tablespace:
--

ALTER TABLE ONLY dbmail_users
ADD CONSTRAINT dbmail_users_pkey PRIMARY KEY (user_idnr);

--
-- Name: dbmail_whitelist_pkey; Type: CONSTRAINT; Schema: public; Owner: dbmail; Tablespace:
--

ALTER TABLE ONLY dbmail_whitelist
ADD CONSTRAINT dbmail_whitelist_pkey PRIMARY KEY (id);

--
-- Name: domains_domainname_key; Type: CONSTRAINT; Schema: public; Owner: dbmail; Tablespace:
--

ALTER TABLE ONLY domains
ADD CONSTRAINT domains_domainname_key UNIQUE (domainname);

--
-- Name: domains_pkey; Type: CONSTRAINT; Schema: public; Owner: dbmail; Tablespace:
--

ALTER TABLE ONLY domains
ADD CONSTRAINT domains_pkey PRIMARY KEY (unic_id);

--
-- Name: email_templates_pkey; Type: CONSTRAINT; Schema: public; Owner: dbmail; Tablespace:
--

ALTER TABLE ONLY email_templates
ADD CONSTRAINT email_templates_pkey PRIMARY KEY (id);

--
-- Name: INDEX; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX "INDEX" ON dbmail_whitelist USING btree (user_idnr, mail);

--
-- Name: dbmail_aliases_alias_idx; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_aliases_alias_idx ON dbmail_aliases USING btree (alias);

--
-- Name: dbmail_aliases_alias_low_idx; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_aliases_alias_low_idx ON dbmail_aliases USING btree (lower((alias)::text));

--
-- Name: dbmail_ccfield_1; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE UNIQUE INDEX dbmail_ccfield_1 ON dbmail_ccfield USING btree (physmessage_id, id);

--
-- Name: dbmail_ccfield_2; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_ccfield_2 ON dbmail_ccfield USING btree (ccname);

--
-- Name: dbmail_ccfield_3; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_ccfield_3 ON dbmail_ccfield USING btree (ccaddr);

--
-- Name: dbmail_datefield_1; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE UNIQUE INDEX dbmail_datefield_1 ON dbmail_datefield USING btree (physmessage_id, id);

--
-- Name: dbmail_datefield_2; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_datefield_2 ON dbmail_datefield USING btree (datefield);

--
-- Name: dbmail_envelope_1; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE UNIQUE INDEX dbmail_envelope_1 ON dbmail_envelope USING btree (physmessage_id, id);

--
-- Name: dbmail_fromfield_1; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE UNIQUE INDEX dbmail_fromfield_1 ON dbmail_fromfield USING btree (physmessage_id, id);

--
-- Name: dbmail_fromfield_2; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_fromfield_2 ON dbmail_fromfield USING btree (fromaddr);

--
-- Name: dbmail_fromfield_3; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_fromfield_3 ON dbmail_fromfield USING btree (fromname);

--
-- Name: dbmail_headername_1; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE UNIQUE INDEX dbmail_headername_1 ON dbmail_headername USING btree (lower((headername)::text));

--
-- Name: dbmail_headervalue_1; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE UNIQUE INDEX dbmail_headervalue_1 ON dbmail_headervalue USING btree (physmessage_id, id);

--
-- Name: dbmail_headervalue_2; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_headervalue_2 ON dbmail_headervalue USING btree (physmessage_id);

--
-- Name: dbmail_headervalue_3; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_headervalue_3 ON dbmail_headervalue USING btree ("substring"(headervalue, 0, 255));

--
-- Name: dbmail_idx_ipnumber; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE UNIQUE INDEX dbmail_idx_ipnumber ON dbmail_pbsp USING btree (ipnumber);

--
-- Name: dbmail_idx_since; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_idx_since ON dbmail_pbsp USING btree (since);

--
-- Name: dbmail_mailboxes_name_idx; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_mailboxes_name_idx ON dbmail_mailboxes USING btree (name);

--
-- Name: dbmail_mailboxes_owner_idx; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_mailboxes_owner_idx ON dbmail_mailboxes USING btree (owner_idnr);

--
-- Name: dbmail_mailboxes_owner_name_idx; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE UNIQUE INDEX dbmail_mailboxes_owner_name_idx ON dbmail_mailboxes USING btree (owner_idnr, name);

--
-- Name: dbmail_messageblks_physmessage_idx; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_messageblks_physmessage_idx ON dbmail_messageblks USING btree (physmessage_id);

--
-- Name: dbmail_messageblks_physmessage_is_header_idx; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_messageblks_physmessage_is_header_idx ON dbmail_messageblks USING btree (physmessage_id, is_header);

--
-- Name: dbmail_messages_1; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_messages_1 ON dbmail_messages USING btree (mailbox_idnr);

--
-- Name: dbmail_messages_2; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_messages_2 ON dbmail_messages USING btree (physmessage_id);

--
-- Name: dbmail_messages_3; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_messages_3 ON dbmail_messages USING btree (seen_flag);

--
-- Name: dbmail_messages_4; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_messages_4 ON dbmail_messages USING btree (unique_id);

--
-- Name: dbmail_messages_5; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_messages_5 ON dbmail_messages USING btree (status);

--
-- Name: dbmail_messages_6; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_messages_6 ON dbmail_messages USING btree (status) WHERE (status < 2::smallint);

--
-- Name: dbmail_messages_7; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_messages_7 ON dbmail_messages USING btree (mailbox_idnr, status, seen_flag);

--
-- Name: dbmail_messages_8; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_messages_8 ON dbmail_messages USING btree (mailbox_idnr, status, recent_flag);

--
-- Name: dbmail_referencesfield_1; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE UNIQUE INDEX dbmail_referencesfield_1 ON dbmail_referencesfield USING btree (physmessage_id, referencesfield);

--
-- Name: dbmail_referencesfield_2; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_referencesfield_2 ON dbmail_referencesfield USING btree (referencesfield);

--
-- Name: dbmail_replytofield_1; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE UNIQUE INDEX dbmail_replytofield_1 ON dbmail_replytofield USING btree (physmessage_id, id);

--
-- Name: dbmail_replytofield_2; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_replytofield_2 ON dbmail_replytofield USING btree (replytoname);

--
-- Name: dbmail_replytofield_3; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_replytofield_3 ON dbmail_replytofield USING btree (replytoaddr);

--
-- Name: dbmail_sievescripts_1; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_sievescripts_1 ON dbmail_sievescripts USING btree (owner_idnr, name);

--
-- Name: dbmail_sievescripts_2; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_sievescripts_2 ON dbmail_sievescripts USING btree (owner_idnr, active);

--
-- Name: dbmail_subjectfield_1; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE UNIQUE INDEX dbmail_subjectfield_1 ON dbmail_subjectfield USING btree (physmessage_id, id);

--
-- Name: dbmail_subjectfield_2; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_subjectfield_2 ON dbmail_subjectfield USING btree (subjectfield);

--
-- Name: dbmail_tofield_1; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE UNIQUE INDEX dbmail_tofield_1 ON dbmail_tofield USING btree (physmessage_id, id);

--
-- Name: dbmail_tofield_2; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_tofield_2 ON dbmail_tofield USING btree (toname);

--
-- Name: dbmail_tofield_3; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_tofield_3 ON dbmail_tofield USING btree (toaddr);

--
-- Name: dbmail_users_2; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE INDEX dbmail_users_2 ON dbmail_users USING btree (lower((userid)::text));

--
-- Name: dbmail_users_name_idx; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE UNIQUE INDEX dbmail_users_name_idx ON dbmail_users USING btree (userid);

--
-- Name: replycache_1; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE UNIQUE INDEX replycache_1 ON dbmail_replycache USING btree (to_addr, from_addr, handle);

--
-- Name: template_id_index; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE UNIQUE INDEX template_id_index ON email_templates USING btree (id);

--
-- Name: usermap_idx_1; Type: INDEX; Schema: public; Owner: dbmail; Tablespace:
--

CREATE UNIQUE INDEX usermap_idx_1 ON dbmail_usermap USING btree (login, sock_allow, userid);

--
-- Name: on_add2wl; Type: TRIGGER; Schema: public; Owner: dbmail
--

CREATE TRIGGER on_add2wl
AFTER INSERT ON dbmail_whitelist
FOR EACH ROW
EXECUTE PROCEDURE spam2inbox();

--
-- Name: on_message; Type: TRIGGER; Schema: public; Owner: dbmail
--

CREATE TRIGGER on_message
AFTER INSERT ON dbmail_messages
FOR EACH ROW
EXECUTE PROCEDURE on_message();

--
-- Name: captcha_message_idnr_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dbmail
--

ALTER TABLE ONLY captcha
ADD CONSTRAINT captcha_message_idnr_fkey FOREIGN KEY (message_idnr) REFERENCES dbmail_messages(message_idnr) ON UPDATE CASCADE ON DELETE CASCADE;

--
-- Name: dbmail_acl_mailbox_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dbmail
--

ALTER TABLE ONLY dbmail_acl
ADD CONSTRAINT dbmail_acl_mailbox_id_fkey FOREIGN KEY (mailbox_id) REFERENCES dbmail_mailboxes(mailbox_idnr) ON UPDATE CASCADE ON DELETE CASCADE;

--
-- Name: dbmail_acl_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dbmail
--

ALTER TABLE ONLY dbmail_acl
ADD CONSTRAINT dbmail_acl_user_id_fkey FOREIGN KEY (user_id) REFERENCES dbmail_users(user_idnr) ON UPDATE CASCADE ON DELETE CASCADE;

--
-- Name: dbmail_auto_notifications_user_idnr_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dbmail
--

ALTER TABLE ONLY dbmail_auto_notifications
ADD CONSTRAINT dbmail_auto_notifications_user_idnr_fkey FOREIGN KEY (user_idnr) REFERENCES dbmail_users(user_idnr) ON UPDATE CASCADE ON DELETE CASCADE;

--
-- Name: dbmail_auto_replies_user_idnr_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dbmail
--

ALTER TABLE ONLY dbmail_auto_replies
ADD CONSTRAINT dbmail_auto_replies_user_idnr_fkey FOREIGN KEY (user_idnr) REFERENCES dbmail_users(user_idnr) ON UPDATE CASCADE ON DELETE CASCADE;

--
-- Name: dbmail_ccfield_physmessage_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dbmail
--

ALTER TABLE ONLY dbmail_ccfield
ADD CONSTRAINT dbmail_ccfield_physmessage_id_fkey FOREIGN KEY (physmessage_id) REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE CASCADE;

--
-- Name: dbmail_datefield_physmessage_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dbmail
--

ALTER TABLE ONLY dbmail_datefield
ADD CONSTRAINT dbmail_datefield_physmessage_id_fkey FOREIGN KEY (physmessage_id) REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE CASCADE;

--
-- Name: dbmail_envelope_physmessage_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dbmail
--

ALTER TABLE ONLY dbmail_envelope
ADD CONSTRAINT dbmail_envelope_physmessage_id_fkey FOREIGN KEY (physmessage_id) REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE CASCADE;

--
-- Name: dbmail_fromfield_physmessage_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dbmail
--

ALTER TABLE ONLY dbmail_fromfield
ADD CONSTRAINT dbmail_fromfield_physmessage_id_fkey FOREIGN KEY (physmessage_id) REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE CASCADE;

--
-- Name: dbmail_headervalue_headername_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dbmail
--

ALTER TABLE ONLY dbmail_headervalue
ADD CONSTRAINT dbmail_headervalue_headername_id_fkey FOREIGN KEY (headername_id) REFERENCES dbmail_headername(id) ON UPDATE CASCADE ON DELETE CASCADE;

--
-- Name: dbmail_headervalue_physmessage_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dbmail
--

ALTER TABLE ONLY dbmail_headervalue
ADD CONSTRAINT dbmail_headervalue_physmessage_id_fkey FOREIGN KEY (physmessage_id) REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE CASCADE;

--
-- Name: dbmail_mailboxes_owner_idnr_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dbmail
--

ALTER TABLE ONLY dbmail_mailboxes
ADD CONSTRAINT dbmail_mailboxes_owner_idnr_fkey FOREIGN KEY (owner_idnr) REFERENCES dbmail_users(user_idnr) ON UPDATE CASCADE ON DELETE CASCADE;

--
-- Name: dbmail_messageblks_physmessage_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dbmail
--

ALTER TABLE ONLY dbmail_messageblks
ADD CONSTRAINT dbmail_messageblks_physmessage_id_fkey FOREIGN KEY (physmessage_id) REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE CASCADE;

--
-- Name: dbmail_messages_mailbox_idnr_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dbmail
--

ALTER TABLE ONLY dbmail_messages
ADD CONSTRAINT dbmail_messages_mailbox_idnr_fkey FOREIGN KEY (mailbox_idnr) REFERENCES dbmail_mailboxes(mailbox_idnr) ON UPDATE CASCADE ON DELETE CASCADE;

--
-- Name: dbmail_messages_physmessage_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dbmail
--

ALTER TABLE ONLY dbmail_messages
ADD CONSTRAINT dbmail_messages_physmessage_id_fkey FOREIGN KEY (physmessage_id) REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE CASCADE;

--
-- Name: dbmail_referencesfield_physmessage_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dbmail
--

ALTER TABLE ONLY dbmail_referencesfield
ADD CONSTRAINT dbmail_referencesfield_physmessage_id_fkey FOREIGN KEY (physmessage_id) REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE CASCADE;

--
-- Name: dbmail_replytofield_physmessage_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dbmail
--

ALTER TABLE ONLY dbmail_replytofield
ADD CONSTRAINT dbmail_replytofield_physmessage_id_fkey FOREIGN KEY (physmessage_id) REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE CASCADE;

--
-- Name: dbmail_sievescripts_owner_idnr_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dbmail
--

ALTER TABLE ONLY dbmail_sievescripts
ADD CONSTRAINT dbmail_sievescripts_owner_idnr_fkey FOREIGN KEY (owner_idnr) REFERENCES dbmail_users(user_idnr) ON UPDATE CASCADE ON DELETE CASCADE;

--
-- Name: dbmail_subjectfield_physmessage_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dbmail
--

ALTER TABLE ONLY dbmail_subjectfield
ADD CONSTRAINT dbmail_subjectfield_physmessage_id_fkey FOREIGN KEY (physmessage_id) REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE CASCADE;

--
-- Name: dbmail_subscription_mailbox_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dbmail
--

ALTER TABLE ONLY dbmail_subscription
ADD CONSTRAINT dbmail_subscription_mailbox_id_fkey FOREIGN KEY (mailbox_id) REFERENCES dbmail_mailboxes(mailbox_idnr) ON UPDATE CASCADE ON DELETE CASCADE;

--
-- Name: dbmail_subscription_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dbmail
--

ALTER TABLE ONLY dbmail_subscription
ADD CONSTRAINT dbmail_subscription_user_id_fkey FOREIGN KEY (user_id) REFERENCES dbmail_users(user_idnr) ON UPDATE CASCADE ON DELETE CASCADE;

--
-- Name: dbmail_tofield_physmessage_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dbmail
--

ALTER TABLE ONLY dbmail_tofield
ADD CONSTRAINT dbmail_tofield_physmessage_id_fkey FOREIGN KEY (physmessage_id) REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE CASCADE;

--
-- Name: email_templates_owner_idnr_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dbmail
--

ALTER TABLE ONLY email_templates
ADD CONSTRAINT email_templates_owner_idnr_fkey FOREIGN KEY (owner_idnr) REFERENCES dbmail_users(user_idnr);

--
-- Name: user_prefs_spambox_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dbmail
--

ALTER TABLE ONLY user_prefs
ADD CONSTRAINT user_prefs_spambox_fkey FOREIGN KEY (spambox) REFERENCES dbmail_mailboxes(mailbox_idnr);

--
-- Name: user_prefs_template_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dbmail
--

ALTER TABLE ONLY user_prefs
ADD CONSTRAINT user_prefs_template_fkey FOREIGN KEY (template) REFERENCES email_templates(id) ON UPDATE SET DEFAULT ON DELETE SET DEFAULT;

--
-- Name: user_prefs_user_idnr_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dbmail
--

ALTER TABLE ONLY user_prefs
ADD CONSTRAINT user_prefs_user_idnr_fkey FOREIGN KEY (user_idnr) REFERENCES dbmail_users(user_idnr) ON UPDATE CASCADE ON DELETE CASCADE;

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

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

--
-- PostgreSQL database dump complete
--

в атаче (attachment) прикреплен файл структуры

тут реализован персональный шаблон, который отправляется в ответ (в attachment)

б) в атаче (attachment) прикреплены скрипты (не знаю кто их автор) можно реализовать самостоятельно

captcha.pl

Code:

#!/usr/bin/perl
use lib 'lib';
use strict;
use config;
use db;

open z,'-|','php',$path::captcha_php;
chomp(my $c=<z>);
my $t=substr $c,0,1,'';

$db::h->prepare('update captcha set captcha=?, ip=?, ua=? where key=?')
->execute($c,$ENV{REMOTE_ADDR},$ENV{HTTP_USER_AGENT},$ENV{QUERY_STRING});

undef $/;
print "Content-type: image/",(qw[jpeg gif x-png])[$t],"\n\n",<z>;
close z;

config.pm

Code:

package path;

$root='.';
$templates="$root/templates";

$captcha_php="$root/captcha/i.php";

package base;

$dbname='dbmail';
$host='localhost';

$dsn="dbi:Pg:dbname=$dbname;host=$host";
$login='dbmail';
$pass='dbm4i1n3w';

#########
666;

confirm.pl

Code:

#!/usr/bin/perl
use lib 'lib';
use strict;
use www;
use config;
use db;

$^{key}=$ENV{QUERY_STRING};
my $m;
(my $st=$db::h->prepare('select "from","to" from captcha where key=? limit 1'))
->execute($^{key});
my $vars=$st->fetchrow_hashref;

($st=$db::h->prepare('select count(*) from captcha where "from"=(select "from" from captcha where key=?) and confirmed=false'))
->execute($^{key});
$vars->{count}=($st->fetchrow_array)[0];
if($vars->{count}==0)
{
$^{M}=www::template 'not';
}
elsif($ENV{REQUEST_METHOD} eq 'POST')
{
($st=$db::h->prepare('select * from captcha(?,?::inet,?,?)'))
->execute($^{key},$ENV{REMOTE_ADDR},$ENV{HTTP_USER_AGENT},$ENV{POST}{captcha});

my $n=$st->rows;
if($n)
{
my ($list,$r);
my $s=$db::h->prepare('select to_char(internal_date,\'DD.MM.YYYY HH:MM:SS\'),subjectfield from dbmail_physmessage,dbmail_subjectfield where physmessage_id = (select physmessage_id from dbmail_messages where message_idnr=?) and dbmail_physmessage.id=physmessage_id');
while(($r)=$st->fetchrow_array)
{
$s->execute($r);
my($d,$j)=$s->fetchrow_array;
$list.=www::template('line',{subj=>$j,date=>$d});
}
# my($r,$s);
my @x=split/\n/,www::template('counttext');

$^{M}=www::template('ack',{%$vars,count=>$n,counttext=>$x[($n>1)+($n>4)],list=>$list});

}
else
{
$^{M}=www::template('nak',$vars);
}
}
else
{
$^{M}=www::template 'req',$vars;
}

www::ok;

Code:

package www;
use strict;
use qxp;
use config;
use L2;
#use encoding 'utf8';
my $utf8=0;

sub import{no strict 'refs';do{*{caller(0)."::$_"}=${"$_[0]::"}{$_}} for qw'template html depercent';} # out! out!

my %template;
sub template;
#require tags;
*template=templator{copy $template{$_[0]}||=slurp "$path::templates/$_[0].htm"}
# '<#','#>' => sub{package tags;eval$_[0]},
'${','}' => sub{$_[1]{$_[0]}},
'<@','@>' => sub{template $_[0]},
'<^','^>' => sub{$^{$_[0]}}
;

sub html;
*html=subst
'&'=>'&',
'<'=>'<'
;

sub depercent{map{s/%([\dA-Fa-f]{2})/pack 'C',hex $1/ge;utf8::decode $_ if $utf8;$_}@_}

sub ok{print template 'index'}

sub rf{
$_[0]{url}||=$ENV{HTTP_REFERER};
$_[0]{time}||=0;
print template 'refresh',$_[0];
}

$ENV{PERL}=$];
$ENV{URL}=s,^/,,?[grep!/\W/,split'/']:[] for copy $ENV{REDIRECT_URL};
$ENV{QUERY}={depercent split/&|=/,$ENV{QUERY_STRING}};
$ENV{POST}={depercent split/&|=/,do{read STDIN, my $x, $ENV{CONTENT_LENGTH};$x}} if $ENV{REQUEST_METHOD} eq 'POST' and $ENV{CONTENT_TYPE} =~ '^application/';
$ENV{COOKIE}={split/; |=/,$ENV{HTTP_COOKIE}};

666;

Code:

package qxp;
use strict;

sub H($){pack'H*',$_[0]}
sub by{map{[splice@_,1,$_[0]]}!($#_%$_[0])..$#_/$_[0]}
sub is($){$_ eq$_[0]}
sub cat{join'',@_}
sub list{@_}
sub wa{wantarray?@_:cat@_}
sub yuki(&@){shift->();@_}
sub fopen(;$){open my$z,$_[0]||$_;$z}{my %file;
sub file($){readline($file{$_[0]}||=fopen$_[0])}}
sub slurp(;$){wa map{yuki{close$_}<$_>}fopen$_[0]}
#sub spew($;$){sub{map{print $_ $_[1];close$_}fopen$_[0]}->($_[0],$#_?$_[1]:$_)}
sub spew($;$){open my$z,">$_[0]";print $z $#_?$_[1]:$_;close $z}
sub table{map{chomp;[split/\t/]}@_}
sub trim{map{s/^\s+|\s+$//g}@_?@_:$_}
sub count{my%z;$z{$_}++for@_;\%z}
sub uniq{keys%{count@_}}
sub copy{wa local@_=@_}
sub find(&@){no strict 'refs';local(*{(caller).'::a'})=\(my$a=$_[1]);&{$_[0]}||($a=$_)for(@_[2..$#_]);$a}
sub first(&@){$_[0]->()&&return$_ for@_[1..$#_]}
sub min{find{$a<$_}@_}
sub max{find{$a>$_}@_}
sub section{map{$_->[$_[0]]}@_[1..$#_]}
*#=sub{map$#$_,@_};
sub zip{map section($_,@_),0..max &#(@_)}
sub char{substr$#_?shift:$_,$_[0],1}
sub via(&$){local $_=$_[1];$_[0]->()}

*\=sub{map[section($_,@_)],0..max &#(@_)};

{my%ops;*?=sub{ref$_[-1]?map&?($_[0],@$_),&\(@_[1..$#_]):wa do{my@z=$_[1];@z=$ops{$_[0]}->($_,@z)for@_[2..$#_];@z}};
{no strict 'refs';(*$_,$ops{$_})=eval"sub{&?('$_',\@_)},sub{wa(\@_[1..\$#_]) $_ \$_[0]}" for qw'** =~ !~ * / % x + - . << >> < > <= >= lt gt le ge == != <=> eq ne cmp & | ^ && || .. ... = , => and or xor'}}
my%refs=(''=>'_',map{$_=>lc substr $_,0,1}qw{SCALAR ARRAY HASH CODE REF GLOB LVALUE});
sub refs{wa map{$refs{ref $_}||'o'}@_}
sub _{cat map{
is '' ? $_:
is 'a' ? @{$_[0]}:
()
}refs@_}

sub wday{(int(365.25*($_[2]-($_[1]<3)))+int(30.6*(1+$_[1]+12*($_[1]<3)))+$_[0]-621050)%7+1}
sub leap{$_[0]%4?0:$_[0]%100?1:$_[0]%400?0:1}
sub days{(31,(28+leap $_[1]),(31,30,31,30,31)x2)[$_[0]-1]}
sub cal{by 7,((undef)x(wday(1,@_)-1),1..days @_)}

sub all{@_==grep$_,@_}
sub one{1==grep$_,@_}
sub none{!grep$_,@_}
sub any{!&none}

sub import{no strict 'refs';*{caller(0)."::$_"}=${"$_[0]::"}{$_} for grep !/^(BEGIN|__ANON__|a|import)$/, keys%{"$_[0]::"}}

Code:

package L2;

use strict;
use qxp;

sub kludges{cat map{(join': ',@$_)."\n"}by 2,@_}

sub tree2(&$@){
wa map{
{
ARRAY=>sub{$_[0]->(1,$_[1],$_[2],cat tree2($_[0],$_[1]+1,@{$_[3]}))},
''=>sub{$_[0]->(0,@_[1..3])}
}->{ref($_->[1])}->(@_[0,1],@$_)
}by 2,@_[2..$#_]
}

sub templator(&@){
my($s,%s,%x)=shift;
my$x=join'|',map{$x{$_->[0]}=quotemeta$_->[1];$s{$_->[0]}=$_->[2];quotemeta$_->[0]}by 3,@_;
sub{wa map{s;((??{$x}))(.*?)(??{$x{$1}});$s{$1}($2,$_[1]);eg;$_}$s->($_[0])}
}

sub subst{
my%s=@_;
my$s=join'|',map quotemeta,keys%s;
sub{wa map{s/($s)/$s{$1}/eg;$_}copy @_}
}

# 2do: move to module
sub import{no strict 'refs';*{caller(0)."::$_"}=${"$_[0]::"}{$_} for grep !/^(BEGIN|__ANON__|a|import)$/, keys%{"$_[0]::"}}

666;

остальное в attachment...

в) админка написано, но не оформленная, не дописана, надо дописать со всем не много. в атаче (attachment)

г) в атаче (attachment) прикреплены скрипты (не знаю кто их автор) можно написать самому

Code:

package Gfn;

use strict;
use vars qw($VERSION @ISA @EXPORT @EXPORT_OK);
use DBI qw(:sql_types);
use CGI qw(:all escape);
require Exporter;
@ISA = qw(Exporter AutoLoader);
@EXPORT = qw();
$VERSION = '0.01';

my $cgi = new CGI;
my $base = "dbmail";
my $user = "dbmail";
my $pass = "123123";

##################New email domain##############
sub newD ()
{

my $mail=$ENV{POST}{name}.'@'.$ENV{POST}{domain};
my $dbh=DBI->connect("DBI:Pg:dbname=$base", "$user", "$pass",{PrintError => 0, RaiseError => 0}) or return 2;
my $ins1 = $dbh->prepare(q{insert into domains(domainname) values (?)});
my $ins2 = $dbh->prepare(q{insert into dbmail_users(userid,passwd) values (?,?))});
my $ins3 = $dbh->prepare(q{insert into dbmail_aliases(alias,deliver_to) values (?,(select user_idnr from dbmail_users where userid=?))});

$ins1->execute($ENV{POST}{domain});
$ins2->execute($mail,$ENV{POST}{passwd});
$ins3->execute($mail,$mail);
$dbh->disconnect();
return 0;
}

##################Delete Domain##################
sub DelD ()
{

my $dbh=DBI->connect("DBI:Pg:dbname=$base", "$user", "$pass",{PrintError => 0, RaiseError => 0}) or return 2;
my $ins1 = $dbh->prepare('delete from dbmail_aliases where alias ~ \'@'.$ENV{POST}{elid}.'$\'');
my $ins2 = $dbh->prepare('delete from dbmail_users where userid ~ \'@'.$ENV{POST}{elid}.'$\'');
my $ins3 = $dbh->prepare('delete from domains where domainname = ?');

$ins1->execute();
$ins2->execute();
$ins3->execute($ENV{POST}{elid});
$dbh->disconnect();
return 0;
}

#################New mail box####################
sub newM ()
{

my u=($s->fetchrow_array)[0];
my $mail=$ENV{POST}{name}.'@'.$ENV{POST}{domain};
my $q=$ENV{POST}{quota};
my $q=250 if $q>250;
my $q*=1024*1024;

my $dbh=DBI->connect("DBI:Pg:dbname=$base", "$user", "$pass",{PrintError => 0, RaiseError => 0}) or return 2;
my $ins1 = $dbh->prepare('insert into dbmail_users(userid,passwd,maxmail_size) values (?,?,?)');
my $ins2 = $dbh->prepare('select user_idnr from dbmail_users where userid=?');
my $ins3 = $dbh->prepare('insert into dbmail_aliases(alias,deliver_to) values (?,?)');
my $ins4 = $dbh->prepare('insert into dbmail_mailboxes(owner_idnr,name) values (?,\'SPAM\')');
my $ins5 = $dbh->prepare('insert into user_prefs(user_idnr,template,spambox) values (?,1,(select mailbox_idnr from dbmail_mailboxes where name=\'SPAM\' and owner_idnr=?)));

$ins1->execute(mail,$ENV{POST}{passwd},$q);
$ins2->execute($mail);
$ins3->execute($_.'@'.$ENV{POST}{domain},$u) for $ENV{POST}{name},split /\+/,$ENV{POST}{aliases};
$ins4->execute($u);
$ins5->execute($u,$u);

$dbh->disconnect();

return 0;
}
##########Set parameters for new email######

sub SetP ()
{

$q=$ENV{POST}{quota};
$q=250 if $q>250;
$q*=1024*1024;
$dbh=DBI->connect("DBI:Pg:dbname=$base", "$user", "$pass",{PrintError => 0, RaiseError => 0}) or return 2;
$dbh->prepare('update dbmail_users set maxmail_size=? where userid=?')->execute($q,$ENV{POST}{elid});
$dbh->prepare('update dbmail_users set passwd=? where userid=?')->execute($ENV{POST}{passwd},$ENV{POST}{elid}) if $ENV{POST}{passwd};

($s=$db::h->prepare('select user_idnr from dbmail_users where userid=?'))->execute($ENV{POST}{elid});
$u=($s->fetchrow_array)[0];

($d=$ENV{POST}{elid})=~s/^.*?@//;
$s=$db::h->prepare('insert into dbmail_aliases(alias,deliver_to) values (?,?)');
$dbh->prepare('delete from dbmail_aliases where deliver_to=?')->execute($u);
$s->execute($_.'@'.$d,$u) for split /\+/,$ENV{POST}{aliases};
$s->execute($ENV{POST}{elid},$u);

}

#############Disable Email##################
sub DisD ()
{

my $dbh=DBI->connect("DBI:Pg:dbname=$base", "$user", "$pass",{PrintError => 0, RaiseError => 0}) or return 2;
my $ins1 = $dbh->prepare('delete from dbmail_aliases where alias=?');

$ins1->execute($_) for split /, /, $ENV{QUERY}{elid};

$dbh->disconnect();

return 0;

}
############Clear Domain####################
sub CleD ()
{

my $dbh=DBI->connect("DBI:Pg:dbname=$base", "$user", "$pass",{PrintError => 0, RaiseError => 0}) or return 2;
$dbh->prepare( <<SQL )->execute($_) for split/, /, $ENV{QUERY}{elid};
delete from dbmail_messages where mailbox_idnr in (
select mailbox_idnr from dbmail_mailboxes where owner_idnr = (
select user_idnr from dbmail_users where userid = ?
) )
SQL

$dbh->disconnect();

return 0;

}
############Delete User######################
sub DelU ()
{

my $mail=$ENV{QUERY}{elid};
my $u=($s->fetchrow_array)[0];
my $dbh=DBI->connect("DBI:Pg:dbname=$base", "$user", "$pass",{PrintError => 0, RaiseError => 0}) or return 2;
my $ins1 = $dbh->prepare('select user_idnr from dbmail_users where userid=?');
my $ins2 = $dbh->prepare('delete from dbmail_users where user_idnr=?');
my $ins3 = $dbh->prepare('delete from dbmail_aliases where deliver_to=?');

$ins1->execute($mail);
$ins2->execute($u);
$ins3->execute($u);
$dbh->disconnect();

return 0;

}

Code:

#!/usr/bin/perl

use lib 'event';
use lib 'event/lib';
use config;
use www;
use db;

do 'dump';

$q=$ENV{POST}{quota};
$q=250 if $q>250;
$q*=1024*1024;
$db::h->prepare('update dbmail_users set maxmail_size=? where userid=?')->execute($q,$ENV{POST}{elid});
$db::h->prepare('update dbmail_users set passwd=? where userid=?')->execute($ENV{POST}{passwd},$ENV{POST}{elid}) if $ENV{POST}{passwd};

($s=$db::h->prepare('select user_idnr from dbmail_users where userid=?'))->execute($ENV{POST}{elid});
$u=($s->fetchrow_array)[0];

($d=$ENV{POST}{elid})=~s/^.*?@//;
$s=$db::h->prepare('insert into dbmail_aliases(alias,deliver_to) values (?,?)');
$db::h->prepare('delete from dbmail_aliases where deliver_to=?')->execute($u);
$s->execute($_.'@'.$d,$u) for split /\+/,$ENV{POST}{aliases};
$s->execute($ENV{POST}{elid},$u);

Code:

#!/usr/bin/perl

use lib 'event';
use lib 'event/lib';
use config;
use www;
use db;

do 'dump';

$db::h->prepare( <<SQL )->execute($_,$_) for split/, /, $ENV{QUERY}{elid};
insert into dbmail_aliases(alias,deliver_to) values (?,(select user_idnr from dbmail_users where userid=?))

SQL

Code:

#!/usr/bin/perl

use Gfn;

###########################New email domain##############################################
$mail=$ENV{POST}{name}.'@'.$ENV{POST}{domain};
$db::h->prepare('insert into domains(domainname) values (?)')->execute($ENV{POST}{domain});
$db::h->prepare('insert into dbmail_users(userid,passwd) values (?,?)')->execute($mail,$ENV{POST}{passwd});
$db::h->prepare('insert into dbmail_aliases(alias,deliver_to) values (?,(select user_idnr from dbmail_users where userid=?))')->execute($mail,$mail);

###########################Delete emaildomain############################################
$db::h->prepare('delete from dbmail_aliases where alias ~ \'@'.$ENV{POST}{elid}.'$\'')->execute();
$db::h->prepare('delete from dbmail_users where userid ~ \'@'.$ENV{POST}{elid}.'$\'')->execute();
$db::h->prepare('delete from domains where domainname = ?')->execute($ENV{POST}{elid});

###########################New email#####################################################
$mail=$ENV{POST}{name}.'@'.$ENV{POST}{domain};
$q=$ENV{POST}{quota};
$q=250 if $q>250;
$q*=1024*1024;
$db::h->prepare('insert into dbmail_users(userid,passwd,maxmail_size) values (?,?,?)')->execute($mail,$ENV{POST}{passwd},$q);
($s=$db::h->prepare('select user_idnr from dbmail_users where userid=?'))->execute($mail);
$u=($s->fetchrow_array)[0];
$db::h->prepare('insert into dbmail_aliases(alias,deliver_to) values (?,?)')->execute($_.'@'.$ENV{POST}{domain},$u) for $ENV{POST}{name},split /\+/,$ENV{POST}{aliases};
$db::h->prepare('insert into dbmail_mailboxes(owner_idnr,name) values (?,\'SPAM\')')->execute($u);
$db::h->prepare('insert into user_prefs(user_idnr,template,spambox) values (?,1,(select mailbox_idnr from dbmail_mailboxes where name=\'SPAM\' and owner_idnr=?))')->execute($u,$u);

############################Set params new email##################################################
$q=$ENV{POST}{quota};
$q=250 if $q>250;
$q*=1024*1024;
$db::h->prepare('update dbmail_users set maxmail_size=? where userid=?')->execute($q,$ENV{POST}{elid});
$db::h->prepare('update dbmail_users set passwd=? where userid=?')->execute($ENV{POST}{passwd},$ENV{POST}{elid}) if $ENV{POST}{passwd};
($s=$db::h->prepare('select user_idnr from dbmail_users where userid=?'))->execute($ENV{POST}{elid});
$u=($s->fetchrow_array)[0];
($d=$ENV{POST}{elid})=~s/^.*?@//;
$s=$db::h->prepare('insert into dbmail_aliases(alias,deliver_to) values (?,?)');
$db::h->prepare('delete from dbmail_aliases where deliver_to=?')->execute($u);
$s->execute($_.'@'.$d,$u) for split /\+/,$ENV{POST}{aliases};
$s->execute($ENV{POST}{elid},$u);

###########################Disable email###################################################
$db::h->prepare('delete from dbmail_aliases where alias=?')->execute($_) for split /, /, $ENV{QUERY}{elid};

###########################Clear domain####################################################
$db::h->prepare( <<SQL )->execute($_) for split/, /, $ENV{QUERY}{elid};
delete from dbmail_messages where mailbox_idnr in (
select mailbox_idnr from dbmail_mailboxes where owner_idnr = (
select user_idnr from dbmail_users where userid = ?
) )
SQL

############################Delete user#################################################
$mail=$ENV{QUERY}{elid};
($s=$db::h->prepare('select user_idnr from dbmail_users where userid=?'))->execute($mail);
$u=($s->fetchrow_array)[0];
$db::h->prepare('delete from dbmail_aliases where deliver_to=?')->execute($u);
$db::h->prepare('delete from dbmail_users where user_idnr=?')->execute($u);

все прицепленно в Attachments в zip

	Author	Topic: Защита от спама с помощью Captcha (DBmail + PgSQL) (Read 1947 times)
0 Members and 1 Guest are viewing this topic.