diff -r c83d559ca362 -r 38aadc7c6386 sql/schéma.sql --- a/sql/schéma.sql Tue May 05 16:27:32 2009 +0200 +++ b/sql/schéma.sql Wed May 06 13:12:03 2009 +0200 @@ -2,7 +2,7 @@ -- PostgreSQL database dump -- --- Started on 2009-05-05 16:25:08 CEST +-- Started on 2009-05-06 02:13:29 CEST SET client_encoding = 'UTF8'; SET standard_conforming_strings = off; @@ -11,7 +11,7 @@ SET escape_string_warning = off; -- --- TOC entry 6 (class 2615 OID 16387) +-- TOC entry 8 (class 2615 OID 16387) -- Name: posta; Type: SCHEMA; Schema: -; Owner: - -- @@ -21,23 +21,33 @@ SET search_path = posta, pg_catalog; -- --- TOC entry 22 (class 1255 OID 16646) --- Dependencies: 6 +-- TOC entry 55 (class 1255 OID 16694) +-- Dependencies: 364 8 -- 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 +CREATE FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) RETURNS text + AS $_$DECLARE pocet integer; +BEGIN +UPDATE uzivatel SET heslo = md5($4) WHERE login = $1 AND domena = $2 - AND heslo = md5($3);$_$ - LANGUAGE sql SECURITY DEFINER; + AND heslo = md5($3); + GET DIAGNOSTICS pocet = ROW_COUNT; + IF pocet = 1 THEN + RETURN 'Změna hesla pro ' || $1 || '@' || $2 || ' proběhla úspěšně.'; + ELSE + RAISE EXCEPTION 'Nesprávné heslo, nebo uživatel % neexistuje', $1 || '@' || $2; + END IF; +END; + $_$ + LANGUAGE plpgsql SECURITY DEFINER; -- --- TOC entry 1801 (class 0 OID 0) --- Dependencies: 22 +-- TOC entry 1835 (class 0 OID 0) +-- Dependencies: 55 -- Name: FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying); Type: COMMENT; Schema: posta; Owner: - -- @@ -49,8 +59,8 @@ SET default_with_oids = false; -- --- TOC entry 1515 (class 1259 OID 16550) --- Dependencies: 6 +-- TOC entry 1549 (class 1259 OID 16550) +-- Dependencies: 8 -- Name: alias; Type: TABLE; Schema: posta; Owner: -; Tablespace: -- @@ -62,8 +72,8 @@ -- --- TOC entry 1502 (class 1259 OID 16396) --- Dependencies: 6 +-- TOC entry 1536 (class 1259 OID 16396) +-- Dependencies: 8 -- Name: domena; Type: TABLE; Schema: posta; Owner: -; Tablespace: -- @@ -73,8 +83,8 @@ -- --- TOC entry 1503 (class 1259 OID 16409) --- Dependencies: 6 +-- TOC entry 1537 (class 1259 OID 16409) +-- Dependencies: 8 -- Name: uzivatel; Type: TABLE; Schema: posta; Owner: -; Tablespace: -- @@ -86,8 +96,8 @@ -- --- TOC entry 1517 (class 1259 OID 16602) --- Dependencies: 1596 6 +-- TOC entry 1551 (class 1259 OID 16602) +-- Dependencies: 1630 8 -- Name: dovecot_heslo; Type: VIEW; Schema: posta; Owner: - -- @@ -96,8 +106,8 @@ -- --- TOC entry 1519 (class 1259 OID 16631) --- Dependencies: 1598 6 +-- TOC entry 1553 (class 1259 OID 16631) +-- Dependencies: 1632 8 -- Name: dovecot_uzivatel; Type: VIEW; Schema: posta; Owner: - -- @@ -106,8 +116,8 @@ -- --- TOC entry 1516 (class 1259 OID 16563) --- Dependencies: 1595 6 +-- TOC entry 1550 (class 1259 OID 16563) +-- Dependencies: 1629 8 -- Name: postfix_alias; Type: VIEW; Schema: posta; Owner: - -- @@ -116,8 +126,8 @@ -- --- TOC entry 1518 (class 1259 OID 16607) --- Dependencies: 1597 6 +-- TOC entry 1552 (class 1259 OID 16607) +-- Dependencies: 1631 8 -- Name: postfix_uzivatel; Type: VIEW; Schema: posta; Owner: - -- @@ -126,8 +136,8 @@ -- --- TOC entry 1795 (class 2606 OID 16557) --- Dependencies: 1515 1515 1515 1515 +-- TOC entry 1829 (class 2606 OID 16557) +-- Dependencies: 1549 1549 1549 1549 -- Name: aliasy_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace: -- @@ -136,8 +146,8 @@ -- --- TOC entry 1791 (class 2606 OID 16400) --- Dependencies: 1502 1502 +-- TOC entry 1825 (class 2606 OID 16400) +-- Dependencies: 1536 1536 -- Name: domena_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace: -- @@ -146,8 +156,8 @@ -- --- TOC entry 1793 (class 2606 OID 16416) --- Dependencies: 1503 1503 1503 +-- TOC entry 1827 (class 2606 OID 16416) +-- Dependencies: 1537 1537 1537 -- Name: uzivatel_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace: -- @@ -156,8 +166,8 @@ -- --- TOC entry 1797 (class 2606 OID 16558) --- Dependencies: 1790 1515 1502 +-- TOC entry 1831 (class 2606 OID 16558) +-- Dependencies: 1824 1536 1549 -- Name: alias_domena_fk; Type: FK CONSTRAINT; Schema: posta; Owner: - -- @@ -166,8 +176,8 @@ -- --- TOC entry 1796 (class 2606 OID 16417) --- Dependencies: 1503 1502 1790 +-- TOC entry 1830 (class 2606 OID 16417) +-- Dependencies: 1537 1536 1824 -- Name: uzivatel_domena_fk; Type: FK CONSTRAINT; Schema: posta; Owner: - -- @@ -176,8 +186,8 @@ -- --- TOC entry 1800 (class 0 OID 0) --- Dependencies: 6 +-- TOC entry 1834 (class 0 OID 0) +-- Dependencies: 8 -- Name: posta; Type: ACL; Schema: -; Owner: - -- @@ -185,11 +195,25 @@ REVOKE ALL ON SCHEMA posta FROM posta; GRANT ALL ON SCHEMA posta TO posta; GRANT USAGE ON SCHEMA posta TO posta_cteni; +GRANT USAGE ON SCHEMA posta TO posta_zmena_hesla; -- --- TOC entry 1802 (class 0 OID 0) --- Dependencies: 1515 +-- TOC entry 1836 (class 0 OID 0) +-- Dependencies: 55 +-- Name: zmen_heslo(character varying, character varying, character varying, character varying); Type: ACL; Schema: posta; Owner: - +-- + +REVOKE ALL ON FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) FROM PUBLIC; +REVOKE ALL ON FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) FROM posta; +GRANT ALL ON FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) TO posta; +GRANT ALL ON FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) TO PUBLIC; +GRANT ALL ON FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) TO posta_zmena_hesla; + + +-- +-- TOC entry 1837 (class 0 OID 0) +-- Dependencies: 1549 -- Name: alias; Type: ACL; Schema: posta; Owner: - -- @@ -199,8 +223,8 @@ -- --- TOC entry 1803 (class 0 OID 0) --- Dependencies: 1502 +-- TOC entry 1838 (class 0 OID 0) +-- Dependencies: 1536 -- Name: domena; Type: ACL; Schema: posta; Owner: - -- @@ -211,8 +235,8 @@ -- --- TOC entry 1804 (class 0 OID 0) --- Dependencies: 1503 +-- TOC entry 1839 (class 0 OID 0) +-- Dependencies: 1537 -- Name: uzivatel; Type: ACL; Schema: posta; Owner: - -- @@ -223,8 +247,8 @@ -- --- TOC entry 1805 (class 0 OID 0) --- Dependencies: 1517 +-- TOC entry 1840 (class 0 OID 0) +-- Dependencies: 1551 -- Name: dovecot_heslo; Type: ACL; Schema: posta; Owner: - -- @@ -235,8 +259,8 @@ -- --- TOC entry 1806 (class 0 OID 0) --- Dependencies: 1519 +-- TOC entry 1841 (class 0 OID 0) +-- Dependencies: 1553 -- Name: dovecot_uzivatel; Type: ACL; Schema: posta; Owner: - -- @@ -247,8 +271,8 @@ -- --- TOC entry 1807 (class 0 OID 0) --- Dependencies: 1516 +-- TOC entry 1842 (class 0 OID 0) +-- Dependencies: 1550 -- Name: postfix_alias; Type: ACL; Schema: posta; Owner: - -- @@ -259,8 +283,8 @@ -- --- TOC entry 1808 (class 0 OID 0) --- Dependencies: 1518 +-- TOC entry 1843 (class 0 OID 0) +-- Dependencies: 1552 -- Name: postfix_uzivatel; Type: ACL; Schema: posta; Owner: - -- @@ -270,7 +294,7 @@ GRANT SELECT ON TABLE postfix_uzivatel TO posta_cteni; --- Completed on 2009-05-05 16:25:18 CEST +-- Completed on 2009-05-06 02:13:36 CEST -- -- PostgreSQL database dump complete