Recover Azure PostgreSQL Flexible Server from a failed state `Encryption key is not accessible`

Introduction

In this document, we will go through the steps to recover an Azure PostgreSQL Flexible Server that is in a failed state due to the error Encryption key is not accessible. This error typically occurs when the encryption key used for data encryption is no longer accessible, which can happen for various reasons, such as key deletion or loss of access to the Key Vault.

In our environment, we use IaC based on Terraform to deploy Azure PostgreSQL Flexible Server and the required infrastructure. This document focuses only on the relevant steps to recover the server from the failed state, not on the IaC deployment itself.

When a PostgreSQL Flexible Server is in a failed state, it cannot be stopped, started, restarted, deleted, or modified, and its data cannot be accessed. In our environment, a PostgreSQL Flexible Server ends up in this state after a failed Terraform pipeline used to destroy the server.

Our architecture

To understand the issue and the recovery process, we need to understand our infrastructure.

The simplified graphical representation of our architecture is shown below:

architecture-beta
    group azure(cloud)[Azure subscription]
    group kv(server)[Key Vault] in azure

    service pg(database)[PostgreSQL Flexible Server] in azure
    service msi(server)[User assigned managed identity] in azure
    service ra(internet)[Role assignment] in azure
    service cmk(disk)[Customer managed key] in kv

    pg:R --> L:msi
    msi:B <-- T:ra
    ra:L --> R:cmk
    pg:B -[unwrap]-> T:cmk

In the architecture above, Terraform tries to destroy all of the resources shown. For some reason, the PostgreSQL Flexible Server fails to be destroyed. It enters a failed state as soon as any of the other resources is destroyed.

What to do when MSI is deleted

When the user assigned managed identity (MSI) is deleted, the PostgreSQL Flexible Server loses access to the customer managed key (CMK) stored in Azure Key Vault. This results in the server being unable to decrypt its data, leading to the error Encryption key is not accessible.

To recover from this state, we need to reconfigure the server to use either a newly created MSI or the system-assigned managed identity (if available). The steps to reconfigure the server to use the system-assigned managed identity are as follows:

# Set your resource variables
PG_RG="<postgres-resource-group>"
PG_NAME="<postgres-server-name>"
az postgres flexible-server identity update \
  --resource-group $PG_RG \
  --server-name $PG_NAME \
  --system-assigned Enabled

Once this command has executed successfully, we need to create a new role assignment that allows the system-assigned managed identity to access the customer managed key in Azure Key Vault. The steps to create a new role assignment are as follows:

# Set your resource variables
PG_RG="<postgres-resource-group>"
PG_NAME="<postgres-server-name>"
KV_RG="<keyvault-resource-group>"
KV_NAME="<keyvault-name>"

# 1. Get the Principal ID of the PostgreSQL system-assigned identity
PRINCIPAL_ID=$(az postgres flexible-server show \
  --resource-group $PG_RG \
  --name $PG_NAME \
  --query "identity.principalId" \
  --output tsv)

# 2. Get the full Resource ID of the Key Vault
KV_SCOPE=$(az keyvault show \
  --resource-group $KV_RG \
  --name $KV_NAME \
  --query "id" \
  --output tsv)

# 3. Create a new role assignment for the PostgreSQL system-assigned identity to access the Key Vault
az role assignment create \
  --role "Key Vault Crypto Service Encryption User" \
  --assignee-object-id $PRINCIPAL_ID \
  --assignee-principal-type ServicePrincipal \
  --scope $KV_SCOPE

Checking the state of the PostgreSQL Flexible Server

Allow a few minutes for the role assignment to propagate, then check the state of the PostgreSQL Flexible Server. If everything is configured correctly, the server should return to a successful state. The easiest way to check the state is to use the Azure portal or the Azure CLI:

# Set your resource variables
PG_RG="<postgres-resource-group>"
PG_NAME="<postgres-server-name>"

az postgres flexible-server show \
  --resource-group $PG_RG \
  --name $PG_NAME \
  --query "state" \
  --output tsv

What to do when the role assignment is deleted

When the role assignment for the managed identity is deleted, the PostgreSQL Flexible Server loses access to the customer managed key (CMK) stored in Azure Key Vault. This results again in the server being unable to decrypt its data, leading to the same error Encryption key is not accessible.

