求Python实现Postfix MySQL格式的用户密码加密方案

I see you're setting up a Postfix virtual mail server and want to let users generate their own hashed passwords via Python instead of relying on MySQL's built-in ENCRYPT function. Let's walk through exactly how to replicate that MySQL password hashing logic and handle the user insertion in Python.

First, Let's Break Down the Original MySQL Logic #

Your existing MySQL insert uses this for password hashing:

ENCRYPT('firstpassword', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16)))

This creates a SHA-512 password hash (the $6$ prefix denotes SHA-512) with a random salt made from the last 16 characters of a SHA-1 hash of a random value. We'll replicate this exact logic in Python.

Step 1: Generate the Postfix-Compatible Password Hash #

We'll use Python's built-in crypt module (for Unix-style password hashing) plus hashlib and secrets for secure randomness (far better than random for security purposes):

import crypt
import hashlib
import secrets

def generate_postfix_password_hash(password: str) -> str:
    # Generate a secure random salt matching MySQL's pattern: $6$ + last 16 chars of SHA-1(random)
    random_bytes = secrets.token_bytes(16)
    sha1_hash = hashlib.sha1(random_bytes).hexdigest()
    salt = f"$6${sha1_hash[-16:]}"
    
    # Create the SHA-512 hash using the custom salt
    return crypt.crypt(password, salt)

This function will output a hash that's 100% compatible with what MySQL's ENCRYPT function produces.

Step 2: Insert the User into MySQL #

Next, we'll connect to your servermail database and insert the user with the pre-hashed password. We'll use mysql-connector-python (install it with pip install mysql-connector-python):

import mysql.connector
from mysql.connector import Error

def insert_virtual_user(domain_id: int, email: str, hashed_password: str):
    try:
        # Replace these with your actual DB credentials (use env vars in production!)
        connection = mysql.connector.connect(
            host='your_database_host',
            database='servermail',
            user='your_db_username',
            password='your_db_password'
        )
        
        if connection.is_connected():
            cursor = connection.cursor()
            # We omit the `id` column here assuming it's set to auto-increment (recommended!)
            insert_query = """
            INSERT INTO virtual_users (domain_id, password, email)
            VALUES (%s, %s, %s)
            """
            record = (domain_id, hashed_password, email)
            cursor.execute(insert_query, record)
            connection.commit()
            print(f"Successfully added user: {email}")
            
    except Error as e:
        print(f"Database error occurred: {e}")
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL connection closed")

# Example usage
if __name__ == "__main__":
    # User-provided password (or let users input this via a form/script)
    user_password = "my_secure_password_123"
    hashed_pw = generate_postfix_password_hash(user_password)
    # Insert for domain ID 1 (match your existing domain setup)
    insert_virtual_user(1, "email1@example.com", hashed_pw)

Important Considerations #

• Security Best Practices: Never hardcode database credentials in your code. Use environment variables (e.g., os.getenv("DB_PASSWORD")) or a secure secrets manager instead.
• Auto-Increment ID: In your original query, you hardcoded id='1'. It's much safer to set the id column as an auto-increment primary key so MySQL handles unique IDs automatically.
• User Submission Flow: To let users submit their own hashed passwords, you can build a simple web form or CLI script that takes their email and password, runs generate_postfix_password_hash, and sends the hash + email to your insertion logic. Always use HTTPS if this is exposed over the web!
• Windows Compatibility: The crypt module is standard on Unix-like systems (Linux, macOS). If you're working on Windows, install cryptography with pip install cryptography as a fallback.

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