sql/schéma.sql
author František Kučera <franta-hg@frantovo.cz>
Mon, 10 Mar 2014 12:21:02 +0100
changeset 27 60cfdaa89b3a
parent 24 571d9fd2d336
permissions -rw-r--r--
hgignore
     1 --
     2 -- PostgreSQL database dump
     3 --
     4 
     5 -- Started on 2011-12-03 17:50:08 CET
     6 
     7 SET statement_timeout = 0;
     8 SET client_encoding = 'UTF8';
     9 SET standard_conforming_strings = off;
    10 SET check_function_bodies = false;
    11 SET client_min_messages = warning;
    12 SET escape_string_warning = off;
    13 
    14 --
    15 -- TOC entry 7 (class 2615 OID 16397)
    16 -- Name: posta; Type: SCHEMA; Schema: -; Owner: posta
    17 --
    18 
    19 CREATE SCHEMA posta;
    20 
    21 
    22 ALTER SCHEMA posta OWNER TO posta;
    23 
    24 SET search_path = posta, pg_catalog;
    25 
    26 --
    27 -- TOC entry 189 (class 1255 OID 16398)
    28 -- Dependencies: 7 551
    29 -- Name: zmen_heslo(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: posta; Owner: posta
    30 --
    31 
    32 CREATE FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) RETURNS text
    33     LANGUAGE plpgsql SECURITY DEFINER
    34     AS $$DECLARE pocet integer;
    35 BEGIN
    36 UPDATE uzivatel
    37  SET heslo = md5("Nove_heslo")
    38  WHERE 	uzivatel.login = "Login"
    39 	AND uzivatel.domena = "Domena"
    40 	AND uzivatel.heslo = md5("Stare_heslo");
    41 	GET DIAGNOSTICS pocet = ROW_COUNT;
    42 	IF pocet = 1 THEN
    43 		RETURN 'Změna hesla pro ' || "Login" || '@' || "Domena" || ' proběhla úspěšně.';
    44 	ELSE
    45 		RAISE EXCEPTION 'Nesprávné heslo, nebo uživatel % neexistuje', "Login" || '@' || "Domena";
    46 	END IF;
    47 END;
    48 	$$;
    49 
    50 
    51 ALTER FUNCTION posta.zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) OWNER TO posta;
    52 
    53 --
    54 -- TOC entry 1900 (class 0 OID 0)
    55 -- Dependencies: 189
    56 -- Name: FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying); Type: COMMENT; Schema: posta; Owner: posta
    57 --
    58 
    59 COMMENT ON FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) IS 'Změní heslo uživatele.';
    60 
    61 
    62 SET default_tablespace = '';
    63 
    64 SET default_with_oids = false;
    65 
    66 --
    67 -- TOC entry 143 (class 1259 OID 16399)
    68 -- Dependencies: 7
    69 -- Name: alias; Type: TABLE; Schema: posta; Owner: posta; Tablespace: 
    70 --
    71 
    72 CREATE TABLE alias (
    73     zdroj_jmeno character varying(255) NOT NULL,
    74     zdroj_domena character varying(255) NOT NULL,
    75     cil_jmeno character varying(255) NOT NULL,
    76     cil_domena character varying(255) NOT NULL
    77 );
    78 
    79 
    80 ALTER TABLE posta.alias OWNER TO posta;
    81 
    82 --
    83 -- TOC entry 1902 (class 0 OID 0)
    84 -- Dependencies: 143
    85 -- Name: COLUMN alias.zdroj_jmeno; Type: COMMENT; Schema: posta; Owner: posta
    86 --
    87 
    88 COMMENT ON COLUMN alias.zdroj_jmeno IS 'Část zdrojové adresy před zavináčem.
    89 (sem přijde zpráva)';
    90 
    91 
    92 --
    93 -- TOC entry 1903 (class 0 OID 0)
    94 -- Dependencies: 143
    95 -- Name: COLUMN alias.zdroj_domena; Type: COMMENT; Schema: posta; Owner: posta
    96 --
    97 
    98 COMMENT ON COLUMN alias.zdroj_domena IS 'Část zdrojové adresy za zavináčem.
    99 (sem přijde zpráva)';
   100 
   101 
   102 --
   103 -- TOC entry 1904 (class 0 OID 0)
   104 -- Dependencies: 143
   105 -- Name: COLUMN alias.cil_jmeno; Type: COMMENT; Schema: posta; Owner: posta
   106 --
   107 
   108 COMMENT ON COLUMN alias.cil_jmeno IS 'Část cílové adresy před zavináčem.
   109 (sem zprávu předáme)';
   110 
   111 
   112 --
   113 -- TOC entry 1905 (class 0 OID 0)
   114 -- Dependencies: 143
   115 -- Name: COLUMN alias.cil_domena; Type: COMMENT; Schema: posta; Owner: posta
   116 --
   117 
   118 COMMENT ON COLUMN alias.cil_domena IS 'Část cílové adresy za zavináčem.
   119 (sem zprávu předáme)';
   120 
   121 
   122 --
   123 -- TOC entry 144 (class 1259 OID 16405)
   124 -- Dependencies: 7
   125 -- Name: domena; Type: TABLE; Schema: posta; Owner: posta; Tablespace: 
   126 --
   127 
   128 CREATE TABLE domena (
   129     domena character varying(255) NOT NULL
   130 );
   131 
   132 
   133 ALTER TABLE posta.domena OWNER TO posta;
   134 
   135 --
   136 -- TOC entry 145 (class 1259 OID 16408)
   137 -- Dependencies: 1691 7
   138 -- Name: alias_externi; Type: VIEW; Schema: posta; Owner: posta
   139 --
   140 
   141 CREATE VIEW alias_externi AS
   142     SELECT a.zdroj_jmeno, a.zdroj_domena, a.cil_jmeno, a.cil_domena FROM (alias a LEFT JOIN domena d ON (((a.cil_domena)::text = (d.domena)::text))) WHERE (d.domena IS NULL);
   143 
   144 
   145 ALTER TABLE posta.alias_externi OWNER TO posta;
   146 
   147 --
   148 -- TOC entry 1908 (class 0 OID 0)
   149 -- Dependencies: 145
   150 -- Name: VIEW alias_externi; Type: COMMENT; Schema: posta; Owner: posta
   151 --
   152 
   153 COMMENT ON VIEW alias_externi IS 'Poštovní alias, který vede na doménu nacházející se na jiném serveru (tzn. e-mail opouští Veverku)';
   154 
   155 
   156 --
   157 -- TOC entry 146 (class 1259 OID 16412)
   158 -- Dependencies: 1692 7
   159 -- Name: alias_interni; Type: VIEW; Schema: posta; Owner: posta
   160 --
   161 
   162 CREATE VIEW alias_interni AS
   163     SELECT a.zdroj_jmeno, a.zdroj_domena, a.cil_jmeno, a.cil_domena FROM (alias a LEFT JOIN domena d ON (((a.cil_domena)::text = (d.domena)::text))) WHERE (d.domena IS NOT NULL);
   164 
   165 
   166 ALTER TABLE posta.alias_interni OWNER TO posta;
   167 
   168 --
   169 -- TOC entry 1909 (class 0 OID 0)
   170 -- Dependencies: 146
   171 -- Name: VIEW alias_interni; Type: COMMENT; Schema: posta; Owner: posta
   172 --
   173 
   174 COMMENT ON VIEW alias_interni IS 'Poštovní alias, který vede na doménu nacházející se na tomto serveru (tzn. e-mail neopouští Veverku)';
   175 
   176 
   177 --
   178 -- TOC entry 147 (class 1259 OID 16416)
   179 -- Dependencies: 7
   180 -- Name: uzivatel; Type: TABLE; Schema: posta; Owner: posta; Tablespace: 
   181 --
   182 
   183 CREATE TABLE uzivatel (
   184     login character varying(255) NOT NULL,
   185     heslo character varying(512),
   186     domena character varying(255) NOT NULL,
   187     jmeno character varying(32)
   188 );
   189 
   190 
   191 ALTER TABLE posta.uzivatel OWNER TO posta;
   192 
   193 --
   194 -- TOC entry 1910 (class 0 OID 0)
   195 -- Dependencies: 147
   196 -- Name: COLUMN uzivatel.jmeno; Type: COMMENT; Schema: posta; Owner: posta
   197 --
   198 
   199 COMMENT ON COLUMN uzivatel.jmeno IS 'Celé jméno uživatele';
   200 
   201 
   202 --
   203 -- TOC entry 148 (class 1259 OID 16422)
   204 -- Dependencies: 1693 7
   205 -- Name: dovecot_heslo; Type: VIEW; Schema: posta; Owner: posta
   206 --
   207 
   208 CREATE VIEW dovecot_heslo AS
   209     SELECT (((uzivatel.login)::text || '@'::text) || (uzivatel.domena)::text) AS "user", uzivatel.heslo AS password, uzivatel.login, uzivatel.domena FROM uzivatel;
   210 
   211 
   212 ALTER TABLE posta.dovecot_heslo OWNER TO posta;
   213 
   214 --
   215 -- TOC entry 149 (class 1259 OID 16426)
   216 -- Dependencies: 1694 7
   217 -- Name: dovecot_uzivatel; Type: VIEW; Schema: posta; Owner: posta
   218 --
   219 
   220 CREATE VIEW dovecot_uzivatel AS
   221     SELECT 5000 AS uid, 5000 AS gid, ((('/var/mail/virtualniposta/'::text || (uzivatel.domena)::text) || '/'::text) || (uzivatel.login)::text) AS home, (((('maildir:/var/mail/virtualniposta/'::text || (uzivatel.domena)::text) || '/'::text) || (uzivatel.login)::text) || '/Maildir'::text) AS mail, 'maildir:Maximalni velikost schranky'::text AS quota, '*:storage=1G'::text AS quota_rule, uzivatel.login, uzivatel.domena FROM uzivatel;
   222 
   223 
   224 ALTER TABLE posta.dovecot_uzivatel OWNER TO posta;
   225 
   226 --
   227 -- TOC entry 150 (class 1259 OID 16430)
   228 -- Dependencies: 1695 7
   229 -- Name: postfix_alias; Type: VIEW; Schema: posta; Owner: posta
   230 --
   231 
   232 CREATE VIEW postfix_alias AS
   233     SELECT (((alias.zdroj_jmeno)::text || '@'::text) || (alias.zdroj_domena)::text) AS zdroj, (((alias.cil_jmeno)::text || '@'::text) || (alias.cil_domena)::text) AS cil FROM alias;
   234 
   235 
   236 ALTER TABLE posta.postfix_alias OWNER TO posta;
   237 
   238 --
   239 -- TOC entry 151 (class 1259 OID 16434)
   240 -- Dependencies: 1696 7
   241 -- Name: postfix_uzivatel; Type: VIEW; Schema: posta; Owner: posta
   242 --
   243 
   244 CREATE VIEW postfix_uzivatel AS
   245     SELECT ((((uzivatel.domena)::text || '/'::text) || (uzivatel.login)::text) || '/Maildir/'::text) AS cesta, uzivatel.login, uzivatel.domena FROM uzivatel;
   246 
   247 
   248 ALTER TABLE posta.postfix_uzivatel OWNER TO posta;
   249 
   250 --
   251 -- TOC entry 152 (class 1259 OID 16438)
   252 -- Dependencies: 1697 7
   253 -- Name: sogo_uzivatel; Type: VIEW; Schema: posta; Owner: posta
   254 --
   255 
   256 CREATE VIEW sogo_uzivatel AS
   257     SELECT (((uzivatel.login)::text || '@'::text) || (uzivatel.domena)::text) AS c_uid, COALESCE((uzivatel.jmeno)::text, (((uzivatel.login)::text || '@'::text) || (uzivatel.domena)::text)) AS c_cn, (((uzivatel.login)::text || '@'::text) || (uzivatel.domena)::text) AS c_name, (((uzivatel.login)::text || '@'::text) || (uzivatel.domena)::text) AS mail, uzivatel.heslo AS c_password FROM uzivatel;
   258 
   259 
   260 ALTER TABLE posta.sogo_uzivatel OWNER TO posta;
   261 
   262 --
   263 -- TOC entry 1890 (class 2606 OID 16443)
   264 -- Dependencies: 143 143 143 143 143
   265 -- Name: aliasy_pk; Type: CONSTRAINT; Schema: posta; Owner: posta; Tablespace: 
   266 --
   267 
   268 ALTER TABLE ONLY alias
   269     ADD CONSTRAINT aliasy_pk PRIMARY KEY (zdroj_jmeno, zdroj_domena, cil_jmeno, cil_domena);
   270 
   271 
   272 --
   273 -- TOC entry 1892 (class 2606 OID 16445)
   274 -- Dependencies: 144 144
   275 -- Name: domena_pk; Type: CONSTRAINT; Schema: posta; Owner: posta; Tablespace: 
   276 --
   277 
   278 ALTER TABLE ONLY domena
   279     ADD CONSTRAINT domena_pk PRIMARY KEY (domena);
   280 
   281 
   282 --
   283 -- TOC entry 1894 (class 2606 OID 16447)
   284 -- Dependencies: 147 147 147
   285 -- Name: uzivatel_pk; Type: CONSTRAINT; Schema: posta; Owner: posta; Tablespace: 
   286 --
   287 
   288 ALTER TABLE ONLY uzivatel
   289     ADD CONSTRAINT uzivatel_pk PRIMARY KEY (login, domena);
   290 
   291 
   292 --
   293 -- TOC entry 1895 (class 2606 OID 16448)
   294 -- Dependencies: 143 1891 144
   295 -- Name: alias_domena_fk; Type: FK CONSTRAINT; Schema: posta; Owner: posta
   296 --
   297 
   298 ALTER TABLE ONLY alias
   299     ADD CONSTRAINT alias_domena_fk FOREIGN KEY (zdroj_domena) REFERENCES domena(domena);
   300 
   301 
   302 --
   303 -- TOC entry 1896 (class 2606 OID 16453)
   304 -- Dependencies: 1891 144 147
   305 -- Name: uzivatel_domena_fk; Type: FK CONSTRAINT; Schema: posta; Owner: posta
   306 --
   307 
   308 ALTER TABLE ONLY uzivatel
   309     ADD CONSTRAINT uzivatel_domena_fk FOREIGN KEY (domena) REFERENCES domena(domena);
   310 
   311 
   312 --
   313 -- TOC entry 1899 (class 0 OID 0)
   314 -- Dependencies: 7
   315 -- Name: posta; Type: ACL; Schema: -; Owner: posta
   316 --
   317 
   318 REVOKE ALL ON SCHEMA posta FROM PUBLIC;
   319 REVOKE ALL ON SCHEMA posta FROM posta;
   320 GRANT ALL ON SCHEMA posta TO posta;
   321 GRANT USAGE ON SCHEMA posta TO posta_cteni;
   322 GRANT USAGE ON SCHEMA posta TO posta_zmena_hesla;
   323 
   324 
   325 --
   326 -- TOC entry 1901 (class 0 OID 0)
   327 -- Dependencies: 189
   328 -- Name: zmen_heslo(character varying, character varying, character varying, character varying); Type: ACL; Schema: posta; Owner: posta
   329 --
   330 
   331 REVOKE ALL ON FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) FROM PUBLIC;
   332 REVOKE ALL ON FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) FROM posta;
   333 GRANT ALL ON FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) TO posta;
   334 GRANT ALL ON FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) TO PUBLIC;
   335 GRANT ALL ON FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) TO posta_zmena_hesla;
   336 
   337 
   338 --
   339 -- TOC entry 1906 (class 0 OID 0)
   340 -- Dependencies: 143
   341 -- Name: alias; Type: ACL; Schema: posta; Owner: posta
   342 --
   343 
   344 REVOKE ALL ON TABLE alias FROM PUBLIC;
   345 REVOKE ALL ON TABLE alias FROM posta;
   346 GRANT ALL ON TABLE alias TO posta;
   347 
   348 
   349 --
   350 -- TOC entry 1907 (class 0 OID 0)
   351 -- Dependencies: 144
   352 -- Name: domena; Type: ACL; Schema: posta; Owner: posta
   353 --
   354 
   355 REVOKE ALL ON TABLE domena FROM PUBLIC;
   356 REVOKE ALL ON TABLE domena FROM posta;
   357 GRANT ALL ON TABLE domena TO posta;
   358 GRANT SELECT ON TABLE domena TO posta_cteni;
   359 
   360 
   361 --
   362 -- TOC entry 1911 (class 0 OID 0)
   363 -- Dependencies: 147
   364 -- Name: uzivatel; Type: ACL; Schema: posta; Owner: posta
   365 --
   366 
   367 REVOKE ALL ON TABLE uzivatel FROM PUBLIC;
   368 REVOKE ALL ON TABLE uzivatel FROM posta;
   369 GRANT ALL ON TABLE uzivatel TO posta;
   370 GRANT SELECT ON TABLE uzivatel TO posta_cteni;
   371 
   372 
   373 --
   374 -- TOC entry 1912 (class 0 OID 0)
   375 -- Dependencies: 148
   376 -- Name: dovecot_heslo; Type: ACL; Schema: posta; Owner: posta
   377 --
   378 
   379 REVOKE ALL ON TABLE dovecot_heslo FROM PUBLIC;
   380 REVOKE ALL ON TABLE dovecot_heslo FROM posta;
   381 GRANT ALL ON TABLE dovecot_heslo TO posta;
   382 GRANT SELECT ON TABLE dovecot_heslo TO posta_cteni;
   383 
   384 
   385 --
   386 -- TOC entry 1913 (class 0 OID 0)
   387 -- Dependencies: 149
   388 -- Name: dovecot_uzivatel; Type: ACL; Schema: posta; Owner: posta
   389 --
   390 
   391 REVOKE ALL ON TABLE dovecot_uzivatel FROM PUBLIC;
   392 REVOKE ALL ON TABLE dovecot_uzivatel FROM posta;
   393 GRANT ALL ON TABLE dovecot_uzivatel TO posta;
   394 GRANT SELECT ON TABLE dovecot_uzivatel TO posta_cteni;
   395 
   396 
   397 --
   398 -- TOC entry 1914 (class 0 OID 0)
   399 -- Dependencies: 150
   400 -- Name: postfix_alias; Type: ACL; Schema: posta; Owner: posta
   401 --
   402 
   403 REVOKE ALL ON TABLE postfix_alias FROM PUBLIC;
   404 REVOKE ALL ON TABLE postfix_alias FROM posta;
   405 GRANT ALL ON TABLE postfix_alias TO posta;
   406 GRANT SELECT ON TABLE postfix_alias TO posta_cteni;
   407 
   408 
   409 --
   410 -- TOC entry 1915 (class 0 OID 0)
   411 -- Dependencies: 151
   412 -- Name: postfix_uzivatel; Type: ACL; Schema: posta; Owner: posta
   413 --
   414 
   415 REVOKE ALL ON TABLE postfix_uzivatel FROM PUBLIC;
   416 REVOKE ALL ON TABLE postfix_uzivatel FROM posta;
   417 GRANT ALL ON TABLE postfix_uzivatel TO posta;
   418 GRANT SELECT ON TABLE postfix_uzivatel TO posta_cteni;
   419 
   420 
   421 --
   422 -- TOC entry 1916 (class 0 OID 0)
   423 -- Dependencies: 152
   424 -- Name: sogo_uzivatel; Type: ACL; Schema: posta; Owner: posta
   425 --
   426 
   427 REVOKE ALL ON TABLE sogo_uzivatel FROM PUBLIC;
   428 REVOKE ALL ON TABLE sogo_uzivatel FROM posta;
   429 GRANT ALL ON TABLE sogo_uzivatel TO posta;
   430 GRANT SELECT ON TABLE sogo_uzivatel TO posta_cteni;
   431 
   432 
   433 -- Completed on 2011-12-03 17:50:08 CET
   434 
   435 --
   436 -- PostgreSQL database dump complete
   437 --
   438