How to: use custom / self-signed certificates with Requests in Python

In a previous post, I explained how to configure Azurite to use a self-signed certificate to enable OAuth authentication. One challenge with this method is that the Azure Python SDK will refuse to connect to azurite, reporting errors such as:

azure.core.exceptions.ServiceRequestError: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1006)

This is because the the Azure SDK uses the Requests library, which in turn uses Certifi as its source of root certificates. Certifi provides a regularly updated bundle of the Mozilla root trust store, and our self-signed custom certificate obviously isn’t in Mozilla’s trust list!

Making Requests trust our self-signed certificate

You can get around this by setting the REQUESTS_CA_BUNDLE or CA_BUNDLE environment variables which tells Certifi to ignore its inbuilt certificate bundles, but if we just point that at our self-signed root certificate then we lose access to the rest of the trust store, which makes connecting to public APIs difficult.

One option is to copy the certifi trust store to a location in our workspace and inject our certificate, then set the environment variables to point to this new store, but that’s more config to manage, and this is just local development – we dont need the extra hassle.

Injecting our certificate in to Certifi’s trust store

So my preferred method is to inject my self-signed certificate in to Certifi’s trust store directly, and I created the following bash script to do this. First it checks you’re in a virtual environment, then checks that certifi is installed (it uses pip for this, but this will work alongside other package managers). Finally, it extracts the first line of the base64 encoded certificate and checks to make sure it isn’t already in the certificate bundle before injecting it along with some metadata to help you figure out where it came from.

#!/bin/bash
# Function to log errors and exit
log_error() {
    echo "❌ Error: $1"
    exit 1
}
# Check if custom certificate path is provided
CUSTOM_CERT="$1"
if [ -z "$CUSTOM_CERT" ]; then
    log_error "Please provide the path to the custom certificate as an argument."
fi
# Check if the custom certificate file exists
if [ ! -f "$CUSTOM_CERT" ]; then
    log_error "Custom certificate file not found at $CUSTOM_CERT."
fi
# Check if in a virtual environment
if python -c 'import sys; sys.exit(0 if sys.prefix != sys.base_prefix else 1)'; then
    echo "✅ Virtual environment detected: $(python -c 'import sys; sys.stdout.write(sys.prefix)')"
else
    log_error "Not in a virtual environment. Please activate a virtual environment before running the script."
fi
# Check if certifi is installed
if ! python3 -m pip show certifi >/dev/null 2>&1; then
    log_error "certifi is not installed. Please install it using 'python3 -m pip install certifi'."
fi
# Get the path to the certifi certificate file
CERTIFI_CERT=$(python3 -m certifi) || log_error "Failed to get certifi certificate path."
if [ ! -f "$CERTIFI_CERT" ]; then
    log_error "Certifi certificate file not found at $CERTIFI_CERT."
fi
# Extract the first line of the public key from the custom certificate as the unique identifier
UNIQUE_IDENTIFIER=$(awk '/BEGIN CERTIFICATE/{getline; print}' "$CUSTOM_CERT")
echo "Custom certificate: $CUSTOM_CERT"
echo "Certifi certificate file: $CERTIFI_CERT"
echo "Unique identifier: $UNIQUE_IDENTIFIER"
# Check if the custom certificate is already in certifi's certificate file, and append if not
if grep -q "$UNIQUE_IDENTIFIER" "$CERTIFI_CERT"; then
    echo "✅ Custom certificate is already present in certifi's certificate file."
else
    echo "Appending custom certificate..."
    # Extract additional certificate information
    ISSUER=$(openssl x509 -in "$CUSTOM_CERT" -noout -issuer | sed 's/issuer=//')
    SUBJECT=$(openssl x509 -in "$CUSTOM_CERT" -noout -subject | sed 's/subject=//')
    LABEL=$(openssl x509 -in "$CUSTOM_CERT" -noout -subject | awk -F 'CN=' '{print $2}' | cut -d, -f1)
    SERIAL=$(openssl x509 -in "$CUSTOM_CERT" -noout -serial | sed 's/serial=//')
    MD5_FINGERPRINT=$(openssl x509 -in "$CUSTOM_CERT" -noout -fingerprint -md5 | sed 's/MD5 Fingerprint=//')
    SHA1_FINGERPRINT=$(openssl x509 -in "$CUSTOM_CERT" -noout -fingerprint -sha1 | sed 's/SHA1 Fingerprint=//')
    SHA256_FINGERPRINT=$(openssl x509 -in "$CUSTOM_CERT" -noout -fingerprint -sha256 | sed 's/SHA256 Fingerprint=//')
    TIMESTAMP=$(date -u +"%Y-%m-%d %H:%M:%S UTC")
    # Append the certificate with metadata
    {
        echo
        echo "# Certificate added by script at $TIMESTAMP"
        echo "# Issuer: $ISSUER"
        echo "# Subject: $SUBJECT"
        echo "# Label: \"$LABEL\""
        echo "# Serial: $SERIAL"
        echo "# MD5 Fingerprint: $MD5_FINGERPRINT"
        echo "# SHA1 Fingerprint: $SHA1_FINGERPRINT"
        echo "# SHA256 Fingerprint: $SHA256_FINGERPRINT"
        cat "$CUSTOM_CERT"
    } >> "$CERTIFI_CERT" || log_error "Failed to append custom certificate to certifi."
    echo "✅ Custom certificate appended to certifi's certificate file."
fi

Drawback – certifi is updated

This approach (or either approach really) has one major drawback – it needs to be run every time Certifi is updated, which is roughly monthly. So, i decided to run the script as a launch task in VSCode so that I can attach it to the “debug” tasks that are already embedded in my workflow. While i was at it, i chained a poetry install task to ensure that my dependencies are fully installed. I added the task in VSCode’s tasks.json to run the script after installing the packages (also note i’ve replaced pip with poetry in the package task):

{
  "version": "2.0.0",
  "tasks": [
    {
      "label": "func host start",
      "type": "func",
      "command": "host start --verbose --script-root ${workspaceFolder}/app",
      "options": {
        "env": {
          "PYTHON_ENABLE_DEBUG_LOGGING": "1"
        }
      },
      "problemMatcher": "$func-python-watch",
      "isBackground": true,
      "dependsOn": "add minica cert to certifi"
    },
    {
      "label": "add minica cert to certifi",
      "type": "shell",
      "osx": {
        "command": "${workspaceFolder}/azurite/add_minica_cert_to_certifi.sh ${workspaceFolder}/azurite/minica.pem"
      },
      "linux": {
        "command": "${workspaceFolder}/azurite/add_minica_cert_to_certifi.sh ${workspaceFolder}/azurite/minica.pem"
      },
      "problemMatcher": [],
      "dependsOn": "pip install (functions)"
    },
    {
      "label": "pip install (functions)",
      "type": "shell",
      "osx": {
        "command": "poetry install --with dev --no-interaction --sync --no-root"
      },
      "linux": {
        "command": "poetry install --with dev --no-interaction --sync --no-root"
      },
      "problemMatcher": []
    },
  ]
}

Finally, i added it as a preLaunchTask in launch.json so that it runs when i manually debug too:

{
  // Use IntelliSense to learn about possible attributes.
  // Hover to view descriptions of existing attributes.
  // For more information, visit: https://go.microsoft.com/fwlink/?linkid=830387
  "version": "0.2.0",
  "configurations": [
    {
      "name": "Python: Debug Tests",
      "type": "debugpy",
      "request": "launch",
      "program": "${file}",
      "purpose": [
        "debug-test"
      ],
      "console": "integratedTerminal",
      "justMyCode": true,
      "stopOnEntry": false,      
      "env": {
        "PYTEST_ADDOPTS": "--no-cov",
        "PYTHONPATH": "${workspaceFolder}",
        "LOG_LEVEL": "DEBUG"
      },
      "preLaunchTask": "add minica cert to certifi"
    },
    {
      "name": "Python Debugger: Current File",
      "type": "debugpy",
      "request": "launch",
      "program": "${file}",
      "console": "integratedTerminal",
      "preLaunchTask": "add minica cert to certifi"
    },
    {
      "name": "Attach to Python Functions",
      "type": "debugpy",
      "request": "attach",
      "connect": {
        "host": "localhost",
        "port": 9091
      },
      "preLaunchTask": "func host start"
    }
  ]
}

Conclusion

By injecting our self-signed certificate in to Certifi’s trust store, we’ve created a solution that:

  1. Maintains the integrity of the existing trust store
  2. Avoids the need for environment variable management
  3. Integrates seamlessly with our development workflow in VSCode

Although this approach requires us to run the script whenever Certifi is updated, by using a VSCode task configuration we can ensure that this happens automatically, and link it to our existing debug tasks.

Remember, this method is intended for development purposes. In production environments, always use properly signed certificates from trusted certificate authorities.

How to: use Azurite with self-generated certificates for HTTPS in a Codespace or Devcontainer

I’ve been using Azurite to simulate Azure storage for my development. If you’re not familiar with it, Azurite is a local storage emulator for Azure Storage, and you can read my other post about how i’ve set up my devcontainer configuration to run Azurite as a service container. As my deployed code is using an Azure Managed Identity, I wanted ensure my development environment was consistent with this and also uses Azure DefaultAzureCredential credential provider class. In this post, i will talk through the steps required to switch from using a connection string (with a well-known account and key) to using OAuth and HTTPS, helping to increase feature parity between development and production, reducing the chances of mistakes.

There are essentially 5 steps:

  1. Create a local Certificate Authority
  2. Configure Azurite to use the certificate (and enable OAuth with basic checking)
  3. Configure the devcontainer to trust the certificates
  4. Configure the local credential for OAuth
  5. Configure Azure Storage Explorer to trust the minica root certificate

Obviously, this is only useful for development, and you shouldn’t use this to secure services running directly on the internet.

Create a local Certificate Authority

The first hurdle is to set up a CA, and issue a certificate for Azurite to use. By far the simplest way is to use minica – a simple CA which generates a root certificate and any number of other certificates. The other tool i found is mkcert but i didn’t try it.

We could set this up so that it’s built every time we rebuild the devcontainer, but the minica certificate is valid for over 2 years, so it’s probably not worth it, so instead just install minica on your local and generate the certificates which we can then copy to the repo. There are installation instructions on the minica repo – I did this on my Mac:

~/Downloads > brew install minica
==> Downloading https://ghcr.io/v2/homebrew/core/minica/manifests/1.1.0
Already downloaded: /Users/rob/Library/Caches/Homebrew/downloads/291ff83573a0a9e0a7033accd18d58fcf701211c2b7b63a31e49c36fabc0cb5f--minica-1.1.0.bottle_manifest.json
==> Fetching minica
==> Downloading https://ghcr.io/v2/homebrew/core/minica/blobs/sha256:dc8955ffd5c34b8eaedbc556e71188ec55c2a01e76c26f853aeb0038c7ac2426
############################################################################################################################################################################################################### 100.0%
==> Pouring minica--1.1.0.arm64_sonoma.bottle.tar.gz
🍺  /opt/homebrew/Cellar/minica/1.1.0: 6 files, 4.3MB
==> Running `brew cleanup minica`...
Disable this behaviour by setting HOMEBREW_NO_INSTALL_CLEANUP.
Hide these hints with HOMEBREW_NO_ENV_HINTS (see `man brew`).
~/Downloads > mkdir azurite-certs
~/Downloads > cd azurite-certs
~/Downloads/azurite-certs > minica -ip-addresses 127.0.0.1
~/Downloads/azurite-certs > tree
.
├── 127.0.0.1
│   ├── cert.pem
│   └── key.pem
├── minica-key.pem
└── minica.pem
2 directories, 4 files

The minica-key.pem and minica.pem files are the CA’s private and public keys respectively. The 127.0.0.1 folder contains the private key and certificate for the hostname 127.0.0.1.

Be sure to use the argument -ip-addresses and not -domains – Node requires that IP addresses are present in the Subject Alternative Name (SAN) field of certificates. If you accidentially use the -domains option, you’ll get an ERR_TLS_CERT_ALTNAME_INVALID error when you try to connect from Azure Storage Explorer.

Examining the certificate, it looks like this:

~/Downloads/azurite-certs/127.0.0.1 > openssl x509 -in cert.pem -text -noout
Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number: 4677514164283179045 (0x40e9de5991f50025)
        Signature Algorithm: ecdsa-with-SHA384
        Issuer: CN=minica root ca 190f9e
        Validity
            Not Before: Oct  6 10:03:35 2024 GMT
            Not After : Nov  5 11:03:35 2026 GMT
        Subject: CN=127.0.0.1
        Subject Public Key Info:
            Public Key Algorithm: id-ecPublicKey
                Public-Key: (384 bit)
                pub:
                    04:...:eb
                ASN1 OID: secp384r1
                NIST CURVE: P-384
        X509v3 extensions:
            X509v3 Key Usage: critical
                Digital Signature, Key Encipherment
            X509v3 Extended Key Usage: 
                TLS Web Server Authentication, TLS Web Client Authentication
            X509v3 Basic Constraints: critical
                CA:FALSE
            X509v3 Authority Key Identifier: 
                F1:D0:94:63:AA:37:F6:EF:CF:5F:CD:83:80:2C:95:D0:76:6C:2A:07
            X509v3 Subject Alternative Name: 
                IP Address:127.0.0.1
    Signature Algorithm: ecdsa-with-SHA384
    Signature Value:
        30:...:50

I copied the entire azurite-certs folder to the .devcontainer folder of my project, and renamed the folder 127.0.0.1 to certs (as i found i couldn’t mount the originally named folder into the container).

Configure Azurite to use the certificate (and enable OAuth with basic checking)

This is relatively easy. Azurite only really needs to be told the path to the certificate and private key from the 127.0.0.1 folder. To do this, we can mount the folder in to the container, and pass the path to Azurite in the command:

services:
  devcontainer:
    ...
  azurite:
    image: mcr.microsoft.com/azure-storage/azurite
    ports:
      - "127.0.0.1:10000:10000"
      - "127.0.0.1:10001:10001"
      - "127.0.0.1:10002:10002"
    command: >
      azurite
      --blobHost 0.0.0.0
      --queueHost 0.0.0.0
      --tableHost 0.0.0.0
      --cert /workspace/certs/cert.pem
      --key /workspace/certs/key.pem
      --oauth basic
    volumes:
      - ./azurite-certs/certs:/workspace/certs

Configure the devcontainer to trust the certificates

This step is more complicated. To enable trust, you need to install the minica root certificate in to the relevant trust stores inside the container. Thankfully, i found this script which does the trick. To use it, we’ll create our own Dockerfile which defines the devcontainer. We’ll base it on the existing image, and add a couple of steps

FROM mcr.microsoft.com/devcontainers/python:1-3.11-bullseye
# Switch to root user to install packages and update certificates
USER root
# Install ca-certificates package and libnss3-tools
RUN apt-get update && apt-get install -y ca-certificates libnss3-tools
# Copy the minica certificate to the container and install it
COPY ./azurite-certs/minica.pem /usr/local/share/ca-certificates/minica.crt
COPY ./azurite-certs/trust_minica.sh /usr/local/bin/trust_minica.sh
# Update CA certificates
RUN chmod +x /usr/local/bin/trust_minica.sh
RUN /usr/local/bin/trust_minica.sh
RUN update-ca-certificates
# Switch back to the non-root user (devcontainer default user)
USER vscode
# Keep the container running
CMD ["/bin/sh", "-c", "while sleep 1000; do :; done"]

And we’ll put the script (not my work – from this Gist) in the azurite-certs folder as trust_minica.sh:

#!/bin/sh
### Script installs minica.pem to certificate trust store of applications using NSS
### https://gist.github.com/mwidmann/115c2a7059dcce300b61f625d887e5dc
### (e.g. Firefox, Thunderbird, Chromium)
### Mozilla uses cert8, Chromium and Chrome use cert9
###
### Requirement: apt install libnss3-tools
###
###
### CA file to install (customize!)
### Retrieve Certname: openssl x509 -noout -subject -in minica.pem
###
certfile="minica.pem"
certname="minica root ca"
###
### For cert8 (legacy - DBM)
###
for certDB in $(find ~/ -name "cert8.db"); do
    certdir=$(dirname ${certDB})
    certutil -A -n "${certname}" -t "TCu,Cu,Tu" -i ${certfile} -d dbm:${certdir}
done
###
### For cert9 (SQL)
###
for certDB in $(find ~/ -name "cert9.db"); do
    certdir=$(dirname ${certDB})
    certutil -A -n "${certname}" -t "TCu,Cu,Tu" -i ${certfile} -d sql:${certdir}
done

Now we need to update our compose file to point to this new Dockerfile and use that instead of the base image. Our docker-compose.yml now looks like this:

services:
  devcontainer:
    build:
      context: .
      dockerfile: Dockerfile
    platform: linux/amd64
    volumes:
      - ..:/workspace:delegated
    ports:
      - "5000:5000"
    environment:
      - POETRY_VIRTUALENVS_IN_PROJECT=true
    command: /bin/sh -c "while sleep 1000; do :; done"
    network_mode: "host"
  azurite:
    image: mcr.microsoft.com/azure-storage/azurite
    ports:
      - "127.0.0.1:10000:10000"
      - "127.0.0.1:10001:10001"
      - "127.0.0.1:10002:10002"
    command: >
      azurite
      --blobHost 0.0.0.0
      --queueHost 0.0.0.0
      --tableHost 0.0.0.0
      --cert /workspace/certs/cert.pem
      --key /workspace/certs/key.pem
      --oauth basic
    volumes:
      - ./azurite-certs/certs:/workspace/certs

Remember to rebuild your devcontainer after making these changes

Configure the local credential for OAuth

You should now be able to run your code and you won’t receive any SSL certificate errors. But the credential provider will most likely complain that it could not find a credential:

[2024-08-30T10:23:59.038Z] DefaultAzureCredential failed to retrieve a token from the included credentials.
[2024-08-30T10:23:59.039Z] Attempted credentials:
[2024-08-30T10:23:59.039Z]      EnvironmentCredential: EnvironmentCredential authentication unavailable. Environment variables are not fully configured.
[2024-08-30T10:23:59.039Z] Visit https://aka.ms/azsdk/python/identity/environmentcredential/troubleshoot to troubleshoot this issue.
[2024-08-30T10:23:59.039Z]      ManagedIdentityCredential: ManagedIdentityCredential authentication unavailable. The requested identity has not been assigned to this resource. Error: Unexpected response "{'error': 'invalid_request', 'error_description': 'Identity not found'}"
[2024-08-30T10:23:59.039Z]      SharedTokenCacheCredential: SharedTokenCacheCredential authentication unavailable. No accounts were found in the cache.
[2024-08-30T10:23:59.039Z]      AzureCliCredential: Please run 'az login' to set up an account
[2024-08-30T10:23:59.039Z]      AzurePowerShellCredential: PowerShell is not installed
[2024-08-30T10:23:59.039Z]      AzureDeveloperCliCredential: Azure Developer CLI could not be found. Please visit https://aka.ms/azure-dev for installation instructions and then,once installed, authenticate to your Azure account using 'azd auth login'.
[2024-08-30T10:23:59.039Z] To mitigate this issue, please refer to the troubleshooting guidelines here at https://aka.ms/azsdk/python/identity/defaultazurecredential/troubleshoot.

The solution for this is relatively simple. Azurite only performs basic validation on the presented token – checking for expiry and structure, but does not validate the permissions associated with the token. So we can simply log in with the Azure CLI (az login) to ensure that a principal is available.

Configure Azure Storage Explorer to trust the minica root certificate

Azure Storage Explorer also needs to be configured to trust the new root CA. To do this, click Edit > SSL Certificates > Import Certificates and import the minica.pem file:

Next, reestablish your connection with Azurite and check the Use HTTPS box:

You can access this folder at ~/Library/Application Support/StorageExplorer/certs/ on a Mac. Restart Azurite and you’re good to go!

How to: Use Azurite as a service container in Codespaces or Devcontainers

When developing with Azure Storage, it can significantly speed up your development process if you can use a local development environment, rather than constantly connecting directly to storage in Azure itself. This is where Azurite comes in – Azurite is a local storage emulator for Azure Storage, mimicing blob/container, queue and table storage. While there are lots of ways to get it running (e.g. from binary, manually using Docker etc.), I wanted to set it up as a service container in my devcontainer configuration which provides a few benefits:

  • consistency – every time i rebuild my devcontainer i know i’m resetting Azurite back to a known, clean state
  • Isolation – running it in a separate container means i avoid any potential side effects which might arise if it’s running in my main development container
  • Portability – it works consistently on Github codespaces, local devcontainer setups etc.

This guide provides a basic outline for setting up Azurite as a service container in a devcontainer configuration.

Create docker-compose.yml to define the services

Using a docker compose file inside your devcontainer definition lets you define multiple ‘services’ which all work together. In my case, i’ve set up the devcontainer service which is the main development environment based on the Microsoft Python devcontainer image, and azurite which contains azurite. Of course you could add whatever you need – PostgreSQL, Mongo, whatever you need. Here’s my docker-compose file:

services:
  devcontainer:
    image: mcr.microsoft.com/devcontainers/python:1-3.11-bullseye
    platform: linux/amd64
    volumes:
      - ..:/workspace:delegated
    ports:
      - "5000:5000"
    environment:
      - POETRY_VIRTUALENVS_IN_PROJECT=true
    command: /bin/sh -c "while sleep 1000; do :; done"
    network_mode: "host"
  azurite:
    image: mcr.microsoft.com/azure-storage/azurite
    ports:
      - "127.0.0.1:10000:10000"
      - "127.0.0.1:10001:10001"
      - "127.0.0.1:10002:10002"
    command: >
      azurite
      --blobHost 0.0.0.0
      --queueHost 0.0.0.0
      --tableHost 0.0.0.0

Create devcontainer.json

After defining your services, you need to create a devcontainer.json file. This configures the development environment, and its in the dockerComposeFile attribute that you point to your docker-compose file, and defining the service which represents the actual devcontainer. I’ve added a bunch of features which . In my configuration, i’ve also disabled some extensions which are recommended by the base image or other feature extensions by prefixing their names with -. Finally, i included a postCreateCommand which marks the /workspace folder as safe, and installs dependencies using Poetry, although you may want to skip this last step.

{
	"name": "Python 3",
	"dockerComposeFile": ["docker-compose.yml"],
	"workspaceFolder": "/workspace",
	"service": "devcontainer",
	"features": {
		"ghcr.io/devcontainers-contrib/features/poetry:2": {"version": "1.8.3"},
		"ghcr.io/devcontainers/features/github-cli:1": {},
		"ghcr.io/devcontainers/features/node:1": {},
		"ghcr.io/devcontainers/features/azure-cli:1": {},
		"ghcr.io/flexwie/devcontainer-features/pulumi:1": {},
		"ghcr.io/prulloac/devcontainer-features/pre-commit:1": {},
		"ghcr.io/jlaundry/devcontainer-features/azure-functions-core-tools:1": {},
		"ghcr.io/devcontainers/features/rust:1": {}, // for cryptography package
		"ghcr.io/devcontainers/features/docker-outside-of-docker": {}
	},
	"customizations": {
		"vscode": {
			"extensions": [
				"-vadimcn.vscode-lldb",
				"-rust-lang.rust-analyzer",
				"-tamasfe.even-better-toml",
				"-dbaeumer.vscode-eslint"
			]
		}
	},
	"forwardPorts": [10000, 10001, 10002],
  "postCreateCommand": "git config --global --add safe.directory /workspace && poetry self add poetry-plugin-export && poetry config warnings.export false && poetry config virtualenvs.in-project true --local && poetry install --with dev --no-interaction --sync --no-root"
}

Next steps

You can connect to the locally emulated storage using a connection string based on a well-known account and key baked in to Azurite:

DefaultEndpointsProtocol=http;AccountName=devstoreaccount1;AccountKey=Eby8vdM02xNOcqFlqUwJPLlmEtlCDXJ1OUzFT50uSRZ6IFsuFq2UVErCz4I6tq/K1SZFPTOtr/KBHBeksoGMGw==;BlobEndpoint=http://127.0.0.1:10000/devstoreaccount1;QueueEndpoint=http://127.0.0.1:10001/devstoreaccount1;TableEndpoint=http://127.0.0.1:10002/devstoreaccount1;

You might choose to add more advanced configuration, such as persistent storage, or enabling OAuth (to allow the use of the DefaultAzureCredential).

Finally, you can add additional services, such as PostgreSQL or Mongo, to aid your development process.

How to: Create SAS with multiple permissions in Pulumi

In Pulumi, when calling pulumi_azure_native.storage.list_storage_account_service_sas_output() to generate a SAS, you pass the required permissions to the permissions: Input[str | Permissions | None] parameter. pulumi_azure_native.storage.Permissions is an enum, offering simple selections (R, L etc.):

# Create a shared access signature scoped to the container
app_container_signature = (
  pulumi.Output.all(resource_group.name, storage_account.name, app_container.name)
  .apply(
    lambda args: azure_native.storage.list_storage_account_service_sas_output(
      resource_group_name=args[0],
      account_name=args[1],
      protocols=azure_native.storage.HttpProtocol.HTTPS,
      shared_access_start_time="2022-01-01",
      shared_access_expiry_time="2030-01-01",
      resource=azure_native.storage.SignedResource.C,
      permissions=azure_native.storage.Permissions.R,
      content_type="application/json",
      cache_control="max-age=5",
      content_disposition="inline",
      content_encoding="deflate",
      canonicalized_resource=f"/blob/{args[1]}/{args[2]}",
    )
  )
  .apply(lambda result: pulumi.Output.secret(result.service_sas_token))
)

But you can also pass a string of permissions, any of R, L, D, W, C, A, or P, depending on the actions you want to allow for the SAS. This allows you to specify permissions for reading (R), listing (L), deleting (D), writing (W), creating (C), adding (A), or processing (P) blobs within the container, such as permissions="RWL":

app_container_signature = (
  pulumi.Output.all(resource_group.name, storage_account.name, app_container.name)
  .apply(
    lambda args: azure_native.storage.list_storage_account_service_sas_output(
      resource_group_name=args[0],
      account_name=args[1],
      protocols=azure_native.storage.HttpProtocol.HTTPS,
      shared_access_start_time="2022-01-01",
      shared_access_expiry_time="2030-01-01",
      resource=azure_native.storage.SignedResource.C,
      permissions="RWL",
      content_type="application/json",
      cache_control="max-age=5",
      content_disposition="inline",
      content_encoding="deflate",
      canonicalized_resource=f"/blob/{args[1]}/{args[2]}",
    )
  )
  .apply(lambda result: pulumi.Output.secret(result.service_sas_token))
)

How to: retrieve storage account primary key using Pulumi

Another note for myself. I wanted to use this to give my app access to the entire account. I thought they would be a property of pulumi_azure_native.storage.StorageAccount but they’re not. Instead you need to call pulumi_azure_native.storage.list_storage_account_keys().

import pulumi
import pulumi_azure_native as azure_native
config = pulumi.Config()
# Create a Resource Group
resource_group_name = config.require("resourceGroupName")
location = config.require("location")
resource_group = azure_native.resources.ResourceGroup(
  resource_group_name, resource_group_name=resource_group_name, location=location
)
# Create a Storage Account
storage_account = azure_native.storage.StorageAccount(
  config.require("storageAccountName"),
  resource_group_name=resource_group.name,
  sku=azure_native.storage.SkuArgs(
    name=azure_native.storage.SkuName.STANDARD_LRS,
  ),
  kind=azure_native.storage.Kind.STORAGE_V2,
  location=resource_group.location,
)
# fetch primary key
storage_account_primary_key = (
  pulumi.Output.all(resource_group.name, storage_account.name)
  .apply(lambda args: azure_native.storage.list_storage_account_keys(resource_group_name=args[0], account_name=args[1]))
  .apply(lambda accountKeys: pulumi.Output.secret(accountKeys.keys[0].value))
)

How to: check if a container exists without account level List permission

In a storage account, you can create a SAS scoped to a specific container, however, that SAS does not have permission to execute BlobClient.exists() as this requires at least list privileges on the parent object (e.g. the account), and when you try to perform the check, you’ll get this error:

azure.core.exceptions.HttpResponseError: This request is not authorized to perform this operation.

Note that this is an HttpResponseError, not a ClientAuthenticationError (which is actually a more specific error which extends HttpResponseError), so you need to interrogate the specific response code, although note that if the container client does not exist, you might also get a ClientAuthenticationError with this message:

Server failed to authenticate the request. Make sure the value of Authorization header is formed correctly including the signature.\nRequestId:a52b0bdf-401e-0030-755f-fc3a0f000000\nTime:2024-09-01T11:09:19.0123125Z\nErrorCode:AuthenticationFailed\nauthenticationerrordetail:Signature did not match. String to sign used was rwl\n2022-01-01T00:00:00.0000000Z\n2030-01-01T00:00:00.0000000Z\n/blob/<account>/<container>\n\n\nhttps\n2015-04-05\nmax-age=5\ninline\ndeflate\n\napplication/json

As a workaround, you can instead try to read from or write to (depending on the privilege level you want to test for) the container. For example, you might do something like this:

def test_if_container_exists(container_client: ContainerClient) -> bool:
  """check if a container exists first by calling the .exists() method, even if the SAS scope is restricted to the specific container"""
  # first use the .exists() method to check if the container exists
  try:
    container_client.exists()
    return True
  except ClientAuthenticationError:
    # we dont know if this is because the SAS token doesnt have access or the container doesnt exist
    pass
  except HttpResponseError as e:
    if e.status_code == 404:
      return False
    elif e.status_code == 403:
      pass  # could be a SAS token scope restriction
  except ResourceNotFoundError:
    return False
  except Exception as e:
    # if we got any other exception, raise it
    raise
  # if we got ClientAuthenticationError, try to write a small blob to check if the SAS token is valid
  try:
    blob_client = container_client.get_blob_client("test_blob")
    blob_client.upload_blob(b"Test content", overwrite=True)
    return True
  except ClientAuthenticationError or ResourceNotFoundError:
    return False
  except Exception as e:
    # if we got any other exception, raise it
    raise

How to automate backups of a PostgreSQL server using Barman/barman-cloud-backup to S3

I was surprised not to find many up to date instructions on this. I have a few basic requirements:

  • Back up daily to an S3 bucket
  • Keep a certain number of backups
  • Run automatically, preferably using systemd not cron as it’s easier to set up and troubleshoot
  • Use a user with least privileges on the database, operating system, and in AWS/S3
  • Send the results of each backup activity to healthchecks.io

After a bit of playing around, I decided to use Barman for the backups – it’s significantly easier to configure and use than pgBackRest and has native support for backing up to S3, point-in-time restore, and more. The major downside compared to, say, running pg_dump every night, is that it requires an identical setup to restore to – identical hardware and PostgreSQL version. Least privileges in the database is tricky – to be able to back up things like roles, the account basically needs full access to all schemas. The Barman documentation says that it should run as the same user as PostgresQL, postgres.

  1. Step 1: Create an S3 bucket
  2. Step 2: Create an IAM Policy to grant access to the bucket
  3. Step 3: Create a new S3 user, assign the policy and generate credentials
  4. Step 4: Create a new check on healthchecks.io
  5. Step 5: Install AWS CLI on the server
  6. Step 6: Authenticate your new user with IAM credentials
  7. Step 7: Install prerequisites for python-snappy compression library
  8. Step 8: Download and install Barman
  9. Step 9: Share AWS credentials with postgres user
  10. Step 10: Install python-snappy as user postgres
  11. Step 11: Create backup service
  12. Step 12: Configure barman-cloud-backup to run on a schedule
  13. Step 13: Configure PostgreSQL to use barman-wal-cloud-archive to archive WAL files to S3
  14. Step 14: Verify your backup works

Step 1: Create an S3 bucket

This one’s pretty simple. Just follow the instructions on the Amazon website.

Step 2: Create an IAM Policy to grant access to the bucket

We use an IAM role to provide only the specific access that the service account needs. Go to the IAM console, select “Policies” on the left, and “Create new”. This is the template. Substitute <container_name> for your container name, obviously:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:DeleteObject",
                "s3:PutObjectAcl"
            ],
            "Resource": [
                "arn:aws:s3:::<container_name>/*"
            ]
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::<container_name>"
            ]
        }
    ]
}

Step 3: Create a new S3 user, assign the policy and generate credentials

In the IAM console, select Users > Create user. Give them a unique name. Do NOT grant console access. Click Next. On the “Set permissions” page, select “Attach policies directly” and attach the policy you just created. It’s easier if you “Filter by Type” and select “Customer managed”. Select Next then Review and Create. Lets assume you’ve created a user called backup_user.

Once you’ve created backup_user, click on their name in the list and go to the “Security Credentials” tab. Click “Create Access Key” and then select “Other” from the list of options. We need a long-lived key, so this is the best approach (unless you want to go and re-authenticate them every month??). Create the access key and then copy and note down both the Access Key and Secret. Do this now or you won’t be able to access them again and you’ll need to regenerate them.

Step 4: Create a new check on healthchecks.io

I use healthchecks.io to keep track of all the scheduled tasks and processes i’m expecting to run. Log in and create a new health check. Note the URL.

Step 5: Install AWS CLI on the server

I found that this mostly went as expected. I followed the instructions on the AWS website, however, as i’d hardened my server using the Ubuntu CIS hardening baseline, i had to set some additional permissions:

sudo chmod -R 755 /usr/local/aws-cli

Step 6: Authenticate your new user with IAM credentials

Run aws configure. Enter the Access Key ID and Secret Access Key recorded in the step above. This generates a file at ~/.aws/credentials which contains these details. Later we’ll copy this to our postgres user’s home directory – but first we need to test our backup.

Step 7: Install prerequisites for python-snappy compression library

We’re going to use the snappy compression algorithm because of its significant performance improvements over the defaults while still achieving approximately a 2:1 compression ratio (saving on both egress and S3 storage costs). First, install the required library and pip:

sudo apt-get install libsnappy-dev python3-pip

Then we install the package – we’ll need to do this again for the postgres user later, as the package is installed to user packages, not site packages.

pip install python-snappy

Step 8: Download and install Barman

Barman is super easy to install. In my server setup, i added the PostgreSQL repos to my server – if you haven’t added the repo, follow the instructions there (which are slightly different to the ones on the PostgreSQL wiki), then simply install it – we’ll also install the Cloud CLI, allowing us to back up to S3:

sudo apt-get install barman barman-cli-cloud

Although documentation says we should configure Barman specifically for local backup by setting backup_method to local-rsync for our local server in a specific configuration file, we don’t actually need to do thatbarman-backup-cloud is a standalone script that simply uses Barman. We can quickly test our backup. Note i’ve already set up a .pgpass file for the postgres_admin user:

rob@pg:~$ sudo -E barman-cloud-backup -v --cloud-provider aws-s3 --snappy --host localhost -U postgres_admin -d postgres s3://<container_name>/barman pg
2023-11-07 23:01:40,171 [1139430] INFO: Found credentials in shared credentials file: ~/.aws/credentials
2023-11-07 23:01:40,749 [1139430] INFO: Starting backup '20231107T230140'
2023-11-07 23:01:41,408 [1139430] INFO: Uploading 'pgdata' directory '/mnt/postgres/postgresql/15/main' as 'data.tar.snappy'
2023-11-07 23:01:51,430 [1139436] INFO: Upload process started (worker 1)
2023-11-07 23:01:51,428 [1139435] INFO: Upload process started (worker 0)
2023-11-07 23:01:51,533 [1139436] INFO: Found credentials in shared credentials file: ~/.aws/credentials
2023-11-07 23:01:51,534 [1139435] INFO: Found credentials in shared credentials file: ~/.aws/credentials
2023-11-07 23:01:51,680 [1139435] INFO: Uploading 'barman/pg/base/20231107T230140/data.tar.snappy', part '1' (worker 0)
2023-11-07 23:01:58,138 [1139436] INFO: Uploading 'barman/pg/base/20231107T230140/data.tar.snappy', part '2' (worker 1)
...
2023-11-07 23:12:38,601 [1139436] INFO: Uploading 'barman/pg/base/20231107T230140/data.tar.snappy', part '278' (worker 1)
2023-11-07 23:12:41,232 [1139430] INFO: Uploading 'pg_control' file from '/mnt/postgres/postgresql/15/main/global/pg_control' to 'data.tar.snappy' with path 'global/pg_control'
2023-11-07 23:12:41,248 [1139430] INFO: Uploading 'config_file' file from '/etc/postgresql/15/main/postgresql.conf' to 'data.tar.snappy' with path 'postgresql.conf'
2023-11-07 23:12:41,249 [1139430] INFO: Uploading 'hba_file' file from '/etc/postgresql/15/main/pg_hba.conf' to 'data.tar.snappy' with path 'pg_hba.conf'
2023-11-07 23:12:41,249 [1139430] INFO: Uploading 'ident_file' file from '/etc/postgresql/15/main/pg_ident.conf' to 'data.tar.snappy' with path 'pg_ident.conf'
2023-11-07 23:12:41,250 [1139430] INFO: Stopping backup '20231107T230140'
2023-11-07 23:12:41,545 [1139430] INFO: Restore point 'barman_20231107T230140' successfully created
2023-11-07 23:12:41,546 [1139430] INFO: Uploading 'backup_label' file to 'data.tar.snappy' with path 'backup_label'
2023-11-07 23:12:41,546 [1139430] INFO: Marking all the uploaded archives as 'completed'
2023-11-07 23:12:41,547 [1139435] INFO: Uploading 'barman/pg/base/20231107T230140/data.tar.snappy', part '279' (worker 0)
2023-11-07 23:12:41,745 [1139436] INFO: Completing 'barman/pg/base/20231107T230140/data.tar.snappy' (worker 1)
2023-11-07 23:12:41,880 [1139430] INFO: Calculating backup statistics
2023-11-07 23:12:41,886 [1139430] INFO: Uploading 'barman/pg/base/20231107T230140/backup.info'
2023-11-07 23:12:42,016 [1139430] INFO: Backup end at LSN: 52/B91715B0 (0000000100000052000000B9, 001715B0)
2023-11-07 23:12:42,017 [1139430] INFO: Backup completed (start time: 2023-11-07 23:01:40.749792, elapsed time: 11 minutes, 1 second)
2023-11-07 23:12:42,021 [1139435] INFO: Upload process stopped (worker 0)
2023-11-07 23:12:42,022 [1139436] INFO: Upload process stopped (worker 1)

Step 9: Share AWS credentials with postgres user

Barman and barman-cloud-backup both require read access to the PostgreSQL storage. So we need to run our backup job as the postgres user. To make this work, we’ll copy our AWS credentials to them:

sudo mkdir ~postgres/.aws
sudo cp ~/.aws/credentials ~postgres/.aws/credentials
sudo chmod 0600 ~postgres/.aws/credentials
sudo chown -R postgres: ~postgres/.aws

Step 10: Install python-snappy as user postgres

We quickly need to log in and install the python-snappy package for the postgres user. First, log in as them:

sudo -i -u postgres 

If you get this error on logging in as the user:
rob@pg:~$ sudo -i -u postgres
sudo: unable to change directory to /var/lib/postgresql: No such file or directory

you’ll need to create the user’s home directory. First, log out of the postgres user, then check the home directory:
rob@pg:~$ getent passwd barman
postgres:x:116:122:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash

then create it:
sudo mkdir -p /var/lib/postgresql
sudo chown postgres:postgres /var/lib/postgresql

Then once logged in as them, install the package:

pip install python-snappy

Step 11: Create backup service

We want the schedule to run every day, so we’ll create three systemd files. The first two are a backup script and service, the third a timer to trigger it. Firstly, we’ll check the home directory of the postgres user:

postgres@pg:~$ getent passwd barman
barman:x:118:123:Backup and Recovery Manager for PostgreSQL,,,:/var/lib/barman:/bin/bash

We can see that this it’s /var/lib/barman. If yours is different adjust these scripts. We need to use the absolute path because they won’t be expanded when running as a service. Create this file with sudo nano ~postgres/backup-script.sh, obviously substituting your S3 bucket, Healthchecks.io UUID and retention policy. We’re using peer authentication to allow the postgres user to sign in without a password:

#!/bin/bash

# Variables
BACKUP_DIR="/var/lib/postgresql/backup"
DATE_SUFFIX=$(date +%F_%H-%M-%S)
LOG_FILE="$BACKUP_DIR/barman_backup_log_$DATE_SUFFIX.txt"
S3_BUCKET="s3://<container_name>/barman"
HEALTHCHECK_URL="https://hc-ping.com/<UUID>"
SERVER_NAME="pg"
RETENTION_POLICY="RECOVERY WINDOW OF 30 DAYS"  # Adjust the retention policy as needed
RETAIN_LOG_DAYS=7

# create backup temp dir if it doesnt exist
mkdir -p $BACKUP_DIR

# Redirect all output to log file
exec > "$LOG_FILE" 2>&1

# Function to send log to healthchecks.io
send_log() {
    local url="$1"
    curl -fsS --retry 3 -m 10 -X POST -H "Content-Type: text/plain" --data-binary "@$LOG_FILE" "$url"
}

# Perform backup with Barman
barman-cloud-backup -v --cloud-provider aws-s3 --snappy -d postgres --port 1234 "$S3_BUCKET" "$SERVER_NAME" || {
    send_log "$HEALTHCHECK_URL/fail"
    exit 1
}

# Delete old backups according to retention policy
barman-cloud-backup-delete --cloud-provider aws-s3 --retention-policy "$RETENTION_POLICY" "$S3_BUCKET" "$SERVER_NAME" || {
    send_log "$HEALTHCHECK_URL/fail"
    exit 1
}

# Notify healthchecks.io of success and send log
send_log "$HEALTHCHECK_URL"

# Finally, delete old log files in BACKUP_DIR
find "$BACKUP_DIR" -type f -name 'barman_backup_log_*.txt' -mtime +$RETAIN_LOG_DAYS -exec rm -f {} \;

Make sure that the postgres user owns it and it’s executable:

sudo chown -R postgres: ~postgres/backup-script.sh
sudo chmod +x ~postgres/backup-script.sh

Create this file as /etc/systemd/system/barman-cloud-backup.service:

[Unit]
Description=Barman Cloud Backup Service

[Service]
Type=oneshot
ExecStart=/var/lib/postgresql/backup-script.sh
User=postgres

Test the timer with sudo systemctl start barman-cloud-backup. You can check the status using systemctl too – although you’ll need to do it from a second terminal as the service is non-forking. Here we can see that the service is running:

rob@pg:~$ sudo systemctl status barman-cloud-backup
● barman-cloud-backup.service - Barman Cloud Backup Service
     Loaded: loaded (/etc/systemd/system/barman-cloud-backup.service; static)
     Active: activating (start) since Tue 2023-11-07 23:34:26 UTC; 16s ago
   Main PID: 1143268 (backup-script.s)
      Tasks: 2 (limit: 2220)
     Memory: 50.7M
        CPU: 10.325s
     CGroup: /system.slice/barman-cloud-backup.service
             ├─1143268 /bin/bash /var/lib/postgresql/backup-script.sh
             └─1143271 /usr/bin/python3 /usr/bin/barman-cloud-backup -v --cloud-provider aws-s3 --snappy -d postgres --port 1234 s3://<container_name>/barman pg

Nov 07 23:34:26 pg systemd[1]: Starting Barman Cloud Backup Service...

We can check the log file too:

rob@pg:~$ sudo ls -ltr ~postgres/backup
total 36
-rw-r--r-- 1 postgres postgres 36296 Nov  7 23:48 barman_backup_log_2023-11-07_23-34-25.txt
rob@pg:~$ sudo cat ~postgres/backup/barman_backup_log_2023-11-07_23-34-25.txt
2023-11-07 23:34:26,705 [1143271] INFO: Found credentials in shared credentials file: ~/.aws/credentials
2023-11-07 23:34:27,263 [1143271] INFO: Starting backup '20231107T233427'
2023-11-07 23:34:33,420 [1143271] INFO: Uploading 'pgdata' directory '/mnt/postgres/postgresql/15/main' as 'data.tar.gz'
2023-11-07 23:35:04,095 [1143316] INFO: Upload process started (worker 1)
2023-11-07 23:35:04,097 [1143315] INFO: Upload process started (worker 0)
2023-11-07 23:35:04,213 [1143316] INFO: Found credentials in shared credentials file: ~/.aws/credentials
2023-11-07 23:35:04,220 [1143315] INFO: Found credentials in shared credentials file: ~/.aws/credentials
2023-11-07 23:35:04,352 [1143316] INFO: Uploading 'barman/pg/base/20231107T233427/data.tar.gz', part '1' (worker 1)
2023-11-07 23:35:35,917 [1143315] INFO: Uploading 'barman/pg/base/20231107T233427/data.tar.gz', part '2' (worker 0)
2023-11-07 23:36:03,578 [1143316] INFO: Uploading 'barman/pg/base/20231107T233427/data.tar.gz', part '3' (worker 1)
...

Eventually, the backup will complete and we can check it in Healthchecks.io. We can also use barman-cloud-backup-list to list the backups:

rob@pg:~$ barman-cloud-backup-list s3://<container>/barman pg
Backup ID           End Time                 Begin Wal                     Archival Status  Name                
20231023T132628     2023-10-23 13:33:25      000000010000004E00000060                                           
20231103T130531     2023-11-03 13:22:11      000000010000005200000081                                           
20231103T135700     2023-11-03 14:11:59      000000010000005200000083                                           
20231107T211340     2023-11-07 21:28:10      0000000100000052000000B1                                           
20231107T230140     2023-11-07 23:12:41      0000000100000052000000B9                                           
20231107T231341     2023-11-07 23:22:42      0000000100000052000000BB                                           
20231107T234029     2023-11-07 23:48:10      0000000100000052000000C1                                           

Step 12: Configure barman-cloud-backup to run on a schedule

Create the timer as /etc/systemd/system/barman-cloud-backup.timer

[Unit]
Description=Run Barman Cloud Backup every 6 hours

[Timer]
OnCalendar=*-*-* 00/6:00:00
Persistent=true

[Install]
WantedBy=timers.target

Install the timer with:

sudo systemctl enable barman-cloud-backup.timer
sudo systemctl start barman-cloud-backup.timer

Step 13: Configure PostgreSQL to use barman-wal-cloud-archive to archive WAL files to S3

Barman uses WAL archives for a restore. By configuring PostgreSQL to ship WAL archives directly to S3, we can achieve almost no loss of data on failure. We’ll do this by setting the archive_command and archive_mode configuration item in /etc/postgresql/15/main/postgresql.conf to the following values:

archive_mode = on
archive_command = 'barman-cloud-wal-archive --snappy s3://<container_name>/barman pg %p'

archive_mode tells PostgreSQL to process completed archive files with the archive_command. That means when the archive file completes, it is uploaded to S3

Step 14: Verify your backup works

I’ve written a whole article on how to restore from a barman backup – or you could check out the barman-cloud-restore documentation.

And that’s it! Check healthchecks.io for exceptions, check your S3 storage costs, and periodically test a restore!

Migrate your TimescaleDB from timescale.com to self hosted

I’ve been using timescale.com for about a year, but it’s quite expensive for a hobbyist (their cheapest plan in the UK is about $50/month), so I thought i’d try and implement something cheaper. I know i won’t get the cool automatic DR failover or the sexy continuous backups – but it’s not really mission critical if i lose a small amount of the history of my home energy consumption. Timescale publish some instructions, but they weren’t complete, and didnt work for me.

  1. Step 1 – install base OS
  2. Step 2: install PostgreSQL and move data to other drive (and change the port)
  3. Step 3: Install TimescaleDB
  4. Step 4: back up your existing DB
  5. Step 5: Create roles in the database
  6. Step 6: actually restore the data
  7. Step 7: Allow external network connections
  8. Next steps

Step 1 – install base OS

I use linode. I chose a Linode 2GB which comes in at around $12/month for 1 shared CPU and 2GB ram. This is about double what i had on timescale, which was 0.5 CPU. I added a 50GB storage account, which woudl allow me to rebuild my linode without worrying about losing my database data, bringing my total to $17/month – but this isnt necessary.

I installed Ubuntu 22.04 LTS, and set up default automatic updates, disable SSH password auth etc. using my standard setup scripts. I decided to use ZFS as the file system for the data drive to take advantage of the native error checking capabilities. It does have some performance implications, but for a single drive with a relatively small database, i think this is an acceptable tradeoff, especially when compared to the significant performance hit of BTFRS. I called the additional volume postgres, then mounted this at /mnt/postgres

First, install ZFS, create ZFS pool,

sudo apt-get install zfsutils-linux
sudo zpool create postgres_storage /dev/disk/by-id/scsi-0Linode_Volume_postgres

If the volume was previously used for something else, ZFS will warn you:
/dev/disk/by-id/scsi-0Linode_Volume_postgres contains a filesystem of type 'ext4'
You’ll need to erase the existing file system before creating the new ZFS pool. Obviously this is destructive and you’ll lose all the data on the drive:
sudo wipefs --all --force /dev/disk/by-id/scsi-0Linode_Volume_postgres

Create a new ZFS dataset and mount it:

sudo zfs create postgres_storage/data
sudo zfs set mountpoint=/mnt/postgres postgres_storage/data

There’s no need to modify /etc/fstab as ZFS will automatically manage mounting of datasets within a pool at boot time.

Step 2: install PostgreSQL and move data to other drive (and change the port)

Next, i had to install PostgreSQL and TimescaleDB. First, install PostgreSQL itself. Timescale’s latest supported version is 15. I had to change the instructions slightly from the PostgreSQL website because they use the legacy keystore.

# crate repository info
sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Import the repository signing key - note this instruction is different than the PostgreSQL website.
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmour -o /etc/apt/trusted.gpg.d/postgres.gpg

# fix permissions on the new key
sudo chmod 644 /etc/apt/trusted.gpg.d/*.gpg

# Update the package lists:
sudo apt-get update

# Install version 15 PostgreSQL:
sudo apt-get -y install postgresql-15

ok great! Now lets move the database storage to our new ZFS volume. First, i need to stop the service:

sudo systemctl stop postgresql

then open the config file to edit:

sudo nano /etc/postgresql/15/main/postgresql.conf

I also want to change the port that PostgreSQL is on. So find the port line and edit it:

port = 15433

and move the data by updating the data_directory property:

data_directory = '/mnt/postgres/postgresql/15/main'

Finally, move the database, update permissions and restart PostgreSQL:

sudo cp -ra /var/lib/postgresql /mnt/postgres/
sudo chown -R postgres:postgres /mnt/postgres/
sudo chmod -R 700 /mnt/postgres/
sudo systemctl start postgresql

Verify the new location:

rob@server:~$ sudo -u postgres psql -c "show data_directory;"
          data_directory          
----------------------------------
 /mnt/postgres/postgresql/15/main
(1 row)

If there are any problems, check the logs with sudo cat /var/log/postgresql/postgresql-15-main.log

Delete the old storage so that we don’t get confused:

sudo rm -r /var/lib/postgresql/15/main

To make it easy to remember the port (i.e. so that i dont need to set it every time), i set it as an export:

echo "export PGPORT=15433" >> ~/.bashrc 
source ~/.bashrc

Step 3: Install TimescaleDB

First, log in to the new server, then add the TimescaleDB repo:

echo "deb https://packagecloud.io/timescale/timescaledb/$(lsb_release -i -s | tr '[:upper:]' '[:lower:]')/ $(lsb_release -c -s) main" | sudo tee /etc/apt/sources.list.d/timescaledb.list

#fix permissions
sudo chmod 644 /etc/apt/sources.list.d/*.list

Then add the TimescaleDB GPG key:

wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/timescaledb.gpg

# fix permissions
sudo chmod 644 /etc/apt/trusted.gpg.d/*.gpg

Update and install:

sudo apt update
sudo apt install timescaledb-2-postgresql-15

The extension needs to be preloaded, so edit the config file and add it. Assuming there are no other preloaded extensions, just run this:

echo "shared_preload_libraries = 'timescaledb'" >> /etc/postgresql/15/main/postgresql.conf

To finish off, run timescaledb-tune to set up the extension:

sudo timescaledb-tune

Increase max_locks_per_transaction to 1024 in config file /etc/postgresql/*/main/postgresql.conf. First, uncomment it, if commented out:

sudo sed -i.bak -E '/^[[:space:]]*#?[[:space:]]*max_locks_per_transaction[[:space:]]*=[[:space:]]*[0-9]+/s/^([[:space:]]*)#?/\1/' /etc/postgresql/*/main/postgresql.conf

Then set the value to 1024:

sudo sed -i.bak -E '/^[[:space:]]*max_locks_per_transaction[[:space:]]*=[[:space:]]*[0-9]+/s/[0-9]+/1024/' /etc/postgresql/*/main/postgresql.conf

restart PostgreSQL with sudo systemctl restart postgresql

If you have any other extensions installed, be sure to install them now. For example, i also have PostGIS installed, so i followed the instructions on the PostGIS website – it uses the same repo as PostgreSQL so i just needed to install the package with sudo apt-get install postgresql-15-postgis-3 and i was done.

Step 4: back up your existing DB

This part is pretty easy. Stop any processes which are using the database so you dont lose data. Then run this command, substituting your own details for -h, -p and –U. I believe tsdb is the default database name on timescale.com but check yours. Do this from the new server – the slowest part of the process is the restore, so we want the files locally. That also lets you redo the restore if it fails without incurring ingress costs.

pg_dump -Fd -j 1 -h c99aarsjk6.b099as2kb2.tsdb.cloud.timescale.com -p 15433 -U tsdbadmin -d tsdb -f _dump

I found that trying to run more than one job in parallel just failed with memory errors. I ended up with about 500GB of data for a ~2,500GB database.

Step 5: Create roles in the database

This was more tricky. You have to run a few more magic steps on the new server.

Timescale.com doesn’t let you dump roles because roles are cluster-wide. But if you’re migrating from your own server, you can do it with the command pg_dumpall --roles-only -U postgres_admin -h localhost -f roles.sql
You’ll need to filter roles.sql to only include the roles you’re interested in – but it’s much easier.

We need to database and roles i needed as pg_restore doesn’t create roles, only assigns privileges. First, start psql and connect to my hosted timescale.com database:

psql -h c99aarsjk6.b099as2kb2.tsdb.cloud.timescale.com -p 15433 -U tsdbadmin -d tsdb

You can find your roles with the \du command e.g.

sdb=> \du
   Role name      |            Attributes             |    Member of    
------------------+-----------------------------------+------------------
 conditionseditor | Cannot login                      | {}               
 functionrestapi  |                                   | {conditionseditor}
 functionwriter   |                                   | {conditionseditor}
 postgres         | Superuser, Create role, Create DB,| {}               
                  | Replication, Bypass RLS           |                  
 postgres_admin   | Superuser, Create DB              | {}               
 tsdbadmin        |                                   | {}               
 tsdbexplorer     | Cannot login                      | {}               

You’ll need to create these roles and add them together. Naturally, you should use long, complex passwords. To connect to the new database, create a postgres_admin user with a password. We do this by connecting as the postgres user:

sudo -u postgres psql

then create the user with a password:

CREATE ROLE postgres_admin;
ALTER ROLE postgres_admin WITH SUPERUSER INHERIT NOCREATEROLE CREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'supersecretpassword';

Quit with \q.

Create a .pgpass file to store the password for login and set permissions on it to secure it. The docs say you can use * for hostname, port, database or username.

echo "localhost:*:*:postgres_admin:supersecretpassword" | tee ~/.pgpass
chmod 600 ~/.pgpass

We can then connect to the database using psql and our new postgres_admin user. You need to use -h so that you can specify -U.

psql -h localhost -U postgres_admin postgres

then create the roles:

CREATE ROLE conditionseditor WITH NOLOGIN;
CREATE ROLE functionrestapi WITH LOGIN PASSWORD '<pw_here>';
CREATE ROLE functionwriter WITH LOGIN PASSWORD '<pw_here>';
GRANT conditionseditor TO functionrestapi;
GRANT conditionseditor TO functionwriter;
CREATE ROLE tsdbadmin WITH LOGIN PASSWORD '<pw_here>';
CREATE ROLE tsdbexplorer NOLOGIN;

