diff -r 000000000000 -r 74a6a363ff9e sql/schéma.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sql/schéma.sql Tue May 05 17:08:09 2009 +0200 @@ -0,0 +1,278 @@ +-- +-- PostgreSQL database dump +-- + +-- Started on 2009-05-05 16:25:08 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 6 (class 2615 OID 16387) +-- Name: posta; Type: SCHEMA; Schema: -; Owner: - +-- + +CREATE SCHEMA posta; + + +SET search_path = posta, pg_catalog; + +-- +-- TOC entry 22 (class 1255 OID 16646) +-- Dependencies: 6 +-- 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 void + AS $_$UPDATE uzivatel + SET heslo = md5($4) + WHERE login = $1 + AND domena = $2 + AND heslo = md5($3);$_$ + LANGUAGE sql SECURITY DEFINER; + + +-- +-- TOC entry 1801 (class 0 OID 0) +-- Dependencies: 22 +-- 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 1515 (class 1259 OID 16550) +-- Dependencies: 6 +-- Name: alias; Type: TABLE; Schema: posta; Owner: -; Tablespace: +-- + +CREATE TABLE alias ( + login character varying(255) NOT NULL, + domena character varying(255) NOT NULL, + cilova_adresa character varying(255) NOT NULL +); + + +-- +-- TOC entry 1502 (class 1259 OID 16396) +-- Dependencies: 6 +-- Name: domena; Type: TABLE; Schema: posta; Owner: -; Tablespace: +-- + +CREATE TABLE domena ( + domena character varying(255) NOT NULL +); + + +-- +-- TOC entry 1503 (class 1259 OID 16409) +-- Dependencies: 6 +-- 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 1517 (class 1259 OID 16602) +-- Dependencies: 1596 6 +-- 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 1519 (class 1259 OID 16631) +-- Dependencies: 1598 6 +-- 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 1516 (class 1259 OID 16563) +-- Dependencies: 1595 6 +-- Name: postfix_alias; Type: VIEW; Schema: posta; Owner: - +-- + +CREATE VIEW postfix_alias AS + SELECT (((alias.login)::text || '@'::text) || (alias.domena)::text) AS zdroj, alias.cilova_adresa AS cil FROM alias; + + +-- +-- TOC entry 1518 (class 1259 OID 16607) +-- Dependencies: 1597 6 +-- 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 1795 (class 2606 OID 16557) +-- Dependencies: 1515 1515 1515 1515 +-- Name: aliasy_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY alias + ADD CONSTRAINT aliasy_pk PRIMARY KEY (login, domena, cilova_adresa); + + +-- +-- TOC entry 1791 (class 2606 OID 16400) +-- Dependencies: 1502 1502 +-- Name: domena_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY domena + ADD CONSTRAINT domena_pk PRIMARY KEY (domena); + + +-- +-- TOC entry 1793 (class 2606 OID 16416) +-- Dependencies: 1503 1503 1503 +-- Name: uzivatel_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY uzivatel + ADD CONSTRAINT uzivatel_pk PRIMARY KEY (login, domena); + + +-- +-- TOC entry 1797 (class 2606 OID 16558) +-- Dependencies: 1790 1515 1502 +-- Name: alias_domena_fk; Type: FK CONSTRAINT; Schema: posta; Owner: - +-- + +ALTER TABLE ONLY alias + ADD CONSTRAINT alias_domena_fk FOREIGN KEY (domena) REFERENCES domena(domena); + + +-- +-- TOC entry 1796 (class 2606 OID 16417) +-- Dependencies: 1503 1502 1790 +-- 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 1800 (class 0 OID 0) +-- Dependencies: 6 +-- 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; + + +-- +-- TOC entry 1802 (class 0 OID 0) +-- Dependencies: 1515 +-- 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 1803 (class 0 OID 0) +-- Dependencies: 1502 +-- 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 1804 (class 0 OID 0) +-- Dependencies: 1503 +-- 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 1805 (class 0 OID 0) +-- Dependencies: 1517 +-- 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 1806 (class 0 OID 0) +-- Dependencies: 1519 +-- 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 1807 (class 0 OID 0) +-- Dependencies: 1516 +-- 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 1808 (class 0 OID 0) +-- Dependencies: 1518 +-- 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-05 16:25:18 CEST + +-- +-- PostgreSQL database dump complete +-- +