Recently in a project, I stumbled into an interesting puzzle and decided to share it with you along with my own solution. Part of the credit goes to Jarkko Hyöty for great feedback and ideas.
We needed a data model where exactly two persons could have a partnership with each other during some time period, while one person should never belong to more than one partnership simultaneously. Initially, the problem sounded quite simple, but it starts to get a little more tricky as you dive deeper into it – especially if you want to model those rules as database constraints. It might make sense to model this sort of a thing rather in a graph database, but we wanted to stick with the good old PostgreSQL which we were already using.

First steps towards the solution

So how might we start to model this in a relational database? My first idea for modelling this kind of one-to-two relationship was something like this:

CREATE TABLE person (
    id              SERIAL PRIMARY KEY, -- auto-incrementing integer
    name            TEXT NOT NULL
    -- other person details ...
);
CREATE TABLE partnership (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v1(),
    person1         INTEGER REFERENCES person(id) ON DELETE CASCADE,
    person2         INTEGER REFERENCES person(id) ON DELETE CASCADE,
    start_date      DATE NOT NULL,
    end_date        DATE NOT NULL CHECK (start_date <= end_date)
);

That does perfectly limit the number of partners in a partnership to exactly two. However, there is a troublesome distinction between person 1 and person 2. The roles in the partnership are not strictly equal. So maybe something like this instead?

CREATE TABLE partnership (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v1(),
    start_date      DATE NOT NULL,
    end_date        DATE NOT NULL CHECK (start_date <= end_date)
);
CREATE TABLE partner (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v1(),
    partnership_id  UUID REFERENCES partnership(id) ON DELETE CASCADE,
    person_id       INTEGER REFERENCES person(id) ON DELETE CASCADE
);

Limiting the maximum number of partners

Now we no longer distinguish between partners 1 and 2, so that is good, but since this is now a standard one-to-many relationship, it no longer covers the requirement of there being exactly two people in a partnership. That requirement can be divided into two parts: 1) a partnership must have at least two members, and 2) a partnership must have at most two members. The first one is actually quite difficult, so let’s return to it later. The second one, however, can be solved for example like this:

CREATE TABLE partnership (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v1(),
    start_date      DATE NOT NULL,
    end_date        DATE NOT NULL CHECK (start_date <= end_date)
);
CREATE TABLE partner (
    partnership_id  UUID REFERENCES partnership(id) ON DELETE CASCADE,
    ind             SMALLINT NOT NULL CHECK (ind BETWEEN 1 AND 2),
    person_id       INTEGER REFERENCES person(id) ON DELETE CASCADE,
    PRIMARY KEY (partnership_id, ind),
    UNIQUE (partnership_id, person_id)
);

Here we have removed the separate primary key id from the partner table and instead added a composite primary key of partnership_id and ind. Here ind (i.e. index) is a new integer column, where the value must equal either 1 or 2. Because it is used in the composite key, it cannot have the same value twice within a partnership. Therefore it enforces the constraint that there can be at most two people in a partnership. Finally, we also add a unique constraint for (partnership_id, person_id) to prevent one person from being in a partnership with him/herself, although we will soon be adding another constraint which effectively also does prevent this.

Preventing overlapping partnerships

What about the requirement of no overlapping partnerships then? At first, it sounded impossible to enforce this on the database constraint level, but after some research, I found out that the PostgreSQL exclude constraint combined with a date range and an overlap operator might be a potential fit for this. For example, in this presentation by Jeff Davis, those were used to guarantee that there are no overlapping reservations for the same room. However, applying it to our current data model does not seem to quite work out. This kind of constraint can only compare rows within one table, but we have person_id and dates in different tables. Could we somehow still make it work?
Let’s try changing our data model a bit more by moving the date columns from the partnership association table into the same partner table with person_id. At this point the partnership table has no other columns left except for the primary key id. Therefore it is actually redundant and can be left out if we just generate the shared partnership_id at the application level instead.

