attr_encrypted_pgcrypto

A pgcrypto-based Encryptor implementation for attr_encrypted. It delegates to pgp_sym_encrypt() and pgp_sym_decrypt() to provide symmetric-key encryption. It's useful if you need to:

• Access the plain text values directly from SQL without bringing the data into Ruby
• Integrate databases managed by other applications

Is this library a bad idea? Potentially! Please open an issue to discuss and help document any caveats.

Installation

Add this line to your application's Gemfile:

gem 'attr_encrypted_pgcrypto'

And then execute:

$ bundle

Your platform may not ship with the pgcrypto extensions by default. On Ubuntu, run:

apt-get install postgresql-contrib-9.1

Generate a migration to load the pgcrypto extension into your database. Your user will need superuser privileges to run this query, so you may need to manually run this via psql as the postgres user if your Rails database user does not have access.

execute("CREATE EXTENSION IF NOT EXISTS pgcrypto")

Extensions are database specific. To ensure that the extension is also enabled for your test database, rails needs to use the sql schema format. Edit config/application.rb to set:

config.active_record.schema_format = :sql

Usage

See attr_encrypted's Custom encryptor documentation.

class User
  attr_encrypted :ssn, :key => 'a secret key', :encryptor => AttrEncryptedPgcrypto::Encryptor, :encode => false
end

If you do not disable :encode, attr_encrypted will base64 encode the output, defeating the purpose of being able to query the data directly from SQL.

This is an example - please don't actually embed your keys directly in your model as literal strings, or even commit them in your repository. I recommend storing your key in a .gitignored config/pgcrypto_key.txt file, having capistrano (or your preferred deployment utility) copy this from a local 'shared/' folder, and reading the value into Rails.application.config.pgcrypto via an initializer.

Caveats

• Your key is embedded into any SQL queries. The key itself will be automatically filtered from your Rails logs. However, make sure you are using a secured or private connection between your Rails server and your database.
• Unlike the OpenSSL algorithms used in the default Encryptor, pgp_sym_encrypt() uses an IV and will generate different cipher text every call. While this is more secure, you will not be able to use attr_encrypted's findby methods.

Benchmarks

pgcrypto comes out slightly faster than the OpenSSL implementation used in the default encryptor.

Benchmarking 10000 calls
               user     system      total        real
pgcrypto   1.640000   1.590000   3.230000 ( 11.775697)
openssl   15.740000   0.000000  15.740000 ( 15.704010)

Since pgcrypto is executed in a separate process, pay attention to the 'real' column for the relevant metric.

Setup spec/database.yml and run rake benchmark to test the results on your own system. You may pass an optional 'count' parameter via rake "benchmark[100000]".

Compatability

Tested against:

• MRI Ruby 2.7
• Rails 6
• attr_encrypted 3.1.0
• PostgreSQL 11.8

Credits

The bulk of this code is a humble verbatim copy and paste job from jmazzi's crypt_keeper gem. Thanks, Justin!

Contributing

1. Fork it
2. Create your feature branch (git checkout -b my-new-feature)
3. Commit your changes (git commit -am 'Add some feature')
4. Push to the branch (git push origin my-new-feature)
5. Create new Pull Request