Connectivity issue from APM server to PostgreSQL DB

Products

• CA Application Performance Management Agent (APM / Wily / Introscope)
• CA Application Performance Management (APM / Wily / Introscope)
• INTROSCOPE
• DX Application Performance Management

Issue/Introduction

I have spin up new APM Instance in AWS using CloudFormation script. APM is not able to connect with external PostgreSQL DB and throws below exception in em logs.

[WARN] [C3P0PooledConnectionPoolManager[identityToken->2y0cfyactqq6uu55edi|4b325930, dataSourceName->apmDataSource]-HelperThread-
#0] [com.mchange.v2.resourcepool.BasicResourcePool] com[email protected]417b143d -- Acquisition Attempt Failed
!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attemp
ts (3). Last acquisition attempt exception:
org.postgresql.util.PSQLException: SSL error: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPat
hBuilderException: unable to find valid certification path to requested target
        at org.postgresql.ssl.jdbc4.AbstractJdbc4MakeSSL.convert(AbstractJdbc4MakeSSL.java:125)
        at org.postgresql.core.v3.ConnectionFactoryImpl.enableSSL(ConnectionFactoryImpl.java:316)
        at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:105)
        at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:64)
        at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:136)
        at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:29)
        at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:21)
        at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:31)
        at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24)
        at org.postgresql.Driver.makeConnection(Driver.java:393)
        at org.postgresql.Driver.connect(Driver.java:267)
        at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:146)
        at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:195)
        at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:184)
        at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:200)

PostgreSQL DB configured as SSL in AWS RDS

APM is working as expected if we disabled SSL in RDS but once we enabled SSL in RDS then APM is not connecting to DB. It throws same SSL exception.

I have found below KB article but its for cloud proxy and couldn't find any KB doc for Introscope EM related.

https://knowledge.broadcom.com/external/article?articleId=137363&_ga=2.90195674.1505628538.1597774701-283908835.1589831954

Kindly help to fix this issue.

Environment

Release : 10.7.0

Component : APM Agents

Resolution

See https://jdbc.postgresql.org/documentation/head/ssl-client.html  for some great troubleshooting.  Setting sslmode=verify-full solved it. If sslmode=verify-full, the server host name will be verified to make sure it matches the name stored in the server certificate.

Additional Information

Other things to check 

1. Was the certificate generated following the section "(Optional) Enable SSL Support for PostgreSQL" on the doc

https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/it-operations-management/application-performance-management/10-7/install-and-configure-the-apm-database.html#concept.dita_a76880049931c2879d1a17908c088e72758b7c34_PostAPMDatabaseInstallationSteps

Or it's from some other ways?

 

2. Have we validated the certificate yet? i.e. Are we able to do ssl connection to the DB from the APM machine using some other tool like pgAdmin?

 

3. Setting the connection URL parameter sslfactory=org.postgresql.ssl.NonValidatingFactory will turn off all SSL validation.  Good for troubleshooting.

4. In the event of problems extra debugging information is available by adding -Djavax.net.debug=ssl to your command line.   This showed an unknown certificate.