Postgresql 中文操作指南

21.5. Password Authentication #

有几种基于密码的身份验证方法。这些方法的工作方式类似，但用户密码在服务器上的存储方式和客户端提供的密码在连接中的发送方式不同。

There are several password-based authentication methods. These methods operate similarly but differ in how the users' passwords are stored on the server and how the password provided by a client is sent across the connection.

  • scram-sha-256

    • The method scram-sha-256 performs SCRAM-SHA-256 authentication, as described in RFC 7677. It is a challenge-response scheme that prevents password sniffing on untrusted connections and supports storing passwords on the server in a cryptographically hashed form that is thought to be secure.

    • This is the most secure of the currently provided methods, but it is not supported by older client libraries.

  • md5

    • The method md5 uses a custom less secure challenge-response mechanism. It prevents password sniffing and avoids storing passwords on the server in plain text but provides no protection if an attacker manages to steal the password hash from the server. Also, the MD5 hash algorithm is nowadays no longer considered secure against determined attacks.

    • The md5 method cannot be used with the db_user_namespace feature.

    • To ease transition from the md5 method to the newer SCRAM method, if md5 is specified as a method in pg_hba.conf but the user’s password on the server is encrypted for SCRAM (see below), then SCRAM-based authentication will automatically be chosen instead.

  • password

    • The method password sends the password in clear-text and is therefore vulnerable to password “sniffing” attacks. It should always be avoided if possible. If the connection is protected by SSL encryption then password can be used safely, though. (Though SSL certificate authentication might be a better choice if one is depending on using SSL).

PostgreSQL 数据库密码与操作系统用户密码是分开的。每个数据库用户的密码都存储在 pg_authid 系统目录中。可以使用 SQL 命令 CREATE ROLE 和 ALTER ROLE （例如 CREATE ROLE foo WITH LOGIN PASSWORD 'secret' ）或 psql 命令 \password 来管理密码。如果尚未为用户设置密码，则存储的密码为空，且密码认证将始终针对该用户失败。

PostgreSQL database passwords are separate from operating system user passwords. The password for each database user is stored in the pg_authid system catalog. Passwords can be managed with the SQL commands CREATE ROLE and ALTER ROLE, e.g., CREATE ROLE foo WITH LOGIN PASSWORD 'secret', or the psql command \password. If no password has been set up for a user, the stored password is null and password authentication will always fail for that user.

不同的基于密码的认证方法的可用性取决于服务器如何加密（或更准确地说，散列）用户的密码。这由密码设置时的配置参数 password_encryption 控制。如果某个密码使用 scram-sha-256 设置进行了加密，则该密码可用于认证方法 scram-sha-256 和 password（但在后一种情况下密码传输将采用纯文本）。认证方法规范 md5 将在这种情况下自动切换为使用 scram-sha-256 方法（如上所述），因此这也适用。如果某个密码使用 md5 设置进行了加密，则该密码只能用于 md5 和 password 认证方法规范（同样，在后一种情况下密码将以纯文本传输）。（以前的 PostgreSQL 版本支持以纯文本的形式在服务器上存储密码。这不再可能。）要检查当前存储的密码哈希，请参阅系统目录 pg_authid.

The availability of the different password-based authentication methods depends on how a user’s password on the server is encrypted (or hashed, more accurately). This is controlled by the configuration parameter password_encryption at the time the password is set. If a password was encrypted using the scram-sha-256 setting, then it can be used for the authentication methods scram-sha-256 and password (but password transmission will be in plain text in the latter case). The authentication method specification md5 will automatically switch to using the scram-sha-256 method in this case, as explained above, so it will also work. If a password was encrypted using the md5 setting, then it can be used only for the md5 and password authentication method specifications (again, with the password transmitted in plain text in the latter case). (Previous PostgreSQL releases supported storing the password on the server in plain text. This is no longer possible.) To check the currently stored password hashes, see the system catalog pg_authid.

要将现有安装从 md5 升级到 scram-sha-256，在确保正在使用的所有客户端库都足够新以支持 SCRAM 之后，请在 postgresql.conf 中设置 password_encryption = 'scram-sha-256'，让所有用户设置新密码，并将 pg_hba.conf 中的身份验证方法规范更改为 scram-sha-256。

To upgrade an existing installation from md5 to scram-sha-256, after having ensured that all client libraries in use are new enough to support SCRAM, set password_encryption = 'scram-sha-256' in postgresql.conf, make all users set new passwords, and change the authentication method specifications in pg_hba.conf to scram-sha-256.