如何用PostgreSQL存储的MD5哈希远程登录？ODBC驱动仅支持明文

Great question—let’s break this down clearly, starting with your core question and moving to the ODBC-specific solution.

Core Answer: Yes, You Can Use the MD5 Hash to Log In #

PostgreSQL’s md5 authentication scheme is built to work with the hashed value stored in the pg_authid.rolpassword column. But there’s a critical detail about the hash format you need to know:
The stored hash isn’t just the MD5 of your plaintext password. It’s a string starting with md5, followed by the MD5 hash of [your password] + [your username] (for example, if your password is mypass123 and username is manuel, the stored value would look like md5abc123def456...). You must use this exact full string (including the md5 prefix) as your "password" when connecting.

How to Connect with the Hash Using psql #

You can pass the hash directly via the command line or when prompted:

• Command line example:

  psql "host=your-server port=5432 dbname=your-db user=manuel password=md5your-full-hash-here"
  

• If you run psql without the password in the string, simply enter the full md5... hash when asked for your password.

Making This Work with ODBC #

You mentioned your ODBC driver seems to only accept plaintext—but that’s not the case! Modern PostgreSQL ODBC drivers (like psqlODBC) do support using the MD5 hash directly. Here’s how to set it up:

Option 1: Connection String #

Include the full md5... hash as the value for the Pwd parameter:

Driver={PostgreSQL Unicode};Server=your-server;Port=5432;Database=your-db;Uid=manuel;Pwd=md5your-full-hash-here;

Option 2: ODBC DSN Setup (Windows) #

When creating or editing a System/User DSN in the ODBC Data Source Administrator, enter the full md5... string into the "Password" field instead of your plaintext password.

Note: If you’re using an older ODBC driver, you may need to update to a recent version (10.x or later) to ensure this works smoothly.

Key Context from the PostgreSQL Docs #

As you referenced:

md5认证方式采用一种自定义的安全性较低的挑战-响应机制。它可以防止密码嗅探，避免在服务器上明文存储密码，但如果攻击者窃取了服务器上的密码哈希，则无法提供保护。此外，如今MD5哈希算法已不再被认为能抵御针对性攻击。

This is a critical security reminder: using the hash to log in is convenient, but if that hash is stolen, an attacker can use it exactly like you would to access your database. If security is a top priority, consider upgrading to scram-sha-256 authentication (available in PostgreSQL 10+)—it’s far more resistant to hash theft and brute-force attacks.

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