Annotation: policy.change_password

Information: policy.change_password

Implementation: policy.change_password

CREATE OR REPLACE FUNCTION policy.change_password(_admin_user_id integer, _user_name text, _new_password text)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
    DECLARE _user_id            integer;
    DECLARE _office_id          integer;
    DECLARE _admin_office_id    integer;
BEGIN
    IF(COALESCE($2, '') = '') THEN
        RAISE EXCEPTION 'Invalid user name.';
    END IF;

    IF(COALESCE($3, '') = '') THEN
        RAISE EXCEPTION 'Password cannot be empty.';
    END IF;

    SELECT 
        office.users.user_id,
        office.users.office_id
    INTO
        _user_id,
        _office_id
    FROM office.users
    WHERE office.users.user_name=_user_name;

    IF(COALESCE(_user_id, 0) = 0) THEN
        RAISE EXCEPTION 'Invalid user name.';
    END IF;

    IF(NOT office.is_admin(_admin_user_id)) THEN
        RAISE EXCEPTION 'Access is denied.';
    END IF;

    SELECT office.users.office_id INTO _admin_office_id
    FROM office.users
    WHERE office.users.user_id = _admin_user_id;

    IF(_admin_office_id != _office_id AND NOT office.is_parent_office(_admin_office_id, _office_id)) THEN
        RAISE EXCEPTION 'Access is denied.';
    END IF;

    UPDATE office.users
    SET password = encode(digest($2 || $3, 'sha512'), 'hex')
    WHERE office.users.user_name=$2;    
END
$function$