Storing encrypted data in Postgres with Go

Storing encrypted data in Postgres with Go

In this article, we will see how to store a Go object (struct) as as encrypted data in a database. We will assume Postgres for this article, but the technique should work with other databases as well. To show the versatility of the technique, we will look at using both pgx (for raw SQL) and the gorm ORM toolkit. Presently, we will look at how to perform symmetric encryption with the symmecrypt library using the AES-GCM algorithms. In a future article we may look at how we can achieve a similar result using GPG via the go-opengpg library.

If you want to go straight to the code, you can find it in this repository: https://github.com/zikani03/store-encrypted-json-go-struct-tutorial

Why and when to encrypt data stored in the database

Firstly, why would we want to store data encrypted in a database?

With the increase of cyber threats and threat actors on the internet, any application you develop must have Security as a top priority. Encryption provides a good level of security for sensitive data such as Personally Identifying Information (PII), Finance information (like Credit Card information) and other sensitive pieces of data. If a threat actor gains access to the encrypted data, you want to make it harder for them to decrypt and get the raw data.

So, when should we encrypt data in our applications? Ideally, every time. Practically, no one encrypts everything in the database at the application level. You can use disk level encryption to secure your whole storage layer but that is out of scope of this article.

What will we be encrypting?

In this article, we will demonstrate the technique using a fictional service that needs to encrypt Customer settings which may contain API Keys to third-party services, something that is common nowadays with SaaS platforms and AI service wrappers which ask you keys for APIs like OpenAI’s or another platform.

We want to keep data securely, especially in a multi-tenant system where each Customer has their own settings, and we want to ensure that data is encrypted so that even a database administrator or anyone with access to the database cannot read that information without a decryption key.

The key structs we will work with are shown below:

type Setting struct {
    CustomerID   int64        `json:"customer_id" gorm:"primaryKey"`
    Key          string       `json:"key" gorm:"key"`
    Data         SettingsData `json:"data" gorm:"column:data_hash;type:text"`
}

type SettingsData struct {
    Version         string `json:"version"`
    AllowNewMembers bool   `json:"allowNewMembers"`
    OpenAIAPIKey    string `json:"apiKey"`
}

Our database table for this would look like this. Note that the data is stored as a text column

CREATE TABLE tenant_settings (
  customer_id integer primary key not null,
  key text not null,
  data_hash text not null
);

Encryption using AES-GCM using symmecrypt

Let’s see how to perform basic encryption of data. We will use ovh/symmecrypt to enable us to encrypt data. Firstly, we need to generate an encryption key, which we have to keep somewhere safe as it will be used for both encryption and decryption.

func generateSecureKey() string {
    b := make([]byte, 32)
    // Read 32 random bytes from the cryptographically secure random number generator
    _, err := rand.Read(b)
    if err != nil {
        fmt.Println("Error generating random bytes:", err)
        return ""
    }
    // Print the random bytes in hexadecimal format
    return fmt.Sprintf("%x", b)
}

After generating and storing our encryption key, we need to pick an algorithm to use for encryption/decryption. For the purposes of this article we chose AES-GCM, although the symmecrypt supports other algorithms as well.

We have to store the key in a keyloader, as shown below:

    secretKey := generateSecureKey()
    kk := keyloader.KeyConfig{
        Identifier: EncryptionKeyCtx,
        Cipher:     "aes-gcm",
        Timestamp:  time.Now().UnixMilli(),
        Sealed:     false,
        Key:        string(secretKey),
    }

    keyList := []configstore.Item{}
    keyList = append(keyList, configstore.NewItem(EncryptionKeyCtx, kk.String(), 1))
    configstore.RegisterProvider("env", func() (configstore.ItemList, error) {
        return configstore.ItemList{
            Items: keyList,
        }, nil
    })

The following snippet shows how to perform the encryption once the key is loaded into the configuration store. As you can see, the Encrypt function expects a slice of bytes and also returns a slice of encrypted bytes. As a result we have to decide on how to encode those bytes in order to print them out to the terminal, in this article we are encoding to Base64.


func HowToEncrypt() {
    k, err := keyloader.LoadSingleKey()
    if err != nil {
        log.Fatalf("failed to load encryption key")
        return
    }

    encrypted, err := k.Encrypt([]byte("Hello World, Please encrypt me!"))
    if err != nil {
        log.Fatalf("failed to encrypt data %v", err)
        return
    }

    fmt.Println("Encrypted, encoded as Base64", base64.StdEncoding.EncodeToString(encrypted))
}

