Create a VPC Connection between Azure and Timescale

This week, I was helping a customer of mine to migrate from an on-prem database to a cloud-hosted TimescaleDB. Since the customer is using Azure, they wanted to host their new TimescaleDB in Azure. Timescale offers to host the database on Azure and also allows a private connection via VPC (virtual private cloud) peering. This allows customers to peer a VNet with the VNet of Timescale and therefore only allow traffic coming from this VNet. As a result, it is not possible to connect to the database over the internet.

Unfortunately, I had some problems creating the VPC peering, and therefore want to describe how I achieved it.

Introduction

There is an article about Setting up a VPC on Azure in the Timescale knowledge base. I only found this article after I emailed the support (which replied very fast). After trying to follow this guide, I realized that some commands were not working in the Azure CLI and also that the commands were more complicated than needed. With that, I mean that if a query returned a value, instead of writing this value into a variable, the guide stated to find the variable from the output and assign it to a variable by hand.

If you follow this guide, you will see what is not working on the official documentation and you also won’t need to assign any output values by hand. They will be assigned automatically.

Let’s get started

This article assumes that you already have a TimescaleDB with VPC running in Azure. If not, log into your Timescale account and create a new VPC in the desired Azure region. Wait until the state of the VPC is active and then create a new database with the previously created VPC as its location. Note that you won’t be able to access this database until you finish the VPC peering.

All of the following guide will be done in the command line using PowerShell, the Azure CLI and Aiven CLI on Ubuntu. It is possible to create a VPC peering in the Timescale portal, but this won’t work.

Create an Application in your AAD

Log into your Azure account using the Azure CLI and set the subscription you want to use (this is only necessary if your account has multiple subscriptions).

Create an application in your AAD with the following command. You can use whatever display name you prefer.

This app uses the –sign-in-audience AzureADandPersonalMicrosoftAccount flag which will enable multiple tenants to log into this application. However, only your tenant has the credentials for the authentication.

Here I found the biggest problem with the official guide. This uses the –available-to-other-tenants flag, which to my knowledge does not exist and therefore won’t work. Make sure to use the –sign-in-audience flag instead.

Create a Service Principal

Next, create a service principal for the previously created AAD application.

This service principal will be used later to peer your VNet with the VNet of Timescale.

Set a Password for the Application

You will use the previously created AAD application to log into Azure. Therefore, you will need its password. You can retrieve the password by simply resetting the current one.

This command will give you a warning to be careful because the output contains credentials. You can ignore this warning since that’s exactly what we wanted to do.

Gather Information about your VNet, Resource Group, and Subscription

This step assigns the id of your VNet, the resource group it is located in, your subscription id, and your tenant id into variables. Make sure to replace <YOUR_PEERING_NAME> with a name of your choosing and enter your VNet name in the first line of the following code:

Assign the Network Contributor Role to the Service Principal

The previously created service principal will be used to create the peering from your VNet to the Timescale VNet. Therefore, the service principal will need the Network Contributor Role. The following commands will query the id of this role and then assign it to the service principal.

The –scope flag limits the role assignment to the VNet resource.

Create a Service Principal for the TimescaleDB application

The Timescale Tenant on Azure contains an application that can be used to create a peering from your Timescale VPC project to your VNet. To be able to do that, the Timescale AD application needs a service principal in your subscription. You can create this service principal with the following code.

Note that you need the “Application Administrator” permission to be able to execute the code above. If you do not have this permission, you will get the following error message:

Create a Custom Role for the Network Peering

The Timescale application needs the “Microsoft.Network/virtualNetworks/peer/action” permission to create the network peering. Since you always want to give the least amount of permissions, it is recommended to create a custom role that contains only this permission. To make the creation of the role in the command line easier, save the following code into a JSON file. Replace <YOUR_SUBSCRIPTION_ID> with your actual subscription id and optionally, change the name of the role.

Next, execute a role definition create and pass the JSON file as a parameter:

Lastly, assign this new custom role to the previously created Timescale service principal:

Create an Access Token for the Aiven CLI

Log into your account in the Timescale portal and select your account icon on the top right, switch to the Authentication tab, and then click on “Generate token”

This opens a new window where you can enter a name for your access token and configure its expiration time.

After you clicked on generate token, the token is generated.

Make sure to copy the access token since this is the only time that you have access to the token.

Additionally, I would recommend you enable two-factor authentication in your account settings.

Install the Aiven CLI

The Aiven CLI installation needs Python (pip) to be installed on your system. To install it, use the following code:

Next, install the Aiven CLI.

Use the Aiven CLI to log into your Timescale account. Make sure to change <YOUR_USER> with your Timescale user.

After you execute the login, pass your previously created access token.

Retrieve the VPC Project ID

works because only 1 VPC per region –> replace timescale-azure-switzerland-north with your location if you are not using Switzerland North. The project ID is always 36 characters long, as far as I know

Create Peering Connection

The VPC project id and some of the previously created variables are now needed to create a peering connection from the Timescale VNet into your VNet. Use the following code to create the connection:

Note that the input variables starting with user_ must be lowercase since the Aiven API can only handle lower-case inputs.

Check the Peering Status and Assign Variables

Creating the peering connection might take some time but usually, it is only a couple of seconds. Before you proceed, make sure with the following command that the state of the connection is ACTIVE.

If the state is not ACTIVE, wait a bit and retry the command from above. If the state is INVALID_SPECIFICATION or REJECTED_BY_PEER, check that VNet you passed as a parameter exists and that the Timescale application was given the proper permissions. After you checked that, repeat the command from above and the peering connection should be created.

The output of this command is horrendous but you have to copy the value of to-tenant-id and to-network-id to the variables aiven_vnet_id and aiven_tenant_id. Make sure to replace the placeholder with your actual values.

The tenant Id is a GuId and the VNet id should look something like “/subscriptions/<SUBSCRIPTION_GUID}>/resourceGroups/…

Create the VNet peering from your VNet to your VPC

Log out of the Azure CLI and then log in with your service principal into your tenant and also into the Timescale tenant.

After you successfully logged into both tenants, create the VNet peering between your VNet and the Timescale VNet

You can check the state of your peering connection with the following command:

Right after you created the peering connection, the state will be APPROVED and after a couple of seconds, it should switch to PENDING_PEER.

Wait a bit and the peering state should switch to ACTIVE

If you follow the official documentation, note that the command to check the state is cut off and won’t work.

Test the Connection

With the peering connection in place, test if you can log into your Timescale database. Make sure that you are connecting from within your peered VNet, otherwise, the connection won’t work.

Note that I used pgAdmin to test the connection despite using Azure Data Studio at the beginning of this tutorial. The PostgreSQL extension of Azure Data Studio was constantly crashing and I could not get it working again.

Clean up your Resources

If you do not need your resources anymore, make sure to clean up everything you have created.

Delete the VNet Peering

First, delete the VNet peering with the following command.

Delete both Role Bindings

Next, retrieve the Ids of the role bindings and then delete both.

Delete the Custom Role

After you deleted the role binding, delete the previously created custom role.

If you used a different name for the custom role, make sure to replace “VnetPeerCreator” with your name.

Delete the Applications from your AAD

Lastly, delete both applications from your AAD.

Make sure that you have the “Application Administrator” permission to delete the app with the Aiven service principal.

Conclusion

This post showed how you can create a database on Timescale and how to create a private peering connection between your Azure subscription and the Timescale network. Setting up the VNet peering allows only connections from within your VNet to access the database. This approach enables you to let Timescale manage your database while it is not accessible over the internet and therefore should be quite secure.