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

     1 --

     2 -- PostgreSQL database dump

     3 --

     5 -- Started on 2009-05-10 17:40:57 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     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)

    72 );

    75 --

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

    77 -- Dependencies: 1549

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

    84 --

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

    86 -- Dependencies: 1549

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

    88 --

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

    93 --

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

    95 -- Dependencies: 1549

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

    97 --

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

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

   103 --

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

   105 -- Dependencies: 1549

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

   107 --

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

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

   113 --

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

   115 -- Dependencies: 8

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

   117 --

   119 CREATE TABLE domena (

   120     domena character varying(255) NOT NULL

   121 );

   124 --

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

   126 -- Dependencies: 8

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

   128 --

   130 CREATE TABLE uzivatel (

   131     login character varying(255) NOT NULL,

   132     heslo character varying(512),

   133     domena character varying(255) NOT NULL

   134 );

   137 --

   138 -- TOC entry 1550 (class 1259 OID 16602)

   139 -- Dependencies: 1629 8

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

   141 --

   143 CREATE VIEW dovecot_heslo AS

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

   147 --

   148 -- TOC entry 1552 (class 1259 OID 16631)

   149 -- Dependencies: 1631 8

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

   151 --

   153 CREATE VIEW dovecot_uzivatel AS

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

   157 --

   158 -- TOC entry 1553 (class 1259 OID 16695)

   159 -- Dependencies: 1632 8

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

   161 --

   163 CREATE VIEW postfix_alias AS

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

   167 --

   168 -- TOC entry 1551 (class 1259 OID 16607)

   169 -- Dependencies: 1630 8

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

   171 --

   173 CREATE VIEW postfix_uzivatel AS

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

   177 --

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

   179 -- Dependencies: 1549 1549 1549 1549

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

   181 --

   183 ALTER TABLE ONLY alias

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

   187 --

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

   189 -- Dependencies: 1536 1536

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

   191 --

   193 ALTER TABLE ONLY domena

   194     ADD CONSTRAINT domena_pk PRIMARY KEY (domena);

   197 --

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

   199 -- Dependencies: 1537 1537 1537

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

   201 --

   203 ALTER TABLE ONLY uzivatel

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

   207 --

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

   209 -- Dependencies: 1536 1549 1824

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

   211 --

   213 ALTER TABLE ONLY alias

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

   217 --

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

   219 -- Dependencies: 1536 1824 1537

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

   221 --

   223 ALTER TABLE ONLY uzivatel

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

   227 --

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

   229 -- Dependencies: 8

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

   231 --

   233 REVOKE ALL ON SCHEMA posta FROM PUBLIC;

   234 REVOKE ALL ON SCHEMA posta FROM posta;

   235 GRANT ALL ON SCHEMA posta TO posta;

   236 GRANT USAGE ON SCHEMA posta TO posta_cteni;

   237 GRANT USAGE ON SCHEMA posta TO posta_zmena_hesla;

   240 --

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

   242 -- Dependencies: 55

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

   244 --

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

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

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

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

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

   253 --

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

   255 -- Dependencies: 1549

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

   257 --

   259 REVOKE ALL ON TABLE alias FROM PUBLIC;

   260 REVOKE ALL ON TABLE alias FROM posta;

   261 GRANT ALL ON TABLE alias TO posta;

   264 --

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

   266 -- Dependencies: 1536

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

   268 --

   270 REVOKE ALL ON TABLE domena FROM PUBLIC;

   271 REVOKE ALL ON TABLE domena FROM posta;

   272 GRANT ALL ON TABLE domena TO posta;

   273 GRANT SELECT ON TABLE domena TO posta_cteni;

   276 --

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

   278 -- Dependencies: 1537

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

   280 --

   282 REVOKE ALL ON TABLE uzivatel FROM PUBLIC;

   283 REVOKE ALL ON TABLE uzivatel FROM posta;

   284 GRANT ALL ON TABLE uzivatel TO posta;

   285 GRANT SELECT ON TABLE uzivatel TO posta_cteni;

   288 --

   289 -- TOC entry 1844 (class 0 OID 0)

   290 -- Dependencies: 1550

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

   292 --

   294 REVOKE ALL ON TABLE dovecot_heslo FROM PUBLIC;

   295 REVOKE ALL ON TABLE dovecot_heslo FROM posta;

   296 GRANT ALL ON TABLE dovecot_heslo TO posta;

   297 GRANT SELECT ON TABLE dovecot_heslo TO posta_cteni;

   300 --

   301 -- TOC entry 1845 (class 0 OID 0)

   302 -- Dependencies: 1552

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

   304 --

   306 REVOKE ALL ON TABLE dovecot_uzivatel FROM PUBLIC;

   307 REVOKE ALL ON TABLE dovecot_uzivatel FROM posta;

   308 GRANT ALL ON TABLE dovecot_uzivatel TO posta;

   309 GRANT SELECT ON TABLE dovecot_uzivatel TO posta_cteni;

   312 --

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

   314 -- Dependencies: 1553

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

   316 --

   318 REVOKE ALL ON TABLE postfix_alias FROM PUBLIC;

   319 REVOKE ALL ON TABLE postfix_alias FROM posta;

   320 GRANT ALL ON TABLE postfix_alias TO posta;

   321 GRANT SELECT ON TABLE postfix_alias TO posta_cteni;

   324 --

   325 -- TOC entry 1847 (class 0 OID 0)

   326 -- Dependencies: 1551

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

   328 --

   330 REVOKE ALL ON TABLE postfix_uzivatel FROM PUBLIC;

   331 REVOKE ALL ON TABLE postfix_uzivatel FROM posta;

   332 GRANT ALL ON TABLE postfix_uzivatel TO posta;

   333 GRANT SELECT ON TABLE postfix_uzivatel TO posta_cteni;

   336 -- Completed on 2009-05-10 17:41:05 CEST

   338 --

   339 -- PostgreSQL database dump complete

   340 --

author	František Kučera <franta-hg@frantovo.cz>
	Sun May 10 18:18:04 2009 +0200 (2009-05-10)
changeset 14	c830b05699ba
parent 12	38aadc7c6386
child 15	ad391f838b9d
permissions	-rw-r--r--