Check if PostgreSQL connections from ArcGIS Clients are encrypted

Summary

It is important to note that, although general PostgreSQL documentation often recommends specifying the sslmode parameter, such as "require", "verify-ca", or "verify-full" in client connection strings, this is neither required nor supported when connecting from ArcGIS client software. See the following references:

• Connect to PostgreSQL from ArcGIS
• Create a geodatabase on Amazon RDS for PostgreSQL or Aurora PostgreSQL 
• Add databases to an ArcGIS Server site on Microsoft Azure

Based on observed behavior, ArcGIS clients implicitly attempt to establish secure connections using SSL when communicating with cloud-hosted PostgreSQL databases. 

Procedure

To validate that encryption is being used, the following SQL query can be executed via pgAdmin or any SQL client using credentials with appropriate privileges:

SELECT
  a.datname,
  a.usename,
  a.client_addr,
  s.ssl,
  s.version,
  s.cipher
FROM
  pg_stat_activity a
LEFT JOIN
  pg_stat_ssl s ON a.pid = s.pid
WHERE
  a.state = 'active';

The ssl field in the results indicate whether each connection is encrypted. The client_addr field shows the IP addresses of connected clients. In this context, it is expected that the IPs correspond to ArcGIS Pro and ArcGIS Enterprise Server clients.

Note that LEFT JOIN instead of JOIN ensures that all active sessions are included, even if they don’t use SSL, that is, s.ssl will be NULL for non-encrypted connections.

Depending on the deployment of Postgress EGDB there are various ways to configure encrypted communication.

• For PostgreSQL 16 and above deployed on hosted servers, whether on-premises or in the cloud, SSL settings are typically configured in the postgresql.conf file to specify the certificate authority (CA), intermediate, and server certificates used for encryption. The pg_hba.conf file should include hostssl entries for client hosts that require encrypted communication. refer to: PostgreSQL: Documentation: 16: 19.9. Secure TCP/IP Connections with SSL 
• For Amazon RDS for PostgreSQL, SSL settings are typically enabled by default, eliminating the need for manual configuration. See:
  • Using SSL with a PostgreSQL DB instance
  • Using SSL/TLS to encrypt a connection to a DB instance or cluster
• Using Azure PostgreSQL cloud services, the following articles provide guidance on how SSL/TLS is configured and managed:
  • Encrypted Connectivity with TLS/SSL - Azure Database for PostgreSQL
  • Transport Layer Security (TLS) - Azure Cosmos DB for PostgreSQL