CREATE TABLE partner (
    partnership_id  UUID NOT NULL,
    ind             SMALLINT NOT NULL CHECK (ind BETWEEN 1 AND 2),
    person_id       INTEGER REFERENCES person(id) ON DELETE CASCADE,
    start_date      DATE NOT NULL,
    end_date        DATE NOT NULL CHECK (start_date <= end_date),
    PRIMARY KEY (partnership_id, ind),
    UNIQUE (partnership_id, person_id)
);

Since all the relevant data is now located at the same table, it becomes possible to add an exclusion constraint which prevents one person from having two simultaneous partnerships. The idea we want to express in the constraint is that if any two rows have the same person_id and their durations overlap, then that is a constraint violation. The syntax for that is this:

EXCLUDE USING gist (
    person_id WITH =,
    daterange(start_date, end_date, '[]') WITH &&
)

Note that this uses a gist index so you need to have the btree_gist extension enabled.

CREATE EXTENSION IF NOT EXISTS btree_gist;

As you may have noticed while changing the data model we simultaneously created a new problem though. Since the dates are no longer shared through the association table, it is now possible for the two members of a partnership to have different durations for it. This does not really make sense here. Fortunately, that can be remedied by adding a couple more exclusion constraints which will cause a constraint violation if two rows with equal partnership_id values have differing start_date or end_date values. Note that when updating dates of an existing partnership, this constraint will temporarily break until the second row has also been updated, so we must make these constraints deferred to only enforce them at transaction commit time.

EXCLUDE USING gist (partnership_id WITH =, start_date WITH <>) DEFERRABLE INITIALLY DEFERRED,
EXCLUDE USING gist (partnership_id WITH =, end_date WITH <>) DEFERRABLE INITIALLY DEFERRED

Limiting the minimum number of partners

Finally, let’s return to the constraint that a valid partnership should always have two persons in it – not just one. As far as I know, this can only be solved by a trigger function which checks the partnership member count on insert, update and delete, and raises an exception if the constraint is violated. Furthermore, it has to be a deferred trigger, so that the rule is only enforced at transaction commit time, instead of raising an error immediately when you try to insert the first person to the partnership before you even have had a chance to insert the second person.
This kind of triggers can cause a considerable amount of complexity and may be difficult to maintain and debug, so in the end, we decided that enforcing this constraint is best left to the application code since it is anyway trivial to do there.

Testing out the solution

So does our finished data model now make sense and is it usable? Let’s draft a couple of queries to validate that!
Creating a new partnership can be done by inserting two rows having same partnership_id, start_date and end_date with each other and different ind and .

-- Assuming we have two persons in the person table with ids 1 and 2:
-- INSERT INTO person (name) VALUES ('Donald'), ('Daisy');
INSERT INTO partner VALUES
    (
        '336a7c66-a43c-478d-a724-a65b377d77ee',     -- some generated partnership_id
        1,                                          -- first ind
        1,                                          -- first person_id
        '2018-01-01',                               -- start_date
        '2019-06-30'                                -- end_date
    ),
    (
        '336a7c66-a43c-478d-a724-a65b377d77ee',     -- same partnership_id
        2,                                          -- second ind
        2,                                          -- second person_id
        '2018-01-01',                               -- same start_date
        '2019-06-30'                                -- same end_date
    );

Fetching partners for a given person is slightly more complex, but not too bad either. We just need to fetch all rows where there exists another row with the same partnership_id and the desired person_id. So to get for example Donald’s partners we could write:

