Create and manage Private Link for Azure Database for PostgreSQL - Single server using CLI

APPLIES TO: Azure Database for PostgreSQL - Single Server

Important

Azure Database for PostgreSQL - Single Server is on the retirement path. We strongly recommend that you upgrade to Azure Database for PostgreSQL - Flexible Server. For more information about migrating to Azure Database for PostgreSQL - Flexible Server, see What's happening to Azure Database for PostgreSQL Single Server?.

A Private Endpoint is the fundamental building block for private link in Azure. It enables Azure resources, like Virtual Machines (VMs), to communicate privately with private link resources. In this article, you will learn how to use the Azure CLI to create a VM in an Azure Virtual Network and an Azure Database for PostgreSQL Single server with an Azure private endpoint.

Note

The private link feature is only available for Azure Database for PostgreSQL servers in the General Purpose or Memory Optimized pricing tiers. Ensure the database server is in one of these pricing tiers.

Prerequisites

To step through this how-to guide, you need:

• An Azure Database for PostgreSQL server and database.

Azure Cloud Shell

Azure hosts Azure Cloud Shell, an interactive shell environment that you can use through your browser. You can use either Bash or PowerShell with Cloud Shell to work with Azure services. You can use the Cloud Shell preinstalled commands to run the code in this article, without having to install anything on your local environment.

To start Azure Cloud Shell:

Option	Example/Link
Select Try It in the upper-right corner of a code or command block. Selecting Try It doesn't automatically copy the code or command to Cloud Shell.
Go to https://shell.azure.com, or select the Launch Cloud Shell button to open Cloud Shell in your browser.
Select the Cloud Shell button on the menu bar at the upper right in the Azure portal.

To use Azure Cloud Shell:

1. Start Cloud Shell.

2. Select the Copy button on a code block (or command block) to copy the code or command.

3. Paste the code or command into the Cloud Shell session by selecting Ctrl+Shift+V on Windows and Linux, or by selecting Cmd+Shift+V on macOS.

4. Select Enter to run the code or command.

If you decide to install and use Azure CLI locally instead, this quickstart requires you to use Azure CLI version 2.0.28 or later. To find your installed version, run az --version. See Install Azure CLI for install or upgrade info.

Create a resource group

Before you can create any resource, you have to create a resource group to host the Virtual Network. Create a resource group with az group create. This example creates a resource group named myResourceGroup in the westeurope location:

az group create --name myResourceGroup --location westeurope

Create a Virtual Network

Create a Virtual Network with az network vnet create. This example creates a default Virtual Network named myVirtualNetwork with one subnet named mySubnet:

az network vnet create \
--name myVirtualNetwork \
--resource-group myResourceGroup \
--subnet-name mySubnet

Disable subnet private endpoint policies

Azure deploys resources to a subnet within a virtual network, so you need to create or update the subnet to disable private endpoint network policies. Update a subnet configuration named mySubnet with az network vnet subnet update:

az network vnet subnet update \
--name mySubnet \
--resource-group myResourceGroup \
--vnet-name myVirtualNetwork \
--disable-private-endpoint-network-policies true

Create the VM

Create a VM with az vm create. When prompted, provide a password to be used as the sign-in credentials for the VM. This example creates a VM named myVm:

az vm create \
  --resource-group myResourceGroup \
  --name myVm \
  --image Win2019Datacenter

Note the public IP address of the VM. You will use this address to connect to the VM from the internet in the next step.

Create an Azure Database for PostgreSQL - Single server

Create an Azure Database for PostgreSQL with the az postgres server create command. Remember that the name of your PostgreSQL Server must be unique across Azure, so replace the placeholder value with your own unique values that you used above:

# Create a server in the resource group

[!INCLUDE [applies-to-postgresql-single-server](../includes/applies-to-postgresql-single-server.md)]

[!INCLUDE [azure-database-for-postgresql-single-server-deprecation](../includes/azure-database-for-postgresql-single-server-deprecation.md)]


az postgres server create \
--name mydemoserver \
--resource-group myresourcegroup \
--location westeurope \
--admin-user mylogin \
--admin-password <server_admin_password> \
--sku-name GP_Gen5_2

Create the Private Endpoint

Create a private endpoint for the PostgreSQL server in your Virtual Network:

