veverka.ch: sql/schéma.sql@a5c8edf14f82

     1 --

     2 -- PostgreSQL database dump

     3 --

     5 -- Started on 2009-08-28 17:21:16 CEST

     7 SET client_encoding = 'UTF8';

     8 SET standard_conforming_strings = off;

     9 SET check_function_bodies = false;

    10 SET client_min_messages = warning;

    11 SET escape_string_warning = off;

    13 --

    14 -- TOC entry 8 (class 2615 OID 16387)

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

    16 --

    18 CREATE SCHEMA posta;

    21 SET search_path = posta, pg_catalog;

    23 --

    24 -- TOC entry 57 (class 1255 OID 16694)

    25 -- Dependencies: 472 8

    26 -- Name: zmen_heslo(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: posta; Owner: -

    27 --

    29 CREATE FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) RETURNS text

    30     AS $$DECLARE pocet integer;

    31 BEGIN

    32 UPDATE uzivatel

    33  SET heslo = md5("Nove_heslo")

    34  WHERE 	uzivatel.login = "Login"

    35 	AND uzivatel.domena = "Domena"

    36 	AND uzivatel.heslo = md5("Stare_heslo");

    37 	GET DIAGNOSTICS pocet = ROW_COUNT;

    38 	IF pocet = 1 THEN

    39 		RETURN 'Změna hesla pro ' || "Login" || '@' || "Domena" || ' proběhla úspěšně.';

    40 	ELSE

    41 		RAISE EXCEPTION 'Nesprávné heslo, nebo uživatel % neexistuje', "Login" || '@' || "Domena";

    42 	END IF;

    43 END;

    44 	$$

    45     LANGUAGE plpgsql SECURITY DEFINER;

    48 --

    49 -- TOC entry 1981 (class 0 OID 0)

    50 -- Dependencies: 57

    51 -- Name: FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying); Type: COMMENT; Schema: posta; Owner: -

    52 --

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

    57 SET default_tablespace = '';

    59 SET default_with_oids = false;

    61 --

    62 -- TOC entry 1657 (class 1259 OID 16550)

    63 -- Dependencies: 8

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

    65 --

    67 CREATE TABLE alias (

    68     zdroj_jmeno character varying(255) NOT NULL,

    69     zdroj_domena character varying(255) NOT NULL,

    70     cil_jmeno character varying(255) NOT NULL,

    71     cil_domena character varying(255) NOT NULL

    72 );

    75 --

    76 -- TOC entry 1983 (class 0 OID 0)

    77 -- Dependencies: 1657

    78 -- Name: COLUMN alias.zdroj_jmeno; Type: COMMENT; Schema: posta; Owner: -

    79 --

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

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

    85 --

    86 -- TOC entry 1984 (class 0 OID 0)

    87 -- Dependencies: 1657

    88 -- Name: COLUMN alias.zdroj_domena; Type: COMMENT; Schema: posta; Owner: -

    89 --

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

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

    95 --

    96 -- TOC entry 1985 (class 0 OID 0)

    97 -- Dependencies: 1657

    98 -- Name: COLUMN alias.cil_jmeno; Type: COMMENT; Schema: posta; Owner: -

    99 --

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

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

   105 --

   106 -- TOC entry 1986 (class 0 OID 0)

   107 -- Dependencies: 1657

   108 -- Name: COLUMN alias.cil_domena; Type: COMMENT; Schema: posta; Owner: -

   109 --

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

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

   115 --

   116 -- TOC entry 1644 (class 1259 OID 16396)

   117 -- Dependencies: 8

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

   119 --

   121 CREATE TABLE domena (

   122     domena character varying(255) NOT NULL

   123 );

   126 --

   127 -- TOC entry 1645 (class 1259 OID 16409)

   128 -- Dependencies: 8

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

   130 --

   132 CREATE TABLE uzivatel (

   133     login character varying(255) NOT NULL,

   134     heslo character varying(512),

   135     domena character varying(255) NOT NULL

   136 );

   139 --

   140 -- TOC entry 1658 (class 1259 OID 16602)

   141 -- Dependencies: 1775 8

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

   143 --

   145 CREATE VIEW dovecot_heslo AS

   146     SELECT (((uzivatel.login)::text || '@'::text) || (uzivatel.domena)::text) AS "user", uzivatel.heslo AS password, uzivatel.login, uzivatel.domena FROM uzivatel;

   149 --

   150 -- TOC entry 1660 (class 1259 OID 16631)

   151 -- Dependencies: 1777 8

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

   153 --

   155 CREATE VIEW dovecot_uzivatel AS

   156     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:storage=524288'::text AS quota, uzivatel.login, uzivatel.domena FROM uzivatel;

   159 --

   160 -- TOC entry 1661 (class 1259 OID 16695)

   161 -- Dependencies: 1778 8

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

   163 --

   165 CREATE VIEW postfix_alias AS

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

   169 --

   170 -- TOC entry 1659 (class 1259 OID 16607)

   171 -- Dependencies: 1776 8

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

   173 --

   175 CREATE VIEW postfix_uzivatel AS

   176     SELECT ((((uzivatel.domena)::text || '/'::text) || (uzivatel.login)::text) || '/Maildir/'::text) AS cesta, uzivatel.login, uzivatel.domena FROM uzivatel;

   179 --

   180 -- TOC entry 1975 (class 2606 OID 17071)

   181 -- Dependencies: 1657 1657 1657 1657 1657

   182 -- Name: aliasy_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace:

   183 --

   185 ALTER TABLE ONLY alias

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

   189 --

   190 -- TOC entry 1971 (class 2606 OID 16400)

   191 -- Dependencies: 1644 1644

   192 -- Name: domena_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace:

   193 --

   195 ALTER TABLE ONLY domena

   196     ADD CONSTRAINT domena_pk PRIMARY KEY (domena);

   199 --

   200 -- TOC entry 1973 (class 2606 OID 16416)

   201 -- Dependencies: 1645 1645 1645

   202 -- Name: uzivatel_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace:

   203 --

   205 ALTER TABLE ONLY uzivatel

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

   209 --

   210 -- TOC entry 1977 (class 2606 OID 16558)

   211 -- Dependencies: 1657 1970 1644

   212 -- Name: alias_domena_fk; Type: FK CONSTRAINT; Schema: posta; Owner: -

   213 --

   215 ALTER TABLE ONLY alias

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

   219 --

   220 -- TOC entry 1976 (class 2606 OID 16417)

   221 -- Dependencies: 1644 1645 1970

   222 -- Name: uzivatel_domena_fk; Type: FK CONSTRAINT; Schema: posta; Owner: -

   223 --

   225 ALTER TABLE ONLY uzivatel

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

   229 --

   230 -- TOC entry 1980 (class 0 OID 0)

   231 -- Dependencies: 8

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

   233 --

   235 REVOKE ALL ON SCHEMA posta FROM PUBLIC;

   236 REVOKE ALL ON SCHEMA posta FROM posta;

   237 GRANT ALL ON SCHEMA posta TO posta;

   238 GRANT USAGE ON SCHEMA posta TO posta_cteni;

   239 GRANT USAGE ON SCHEMA posta TO posta_zmena_hesla;

   242 --

   243 -- TOC entry 1982 (class 0 OID 0)

   244 -- Dependencies: 57

   245 -- Name: zmen_heslo(character varying, character varying, character varying, character varying); Type: ACL; Schema: posta; Owner: -

   246 --

   248 REVOKE ALL ON FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) FROM PUBLIC;

   249 REVOKE ALL ON FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) FROM posta;

   250 GRANT ALL ON FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) TO posta;

   251 GRANT ALL ON FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) TO PUBLIC;

   252 GRANT ALL ON FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) TO posta_zmena_hesla;

   255 --

   256 -- TOC entry 1987 (class 0 OID 0)

   257 -- Dependencies: 1657

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

   259 --

   261 REVOKE ALL ON TABLE alias FROM PUBLIC;

   262 REVOKE ALL ON TABLE alias FROM posta;

   263 GRANT ALL ON TABLE alias TO posta;

   266 --

   267 -- TOC entry 1988 (class 0 OID 0)

   268 -- Dependencies: 1644

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

   270 --

   272 REVOKE ALL ON TABLE domena FROM PUBLIC;

   273 REVOKE ALL ON TABLE domena FROM posta;

   274 GRANT ALL ON TABLE domena TO posta;

   275 GRANT SELECT ON TABLE domena TO posta_cteni;

   278 --

   279 -- TOC entry 1989 (class 0 OID 0)

   280 -- Dependencies: 1645

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

   282 --

   284 REVOKE ALL ON TABLE uzivatel FROM PUBLIC;

   285 REVOKE ALL ON TABLE uzivatel FROM posta;

   286 GRANT ALL ON TABLE uzivatel TO posta;

   287 GRANT SELECT ON TABLE uzivatel TO posta_cteni;

   290 --

   291 -- TOC entry 1990 (class 0 OID 0)

   292 -- Dependencies: 1658

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

   294 --

   296 REVOKE ALL ON TABLE dovecot_heslo FROM PUBLIC;

   297 REVOKE ALL ON TABLE dovecot_heslo FROM posta;

   298 GRANT ALL ON TABLE dovecot_heslo TO posta;

   299 GRANT SELECT ON TABLE dovecot_heslo TO posta_cteni;

   302 --

   303 -- TOC entry 1991 (class 0 OID 0)

   304 -- Dependencies: 1660

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

   306 --

   308 REVOKE ALL ON TABLE dovecot_uzivatel FROM PUBLIC;

   309 REVOKE ALL ON TABLE dovecot_uzivatel FROM posta;

   310 GRANT ALL ON TABLE dovecot_uzivatel TO posta;

   311 GRANT SELECT ON TABLE dovecot_uzivatel TO posta_cteni;

   314 --

   315 -- TOC entry 1992 (class 0 OID 0)

   316 -- Dependencies: 1661

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

   318 --

   320 REVOKE ALL ON TABLE postfix_alias FROM PUBLIC;

   321 REVOKE ALL ON TABLE postfix_alias FROM posta;

   322 GRANT ALL ON TABLE postfix_alias TO posta;

   323 GRANT SELECT ON TABLE postfix_alias TO posta_cteni;

   326 --

   327 -- TOC entry 1993 (class 0 OID 0)

   328 -- Dependencies: 1659

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

   330 --

   332 REVOKE ALL ON TABLE postfix_uzivatel FROM PUBLIC;

   333 REVOKE ALL ON TABLE postfix_uzivatel FROM posta;

   334 GRANT ALL ON TABLE postfix_uzivatel TO posta;

   335 GRANT SELECT ON TABLE postfix_uzivatel TO posta_cteni;

   338 -- Completed on 2009-08-28 17:21:17 CEST

   340 --

   341 -- PostgreSQL database dump complete

   342 --

author	František Kučera <franta-hg@frantovo.cz>
	Wed, 30 Nov 2011 15:44:34 +0100
changeset 21	a5c8edf14f82
parent 17	693f2b2587aa
child 24	571d9fd2d336
permissions	-rw-r--r--