-
Couldn't load subscription status.
- Fork 958
How to encrypt a column? #3717
-
For example, I want to generate code that encrypts and decrypts a specific column/field on the Go side when storing and retrieving the value from the database to better protect sensitive data. How can this be done?
Beta Was this translation helpful? Give feedback.
All reactions
Replies: 1 comment 1 reply
-
sqlc doesn't offer any support for client-side encryption. You would need to create your own type with custom scan / value methods that encrypt / decrypt the data as it's read across the wire.
I haven't seen anyone do this before, so can't offer any examples.
Beta Was this translation helpful? Give feedback.
All reactions
-
Here is an example in one of my services. I am not using sqlc yet so this is hand-written code:
func (d *DB) StoreAcmeAccount(ctx context.Context, account *types.AcmeAccount) error { // Encrypt sensitive data encryptedKey, err := d.encryptor.Encrypt(account.PrivateKey) if err != nil { return fmt.Errorf("failed to encrypt private key: %v", err) } encryptedReg, err := d.encryptor.Encrypt(account.Registration) if err != nil { return fmt.Errorf("failed to encrypt registration: %v", err) } query := ` INSERT INTO acme_accounts (email, acme_url, private_key, registration, created_at, updated_at) VALUES (1,ドル 2,ドル 3,ドル 4,ドル 5,ドル 5ドル) ON CONFLICT (email, acme_url) DO UPDATE SET private_key = EXCLUDED.private_key, registration = EXCLUDED.registration, updated_at = CURRENT_TIMESTAMP ` _, err = d.db.ExecContext(ctx, query, account.Email, account.AcmeURL, encryptedKey, encryptedReg, time.Now(), ) if err != nil { return fmt.Errorf("failed to store ACME account: %v", err) } return nil } func (d *DB) GetAcmeAccount(ctx context.Context, email, acmeURL string) (*types.AcmeAccount, error) { query := ` SELECT email, acme_url, private_key, registration FROM acme_accounts WHERE email = 1ドル AND acme_url = 2ドル ` var account types.AcmeAccount var encryptedKey, encryptedReg []byte err := d.db.QueryRowContext(ctx, query, email, acmeURL).Scan( &account.Email, &account.AcmeURL, &encryptedKey, &encryptedReg, ) if err == sql.ErrNoRows { return nil, nil } if err != nil { return nil, fmt.Errorf("failed to query ACME account: %v", err) } // Decrypt sensitive data privateKey, err := d.encryptor.Decrypt(encryptedKey) if err != nil { return nil, fmt.Errorf("failed to decrypt private key: %v", err) } account.PrivateKey = privateKey registration, err := d.encryptor.Decrypt(encryptedReg) if err != nil { return nil, fmt.Errorf("failed to decrypt registration: %v", err) } account.Registration = registration return &account, nil }
The only difference is using encryptedKey, err := d.encryptor.Encrypt(account.PrivateKey) + error checking in the DB retrieval and update methods. (I don't think the implementation of d.encryptor.Encrypt/Decrypt is relevant here as that does not need to be generated) and the reverse. But my question is how would this be generated?
If there was a concept of transform:
func (t *QueryTransform) Store() func (t *QueryTransform) Fetch()
And then in the SQL schema definition you could annotate a field with transform=true or something.
I am curious what you mean by a custom Scan method.
Beta Was this translation helpful? Give feedback.