az network private-endpoint create \  
    --name myPrivateEndpoint \  
    --resource-group myResourceGroup \  
    --vnet-name myVirtualNetwork  \  
    --subnet mySubnet \  
    --private-connection-resource-id $(az resource show -g myResourcegroup -n mydemoserver --resource-type "Microsoft.DBforPostgreSQL/servers" --query "id" -o tsv) \    
    --group-id postgresqlServer \  
    --connection-name myConnection  

Configure the Private DNS Zone

Create a Private DNS Zone for PostgreSQL server domain and create an association link with the Virtual Network.

az network private-dns zone create --resource-group myResourceGroup \ 
   --name  "privatelink.postgres.database.azure.com" 
az network private-dns link vnet create --resource-group myResourceGroup \ 
   --zone-name  "privatelink.postgres.database.azure.com"\ 
   --name MyDNSLink \ 
   --virtual-network myVirtualNetwork \ 
   --registration-enabled false

#Query for the network interface ID  

networkInterfaceId=$(az network private-endpoint show --name myPrivateEndpoint --resource-group myResourceGroup --query 'networkInterfaces[0].id' -o tsv)


az resource show --ids $networkInterfaceId --api-version 2019-04-01 -o json
# Copy the content for privateIPAddress and FQDN matching the Azure database for PostgreSQL name

[!INCLUDE [applies-to-postgresql-single-server](../includes/applies-to-postgresql-single-server.md)]

[!INCLUDE [azure-database-for-postgresql-single-server-deprecation](../includes/azure-database-for-postgresql-single-server-deprecation.md)]


#Create DNS records 

az network private-dns record-set a create --name myserver --zone-name privatelink.postgres.database.azure.com --resource-group myResourceGroup  
az network private-dns record-set a add-record --record-set-name myserver --zone-name privatelink.postgres.database.azure.com --resource-group myResourceGroup -a <Private IP Address>

Note

The FQDN in the customer DNS setting does not resolve to the private IP configured. You will have to setup a DNS zone for the configured FQDN as shown here.

Note

In some cases the Azure Database for PostgreSQL and the VNet-subnet are in different subscriptions. In these cases you must ensure the following configurations:

• Make sure that both the subscription has the Microsoft.DBforPostgreSQL resource provider registered. For more information, refer to resource providers.

Connect to a VM from the internet

Connect to the VM myVm from the internet as follows:

1. In the portal's search bar, enter myVm.

2. Select the Connect button. After selecting the Connect button, Connect to virtual machine opens.

3. Select Download RDP File. Azure creates a Remote Desktop Protocol (.rdp) file and downloads it to your computer.

4. Open the downloaded.rdp file.

   1. If prompted, select Connect.

   2. Enter the username and password you specified when creating the VM.

      Note

      You may need to select More choices > Use a different account, to specify the credentials you entered when you created the VM.

5. Select OK.

6. You may receive a certificate warning during the sign-in process. If you receive a certificate warning, select Yes or Continue.

7. Once the VM desktop appears, minimize it to go back to your local desktop.

Access the PostgreSQL server privately from the VM

1. In the Remote Desktop of myVM, open PowerShell.

2. Enter  nslookup mydemopostgresserver.privatelink.postgres.database.azure.com.

   You'll receive a message similar to this:

   Server:  UnKnown
   Address:  168.63.129.16
   Non-authoritative answer:
   Name:    mydemopostgresserver.privatelink.postgres.database.azure.com
   Address:  10.1.3.4
   

3. Test the private link connection for the PostgreSQL server using any available client. The following example uses Azure Data studio to do the operation.

4. In New connection, enter or select this information:

   Setting	Value
   Server type	Select PostgreSQL.
   Server name	Select mydemopostgresserver.privatelink.postgres.database.azure.com
   User name	Enter username as username@servername which is provided during the PostgreSQL server creation.
   Password	Enter a password provided during the PostgreSQL server creation.
   SSL	Select Required.

5. Select Connect.

6. Browse databases from left menu.

7. (Optionally) Create or query information from the postgreSQL server.

8. Close the remote desktop connection to myVm.

Clean up resources

When no longer needed, you can use az group delete to remove the resource group and all the resources it has:

az group delete --name myResourceGroup --yes 

Next steps

• Learn more about What is Azure private endpoint