Exportar hashes de contraseñas en formato PHC

Documentación Autoalojamiento
1

Los detalles actuales sobre el sistema de almacenamiento de contraseñas de Discourse se encuentran en discourse/docs/SECURITY.md at main · discourse/discourse · GitHub. Al momento de escribir esto, utilizamos PBKDF2-SHA256 con 600.000 iteraciones.

En algunas situaciones, es posible que desees exportar todas las contraseñas hash de Discourse e importarlas en otro sistema. Por ejemplo, podrías estar migrando desde la autenticación integrada de Discourse hacia un sistema SSO personalizado. Recuerda que es imposible extraer las contraseñas originales de la base de datos, por lo que tu sistema de destino debe ser capaz de ejecutar el mismo algoritmo de hash con los mismos parámetros.

Los datos de las contraseñas se almacenan en la tabla user_passwords, que contiene las columnas password_hash, password_salt y password_algorithm. La columna password_algorithm almacena el prefijo completo del algoritmo PHC (por ejemplo, $pbkdf2-sha256$i=600000,l=32$), el cual puede variar por usuario si el número de iteraciones ha aumentado con el tiempo.

Puedes usar el Explorador de Datos para exportar la información en un formato legible por computadora:

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

Esto exportará los datos en el formato nativo de Discourse. La sal está codificada en hexadecimal y el hash de la contraseña también está codificado en hexadecimal.

Algunos sistemas externos admiten el formato de cadena PHC, que busca ser una forma de representar la salida de una función de hash de contraseñas independiente del algoritmo. Para pbkdf2-sha256, esta cadena contiene el tipo de algoritmo, el número de iteraciones, la sal codificada en base64 y el hash codificado en base64. Afortunadamente, Postgres puede manejar todo esto por nosotros en una sola consulta.

Para generar cadenas PHC para cada usuario de Discourse, puedes usar una consulta del Explorador de Datos como esta:

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

Si utilizas Auth0, entonces necesitas esta consulta en su lugar:

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 Me gusta
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 me gusta
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 Me gusta
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 Me gusta
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 Me gusta
7

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

1 me gusta
9

Hey @angus, we’re cleaning things up here.

Is it true that the OP code block should read:

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

and include something like:

For more information about importing Discourse passwords to Auth0 see Bulk User Import Custom Password Hash Issue - Auth0 Community.

To move data from Auth0 to Discourse, this might help: Migrated password hashes support.

3 Me gusta
10

Yup, that looks good.

You may want to add something in about each import needing specific attention, as the user data being handled will differ depending on the use case, i.e. don’t just copy / paste these queries.

Also, exporting passwords in PHC is not necessarily only for Auth0, so perhaps that should just be refered to as an “example”.

The full query I used for my export was

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 Me gusta