This situation is the easiest to recover from, as we only need to create a new role assignment for the server's system-assigned managed identity to access the customer managed key in Azure Key Vault. The steps to create a new role assignment are the same as described in the previous section.

# Set your resource variables
PG_RG="<postgres-resource-group>"
PG_NAME="<postgres-server-name>"
KV_RG="<keyvault-resource-group>"
KV_NAME="<keyvault-name>"

# 1. Get the Principal ID of the PostgreSQL system-assigned identity
PRINCIPAL_ID=$(az postgres flexible-server show \
  --resource-group $PG_RG \
  --name $PG_NAME \
  --query "identity.principalId" \
  --output tsv)

# 2. Get the full Resource ID of the Key Vault
KV_SCOPE=$(az keyvault show \
  --resource-group $KV_RG \
  --name $KV_NAME \
  --query "id" \
  --output tsv)

# 3. Create a new role assignment for the PostgreSQL system-assigned identity to access the Key Vault
az role assignment create \
  --role "Key Vault Crypto Service Encryption User" \
  --assignee-object-id $PRINCIPAL_ID \
  --assignee-principal-type ServicePrincipal \
  --scope $KV_SCOPE

Repeat the same steps as in Checking the state of the PostgreSQL Flexible Server to check the state of the PostgreSQL Flexible Server. If everything is configured correctly, the server should return to a successful state.

What to do when the customer managed key is deleted

When the customer managed key (CMK) is deleted, the PostgreSQL Flexible Server loses access to the encryption key used for data encryption. This results in the server being unable to decrypt its data, leading to the error Encryption key is not accessible.

To recover from this issue, one prerequisite must be met: your Azure Key Vault must have soft-delete enabled. If soft-delete is not enabled, the customer managed key is permanently deleted and cannot be recovered. The only option then is to open a ticket with Microsoft support and ask them to delete the PostgreSQL Flexible Server and all its data. If soft-delete is enabled, you can recover the deleted customer managed key from the Azure Key Vault.

You can recover the deleted customer managed key by using the Azure CLI or the Azure portal. The steps for the Azure CLI are as follows:

#!/usr/bin/env bash
set -euo pipefail

# 1. Set your PostgreSQL Flexible Server details
PG_RG="<your-resource-group>"
PG_NAME="<your-server-name>"

# 2. Fetch the primaryKeyURI from the PostgreSQL server
KEY_URI=$(az postgres flexible-server show \
  --resource-group "$PG_RG" \
  --name "$PG_NAME" \
  --query "dataEncryption.primaryKeyURI" \
  --output tsv)

if [[ -z "$KEY_URI" ]]; then
  echo "Error: No Customer-Managed Key (primaryKeyURI) found on server '$PG_NAME'."
  exit 1
fi

# 3. Parse the Vault Name and Key Name from the URI
# Expected URI format: https://<vault-name>.vault.azure.net/keys/<key-name>/<version>
VAULT_NAME=$(echo "$KEY_URI" | awk -F'[/.]' '{print $3}')
KEY_NAME=$(echo "$KEY_URI" | awk -F'/' '{print $5}')

echo "Found Key Vault : $VAULT_NAME"
echo "Found CMK Name   : $KEY_NAME"

# 4. Recover the soft-deleted key
echo "Recovering key '$KEY_NAME' from vault '$VAULT_NAME'..."
az keyvault key recover \
  --vault-name "$VAULT_NAME" \
  --name "$KEY_NAME"

echo "Recovery complete."

Allow a few minutes for the recovery to propagate, then check the state of the PostgreSQL Flexible Server. If everything is configured correctly, the server should return to a successful state. You can use the same steps as in Checking the state of the PostgreSQL Flexible Server to check the state of the server.

Conclusion

As we have seen, recovering an Azure PostgreSQL Flexible Server from a failed state due to the error Encryption key is not accessible involves understanding the underlying architecture and the relationships between the server, managed identities, role assignments, and customer managed keys. By following the steps outlined in this document, you can effectively recover your server and restore access to your data.

However, it is important to note this best practice: have soft-delete enabled on the Key Vault to avoid permanent loss of customer managed keys. If we lose access to the encryption key and the Key Vault does not have soft-delete enabled, or the retention period has expired, we cannot recover the PostgreSQL Flexible Server to a successful state.

Recover Azure PostgreSQL Flexible Server from a failed state Encryption key is not accessible