I am writing this blog to explain different methods for connecting Azure Database for PostgreSQL server those who knows Azure but never worked in PostgreSQL. So, I will not be explaining how to manage databases but managing the PaaS from the portal. You may review details of this PaaS service from the Microsoft documentation to understand details like the SKUs, different plan, generations etc. I am just sharing some of my experience when I tried to deal with this for the first time.
Before we get into the connectivity methods, let’s talk about deploying PostgreSQL with the General-Purpose performance configuration which I think it is important . The available Generation is 5 as Generation 4 is not available for the deployment.
We can use either ARM templates or portal to perform the deployment. There are few ARM templates for PostgreSQL available in the Gibhub.
There are few things you need to remember when you input the parameters using ARM. The below things might confuse you.
- SKU Name – GP_Gen5_x where x means the number of vCores. If you are using 4 vCore then It needs to be changed if you are going with 4 cores. So it should be “GP_Gen5_4?
- SKU Name – ‘General Purpose’ which comes with balanced compute, memory and I/O throughput.
- Family – ‘Gen5’ , it is the currently generation. Gen4 is not available now it will throw an error.
- Storage MB – you cannot select exact value most of the time from the portal, so it is better to ARM.
Once you deployed your PostgreSQL, I think we have two important use cases for the connectivity.
- Connect to PostgreSQL for administration
- Connect o PostgreSQL for application access.
Since it has Azure public endpoints, it allows you to connect from internet. But it does not look good from the security perspective. You may use the PGadmin to connect the PostgreSQL after you deploy it. I recommend you connecting from a jump server/ Bastion host from the Azure environment itself. However,I will try to explain all the methods more in details.
Allowing all the Azure Services to connect
From the Connection Security, if you select ’ON’ for ‘Allow Access to Azure Services’. It will allow every resources in the Azure Cloud to connect to the PostgreSQL instance in your subscription. You could connect from any subscription as long as they have user credentials. But you won’t be allowed to connect from internet.
Connecting directly from Internet.
From the Connection security , you can select either ‘Add Client IP’ ( will add your source ip automatically) or you can specify you Source IP or Ip Range. If it is just one IP, provide the same IP at the source and destination of the rule.
The rules below do not work if you want to connect from a VM in your vNET. Those rules meant for internet source management.
Connect using a VM within the vNET.
From the Connection security click option ‘Adding existing virtual network’. Enter the rule name without any space then select the subscription, vNET and Subnet. Enable the Service Endpoints, it is leveraging existing Microsoft.Sql service endpoints I think. if you do not want to select this now but later ,you can select the checkbox ‘Ignore missing Microsoft.Sql service endpoint. We need to enable service endpoints to be able to access the PGSQL securely from the subnet where you source machine is. It does not allow the access without that settings.
I found the third connection method described above looks secure as it is not connecting to internet and it is restricted only for a specific subnet in your vNET. You can further bring more strict rules at the source NSG if you need only specific VMs to be able to access your database.
anoop.blog 