franta-hg@0: -- franta-hg@0: -- PostgreSQL database dump franta-hg@0: -- franta-hg@0: franta-hg@16: -- Started on 2009-05-29 21:13:11 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@16: 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@16: SET heslo = md5("Nove_heslo") franta-hg@16: WHERE uzivatel.login = "Login" franta-hg@16: AND uzivatel.domena = "Domena" franta-hg@16: AND uzivatel.heslo = md5("Stare_heslo"); 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@16: -- 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@16: 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@14: zdroj_jmeno character varying(255) NOT NULL, franta-hg@14: zdroj_domena character varying(255) NOT NULL, franta-hg@14: cil_jmeno character varying(255) NOT NULL, franta-hg@14: cil_domena character varying(255) franta-hg@0: ); franta-hg@0: franta-hg@0: franta-hg@0: -- franta-hg@14: -- TOC entry 1837 (class 0 OID 0) franta-hg@14: -- Dependencies: 1549 franta-hg@14: -- Name: COLUMN alias.zdroj_jmeno; Type: COMMENT; Schema: posta; Owner: - franta-hg@14: -- franta-hg@14: franta-hg@15: COMMENT ON COLUMN alias.zdroj_jmeno IS 'Část zdrojové adresy před zavináčem. franta-hg@15: (sem přijde zpráva)'; franta-hg@14: franta-hg@14: franta-hg@14: -- franta-hg@14: -- TOC entry 1838 (class 0 OID 0) franta-hg@14: -- Dependencies: 1549 franta-hg@14: -- Name: COLUMN alias.zdroj_domena; Type: COMMENT; Schema: posta; Owner: - franta-hg@14: -- franta-hg@14: franta-hg@15: COMMENT ON COLUMN alias.zdroj_domena IS 'Část zdrojové adresy za zavináčem. franta-hg@15: (sem přijde zpráva)'; franta-hg@14: franta-hg@14: franta-hg@14: -- franta-hg@14: -- TOC entry 1839 (class 0 OID 0) franta-hg@14: -- Dependencies: 1549 franta-hg@14: -- Name: COLUMN alias.cil_jmeno; Type: COMMENT; Schema: posta; Owner: - franta-hg@14: -- franta-hg@14: franta-hg@15: COMMENT ON COLUMN alias.cil_jmeno IS 'Část cílové adresy před zavináčem. franta-hg@14: (sem zprávu předáme)'; franta-hg@14: franta-hg@14: franta-hg@14: -- franta-hg@14: -- TOC entry 1840 (class 0 OID 0) franta-hg@14: -- Dependencies: 1549 franta-hg@14: -- Name: COLUMN alias.cil_domena; Type: COMMENT; Schema: posta; Owner: - franta-hg@14: -- franta-hg@14: franta-hg@15: COMMENT ON COLUMN alias.cil_domena IS 'Část cílové adresy za zavináčem. franta-hg@14: (sem zprávu předáme)'; franta-hg@14: franta-hg@14: franta-hg@14: -- 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@14: -- TOC entry 1550 (class 1259 OID 16602) franta-hg@14: -- Dependencies: 1629 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@14: -- TOC entry 1552 (class 1259 OID 16631) franta-hg@14: -- Dependencies: 1631 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@14: -- TOC entry 1553 (class 1259 OID 16695) franta-hg@14: -- Dependencies: 1632 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@14: 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; franta-hg@0: franta-hg@0: franta-hg@0: -- franta-hg@14: -- TOC entry 1551 (class 1259 OID 16607) franta-hg@14: -- Dependencies: 1630 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@14: ADD CONSTRAINT aliasy_pk PRIMARY KEY (zdroj_jmeno, zdroj_domena, cil_jmeno); 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@16: -- Dependencies: 1549 1824 1536 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@14: ADD CONSTRAINT alias_domena_fk FOREIGN KEY (zdroj_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@14: -- Dependencies: 1536 1824 1537 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@16: REVOKE ALL ON FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) FROM PUBLIC; franta-hg@16: REVOKE ALL ON FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) FROM posta; franta-hg@16: GRANT ALL ON FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) TO posta; franta-hg@16: GRANT ALL ON FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) TO PUBLIC; franta-hg@16: 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@14: -- TOC entry 1841 (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@14: -- TOC entry 1842 (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@14: -- TOC entry 1843 (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@14: -- TOC entry 1844 (class 0 OID 0) franta-hg@14: -- Dependencies: 1550 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@14: -- TOC entry 1845 (class 0 OID 0) franta-hg@14: -- Dependencies: 1552 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@14: -- TOC entry 1846 (class 0 OID 0) franta-hg@14: -- Dependencies: 1553 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@14: -- TOC entry 1847 (class 0 OID 0) franta-hg@14: -- Dependencies: 1551 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@16: -- Completed on 2009-05-29 21:13:23 CEST franta-hg@0: franta-hg@0: -- franta-hg@0: -- PostgreSQL database dump complete franta-hg@0: -- franta-hg@0: