Trouble Connecting To Postgres (Google Cloud SQL) with SSL

[Daniel_Turner]

I am having trouble connecting to a postgres db on google cloud sql

I can connect without SSL required, but when I require SSL, I got the following error:

Failed to establish connection. Error: The remote certificate is invalid according to the validation procedure.

I do have an active server SSL cert.

[Daniel_Turner]

Oh, I see this:

"
We highly recommend that the PostgreSQL instance uses a server certificate generated by a widely recognized Certificate Authority such as VeriSign or GeoTrust. This will ensure that the certificate meets all of the relevant encryption and formatting standards. Some cloud storage providers, such as Google Cloud and Amazon RDS, also generate server certificates for the PostgreSQL instances that they host. Currently, server certificates generated by TinyCA are not supported.

It’s also good practice to sign the server certificate using SHA-2 hashing algorithms. This is because SHA-1 algorithms are no longer considered fully secure, and many cloud providers, including Microsoft, Amazon, and Google, are increasingly moving to SHA-2 and SHA-3.
"

It’s not clear if this is saying certificates generate by Google Cloud will meet “all of the relevant encryption and formatting standards”, or if I need to use a cert from VeriSign or GeoTrust…?

[kyzer45]

I have this same problem and looks like its not been fixed yet. Hopefully being bought by Google should fix the issue.

[elco]

I have the same issue still

So we simply cannot use GCP for SQL servers because almost noone wants to use it without SSL.

Why can we not add client certificates? That’s one solution for all clouds that generate their own certificate.

[elco]

@prithpal @JCadence since you’re the last two people I talked with from AppSheet, can you please bring this limitation to developer attention.

This is a major feature breaking specification. You cannot connect to GCP’s SQL servers securely.

Is there a place to add your vote? This thread has been ignored so far.

[Former Community Member]

Including @Scott_Haaland from our team who can help with this

[shaaland]

Hi @elco ,

We are adding a feature to CloudSQL MySQL to be able to configure client certificates from the database into the Data Source configuration for an additional security layer.

We don’t have this solution in the works for Postgres on CloudSQL yet, however. It is tentatively on our roadmap based on customer demand. We just haven’t seen many customers using Postgres with AppSheet and asking for this feature. We can consider it if we continue to see requests like yours. Is there any chance you could switch to MySQL? We are going to be adding this client cert feature there soon.

R,
Scott

[elco]

No we can’t switch due to other limitations. AppSheet should then advertise this and remove PSQL from the list (or add that * like a coward).

Good to hear it’s on the map and hopefully it doesn’t take another 2 more years. Would have loved to have this info upfront and not hidden.

[shaaland]

Hi @elco ,

Just want to be clear. You can connect to Postgres using server certificates, which does enable SSL. We just don’t have the support for the client certificates on the AppSheet side. Each customer has different IT Security policies, so some customers are ok with the combination of the Server certificates and the whitelisting of AppSheet IPs and are able to use Postgres in CloudSQL. Apparently, your security requirements are higher, and that is why you are hitting this limitation. In this case, your one remaining option is to file for a security exception with your IT Security team and see if you can get an exception based on the existing security features (server certs and whitelisted IPs).

Thanks,
Scott

[Daniel_Turner]

Hi Scott,

So, in our testing, we were not able to use enable SSL, in appsheet, with CloudSQL Postgres. We do have an active SSL server certificate. If I try to connect, with SSL, from appsheet I get the error " Error

Failed to establish connection. Error: The remote certificate is invalid according to the validation procedure."

[elco]

Let’s be crystal clear for the next person in my position:

• You don’t support the 2 major players in fully-managed cloud.
• And you do not support the cloud of your parent company.
• And server side SSL is only possible with very limited root certificates; we do not want/need client side certificates, this is your requirement.
• And PSQL connections does not work with many field types.
• And PSQL connection fails to import tables with timestamps.

If you had included these limitation on due to “high security requirement” in your docs, I would have been very happy. But since it took about over 10 hours of my time to figure out these limitations… I want to be clear for the next person.

You do not support PSQL.

[Daniel_Turner]

Wait …we just upgraded to enterprise primarily so we could use PSQL as a data source.

What are the field types that are not supported?
Are timestamp types really not supported? (does it matter if it is with or without timezone?)

Last night, one of our devs was running into an issue with a timestamp column used in a formula, we weren’t sure what the issue was…

Editing this post: Just spoke with our dev and yes, it looks like timestamp columns do not ingest correctly. We will need create additional date and/or epoch columns. This is pretty bad. AppSheet should definitely support timestamp columns (with or without time zones) in PSQL.