Don’t forget the admin users (tsdb… etc.).

Next, create your database, switch to it, and activate the timescaledb_pre_restore which does some magic. Inside psql:

CREATE DATABASE tsdb;
\c tsdb
CREATE EXTENSION IF NOT EXISTS timescaledb;
SELECT timescaledb_pre_restore();

then quit with \q.

Be sure to create the database with the same extension version as your current database server. Check the version with \dx timescaledb then use CREATE EXTENSION IF NOT EXISTS timescaledb VERSION 'xxx'; where xxx is the version in your current environment.

Step 6: actually restore the data

This step takes ages. I found that i need to avoid parallelism or i received referential integrity errors. Here’s the command which worked – obviously from the new server, as thats where i backed up to before. I do this using tmux in case my SSH connection drops:

pg_restore -Fd -j1 -h localhost -U postgres_admin -d tsdb -S postgres_admin --disable-triggers --exit-on-error --verbose _dump

You need the --disable-triggers and if you have that, you also need the -S flag to specify the superuser to run the disable commands as. We add --exit-on-error so that we dont lose track of things. When finished, log in to psql and run the post-restore script and update statistics using ANALYZE – this last step took a while too:

SELECT timescaledb_post_restore();
ANALYZE;

Step 7: Allow external network connections

By default, PostgreSQL doesn’t allow external connections. We can test that by using our connection string from another machine e.g.:

 ~  psql "postgres://functionwriter:<password>@server.example.com:15433/tsdb?sslmode=require"
psql: error: connection to server at "server.example.com" (2606:2800:220:1:248:1893:25c8:1946), port 15433 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
connection to server at "server.example.com" (1.2.3.4), port 15433 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?

To do this, on the server, we need to adjust postgresql.conf to listen on our external addresses, and update pg_hba.conf to accept authentication requests from them too.

Set listen_addresses to '*'. First, uncomment the line listen_addresses:

sudo sed -i.bak -E '/^[[:space:]]*#?[[:space:]]*listen_addresses[[:space:]]*=[[:space:]]*/s/^#//' /etc/postgresql/*/main/postgresql.conf

Then, set value to '*'

sudo sed -i.bak -E "/^[[:space:]]*listen_addresses[[:space:]]*=[[:space:]]*/s/=.*/= '*'/" /etc/postgresql/*/main/postgresql.conf

Next, allow scram-sha-256 authentication from 0.0.0.0/0 for IPv4 and ::/0 for IPv6 we add them explicitly to the config file:

echo -e "# Allow all IP addresses with scram-sha-256 authentication\n# IPv4\nhost    all             all             0.0.0.0/0               scram-sha-256\n# IPv6\nhost    all             all             ::/0                    scram-sha-256" | sudo tee -a /etc/postgresql/*/main/pg_hba.conf

Then restart PostgreSQL:

sudo systemctl restart postgresql

I can test the connection from a different machine:

 ~  psql "postgres://functionwriter:<password>@server.example.com:15433/tsdb?sslmode=require"                                                                            ok 
psql (16.0, server 15.4 (Ubuntu 15.4-2.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

tsdb=> 

thats pretty much it! Now i just need to update the connection string in my apps, and i’m good to go.

Next steps

I should probably sort a few other things out:

  • backups – doesnt come for free any more. I need to figure something out, probably using pg_dump again.
  • Install fail2ban with a custom filter for postgresql
  • Automated updates – typically, i’d like new minor versions of TimescaleDB to be installed. Can i automate the psql command?
  • Automated updates – how to manage major version upgrades?
  • Updates to PostgreSQL – e.g. PostgreSQL 16 just came out.
  • of course i set up a firewall on linode, but can i do more?

Using an ephemeral MongoDB single node replicaset in a devcontainer or codespace

I love using devcontainers to manage my development environment. They make it super easy to ensure a consistent development stack is in place. Recently i started developing against a MongoDB instance. For node.js, i use mongodb-unit to spin up a standalone server on the local client. But there’s no equivalent package for Python.

Although there are lots of posts on stackoverflow about configuring a single node replicaset using a healthcheck, and there’s even an example given by a MongoDB employee, they didnt work for me. When setting up the server to use authentication, it also needs a keyfile, which has to be generated and secured in a specific way or you get this error:

BadValue: security.keyFile is required when authorization is enabled with replica sets

Without authentication, i was unable to create databases and collections, but the username and password in MONGODB_INITDB_ROOT_USERNAME and MONGODB_INITDB_ROOT_PASSWORD didnt get created automatically in the admin database:

{"t":{"$date":"2023-09-28T20:59:21.342+00:00"},"s":"I",  "c":"ACCESS",   "id":5286307, "ctx":"conn30","msg":"Failed to authenticate","attr":{"client":"127.0.0.1:33216","isSpeculative":true,"isClusterMember":false,"mechanism":"SCRAM-SHA-256","user":"root","db":"admin","error":"UserNotFound: Could not find user \"root\" for db \"admin\"","result":11,"metrics":{"conversation_duration":{"micros":221,"summary":{"0":{"step":1,"step_total":2,"duration_micros":206}}}},"extraInfo":{}}}

Mongo has a very clear, step by step instructions to set up a replicaset, but it requires a lot of manual steps. So i decided to automate it with a bash script, and then trigger this as a healthcheck. Here are the steps i followed:

My requirements are for an ephemeral database – that means the data is destroyed when the container is removed. To persist the data, you need to map the container folder /data/db to a local folder using a volume in the docker-compose file.

  1. Planning it out
  2. Create a script to initialize the replicaset and create the root user
  3. Create a Dockerfile to generate the replica keyfile and inject my initialisation script
  4. Create a docker-compose.yaml file to build my app landscape
  5. Create devcontainer.json to bring it all together
  6. Accessing from the local machine

Planning it out

Here’s what we’re building. 4 files and at the end, MongoDB will be running and we can connect to it from both our local machine and from inside the devcontainer.

<project workspace>
  |
  |-- .devcontainer
  |        |-- devcontainer.json
  |        |-- docker-compose.yaml
  |        |-- Dockerfile
  |        |-- mongodb_healthcheck.sh
  |
  |-- <other code and folders>

 

Create a script to initialize the replicaset and create the root user

The script is intended to be idempotent i.e. you can run it several times and it will only return 0 (success) when the replicaset is up and running, and the username/password works:

[Start]
  |
[check_replica_set]
  |
  |--[Yes]--->[check_authentication]--->[Yes]--->[Exit 0]
  |                   |
  |                   |--[No]
  |                   |
  |               [create_root_user]
  |                   |
  |                   |--[Success]--->[Exit 1]
  |                   |
  |                   |--[Failure]--->[Exit 1]
  |
  |--[No]--->[initialize_replica_set]
                      |
                      |--[Already Initialized or Success]--->[Exit 1]
                      |
                      |--[Failure]--->[Exit 1]

Here’s the full script:

#!/bin/bash

# Function to check if MongoDB replica set is ready
check_replica_set() {
    echo "init_script: Checking MongoDB replica set..."
    local is_master_result=$(mongosh --quiet --eval 'rs.isMaster().ismaster')
    echo "Result of rs.isMaster().ismaster: $is_master_result"  # Display the result

    if echo "$is_master_result" | grep -q true; then
        echo "init_script: MongoDB replica set is ready."
        return 0
    else
        echo "init_script: MongoDB replica set NOT ready."
        return 1
    fi
}

# Function to initialize MongoDB replica set if necessary
initialize_replica_set() {
    echo "init_script: Starting to initialise replica set."
    local result=$(mongosh --quiet --eval 'rs.initiate()')
    echo "Result of rs.initiate(): $result"  # Display the result

    if [[ "$result" == *"already initialized"* || "$result" == *'ok: 1'* || "$result" == *'"ok" : 1'* ]]; then
        echo "init_script: MongoDB replica set is already initialized or initialized successfully."
        exit 0
    else
        echo "init_script: Failed to initialize MongoDB replica set."
        exit 1
    fi
}

check_authentication() {
    local auth_result=$(mongosh -u "$MONGODB_INITDB_ROOT_USERNAME" -p "$MONGODB_INITDB_ROOT_PASSWORD" --quiet --eval "db.runCommand({ ping: 1 })")
    echo "Result of authentication: $auth_result"  # Display the result

    if echo "$auth_result" | grep 'ok' | grep -q '1'; then
        echo "init_script: Authentication successful."
        exit 0
    else
        echo "init_script: Authentication failed."
        return 1
    fi
}

# Function to create MongoDB root user
create_root_user() {
    echo "init_script: Creating MongoDB root user..."
    output=$(mongosh <<EOF
    admin = db.getSiblingDB("admin")
    result = admin.createUser(
      {
        user: "$MONGODB_INITDB_ROOT_USERNAME",
        pwd: "$MONGODB_INITDB_ROOT_PASSWORD",
        roles: [ { role: "root", db: "admin" } ]
      }
    )
    printjson(result)
EOF
    )
    echo "Result of createUser: $output"  # Display the result

    if echo "$output" | grep 'ok' | grep -q '1'; then
        echo "init_script: MongoDB root user created successfully."
        exit 0
    else
        echo "init_script: Failed to create admin user."
        exit 1
    fi
}

# Check if MongoDB replica set is ready and initialize if needed
if check_replica_set; then
    if check_authentication; then
        exit 0
    else
        create_root_user
    fi
else
    initialize_replica_set
fi

Create a Dockerfile to generate the replica keyfile and inject my initialisation script

I wanted to use the existing mongo image for this, and perform the minimum number of changes. So i created a simple Dockerfile which creates a new keyfile, and puts the init script in. I then trigger the init script as a ‘healthcheck’ meaning it’ll get automatically triggered by Docker after 30 seconds, and then at 10 second intervals, for up to 10,000 seconds (!!!):

FROM mongo

# Initiate replica set
RUN openssl rand -base64 756 > "/tmp/replica.key"
RUN chmod 600 /tmp/replica.key
RUN chown 999:999 /tmp/replica.key

# Copy the health check script to the container
COPY mongodb_healthcheck.sh /usr/local/bin/

# Set execute permissions for the script
RUN chmod +x /usr/local/bin/mongodb_healthcheck.sh

# Define the health check command
HEALTHCHECK --interval=10s --timeout=5s --start-period=30s --retries=100 CMD /usr/local/bin/mongodb_healthcheck.sh

CMD ["mongod", "--replSet", "rs0", "--bind_ip_all", "--keyFile", "/tmp/replica.key", "--auth"]

Create a docker-compose.yaml file to build my app landscape

This is pretty simple. I wanted to use the Microsoft Python 3.11 devcontainer, and have it access Mongo in my MongoDB container. I want to set the root username and password here too. I created a network to allow the pods to talk to each other:

version: '3'
services:
  app:
    image: mcr.microsoft.com/devcontainers/python:3.11
    command: ["sleep", "infinity"]
    volumes:
      - ..:/workspace:cached
    ports:
      - "5000:5000"
    environment:
      - "PYTHONBUFFERED=1"
      - "PYTHONUNBUFFERED=1"
    networks:
      - mynetwork

  mongodb:
    build:
      dockerfile: ./Dockerfile
    ports:
      - "27017:27017"
    environment:
      - MONGODB_INITDB_ROOT_USERNAME=root
      - MONGODB_INITDB_ROOT_PASSWORD=example
    hostname: mongodb
    networks:
      - mynetwork

networks:
  mynetwork:

Create devcontainer.json to bring it all together

devcontainer.json is an open standard. The only bit that tripepd me up here was the need to be explicit about which service was exposing which port, which you do by adding the service name (from the docker-compose.yaml file) in front of it:

{
    "name": "Python 3.11 + MongoDB",
    "dockerComposeFile": "docker-compose.yml",
    "workspaceFolder": "/workspace",
    "service": "app",
    "features": {
        "ghcr.io/devcontainers/features/node:1": {
            "version": "latest"
        },
        "ghcr.io/devcontainers-contrib/features/poetry:2": {}
    },
    "forwardPorts": [
        "app:5000",
        "mongodb:27017"
    ],
    "customizations": {
        "vscode": {
            "settings": {
                "python.defaultInterpreterPath": "/usr/local/bin/python",
                "python.linting.pylintEnabled": false,
                "python.linting.flake8Enabled": true,
                "python.linting.enabled": true,
                "editor.detectIndentation": false,
                "editor.tabSize": 4
            },
            "extensions": [
                "ms-python.python",
                "ms-python.flake8",
                "ms-python.vscode-pylance",
                "VisualStudioExptTeam.vscodeintellicode",
                "njpwerner.autodocstring",
                "GitHub.copilot",
                "GitHub.copilot-chat",
                "GitHub.copilot-labs"
            ]
        }
    }
}

Accessing from the local machine

MongoSH will now connect from the local machine or devcontainer. Without a username/password you get quite limited access, but using mongosh -u root -p example it’ll connect just fine and you can administer the database using the account we created earler. If you just try to connect using MonoDB Compass however you’ll get this error:

getaddrinfo ENOTFOUND mongodb

This can be solved by adding directConnection=true to the connection string e.g.:

mongodb://root:example@127.0.0.1:27017/?directConnection=true&serverSelectionTimeoutMS=2000

How to: LetsEncrypt in standalone mode for Unifi on Ubuntu 20.04 LTS

This is an update of my previous post, now that cert-manager is more mature, and i’ve rebuilt my server on Ubuntu 20.04 (from 18.04).

  1. install certbot
  2. install script to update unifi certificate
  3. Test
  4. Issue full certificate
  5. Install cron jobs to automate renewal

Install certbot

Certbot installation instructions are at online of course but here’s a summary:

  1. Update package list:
    sudo apt update
  2. install:
    sudo apt install -y certbot

Create a new certificate using LetsEncrypt

We’re going to use standalone mode, and first we’ll get a test certificate just to validate that everything’s working (so that we don’t trigger LetsEncrypt’s rate limits).

  1. open port 80 in ufw:
sudo ufw allow http
  1. Test certificate issuance:
sudo certbot certonly --standalone -d <hostname> -n --test-cert --agree-tos -m <email>

You should see something like this:

Saving debug log to /var/log/letsencrypt/letsencrypt.log
Plugins selected: Authenticator standalone, Installer None
Obtaining a new certificate
Performing the following challenges:
http-01 challenge for <hostname>
Waiting for verification...
Cleaning up challenges

IMPORTANT NOTES:
 - Congratulations! Your certificate and chain have been saved at:
   /etc/letsencrypt/live/<hostname>/fullchain.pem
   Your key file has been saved at:
   /etc/letsencrypt/live/<hostname>/privkey.pem
   Your cert will expire on 2021-04-08. To obtain a new or tweaked
   version of this certificate in the future, simply run certbot
   again. To non-interactively renew *all* of your certificates, run
   "certbot renew"
 - Your account credentials have been saved in your Certbot
   configuration directory at /etc/letsencrypt. You should make a
   secure backup of this folder now. This configuration directory will
   also contain certificates and private keys obtained by Certbot so
   making regular backups of this folder is ideal.
  1. If that’s worked, close the firewall (sudo ufw deny http) and move on to the next step and install the certificate in unifi. Later, we’ll come back and get a ‘real’ (not staging) certificate.

Install certificate in unifi

I use an amazing certificate installation script from Steve Jenkins.

  1. Get the script:
wget https://raw.githubusercontent.com/stevejenkins/unifi-linux-utils/master/unifi_ssl_import.sh
  1. Edit the config settings in the script to add hostname, switch from Fedora/RedHat/CentOS to Debian/Ubuntu, enable LE_MODE, and disable key paths:
# CONFIGURATION OPTIONS
UNIFI_HOSTNAME=<hostname>   
UNIFI_SERVICE=unifi

# Uncomment following three lines for Fedora/RedHat/CentOS
# UNIFI_DIR=/opt/UniFi
# JAVA_DIR=${UNIFI_DIR}
# KEYSTORE=${UNIFI_DIR}/data/keystore

# Uncomment following three lines for Debian/Ubuntu
UNIFI_DIR=/var/lib/unifi
JAVA_DIR=/usr/lib/unifi
KEYSTORE=${UNIFI_DIR}/keystore

# Uncomment following three lines for CloudKey
#UNIFI_DIR=/var/lib/unifi
#JAVA_DIR=/usr/lib/unifi
#KEYSTORE=${JAVA_DIR}/data/keystore

# FOR LET'S ENCRYPT SSL CERTIFICATES ONLY
# Generate your Let's Encrtypt key & cert with certbot before running this script
LE_MODE=yes
LE_LIVE_DIR=/etc/letsencrypt/live

# THE FOLLOWING OPTIONS NOT REQUIRED IF LE_MODE IS ENABLED
# PRIV_KEY=/etc/ssl/private/hostname.example.com.key
# SIGNED_CRT=/etc/ssl/certs/hostname.example.com.crt
# CHAIN_FILE=/etc/ssl/certs/startssl-chain.crt
  1. copy to /usr/local/bin and make executable:
sudo cp unifi_ssl_import.sh /usr/local/bin/
sudo chmod +x /usr/local/bin/unifi_ssl_import.sh
  1. Run the script to import the certificate. Look for any errors:
sudo /usr/local/bin/unifi_ssl_import.sh
  1. Navigate to your server (https://<hostname>:8443). If it worked, you’ll see a warning that the certificate isnt trusted, but when you examine the cert, it’s issued by a ‘fake’ Lets Encrypt issuer, for example:
Certificate showing a chain back to a root called 'Fake LE Intermediate X1'

Get the real LetsEncrypt certificate

Simply run the same certbot command as before, but leave off the --test-cert flag, and add the --force-renew flag to force it to replace the (unexpired) test certificate:

sudo certbot certonly --standalone -d <hostname> -n --force-renew --agree-tos -m <email>

and rerun the installation script:

sudo /usr/local/bin/unifi_ssl_import.sh

Close the browser window and reopen it, then navigate to your server again. You should now see the valid certificate:

A trusted certificate chain for the host

Automate renewal and issuance

Set up a crontab to renew the cert. Pick a randomish time. It should run every day – if the certificate is still valid, it’ll just skip

  1. load crontab – you may be asked to pick an editor – i suggest nano:
    sudo crontab -e
  2. add the schedule – use crontab guru if you arent familiar with crontab schedule expressions, and set up tasks to:
    1. request a new certificate, and
    2. install the updated certificate. I chose a time just over an hour after certificate issue.

It should look like this:

# renew any certificates due to expire soon at 05:20 each day
20 5 * * * /usr/bin/certbot renew --standalone -n --agree-tos -m <email> --pre-hook 'ufw allow http' --post-hook 'ufw deny http'
# install any updated certificates at 06:29 each day
29 6 * * * /usr/local/bin/unifi_ssl_import.sh

The --pre-hook and --post-hook commands tell UFW to open up port 80 and then close it again afterwards.