--

-- PostgreSQL database dump

--

-- Started on 2009-05-10 17:40:57 CEST

SET client_encoding = 'UTF8';

SET standard_conforming_strings = off;

SET check_function_bodies = false;

SET client_min_messages = warning;

SET escape_string_warning = off;

--

-- TOC entry 8 (class 2615 OID 16387)

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

--

CREATE SCHEMA posta;

SET search_path = posta, pg_catalog;

--

-- TOC entry 55 (class 1255 OID 16694)

-- Dependencies: 364 8

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

--

CREATE FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) RETURNS text

    AS $_$DECLARE pocet integer;

BEGIN

UPDATE uzivatel

 SET heslo = md5($4)

 WHERE 	login = $1

	AND domena = $2

	AND heslo = md5($3);

	GET DIAGNOSTICS pocet = ROW_COUNT;

	IF pocet = 1 THEN

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

	ELSE

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

	END IF;

END;

	$_$

    LANGUAGE plpgsql SECURITY DEFINER;

--

-- TOC entry 1835 (class 0 OID 0)

-- Dependencies: 55

-- Name: FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying); Type: COMMENT; Schema: posta; Owner: -

--

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

SET default_tablespace = '';

SET default_with_oids = false;

--

-- TOC entry 1549 (class 1259 OID 16550)

-- Dependencies: 8

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

--

CREATE TABLE alias (

    zdroj_jmeno character varying(255) NOT NULL,

    zdroj_domena character varying(255) NOT NULL,

    cil_jmeno character varying(255) NOT NULL,

    cil_domena character varying(255)

);

--

-- TOC entry 1837 (class 0 OID 0)

-- Dependencies: 1549

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

--

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

--

-- TOC entry 1838 (class 0 OID 0)

-- Dependencies: 1549

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

--

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

--

-- TOC entry 1839 (class 0 OID 0)

-- Dependencies: 1549

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

--

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

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

--

-- TOC entry 1840 (class 0 OID 0)

-- Dependencies: 1549

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

--

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

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

--

-- TOC entry 1536 (class 1259 OID 16396)

-- Dependencies: 8

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

--

CREATE TABLE domena (

    domena character varying(255) NOT NULL

);

--

-- TOC entry 1537 (class 1259 OID 16409)

-- Dependencies: 8

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

--

CREATE TABLE uzivatel (

    login character varying(255) NOT NULL,

    heslo character varying(512),

    domena character varying(255) NOT NULL

);

--

-- TOC entry 1550 (class 1259 OID 16602)

-- Dependencies: 1629 8

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

--

CREATE VIEW dovecot_heslo AS

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

--

-- TOC entry 1552 (class 1259 OID 16631)

-- Dependencies: 1631 8

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

--

CREATE VIEW dovecot_uzivatel AS

    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;

--

-- TOC entry 1553 (class 1259 OID 16695)

-- Dependencies: 1632 8

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

--

CREATE VIEW postfix_alias AS

    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;

--

-- TOC entry 1551 (class 1259 OID 16607)

-- Dependencies: 1630 8

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

--

CREATE VIEW postfix_uzivatel AS

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

--

-- TOC entry 1829 (class 2606 OID 16557)

-- Dependencies: 1549 1549 1549 1549

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

--

ALTER TABLE ONLY alias

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

--

-- TOC entry 1825 (class 2606 OID 16400)

-- Dependencies: 1536 1536

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

--

ALTER TABLE ONLY domena

    ADD CONSTRAINT domena_pk PRIMARY KEY (domena);

--

-- TOC entry 1827 (class 2606 OID 16416)

-- Dependencies: 1537 1537 1537

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

--

ALTER TABLE ONLY uzivatel

    ADD CONSTRAINT uzivatel_pk PRIMARY KEY (login, domena);

--

-- TOC entry 1831 (class 2606 OID 16558)

-- Dependencies: 1536 1549 1824

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

--

ALTER TABLE ONLY alias

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

--

-- TOC entry 1830 (class 2606 OID 16417)

-- Dependencies: 1536 1824 1537

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

--

ALTER TABLE ONLY uzivatel

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

--

-- TOC entry 1834 (class 0 OID 0)

-- Dependencies: 8

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

--

REVOKE ALL ON SCHEMA posta FROM PUBLIC;

REVOKE ALL ON SCHEMA posta FROM posta;

GRANT ALL ON SCHEMA posta TO posta;

GRANT USAGE ON SCHEMA posta TO posta_cteni;

GRANT USAGE ON SCHEMA posta TO posta_zmena_hesla;

--

-- TOC entry 1836 (class 0 OID 0)

-- Dependencies: 55

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

--

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

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

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

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

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

--

-- TOC entry 1841 (class 0 OID 0)

-- Dependencies: 1549

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

--

REVOKE ALL ON TABLE alias FROM PUBLIC;

REVOKE ALL ON TABLE alias FROM posta;

GRANT ALL ON TABLE alias TO posta;

--

-- TOC entry 1842 (class 0 OID 0)

-- Dependencies: 1536

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

--

REVOKE ALL ON TABLE domena FROM PUBLIC;

REVOKE ALL ON TABLE domena FROM posta;

GRANT ALL ON TABLE domena TO posta;

GRANT SELECT ON TABLE domena TO posta_cteni;

--

-- TOC entry 1843 (class 0 OID 0)

-- Dependencies: 1537

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

--

REVOKE ALL ON TABLE uzivatel FROM PUBLIC;

REVOKE ALL ON TABLE uzivatel FROM posta;

GRANT ALL ON TABLE uzivatel TO posta;

GRANT SELECT ON TABLE uzivatel TO posta_cteni;

--

-- TOC entry 1844 (class 0 OID 0)

-- Dependencies: 1550

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

--

REVOKE ALL ON TABLE dovecot_heslo FROM PUBLIC;

REVOKE ALL ON TABLE dovecot_heslo FROM posta;

GRANT ALL ON TABLE dovecot_heslo TO posta;

GRANT SELECT ON TABLE dovecot_heslo TO posta_cteni;

--

-- TOC entry 1845 (class 0 OID 0)

-- Dependencies: 1552

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

--

REVOKE ALL ON TABLE dovecot_uzivatel FROM PUBLIC;

REVOKE ALL ON TABLE dovecot_uzivatel FROM posta;

GRANT ALL ON TABLE dovecot_uzivatel TO posta;

GRANT SELECT ON TABLE dovecot_uzivatel TO posta_cteni;

--

-- TOC entry 1846 (class 0 OID 0)

-- Dependencies: 1553

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

--

REVOKE ALL ON TABLE postfix_alias FROM PUBLIC;

REVOKE ALL ON TABLE postfix_alias FROM posta;

GRANT ALL ON TABLE postfix_alias TO posta;

GRANT SELECT ON TABLE postfix_alias TO posta_cteni;

--

-- TOC entry 1847 (class 0 OID 0)

-- Dependencies: 1551

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

--

REVOKE ALL ON TABLE postfix_uzivatel FROM PUBLIC;

REVOKE ALL ON TABLE postfix_uzivatel FROM posta;

GRANT ALL ON TABLE postfix_uzivatel TO posta;

GRANT SELECT ON TABLE postfix_uzivatel TO posta_cteni;

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

--

-- PostgreSQL database dump complete

--