如何为EDB PostgreSQL创建满足复杂度要求的密码验证函数

Hey there! Let's build out that password validation function you started for EDB PostgreSQL to cover all your required checks. You already have a basic skeleton, so we'll extend it to enforce every rule you listed.

First, let's recap the validation rules we need to enforce:

• At least 1 uppercase letter (A-Z)
• At least 1 lowercase letter (a-z)
• At least 1 numeric digit (0-9)
• Minimum length of 8 characters
• At least 1 special character (we'll define this as any non-alphanumeric character, e.g., !@#$%^&*()_+-=[]{}|;:'",.<>?/)

Complete Function Code #

Here's the full, working function that implements all these checks. Note that since you're using EDB PostgreSQL, we can leverage its PL/SQL compatibility (hence the varchar2 type and raise_application_error calls):

CREATE OR REPLACE FUNCTION verify_password(
    user_name varchar2,
    new_password varchar2,
    old_password varchar2
) RETURN boolean IMMUTABLE IS
BEGIN
    -- Check minimum length (8 characters)
    IF length(new_password) < 8 THEN
        raise_application_error(-20001, 'Password must be at least 8 characters long.');
    END IF;

    -- Check for at least one uppercase letter
    IF substring(new_password FROM '[A-Z]') IS NULL THEN
        raise_application_error(-20002, 'Password must contain at least one uppercase letter (A-Z).');
    END IF;

    -- Check for at least one lowercase letter
    IF substring(new_password FROM '[a-z]') IS NULL THEN
        raise_application_error(-20003, 'Password must contain at least one lowercase letter (a-z).');
    END IF;

    -- Check for at least one numeric digit
    IF substring(new_password FROM '[0-9]') IS NULL THEN
        raise_application_error(-20004, 'Password must contain at least one numeric digit (0-9).');
    END IF;

    -- Check for at least one special character (non-alphanumeric)
    IF substring(new_password FROM '[^A-Za-z0-9]') IS NULL THEN
        raise_application_error(-20005, 'Password must contain at least one special character (e.g., !@#$%^&*()).');
    END IF;

    -- Optional: Check that new password isn't the same as old password
    IF new_password = old_password THEN
        raise_application_error(-20006, 'New password cannot be identical to the old password.');
    END IF;

    -- If all checks pass, return true
    RETURN true;
EXCEPTION
    WHEN OTHERS THEN
        -- Re-raise the error with the custom message
        RAISE;
END;
/

Breakdown of Each Check #

Let's walk through what each part does:

• Length Check: Replaces your original 5-character minimum with the required 8-character threshold.
• Uppercase Check: Uses a regex pattern [A-Z] to look for any uppercase letter; if no match is found, it throws an error.
• Lowercase Check: Similar regex [a-z] ensures at least one lowercase letter is present.
• Digit Check: Regex [0-9] verifies there's at least one number in the password.
• Special Character Check: The regex [^A-Za-z0-9] matches any character that isn't a letter or number (i.e., a special character).
• Optional Old Password Check: Added this as a common best practice to prevent users from reusing their old password—you can remove this if it's not needed for your use case.

How to Use This Function #

You can call this function directly when creating or updating users, or attach it to a trigger for automatic validation. For example:

-- Example: Using the function when creating a user
CREATE USER test_user WITH PASSWORD 'Pass123!' VALIDATE WITH verify_password;

-- Example: Altering a user's password with validation
ALTER USER test_user WITH PASSWORD 'NewPass456!' VALIDATE WITH verify_password;

Test Cases #

Let's verify some scenarios:

• Valid password: MyPass123! → Passes all checks
• Invalid (too short): Pass1! → Fails length check
• Invalid (no uppercase): mypass123! → Fails uppercase check
• Invalid (no special character): MyPass123 → Fails special character check

内容的提问来源于stack exchange，提问作者SQLDBA