Fetching Donald’s partners
SELECT person_id, name, start_date, end_date
FROM partner p1                                         -- Select from partner table
LEFT OUTER JOIN person ON person.id = p1.person_id      -- (joined with the person table just to get the partner's name too)
WHERE EXISTS (                                          -- all the rows for which there exists
    SELECT 1 FROM partner p2                            -- another partner row
    WHERE p2.person_id = 1 AND                          -- where the person is Donald (id 1)
          p2.partnership_id = p1.partnership_id AND     -- which is part of the same partnership
          p2.ind <> p1.ind                                -- but is not the same row
);

Resulting in

person_id
name
start_date
end_date
2
Daisy
2018-01-01
2019-06-30

Updating a partnership duration can also be done with just one simple statement, even though the dates must be updated on both rows. Note also, that in order to keep the time comparisons simple we did not make end_date nullable, but we can use a postgreSQL special value ‘infinity’ if we want to express that a partnership currently has no known end date. That value can later be converted into null at the application level if desired.

UPDATE partner
SET start_date = '2018-02-10', end_date = 'infinity'
WHERE partnership_id = '336a7c66-a43c-478d-a724-a65b377d77ee';

All in all the solution seems to work fine, even though one can question if it would have made more sense to just check all the constraints in the application level to keep the data model simpler. Anyway, if you have alternative solutions for this little puzzle I’ll be interested in hearing them. Cheers!

Joosa Kurvinen

Joosa Kurvinen

Joosa is an experienced fullstack software developer with a very broad skill set. He is always eager to learn and try out new things, regardless of whether that is with backend, frontend, devops or architecture. He has an agile mindset and always strives after clean and testable code. Joosa graduated from the University of Helsinki and wrote his master's thesis on AI and optimization algorithms.

Do you know a perfect match? Sharing is caring

This blog series is split into four parts:

  1. General information of secrets management
  2. Example how to store secrets into a version control
  3. Example how to use encrypted secrets in CI-pipelines
  4. Security issue and threat analysis based on previous examples

I’ll provide a small threat and security issue analysis based on this blog post series and the examples used, case-by-case.

Compromised personal or service account cloud credentials

It is possible that some member’s or service account’s credentials are compromised one way or another.
Simple steps for protecting compromised accounts:

  • Revoke and reissue credentials
  • Regenerate API keys
  • Look logs for unauthorised access and usage
  • Remove unexpected resources on a cloud platform

At least the Google Cloud Platform (GCP) has its own detailed guide for compromised credentials which can be read here: https://cloud.google.com/security/compromised-credentials
Last but not least: always use the multi-factor authentication for personal credentials.

Reducing vulnerability to a key compromise

If an attacker gets access to the key and encrypted secrets then all the secrets can be exposed – well, game over. The attacker can take your skeletons from version control and use them to carry out harmful acts like exposing IP addresses, passwords, API keys or even something worse.
However, you can reduce vulnerability to a key compromise with a few simple things.

Don’t re-use keys too often

With proper secrets management, you should never use a single, “one-and-only”, key for encrypting and decrypting all the secrets you have. You should create a new key which has its own purpose and encryption and is only for specific data.
In this blog series, I’ve used only one key to demonstrate how Mozilla SOPS work. You could make environment or version control repository based keys which would make things harder for an attacker. In the Very secret development operations, part II: Storing skeletons into a version control -blog, there was an example of how multiple different keys can be used with environment-specific rules (Advanced usage – Creation rules configuration).

Rotate keys and remove old versions of a key

Key rotation is a simple method to prevent key compromise: the old version of the key is versioned to history and a new, primary version of the key is created. Only the primary key is used for encrypting (and decrypting) the secrets while the old versions of key are used only for decrypting the secrets. Still, an attacker can have an old version of the key and use that for data leakage – but not for long if you remove old versions of the key!
You can manually or automatically rotate or destroy keys in cloud platforms. GCP has multiple guides regarding key management like:

In the Very secret development operations, part III: CI-pipelines -blog, there was an example of how to setup rotation period for a key, so GCP rotates keys automatically.
With SOPS you can renew the data key from the secret by command:

sops -r test.enc.yaml

For further reading about key rotation with SOPS: https://github.com/mozilla/sops#key-rotation

A person leaving the project/organisation

If a person is leaving a company or a project they can be a sort of security issue if they still have access to resources after they have left. You have to always revoke access to all systems and keys which they have used.
While SOPS handles access to keys automatically, you only have to revoke access to cloud platforms and servers where your keys are stored. GCP has a good guide for revoking access to different resources: https://cloud.google.com/security/data-loss-prevention/revoking-user-access
Also, remember to revoke access to a version control – like remove a member from a Gitlab group or project.

What could happen after a compromise?

As I mentioned earlier, an attacker could use a compromised key to make harmful acts like exposing IP addresses and passwords. But things can be even worse than that, so I’ll mention a few aspects.

  • Loss of sensitive information
    • Personal data
    • Industry secrets
    • IP addresses
  • Financial losses
    • Illegitimate financial transactions
    • Fines
    • Compensation to customers
  • Loss of reputation
    • Customer
    • Professional
    • Business

After all, your business can close down pretty quickly after the security breach. So keep your skeletons well hidden and secure secrets with proper secrets management, follow common security practices and follow, or even create security policies for your business and project.

Further reading:

Jarkko Koistinaho

Jarkko Koistinaho

Jarkko works as a technical project manager at Gofore and he is a quality and testing oriented professional in the software industry. Depending on the situation, he could be a software engineer or a Scrum Master in addition to being a software tester. Jarkko can also do some DevOps-tasks. Model-based testing and performance testing are his special skills.

Do you know a perfect match? Sharing is caring

This blog series is split into four parts:

  1. General information of secrets management
  2. Example how to store secrets into a version control
  3. Example how to use encrypted secrets in CI-pipelines
  4. Security issue and threat analysis based on previous examples

After the secrets are stored securely on a version control, it is time to look how a continuous integration pipeline should handle the secrets. In this guide, I’ll use Gitlab’s pipelines.

Configuring GCP

First of all, you have to create a new service account and a new KMS key which is used only by the service account.

  1. Head to https://console.cloud.google.com/iam-admin/serviceaccounts, select the project used and create a service account
  2. Create the private key in JSON format and temporarily store it on your computer. It will be used later when configuring Gitlab CI-pipeline.
  3. Then open https://console.cloud.google.com/security/kms, select your preferred keyring (or create a new one) and create a new key
    1. Purpose: Symmetric encrypt/decrypt
    2. Rotation period: 7 days (I’ll tell about this on the last blog post)
  4. Select the key created and add the service account with decryption access (‘Cloud KMS CryptoKey Decrypter’) to it


Modifying access to the cryptographic key

Encrypting with a new key

If you have already encrypted secrets, you must add the key to the secrets after setting up the new cryptographic key.

# Adding GCP key
sops -r -i --add-gcp-kms projects/<gcp project>/locations/global/keyRings/sops/cryptoKeys/gitlab-test api-key.json
# Removing GCP key
# sops -r -i --rm-gcp-kms projects/<gcp project>/locations/global/keyRings/sops/cryptoKeys/gitlab-test api-key.json
# -r option: 'generate a new data encryption key and reencrypt all values with the new key'

For .sops.yaml configuration, you have to add the new cryptographic key and re-encrypt the secrets. The previous blog post contained the Git pre-commit script which will re-encrypt all secrets if the configuration file is changed.

.sops.yaml
creation_rules:
  # Global enc-files (typically for testing and dev-environment)
  - path_regex: .*\.enc(\.yaml|\.json)?$
    gcp_kms: projects/<gcp dev project>/locations/global/keyRings/sops/cryptoKeys/dev-sops-key,projects/<gcp dev project>/locations/global/keyRings/sops/cryptoKeys/gitlab-test

Gitlab configuration

Variables

You can add variables for the CI-pipeline in the project or group settings. I’ll add the following variables to the project settings (Gitlab > Project > Settings > CI/CD > Variables).

Key
Value
GCLOUD_PROJECT_NAME <project name>
GCLOUD_SERVICE_KEY <service account key file content in JSON-format)
SOPS_VERSION 3.4.0

CI Pipeline

Build contains following steps:

  1. Authenticate and setup gcloud CLI
  2. Install SOPS
  3. Decrypt secrets and cache them for the next build steps
    1. decrypt_secrets.sh -script is the same that was used in the previous blog post.
.gitlab-ci.yml
decrypt secrets:
  image: google/cloud-sdk:alpine
  cache:
    # Caches decrypted secrets to next build step (among with other untracked files)
    untracked: true
  script:
    # Authentication for google cloud
    - echo "$GCLOUD_SERVICE_KEY" > ${HOME}/gcloud-service-key.json
    - export GOOGLE_APPLICATION_CREDENTIALS=${HOME}/gcloud-service-key.json
    - gcloud auth activate-service-account --key-file ${HOME}/gcloud-service-key.json
    - gcloud config set project $GCLOUD_PROJECT_NAME
     
    # Install SOPS
    - apk update && apk add --no-cache ca-certificates
    - wget https://github.com/mozilla/sops/releases/download/${SOPS_VERSION}/sops-${SOPS_VERSION}.linux -O /usr/local/bin/sops
    - chmod 0755 /usr/local/bin/sops
     
    # Install GNU findutils for proper regex support
    - apk add findutils
    - sh decrypt_secrets.sh

Gitlab missing features and bugs