Storing and reading the encrypted data via pgx or `database/sql`

In order to store our encrypted data to the database via pgx we need to implement the Valuer interface. This interface must be implemented for the structs that we wish to store in encrypted manner. The extra benefit is that this enables this technique to work with other databases using the standard database/sql module.

Implementing the Value() function

This implementation of the Value() function does several key things to make things work:

Marshals the value of the struct itself to JSON data
Loads the encryption key from the key loader
Encrypts the JSON data using the loaded key
Encodes and returns the encrypted data as a Base64 string

func (settings *SettingsData) Value() (driver.Value, error) {
    data, err := json.Marshal(settings)
    if err != nil {
        return nil, fmt.Errorf("failed to marshal data %v", err)
    }

    k, err := keyloader.LoadSingleKey()
    if err != nil {
        return nil, fmt.Errorf("failed to marshal data %v", err)
    }

    encrypted, err := k.Encrypt(data)
    if err != nil {
        return nil, fmt.Errorf("failed to marshal data %v", err)
    }

    return base64.StdEncoding.EncodeToString(encrypted), nil
}

As a result of these steps, the database driver will store the value as a Base64 string in the database. That string may be decoded from base64 but because it is encrypted the output will look like garbage. In order to decrypt the data, one must use the same key and algorithm that was used to encrypt it.

Implementing the Scan() function

In order to read the data back and decrypt it we simply have to implement the database Scanner interface. This function does a few things:

It tries to read/cast the value to a string
It tries to decode the value from Base64 into a slice of bytes
Loads a key from the key loader
Uses the key to decrypt the data, which is expected to be stored as JSON
Unmarshals the JSON into the struct

func (settings *SettingsData) Scan(value interface{}) error {
    stored, ok := value.(string)
    if !ok {
        return errors.New(fmt.Sprint("Failed to unmarshal JSONB value:", value))
    }

    data, err := base64.StdEncoding.DecodeString(stored)
    if err != nil {
        return fmt.Errorf("failed to decode base64 %v", err)
    }

    k, err := keyloader.LoadSingleKey()
    if err != nil {
        return fmt.Errorf("failed to load key %v", err)
    }
    decryptedData, err := k.Decrypt(data)
    if err != nil {
        return fmt.Errorf("failed to decrypt %v", err)
    }
    result := SettingsData{}
    err = json.Unmarshal(decryptedData, &result)
    *settings = result
    return err
}

Storing the encrypted data via Gorm

Similar to how we store the data when using pgx or regular database/sql - using Gorm also requires implementation of a specific interface, GormValuer, that tells the Gorm library how to handle this data when storing in the database.

Implementing the GormValue() function

Below is an implementation of the GormValue function for our SettingsData type:

func (s SettingsData) GormValue(ctx context.Context, db *gorm.DB) (expr clause.Expr) {
    data, err := json.Marshal(s)
    if err != nil {
        db.AddError(fmt.Errorf("failed to marshal data %v", err))
    }

    k, err := keyloader.LoadSingleKey()
    if err != nil {
        db.AddError(err)
        return
    }

    encrypted, err := k.Encrypt(data)
    if err != nil {
        db.AddError(fmt.Errorf("failed to encrypt data %v", err))
        return
    }

    return clause.Expr{SQL: "?", Vars: []interface{}{base64.StdEncoding.EncodeToString(encrypted)}}
}

Where to get (and store) the Encryption Key

This part is left as an exercise for the reader. It’s up to you to decide

Whether you will store and retrieve the encryption key from Configuration file, environment variables or from a third-party Key Management service like HashiCorp Vault, AWS KMS or Keycloak

Conclusion

In this article, we explored how to securely store Go structs as encrypted JSON in a PostgreSQL database using the AES-GCM algorithm via the symmecrypt library. We demonstrated how to implement encryption and decryption logic compatible with both pgx and the Gorm ORM by leveraging Go's Valuer and Scanner interfaces. This approach ensures sensitive fields like API keys remain protected—even from database administrators—by encrypting data at the application level.

By integrating encryption into your data models, you add a critical layer of security that complements other infrastructure-level protections. While this method requires some setup, it offers strong guarantees for data confidentiality—especially in multi-tenant systems or applications handling sensitive customer information.

For a complete implementation, check out the sample code in the linked repository. And remember: secure key storage is just as important as encryption itself—be deliberate about how and where your keys are managed.