How to implement Postgre PgCrypto and migrate data to encrypted on Ruby on Rails

Secure your Personally Identifiable Information without sacrifice speed query

In Ruby on Rails there is some application level encryption like MessageEncryptor, gemfile such as attr_encrypted, lockbox

The problem for application level encryption is tend difficult to search, eventually when your key or decryption process in cloud. There is another method to create another column and use hash computation since same value will return same hashed value.

PgCrypto is module provides cryptographic functions for postgreSql, it encryption level database. It supported are both symmetric-key and public-key encryption.

We try to implemented on ruby on Rails and save key into KMS platform. And migrate plain attribute into encrypted one.

We install gem active_record-pgcrypto using gem install active_record-pgcrypto.

Symmetric encryption encrypts and decrypts data using the same key and is faster than asymmetric encryption. It is the preferred method in an environment where exchanging secret keys is not an issue. With asymmetric encryption, a public key is used to encrypt data and a private key is used to decrypt data. This is slower then symmetric encryption and it requires a stronger key.

Step 1: create initializer

For these example, we use serializer attributes and arel API on Rails and symmetric-key encryption on postgres.

# config/initializers/pgcrypto.rb
require 'active_record/pgcrypto'
# Replace the default environment variable name with your own value/key.
ActiveRecord::PGCrypto::SymmetricCoder.pgcrypto_key = LOAD_FROM_KMS

We also set initializer to load symmetric key, ensure to save key outside app like using AWS KMS, Google KMS, Vault or another third party key management service, to avoid breach key.

For further option we can also set options and encoding via:

ActiveRecord::PGCrypto::SymmetricCoder.pgcrypto_options = 'cipher-algo=aes256, unicode-mode=1'
ActiveRecord::PGCrypto::SymmetricCoder.pgcrypto_encoding = 'utf8'

Step 2: Implement model

On model we set serializer on rails and create function to search:

class MyModel < ActiveRecord::Base
  serialize(:email, ActiveRecord::PGCrypto::SymmetricCoder)

  def self.decrypted_email
    ActiveRecord::PGCrypto::SymmetricCoder
      .decrypted_arel_text(arel_table[:email])
  end
end

But since above implementation is redundant and attribute to encrypted is more than one, we create metaprogramming and create concern for these one:

metaprogramming for pgcryptoable

decrypted_#{attribute} on above method will return arel query like below

# return Arel::Nodes::NamedFunction, it can use like Arel::Nodes::Matches
# example User.where(User.decrypted_email.matches('raisa@random.com'))
----- will return -----
"SELECT \"users\".* FROM \"users\" WHERE ENCODE(PGP_SYM_DECRYPT_BYTEA(\"users\".\"email\", CRYPTOGRAPHY_KEY), 'escape')ILIKE 'raisa@random.com'"
or it can use below method to get single record
Admin.find_by(Admin.decrypted_email.eq('raisa@random.com'))

We need to test those concern with shared_examples

We try to create shared example to test model which have these function

pgcryptoable shared example

After that we refactor previous model to become:

class MyModel < ActiveRecord::Base
  include PgCryptoable
  attr_pgcrypto :email, :full_name, :gender
end

and adding additional test:

it_behaves_like 'pgcryptoable model'
is_expected.to have_pgcryptoable_attribute(:email)

Step 3: migrate plain attributes

user migration

On these step we enable extension pgcrypto on postgres using command enable_extension .

After that we rename recent attribute into temporary field and do migration, when update method called, it will called serializer to encrypt data

Thats all and now database is encrypted and query search not decrease significanly

time consume

Photo by Mauro Sbicego on Unsplash