Custom Database with automatic migration -> resetting password for un-migrated users

BlackDev · November 1, 2018, 12:49pm

Hi there,

we have set up a custom database and enabled the “import users to auth0 functionality”.

However we run into the issue of some users want to reset their password before they are migrated. The user can request a password change with no problems. He will receive an email and can set a new password. The logs show “success change password request”, followed by “success change password”. However the user isn’t created in auth0 and the password in the custom database hasn’t changed either.

Our login script retrieves a user like this:

{
  "user_id": "00001",
  "user_name": "fnord@user.com",
  "email": "fnord@user.com",
  "email_verified": "TRUE"
}

And thise one is from getUser:

{
  "user_id": "00001",
  "user_name": "fnord@user.com",
  "email": "fnord@user.com",
  "email_verified": "TRUE"
}

This is the log for the successful reset-request:

{
  "date": "2018-11-01T12:02:41.488Z",
  "type": "scp",
  "description": "You can now login to the application with the new password.",
  "connection": "migrationCon",
  "connection_id": "con_000001",
  "client_id": "UltraSecretClientId",
  "client_name": "ClientName",
  "ip": "111.111.111.111",
  "user_agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.100 Safari/537.36",
  "details": {
    "title": "Change Password",
    "email": "fnord@user.com",
    "body": {
      "_csrf": "scnrmfg",
      "ticket": "SomeTicketFoobar",
      "email": "fnord@user.com",
      "newPassword": "*****",
      "confirmNewPassword": "*****"
    },
    "query": {
      "email": "fnord@user.com",
      "username": null,
      "newPassword": null,
      "tenant": "my-tenant",
      "client_id": "UltraSecretClientId",
      "connection": "migrationCon",
      "resultUrl": "",
      "includeEmailInRedirect": true
    }
  },
  "user_id": "",
  "user_name": "fnord@user.com",
  "strategy": "auth0",
  "strategy_type": "database",
  "log_id": "00000000000000000000000000000000002"
}

And this is the following reset successful:

{
  "date": "2018-11-01T12:02:41.488Z",
  "type": "scp",
  "description": "You can now login to the application with the new password.",
  "connection": "migrationCon",
  "connection_id": "con_000001",
  "client_id": "UltraSecretClientId",
  "client_name": "ClientName",
  "ip": "111.111.111.111",
  "user_agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.100 Safari/537.36",
  "details": {
    "title": "Change Password",
    "email": "fnord@user.com",
    "body": {
      "_csrf": "scnrmfg",
      "ticket": "SomeTicket",
      "email": "fnord@user.com",
      "newPassword": "*****",
      "confirmNewPassword": "*****"
    },
    "query": {
      "email": "fnord@user.com",
      "username": null,
      "newPassword": null,
      "tenant": "my-tenant",
      "client_id": "UltraSecretClientId",
      "connection": "migrationCon",
      "resultUrl": "",
      "includeEmailInRedirect": true
    }
  },
  "user_id": "",
  "user_name": "fnord@user.com",
  "strategy": "auth0",
  "strategy_type": "database",
  "log_id": "00000000000000000000000000000000002"
}

Shouldn’t it be possible to migrate users on a password-reset from a custom database? Am I missing something?

markd · November 2, 2018, 1:11pm

That’s correct, both login and password reset will trigger a user migration if you are set up for that. I suspect you’ll need to paste your custom DB scripts here so the experts can review them.

Auth0 will not change the user’s password in your custom DB (if you are importing your users into an Auth0 data store). When a user logs in or resets their password, those credentials are stored in a separate database that we (Auth0 clients) don’t have direct access to.

In your log entries above, there’s no need to hide the client ID. Client IDs are public (they form part of the URL).

BlackDev · November 2, 2018, 1:49pm

Here are the two scripts. Login:

