How to connect azure postgres with bastion?

Question

In the page also, it was shown to create a separate vm in the same vnet to connect to postgres setup in a private vnet option selected.

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/quickstart-create-connect-server-vnet

Is there anyway to connect using azure bastion.please show the steps.

Answer 1

Dear all,

There is a solution to this problem and the answer from Microsoft Employee seems to be wrong.

Firstly, I have tested this solution for Postgresql Single Server. In other words, I have not testet it against a Flex server. Secondly, the solution may or may not be secure enough based on your security requirements.

UPDATE: The instructions are re-written for better documentation.

Highlights:

1. SETUP A TUNNEL TO AZURE VM VIA BASTION HOST

Set up a SSH tunnel to Azure Bastion Host using "az network bastion tunnel" command. For instance:

az network bastion tunnel --name <your-bastion-host> --resource-group <your-resource-group> --resource-port 22 --port 2022 --target-resource-id /subscriptions/<your-subscription-id>/resourceGroups/<your-resource-group>/providers/Microsoft.Compute/virtualMachines/<your-azure-vm>

Having established the tunnel, you should first try to log on your vm and test the connection, e.g.

ssh -i <your-private-key-for-your-vm> <your-vm-username-that-matches-the-private-key>@localhost

2. SETUP A DOUBLE TUNNEL TO POSTGRESQL SERVER

If you can successfully connect to your vm with ssh tunnel, you can now establish a second tunnel from your vm to your Postgresql server. Create a script like:

#!/bin/bash

VM_JUMPHOST="127.0.0.1"

PORT=5432

PK_FILE_JUMPHOST="<your-private-key-file-name>.pem"

POSTGRESQL_SERVER="<your-postgresql-server-name>.postgres.database.azure.com"

MYUSER="<your-vm-username-that-matches-the-private-key>"

ssh -vv -p 2022 -i $PK_FILE_JUMPHOST -L $PORT:$POSTGRESQL_SERVER:$PORT -l $MYUSER -N $VM_JUMPHOST

Now, follow the terminal for connectivity....

Update, 2B. SETUP A DOUBLE TUNNEL TO POSTGRESQL SERVER WITH SHORT-LIVED KEYS

In above, we used private keys that are statically created upon the creation of the VM. In below, we show how double tunnel can be accomplished using short-lived keys that are dynamically managed by Azure.

#!/bin/bash

VM_JUMPHOST="127.0.0.1"

PORT=2022

vmIP=<Your-VM-IP-address>

az ssh config --file ~/.ssh/config --ip $vmIP

PK_FILE_JUMPHOST="~/.ssh/az_ssh_config/$vmIP/id_rsa"

CERTIFICATE_FILE_JUMPHOST="~/.ssh/az_ssh_config/$vmIP/id_rsa.pub-aadcert.pub"

MYUSER="firstname.lastname@<your-domain-name>"

ssh -vv -p $PORT -o CertificateFile=$CERTIFICATE_FILE_JUMPHOST -i $PK_FILE_JUMPHOST $MYUSER@localhost

Requirements for this solution are:

• Each VM has to be assigned managed identity
• SSH-extension should be installed on each VM
• You need Virtual Machine Administrator Login or Virtual Machine Login

3. CONNECT TO DATABASE

You can now use psql to connect to Postgresql as:

psql "host=localhost port=5432 dbname=<database-name> user=<your-postgresql-server-username>@<your-postgresql-server-name> password=<your-password-for-the-database> sslmode=require"

Note that the first time you connect to the Postgresql server, the default database is "postgres" and you should write dbname=postgres. You may change the value of dbname to point to your other databases later on.

Final note

In the above, we assumed that you use a fixed private key for your Azure VM. Such keys are typically generated upon the creation of VM. From a security point of view, it is better to use short-lived private keys that are generated by "az ssh config" command. The latter should theoretically be possible. However, as of this writing, I have not managed to make short-lived private keys work with double tunnels.

Good luck

Answer 2

Hi, @Uday Kiran Reddy (ureddy) Thanks for the ask and using the Microsoft Q&A platform.
Is that something related to VMs? you are looking for.

Bastion is to connect to VMs from the web, so Bastion connects to VM, the VM can connect to PostgreSQL but you cannot SSH to PostgreSQL PaaS that is to connect to the VM that hosts postgres is not possible. Bastion will be used to securely connect directly from Azure Portal only if you have available an Azure VM which can be used a jump sever in the same vnet as Azure Databased for PostgreSQL PaaS service. That VM needs to have installed Postgres client (psql) or PgAdmin so that you can manage your Postgres PaaS instance

So if the server is public access, then it won't integrate to the vnet the connection will be routed through public space,

you can find Bastion documentation here
Regards
Geetha