123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650 |
- --
- -- PostgreSQL database dump
- --
-
- -- Dumped from database version 11.2 (Debian 11.2-1.pgdg90+1)
- -- Dumped by pg_dump version 11.2 (Debian 11.2-1.pgdg90+1)
-
- SET statement_timeout = 0;
- SET lock_timeout = 0;
- SET idle_in_transaction_session_timeout = 0;
- SET client_encoding = 'UTF8';
- SET standard_conforming_strings = on;
- SELECT pg_catalog.set_config('search_path', '', false);
- SET check_function_bodies = false;
- SET client_min_messages = warning;
- SET row_security = off;
-
- --
- -- Name: merge_quota(); Type: FUNCTION; Schema: public; Owner: -
- --
-
- CREATE FUNCTION public.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;
- $$;
-
-
- --
- -- Name: merge_quota2(); Type: FUNCTION; Schema: public; Owner: -
- --
-
- CREATE FUNCTION public.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;
- $$;
-
-
- SET default_tablespace = '';
-
- SET default_with_oids = false;
-
- --
- -- Name: admin; Type: TABLE; Schema: public; Owner: -
- --
-
- CREATE TABLE public.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,
- phone character varying(30) DEFAULT ''::character varying NOT NULL,
- email_other character varying(255) DEFAULT ''::character varying NOT NULL,
- token character varying(255) DEFAULT ''::character varying NOT NULL,
- token_validity timestamp with time zone DEFAULT '2000-01-01 00:00:00+00'::timestamp with time zone
- );
-
-
- --
- -- Name: TABLE admin; Type: COMMENT; Schema: public; Owner: -
- --
-
- COMMENT ON TABLE public.admin IS 'Postfix Admin - Virtual Admins';
-
-
- --
- -- Name: alias; Type: TABLE; Schema: public; Owner: -
- --
-
- CREATE TABLE public.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
- );
-
-
- --
- -- Name: TABLE alias; Type: COMMENT; Schema: public; Owner: -
- --
-
- COMMENT ON TABLE public.alias IS 'Postfix Admin - Virtual Aliases';
-
-
- --
- -- Name: alias_domain; Type: TABLE; Schema: public; Owner: -
- --
-
- CREATE TABLE public.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
- );
-
-
- --
- -- Name: TABLE alias_domain; Type: COMMENT; Schema: public; Owner: -
- --
-
- COMMENT ON TABLE public.alias_domain IS 'Postfix Admin - Domain Aliases';
-
-
- --
- -- Name: config; Type: TABLE; Schema: public; Owner: -
- --
-
- CREATE TABLE public.config (
- id integer NOT NULL,
- name character varying(20) NOT NULL,
- value character varying(20) NOT NULL
- );
-
-
- --
- -- Name: config_id_seq; Type: SEQUENCE; Schema: public; Owner: -
- --
-
- CREATE SEQUENCE public.config_id_seq
- AS integer
- START WITH 1
- INCREMENT BY 1
- NO MINVALUE
- NO MAXVALUE
- CACHE 1;
-
-
- --
- -- Name: config_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
- --
-
- ALTER SEQUENCE public.config_id_seq OWNED BY public.config.id;
-
-
- --
- -- Name: domain; Type: TABLE; Schema: public; Owner: -
- --
-
- CREATE TABLE public.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
- );
-
-
- --
- -- Name: TABLE domain; Type: COMMENT; Schema: public; Owner: -
- --
-
- COMMENT ON TABLE public.domain IS 'Postfix Admin - Virtual Domains';
-
-
- --
- -- Name: domain_admins; Type: TABLE; Schema: public; Owner: -
- --
-
- CREATE TABLE public.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
- );
-
-
- --
- -- Name: TABLE domain_admins; Type: COMMENT; Schema: public; Owner: -
- --
-
- COMMENT ON TABLE public.domain_admins IS 'Postfix Admin - Domain Admins';
-
-
- --
- -- Name: fetchmail; Type: TABLE; Schema: public; Owner: -
- --
-
- CREATE TABLE public.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,
- sslcertck boolean DEFAULT false NOT NULL,
- sslcertpath character varying(255) DEFAULT ''::character varying,
- sslfingerprint character varying(255) DEFAULT ''::character varying,
- domain character varying(255) DEFAULT ''::character varying,
- active boolean DEFAULT false NOT NULL,
- created timestamp with time zone DEFAULT '2000-01-01 00:00:00+00'::timestamp with time zone,
- modified timestamp with time zone DEFAULT now(),
- CONSTRAINT fetchmail_protocol_check CHECK (((protocol)::text = ANY ((ARRAY['POP3'::character varying, 'IMAP'::character varying, 'POP2'::character varying, 'ETRN'::character varying, 'AUTO'::character varying])::text[]))),
- CONSTRAINT fetchmail_src_auth_check CHECK (((src_auth)::text = ANY ((ARRAY['password'::character varying, 'kerberos_v5'::character varying, 'kerberos'::character varying, 'kerberos_v4'::character varying, 'gssapi'::character varying, 'cram-md5'::character varying, 'otp'::character varying, 'ntlm'::character varying, 'msn'::character varying, 'ssh'::character varying, 'any'::character varying])::text[])))
- );
-
-
- --
- -- Name: fetchmail_id_seq; Type: SEQUENCE; Schema: public; Owner: -
- --
-
- CREATE SEQUENCE public.fetchmail_id_seq
- AS integer
- START WITH 1
- INCREMENT BY 1
- NO MINVALUE
- NO MAXVALUE
- CACHE 1;
-
-
- --
- -- Name: fetchmail_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
- --
-
- ALTER SEQUENCE public.fetchmail_id_seq OWNED BY public.fetchmail.id;
-
-
- --
- -- Name: log; Type: TABLE; Schema: public; Owner: -
- --
-
- CREATE TABLE public.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,
- id integer NOT NULL
- );
-
-
- --
- -- Name: TABLE log; Type: COMMENT; Schema: public; Owner: -
- --
-
- COMMENT ON TABLE public.log IS 'Postfix Admin - Log';
-
-
- --
- -- Name: log_id_seq; Type: SEQUENCE; Schema: public; Owner: -
- --
-
- CREATE SEQUENCE public.log_id_seq
- AS integer
- START WITH 1
- INCREMENT BY 1
- NO MINVALUE
- NO MAXVALUE
- CACHE 1;
-
-
- --
- -- Name: log_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
- --
-
- ALTER SEQUENCE public.log_id_seq OWNED BY public.log.id;
-
-
- --
- -- Name: mailbox; Type: TABLE; Schema: public; Owner: -
- --
-
- CREATE TABLE public.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,
- phone character varying(30) DEFAULT ''::character varying NOT NULL,
- email_other character varying(255) DEFAULT ''::character varying NOT NULL,
- token character varying(255) DEFAULT ''::character varying NOT NULL,
- token_validity timestamp with time zone DEFAULT '2000-01-01 00:00:00+00'::timestamp with time zone
- );
-
-
- --
- -- Name: TABLE mailbox; Type: COMMENT; Schema: public; Owner: -
- --
-
- COMMENT ON TABLE public.mailbox IS 'Postfix Admin - Virtual Mailboxes';
-
-
- --
- -- Name: quota; Type: TABLE; Schema: public; Owner: -
- --
-
- CREATE TABLE public.quota (
- username character varying(255) NOT NULL,
- path character varying(100) NOT NULL,
- current bigint DEFAULT 0 NOT NULL
- );
-
-
- --
- -- Name: quota2; Type: TABLE; Schema: public; Owner: -
- --
-
- CREATE TABLE public.quota2 (
- username character varying(100) NOT NULL,
- bytes bigint DEFAULT 0 NOT NULL,
- messages integer DEFAULT 0 NOT NULL
- );
-
-
- --
- -- Name: vacation; Type: TABLE; Schema: public; Owner: -
- --
-
- CREATE TABLE public.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+00'::timestamp with time zone,
- activeuntil timestamp with time zone DEFAULT '2038-01-18 00:00:00+00'::timestamp with time zone,
- interval_time integer DEFAULT 0 NOT NULL
- );
-
-
- --
- -- Name: vacation_notification; Type: TABLE; Schema: public; Owner: -
- --
-
- CREATE TABLE public.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
- );
-
-
- --
- -- Name: config id; Type: DEFAULT; Schema: public; Owner: -
- --
-
- ALTER TABLE ONLY public.config ALTER COLUMN id SET DEFAULT nextval('public.config_id_seq'::regclass);
-
-
- --
- -- Name: fetchmail id; Type: DEFAULT; Schema: public; Owner: -
- --
-
- ALTER TABLE ONLY public.fetchmail ALTER COLUMN id SET DEFAULT nextval('public.fetchmail_id_seq'::regclass);
-
-
- --
- -- Name: log id; Type: DEFAULT; Schema: public; Owner: -
- --
-
- ALTER TABLE ONLY public.log ALTER COLUMN id SET DEFAULT nextval('public.log_id_seq'::regclass);
-
-
- --
- -- Name: admin admin_key; Type: CONSTRAINT; Schema: public; Owner: -
- --
-
- ALTER TABLE ONLY public.admin
- ADD CONSTRAINT admin_key PRIMARY KEY (username);
-
-
- --
- -- Name: alias_domain alias_domain_pkey; Type: CONSTRAINT; Schema: public; Owner: -
- --
-
- ALTER TABLE ONLY public.alias_domain
- ADD CONSTRAINT alias_domain_pkey PRIMARY KEY (alias_domain);
-
-
- --
- -- Name: alias alias_key; Type: CONSTRAINT; Schema: public; Owner: -
- --
-
- ALTER TABLE ONLY public.alias
- ADD CONSTRAINT alias_key PRIMARY KEY (address);
-
-
- --
- -- Name: config config_name_key; Type: CONSTRAINT; Schema: public; Owner: -
- --
-
- ALTER TABLE ONLY public.config
- ADD CONSTRAINT config_name_key UNIQUE (name);
-
-
- --
- -- Name: config config_pkey; Type: CONSTRAINT; Schema: public; Owner: -
- --
-
- ALTER TABLE ONLY public.config
- ADD CONSTRAINT config_pkey PRIMARY KEY (id);
-
-
- --
- -- Name: domain domain_key; Type: CONSTRAINT; Schema: public; Owner: -
- --
-
- ALTER TABLE ONLY public.domain
- ADD CONSTRAINT domain_key PRIMARY KEY (domain);
-
-
- --
- -- Name: fetchmail fetchmail_pkey; Type: CONSTRAINT; Schema: public; Owner: -
- --
-
- ALTER TABLE ONLY public.fetchmail
- ADD CONSTRAINT fetchmail_pkey PRIMARY KEY (id);
-
-
- --
- -- Name: log log_pkey; Type: CONSTRAINT; Schema: public; Owner: -
- --
-
- ALTER TABLE ONLY public.log
- ADD CONSTRAINT log_pkey PRIMARY KEY (id);
-
-
- --
- -- Name: mailbox mailbox_key; Type: CONSTRAINT; Schema: public; Owner: -
- --
-
- ALTER TABLE ONLY public.mailbox
- ADD CONSTRAINT mailbox_key PRIMARY KEY (username);
-
-
- --
- -- Name: quota2 quota2_pkey; Type: CONSTRAINT; Schema: public; Owner: -
- --
-
- ALTER TABLE ONLY public.quota2
- ADD CONSTRAINT quota2_pkey PRIMARY KEY (username);
-
-
- --
- -- Name: quota quota_pkey; Type: CONSTRAINT; Schema: public; Owner: -
- --
-
- ALTER TABLE ONLY public.quota
- ADD CONSTRAINT quota_pkey PRIMARY KEY (username, path);
-
-
- --
- -- Name: vacation_notification vacation_notification_pkey; Type: CONSTRAINT; Schema: public; Owner: -
- --
-
- ALTER TABLE ONLY public.vacation_notification
- ADD CONSTRAINT vacation_notification_pkey PRIMARY KEY (on_vacation, notified);
-
-
- --
- -- Name: vacation vacation_pkey; Type: CONSTRAINT; Schema: public; Owner: -
- --
-
- ALTER TABLE ONLY public.vacation
- ADD CONSTRAINT vacation_pkey PRIMARY KEY (email);
-
- --
- -- Name: alias_address_active; Type: INDEX; Schema: public; Owner: -
- --
-
- CREATE INDEX alias_address_active ON public.alias USING btree (address, active);
-
-
- --
- -- Name: alias_domain_active; Type: INDEX; Schema: public; Owner: -
- --
-
- CREATE INDEX alias_domain_active ON public.alias_domain USING btree (alias_domain, active);
-
-
- --
- -- Name: alias_domain_idx; Type: INDEX; Schema: public; Owner: -
- --
-
- CREATE INDEX alias_domain_idx ON public.alias USING btree (domain);
-
-
- --
- -- Name: domain_domain_active; Type: INDEX; Schema: public; Owner: -
- --
-
- CREATE INDEX domain_domain_active ON public.domain USING btree (domain, active);
-
-
- --
- -- Name: log_domain_timestamp_idx; Type: INDEX; Schema: public; Owner: -
- --
-
- CREATE INDEX log_domain_timestamp_idx ON public.log USING btree (domain, "timestamp");
-
-
- --
- -- Name: mailbox_domain_idx; Type: INDEX; Schema: public; Owner: -
- --
-
- CREATE INDEX mailbox_domain_idx ON public.mailbox USING btree (domain);
-
-
- --
- -- Name: mailbox_username_active; Type: INDEX; Schema: public; Owner: -
- --
-
- CREATE INDEX mailbox_username_active ON public.mailbox USING btree (username, active);
-
-
- --
- -- Name: vacation_email_active; Type: INDEX; Schema: public; Owner: -
- --
-
- CREATE INDEX vacation_email_active ON public.vacation USING btree (email, active);
-
-
- --
- -- Name: quota mergequota; Type: TRIGGER; Schema: public; Owner: -
- --
-
- CREATE TRIGGER mergequota BEFORE INSERT ON public.quota FOR EACH ROW EXECUTE PROCEDURE public.merge_quota();
-
-
- --
- -- Name: quota2 mergequota2; Type: TRIGGER; Schema: public; Owner: -
- --
-
- CREATE TRIGGER mergequota2 BEFORE INSERT ON public.quota2 FOR EACH ROW EXECUTE PROCEDURE public.merge_quota2();
-
-
- --
- -- Name: alias_domain alias_domain_alias_domain_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
- --
-
- ALTER TABLE ONLY public.alias_domain
- ADD CONSTRAINT alias_domain_alias_domain_fkey FOREIGN KEY (alias_domain) REFERENCES public.domain(domain) ON DELETE CASCADE;
-
-
- --
- -- Name: alias alias_domain_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
- --
-
- ALTER TABLE ONLY public.alias
- ADD CONSTRAINT alias_domain_fkey FOREIGN KEY (domain) REFERENCES public.domain(domain);
-
-
- --
- -- Name: alias_domain alias_domain_target_domain_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
- --
-
- ALTER TABLE ONLY public.alias_domain
- ADD CONSTRAINT alias_domain_target_domain_fkey FOREIGN KEY (target_domain) REFERENCES public.domain(domain) ON DELETE CASCADE;
-
-
- --
- -- Name: domain_admins domain_admins_domain_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
- --
-
- ALTER TABLE ONLY public.domain_admins
- ADD CONSTRAINT domain_admins_domain_fkey FOREIGN KEY (domain) REFERENCES public.domain(domain);
-
-
- --
- -- Name: mailbox mailbox_domain_fkey1; Type: FK CONSTRAINT; Schema: public; Owner: -
- --
-
- ALTER TABLE ONLY public.mailbox
- ADD CONSTRAINT mailbox_domain_fkey1 FOREIGN KEY (domain) REFERENCES public.domain(domain);
-
-
- --
- -- Name: vacation vacation_domain_fkey1; Type: FK CONSTRAINT; Schema: public; Owner: -
- --
-
- ALTER TABLE ONLY public.vacation
- ADD CONSTRAINT vacation_domain_fkey1 FOREIGN KEY (domain) REFERENCES public.domain(domain);
-
-
- --
- -- Name: vacation_notification vacation_notification_on_vacation_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
- --
-
- ALTER TABLE ONLY public.vacation_notification
- ADD CONSTRAINT vacation_notification_on_vacation_fkey FOREIGN KEY (on_vacation) REFERENCES public.vacation(email) ON DELETE CASCADE;
-
-
- --
- -- PostgreSQL database dump complete
- --
|