Encrypted columns
Encrypt sensitive fields at rest with [Encrypted] in Socigy.OpenSource.DB v0.3.2. Transparent encrypt-on-write and decrypt-on-read, stored as bytea, using a built-in AES encryptor or a pluggable IFieldEncryptor such as HashiCorp Vault.
Overview
Mark any property [Encrypted] and Socigy transparently encrypts it on write and decrypts it on read. The value is stored as bytea ciphertext; your code keeps working with the original CLR type.
using Socigy.OpenSource.DB.Attributes;
[Table("patients")]
public partial class Patient
{
[PrimaryKey, Default(DbDefaults.Guid.Random)]
public Guid Id { get; set; }
public required string Email { get; set; } // plain. Use this to look rows up.
[Encrypted] public required string Ssn { get; set; }
[Encrypted] public DateTime BirthDate { get; set; }
[Encrypted] public string? Notes { get; set; }
}How it works
- The column is generated as
bytea. On INSERT and UPDATE the value is serialized to bytes, encrypted, and bound asbytea. On SELECT the bytes are decrypted and deserialized back to the property's type. nullis never encrypted. A nullable encrypted column stays SQLNULL.- Encryption uses the ambient
IFieldEncryptorconfigured viaSocigyFieldEncryption(see below). Generated entity code has no DI access, so the encryptor is process-wide ambient state, exactly likeSocigyDbDiagnostics.
Deferred decryption (`AutoDecrypt = false`)
By default every encrypted column is decrypted automatically on read. When most reads don't need the plaintext, set AutoDecrypt = false to skip that cost. The source generator then:
- fills a read-only
{Property}RawEncrypted(byte[]) with the raw ciphertext, and - adds a getter-only
{Property}Decryptedthat decrypts on first access and caches the plaintext into the property itself.
[Table("patients")]
public partial class Patient
{
[PrimaryKey, Default(DbDefaults.Guid.Random)]
public Guid Id { get; set; }
[Encrypted(AutoDecrypt = false)]
public required string Ssn { get; set; }
}
// On read, Ssn stays empty and SsnRawEncrypted holds the ciphertext:
var p = await Patient.Query(x => x.Id == id).WithConnection(conn).ExecuteAsync().FirstAsync();
byte[]? raw = p.SsnRawEncrypted; // raw ciphertext, no decryption performed
string ssn = p.SsnDecrypted; // decrypts once, caches into p.Ssn
// p.Ssn == ssn now (cached)Ssn). For a read-modify-write of an AutoDecrypt = false row, access {Property}Decrypted first so the plaintext is populated before you save.Supported types
Any common CLR value type plus string and byte[]: bool, all integer, floating, and decimal types, char, string, Guid, DateTime, DateTimeOffset, TimeSpan, byte[], enums, and Nullable<> of these. Other types throw NotSupportedException.
[Encrypted] cannot be combined with [ValueConvertor], [JsonColumn], or [RawJsonColumn] in this version. The source generator reports error SCGDB002.Encrypted columns are not queryable
Encryption is non-deterministic (a fresh random IV each time), so the same plaintext produces different ciphertext. Using an encrypted column in a WHERE, ORDER BY, SELECT projection, or LIKE clause throws a clear NotSupportedException:
// Throws: Ssn is [Encrypted].
await foreach (var p in Patient.Query(x => x.Ssn == ssn).WithConnection(conn).ExecuteAsync()) { }
// Correct: filter by a non-encrypted key, then read the decrypted value.
var patient = await Patient.Query(x => x.Email == email).WithConnection(conn).ExecuteAsync().FirstAsync();
var ssn = patient.Ssn; // decrypted automaticallyConfiguring the encryptor
Built-in AES (no external KMS)
AesFieldEncryptor is an authenticated AES-256-CBC + HMAC-SHA256 encryptor (encrypt-then-MAC). Give it a 32-byte, high-entropy random key from your secret store (never hard-code keys, and don't use a passphrase — the key is used directly) and configure it once at startup:
using Socigy.OpenSource.DB.Core.Encryption;
byte[] key = Convert.FromBase64String(builder.Configuration["Encryption:Key"]!); // 32 bytes
SocigyFieldEncryption.Configure(new AesFieldEncryptor(key));Do this before any encrypted column is read or written. If an [Encrypted] column is accessed with no encryptor configured, you get a clear InvalidOperationException.
The ciphertext byte format is fixed little-endian, so values encrypted on one machine decrypt correctly on any other architecture. AesFieldEncryptor is also IDisposable — disposing it zeroes the in-memory key material (after which it throws ObjectDisposedException).
Associated data (context binding)
[Encrypted] columns are bound to their context automatically: the generated code passes table:column as associated data on every encrypt and decrypt, so a stored value cannot be copied into a different column or row and still decrypt — the integrity check fails. You don't need to do anything to get this.
Under the hood, IFieldEncryptor.Encrypt/Decrypt and FieldCrypto accept an optional associatedData byte array that is authenticated but not stored. The same associated data must be supplied on decrypt; this is what the generated code does for you, and you can use it directly for custom encryption:
byte[] aad = System.Text.Encoding.UTF8.GetBytes("patients:ssn");
byte[] cipher = encryptor.Encrypt(plaintext, aad);
byte[] plain = encryptor.Decrypt(cipher, aad); // decrypting with different aad throwsHashiCorp Vault
For Vault-managed keys, use the optional Socigy.OpenSource.DB.HashiCorp package, which installs an IFieldEncryptor whose key is sourced from Vault.
Writing your own encryptor
Implement IFieldEncryptor (synchronous, local) and register it:
public sealed class MyEncryptor : IFieldEncryptor
{
public byte[] Encrypt(byte[] plaintext, byte[]? associatedData = null) => /* ... */;
public byte[] Decrypt(byte[] ciphertext, byte[]? associatedData = null) => /* ... */;
}
SocigyFieldEncryption.Configure(new MyEncryptor());