sql/schéma.sql
changeset 13 119744ae4179
parent 0 c83d559ca362
child 14 c830b05699ba
     1.1 --- a/sql/schéma.sql	Tue May 05 16:27:32 2009 +0200
     1.2 +++ b/sql/schéma.sql	Sun May 10 01:13:12 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