pgSQL - aktualizacja has

Post autor: **LordRuthwen** » 12 października 2011, 12:41

Witam.
Mam polecenie MySQL:

SELECT update_passwd(%c, %u)

które aktualizuje hasło %c użytkownikowi %u
Jak je przerobić, aby następowała aktualizacja następujących kolumn tabeli mailbox w PostGreSQL:

Kod: Zaznacz cały

SELECT "username" FROM "public"."mailbox"
SELECT "password" FROM "public"."mailbox"

Kwestia jest dostosowania wtyczki "password" do RoundCubeMail, domyślnie jest to na MySQL a u mnie poczta chodzi na postgresie

lessmian2 · Post autor: **lessmian2** » 12 października 2011, 19:53

update_passwd to raczej jakaś funkcja zdefiniowana przez tą wtyczkę, więc nie pozostaje nic innego jak sprawdzić ciało tej funkcji (show create update_passwd czy jakoś tak) i odpowiednio zmodyfikować pod postgresa.

Bastian · Post autor: **Bastian** » 12 października 2011, 21:37

Mniejwięcej coś takiego:

Kod: Zaznacz cały

CREATE FUNCTION update_passwd(varchar40, varchar40) RETURNS integer AS '

DECLARE 
 myuser ALIAS FOR $1;
nowypass ALIAS FOR $2;
update_re integer;
tenuser varchar;

BEGIN 
SELECT username INTO tenuser FROM public.mailbox WHERE username= myuser;
IF NOT FOUND THEN
update_re:=0;
RETURN update_re
END IF;
UPDATE public.mailbox SET password=nowypass WHERE user=tenuser;
update_re:=1;
RETURN update_re;
END; language 'plpgsql';

Pisze z pamięci więc trzeba zweryfikować ale mniejwiecej chyba o taka procedure przechowywaną Tobie chodziło

Post autor: **LordRuthwen** » 19 października 2011, 14:19

Dziękuję za odzew, sprawdzę to dam znać, z baz danych to ja cienki jestem

Dodane:

No cóż, nie do końca działa, nie stworzy funkcji.
Strukturę tabeli public.vmail.mailbox mam taką:

Kod: Zaznacz cały

username(varchar)[username jest pełnym mailem], password(varchar), name, maildir, itd

interesuje mnie stworzenie funkcji, która aktualizuje hash MD5 pola password.
Znalazłem coś takiego:

Kod: Zaznacz cały

 CREATE OR REPLACE FUNCTION update_passwd(hash text, account text) RETURNS integer AS $$
        DECLARE
            res integer;
        BEGIN
            UPDATE passwd SET password = hash
            WHERE login = split_part(account, '@', 1)
                AND domainid = (SELECT id FROM domains WHERE name = split_part(account, '@', 2))
            RETURNING id INTO res;
            RETURN res;
        END;
        $$ LANGUAGE plpgsql SECURITY DEFINER;

Kod: Zaznacz cały

        CREATE FUNCTION update_password (oldpass text, cryptpass text, user text) RETURNS text
            MODIFIES SQL DATA
        BEGIN
            DECLARE currentsalt varchar(20);
            DECLARE error text;
            SET error = 'incorrect current password';
            SELECT substring_index(substr(user.password,4),_latin1'$',1) INTO currentsalt FROM users WHERE username=user;
            SELECT '' INTO error FROM users WHERE username=user AND password=ENCRYPT(oldpass,currentsalt);
            UPDATE users SET password=cryptpass WHERE username=user AND password=ENCRYPT(oldpass,currentsalt);
            RETURN error;
        END

ale też nie bardzo chcą działać.
Log postgresa po wykonaniu zapytania dla pierwszej funkcji:

Kod: Zaznacz cały

2011-10-19 14:01:08 CEST ERROR:  relation "passwd" does not exist at character 8
2011-10-19 14:01:08 CEST QUERY:  UPDATE passwd SET password =  $1  WHERE login = split_part( $2 , '@', 1) AND domainid = (SELECT id FROM domains WHERE name = split_part( $2 , '@', 2)) RETURNING id
2011-10-19 14:01:08 CEST CONTEXT:  PL/pgSQL function "update_passwd" line 4 at SQL statement
2011-10-19 14:01:08 CEST STATEMENT:  SELECT update_passwd('$HASH.', 'mail@domena.pl')

Próba stworzenia drugiej z tych funkcji, którą znalazłem:

Kod: Zaznacz cały

