Question 335525 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to connect PGAdmin 4 to Postgres DB residing in Azure Virtual Network

Lots of similar questions on this but have not come across an answer for an "Azure Database for PostgreSQL flexible server" set up with Private Access (VNet Integration).

The Postgresql DB is used by an Azure Web App that queries it and displays data from one of the database tables on the web app’s UI. Obvious case use, but I’m mentioning it as it’s the reason why I set the connectivity method to "Private access (VNet Integration)", instead of "Public access" in Settings->Networking->Network Connectivity->Connectivity method; I did not want the database directly accessible from a public endpoint, only the web app’s back end.

Setting the Database up resulted in adding a virtual network, a "privatelink.postgres.database.azure.com" Private DNS Zone and a Server which sits inside the virtual network, getting fed an ip address from the privatelink Zone.

But here’s the thing… whilst I do not want it accessible from the outside, I do actually need to view the db tables, schema, data etc whilst I build it all, so I need to access it from an app run on my local computer in the office, so…well…from the outside. 🙂

I can write to the database fine through my flask application, I can’t view the tables, data etc.

I have tried using both the Azure Data Studio and pgAdmin 4 apps, but unsurprisingly I get the DNS resolution problem "Unable to connect to server: connection is bad: No such host is known".

This is an example of the same problem but with Public access, which give rise to "timeout expired" notifications, not "no such host is known".

How can i connect Azure postgres database to pgadmin as im getting time out error

I had read that I needed to set some firewall settings as in the link below from a year ago, but the page with the toggle to allow access settings to Azure services does not even exist on my Azure portal.

https://learn.microsoft.com/en-us/azure/postgresql/single-server/concepts-firewall-rules

There is a similar question here, but that points to changing the severs connectivity to publicly accessible:

How to connect to a DB in a Virtual Network in Azure

I’m lost…Any ideas?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    First off, Thanks @Oury-MSFT for the reply. I am sure your approach is EXACTLY what I was looking for but looking at the walkthrough, the cost of the Virtual Network Gateway was at best $0.03 an hour and you have to delete and re-create if you are using it sporadically, which I am. So we went down the VM route, allowing access for pgAdmin through SSH. The VM is easier to spin up/ down when needed and was $0.0052 an hour. Obviously this makes the db accessible to the outside when up, so not an exact fit to my original question, but a good compromise.

    So for those wanting to do the same and connect to postgresql flexible db residing in an Azure virtual network:

    1. Create VM in same private network as database with ssh and https connections. Used Create an Azure Linux virtual machine section of:https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/quickstart-create-connect-server-vnet#create-an-azure-linux-virtual-machine (Be careful about how they want to charge you.). note: I did not make a new subnet like in the link, it was created by default.
    2. Download .pem file and keep safe. This file is created when you create the VM, it has the private key pair for ssh.
    3. Connect to VM using ssh in terminal/PS with "ssh -i pathtopemfile.pem azureuser@{ip of your VM}" If it spits issues about not being secure enough, with Windows, just move file to user with the required permissions, or change permissions in the properties of the file. Make sure it's Read Only and not Archived. In Mac/Linux, this can be changed using chmod.
    4. Go to pgadmin 4, set up server with credentials found in the "Connect" tab of the database server resource in Azure portal (password can be found in the connection string in the Azure Web App resource). In the ssh tab enable ssh using the ip of the VM, "azureuser" or the user you set up when making the VM as the user and using the .pem file to authenticate instead of a password.

  2. 0

    First, this is deprecated https://learn.microsoft.com/en-us/azure/postgresql/single-server/concepts-firewall-rules
    Use the flexible server docs.
    To connect to an Azure Database for PostgreSQL flexible server with Private Access (VNet Integration) from your local computer, you can use a Point-to-Site (P2S) VPN connection. This allows you to connect to the virtual network that hosts your PostgreSQL server from your local computer. You can then use tools like Azure Data Studio or pgAdmin to connect to the database using the private IP address of the server.
    https://techcommunity.microsoft.com/t5/itops-talk-blog/step-by-step-creating-an-azure-point-to-site-vpn/ba-p/326264
    Please do also check https://learn.microsoft.com/en-us/answers/questions/1300731/azure-database-for-postgresql-flexible-servers-wit

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search