1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/sql/schéma.sql Tue May 05 16:46:22 2009 +0200
1.3 @@ -0,0 +1,278 @@
1.4 +--
1.5 +-- PostgreSQL database dump
1.6 +--
1.7 +
1.8 +-- Started on 2009-05-05 16:25:08 CEST
1.9 +
1.10 +SET client_encoding = 'UTF8';
1.11 +SET standard_conforming_strings = off;
1.12 +SET check_function_bodies = false;
1.13 +SET client_min_messages = warning;
1.14 +SET escape_string_warning = off;
1.15 +
1.16 +--
1.17 +-- TOC entry 6 (class 2615 OID 16387)
1.18 +-- Name: posta; Type: SCHEMA; Schema: -; Owner: -
1.19 +--
1.20 +
1.21 +CREATE SCHEMA posta;
1.22 +
1.23 +
1.24 +SET search_path = posta, pg_catalog;
1.25 +
1.26 +--
1.27 +-- TOC entry 22 (class 1255 OID 16646)
1.28 +-- Dependencies: 6
1.29 +-- Name: zmen_heslo(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: posta; Owner: -
1.30 +--
1.31 +
1.32 +CREATE FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) RETURNS void
1.33 + AS $_$UPDATE uzivatel
1.34 + SET heslo = md5($4)
1.35 + WHERE login = $1
1.36 + AND domena = $2
1.37 + AND heslo = md5($3);$_$
1.38 + LANGUAGE sql SECURITY DEFINER;
1.39 +
1.40 +
1.41 +--
1.42 +-- TOC entry 1801 (class 0 OID 0)
1.43 +-- Dependencies: 22
1.44 +-- Name: FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying); Type: COMMENT; Schema: posta; Owner: -
1.45 +--
1.46 +
1.47 +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.';
1.48 +
1.49 +
1.50 +SET default_tablespace = '';
1.51 +
1.52 +SET default_with_oids = false;
1.53 +
1.54 +--
1.55 +-- TOC entry 1515 (class 1259 OID 16550)
1.56 +-- Dependencies: 6
1.57 +-- Name: alias; Type: TABLE; Schema: posta; Owner: -; Tablespace:
1.58 +--
1.59 +
1.60 +CREATE TABLE alias (
1.61 + login character varying(255) NOT NULL,
1.62 + domena character varying(255) NOT NULL,
1.63 + cilova_adresa character varying(255) NOT NULL
1.64 +);
1.65 +
1.66 +
1.67 +--
1.68 +-- TOC entry 1502 (class 1259 OID 16396)
1.69 +-- Dependencies: 6
1.70 +-- Name: domena; Type: TABLE; Schema: posta; Owner: -; Tablespace:
1.71 +--
1.72 +
1.73 +CREATE TABLE domena (
1.74 + domena character varying(255) NOT NULL
1.75 +);
1.76 +
1.77 +
1.78 +--
1.79 +-- TOC entry 1503 (class 1259 OID 16409)
1.80 +-- Dependencies: 6
1.81 +-- Name: uzivatel; Type: TABLE; Schema: posta; Owner: -; Tablespace:
1.82 +--
1.83 +
1.84 +CREATE TABLE uzivatel (
1.85 + login character varying(255) NOT NULL,
1.86 + heslo character varying(512),
1.87 + domena character varying(255) NOT NULL
1.88 +);
1.89 +
1.90 +
1.91 +--
1.92 +-- TOC entry 1517 (class 1259 OID 16602)
1.93 +-- Dependencies: 1596 6
1.94 +-- Name: dovecot_heslo; Type: VIEW; Schema: posta; Owner: -
1.95 +--
1.96 +
1.97 +CREATE VIEW dovecot_heslo AS
1.98 + SELECT (((uzivatel.login)::text || '@'::text) || (uzivatel.domena)::text) AS "user", uzivatel.heslo AS password, uzivatel.login, uzivatel.domena FROM uzivatel;
1.99 +
1.100 +
1.101 +--
1.102 +-- TOC entry 1519 (class 1259 OID 16631)
1.103 +-- Dependencies: 1598 6
1.104 +-- Name: dovecot_uzivatel; Type: VIEW; Schema: posta; Owner: -
1.105 +--
1.106 +
1.107 +CREATE VIEW dovecot_uzivatel AS
1.108 + 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;
1.109 +
1.110 +
1.111 +--
1.112 +-- TOC entry 1516 (class 1259 OID 16563)
1.113 +-- Dependencies: 1595 6
1.114 +-- Name: postfix_alias; Type: VIEW; Schema: posta; Owner: -
1.115 +--
1.116 +
1.117 +CREATE VIEW postfix_alias AS
1.118 + SELECT (((alias.login)::text || '@'::text) || (alias.domena)::text) AS zdroj, alias.cilova_adresa AS cil FROM alias;
1.119 +
1.120 +
1.121 +--
1.122 +-- TOC entry 1518 (class 1259 OID 16607)
1.123 +-- Dependencies: 1597 6
1.124 +-- Name: postfix_uzivatel; Type: VIEW; Schema: posta; Owner: -
1.125 +--
1.126 +
1.127 +CREATE VIEW postfix_uzivatel AS
1.128 + SELECT ((((uzivatel.domena)::text || '/'::text) || (uzivatel.login)::text) || '/Maildir/'::text) AS cesta, uzivatel.login, uzivatel.domena FROM uzivatel;
1.129 +
1.130 +
1.131 +--
1.132 +-- TOC entry 1795 (class 2606 OID 16557)
1.133 +-- Dependencies: 1515 1515 1515 1515
1.134 +-- Name: aliasy_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace:
1.135 +--
1.136 +
1.137 +ALTER TABLE ONLY alias
1.138 + ADD CONSTRAINT aliasy_pk PRIMARY KEY (login, domena, cilova_adresa);
1.139 +
1.140 +
1.141 +--
1.142 +-- TOC entry 1791 (class 2606 OID 16400)
1.143 +-- Dependencies: 1502 1502
1.144 +-- Name: domena_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace:
1.145 +--
1.146 +
1.147 +ALTER TABLE ONLY domena
1.148 + ADD CONSTRAINT domena_pk PRIMARY KEY (domena);
1.149 +
1.150 +
1.151 +--
1.152 +-- TOC entry 1793 (class 2606 OID 16416)
1.153 +-- Dependencies: 1503 1503 1503
1.154 +-- Name: uzivatel_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace:
1.155 +--
1.156 +
1.157 +ALTER TABLE ONLY uzivatel
1.158 + ADD CONSTRAINT uzivatel_pk PRIMARY KEY (login, domena);
1.159 +
1.160 +
1.161 +--
1.162 +-- TOC entry 1797 (class 2606 OID 16558)
1.163 +-- Dependencies: 1790 1515 1502
1.164 +-- Name: alias_domena_fk; Type: FK CONSTRAINT; Schema: posta; Owner: -
1.165 +--
1.166 +
1.167 +ALTER TABLE ONLY alias
1.168 + ADD CONSTRAINT alias_domena_fk FOREIGN KEY (domena) REFERENCES domena(domena);
1.169 +
1.170 +
1.171 +--
1.172 +-- TOC entry 1796 (class 2606 OID 16417)
1.173 +-- Dependencies: 1503 1502 1790
1.174 +-- Name: uzivatel_domena_fk; Type: FK CONSTRAINT; Schema: posta; Owner: -
1.175 +--
1.176 +
1.177 +ALTER TABLE ONLY uzivatel
1.178 + ADD CONSTRAINT uzivatel_domena_fk FOREIGN KEY (domena) REFERENCES domena(domena);
1.179 +
1.180 +
1.181 +--
1.182 +-- TOC entry 1800 (class 0 OID 0)
1.183 +-- Dependencies: 6
1.184 +-- Name: posta; Type: ACL; Schema: -; Owner: -
1.185 +--
1.186 +
1.187 +REVOKE ALL ON SCHEMA posta FROM PUBLIC;
1.188 +REVOKE ALL ON SCHEMA posta FROM posta;
1.189 +GRANT ALL ON SCHEMA posta TO posta;
1.190 +GRANT USAGE ON SCHEMA posta TO posta_cteni;
1.191 +
1.192 +
1.193 +--
1.194 +-- TOC entry 1802 (class 0 OID 0)
1.195 +-- Dependencies: 1515
1.196 +-- Name: alias; Type: ACL; Schema: posta; Owner: -
1.197 +--
1.198 +
1.199 +REVOKE ALL ON TABLE alias FROM PUBLIC;
1.200 +REVOKE ALL ON TABLE alias FROM posta;
1.201 +GRANT ALL ON TABLE alias TO posta;
1.202 +
1.203 +
1.204 +--
1.205 +-- TOC entry 1803 (class 0 OID 0)
1.206 +-- Dependencies: 1502
1.207 +-- Name: domena; Type: ACL; Schema: posta; Owner: -
1.208 +--
1.209 +
1.210 +REVOKE ALL ON TABLE domena FROM PUBLIC;
1.211 +REVOKE ALL ON TABLE domena FROM posta;
1.212 +GRANT ALL ON TABLE domena TO posta;
1.213 +GRANT SELECT ON TABLE domena TO posta_cteni;
1.214 +
1.215 +
1.216 +--
1.217 +-- TOC entry 1804 (class 0 OID 0)
1.218 +-- Dependencies: 1503
1.219 +-- Name: uzivatel; Type: ACL; Schema: posta; Owner: -
1.220 +--
1.221 +
1.222 +REVOKE ALL ON TABLE uzivatel FROM PUBLIC;
1.223 +REVOKE ALL ON TABLE uzivatel FROM posta;
1.224 +GRANT ALL ON TABLE uzivatel TO posta;
1.225 +GRANT SELECT ON TABLE uzivatel TO posta_cteni;
1.226 +
1.227 +
1.228 +--
1.229 +-- TOC entry 1805 (class 0 OID 0)
1.230 +-- Dependencies: 1517
1.231 +-- Name: dovecot_heslo; Type: ACL; Schema: posta; Owner: -
1.232 +--
1.233 +
1.234 +REVOKE ALL ON TABLE dovecot_heslo FROM PUBLIC;
1.235 +REVOKE ALL ON TABLE dovecot_heslo FROM posta;
1.236 +GRANT ALL ON TABLE dovecot_heslo TO posta;
1.237 +GRANT SELECT ON TABLE dovecot_heslo TO posta_cteni;
1.238 +
1.239 +
1.240 +--
1.241 +-- TOC entry 1806 (class 0 OID 0)
1.242 +-- Dependencies: 1519
1.243 +-- Name: dovecot_uzivatel; Type: ACL; Schema: posta; Owner: -
1.244 +--
1.245 +
1.246 +REVOKE ALL ON TABLE dovecot_uzivatel FROM PUBLIC;
1.247 +REVOKE ALL ON TABLE dovecot_uzivatel FROM posta;
1.248 +GRANT ALL ON TABLE dovecot_uzivatel TO posta;
1.249 +GRANT SELECT ON TABLE dovecot_uzivatel TO posta_cteni;
1.250 +
1.251 +
1.252 +--
1.253 +-- TOC entry 1807 (class 0 OID 0)
1.254 +-- Dependencies: 1516
1.255 +-- Name: postfix_alias; Type: ACL; Schema: posta; Owner: -
1.256 +--
1.257 +
1.258 +REVOKE ALL ON TABLE postfix_alias FROM PUBLIC;
1.259 +REVOKE ALL ON TABLE postfix_alias FROM posta;
1.260 +GRANT ALL ON TABLE postfix_alias TO posta;
1.261 +GRANT SELECT ON TABLE postfix_alias TO posta_cteni;
1.262 +
1.263 +
1.264 +--
1.265 +-- TOC entry 1808 (class 0 OID 0)
1.266 +-- Dependencies: 1518
1.267 +-- Name: postfix_uzivatel; Type: ACL; Schema: posta; Owner: -
1.268 +--
1.269 +
1.270 +REVOKE ALL ON TABLE postfix_uzivatel FROM PUBLIC;
1.271 +REVOKE ALL ON TABLE postfix_uzivatel FROM posta;
1.272 +GRANT ALL ON TABLE postfix_uzivatel TO posta;
1.273 +GRANT SELECT ON TABLE postfix_uzivatel TO posta_cteni;
1.274 +
1.275 +
1.276 +-- Completed on 2009-05-05 16:25:18 CEST
1.277 +
1.278 +--
1.279 +-- PostgreSQL database dump complete
1.280 +--
1.281 +