How to encrypt a column?

[brandonkal]

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?

[kyleconroy]

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.

[brandonkal]

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.