I don’t want to presume what appsheet uses for it’s backend, but there are plenty of libraries that support psql, so, I am not sure why this has not been addressed?

[shaaland]

Hi @Daniel_Turner ,

Please see my response to elco…we are definitely going to work on getting this working better. Thanks for your patience.

R,
Scott

[Daniel_Turner]

Thank you Scott, we appreciate it. A quick fix on the timestamp issue (with and without timezone) would be a win!

[shaaland]

Hi @elco ,

I sincerely apologize for your time spent debugging this and your frustrations. I’m going to take a couple of action items, and you can also see that our lead engineer for connectors (@brian ) is also on top of these issues.

1. I’ll work to include these limitations in the docs (hopefully will be less after Brian’s efforts in the next couple of weeks)
2. I’ll work with Brian and the team to see which broken items we can fix right away, and which ones we may need more time and will go on the roadmap. For example, fixing the timestamp is probably relatively easy, but adding support for complex types like arrays may take more time.

Can you please help me understand your server side certificate requirements a bit more? You mentioned there is “Very” limited root certs. Do you mean AppSheet only supports a limited number of root certs (I assume you mean CA’s (certificate authorities like Verisign)?) or do you mean that you only wish to support a limited number of root certs?

Thanks for your patience, and I assure you we will get this working better soon.

Kind regards,
Scott

[silentfred]

@elco Have you noticed improvements here? I'm on the verge of moving our data sources to PSQL tables and don't want any surprises. Would rather go with something else if there are issues

[brian]

I apologize; this is quite broken. Support for PostgreSQL client certs is a known work item and will take some time, but we will try to get something out to enable baseline SSL support with PostgreSQL in GCP as soon as possible (I’m hoping in the next week or two).

[silentfred]

Keen to know if any progress has been made here - trying to use PSQL on AWS RDS and not getting any love with SSL enforced - unable to connect

[brian]

We deployed a change today that will let you set Require SSL for PostgreSQL sources in GCP. This still won’t let you set ‘Allow only SSL connections’ server side, as that still requires work to support client certificates, but it is a step in the right direction. The client cert work is planned, but it’s not something we can turn around nearly as quickly.

I will update our docs with more information about which PostgreSQL types map to which AppSheet types. Changes to type handling or type support are possible, but those tend to be more costly especially if they have implications for other data sources.

[Daniel_Turner]

Hi Brian,

Thank you for the update. I understand that more advanced types would require more work to support, but is this true for timestamp types? That is such a common and useful datatype and required for some basic features (like being able to see when an entry was made in the app).

[brian]

Timestamp is supported. You can use the NOW() expression in AppSheet and have that save back to a timestamp column just fine. You can save that value into a timestamp with time zone column as well, but as the AppSheet DateTime isn’t timezone aware you won’t actually get a time zone (saves with +00). End to end time zone support for PostgreSQL isn’t on our roadmap and isn’t something we could address in the near term.

There isn’t a great workaround, but you could compare NOW() and UTCNOW() to get an idea as to what the timezone setting is on the client. Date and Time Expressions | AppSheet Help Center

[Daniel_Turner]

The issue our dev expressed is that AppSheet can not read timestamps from PostgreSQL, it can write them to the db, but not read them.

[brian]

I’m not able to reproduce that. Using a PostgreSQL source I can read and display values from timestamp and timestamp with time zone columns. Are you not seeing any data from those columns?

[Daniel_Turner]

Hi Brian, you are correct, sorry I had misunderstood what the dev told me. I just talked with them. They said timestamp types do read in (as you confirmed) but they were unable to use them successfully in security filters. They are using date columns instead.

I am having them send me a filter that they have tried. I am wondering if you can quickly attempt to reproduce.

[Steve]

Daniel_Turner:

they were unable to use them successfully in security filters.

That’s definitely unexpected. DateTime values should work just fine in security filters.

[brian]

I’ve updated the documentation with type mappings at Using Data from PostgreSQL | AppSheet Help Center

[tongyuwang]

The SSL connection to Postgres is still not working with GCP Cloud SQL Postgres. I got "Error

Failed to establish connection. (postgres): 28000: connection requires a valid client certificate" message, but there's no option to configure either the client or server certs.

Wasn't Appsheet acquired by Google almost 3 years ago? It shouldn't take this long to integrate with mothership's products.

[Claudio_Modanes]

Is there any news about the SSL client certificate? We understand that there is a server cert but we would like to activate the flag "Use only SSL connection" in the production database and with this configuration it is not possible.