Over 3 million consumers suffered from corporate data breaches in 2023 so far., according to SafeSmartLiving website, tracking major data breach announcements. Now more than ever, it’s important for enterprises to fully understand the risks and consequences of data breaches. This post offers tips and best practices insights into database security hardening of Azure Database for PostgreSQL - Flexible Server, our premium DBaaS PostgreSQL offering in Microsoft Azure.
As we look at PostgreSQL Flexible Server, we can review following server components for security hardening opportunities:
Physical Security
With database servers its critical is to look at how the server is connected to and accessed. As with any security configuration, follow the principle of least privilege when considering how to configure your system; that is, only allow as much access as absolutely required to implement a working system, and no more.
First and foremost, the physical access to data should be limited as much as possible, by ensuring the server is located in a secure facility.
Pic 1. Microsoft Azure Data Center.
As part of Microsoft Azure cloud platform, PostgreSQL Flexible Server benefits from built-in physical security of that platform. Microsoft designs, builds, and operates Azure datacenters in a way that strictly controls physical access to the areas where your data is stored. Microsoft understands the importance of protecting your data and is committed to helping secure the datacenters that contain your data. We have an entire division at Microsoft devoted to designing, building, and operating the physical facilities supporting Azure. This team is invested in maintaining state-of-the-art physical security.
Network Security
You can deploy a flexible server into your Azure virtual network (VNet). Virtual network is a logical construct built on top of the physical Azure network fabric. Each virtual network is isolated from all other virtual networks and internet. This helps ensure that network traffic in your deployments is not accessible to other Azure customers or outside.
To add further basic network level access control (based on IP address and the TCP or UDP protocols), you can use Network Security Groups (NSGs). An NSG is a basic, stateful, packet filtering firewall, and it enables you to control access based on a 5-tuple. NSGs include functionality to simplify management and reduce the chances of misconfigurations that cause many network breaches.
Moreover, Azure security baseline for Virtual Network applies guidance from the Microsoft cloud security benchmark version 1.0 to your VNET. The Microsoft cloud security benchmark provides recommendations on how you can secure your cloud solutions on Azure. The content is grouped by the security controls defined by the Microsoft cloud security benchmark and the related guidance applicable to Virtual Network.
Encryption in transit.
As client traffic to your PostgreSQL Flexible Server flows through the network, it can be additionally recommended, as essential security basic best practice, to encrypt that traffic on the wire. Azure Database for PostgreSQL - Flexible Server enforces connecting your client applications to the .... TLS is an industry-standard protocol that ensures encrypted network connections between your database server and client applications.
Pic 2. SSL handshake workflow. Image credit to Cloudflare.
TLS is an updated protocol of Secure Sockets Layer (SSL). Azure Database for PostgreSQL - Flexible Server supports TLS version 1.2 and later. In RFC 8996, the Internet Engineering Task Force (IETF) explicitly states that TLS 1.0 and TLS 1.1 must not be used. Both protocols were deprecated by the end of 2019. It can be strongly recommended that customers enforce TLS 1.2 encryption in transit vs. allowing older protocol versions, which are no longer secure, according to NIST. You can control minimum accepted TLS version by setting You can also set TLS version by setting ssl_min_protocol_version server parameter.
A TLS connection is initiated using a sequence known as the TLS handshake, as shown in the picture 2 above.
When establishing a secure session, the Handshake Protocol manages the following:
- Cipher suite negotiation
- Authentication of the server and optionally, the client
- Session key information exchange.
Once data is encrypted and authenticated, it is then signed with a message authentication code (MAC). The recipient can then verify the MAC to ensure the integrity of the data.
To determine your current SSL connection status you can load the sslinfo extension and then call the ssl_is_used() function to determine if SSL is being used. The function returns t if the connection is using SSL, otherwise it returns f. You can also collect all the information about your Azure Database for PostgreSQL - Flexible Server instance's SSL usage by process, client, and application by using the following query:
SELECT datname as "Database name", usename as "User name", ssl, client_addr, application_name, backend_type
FROM pg_stat_ssl
JOIN pg_stat_activity
ON pg_stat_ssl.pid = pg_stat_activity.pid
ORDER BY ssl;
Authentication
Next important security component to be considered is client authentication; how we authenticate users and control whether or not they can connect to the server successfully.
One of the best ways to harden authentication security component in PostgreSQL Flexible Server is to use Azure Active Directory (AAD) authentication.
Benefits of using Azure AD include:
- Authentication of users across Azure Services in a uniform way
- Management of password policies and password rotation in a single place
- Multiple forms of authentication supported by Azure Active Directory, which can eliminate the need to store passwords
- Customers can manage database permissions using external (Azure AD) groups.
- Azure AD authentication uses PostgreSQL database roles to authenticate identities at the database level
- Support of token-based authentication for applications connecting to Azure Database for PostgreSQL
PostgreSQL Flexible Server allows you to use Postgres local authentication, Azure AD authentication and hybrid Azure AD\local authentication. If you decide to use Postgres local authentication or hybrid Azure AD\Postgres local authentication, where local user passwords are stored in Postgres, we will definitely recommend using SCRAM (scram-sha-256) password hashing mechanism vs. older md5. Both md5 and scram-sha-256 use a challenge response mechanism to prevent sniffing, and store hashed passwords on the server, however, scram-sha-256 stores the hashes in what is currently considered to be a cryptographically secure form to avoid issues if an attacker gains access to the hash, whereas md5 can no longer be considered secure.
There are two possible problems that make it hard to switch over from md5 to scram-sha-256:
- Since PostgreSQL does not know the original clear text password, the user has to set the password again, after you change the password encryption method to scram-sha-256.
- The PostgreSQL client has to support scram-sha-256 authentication, so authentication with older client software will fail.
Published list of PostgreSQL clients and their support for SCRAM is available here.
Limiting access inside the database with roles.
Best way to manage PostgreSQL database access permissions at scale is using the concept of roles. A role can be either a database user or a group of database users. Roles can own the database objects and assign privileges on those objects to other roles to control who has access to which objects. It is also possible to grant membership in a role to another role, thus allowing the member role to use privileges assigned to another role. PostgreSQL lets you grant permissions directly to the database users. As a good security practice, it can be recommended that you create roles with specific sets of permissions based on minimum application and access requirements. You can then assign the appropriate roles to each user. Roles are used to enforce a least privilege model for accessing database objects.
New databases in PostgreSQL are always created with a default set of privileges in the database's public schema that allow all database users and roles to create objects.
To better control user access to the databases that you create on your PostgreSQL Flexible server, we recommend that you revoke these default privileges to public. After doing so, you then grant specific privileges for database users on a more granular basis.
Encryption at rest with Customer Managed Keys (CMK)
At the most basic level, the data on-disk is encrypted with an azure internal key referred to as Data Encryption Key (DEK). To achieve that goal secure key creation, storage, access control, and management of the encryption keys must be provided. Though details may vary, Azure services Encryption at Rest implementations can be described in terms illustrated in the following picture pic. 3:
Pic 3. Encryption at rest using DEK and KEK keys (Customer managed Key)
The storage location of the encryption keys and access control to those keys is central to an encryption at rest model. The keys need to be highly secured but manageable by specified users and available to specific services. For Azure services, Azure Key Vault (AKV) is the recommended key storage solution and provides a common management experience across services. Keys are stored and managed in AKV, and access to a key vault can be given to users or services. Azure Key Vault supports customer creation of keys or import of customer keys for use in customer-managed encryption key scenarios. Permissions to use the keys stored in Azure Key Vault, either to manage or to access them for Encryption at Rest encryption and decryption, can be given to Azure Active Directory accounts.
Many organizations require full control on access to the data using a customer-managed key. Data encryption with customer-managed keys for Azure Database for PostgreSQL Flexible server - Preview enables you to bring your own key (BYOK) for data protection at rest. It also allows organizations to implement separation of duties in the management of keys and data. With customer-managed encryption, you are responsible for, and in a full control of, a key's lifecycle, key usage permissions, and auditing of operations on keys.
Data encryption with customer-managed keys for Azure Database for PostgreSQL Flexible server, is set at the server-level. For a given server, a customer-managed key, called the key encryption key (KEK), is used to encrypt the symmetric AES256 key data encryption key (DEK) used by the service. The KEK is an asymmetric key stored in a customer-owned and customer-managed Azure Key Vault) instance. A KEK that never leaves Key Vault allows the DEKs themselves to be encrypted and controlled.
Customer Managed Keys (CMK) add more control to our customers over how their data at rest is encrypted. Follow these steps to create server with this feature here.
Additional Resources
Security in Azure Database for PostgreSQL - Flexible Server | Microsoft Learn
Azure Data Encryption-at-Rest - Azure Security | Microsoft Learn
Active Directory authentication - Azure Database for PostgreSQL - Flexible Server | Microsoft Learn
Networking overview - Azure Database for PostgreSQL - Flexible Server | Microsoft Learn
Data security and encryption best practices - Microsoft Azure | Microsoft Learn
We look forward to hearing about your’ experience with Azure Database for PostgreSQL - Flexible server. We’re always eager to hear customer feedback, so please reach out to us at Ask Azure DB for PostgreSQL.
To learn more about our Flexible Server managed service, see the Azure Database for PostgreSQL service page.