Schéma databáze – obsahuje seznamy domén, aliasů a uživatelů
authorFrantišek Kučera <franta-hg@frantovo.cz>
Tue, 05 May 2009 16:27:32 +0200
changeset 0c83d559ca362
child 1 001f2734b554
Schéma databáze – obsahuje seznamy domén, aliasů a uživatelů
sql/schéma.sql
     1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/sql/schéma.sql	Tue May 05 16:27:32 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 +