From ed826e98738bed911f5d53c032bf6537101f76b4 Mon Sep 17 00:00:00 2001 From: Frank Brehm Date: Tue, 16 Apr 2024 17:29:11 +0200 Subject: [PATCH] Completing data model for postfix logs --- db/postfix/postfix-pgsql-create-1.1.sql | 74 +++++++++++++++++++++++++ 1 file changed, 74 insertions(+) diff --git a/db/postfix/postfix-pgsql-create-1.1.sql b/db/postfix/postfix-pgsql-create-1.1.sql index d8129c1..9336eb0 100644 --- a/db/postfix/postfix-pgsql-create-1.1.sql +++ b/db/postfix/postfix-pgsql-create-1.1.sql @@ -26,6 +26,11 @@ SET row_security = off; SET default_tablespace = ''; SET default_table_access_method = heap; +CREATE TYPE data_pair AS ( + value int, + total int +); + CREATE TABLE public.mailhosts ( id serial PRIMARY KEY, hostname character varying(64) NOT NULL, @@ -39,4 +44,73 @@ ALTER TABLE public.mailhosts OWNER TO postfix; CREATE INDEX mailhosts_hostname_idx ON public.mailhosts USING btree (hostname); CREATE INDEX mailhosts_fqdn_idx ON public.mailhosts USING btree (fqdn); +CREATE TABLE public.transactions ( + id bigserial PRIMARY KEY, + mailhost_id int NOT NULL, + postfix_id character varying(64) NOT NULL, + start_date timestamp with time zone, + end_date timestamp with time zone, + message_id character varying(255), + client_host character varying(255), + client_addr inet, + ehlo data_pair, + starttls data_pair, + sent_quit data_pair, + auth data_pair, + commands data_pair, + rcpt data_pair, + data data_pair, + mail data_pair, + from_address character varying(255), + smtpd_pid int, + tls_version character varying(32), + tls_cipher character varying(32), + size int, + nr_rcpt smallint, + finished boolean DEFAULT FALSE, + dkim_selector character varying(64), + dkim_domain character varying(64), + pickup_uid int, + pickup_from character varying(255) +); + +ALTER TABLE public.transactions OWNER TO postfix; + +CREATE INDEX transactions_mailhost_id_idx ON public.transactions USING btree (mailhost_id); +CREATE INDEX transactions_postfix_id_idx ON public.transactions USING btree (postfix_id); +CREATE INDEX transactions_start_idx ON public.transactions USING btree (start_date); + +ALTER TABLE ONLY public.transactions + ADD CONSTRAINT transactions_mailhost_id_fkey FOREIGN KEY (mailhost_id) REFERENCES public.mailhosts(id) ON DELETE CASCADE; + + +CREATE TABLE public.deliver_actions ( + id bigserial PRIMARY KEY, + transaction_id bigint NOT NULL, + command character varying(30) NOT NULL, + to_address character varying(255), + origin_to character varying(255), + pid int, + date timestamp with time zone, + relay character varying(255), + delay_total real, + time_before_queue real, + time_in_queue real, + time_conn_setup real, + time_xmission real, + dsn character varying(64), + status character varying(64), + message text, + remote_id character varying(255) +); + +ALTER TABLE public.deliver_actions OWNER TO postfix; + +CREATE INDEX deliver_actions_transaction_id_idx ON public.deliver_actions USING btree (transaction_id); +CREATE INDEX deliver_actions_command_idx ON public.deliver_actions USING btree (command); + +ALTER TABLE ONLY public.deliver_actions + ADD CONSTRAINT deliver_actions_transaction_id_fkey FOREIGN KEY (transaction_id) REFERENCES public.transactions(id) ON DELETE CASCADE; + + -- 2.39.5