"changed_at" column does not get updated on password update when "password_expiration" is enabled

[joseargao]

We are looking into potentially using rodauth for our app's authentication needs, and one of our required features is password expiration. Our problem is that the "changed_at" column in our DB does not get updated after using the change_password feature. We have set the following in our rodauth_main.rb:

enable :create_account, :verify_account, :verify_account_grace_period,
           :login, :logout, :jwt, :jwt_refresh,
           :reset_password, :change_password, :change_password_notify,
           :change_login, :verify_login_change, :close_account,
           :password_expiration

    password_expiration_id_column :account_id
    password_expiration_changed_at_column :changed_at
    require_password_change_after 30.days

We also added a table to our db:

class CreateAccountPasswordChangeTimes < ActiveRecord::Migration[7.1]
  def change
    create_table :account_password_change_times do |t|
      t.integer :account_id, null: false
      t.timestamp :changed_at, null: false, default: -> { 'CURRENT_TIMESTAMP' }
    end

    add_index :account_password_change_times, :account_id, unique: true
    add_foreign_key :account_password_change_times, :accounts
  end
end

Everything seems to work as expected, except the "changed_at" column does not get updated automatically upon password change. We worked around it with this:

    after_change_password do
      user_id = account_from_session[:id]
      unless user_id.nil?
        password_change_record = AccountPasswordChangeTime.find_by(account_id: user_id)
        password_change_record&.update(changed_at: Time.now)
      end
    end

However, it feels like this is not the right way to get this feature working.

[jeremyevans]

Something seems wrong. The change_password feature calls set_password, and password_expiration overrides that to update the password change time.

If you can put together a failing spec or minimum self contained example showing the issue (using only plain Rodauth and not rodauth-rails), I would be happy to research this issue.

[janko]

I tried running your spec with Sequel using Active Record's DB connection instead of its own, to replicate the conditions you most likely have in your Rails app, but was getting the same result. Setting DB.current_timestamp_utc = true resolves the timezone differences in the "after" of changed_at, so now they're only 1-2 seconds apart for me (sequel-activerecord_connection sets that automatically).

[joseargao]

@janko thank you for looking into this. Just to clarify things, the issue I encountered in my Rails test app is different from the timezone issue I ran into in my Ruby test app. Basically, the "changed_at" column doesn't automatically get updated in the Rails version and I don't know why.

I actually put the Rails test app on github as well, in case you or anyone else would be interested in taking a look and letting me know what's up. It's not a dealbreaker problem because we can still manually update this column in the DB. It's just strange that it doesn't get done automatically (and only in the Rails version).

[janko]

@joseargao I found the issue in your Rails app. You're relying on Timecop making Rodauth set changed_at to the travelled time, but Rodauth updates the column using CURRENT_TIMESTAMP, which retrieves the current time at the database side. It doesn't generate a time object in Ruby, so it's unaffected by Timecop.

If you turn on Active Record logging around the POST /change-password request in your tests, you can clearly see the changed_at being updated:

Sequel (0.3ms)  UPDATE "account_password_change_times" SET "changed_at" = CURRENT_TIMESTAMP WHERE ("account_id" = 1)
Sequel (0.3ms)  UPDATE "accounts" SET "password_hash" = '$2a$04$IQkcYPlm1mkY6PjrvjcnSurTaF4m0OrBfn6F6jgn9/.oDLN7edcfW' WHERE ("id" = 1)

[joseargao]

@janko thanks for pointing me in the right direction! It does indeed look like the DB is being updated as expected based on the Active Record logging. Perhaps Timecop was the wrong tool for me to test this.

[joseargao]

In case anyone wanders into this thread in the future, I have updated my rails project so it can be used as a reference for how I resolved my issues. https://github.com/joseargao/rodauth_rails_change_password