2011-10-19 13:57:32 CEST ERROR:  syntax error at or near "user" at character 64
2011-10-19 13:57:32 CEST STATEMENT:  CREATE FUNCTION update_password (oldpass text, cryptpass text, user text) RETURNS text
                    MODIFIES SQL DATA
                BEGIN
                    DECLARE currentsalt varchar(20);
                    DECLARE error text;
                    SET error = 'incorrect current password';
                    SELECT substring_index(substr(user.password,4),_latin1'$',1) INTO currentsalt FROM users WHERE username=user;
                    SELECT '' INTO error FROM users WHERE username=user AND password=ENCRYPT(oldpass,currentsalt);
                    UPDATE users SET password=cryptpass WHERE username=user AND password=ENCRYPT(oldpass,currentsalt);
                    RETURN error;
                END

Bastian · Post autor: **Bastian** » 19 października 2011, 18:33

Doprecyzujmy, czy używasz w ogóle soli w hasłach? Bo te przykłady, które przedstawiasz nie robią zawsze tego samego.

Post autor: **LordRuthwen** » 20 października 2011, 08:41

Domyślam się, że nie robią tego samego, szczerze mówiąc nie wiem jak przechowuje zapisane hasła postfixadmin, bo do jego bazy się Roundcube i postfix odnosi.

Bastian · Post autor: **Bastian** » 20 października 2011, 13:51

Jeśli to po prostu szyfrowanie MD5 to może tak:

Kod: Zaznacz cały

CREATE FUNCTION update_passwd(varchar40, varchar40) RETURNS integer AS '

DECLARE 
 myuser ALIAS FOR $1;
nowypass ALIAS FOR $2;
update_re integer;
tenuser varchar;

BEGIN 
SELECT username INTO tenuser FROM public.mailbox WHERE username= myuser;
IF NOT FOUND THEN
update_re:=0;
RETURN update_re
END IF;
UPDATE public.mailbox SET password=md5(nowypass) WHERE user=tenuser;
update_re:=1;
RETURN update_re;
END; language 'plpgsql';

Tu nie ma specjalnie co analizować.

Wywołanie:

Kod: Zaznacz cały


SELECT update_passwd("janek", "mojenowehaslo")

Powinno zwrócić 1 jako poprawnie wykonana operacje zmiany hasła.

Post autor: **LordRuthwen** » 20 października 2011, 15:18

Kod: Zaznacz cały

[B]Błąd SQL:[/B]


ERROR:  syntax error at or near "plpgsql" LINE 18: END; language 'plpgsql';                         ^ 		      [B]W poleceniu:[/B]


CREATE FUNCTION update_passwd(varchar40, varchar40) RETURNS integer AS '  DECLARE   myuser ALIAS FOR $1; nowypass ALIAS FOR $2; update_re integer; tenuser varchar;  BEGIN  SELECT username INTO tenuser FROM public.mailbox WHERE username= myuser; IF NOT FOUND THEN update_re:=0; RETURN update_re END IF; UPDATE public.mailbox SET password=md5(nowypass) WHERE user=tenuser; update_re:=1; RETURN update_re; END; language 'plpgsql';

Bastian · Post autor: **Bastian** » 20 października 2011, 21:44

Kod: Zaznacz cały


CREATE FUNCTION update_passwd(varchar40, varchar40) RETURNS integer AS '

DECLARE 
 myuser ALIAS FOR $1;
nowypass ALIAS FOR $2;
update_re integer;
tenuser varchar;

BEGIN 
SELECT username INTO tenuser FROM public.mailbox WHERE username= myuser;
IF NOT FOUND THEN
update_re:=0;
RETURN update_re
END IF;
UPDATE public.mailbox SET password=md5(nowypass) WHERE user=tenuser;
update_re:=1;
RETURN update_re;
END;[B]'[/B] language 'plpgsql';

Post autor: **LordRuthwen** » 21 października 2011, 15:33

Wybacz, chwilę Cię pomęczę.

Kod: Zaznacz cały

[B]Błąd SQL:[/B]
ERROR:  syntax error at or near "END" 
LINE 1: SELECT   $1  END IF
            ^ 
QUERY:  SELECT   $1  END IF 
CONTEXT:  SQL statement in PL/PgSQL function "update_passwd" near line 12
[B]W poleceniu:[/B]
CREATE FUNCTION update_passwd(varchar(40), varchar(40)) RETURNS integer AS '  
DECLARE   
myuser ALIAS FOR $1; 
nowypass ALIAS FOR $2; 
update_re integer; 
tenuser varchar;  
BEGIN  
SELECT username INTO tenuser FROM public.mailbox WHERE username= myuser; 
IF NOT FOUND THEN 
update_re:=0; 
RETURN update_re 
END IF; 
UPDATE public.mailbox SET password=md5(nowypass) WHERE user=tenuser; 
update_re:=1; 
RETURN update_re; 
END;' language 'plpgsql';

pgSQL - aktualizacja has

pgSQL - aktualizacja hasła w tabeli