In the last part of blog series: what vulnerabilities my examples could contain and how to manage them.

Jarkko Koistinaho

Jarkko Koistinaho

Jarkko works as a technical project manager at Gofore and he is a quality and testing oriented professional in the software industry. Depending on the situation, he could be a software engineer or a Scrum Master in addition to being a software tester. Jarkko can also do some DevOps-tasks. Model-based testing and performance testing are his special skills.

Do you know a perfect match? Sharing is caring

This blog series is split into four parts:

  1. General information of secrets management
  2. Example how to store secrets into a version control
  3. Example how to use encrypted secrets in CI-pipelines
  4. Security issue and threat analysis based on previous examples

This time I’ll give a straight forward example how to use Mozilla SOPS with GCP KMS and Git.

Prerequisites

This guide is based on following technologies:

  • Debian-based OS
  • Mozilla SOPS 3.4.0
  • GCP’s  Key Management Service (KMS)
  • Git

Actions:

  1. Create a Google account and GCP project
  2. Install and initialise Google Cloud SDK on your machine
  3. Install Git

Download and install SOPS on local machine

On Debian-based OS

1
2
3
wget https://github.com/mozilla/sops/releases/download/3.4.0/sops_3.4.0_amd64.deb
sudo dpkg -i sops_3.4.0_amd64.deb
rm -f sops_3.4.0_amd64.deb

Releases for other operating systems

Download proper installation file of Mozilla SOPS from releases.

Setup Google KMS and key

Make Google Cloud SDK authentication

1
gcloud auth application-default login

Create a new keyring

1
2
# Create a new keyring
gcloud kms keyrings create sops --location global

If the following error occurs: “ERROR: (gcloud.kms.keyrings.create) FAILED_PRECONDITION: Google Cloud KMS API has not been used in this project before, or it is disabled. Enable it by visiting https://console.developers.google.com/apis/api/cloudkms.googleapis.com/overview?project=<id> then retry. If you enabled this API recently, wait a few minutes for the action to propagate to our systems and retry.”. Follow the link and enable Google KMS API.

Create a new key

1
2
3
4
5
6
7
8
# Create a new key to the keyring
gcloud kms keys create dev-sops-key --location global --keyring sops --purpose encryption
# List all keys in the keyring
gcloud kms keys list --location global --keyring sops
NAME                                                                           PURPOSE          ALGORITHM                    PROTECTION_LEVEL  LABELS  PRIMARY_ID  PRIMARY_STATE
projects/<gcp project>/locations/global/keyRings/sops/cryptoKeys/dev-sops-key  ENCRYPT_DECRYPT  GOOGLE_SYMMETRIC_ENCRYPTION  SOFTWARE                  1           ENABLED

SOPS usage

Basic usage – encrypt and decrypt existing file

1
2
3
4
5
# Encrypt
sops --encrypt --gcp-kms projects/<gcp project>/locations/global/keyRings/sops/cryptoKeys/dev-sops-key api-key.json > api-key.enc.json
# Decrypt
sops --decrypt api-key.enc.json > api-key.json

