Securing Connectivity to Azure Database for PostgreSQL
Published Aug 07 2019 06:24 AM 8,516 Views
Microsoft

Ensuring secure connectivity to database resource is an important requirement and consideration for customers running in cloud environment. Today customers want to connect to their Azure Database for PostgreSQL from both inside/outside of Azure, and based on the security and compliance requirements, organizations can choose one of the options provided by Azure Database for PostgreSQL. In this blog post, we share the different connectivity options that can be used to connect to your Azure Database for PostgreSQL and reap the benefits of the Azure platform.

 

Using Service endpoints supported by Azure Database for PostgreSQL

 

If the resources accessing the Azure Database for PostgreSQL are all within Azure, you can leverage Virtual Network Service Endpoints to connect to the server.  VNet service endpoints enable you to isolate connectivity to your logical server from only a given subnet or set of subnets within your virtual network. The traffic to Azure Database for PostgreSQL from your VNet always stays within the Azure backbone network. Preference for this direct route is over any specific ones that route Internet traffic through virtual appliances or on-premises.

 

Turning on VNet service endpoints does not override firewall rules that you have provisioned on your Azure Database for PostgreSQL or MySQL. Both continue to be applicable. VNet service endpoints don’t extend to on-premises. To allow access from on-premises, firewall rules can be used to limit  connectivity only to your public (NAT) IPs. You can refer to the documentation for Azure Database for PostgreSQL on how to setup service endpoints.

 

Additionally, the Connection security pane has an ON/OFF button that is labeled “Allow Access to Azure services”. The ON setting allows communications from all Azure IP addresses and all Azure subnets. These Azure IPs or subnets might not be owned by you. This ON setting is probably more open than you want your Azure Database for PostgreSQL Database to be. The virtual network rule feature offers much finer granular control and therefore a preferred way to establish connectivity for product environments.

 

Limitations

 

The pre-requisite for VNet service endpoint is “same Azure region and same Azure AD tenant”.
If the resource requesting access to the PostgreSQL resource are in two Active directory tenant, the recommended and preferred option is to use the single AD tenant with multiple subscription and but at the same time limit the access to database subscription using RBAC.

 

Use Public Endpoints to connect to Azure DB for PostgreSQL service

 

The Azure DB for PostgreSQL service is architected such that it sits behind Azure network protection and has its own gateway that securely establishes connections with your server. Connections to the database services are protected further by configuring PostgreSQL native database firewalls which will ensure only traffic from whitelisted IP can enter and try to connect to the database. Additionally, Azure Database for PostgreSQL support SSL connections and it is recommended to keep it “ON” for any public incoming traffic.

Visit these articles to learn how to configure SSL for Azure DB for PostgreSQL service. Native database authentication methods for PostgreSQL are supported out of the box. 

 

You can refer to the documentation for Azure Database for PostgreSQL on how to setup firewall rules.

 

Using a TCP proxy along with VNet Peering to connect to Azure Database of PostgreSQL

 

Customer also want to connect to Azure Database for PostgreSQL from on-premises by allowing outbound traffic to well-known IP addresses for Azure database for PostgreSQL gateway for your Azure region that are documented here. With the firewall rules and public endpoints explained in the previous section, the traffic goes over the Internet which introduces with it some inherent security and reliability challenges. A common example is having to manage firewall rules for mobile clients whose SNAT addresses change frequently.

 

An alternative is to setup a private connection to Azure – via P2S VPN, S2S VPN or Express Route – and then use a TCP proxy server to forward traffic to public IP address for Azure Database for PostgreSQL. We can achieve this using P2S VPN and NGINX server.

 

We used P2S VPN as easy way to get traffic to flow from on-premises to Azure. In real-world scenarios customers should rely on more robust peering solutions like S2S VPN or Express Route for their production workloads.

 

Here is the high-level architecture diagram of how this solution works in practice:

 

10.png

 

Step1 - User connects from on-premises (over VPN) by specifying Private IP address for Azure VM & port 5432. Alternately, hostname can be used with custom DNS that then maps it to Private IP address.

 

Step 2 - NGINX is running on Azure VM and listening for traffic on port 5432

 

Step 3 - Traffic is forwarded by NGINX to the Azure Database for PostgreSQL Gateway (for the region hosting your PostgreSQL server) as part of normal login flow.

 

Here is a step by step guide to implementing this architecture

 

Start by adding Azure VM vnet&subnet to virtual network rule on Azure Database for PostgreSQL as described here. This will ensure that the subnet hosting Azure VM can communicate to PostgreSQL server.

 

The result should be a firewall rule that looks like this.

 

6.png

Next, assign a Private IP address to your via VM Blade à Networking à IP Configurations. For this demo I typed in 10.1.1.5

 

8.png

 

Download Nginx and append the below snippet the bottom of the nginx.conf file as follows without modifying headers.

 

 

 

stream {
   upstream sqlvm {
         server mypgserver.postgres.database.azure.com:5432;
   }
   server {
         listen 5432;
         proxy_pass sqlvm;
   }
}

 

 

 

This tells NGINX to forward any traffic received on port 5432 to your Azure Database for PostgreSQL server mypgserver.postgres.database.azure.com:5432.

 

Now connect via “PgAdmin” specifying the Private IP address of VM and port 5432 (which Nginx is listening on)

 

We showed you a proof-of-concept for how you can connect to Azure Database for PostgreSQL from on-premises by using a TCP proxy server to forward traffic. Please note, the same steps will work for Azure Database for MariaDB and Azure Database for MySQL on port 3306.

 

To conclude, for ensuring secure connectivity to your Azure Database for PostgreSQL, the different options gives you the flexibility and control of how data is transmitted. We are continuously working on making it easy for the customers to manage their database resource. You can engage with us on the Azure feedback forums for Azure Database for PostgreSQL.

 

Version history
Last update:
‎Aug 06 2019 11:24 PM
Updated by: