1.1 --- a/sql/schéma.sql Tue May 05 16:27:32 2009 +0200
1.2 +++ b/sql/schéma.sql Wed May 06 13:12:03 2009 +0200
1.3 @@ -2,7 +2,7 @@
1.4 -- PostgreSQL database dump
1.5 --
1.6
1.7 --- Started on 2009-05-05 16:25:08 CEST
1.8 +-- Started on 2009-05-06 02:13:29 CEST
1.9
1.10 SET client_encoding = 'UTF8';
1.11 SET standard_conforming_strings = off;
1.12 @@ -11,7 +11,7 @@
1.13 SET escape_string_warning = off;
1.14
1.15 --
1.16 --- TOC entry 6 (class 2615 OID 16387)
1.17 +-- TOC entry 8 (class 2615 OID 16387)
1.18 -- Name: posta; Type: SCHEMA; Schema: -; Owner: -
1.19 --
1.20
1.21 @@ -21,23 +21,33 @@
1.22 SET search_path = posta, pg_catalog;
1.23
1.24 --
1.25 --- TOC entry 22 (class 1255 OID 16646)
1.26 --- Dependencies: 6
1.27 +-- TOC entry 55 (class 1255 OID 16694)
1.28 +-- Dependencies: 364 8
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 +CREATE FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) RETURNS text
1.35 + AS $_$DECLARE pocet integer;
1.36 +BEGIN
1.37 +UPDATE uzivatel
1.38 SET heslo = md5($4)
1.39 WHERE login = $1
1.40 AND domena = $2
1.41 - AND heslo = md5($3);$_$
1.42 - LANGUAGE sql SECURITY DEFINER;
1.43 + AND heslo = md5($3);
1.44 + GET DIAGNOSTICS pocet = ROW_COUNT;
1.45 + IF pocet = 1 THEN
1.46 + RETURN 'Změna hesla pro ' || $1 || '@' || $2 || ' proběhla úspěšně.';
1.47 + ELSE
1.48 + RAISE EXCEPTION 'Nesprávné heslo, nebo uživatel % neexistuje', $1 || '@' || $2;
1.49 + END IF;
1.50 +END;
1.51 + $_$
1.52 + LANGUAGE plpgsql SECURITY DEFINER;
1.53
1.54
1.55 --
1.56 --- TOC entry 1801 (class 0 OID 0)
1.57 --- Dependencies: 22
1.58 +-- TOC entry 1835 (class 0 OID 0)
1.59 +-- Dependencies: 55
1.60 -- Name: FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying); Type: COMMENT; Schema: posta; Owner: -
1.61 --
1.62
1.63 @@ -49,8 +59,8 @@
1.64 SET default_with_oids = false;
1.65
1.66 --
1.67 --- TOC entry 1515 (class 1259 OID 16550)
1.68 --- Dependencies: 6
1.69 +-- TOC entry 1549 (class 1259 OID 16550)
1.70 +-- Dependencies: 8
1.71 -- Name: alias; Type: TABLE; Schema: posta; Owner: -; Tablespace:
1.72 --
1.73
1.74 @@ -62,8 +72,8 @@
1.75
1.76
1.77 --
1.78 --- TOC entry 1502 (class 1259 OID 16396)
1.79 --- Dependencies: 6
1.80 +-- TOC entry 1536 (class 1259 OID 16396)
1.81 +-- Dependencies: 8
1.82 -- Name: domena; Type: TABLE; Schema: posta; Owner: -; Tablespace:
1.83 --
1.84
1.85 @@ -73,8 +83,8 @@
1.86
1.87
1.88 --
1.89 --- TOC entry 1503 (class 1259 OID 16409)
1.90 --- Dependencies: 6
1.91 +-- TOC entry 1537 (class 1259 OID 16409)
1.92 +-- Dependencies: 8
1.93 -- Name: uzivatel; Type: TABLE; Schema: posta; Owner: -; Tablespace:
1.94 --
1.95
1.96 @@ -86,8 +96,8 @@
1.97
1.98
1.99 --
1.100 --- TOC entry 1517 (class 1259 OID 16602)
1.101 --- Dependencies: 1596 6
1.102 +-- TOC entry 1551 (class 1259 OID 16602)
1.103 +-- Dependencies: 1630 8
1.104 -- Name: dovecot_heslo; Type: VIEW; Schema: posta; Owner: -
1.105 --
1.106
1.107 @@ -96,8 +106,8 @@
1.108
1.109
1.110 --
1.111 --- TOC entry 1519 (class 1259 OID 16631)
1.112 --- Dependencies: 1598 6
1.113 +-- TOC entry 1553 (class 1259 OID 16631)
1.114 +-- Dependencies: 1632 8
1.115 -- Name: dovecot_uzivatel; Type: VIEW; Schema: posta; Owner: -
1.116 --
1.117
1.118 @@ -106,8 +116,8 @@
1.119
1.120
1.121 --
1.122 --- TOC entry 1516 (class 1259 OID 16563)
1.123 --- Dependencies: 1595 6
1.124 +-- TOC entry 1550 (class 1259 OID 16563)
1.125 +-- Dependencies: 1629 8
1.126 -- Name: postfix_alias; Type: VIEW; Schema: posta; Owner: -
1.127 --
1.128
1.129 @@ -116,8 +126,8 @@
1.130
1.131
1.132 --
1.133 --- TOC entry 1518 (class 1259 OID 16607)
1.134 --- Dependencies: 1597 6
1.135 +-- TOC entry 1552 (class 1259 OID 16607)
1.136 +-- Dependencies: 1631 8
1.137 -- Name: postfix_uzivatel; Type: VIEW; Schema: posta; Owner: -
1.138 --
1.139
1.140 @@ -126,8 +136,8 @@
1.141
1.142
1.143 --
1.144 --- TOC entry 1795 (class 2606 OID 16557)
1.145 --- Dependencies: 1515 1515 1515 1515
1.146 +-- TOC entry 1829 (class 2606 OID 16557)
1.147 +-- Dependencies: 1549 1549 1549 1549
1.148 -- Name: aliasy_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace:
1.149 --
1.150
1.151 @@ -136,8 +146,8 @@
1.152
1.153
1.154 --
1.155 --- TOC entry 1791 (class 2606 OID 16400)
1.156 --- Dependencies: 1502 1502
1.157 +-- TOC entry 1825 (class 2606 OID 16400)
1.158 +-- Dependencies: 1536 1536
1.159 -- Name: domena_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace:
1.160 --
1.161
1.162 @@ -146,8 +156,8 @@
1.163
1.164
1.165 --
1.166 --- TOC entry 1793 (class 2606 OID 16416)
1.167 --- Dependencies: 1503 1503 1503
1.168 +-- TOC entry 1827 (class 2606 OID 16416)
1.169 +-- Dependencies: 1537 1537 1537
1.170 -- Name: uzivatel_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace:
1.171 --
1.172
1.173 @@ -156,8 +166,8 @@
1.174
1.175
1.176 --
1.177 --- TOC entry 1797 (class 2606 OID 16558)
1.178 --- Dependencies: 1790 1515 1502
1.179 +-- TOC entry 1831 (class 2606 OID 16558)
1.180 +-- Dependencies: 1824 1536 1549
1.181 -- Name: alias_domena_fk; Type: FK CONSTRAINT; Schema: posta; Owner: -
1.182 --
1.183
1.184 @@ -166,8 +176,8 @@
1.185
1.186
1.187 --
1.188 --- TOC entry 1796 (class 2606 OID 16417)
1.189 --- Dependencies: 1503 1502 1790
1.190 +-- TOC entry 1830 (class 2606 OID 16417)
1.191 +-- Dependencies: 1537 1536 1824
1.192 -- Name: uzivatel_domena_fk; Type: FK CONSTRAINT; Schema: posta; Owner: -
1.193 --
1.194
1.195 @@ -176,8 +186,8 @@
1.196
1.197
1.198 --
1.199 --- TOC entry 1800 (class 0 OID 0)
1.200 --- Dependencies: 6
1.201 +-- TOC entry 1834 (class 0 OID 0)
1.202 +-- Dependencies: 8
1.203 -- Name: posta; Type: ACL; Schema: -; Owner: -
1.204 --
1.205
1.206 @@ -185,11 +195,25 @@
1.207 REVOKE ALL ON SCHEMA posta FROM posta;
1.208 GRANT ALL ON SCHEMA posta TO posta;
1.209 GRANT USAGE ON SCHEMA posta TO posta_cteni;
1.210 +GRANT USAGE ON SCHEMA posta TO posta_zmena_hesla;
1.211
1.212
1.213 --
1.214 --- TOC entry 1802 (class 0 OID 0)
1.215 --- Dependencies: 1515
1.216 +-- TOC entry 1836 (class 0 OID 0)
1.217 +-- Dependencies: 55
1.218 +-- Name: zmen_heslo(character varying, character varying, character varying, character varying); Type: ACL; Schema: posta; Owner: -
1.219 +--
1.220 +
1.221 +REVOKE ALL ON FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) FROM PUBLIC;
1.222 +REVOKE ALL ON FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) FROM posta;
1.223 +GRANT ALL ON FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) TO posta;
1.224 +GRANT ALL ON FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) TO PUBLIC;
1.225 +GRANT ALL ON FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) TO posta_zmena_hesla;
1.226 +
1.227 +
1.228 +--
1.229 +-- TOC entry 1837 (class 0 OID 0)
1.230 +-- Dependencies: 1549
1.231 -- Name: alias; Type: ACL; Schema: posta; Owner: -
1.232 --
1.233
1.234 @@ -199,8 +223,8 @@
1.235
1.236
1.237 --
1.238 --- TOC entry 1803 (class 0 OID 0)
1.239 --- Dependencies: 1502
1.240 +-- TOC entry 1838 (class 0 OID 0)
1.241 +-- Dependencies: 1536
1.242 -- Name: domena; Type: ACL; Schema: posta; Owner: -
1.243 --
1.244
1.245 @@ -211,8 +235,8 @@
1.246
1.247
1.248 --
1.249 --- TOC entry 1804 (class 0 OID 0)
1.250 --- Dependencies: 1503
1.251 +-- TOC entry 1839 (class 0 OID 0)
1.252 +-- Dependencies: 1537
1.253 -- Name: uzivatel; Type: ACL; Schema: posta; Owner: -
1.254 --
1.255
1.256 @@ -223,8 +247,8 @@
1.257
1.258
1.259 --
1.260 --- TOC entry 1805 (class 0 OID 0)
1.261 --- Dependencies: 1517
1.262 +-- TOC entry 1840 (class 0 OID 0)
1.263 +-- Dependencies: 1551
1.264 -- Name: dovecot_heslo; Type: ACL; Schema: posta; Owner: -
1.265 --
1.266
1.267 @@ -235,8 +259,8 @@
1.268
1.269
1.270 --
1.271 --- TOC entry 1806 (class 0 OID 0)
1.272 --- Dependencies: 1519
1.273 +-- TOC entry 1841 (class 0 OID 0)
1.274 +-- Dependencies: 1553
1.275 -- Name: dovecot_uzivatel; Type: ACL; Schema: posta; Owner: -
1.276 --
1.277
1.278 @@ -247,8 +271,8 @@
1.279
1.280
1.281 --
1.282 --- TOC entry 1807 (class 0 OID 0)
1.283 --- Dependencies: 1516
1.284 +-- TOC entry 1842 (class 0 OID 0)
1.285 +-- Dependencies: 1550
1.286 -- Name: postfix_alias; Type: ACL; Schema: posta; Owner: -
1.287 --
1.288
1.289 @@ -259,8 +283,8 @@
1.290
1.291
1.292 --
1.293 --- TOC entry 1808 (class 0 OID 0)
1.294 --- Dependencies: 1518
1.295 +-- TOC entry 1843 (class 0 OID 0)
1.296 +-- Dependencies: 1552
1.297 -- Name: postfix_uzivatel; Type: ACL; Schema: posta; Owner: -
1.298 --
1.299
1.300 @@ -270,7 +294,7 @@
1.301 GRANT SELECT ON TABLE postfix_uzivatel TO posta_cteni;
1.302
1.303
1.304 --- Completed on 2009-05-05 16:25:18 CEST
1.305 +-- Completed on 2009-05-06 02:13:36 CEST
1.306
1.307 --
1.308 -- PostgreSQL database dump complete