franta-hg@0: --
franta-hg@0: -- PostgreSQL database dump
franta-hg@0: --
franta-hg@0: 
franta-hg@12: -- Started on 2009-05-06 02:13:29 CEST
franta-hg@0: 
franta-hg@0: SET client_encoding = 'UTF8';
franta-hg@0: SET standard_conforming_strings = off;
franta-hg@0: SET check_function_bodies = false;
franta-hg@0: SET client_min_messages = warning;
franta-hg@0: SET escape_string_warning = off;
franta-hg@0: 
franta-hg@0: --
franta-hg@12: -- TOC entry 8 (class 2615 OID 16387)
franta-hg@0: -- Name: posta; Type: SCHEMA; Schema: -; Owner: -
franta-hg@0: --
franta-hg@0: 
franta-hg@0: CREATE SCHEMA posta;
franta-hg@0: 
franta-hg@0: 
franta-hg@0: SET search_path = posta, pg_catalog;
franta-hg@0: 
franta-hg@0: --
franta-hg@12: -- TOC entry 55 (class 1255 OID 16694)
franta-hg@12: -- Dependencies: 364 8
franta-hg@0: -- Name: zmen_heslo(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: posta; Owner: -
franta-hg@0: --
franta-hg@0: 
franta-hg@12: CREATE FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) RETURNS text
franta-hg@12:     AS $_$DECLARE pocet integer;
franta-hg@12: BEGIN
franta-hg@12: UPDATE uzivatel
franta-hg@0:  SET heslo = md5($4)
franta-hg@0:  WHERE 	login = $1
franta-hg@0: 	AND domena = $2
franta-hg@12: 	AND heslo = md5($3);
franta-hg@12: 	GET DIAGNOSTICS pocet = ROW_COUNT;
franta-hg@12: 	IF pocet = 1 THEN
franta-hg@12: 		RETURN 'Změna hesla pro ' || $1 || '@' || $2 || ' proběhla úspěšně.';
franta-hg@12: 	ELSE
franta-hg@12: 		RAISE EXCEPTION 'Nesprávné heslo, nebo uživatel % neexistuje', $1 || '@' || $2;
franta-hg@12: 	END IF;
franta-hg@12: END;
franta-hg@12: 	$_$
franta-hg@12:     LANGUAGE plpgsql SECURITY DEFINER;
franta-hg@0: 
franta-hg@0: 
franta-hg@0: --
franta-hg@12: -- TOC entry 1835 (class 0 OID 0)
franta-hg@12: -- Dependencies: 55
franta-hg@0: -- Name: FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying); Type: COMMENT; Schema: posta; Owner: -
franta-hg@0: --
franta-hg@0: 
franta-hg@0: 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.';
franta-hg@0: 
franta-hg@0: 
franta-hg@0: SET default_tablespace = '';
franta-hg@0: 
franta-hg@0: SET default_with_oids = false;
franta-hg@0: 
franta-hg@0: --
franta-hg@12: -- TOC entry 1549 (class 1259 OID 16550)
franta-hg@12: -- Dependencies: 8
franta-hg@0: -- Name: alias; Type: TABLE; Schema: posta; Owner: -; Tablespace: 
franta-hg@0: --
franta-hg@0: 
franta-hg@0: CREATE TABLE alias (
franta-hg@0:     login character varying(255) NOT NULL,
franta-hg@0:     domena character varying(255) NOT NULL,
franta-hg@0:     cilova_adresa character varying(255) NOT NULL
franta-hg@0: );
franta-hg@0: 
franta-hg@0: 
franta-hg@0: --
franta-hg@12: -- TOC entry 1536 (class 1259 OID 16396)
franta-hg@12: -- Dependencies: 8
franta-hg@0: -- Name: domena; Type: TABLE; Schema: posta; Owner: -; Tablespace: 
franta-hg@0: --
franta-hg@0: 
franta-hg@0: CREATE TABLE domena (
franta-hg@0:     domena character varying(255) NOT NULL
franta-hg@0: );
franta-hg@0: 
franta-hg@0: 
franta-hg@0: --
franta-hg@12: -- TOC entry 1537 (class 1259 OID 16409)
franta-hg@12: -- Dependencies: 8
franta-hg@0: -- Name: uzivatel; Type: TABLE; Schema: posta; Owner: -; Tablespace: 
franta-hg@0: --
franta-hg@0: 
franta-hg@0: CREATE TABLE uzivatel (
franta-hg@0:     login character varying(255) NOT NULL,
franta-hg@0:     heslo character varying(512),
franta-hg@0:     domena character varying(255) NOT NULL
franta-hg@0: );
franta-hg@0: 
franta-hg@0: 
franta-hg@0: --
franta-hg@12: -- TOC entry 1551 (class 1259 OID 16602)
franta-hg@12: -- Dependencies: 1630 8
franta-hg@0: -- Name: dovecot_heslo; Type: VIEW; Schema: posta; Owner: -
franta-hg@0: --
franta-hg@0: 
franta-hg@0: CREATE VIEW dovecot_heslo AS
franta-hg@0:     SELECT (((uzivatel.login)::text || '@'::text) || (uzivatel.domena)::text) AS "user", uzivatel.heslo AS password, uzivatel.login, uzivatel.domena FROM uzivatel;
franta-hg@0: 
franta-hg@0: 
franta-hg@0: --
franta-hg@12: -- TOC entry 1553 (class 1259 OID 16631)
franta-hg@12: -- Dependencies: 1632 8
franta-hg@0: -- Name: dovecot_uzivatel; Type: VIEW; Schema: posta; Owner: -
franta-hg@0: --
franta-hg@0: 
franta-hg@0: CREATE VIEW dovecot_uzivatel AS
franta-hg@0:     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;
franta-hg@0: 
franta-hg@0: 
franta-hg@0: --
franta-hg@12: -- TOC entry 1550 (class 1259 OID 16563)
franta-hg@12: -- Dependencies: 1629 8
franta-hg@0: -- Name: postfix_alias; Type: VIEW; Schema: posta; Owner: -
franta-hg@0: --
franta-hg@0: 
franta-hg@0: CREATE VIEW postfix_alias AS
franta-hg@0:     SELECT (((alias.login)::text || '@'::text) || (alias.domena)::text) AS zdroj, alias.cilova_adresa AS cil FROM alias;
franta-hg@0: 
franta-hg@0: 
franta-hg@0: --
franta-hg@12: -- TOC entry 1552 (class 1259 OID 16607)
franta-hg@12: -- Dependencies: 1631 8
franta-hg@0: -- Name: postfix_uzivatel; Type: VIEW; Schema: posta; Owner: -
franta-hg@0: --
franta-hg@0: 
franta-hg@0: CREATE VIEW postfix_uzivatel AS
franta-hg@0:     SELECT ((((uzivatel.domena)::text || '/'::text) || (uzivatel.login)::text) || '/Maildir/'::text) AS cesta, uzivatel.login, uzivatel.domena FROM uzivatel;
franta-hg@0: 
franta-hg@0: 
franta-hg@0: --
franta-hg@12: -- TOC entry 1829 (class 2606 OID 16557)
franta-hg@12: -- Dependencies: 1549 1549 1549 1549
franta-hg@0: -- Name: aliasy_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace: 
franta-hg@0: --
franta-hg@0: 
franta-hg@0: ALTER TABLE ONLY alias
franta-hg@0:     ADD CONSTRAINT aliasy_pk PRIMARY KEY (login, domena, cilova_adresa);
franta-hg@0: 
franta-hg@0: 
franta-hg@0: --
franta-hg@12: -- TOC entry 1825 (class 2606 OID 16400)
franta-hg@12: -- Dependencies: 1536 1536
franta-hg@0: -- Name: domena_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace: 
franta-hg@0: --
franta-hg@0: 
franta-hg@0: ALTER TABLE ONLY domena
franta-hg@0:     ADD CONSTRAINT domena_pk PRIMARY KEY (domena);
franta-hg@0: 
franta-hg@0: 
franta-hg@0: --
franta-hg@12: -- TOC entry 1827 (class 2606 OID 16416)
franta-hg@12: -- Dependencies: 1537 1537 1537
franta-hg@0: -- Name: uzivatel_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace: 
franta-hg@0: --
franta-hg@0: 
franta-hg@0: ALTER TABLE ONLY uzivatel
franta-hg@0:     ADD CONSTRAINT uzivatel_pk PRIMARY KEY (login, domena);
franta-hg@0: 
franta-hg@0: 
franta-hg@0: --
franta-hg@12: -- TOC entry 1831 (class 2606 OID 16558)
franta-hg@12: -- Dependencies: 1824 1536 1549
franta-hg@0: -- Name: alias_domena_fk; Type: FK CONSTRAINT; Schema: posta; Owner: -
franta-hg@0: --
franta-hg@0: 
franta-hg@0: ALTER TABLE ONLY alias
franta-hg@0:     ADD CONSTRAINT alias_domena_fk FOREIGN KEY (domena) REFERENCES domena(domena);
franta-hg@0: 
franta-hg@0: 
franta-hg@0: --
franta-hg@12: -- TOC entry 1830 (class 2606 OID 16417)
franta-hg@12: -- Dependencies: 1537 1536 1824
franta-hg@0: -- Name: uzivatel_domena_fk; Type: FK CONSTRAINT; Schema: posta; Owner: -
franta-hg@0: --
franta-hg@0: 
franta-hg@0: ALTER TABLE ONLY uzivatel
franta-hg@0:     ADD CONSTRAINT uzivatel_domena_fk FOREIGN KEY (domena) REFERENCES domena(domena);
franta-hg@0: 
franta-hg@0: 
franta-hg@0: --
franta-hg@12: -- TOC entry 1834 (class 0 OID 0)
franta-hg@12: -- Dependencies: 8
franta-hg@0: -- Name: posta; Type: ACL; Schema: -; Owner: -
franta-hg@0: --
franta-hg@0: 
franta-hg@0: REVOKE ALL ON SCHEMA posta FROM PUBLIC;
franta-hg@0: REVOKE ALL ON SCHEMA posta FROM posta;
franta-hg@0: GRANT ALL ON SCHEMA posta TO posta;
franta-hg@0: GRANT USAGE ON SCHEMA posta TO posta_cteni;
franta-hg@12: GRANT USAGE ON SCHEMA posta TO posta_zmena_hesla;
franta-hg@0: 
franta-hg@0: 
franta-hg@0: --
franta-hg@12: -- TOC entry 1836 (class 0 OID 0)
franta-hg@12: -- Dependencies: 55
franta-hg@12: -- Name: zmen_heslo(character varying, character varying, character varying, character varying); Type: ACL; Schema: posta; Owner: -
franta-hg@12: --
franta-hg@12: 
franta-hg@12: REVOKE ALL ON FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) FROM PUBLIC;
franta-hg@12: REVOKE ALL ON FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) FROM posta;
franta-hg@12: GRANT ALL ON FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) TO posta;
franta-hg@12: GRANT ALL ON FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) TO PUBLIC;
franta-hg@12: GRANT ALL ON FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) TO posta_zmena_hesla;
franta-hg@12: 
franta-hg@12: 
franta-hg@12: --
franta-hg@12: -- TOC entry 1837 (class 0 OID 0)
franta-hg@12: -- Dependencies: 1549
franta-hg@0: -- Name: alias; Type: ACL; Schema: posta; Owner: -
franta-hg@0: --
franta-hg@0: 
franta-hg@0: REVOKE ALL ON TABLE alias FROM PUBLIC;
franta-hg@0: REVOKE ALL ON TABLE alias FROM posta;
franta-hg@0: GRANT ALL ON TABLE alias TO posta;
franta-hg@0: 
franta-hg@0: 
franta-hg@0: --
franta-hg@12: -- TOC entry 1838 (class 0 OID 0)
franta-hg@12: -- Dependencies: 1536
franta-hg@0: -- Name: domena; Type: ACL; Schema: posta; Owner: -
franta-hg@0: --
franta-hg@0: 
franta-hg@0: REVOKE ALL ON TABLE domena FROM PUBLIC;
franta-hg@0: REVOKE ALL ON TABLE domena FROM posta;
franta-hg@0: GRANT ALL ON TABLE domena TO posta;
franta-hg@0: GRANT SELECT ON TABLE domena TO posta_cteni;
franta-hg@0: 
franta-hg@0: 
franta-hg@0: --
franta-hg@12: -- TOC entry 1839 (class 0 OID 0)
franta-hg@12: -- Dependencies: 1537
franta-hg@0: -- Name: uzivatel; Type: ACL; Schema: posta; Owner: -
franta-hg@0: --
franta-hg@0: 
franta-hg@0: REVOKE ALL ON TABLE uzivatel FROM PUBLIC;
franta-hg@0: REVOKE ALL ON TABLE uzivatel FROM posta;
franta-hg@0: GRANT ALL ON TABLE uzivatel TO posta;
franta-hg@0: GRANT SELECT ON TABLE uzivatel TO posta_cteni;
franta-hg@0: 
franta-hg@0: 
franta-hg@0: --
franta-hg@12: -- TOC entry 1840 (class 0 OID 0)
franta-hg@12: -- Dependencies: 1551
franta-hg@0: -- Name: dovecot_heslo; Type: ACL; Schema: posta; Owner: -
franta-hg@0: --
franta-hg@0: 
franta-hg@0: REVOKE ALL ON TABLE dovecot_heslo FROM PUBLIC;
franta-hg@0: REVOKE ALL ON TABLE dovecot_heslo FROM posta;
franta-hg@0: GRANT ALL ON TABLE dovecot_heslo TO posta;
franta-hg@0: GRANT SELECT ON TABLE dovecot_heslo TO posta_cteni;
franta-hg@0: 
franta-hg@0: 
franta-hg@0: --
franta-hg@12: -- TOC entry 1841 (class 0 OID 0)
franta-hg@12: -- Dependencies: 1553
franta-hg@0: -- Name: dovecot_uzivatel; Type: ACL; Schema: posta; Owner: -
franta-hg@0: --
franta-hg@0: 
franta-hg@0: REVOKE ALL ON TABLE dovecot_uzivatel FROM PUBLIC;
franta-hg@0: REVOKE ALL ON TABLE dovecot_uzivatel FROM posta;
franta-hg@0: GRANT ALL ON TABLE dovecot_uzivatel TO posta;
franta-hg@0: GRANT SELECT ON TABLE dovecot_uzivatel TO posta_cteni;
franta-hg@0: 
franta-hg@0: 
franta-hg@0: --
franta-hg@12: -- TOC entry 1842 (class 0 OID 0)
franta-hg@12: -- Dependencies: 1550
franta-hg@0: -- Name: postfix_alias; Type: ACL; Schema: posta; Owner: -
franta-hg@0: --
franta-hg@0: 
franta-hg@0: REVOKE ALL ON TABLE postfix_alias FROM PUBLIC;
franta-hg@0: REVOKE ALL ON TABLE postfix_alias FROM posta;
franta-hg@0: GRANT ALL ON TABLE postfix_alias TO posta;
franta-hg@0: GRANT SELECT ON TABLE postfix_alias TO posta_cteni;
franta-hg@0: 
franta-hg@0: 
franta-hg@0: --
franta-hg@12: -- TOC entry 1843 (class 0 OID 0)
franta-hg@12: -- Dependencies: 1552
franta-hg@0: -- Name: postfix_uzivatel; Type: ACL; Schema: posta; Owner: -
franta-hg@0: --
franta-hg@0: 
franta-hg@0: REVOKE ALL ON TABLE postfix_uzivatel FROM PUBLIC;
franta-hg@0: REVOKE ALL ON TABLE postfix_uzivatel FROM posta;
franta-hg@0: GRANT ALL ON TABLE postfix_uzivatel TO posta;
franta-hg@0: GRANT SELECT ON TABLE postfix_uzivatel TO posta_cteni;
franta-hg@0: 
franta-hg@0: 
franta-hg@12: -- Completed on 2009-05-06 02:13:36 CEST
franta-hg@0: 
franta-hg@0: --
franta-hg@0: -- PostgreSQL database dump complete
franta-hg@0: --
franta-hg@0: