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.
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âŚ?
I have this same problem and looks like its not been fixed yet. Hopefully being bought by Google should fix the issue.
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.
@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.
Including @Scott_Haaland from our team who can help with this
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
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.
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
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."
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).
Letâs be crystal clear for the next person in my position:
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.
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?
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.
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
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
Thank you Scott, we appreciate it. A quick fix on the timestamp issue (with and without timezone) would be a win!
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.
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).
Iâve updated the documentation with type mappings at Using Data from PostgreSQL | AppSheet Help Center
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
