postgresql - 使用 md5 加密密码而非明文密码登录 PostgreSQL

Question

我创建了一个使用 md5 加密密码的用户，如下所示:

create user testuser with encrypted password 'md54ca03099a7cd3945e0260801ff5972a3';

加密后的密码是"md5"+ md5(密码+用户名)的组合

password=test
username=testuser

使用 md5 方法在 pg_hba.conf 文件中添加了 testuser 条目

现在我正在尝试使用上面创建的用户登录，如下所示:

psql -d dbexpress -U testuser

它提示输入密码。我已经提供了上面的加密密码，所以它给我的错误是:

psql: FATAL:  password authentication failed for user "testuser"

但我可以使用最简单的密码“test”登录到 postgresql。

Answer 1

认证方法md5不直接管理系统目录中的密码加密(CREATE ROLE中的关键字ENCRYPTED):

Postgres 10 或更高版本

注意这个update in Postgres 10

• Add SCRAM-SHA-256 support for password negotiation and storage(Michael Paquier, Heikki Linnakangas)

  This provides better security than the existing md5 negotiation andstorage method.

The manual:

To ease transition from the md5 method to the newer SCRAM method, ifmd5 is specified as a method in pg_hba.conf but the user's password onthe server is encrypted for SCRAM (see below), then SCRAM-basedauthentication will automatically be chosen instead.

Postgres 9.6 或更早版本

Per documentation on the authentication method:

The password-based authentication methods are md5 and password. Thesemethods operate similarly except for the way that the password is sentacross the connection, namely MD5-hashed and clear-text respectively.

Per documentation on the ENCRYPTED keyword in CREATE ROLE :

ENCRYPTED
UNENCRYPTED

These key words control whether the password is stored encrypted in the system catalogs. (If neither is specified, the default behavioris determined by the configuration parameter password_encryption.) Ifthe presented password string is already in MD5-encrypted format, thenit is stored encrypted as-is, regardless of whether ENCRYPTED orUNENCRYPTED is specified (since the system cannot decrypt thespecified encrypted password string). This allows reloading ofencrypted passwords during dump/restore.

两者都使用 md5 加密，但第一个与传输有关，第二个与存储有关。即使使用身份验证方法 md5(在 pg_hba.conf 中设置)，您仍然需要为登录提供未加密密码 )。用户名作为salt在客户端和服务端进行md5加密。

pg_hba.conf 中的第一个匹配条目

关于您的评论:

Added entry for testuser in pg_hba.conf file with md5 method.

不要只是“添加”一个条目。 pg_hba.conf 中的第一个 匹配行已应用!

pg_hba.conf 手册:

The first record with a matching connection type, client address,requested database, and user name is used to perform authentication.

在所有引号中大胆强调我的。