2 -- PostgreSQL database dump
5 SET statement_timeout
= 0;
6 SET client_encoding
= 'UTF8';
7 SET standard_conforming_strings
= on;
8 SET check_function_bodies
= false;
9 SET client_min_messages
= warning
;
12 -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
15 CREATE EXTENSION
IF NOT EXISTS plpgsql
WITH SCHEMA pg_catalog
;
19 -- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
22 COMMENT ON EXTENSION plpgsql
IS 'PL/pgSQL procedural language';
25 SET search_path
= public, pg_catalog
;
28 -- Name: merge_quota(); Type: FUNCTION; Schema: public; Owner: postgres
31 CREATE FUNCTION merge_quota() RETURNS trigger
35 UPDATE quota
SET current = NEW.
current + current WHERE username
= NEW.username
AND path = NEW.
path;
45 ALTER FUNCTION public.
merge_quota() OWNER TO postgres
;
48 -- Name: merge_quota2(); Type: FUNCTION; Schema: public; Owner: postgres
51 CREATE FUNCTION merge_quota2() RETURNS trigger
55 IF NEW.messages
< 0 OR NEW.messages
IS NULL THEN
56 -- ugly kludge: we came here from this function, really do try to insert
57 IF NEW.messages
IS NULL THEN
60 NEW.messages
= -NEW.messages
;
66 UPDATE quota2
SET bytes
= bytes
+ NEW.bytes
,
67 messages
= messages
+ NEW.messages
68 WHERE username
= NEW.username
;
74 IF NEW.messages
= 0 THEN
75 INSERT INTO quota2 (bytes
, messages
, username
) VALUES (NEW.bytes
, NULL, NEW.username
);
77 INSERT INTO quota2 (bytes
, messages
, username
) VALUES (NEW.bytes
, -NEW.messages
, NEW.username
);
80 EXCEPTION WHEN unique_violation
THEN
81 -- someone just inserted the record, update it
88 ALTER FUNCTION public.
merge_quota2() OWNER TO postgres
;
90 SET default_tablespace
= '';
92 SET default_with_oids
= true;
95 -- Name: admin; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
99 username
character varying(255) NOT NULL,
100 password character varying(255) DEFAULT ''::character varying NOT NULL,
101 created
timestamp with time zone DEFAULT now(),
102 modified
timestamp with time zone DEFAULT now(),
103 active
boolean DEFAULT true NOT NULL
107 ALTER TABLE public.
admin OWNER TO postgres
;
110 -- Name: TABLE admin; Type: COMMENT; Schema: public; Owner: postgres
113 COMMENT ON TABLE admin IS 'Postfix Admin - Virtual Admins';
117 -- Name: alias; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
121 address
character varying(255) NOT NULL,
123 domain character varying(255) NOT NULL,
124 created
timestamp with time zone DEFAULT now(),
125 modified
timestamp with time zone DEFAULT now(),
126 active
boolean DEFAULT true NOT NULL
130 ALTER TABLE public.
alias OWNER TO postgres
;
133 -- Name: TABLE alias; Type: COMMENT; Schema: public; Owner: postgres
136 COMMENT ON TABLE alias IS 'Postfix Admin - Virtual Aliases';
139 SET default_with_oids
= false;
142 -- Name: alias_domain; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
145 CREATE TABLE alias_domain (
146 alias_domain
character varying(255) NOT NULL,
147 target_domain
character varying(255) NOT NULL,
148 created
timestamp with time zone DEFAULT now(),
149 modified
timestamp with time zone DEFAULT now(),
150 active
boolean DEFAULT true NOT NULL
154 ALTER TABLE public.alias_domain
OWNER TO postgres
;
157 -- Name: TABLE alias_domain; Type: COMMENT; Schema: public; Owner: postgres
160 COMMENT ON TABLE alias_domain
IS 'Postfix Admin - Domain Aliases';
163 SET default_with_oids
= true;
166 -- Name: config; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
169 CREATE TABLE config (
171 name character varying(20) NOT NULL,
172 value character varying(20) NOT NULL
176 ALTER TABLE public.config
OWNER TO postgres
;
179 -- Name: config_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
182 CREATE SEQUENCE config_id_seq
190 ALTER TABLE public.config_id_seq
OWNER TO postgres
;
193 -- Name: config_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
196 ALTER SEQUENCE config_id_seq
OWNED BY config.
id;
200 -- Name: domain; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
203 CREATE TABLE domain (
204 domain character varying(255) NOT NULL,
205 description
character varying(255) DEFAULT ''::character varying NOT NULL,
206 aliases
integer DEFAULT 0 NOT NULL,
207 mailboxes
integer DEFAULT 0 NOT NULL,
208 maxquota
bigint DEFAULT 0 NOT NULL,
209 quota
bigint DEFAULT 0 NOT NULL,
210 transport
character varying(255),
211 backupmx
boolean DEFAULT false NOT NULL,
212 created
timestamp with time zone DEFAULT now(),
213 modified
timestamp with time zone DEFAULT now(),
214 active
boolean DEFAULT true NOT NULL
218 ALTER TABLE public.
domain OWNER TO postgres
;
221 -- Name: TABLE domain; Type: COMMENT; Schema: public; Owner: postgres
224 COMMENT ON TABLE domain IS 'Postfix Admin - Virtual Domains';
228 -- Name: domain_admins; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
231 CREATE TABLE domain_admins (
232 username
character varying(255) NOT NULL,
233 domain character varying(255) NOT NULL,
234 created
timestamp with time zone DEFAULT now(),
235 active
boolean DEFAULT true NOT NULL
239 ALTER TABLE public.domain_admins
OWNER TO postgres
;
242 -- Name: TABLE domain_admins; Type: COMMENT; Schema: public; Owner: postgres
245 COMMENT ON TABLE domain_admins
IS 'Postfix Admin - Domain Admins';
249 -- Name: fetchmail; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
252 CREATE TABLE fetchmail (
254 mailbox
character varying(255) DEFAULT ''::character varying NOT NULL,
255 src_server
character varying(255) DEFAULT ''::character varying NOT NULL,
256 src_auth
character varying(15) NOT NULL,
257 src_user
character varying(255) DEFAULT ''::character varying NOT NULL,
258 src_password
character varying(255) DEFAULT ''::character varying NOT NULL,
259 src_folder
character varying(255) DEFAULT ''::character varying NOT NULL,
260 poll_time
integer DEFAULT 10 NOT NULL,
261 fetchall
boolean DEFAULT false NOT NULL,
262 keep
boolean DEFAULT false NOT NULL,
263 protocol
character varying(15) NOT NULL,
266 mda
character varying(255) DEFAULT ''::character varying NOT NULL,
267 date timestamp with time zone DEFAULT now(),
268 usessl
boolean DEFAULT false NOT NULL,
269 CONSTRAINT fetchmail_protocol_check
CHECK (((((((protocol
)::text = 'POP3'::text) OR ((protocol
)::text = 'IMAP'::text)) OR ((protocol
)::text = 'POP2'::text)) OR ((protocol
)::text = 'ETRN'::text)) OR ((protocol
)::text = 'AUTO'::text))),
270 CONSTRAINT fetchmail_src_auth_check
CHECK (((((((((((((src_auth
)::text = 'password'::text) OR ((src_auth
)::text = 'kerberos_v5'::text)) OR ((src_auth
)::text = 'kerberos'::text)) OR ((src_auth
)::text = 'kerberos_v4'::text)) OR ((src_auth
)::text = 'gssapi'::text)) OR ((src_auth
)::text = 'cram-md5'::text)) OR ((src_auth
)::text = 'otp'::text)) OR ((src_auth
)::text = 'ntlm'::text)) OR ((src_auth
)::text = 'msn'::text)) OR ((src_auth
)::text = 'ssh'::text)) OR ((src_auth
)::text = 'any'::text)))
274 ALTER TABLE public.fetchmail
OWNER TO postgres
;
277 -- Name: fetchmail_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
280 CREATE SEQUENCE fetchmail_id_seq
288 ALTER TABLE public.fetchmail_id_seq
OWNER TO postgres
;
291 -- Name: fetchmail_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
294 ALTER SEQUENCE fetchmail_id_seq
OWNED BY fetchmail.
id;
298 -- Name: log; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
302 "timestamp" timestamp with time zone DEFAULT now(),
303 username
character varying(255) DEFAULT ''::character varying NOT NULL,
304 domain character varying(255) DEFAULT ''::character varying NOT NULL,
305 action character varying(255) DEFAULT ''::character varying NOT NULL,
306 data text DEFAULT ''::text NOT NULL
310 ALTER TABLE public.log
OWNER TO postgres
;
313 -- Name: TABLE log; Type: COMMENT; Schema: public; Owner: postgres
316 COMMENT ON TABLE log
IS 'Postfix Admin - Log';
320 -- Name: mailbox; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
323 CREATE TABLE mailbox (
324 username
character varying(255) NOT NULL,
325 password character varying(255) DEFAULT ''::character varying NOT NULL,
326 name character varying(255) DEFAULT ''::character varying NOT NULL,
327 maildir
character varying(255) DEFAULT ''::character varying NOT NULL,
328 quota
bigint DEFAULT 0 NOT NULL,
329 created
timestamp with time zone DEFAULT now(),
330 modified
timestamp with time zone DEFAULT now(),
331 active
boolean DEFAULT true NOT NULL,
332 domain character varying(255),
333 local_part
character varying(255) NOT NULL
337 ALTER TABLE public.mailbox
OWNER TO postgres
;
340 -- Name: TABLE mailbox; Type: COMMENT; Schema: public; Owner: postgres
343 COMMENT ON TABLE mailbox
IS 'Postfix Admin - Virtual Mailboxes';
346 SET default_with_oids
= false;
349 -- Name: quota; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
353 username
character varying(255) NOT NULL,
354 path character varying(100) NOT NULL,
359 ALTER TABLE public.quota
OWNER TO postgres
;
362 -- Name: quota2; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
365 CREATE TABLE quota2 (
366 username
character varying(100) NOT NULL,
367 bytes
bigint DEFAULT 0 NOT NULL,
368 messages
integer DEFAULT 0 NOT NULL
372 ALTER TABLE public.quota2
OWNER TO postgres
;
374 SET default_with_oids
= true;
377 -- Name: vacation; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
380 CREATE TABLE vacation (
381 email
character varying(255) NOT NULL,
382 subject
character varying(255) NOT NULL,
383 body
text DEFAULT ''::text NOT NULL,
384 created
timestamp with time zone DEFAULT now(),
385 active
boolean DEFAULT true NOT NULL,
386 domain character varying(255)
390 ALTER TABLE public.vacation
OWNER TO postgres
;
393 -- Name: vacation_notification; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
396 CREATE TABLE vacation_notification (
397 on_vacation
character varying(255) NOT NULL,
398 notified
character varying(255) NOT NULL,
399 notified_at
timestamp with time zone DEFAULT now() NOT NULL
403 ALTER TABLE public.vacation_notification
OWNER TO postgres
;
406 -- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
409 ALTER TABLE ONLY config
ALTER COLUMN id SET DEFAULT nextval('config_id_seq'::regclass);
413 -- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
416 ALTER TABLE ONLY fetchmail
ALTER COLUMN id SET DEFAULT nextval('fetchmail_id_seq'::regclass);
420 -- Name: admin_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
423 ALTER TABLE ONLY admin
424 ADD CONSTRAINT admin_key
PRIMARY KEY (username
);
428 -- Name: alias_domain_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
431 ALTER TABLE ONLY alias_domain
432 ADD CONSTRAINT alias_domain_pkey
PRIMARY KEY (alias_domain
);
436 -- Name: alias_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
439 ALTER TABLE ONLY alias
440 ADD CONSTRAINT alias_key
PRIMARY KEY (address
);
444 -- Name: config_name_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
447 ALTER TABLE ONLY config
448 ADD CONSTRAINT config_name_key
UNIQUE (name);
452 -- Name: config_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
455 ALTER TABLE ONLY config
456 ADD CONSTRAINT config_pkey
PRIMARY KEY (id);
460 -- Name: domain_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
463 ALTER TABLE ONLY domain
464 ADD CONSTRAINT domain_key
PRIMARY KEY (domain);
468 -- Name: fetchmail_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
471 ALTER TABLE ONLY fetchmail
472 ADD CONSTRAINT fetchmail_pkey
PRIMARY KEY (id);
476 -- Name: mailbox_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
479 ALTER TABLE ONLY mailbox
480 ADD CONSTRAINT mailbox_key
PRIMARY KEY (username
);
484 -- Name: quota2_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
487 ALTER TABLE ONLY quota2
488 ADD CONSTRAINT quota2_pkey
PRIMARY KEY (username
);
492 -- Name: quota_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
495 ALTER TABLE ONLY quota
496 ADD CONSTRAINT quota_pkey
PRIMARY KEY (username
, path);
500 -- Name: vacation_notification_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
503 ALTER TABLE ONLY vacation_notification
504 ADD CONSTRAINT vacation_notification_pkey
PRIMARY KEY (on_vacation
, notified
);
508 -- Name: vacation_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
511 ALTER TABLE ONLY vacation
512 ADD CONSTRAINT vacation_pkey
PRIMARY KEY (email
);
516 -- Name: alias_address_active; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
519 CREATE INDEX alias_address_active
ON alias USING btree (address
, active
);
523 -- Name: alias_domain_active; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
526 CREATE INDEX alias_domain_active
ON alias_domain
USING btree (alias_domain
, active
);
530 -- Name: alias_domain_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
533 CREATE INDEX alias_domain_idx
ON alias USING btree (domain);
537 -- Name: domain_domain_active; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
540 CREATE INDEX domain_domain_active
ON domain USING btree (domain, active
);
544 -- Name: mailbox_domain_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
547 CREATE INDEX mailbox_domain_idx
ON mailbox
USING btree (domain);
551 -- Name: mailbox_username_active; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
554 CREATE INDEX mailbox_username_active
ON mailbox
USING btree (username
, active
);
558 -- Name: vacation_email_active; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
561 CREATE INDEX vacation_email_active
ON vacation
USING btree (email
, active
);
565 -- Name: mergequota; Type: TRIGGER; Schema: public; Owner: postgres
568 CREATE TRIGGER mergequota
BEFORE INSERT ON quota
FOR EACH ROW EXECUTE PROCEDURE merge_quota();
572 -- Name: mergequota2; Type: TRIGGER; Schema: public; Owner: postgres
575 CREATE TRIGGER mergequota2
BEFORE INSERT ON quota2
FOR EACH ROW EXECUTE PROCEDURE merge_quota2();
579 -- Name: alias_domain_alias_domain_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
582 ALTER TABLE ONLY alias_domain
583 ADD CONSTRAINT alias_domain_alias_domain_fkey
FOREIGN KEY (alias_domain
) REFERENCES domain(domain) ON DELETE CASCADE;
587 -- Name: alias_domain_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
590 ALTER TABLE ONLY alias
591 ADD CONSTRAINT alias_domain_fkey
FOREIGN KEY (domain) REFERENCES domain(domain);
595 -- Name: alias_domain_target_domain_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
598 ALTER TABLE ONLY alias_domain
599 ADD CONSTRAINT alias_domain_target_domain_fkey
FOREIGN KEY (target_domain
) REFERENCES domain(domain) ON DELETE CASCADE;
603 -- Name: domain_admins_domain_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
606 ALTER TABLE ONLY domain_admins
607 ADD CONSTRAINT domain_admins_domain_fkey
FOREIGN KEY (domain) REFERENCES domain(domain);
611 -- Name: mailbox_domain_fkey1; Type: FK CONSTRAINT; Schema: public; Owner: postgres
614 ALTER TABLE ONLY mailbox
615 ADD CONSTRAINT mailbox_domain_fkey1
FOREIGN KEY (domain) REFERENCES domain(domain);
619 -- Name: vacation_domain_fkey1; Type: FK CONSTRAINT; Schema: public; Owner: postgres
622 ALTER TABLE ONLY vacation
623 ADD CONSTRAINT vacation_domain_fkey1
FOREIGN KEY (domain) REFERENCES domain(domain);
627 -- Name: vacation_notification_on_vacation_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
630 ALTER TABLE ONLY vacation_notification
631 ADD CONSTRAINT vacation_notification_on_vacation_fkey
FOREIGN KEY (on_vacation
) REFERENCES vacation(email
) ON DELETE CASCADE;
635 -- Name: public; Type: ACL; Schema: -; Owner: postgres
638 REVOKE ALL ON SCHEMA public FROM PUBLIC;
639 REVOKE ALL ON SCHEMA public FROM postgres
;
640 GRANT ALL ON SCHEMA public TO postgres
;
641 GRANT ALL ON SCHEMA public TO PUBLIC;
645 -- Name: admin; Type: ACL; Schema: public; Owner: postgres
648 REVOKE ALL ON TABLE admin FROM PUBLIC;
649 REVOKE ALL ON TABLE admin FROM postgres
;
650 GRANT ALL ON TABLE admin TO postgres
;
654 -- Name: alias; Type: ACL; Schema: public; Owner: postgres
657 REVOKE ALL ON TABLE alias FROM PUBLIC;
658 REVOKE ALL ON TABLE alias FROM postgres
;
659 GRANT ALL ON TABLE alias TO postgres
;
663 -- Name: config; Type: ACL; Schema: public; Owner: postgres
666 REVOKE ALL ON TABLE config
FROM PUBLIC;
667 REVOKE ALL ON TABLE config
FROM postgres
;
668 GRANT ALL ON TABLE config
TO postgres
;
672 -- Name: domain; Type: ACL; Schema: public; Owner: postgres
675 REVOKE ALL ON TABLE domain FROM PUBLIC;
676 REVOKE ALL ON TABLE domain FROM postgres
;
677 GRANT ALL ON TABLE domain TO postgres
;
681 -- Name: domain_admins; Type: ACL; Schema: public; Owner: postgres
684 REVOKE ALL ON TABLE domain_admins
FROM PUBLIC;
685 REVOKE ALL ON TABLE domain_admins
FROM postgres
;
686 GRANT ALL ON TABLE domain_admins
TO postgres
;
690 -- Name: fetchmail; Type: ACL; Schema: public; Owner: postgres
693 REVOKE ALL ON TABLE fetchmail
FROM PUBLIC;
694 REVOKE ALL ON TABLE fetchmail
FROM postgres
;
695 GRANT ALL ON TABLE fetchmail
TO postgres
;
699 -- Name: log; Type: ACL; Schema: public; Owner: postgres
702 REVOKE ALL ON TABLE log
FROM PUBLIC;
703 REVOKE ALL ON TABLE log
FROM postgres
;
704 GRANT ALL ON TABLE log
TO postgres
;
708 -- Name: mailbox; Type: ACL; Schema: public; Owner: postgres
711 REVOKE ALL ON TABLE mailbox
FROM PUBLIC;
712 REVOKE ALL ON TABLE mailbox
FROM postgres
;
713 GRANT ALL ON TABLE mailbox
TO postgres
;
717 -- Name: vacation; Type: ACL; Schema: public; Owner: postgres
720 REVOKE ALL ON TABLE vacation
FROM PUBLIC;
721 REVOKE ALL ON TABLE vacation
FROM postgres
;
722 GRANT ALL ON TABLE vacation
TO postgres
;
726 -- Name: vacation_notification; Type: ACL; Schema: public; Owner: postgres
729 REVOKE ALL ON TABLE vacation_notification
FROM PUBLIC;
730 REVOKE ALL ON TABLE vacation_notification
FROM postgres
;
731 GRANT ALL ON TABLE vacation_notification
TO postgres
;
735 -- PostgreSQL database dump complete