Azure PostgreSQL Lesson Learned #4: Fix Connection Error: No pg_hba.conf Entry, SSL off

Co-authored with HaiderZ-MSFT​

The issue occurred when the customer attempted to connect to Azure PostgreSQL using psql and received this error:

psql: error: could not connect to server: FATAL: no pg_hba.conf entry for host "", user "ange", database "postgres", SSL off

Symptoms

• Connection attempts failed from a VM inside the VNet.
• DNS resolved to the private network, but connection was unsuccessful.
• Error message indicated SSL was off.

Root Cause

Azure Database for PostgreSQL requires SSL connections by default for all network types to enhance security. You can verify this by checking the require_secure_transport server parameter, which is set to ON by default in a private network setup:

• The server is accessible only through the private network.
• Public access is disabled.
• DNS must resolve the server's name to the FQDN.

The error occurred because:

• SSL was not enabled in the connection string.
• Customer assumed private network negates SSL requirement.

Resolution Steps

Follow these steps to fix the issue:

1.  Verify Configuration

• In Azure Portal → PostgreSQL Server → Networking, confirm:
• Public access is disabled.
• Private network is linked to the correct VNet/subnet.

2. Check DNS Resolution

       From a VM in the same VNet (or peered VNet):

     nslookup <server>.postgres.database.azure.com

Expected: Returns a private IP address.

3. Test Port Connectivity

        telnet <server>.postgres.database.azure.com 5432

Expected: Successful connection to port 5432.

4. Update Connection String with SSL

         Use the correct format:

         psql "host=<server>.postgres.database.azure.com port=5432 dbname=postgres user=ange@<server> password=<password> sslmode=xx"

Choose the appropriate SSL mode based on your needs; the following explanation will help you determine which option is best for you >

Networking Overview with SSL and TLS - Azure Database for PostgreSQL | Microsoft Learn

5. Validate Connection

Run the command and confirm successful connection. 

Prevention & Best Practices

• Always include sslmode=xx in your connection string.
• Validate DNS resolution for private network regularly.
• Monitor connectivity using Azure Monitor or custom scripts.
• Follow Networking Overview with SSL and TLS - Azure Database for PostgreSQL | Microsoft Learn for security and performance.

Why This Matters

Failing to configure SSL and validate network settings can lead to:

• Application downtime.
• Security compliance issues.
• Customer frustration during critical operations.

By following these practices, you can ensure secure and seamless connectivity to Azure Database for PostgreSQL.

Key Takeaways

• Error occurs because SSL is off in the connection string, even in private networks.
• Azure PostgreSQL requires SSL by default.
• Fix: Add sslmode=require (or appropriate value) to your connection string.
• Validate DNS resolution and port 5432 connectivity.
• Always include SSL settings for compliance and reliability.