veverka.ch: sql/schéma.sql@38aadc7c6386

     1 --

     2 -- PostgreSQL database dump

     3 --

     5 -- Started on 2009-05-06 02:13:29 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 55 (class 1255 OID 16694)

    25 -- Dependencies: 364 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($4)

    34  WHERE 	login = $1

    35 	AND domena = $2

    36 	AND heslo = md5($3);

    37 	GET DIAGNOSTICS pocet = ROW_COUNT;

    38 	IF pocet = 1 THEN

    39 		RETURN 'Změna hesla pro ' || $1 || '@' || $2 || ' proběhla úspěšně.';

    40 	ELSE

    41 		RAISE EXCEPTION 'Nesprávné heslo, nebo uživatel % neexistuje', $1 || '@' || $2;

    42 	END IF;

    43 END;

    44 	$_$

    45     LANGUAGE plpgsql SECURITY DEFINER;

    48 --

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

    50 -- Dependencies: 55

    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 1549 (class 1259 OID 16550)

    63 -- Dependencies: 8

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

    65 --

    67 CREATE TABLE alias (

    68     login character varying(255) NOT NULL,

    69     domena character varying(255) NOT NULL,

    70     cilova_adresa character varying(255) NOT NULL

    71 );

    74 --

    75 -- TOC entry 1536 (class 1259 OID 16396)

    76 -- Dependencies: 8

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

    78 --

    80 CREATE TABLE domena (

    81     domena character varying(255) NOT NULL

    82 );

    85 --

    86 -- TOC entry 1537 (class 1259 OID 16409)

    87 -- Dependencies: 8

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

    89 --

    91 CREATE TABLE uzivatel (

    92     login character varying(255) NOT NULL,

    93     heslo character varying(512),

    94     domena character varying(255) NOT NULL

    95 );

    98 --

    99 -- TOC entry 1551 (class 1259 OID 16602)

   100 -- Dependencies: 1630 8

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

   102 --

   104 CREATE VIEW dovecot_heslo AS

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

   108 --

   109 -- TOC entry 1553 (class 1259 OID 16631)

   110 -- Dependencies: 1632 8

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

   112 --

   114 CREATE VIEW dovecot_uzivatel AS

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

   118 --

   119 -- TOC entry 1550 (class 1259 OID 16563)

   120 -- Dependencies: 1629 8

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

   122 --

   124 CREATE VIEW postfix_alias AS

   125     SELECT (((alias.login)::text || '@'::text) || (alias.domena)::text) AS zdroj, alias.cilova_adresa AS cil FROM alias;

   128 --

   129 -- TOC entry 1552 (class 1259 OID 16607)

   130 -- Dependencies: 1631 8

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

   132 --

   134 CREATE VIEW postfix_uzivatel AS

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

   138 --

   139 -- TOC entry 1829 (class 2606 OID 16557)

   140 -- Dependencies: 1549 1549 1549 1549

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

   142 --

   144 ALTER TABLE ONLY alias

   145     ADD CONSTRAINT aliasy_pk PRIMARY KEY (login, domena, cilova_adresa);

   148 --

   149 -- TOC entry 1825 (class 2606 OID 16400)

   150 -- Dependencies: 1536 1536

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

   152 --

   154 ALTER TABLE ONLY domena

   155     ADD CONSTRAINT domena_pk PRIMARY KEY (domena);

   158 --

   159 -- TOC entry 1827 (class 2606 OID 16416)

   160 -- Dependencies: 1537 1537 1537

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

   162 --

   164 ALTER TABLE ONLY uzivatel

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

   168 --

   169 -- TOC entry 1831 (class 2606 OID 16558)

   170 -- Dependencies: 1824 1536 1549

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

   172 --

   174 ALTER TABLE ONLY alias

   175     ADD CONSTRAINT alias_domena_fk FOREIGN KEY (domena) REFERENCES domena(domena);

   178 --

   179 -- TOC entry 1830 (class 2606 OID 16417)

   180 -- Dependencies: 1537 1536 1824

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

   182 --

   184 ALTER TABLE ONLY uzivatel

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

   188 --

   189 -- TOC entry 1834 (class 0 OID 0)

   190 -- Dependencies: 8

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

   192 --

   194 REVOKE ALL ON SCHEMA posta FROM PUBLIC;

   195 REVOKE ALL ON SCHEMA posta FROM posta;

   196 GRANT ALL ON SCHEMA posta TO posta;

   197 GRANT USAGE ON SCHEMA posta TO posta_cteni;

   198 GRANT USAGE ON SCHEMA posta TO posta_zmena_hesla;

   201 --

   202 -- TOC entry 1836 (class 0 OID 0)

   203 -- Dependencies: 55

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

   205 --

   207 REVOKE ALL ON FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) FROM PUBLIC;

   208 REVOKE ALL ON FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) FROM posta;

   209 GRANT ALL ON FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) TO posta;

   210 GRANT ALL ON FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) TO PUBLIC;

   211 GRANT ALL ON FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) TO posta_zmena_hesla;

   214 --

   215 -- TOC entry 1837 (class 0 OID 0)

   216 -- Dependencies: 1549

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

   218 --

   220 REVOKE ALL ON TABLE alias FROM PUBLIC;

   221 REVOKE ALL ON TABLE alias FROM posta;

   222 GRANT ALL ON TABLE alias TO posta;

   225 --

   226 -- TOC entry 1838 (class 0 OID 0)

   227 -- Dependencies: 1536

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

   229 --

   231 REVOKE ALL ON TABLE domena FROM PUBLIC;

   232 REVOKE ALL ON TABLE domena FROM posta;

   233 GRANT ALL ON TABLE domena TO posta;

   234 GRANT SELECT ON TABLE domena TO posta_cteni;

   237 --

   238 -- TOC entry 1839 (class 0 OID 0)

   239 -- Dependencies: 1537

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

   241 --

   243 REVOKE ALL ON TABLE uzivatel FROM PUBLIC;

   244 REVOKE ALL ON TABLE uzivatel FROM posta;

   245 GRANT ALL ON TABLE uzivatel TO posta;

   246 GRANT SELECT ON TABLE uzivatel TO posta_cteni;

   249 --

   250 -- TOC entry 1840 (class 0 OID 0)

   251 -- Dependencies: 1551

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

   253 --

   255 REVOKE ALL ON TABLE dovecot_heslo FROM PUBLIC;

   256 REVOKE ALL ON TABLE dovecot_heslo FROM posta;

   257 GRANT ALL ON TABLE dovecot_heslo TO posta;

   258 GRANT SELECT ON TABLE dovecot_heslo TO posta_cteni;

   261 --

   262 -- TOC entry 1841 (class 0 OID 0)

   263 -- Dependencies: 1553

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

   265 --

   267 REVOKE ALL ON TABLE dovecot_uzivatel FROM PUBLIC;

   268 REVOKE ALL ON TABLE dovecot_uzivatel FROM posta;

   269 GRANT ALL ON TABLE dovecot_uzivatel TO posta;

   270 GRANT SELECT ON TABLE dovecot_uzivatel TO posta_cteni;

   273 --

   274 -- TOC entry 1842 (class 0 OID 0)

   275 -- Dependencies: 1550

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

   277 --

   279 REVOKE ALL ON TABLE postfix_alias FROM PUBLIC;

   280 REVOKE ALL ON TABLE postfix_alias FROM posta;

   281 GRANT ALL ON TABLE postfix_alias TO posta;

   282 GRANT SELECT ON TABLE postfix_alias TO posta_cteni;

   285 --

   286 -- TOC entry 1843 (class 0 OID 0)

   287 -- Dependencies: 1552

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

   289 --

   291 REVOKE ALL ON TABLE postfix_uzivatel FROM PUBLIC;

   292 REVOKE ALL ON TABLE postfix_uzivatel FROM posta;

   293 GRANT ALL ON TABLE postfix_uzivatel TO posta;

   294 GRANT SELECT ON TABLE postfix_uzivatel TO posta_cteni;

   297 -- Completed on 2009-05-06 02:13:36 CEST

   299 --

   300 -- PostgreSQL database dump complete

   301 --

author	František Kučera <franta-hg@frantovo.cz>
	Wed May 06 13:12:03 2009 +0200 (2009-05-06)
changeset 12	38aadc7c6386
parent 0	c83d559ca362
child 14	c830b05699ba
permissions	-rw-r--r--