robots.org.uk: attachment:sql.txt of MailSystem

   1 --
   2 -- PostgreSQL database dump
   3 --
   4 
   5 SET client_encoding = 'UTF8';
   6 SET standard_conforming_strings = off;
   7 SET check_function_bodies = false;
   8 SET client_min_messages = warning;
   9 SET escape_string_warning = off;
  10 
  11 --
  12 -- Name: mail; Type: SCHEMA; Schema: -; Owner: postgres
  13 --
  14 
  15 CREATE SCHEMA mail;
  16 
  17 
  18 ALTER SCHEMA mail OWNER TO postgres;
  19 
  20 SET search_path = mail, pg_catalog;
  21 
  22 --
  23 -- Name: domain; Type: DOMAIN; Schema: mail; Owner: postgres
  24 --
  25 
  26 CREATE DOMAIN domain AS character varying(64) NOT NULL;
  27 
  28 
  29 ALTER DOMAIN mail.domain OWNER TO postgres;
  30 
  31 --
  32 -- Name: email_address; Type: DOMAIN; Schema: mail; Owner: postgres
  33 --
  34 
  35 CREATE DOMAIN email_address AS character varying(129) NOT NULL
  36 	CONSTRAINT valid_address CHECK ((((VALUE)::text ~ '@'::text) OR ((VALUE)::text = ''::text)));
  37 
  38 
  39 ALTER DOMAIN mail.email_address OWNER TO postgres;
  40 
  41 --
  42 -- Name: local_part; Type: DOMAIN; Schema: mail; Owner: postgres
  43 --
  44 
  45 CREATE DOMAIN local_part AS character varying(64) NOT NULL;
  46 
  47 
  48 ALTER DOMAIN mail.local_part OWNER TO postgres;
  49 
  50 --
  51 -- Name: password; Type: DOMAIN; Schema: mail; Owner: postgres
  52 --
  53 
  54 CREATE DOMAIN password AS text NOT NULL
  55 	CONSTRAINT valid_password CHECK (((VALUE ~ '^\\$1\\$[./0-9A-Za-z]{8}\\$[./0-9A-Za-z]{22}$'::text) OR (VALUE ~ '^[./0-9A-Za-z]{13}$'::text)));
  56 
  57 
  58 ALTER DOMAIN mail.password OWNER TO postgres;
  59 
  60 SET default_tablespace = '';
  61 
  62 SET default_with_oids = false;
  63 
  64 --
  65 -- Name: accounts; Type: TABLE; Schema: mail; Owner: postgres; Tablespace: 
  66 --
  67 
  68 CREATE TABLE accounts (
  69     "user" local_part NOT NULL,
  70     domain domain NOT NULL,
  71     quota integer DEFAULT 10 NOT NULL,
  72     password password NOT NULL
  73 );
  74 
  75 
  76 ALTER TABLE mail.accounts OWNER TO postgres;
  77 
  78 --
  79 -- Name: TABLE accounts; Type: COMMENT; Schema: mail; Owner: postgres
  80 --
  81 
  82 COMMENT ON TABLE accounts IS 'To create a new account, call the function create_account (user, domain, password).';
  83 
  84 
  85 --
  86 -- Name: COLUMN accounts.quota; Type: COMMENT; Schema: mail; Owner: postgres
  87 --
  88 
  89 COMMENT ON COLUMN accounts.quota IS 'In megabytes.';
  90 
  91 
  92 --
  93 -- Name: COLUMN accounts.password; Type: COMMENT; Schema: mail; Owner: postgres
  94 --
  95 
  96 COMMENT ON COLUMN accounts.password IS 'Passwords can be changed more easily with the change_password function.';
  97 
  98 
  99 --
 100 -- Name: change_password(local_part, domain, text); Type: FUNCTION; Schema: mail; Owner: postgres
 101 --
 102 
 103 CREATE FUNCTION change_password(local_part, domain, text) RETURNS accounts
 104     AS $_$
 105 	UPDATE mail.accounts SET password = pgcrypto.crypt ($3, pgcrypto.gen_salt ('md5')) WHERE "user" = $1 AND domain = $2;
 106 	SELECT * FROM mail.accounts WHERE "user" = $1 AND domain = $2;
 107 $_$
 108     LANGUAGE sql;
 109 
 110 
 111 ALTER FUNCTION mail.change_password(local_part, domain, text) OWNER TO postgres;
 112 
 113 --
 114 -- Name: create_account(local_part, domain, text); Type: FUNCTION; Schema: mail; Owner: postgres
 115 --
 116 
 117 CREATE FUNCTION create_account(local_part, domain, text) RETURNS accounts
 118     AS $_$
 119         INSERT INTO mail.accounts ("user", domain, password) VALUES ($1, $2, pgcrypto.crypt ($3, pgcrypto.gen_salt ('md5')));
 120         SELECT * FROM mail.accounts WHERE "user" = $1 AND domain = $2;
 121 $_$
 122     LANGUAGE sql;
 123 
 124 
 125 ALTER FUNCTION mail.create_account(local_part, domain, text) OWNER TO postgres;
 126 
 127 --
 128 -- Name: greylist_process(inet, email_address, email_address); Type: FUNCTION; Schema: mail; Owner: postgres
 129 --
 130 
 131 CREATE FUNCTION greylist_process(inet, email_address, email_address) RETURNS boolean
 132     AS $_$
 133 DECLARE
 134  ip ALIAS FOR $1;
 135  sender ALIAS FOR $2;
 136  recipient ALIAS FOR $3;
 137  present boolean;
 138 BEGIN
 139  SELECT INTO present now() - date > interval '5 minute'
 140   FROM mail.greylist
 141   WHERE relay_ip = ip
 142    AND envelope_sender = sender
 143    AND envelope_recipient = recipient;
 144  IF NOT FOUND THEN
 145   RAISE DEBUG 'new greylist entry';
 146   INSERT INTO mail.greylist (relay_ip, envelope_sender, envelope_recipient, date)
 147    VALUES (ip, sender, recipient, now());
 148   PRESENT := false;
 149  END IF;
 150  RETURN NOT present;
 151 END;
 152 $_$
 153     LANGUAGE plpgsql;
 154 
 155 
 156 ALTER FUNCTION mail.greylist_process(inet, email_address, email_address) OWNER TO postgres;
 157 
 158 --
 159 -- Name: lookup_alias(local_part, domain); Type: FUNCTION; Schema: mail; Owner: postgres
 160 --
 161 
 162 CREATE FUNCTION lookup_alias(local_part, domain) RETURNS text
 163     AS $_$
 164 DECLARE
 165   auser ALIAS FOR $1;
 166   adomain ALIAS FOR $2;
 167   result character varying(129);
 168 BEGIN
 169   SELECT target INTO result
 170     FROM mail.aliases
 171     WHERE "user" = auser AND domain = adomain;
 172   IF FOUND THEN
 173     RAISE DEBUG 'found';
 174     RETURN result;
 175   END IF;
 176   RAISE DEBUG 'not found';
 177   IF auser != '*' THEN
 178     PERFORM 1
 179       FROM mail.accounts
 180       WHERE "user" = auser AND domain = adomain;
 181     IF FOUND THEN
 182       RAISE DEBUG 'account has priority';
 183       RETURN NULL;
 184     END IF;
 185     RETURN mail.lookup_alias ('*', adomain);
 186   END IF;
 187   RAISE DEBUG 'no alias';
 188   RETURN NULL;
 189 END;
 190 $_$
 191     LANGUAGE plpgsql;
 192 
 193 
 194 ALTER FUNCTION mail.lookup_alias(local_part, domain) OWNER TO postgres;
 195 
 196 --
 197 -- Name: aliases; Type: TABLE; Schema: mail; Owner: postgres; Tablespace: 
 198 --
 199 
 200 CREATE TABLE aliases (
 201     "user" local_part NOT NULL,
 202     domain domain NOT NULL,
 203     target text NOT NULL
 204 );
 205 
 206 
 207 ALTER TABLE mail.aliases OWNER TO postgres;
 208 
 209 --
 210 -- Name: COLUMN aliases."user"; Type: COMMENT; Schema: mail; Owner: postgres
 211 --
 212 
 213 COMMENT ON COLUMN aliases."user" IS 'Set to * for a catch-all alias';
 214 
 215 
 216 --
 217 -- Name: COLUMN aliases.target; Type: COMMENT; Schema: mail; Owner: postgres
 218 --
 219 
 220 COMMENT ON COLUMN aliases.target IS 'Unqualified values are for system mail; e.g., a system account or mailing list';
 221 
 222 
 223 --
 224 -- Name: courier_auth; Type: VIEW; Schema: mail; Owner: postgres
 225 --
 226 
 227 CREATE VIEW courier_auth AS
 228     SELECT accounts."user" AS username, accounts.domain, accounts.password AS cryptpw, '' AS clearpw, '8' AS uid, '8' AS gid, ((('/srv/mail/'::text || (accounts.domain)::text) || '/'::text) || (accounts."user")::text) AS home, (((('/srv/mail/'::text || (accounts.domain)::text) || '/'::text) || (accounts."user")::text) || '/'::text) AS maildir, ((((accounts.quota * 1024) * 1024))::text || 'S'::text) AS quota, '' AS fullname, '' AS options FROM accounts;
 229 
 230 
 231 ALTER TABLE mail.courier_auth OWNER TO postgres;
 232 
 233 --
 234 -- Name: dovecot_auth; Type: VIEW; Schema: mail; Owner: postgres
 235 --
 236 
 237 CREATE VIEW dovecot_auth AS
 238     SELECT (((accounts."user")::text || '@'::text) || (accounts.domain)::text) AS "user", accounts.password, ((('/srv/mail/'::text || (accounts.domain)::text) || '/'::text) || (accounts."user")::text) AS userdb_home, '8' AS userdb_uid, '8' AS userdb_gid FROM accounts;
 239 
 240 
 241 ALTER TABLE mail.dovecot_auth OWNER TO postgres;
 242 
 243 --
 244 -- Name: exim_accounts; Type: VIEW; Schema: mail; Owner: postgres
 245 --
 246 
 247 CREATE VIEW exim_accounts AS
 248     SELECT DISTINCT accounts."user", accounts.domain, (((('/srv/mail/'::text || (accounts.domain)::text) || '/'::text) || (accounts."user")::text) || '/'::text) AS maildir FROM accounts ORDER BY accounts."user", accounts.domain, (((('/srv/mail/'::text || (accounts.domain)::text) || '/'::text) || (accounts."user")::text) || '/'::text);
 249 
 250 
 251 ALTER TABLE mail.exim_accounts OWNER TO postgres;
 252 
 253 --
 254 -- Name: exim_auth; Type: VIEW; Schema: mail; Owner: postgres
 255 --
 256 
 257 CREATE VIEW exim_auth AS
 258     SELECT (((accounts."user")::text || '@'::text) || (accounts.domain)::text) AS email, accounts.password FROM accounts;
 259 
 260 
 261 ALTER TABLE mail.exim_auth OWNER TO postgres;
 262 
 263 --
 264 -- Name: exim_domains; Type: VIEW; Schema: mail; Owner: postgres
 265 --
 266 
 267 CREATE VIEW exim_domains AS
 268     SELECT accounts.domain FROM accounts UNION SELECT aliases.domain FROM aliases;
 269 
 270 
 271 ALTER TABLE mail.exim_domains OWNER TO postgres;
 272 
 273 --
 274 -- Name: greylist; Type: TABLE; Schema: mail; Owner: postgres; Tablespace: 
 275 --
 276 
 277 CREATE TABLE greylist (
 278     relay_ip inet NOT NULL,
 279     envelope_sender email_address NOT NULL,
 280     envelope_recipient email_address NOT NULL,
 281     date timestamp without time zone NOT NULL
 282 );
 283 
 284 
 285 ALTER TABLE mail.greylist OWNER TO postgres;
 286 
 287 --
 288 -- Name: greylist_disable; Type: TABLE; Schema: mail; Owner: postgres; Tablespace: 
 289 --
 290 
 291 CREATE TABLE greylist_disable (
 292     address email_address NOT NULL
 293 );
 294 
 295 
 296 ALTER TABLE mail.greylist_disable OWNER TO postgres;
 297 
 298 --
 299 -- Name: TABLE greylist_disable; Type: COMMENT; Schema: mail; Owner: postgres
 300 --
 301 
 302 COMMENT ON TABLE greylist_disable IS 'Recipients in this table will not have greylisting performed for their incoming messages.';
 303 
 304 
 305 --
 306 -- Name: vacation; Type: TABLE; Schema: mail; Owner: postgres; Tablespace: 
 307 --
 308 
 309 CREATE TABLE vacation (
 310     "user" local_part NOT NULL,
 311     domain domain NOT NULL,
 312     message text NOT NULL
 313 );
 314 
 315 
 316 ALTER TABLE mail.vacation OWNER TO postgres;
 317 
 318 --
 319 -- Name: accounts_pkey; Type: CONSTRAINT; Schema: mail; Owner: postgres; Tablespace: 
 320 --
 321 
 322 ALTER TABLE ONLY accounts
 323     ADD CONSTRAINT accounts_pkey PRIMARY KEY ("user", domain);
 324 
 325 
 326 --
 327 -- Name: aliases_pkey; Type: CONSTRAINT; Schema: mail; Owner: postgres; Tablespace: 
 328 --
 329 
 330 ALTER TABLE ONLY aliases
 331     ADD CONSTRAINT aliases_pkey PRIMARY KEY ("user", domain);
 332 
 333 
 334 --
 335 -- Name: greylist_disable_address_key; Type: CONSTRAINT; Schema: mail; Owner: postgres; Tablespace: 
 336 --
 337 
 338 ALTER TABLE ONLY greylist_disable
 339     ADD CONSTRAINT greylist_disable_address_key UNIQUE (address);
 340 
 341 
 342 --
 343 -- Name: greylist_pkey; Type: CONSTRAINT; Schema: mail; Owner: postgres; Tablespace: 
 344 --
 345 
 346 ALTER TABLE ONLY greylist
 347     ADD CONSTRAINT greylist_pkey PRIMARY KEY (relay_ip, envelope_sender, envelope_recipient);
 348 
 349 
 350 --
 351 -- Name: vacation_pkey; Type: CONSTRAINT; Schema: mail; Owner: postgres; Tablespace: 
 352 --
 353 
 354 ALTER TABLE ONLY vacation
 355     ADD CONSTRAINT vacation_pkey PRIMARY KEY ("user", domain);
 356 
 357 
 358 --
 359 -- Name: mail; Type: ACL; Schema: -; Owner: postgres
 360 --
 361 
 362 REVOKE ALL ON SCHEMA mail FROM PUBLIC;
 363 REVOKE ALL ON SCHEMA mail FROM postgres;
 364 GRANT ALL ON SCHEMA mail TO postgres;
 365 GRANT USAGE ON SCHEMA mail TO PUBLIC;
 366 
 367 
 368 --
 369 -- Name: accounts; Type: ACL; Schema: mail; Owner: postgres
 370 --
 371 
 372 REVOKE ALL ON TABLE accounts FROM PUBLIC;
 373 REVOKE ALL ON TABLE accounts FROM postgres;
 374 GRANT ALL ON TABLE accounts TO postgres;
 375 GRANT SELECT ON TABLE accounts TO maildaemon;
 376 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE accounts TO mailadmin;
 377 
 378 
 379 --
 380 -- Name: aliases; Type: ACL; Schema: mail; Owner: postgres
 381 --
 382 
 383 REVOKE ALL ON TABLE aliases FROM PUBLIC;
 384 REVOKE ALL ON TABLE aliases FROM postgres;
 385 GRANT ALL ON TABLE aliases TO postgres;
 386 GRANT SELECT ON TABLE aliases TO maildaemon;
 387 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE aliases TO mailadmin;
 388 
 389 
 390 --
 391 -- Name: courier_auth; Type: ACL; Schema: mail; Owner: postgres
 392 --
 393 
 394 REVOKE ALL ON TABLE courier_auth FROM PUBLIC;
 395 REVOKE ALL ON TABLE courier_auth FROM postgres;
 396 GRANT ALL ON TABLE courier_auth TO postgres;
 397 GRANT SELECT ON TABLE courier_auth TO maildaemon;
 398 
 399 
 400 --
 401 -- Name: dovecot_auth; Type: ACL; Schema: mail; Owner: postgres
 402 --
 403 
 404 REVOKE ALL ON TABLE dovecot_auth FROM PUBLIC;
 405 REVOKE ALL ON TABLE dovecot_auth FROM postgres;
 406 GRANT ALL ON TABLE dovecot_auth TO postgres;
 407 GRANT SELECT ON TABLE dovecot_auth TO maildaemon;
 408 
 409 
 410 --
 411 -- Name: exim_accounts; Type: ACL; Schema: mail; Owner: postgres
 412 --
 413 
 414 REVOKE ALL ON TABLE exim_accounts FROM PUBLIC;
 415 REVOKE ALL ON TABLE exim_accounts FROM postgres;
 416 GRANT ALL ON TABLE exim_accounts TO postgres;
 417 GRANT SELECT ON TABLE exim_accounts TO maildaemon;
 418 
 419 
 420 --
 421 -- Name: exim_auth; Type: ACL; Schema: mail; Owner: postgres
 422 --
 423 
 424 REVOKE ALL ON TABLE exim_auth FROM PUBLIC;
 425 REVOKE ALL ON TABLE exim_auth FROM postgres;
 426 GRANT ALL ON TABLE exim_auth TO postgres;
 427 GRANT SELECT ON TABLE exim_auth TO maildaemon;
 428 
 429 
 430 --
 431 -- Name: exim_domains; Type: ACL; Schema: mail; Owner: postgres
 432 --
 433 
 434 REVOKE ALL ON TABLE exim_domains FROM PUBLIC;
 435 REVOKE ALL ON TABLE exim_domains FROM postgres;
 436 GRANT ALL ON TABLE exim_domains TO postgres;
 437 GRANT SELECT ON TABLE exim_domains TO maildaemon;
 438 
 439 
 440 --
 441 -- Name: greylist; Type: ACL; Schema: mail; Owner: postgres
 442 --
 443 
 444 REVOKE ALL ON TABLE greylist FROM PUBLIC;
 445 REVOKE ALL ON TABLE greylist FROM postgres;
 446 GRANT ALL ON TABLE greylist TO postgres;
 447 GRANT SELECT,INSERT ON TABLE greylist TO maildaemon;
 448 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE greylist TO mailadmin;
 449 
 450 
 451 --
 452 -- Name: greylist_disable; Type: ACL; Schema: mail; Owner: postgres
 453 --
 454 
 455 REVOKE ALL ON TABLE greylist_disable FROM PUBLIC;
 456 REVOKE ALL ON TABLE greylist_disable FROM postgres;
 457 GRANT ALL ON TABLE greylist_disable TO postgres;
 458 GRANT SELECT ON TABLE greylist_disable TO maildaemon;
 459 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE greylist_disable TO mailadmin;
 460 
 461 
 462 --
 463 -- Name: vacation; Type: ACL; Schema: mail; Owner: postgres
 464 --
 465 
 466 REVOKE ALL ON TABLE vacation FROM PUBLIC;
 467 REVOKE ALL ON TABLE vacation FROM postgres;
 468 GRANT ALL ON TABLE vacation TO postgres;
 469 GRANT SELECT ON TABLE vacation TO maildaemon;
 470 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE vacation TO mailadmin;
 471 
 472 
 473 --
 474 -- PostgreSQL database dump complete
 475 --