- 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.
Add this line to your application's Gemfile:
And then execute:
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
class User attr_encrypted :ssn, :key => 'a secret key', :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.
- 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 find_by_ methods.
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
- MRI Ruby 1.9.3
- Rails 3.2.11
- attr_encrypted 1.2.1
- PostgreSQL 9.1
The bulk of this code is a humble verbatim copy and paste job from jmazzi's crypt_keeper gem. Thanks, Justin!
- Fork it
- Create your feature branch (
git checkout -b my-new-feature)
- Commit your changes (
git commit -am 'Add some feature')
- Push to the branch (
git push origin my-new-feature)
- Create new Pull Request