Adding column encryption

[easydatawarehousing]

I'm using roda, sequel and rodauth to create a single-signon application. After a lot of tinkering, reading the docs and source code it all seems to work smoothly.

Currently I'm trying to add column-encryption to the email field of the accounts model. So I changed the migration a bit (postgres version) removing the check constraint on email and adding a unique index: left(email, 48).
Next added a model Account < Sequel::Model and set up column encryption. After that I found some methods needed overriding to work with the model, so far: email_to, account_from_login, account_from_session, new_account, save_account, verify_login_change, otp_provisioning_name.
To make other features like ChangeLogin work some more overrides can be used. An easier solution would be to override method account_ds from feature 'Base', so it returns Account.where(id: id). This method can not be overridden the same way as the other methods since it is not in list 'auth_private_methods'. After changing account_ds implementation in the source of my local rodauth gem all features work like a charm. To make such a change permanent a fork of Rodauth would be needed: not my favorite solution.

So I am wondering if this is the right approach for adding column-encryption? Am I missing something that would make it a lot simpler to add this feature?

[jeremyevans]

You can override any part of Rodauth, even parts without configuration methods, using auth_class_eval:

plugin :rodauth do
  auth_class_eval do
    private def account_ds(id=account_id)
      # ...
    end
  end
end

[easydatawarehousing]

Ah yes, this is a much better and easier solution. It is even listed in the readme file, but I didn't realize this can be used to overwrite existing methods. This opens up a lot of new options, for starters changing the email_from method so it uses 'no-reply' instead of 'webmaster'.
Thanks for your quick reply.

[winstonwolff]

@easydatawarehousing —

First of all, thanks to @jeremyevans for writing Rodauth. We've just started using it and it looks great.

We're trying to also encrypt the email column. Did you ever get this to work?

Using your clues here, we think we need to:

• change account_ds to return an Account Sequel model instead of a hash,
• change save_account to expect an Account model instead of a hash when it's saving, i.e. instead of db[accounts_table].insert(account) it should do account.save

But now RodauthRails is crashing when we try to read the account as a Rails model. We will ask Janko who wrote RodauthRails about that part. But I'm wondering if we are trying to override things at the wrong level. Does it sound like changing @account from a hash to an Account model will cause lots of things to break? Is there another place where we should be making this change?

[jeremyevans]

You'll kind of be on your own for this. By design Rodauth does not use Sequel::Model, and column_encryption is a Sequel::Model plugin. If you grep the Rodauth source code for login_column, you'll see every place you would have to potentially update to support encrypted logins/emails. I'm not opposed to adding hooks to Rodauth in order to implement encrypted logins, it may be as simple as login_column_value_to_db(login) and login_column_value_from_db(login) that by default returns login. Encryption could be built on top of that by overriding those methods to encrypt/decrypt.

[winstonwolff]

@jeremyevans —

Thanks so much for your fast reply. You've really built a nice and composable system here.

We have a new idea: Instead of overriding Rodauth's accounts.email to be encrypted, we change Rodauth's login_column to be user_id that points to our other Rails table and that has an encrypted email. Then we override the various places where it fetches an email, or a login, i.e.

• base:
  • _account_from_login
  • email_to
  • login
• reset_password:
  • send_reset_password_email
• verify email:
  • send_verify_account_email

My hunch is it will disrupt the inner workings of Rodauth less, and thus be more reliable. Which would you suspect would be the easier route?

• login_column_value_to_db(login) and login_column_value_from_db(login)
  or
• override how Rodauth retrieves an account from the login, and the user's email address?

[jeremyevans]

login_column_value_to_db(login) and login_column_value_from_db(login) seems like the easier approach. Assuming that was implemented correctly, you could use those methods for the login<=>user_id conversion, instead of doing encryption/decryption. For account creation, you'll have to create the entry for the login before the user_id lookup.

[janko]

This is what I came up with for encrypting emails using Active Record encryption mentioned in janko/rodauth-rails#196 (though it can be generalized to any encryption):

save_account do
  original_email = account[login_column]
  account[login_column] = encrypt(original_email)
  super()
  account[login_column] = original_email
end

account_from_login { |login| super(encrypt(login)) }

verify_login_change_old_login { decrypt(super()) }
# if you want emails to be encrypted in account_login_change_keys table as well:
verify_login_change_key_insert_hash { |login| super(encrypt(login)) }
get_verify_login_change_login_and_key do |id|
  login, key = super(id)
  [decrypt(login), key]
end

auth_class_eval do
  private

  def account_table_ds
    super.with_row_proc(-> (data) { with_decrypted(data, login_column) })
  end

  def _update_login(login)
    result = super(encrypt(login))
    account[login_column] = login
    result
  end

  def with_decrypted(hash, key)
    hash.merge(key => decrypt(hash[key]))
  end

  def encrypt(string)
    encrypted_type.serialize(string)
  end

  def decrypt(string)
    encrypted_type.deserialize(string)
  end

  def encrypted_type
    Account.attribute_types[login_column.to_s]
  end
end

There are places where Rodauth separates out writing from setting value in memory well, such as verify_login_change_key_insert_hash, so those parts can easily be overridden. Other places, such as save_account and _update_login, currently require a bit more work to ensure the decrypted value stays in memory.

This isn't bulletproof, as any code that calls account_ds.get(login_column) will retrieve the encrypted value (e.g. verify_login_change_old_login), because Sequel's row_proc isn't called on #get, only when retrieving the full record (which makes sense). But the configuration above seems to work for account creation with verification, password reset, and email change with verification.

[winstonwolff]

It's working! We also had to adjust how Account.instantiate worked so that rails_account would not crash with a decryption error. (We also moved the encryption functions from Janko's suggestions into here.) For the next person, here's what we changed:

class Account < ApplicationRecord

  ...

  encrypts :email, deterministic: true, downcase: true

  # `rails_account` sometimes serializes the rodauth `account` hash which has plaintext email.
  # That plaintext email will crash so we need to encrypt it so the normal Rails mechanism can decrypt it.
  def self.instantiate(attributes, column_types = {}, &block)
    attr_copy = attributes.clone
    attr_copy['email'] = encrypt(attributes['email'])
    super(attr_copy, column_types = {}, &block)
  end

  def self.encrypt(string)
    encrypted_type.serialize(string)
  end

  def self.decrypt(string)
    encrypted_type.deserialize(string)
  end

  def self.encrypted_type
    Account.attribute_types['email']
  end
end

[janko]

I think you could still keep the encryption logic inside Rodauth configuration, which should be safer because Active Record uses ActiveRecord::Base.instantiate in other contexts.

auth_class_eval do
  private

  def instantiate_rails_account
    attributes = account.stringify_keys
    attributes["email"] = encrypt(attributes["email"])
    rails_account_model.instantiate(attributes)
  end
end

[max-nitidbit]

This worked great. Thanks so much to both of you @janko @jeremyevans - not only for the help here, but for the absurdly high quality code you built.