PostgreSQL: encrypting and decrypting (and Rails examples)

encryptor.sql

-- first, install the pgcrypto module
CREATE EXTENSION pgcrypto;

-- this is how to use its crypto-functions and encoding the value into base64 (by default postgresql uses bytea)
with sensitive_info (email, encrypted_email) as (
	values ('[email protected]', 'ww0EBwMC1V/tU6ZYV3Nq0kEBz8iqdRYE0A/zL3dQ+du9Ex+GkSDzz3Llq8g1yCoa9XpNbhKzK7U5
b1EtUYzUMer8XSaCwdFSPKmbSfJo1btoSQ==')
)
select encode(pgp_sym_encrypt(s.email, 'mySecretKey'), 'base64')           as encrypted_data
      ,pgp_sym_decrypt(decode(s.encrypted_email, 'base64'), 'mySecretKey') as decrypted_data
  from sensitive_info s
;

There're other alternatives in the application-level, such as:

• using @PrePersist, @PreUpdate and @PostLoad JPA listeners (it's enough for simple use cases)
• encrypting JSON properties with JPA/Hibernate using @PrePersist, @PreUpdate and @PostLoad
• encrypting with the Hibernate @ColumnTranformer annotation and the underlying database cryptor functions
• encrypting with the Hibernate @ColumnTranformer annotation
• encrypting with JPA Type Converters

• Twitter thread: The RubyOnRails has implemented a good approach - Active Record Encryption:

class Person < ApplicationRecord
  encrypts :name
  encrypts :email_address, deterministic: true
end

# Person.find_by(name: "jorge") # doesn't work
# Person.find_by(email_address: "[email protected]") # works

• Here you can check the guide: Active Record Encryption
• LockBox: Another RubyOnRails library that follows the same approach:
  • Works with database fields, files, and strings
  • Maximizes compatibility with existing code and libraries
  • Makes migrating existing data and key rotation easy
  • Has zero dependencies and many integrations

Great article about Securing Sensitive Data in Rails