If the following error occurs on encryption: “Could not generate data key: [failed to encrypt new data key with master key “projects/<gcp project>/locations/global/keyRings/sops/cryptoKeys/dev-sops-key”: Cannot create GCP KMS service: google: could not find default credentials. See https://developers.google.com/accounts/docs/application-default-credentials for more information.]“. You have to make Google Cloud SDK authentication (a few steps earlier in this post)

Basic usage – create or modify encrypted file by SOPS

With the default text editor, you can create or modify encrypted file by SOPS by running following command:

1
sops api-key.enc.json

Creating a new encrypted file needs key parameters like those used in the previous step. But if you have configured creation rules for SOPS, you don’t have to add any key parameter – so head to the next step.

Advanced usage – Creation rules configuration

Create a file named .sops.yaml to root directory where you can set specific encrypted file creation rules (example content below).

.sops.yaml
1
2
3
4
5
6
7
8
9
creation_rules:
  # Staging
  - path_regex: staging/.*\.enc(\.yaml|\.json)?$
    gcp_kms: projects/<gcp staging project>/locations/global/keyRings/sops/cryptoKeys/dev-sops-key
    pgp: 43EBC42D5F6BE0B4617A2C78E2855047997055EC
  # Global enc-files (typically for testing and dev-environment)
  - path_regex: .*\.enc(\.yaml|\.json)?$
    gcp_kms: projects/<gcp dev project>/locations/global/keyRings/sops/cryptoKeys/dev-sops-key,projects/<gcp dev project>/locations/global/keyRings/sops/cryptoKeys/gitlab-sops-key

You can specify multiple path_regex -variables where you set specific regex-named path. You can add one or more keys for the specific path_regex -variable.

Whenever you create an encrypted file to root or sub directories of the configuration file, the specific rules are affected.

Adding to version control

When you have created an encrypted file, you can store it to version control and ignore the decrypted file.

1
2
3
4
5
# Ignore original file
echo "api-key.json" >> .gitignore
# Add crypted file
git add api-key.enc.json

Automating encrypt and decrypt

Because everybody loves automation I’ll show you my tricks by using Git hooks. The basic flows are:

  1. Encrypt secrets when committing changes.
  2. Decrypt secrets when pulling, merging or doing checkout on branches.

Encrypt

Add the following script to <repository>/.git/hooks/pre-commit -hook. This script will automatically encrypt secrets when you try to commit changes. Also you can add this script to a version control and call it via hook.

encrypt_secrets.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
#!/bin/sh
# If SOPS configuration is changed, force update
GIT_SOPS_CHANGED=`git status -s | grep ".sops.yaml"`
FORCE=0
if [ ! -z "$GIT_SOPS_CHANGED" ] || [ "$#" -eq 1 -a "$1" = "-f" -o "$1" = "--forced" ]; then
  FORCE=1
fi
# Find all encrypted files
ENCRYPTED_FILES=`find . -type f -regex ".*\.enc\(\.yaml\|\.json\)?\$"`
for FILE in ${ENCRYPTED_FILES}; do
  DECRYPTED_FILE=`echo "$FILE" | sed 's/.enc././g'`
  if [ ! -f $DECRYPTED_FILE ]; then
    # Decrypt file if none exists
    echo "Decrypted file does not exist. Decrypt and re-encrypt: $FILE"
    sops --decrypt $FILE > $DECRYPTED_FILE
  fi
  # Check if secret is changed
  SECRET_CHANGED=`sops -d $FILE | diff $DECRYPTED_FILE - -q -Z`
  if [ $FORCE -eq 1 ] || [ ! -z "$SECRET_CHANGED" ]; then
    echo "Secret has changed or update is forced. Update: $FILE"
    # Replace old encrypted file with a new one
    cp $DECRYPTED_FILE $FILE
    sops --encrypt --in-place $FILE
    if [ ! -z "`git status -s $FILE`" ]; then
      # Add encrypted file to commit.
      git add $FILE
    fi
  fi
done

Decrypt

Add the following script to a version control and call it from <repository>/.git/hooks/post-checkout and post-merge. It will decrypt secrets when you are changing, pulling or merging a branch. Also this script will be used in a CI-pipeline in the next blog.

decrypt_secrets.sh
1
2
3
4
5
6
7
8
9
#!/bin/sh
ENCRYPTED_FILES=`find . -type f -regex ".*\.enc\(\.yaml\|\.json\)?\$"`
for FILE in ${ENCRYPTED_FILES}; do
  DECRYPTED_FILE=`echo "$FILE" | sed 's/.enc././g'`
  echo "Decrypting $FILE"
  sops --decrypt $FILE > $DECRYPTED_FILE
done

Using Git diff

Add the following configuration to ~/.gitconfig

[diff "sopsdiffer"]
  textconv = "sops -d"

Then create and add <repository>/.gitattributes file with following lines

*.enc diff=sopsdiffer
*.enc.json diff=sopsdiffer
*.enc.yaml diff=sopsdiffer

Now you can use `git diff` so it will show diff between decrypted files!
Next up in the blog series: “How to use secrets in CI-pipelines”-guide.

Jarkko Koistinaho

Jarkko Koistinaho

Jarkko works as a technical project manager at Gofore and he is a quality and testing oriented professional in the software industry. Depending on the situation, he could be a software engineer or a Scrum Master in addition to being a software tester. Jarkko can also do some DevOps-tasks. Model-based testing and performance testing are his special skills.

Do you know a perfect match? Sharing is caring


 
Every developer will occasionally run into a similar problem: where and how to store secrets? And this time I don’t mean hiding skeletons in the closet, but storing credentials, passwords, IP addresses, API keys and something like that. There are many ways to store secrets but this time I will focus on how to store them securely in a version control.
This blog series is split into four parts:

  1. General information about secrets management
  2. Example how to store secrets in a version control
  3. Example how to use encrypted secrets in CI-pipelines
  4. Security issue and threat analysis based on previous examples

So, why should you be serious about security and secrets management? Too many times I’ve seen that some secrets are stored in a version control as a clear text and I admit that I’ve made the same mistake many times – it is just too easy to store a secret and forget that it exists. Anyone who has access to the repository can expose and use the secret. Not to mention that hackers or possible vulnerabilities in systems which have access to data can also expose the secret. Private repositories aren’t enough.
On one day, I was thinking about what would be the best way to store secrets so that all the following requirements are fulfilled:

  1. Multiple encryption types support.
  2. Globally working solution with centralised key management.
  3. Easy to install and use. Also easy to fix any compromised information.
  4. Encrypted information is stored to a version control.

I read up further about secrets management in a version control and came up with five solutions: git-crypt, git-secret, BlackBox, Mozilla SOPS and Transcrypt. There is also Hashicorp Vault but as far as I know, it is not quick to setup from scratch and is not easier to use than other solutions.
I took a look to what encryption types are supported in each solution:

GPG
Symmetric key
Amazon KMS
Google KMS
Azure Key Vault
git-crypt   X            X
git-secret   X
BlackBox   X
Mozilla SOPS   X            X          X              X
Transcrypt            X

I didn’t want to setup any GPG-based keyservers by myself, so a cloud based key management service (KMS) is the way-to-go. In a current client project we use Google Cloud Platform (GCP), Git and Gitlab, so my biggest motivation was to use an encryption solution which supports those technologies – especially GCP. After all, reading detailed information of encryption solutions, I ended up to SOPS.
Why? For me, the most important feature was that I can encrypt and decrypt secrets with multiple keys. If one key is exposed, the key can be rotated or removed without any bigger hassle – just rotate or remove and then encrypt the secrets again. No need to change all the keys.
Another thing is that if the keys are managed on a cloud platform, all authentications are based on a cloud platform’s user and service management with trace logging.
 

 
Mozilla SOPS (Secrets OPerationS) is an open-source editor of encrypted files that supports YAML, JSON, ENV, INI and binary formats and encrypts with AWS KMS, GCP KMS, Azure Key Vault and PGP.
SOPS will encrypt and decrypt secrets in the text form so running any diff tools for tracking changes is possible. It is built in the Go programming language and SOPS is compatible with the Windows, Mac and Linux platforms.
In the second part of this blog series, I’ll dive into a technical “How to add secrets to version control”-guide. It will give a tour of using Mozilla SOPS with GCP and Git.

 

Further reading:

Jarkko Koistinaho

Jarkko Koistinaho

Jarkko works as a technical project manager at Gofore and he is a quality and testing oriented professional in the software industry. Depending on the situation, he could be a software engineer or a Scrum Master in addition to being a software tester. Jarkko can also do some DevOps-tasks. Model-based testing and performance testing are his special skills.

Do you know a perfect match? Sharing is caring