以 PHC 格式导出密码哈希

文档 自我托管
1

Discourse 密码存储系统的当前详细信息可参阅 https://github.com/discourse/discourse/blob/main/docs/SECURITY.md。截至撰写本文时，我们使用 PBKDF2-SHA256 算法，迭代次数为 600,000 次。

在某些情况下，您可能希望将 Discourse 中所有已哈希的密码导出，并导入到另一个系统中。例如，您可能正从 Discourse 内置的身份验证迁移到自定义的 SSO 系统。请记住，无法从数据库中还原原始密码，因此您的目标系统必须能够使用相同的哈希算法及参数进行运算。

密码数据存储在 user_passwords 表中，该表包含 password_hash、password_salt 和 password_algorithm 列。password_algorithm 列存储完整的 PHC 算法前缀（例如 $pbkdf2-sha256$i=600000,l=32$），如果迭代次数随时间增加，该值可能因用户而异。

您可以使用数据浏览器（Data Explorer）以计算机可读格式导出相关信息：

SELECT users.id, users.username, up.password_salt, up.password_hash, up.password_algorithm
FROM users
INNER JOIN user_passwords up ON users.id = up.user_id
WHERE users.id > 0

这将按 Discourse 原生格式导出数据。其中，salt 为十六进制编码，密码哈希值也为十六进制编码。

某些外部系统支持 PHC 字符串格式，该格式旨在以跨算法的方式表示密码哈希函数的输出。对于 pbkdf2-sha256，该字符串包含算法类型、迭代次数、base64 编码的 salt 以及 base64 编码的哈希值。幸运的是，Postgres 可以通过单个查询为我们处理所有这些内容。

若要为每个 Discourse 用户生成 PHC 字符串，您可以使用如下数据浏览器查询：

SELECT users.id, users.username,
  concat(
    up.password_algorithm,
    replace(encode(up.password_salt::bytea, 'base64'), '=', ''),
    '$',
    replace(encode(decode(up.password_hash, 'hex'), 'base64'), '=', '')
  ) as phc
FROM users
INNER JOIN user_passwords up ON users.id = up.user_id
WHERE users.id > 0

如果您使用 Auth0，则应使用以下查询：

SELECT
    user_emails.email,
    users.active as email_verified,
    concat(
        up.password_algorithm,
        replace(encode(up.password_salt::bytea, 'base64'), '=', ''),
        '$',
        replace(encode(decode(up.password_hash, 'hex'), 'base64'), '=', '')
    ) as password_hash
FROM users
INNER JOIN user_passwords up ON users.id = up.user_id
INNER JOIN user_emails 
ON users.id = user_emails.user_id 
AND user_emails.primary IS TRUE
AND users.id > 0
13 个赞
Password hash algorithm
2

Just a note here that I (with some help from the friendly Auth0 team) ended up tweaking the example query to generate valid PHC strings for importing user passwords into Auth0.

I also encoded the salt as base64 by changing this line

salt,

to

replace(encode(users.salt::bytea, 'base64'), '=', ''),

See further here (including a step by step on how to import Discourse users and their passwords into Auth0).

1 个赞
3

Thanks @angus - this is interesting because we have had a customer use the query in the OP to successfully import users to Auth0. I wonder if something has changed in their import process - IIRC the ability to import PHC strings to Auth0 was very new back in November :thinking:

3 个赞
4

Yeah, I was wondering about that, and thought the same.

I also wasn’t quite sure of the language in the PHC specification. Not sure if this means the salt must be B64 encoded or not.

The salt consists in a sequence of characters in: [a-zA-Z0-9/+.-] (lowercase letters, uppercase letters, digits, /, +, . and -). The function specification MUST define the set of valid salt values and a maximum length for this field. Functions that work over arbitrary binary salts SHOULD define that field to be the B64 encoding for a binary value whose length falls in a defined range or set of ranges.

2 个赞
5

Sorry for getting off-topic, but has anyone imported password hashes from Auth0 to Discourse? I’m thinking of doing this migration so any help would be appreciated. I’m not a paying Auth0 customer so I just wanted to know if this is feasible before paying for the password hash export.

Thanks.

6

Importing passwords is not supported by Discourse core, although it might be possible using an adapted version of this third-party plugin:

4 个赞
7

Thank you! I’ve found the repo on GitHub but not the topic here on meta.

1 个赞
9

嘿 @angus，我们正在这里清理一些内容。

OP 代码块是否应该如下所示：

SELECT id, username,
  concat(
    '$pbkdf2-sha256$i=64000,l=32$',
    replace(encode(users.salt::bytea, 'base64'), '=', ''),
    '$',
    replace(encode(decode(password_hash, 'hex'), 'base64'), '=', '')
  ) as phc
FROM users

并包含类似以下内容：

有关将 Discourse 密码导入 Auth0 的更多信息，请参阅 https://community.auth0.com/t/bulk-user-import-custom-password-hash-issue/47408。

要将数据从 Auth0 迁移到 Discourse，这可能会有所帮助：https://meta.discourse.org/t/migrated-password-hashes-support/19512。

3 个赞
10

是的，看起来不错。

你可能需要补充说明一下，每个导入操作都需要特别关注，因为所处理的用户数据会根据使用场景而有所不同，也就是说，不要直接复制/粘贴这些查询语句。

另外，在 PHC 中导出密码并不一定仅适用于 Auth0，因此或许可以将其称为一个“示例”。

我用于导出的完整查询语句如下：

SELECT
    user_emails.email,
    users.active as email_verified,
    concat(
        '$pbkdf2-sha256$i=64000,l=32$',
        replace(encode(users.salt::bytea, 'base64'), '=', ''),
        '$',
        replace(encode(decode(users.password_hash, 'hex'), 'base64'), '=', '')
    ) as password_hash
FROM users
INNER JOIN user_emails 
ON users.id = user_emails.user_id 
AND user_emails.primary IS TRUE
AND users.id > 0
2 个赞