function login(email, password, callback) {
  //this example uses the "tedious" library
  //more info here: http://pekim.github.io/tedious/index.html
  var Connection = require('tedious@1.11.0').Connection;
  var Request = require('tedious@1.11.0').Request;
  var TYPES = require('tedious@1.11.0').TYPES;

  var connection = new Connection({
    userName: 'DbUser',
    password: 'admin',
    server: 'db.database.org',
    options: {
      database: 'migration',
      encrypt: true,
      rowCollectionOnRequestCompletion: true
    }
  });

  var query = "update migrationTable set merged =CURRENT_TIMESTAMP " +
    "WHERE email = @email; " +
	  "SELECT Email, Email_Verified, Password, user_id " +
    "FROM migrationTable WHERE email = @email;";

  connection.on('debug', function (text) {
    // Uncomment next line in order to enable debugging messages
    // console.log(text);
  }).on('errorMessage', function (text) {
    console.log(JSON.stringify(text, null, 2));
    return callback(text);
  }).on('infoMessage', function (text) {
    // Uncomment next line in order to enable information messages
    // console.log(JSON.stringify(text, null, 2));
  });

  connection.on('connect', function (err) {
    if (err) { return callback(err); }

    var request = new Request(query, function (err, rowCount, rows) {
      if (err) {
        callback(new Error(err));
      } else if (rowCount < 1) {
        callback(new WrongUsernameOrPasswordError(email));
      } else {
        bcrypt.compare(password, rows[0][2].value, function (err, isValid) {
          if (err) { callback(new Error(err)); }
          else if (!isValid) { callback(new WrongUsernameOrPasswordError(email)); }
          else {
            callback(null, {
              user_id: rows[0][3].value,
              email: rows[0][0].value,
              email_verified: rows[0][1].value
            });
          }
        });
      }
    });

    request.addParameter('email', TYPES.VarChar, email);
    connection.execSql(request);
  });
}

And the one for getUser:

function getByEmail (name, callback) {
  
  //this example uses the "tedious" library
  //more info here: http://pekim.github.io/tedious/index.html
  var Connection = require('tedious@1.11.0').Connection;
  var Request = require('tedious@1.11.0').Request;
  var TYPES = require('tedious@1.11.0').TYPES;

  var connection = new Connection({
    userName: 'DbUser',
    password: 'admin',
    server: 'db.database.org',
    options: {
      database: 'migration',
      encrypt: true,
      rowCollectionOnRequestCompletion: true
    }
  });

  var query = "SELECT Email, Email_Verified, Password, user_id " +
    "FROM migrationTable WHERE email = @email";

  connection.on('debug', function (text) {
    // Uncomment next line in order to enable debugging messages
    // console.log(text);
  }).on('errorMessage', function (text) {
    console.log(JSON.stringify(text, null, 2));
    return callback(text);
  }).on('infoMessage', function (text) {
    // Uncomment next line in order to enable information messages
    // console.log(JSON.stringify(text, null, 2));
  });

  connection.on('connect', function (err) {
    if (err) { return callback(err); }

    var request = new Request(query, function (err, rowCount, rows) {
      if (err) {
        callback(new Error(err));
      } else if (rowCount < 1) {
        callback(null,null);
      } else {
            callback(null, {
              user_id: rows[0][3].value,
              email: rows[0][0].value,
              email_verified: rows[0][1].value
            });
      }
    });

    request.addParameter('email', TYPES.VarChar, name);
    connection.execSql(request);
  });
}

James.Morrison · November 2, 2018, 5:48pm

Hey there @BlackDev, as we continue to investigate do you mind relaying what database you are currently using? Thank you in advance.

BlackDev · November 6, 2018, 1:05am

Hey, thanks for the response

We are using an MsSQL DB form Azure.

thameera · November 16, 2018, 10:45am

Hey @BlackDev, here’s how password reset works in this scenario:

User submits password reset request.
Auth0 sees that this user is not in Auth0, so it checks the custom DB (using Get User script)
Get User script returns a profile, because the user is there
Auth0 keeps a temporary record of this profile, and the user is sent a password reset email
User enters new password by following link in email
Auth0 pulls up that temporary profile and assigns the new password.
Later, the user logs in with the new password.
Now Auth0 verifies the password with the password in temporary profile.
If passwords match, an actual profile is created for the user, and they are also logged in.

So, going back to your original question:

the user isn’t created in auth0

The user is created only at step 9. In step 6, no user is created and this is by design. In truth, there’s a temporary user profile behind the scenes, which is exposed only after the user logs in for the first time. (I agree this is slightly confusing, but that’s how it is for now)

the custom database hasn’t changed either

This is expected because your custom DB has ‘Import mode’ turned on. No changes will be written in the custom DB and all changes will be done in Auth0 side.

Does that help? If you see anything different from this behavior let us know and we’ll investigate.

system · December 16, 2018, 10:45am

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.