veverka.ch: sql/schéma.sql@60cfdaa89b3a

     1 --

     2 -- PostgreSQL database dump

     3 --

     5 -- Started on 2011-12-03 17:50:08 CET

     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;

    14 --

    15 -- TOC entry 7 (class 2615 OID 16397)

    16 -- Name: posta; Type: SCHEMA; Schema: -; Owner: posta

    17 --

    19 CREATE SCHEMA posta;

    22 ALTER SCHEMA posta OWNER TO posta;

    24 SET search_path = posta, pg_catalog;

    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 --

    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 	$$;

    51 ALTER FUNCTION posta.zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) OWNER TO posta;

    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 --

    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.';

    62 SET default_tablespace = '';

    64 SET default_with_oids = false;

    66 --

    67 -- TOC entry 143 (class 1259 OID 16399)

    68 -- Dependencies: 7

    69 -- Name: alias; Type: TABLE; Schema: posta; Owner: posta; Tablespace:

    70 --

    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 );

    80 ALTER TABLE posta.alias OWNER TO posta;

    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 --

    88 COMMENT ON COLUMN alias.zdroj_jmeno IS 'Část zdrojové adresy před zavináčem.

    89 (sem přijde zpráva)';

    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 --

    98 COMMENT ON COLUMN alias.zdroj_domena IS 'Část zdrojové adresy za zavináčem.

    99 (sem přijde zpráva)';

   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 --

   108 COMMENT ON COLUMN alias.cil_jmeno IS 'Část cílové adresy před zavináčem.

   109 (sem zprávu předáme)';

   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 --

   118 COMMENT ON COLUMN alias.cil_domena IS 'Část cílové adresy za zavináčem.

   119 (sem zprávu předáme)';

   122 --

   123 -- TOC entry 144 (class 1259 OID 16405)

   124 -- Dependencies: 7

   125 -- Name: domena; Type: TABLE; Schema: posta; Owner: posta; Tablespace:

   126 --

   128 CREATE TABLE domena (

   129     domena character varying(255) NOT NULL

   130 );

   133 ALTER TABLE posta.domena OWNER TO posta;

   135 --

   136 -- TOC entry 145 (class 1259 OID 16408)

   137 -- Dependencies: 1691 7

   138 -- Name: alias_externi; Type: VIEW; Schema: posta; Owner: posta

   139 --

   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);

   145 ALTER TABLE posta.alias_externi OWNER TO posta;

   147 --

   148 -- TOC entry 1908 (class 0 OID 0)

   149 -- Dependencies: 145

   150 -- Name: VIEW alias_externi; Type: COMMENT; Schema: posta; Owner: posta

   151 --

   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)';

   156 --

   157 -- TOC entry 146 (class 1259 OID 16412)

   158 -- Dependencies: 1692 7

   159 -- Name: alias_interni; Type: VIEW; Schema: posta; Owner: posta

   160 --

   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);

   166 ALTER TABLE posta.alias_interni OWNER TO posta;

   168 --

   169 -- TOC entry 1909 (class 0 OID 0)

   170 -- Dependencies: 146

   171 -- Name: VIEW alias_interni; Type: COMMENT; Schema: posta; Owner: posta

   172 --

   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)';

   177 --

   178 -- TOC entry 147 (class 1259 OID 16416)

   179 -- Dependencies: 7

   180 -- Name: uzivatel; Type: TABLE; Schema: posta; Owner: posta; Tablespace:

   181 --

   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 );

   191 ALTER TABLE posta.uzivatel OWNER TO posta;

   193 --

   194 -- TOC entry 1910 (class 0 OID 0)

   195 -- Dependencies: 147

   196 -- Name: COLUMN uzivatel.jmeno; Type: COMMENT; Schema: posta; Owner: posta

   197 --

   199 COMMENT ON COLUMN uzivatel.jmeno IS 'Celé jméno uživatele';

   202 --

   203 -- TOC entry 148 (class 1259 OID 16422)

   204 -- Dependencies: 1693 7

   205 -- Name: dovecot_heslo; Type: VIEW; Schema: posta; Owner: posta

   206 --

   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;

   212 ALTER TABLE posta.dovecot_heslo OWNER TO posta;

   214 --

   215 -- TOC entry 149 (class 1259 OID 16426)

   216 -- Dependencies: 1694 7

   217 -- Name: dovecot_uzivatel; Type: VIEW; Schema: posta; Owner: posta

   218 --

   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;

   224 ALTER TABLE posta.dovecot_uzivatel OWNER TO posta;

   226 --

   227 -- TOC entry 150 (class 1259 OID 16430)

   228 -- Dependencies: 1695 7

   229 -- Name: postfix_alias; Type: VIEW; Schema: posta; Owner: posta

   230 --

   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;

   236 ALTER TABLE posta.postfix_alias OWNER TO posta;

   238 --

   239 -- TOC entry 151 (class 1259 OID 16434)

   240 -- Dependencies: 1696 7

   241 -- Name: postfix_uzivatel; Type: VIEW; Schema: posta; Owner: posta

   242 --

   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;

   248 ALTER TABLE posta.postfix_uzivatel OWNER TO posta;

   250 --

   251 -- TOC entry 152 (class 1259 OID 16438)

   252 -- Dependencies: 1697 7

   253 -- Name: sogo_uzivatel; Type: VIEW; Schema: posta; Owner: posta

   254 --

   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;

   260 ALTER TABLE posta.sogo_uzivatel OWNER TO posta;

   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 --

   268 ALTER TABLE ONLY alias

   269     ADD CONSTRAINT aliasy_pk PRIMARY KEY (zdroj_jmeno, zdroj_domena, cil_jmeno, cil_domena);

   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 --

   278 ALTER TABLE ONLY domena

   279     ADD CONSTRAINT domena_pk PRIMARY KEY (domena);

   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 --

   288 ALTER TABLE ONLY uzivatel

   289     ADD CONSTRAINT uzivatel_pk PRIMARY KEY (login, domena);

   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 --

   298 ALTER TABLE ONLY alias

   299     ADD CONSTRAINT alias_domena_fk FOREIGN KEY (zdroj_domena) REFERENCES domena(domena);

   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 --

   308 ALTER TABLE ONLY uzivatel

   309     ADD CONSTRAINT uzivatel_domena_fk FOREIGN KEY (domena) REFERENCES domena(domena);

   312 --

   313 -- TOC entry 1899 (class 0 OID 0)

   314 -- Dependencies: 7

   315 -- Name: posta; Type: ACL; Schema: -; Owner: posta

   316 --

   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;

   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 --

   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;

   338 --

   339 -- TOC entry 1906 (class 0 OID 0)

   340 -- Dependencies: 143

   341 -- Name: alias; Type: ACL; Schema: posta; Owner: posta

   342 --

   344 REVOKE ALL ON TABLE alias FROM PUBLIC;

   345 REVOKE ALL ON TABLE alias FROM posta;

   346 GRANT ALL ON TABLE alias TO posta;

   349 --

   350 -- TOC entry 1907 (class 0 OID 0)

   351 -- Dependencies: 144

   352 -- Name: domena; Type: ACL; Schema: posta; Owner: posta

   353 --

   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;

   361 --

   362 -- TOC entry 1911 (class 0 OID 0)

   363 -- Dependencies: 147

   364 -- Name: uzivatel; Type: ACL; Schema: posta; Owner: posta

   365 --

   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;

   373 --

   374 -- TOC entry 1912 (class 0 OID 0)

   375 -- Dependencies: 148

   376 -- Name: dovecot_heslo; Type: ACL; Schema: posta; Owner: posta

   377 --

   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;

   385 --

   386 -- TOC entry 1913 (class 0 OID 0)

   387 -- Dependencies: 149

   388 -- Name: dovecot_uzivatel; Type: ACL; Schema: posta; Owner: posta

   389 --

   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;

   397 --

   398 -- TOC entry 1914 (class 0 OID 0)

   399 -- Dependencies: 150

   400 -- Name: postfix_alias; Type: ACL; Schema: posta; Owner: posta

   401 --

   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;

   409 --

   410 -- TOC entry 1915 (class 0 OID 0)

   411 -- Dependencies: 151

   412 -- Name: postfix_uzivatel; Type: ACL; Schema: posta; Owner: posta

   413 --

   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;

   421 --

   422 -- TOC entry 1916 (class 0 OID 0)

   423 -- Dependencies: 152

   424 -- Name: sogo_uzivatel; Type: ACL; Schema: posta; Owner: posta

   425 --

   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;

   433 -- Completed on 2011-12-03 17:50:08 CET

   435 --

   436 -- PostgreSQL database dump complete

   437 --

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--