For anyone else that stumbles across this issue in the future, Specifically with GCP postgres, there is some semantics with validating the hostname in the certificate that need to be dealt with.
pg with google cloud postgres 路 Issue #79 路 brianc/node-postgres-docs 路 GitHub talks through the issue in quite some details (Its likely that retool is using node-pg under the hood, but i've not confirmed this, regardless the issue is still valid about validating the hostname)
I came across a Retool environment variable that seems to help me - I can only elaborate on how it works, as I cant find any docs for the variable (asked a question, got no responses Is there any documentation for additional environment variables)
I've set POSTGRES_CUSTOM_SSL_CERT_ALTNAME_REGEX=".*" on my self hosted retool and it allows the connection to succeed. (Of course, this is only applicable for self hosted.)
Hopefully this info helps someone in future.