Docs: Free tier connection string

[kannanlakshmi]

Our current free tier connection string doesn't work for a variety of tools/languages/ORMs. This is a known issue and we're working on fixing it within the UI itself where we offer more details/ options for different languages. The docs team is also re-working the "Hello World" sample apps to include CC but CC free will still be a different experience in some cases compared to CC Dedicated.

Question for docs and appdev: whats the best way to approach these issues that were identified in the free tier fixit in 2020, should we solve these in the Hello World Samples and separate CC free from CC dedicated there? Should we add these as FAQs in the CC free tier docs? My main concern is that we don't wait for a perfect connect modal or set of docs in favor of some short term fixes for just the high priority tools so that these situations don't happen tripping up users . Any thoughts appreciated from app dev and docs team.

1. Java - Hibernate. Free tier string simply does not work as currently displayed in the UI.
   Suggestions from:
   @ianjevans
   Need to prefix jdbc:postgresql to connection string. Need to modify ampersand to &, remove username/password from URL, and replace defaultdb to bank. Doing all that will allow the user to run successfully.

from @vy-ton
"#1: Needed to replace & with & in the connection url
This was what ended up working:
jdbc:postgresql://free-tier.gcp-us-central1.crdb.io:26257/cold-jackal-94.defaultdb?sslmode=verify-full&sslrootcert=/Users/vyton/certs/cc-ca.crt
vy
woc7SQES_taiy5shop"

from @rafiss

• had to put username/password as separate properties, and remove username/password from the URL string
• needed to replace postgres:// with postgresql://
• had to replace all & with &

1. Spring JDBC Seems to not work but need to test this. No suggestions here.

2. DBeaver Some issues
   From @hand-crdb
   Docs assume certificate-based auth, but I overheard that the free tier does not support it (yet?). Also the I don't have permissions to execute the command as listed in the doc to copy the cockroach command to /usr/local/bin/ on my Mac
   From @solongordon
   Our docs are a bit out of date here. The latest DBeaver interfaces look somewhat different from the screenshots.

3. Flyway
   From @solongordon
   Works, but I had to remove the username and password from the connection string and specify them separately. Also had to change "postgres" to "jdbc:postgresql".

4. Liquibase
   @jhatcher9999
   Unexpected error running Liquibase: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: Connection could not be created to jdbc:postgres://jim:[email protected]:26257/proud-cougar-75.defaultdb?sslmode=verify-full&sslrootcert=/Users/jimhatcher/cc_certs/cc-ca.crt with driver org.postgresql.Driver. Possibly the wrong driver for the given database URL
   "I used these steps: https://www.cockroachlabs.com/docs/v20.2/liquibase.html
   I skipped Step 3 in the instructions.
   I tried replacing ""postgres"" with ""postgresql"" and got a different (better?) error:
   Unexpected error running Liquibase: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: Connection could not be created to jdbc:postgresql://jim:[email protected]:26257/defaultdb?sslmode=verify-full&sslrootcert=/Users/jimhatcher/cc_certs/cc-ca.crt&options=--cluster=proud-cougar-75 with driver org.postgresql.Driver. The connection attempt failed.

5. Ruby-active record Not tested but high priority

cc @emily-horing who is working on connect modal, and @timveil who has run into some of these issues

[vy-ton]

A couple of thoughts

• The Use CockroachCloud tab uses client cert-based authentication. As we expand our third-party tool suite, I personally don't think it makes sense to keep cert authentication in our Hello World sample apps, which are designed to be a basic intro to CRDB. If we have dedicated cert authentication code samples, we don't have to worry about creating a sample for every supported third-party tool. cc @piyush-singh
• The Hello World sample apps now have 2 tabs - Use a Local Cluster and Use CockroachCloud. SQL Experience with @ericharmeling and @ianjevans want the CockroachCloud tab to be Free Tier, however, we were waiting for the GA release. We would also need to address the first bullet since CC-Free does not have client cert-based authentication.

[ianjevans]

Eric and I are removing the certificate-based authentication steps of the Hello World tutorials as we revise them to use demo and Free Tier.

See #9793.

[piyush-singh]

The Use CockroachCloud tab uses client cert-based authentication

We do not yet support cert based authentication in CockroachCloud. All connections are authenticated using username and password. The CA certificate that is passed in the connection string is to verify the identity of the CockroachCloud server, not of the client. I think it is important to include this as removing it by using something like sslmode=require could lead to developers making apps that are susceptible to MITM attacks. It is probably not necessary for a sample app that doesn't contain sensitive data, but I think including it because we recommend it as best practice is a good idea.

[ianjevans]

To clarify here on the docs side: the CockroachCloud Free Tier steps/code for the HelloWorld tutorials use username/password with the CA cert using sslmode=verify-full.

[vy-ton]

@piyush-singh Just to confirm what Ian commented will work with Free Tier?

[piyush-singh]

Yes, that should work!

[ericharmeling]

@piyush-singh

We do not yet support cert based authentication in CockroachCloud.

I just published a note to the developer docs about CC not supporting cert-based user authentication (https://github.com/cockroachdb/docs/pull/9869). Hopefully this helps clear things up a bit.

@kannanlakshmi

Spring JDBC Seems to not work but need to test this. No suggestions here.

I tested Spring Data JDBC and Spring Data JPA (with https://github.com/cockroachlabs/roach-data/tree/master) against a Free Tier cluster, and both work just fine. I did not, however, test Spring Data R2DBC, which is what the user in this post is using. It looks like the connection configuration for R2DBC is fairly different from that of JDBC and JPA. @kai-niemi would adding an R2DBC variant to roach-data make sense?

I opened a PR adding a Free Tier workflow to the JDBC and JPA Spring tutorials (https://github.com/cockroachdb/docs/pull/9874). That workflow has instructions for connecting from a Spring app using Spring Data JDBC and Spring Data JPA.

[timveil]

re: R2DBC i'm working on a standalone R2DBC example now and struggling. @ericharmeling you are right it is significantly different from JPA and traditional JDBC. Is there a human readable for of #9874 so that i could take a peak at those documentation changes?

[ericharmeling]

@timveil

re: R2DBC i'm working on a standalone R2DBC example now and struggling.

Godspeed!

Is there a human readable for of #9874 so that i could take a peak at those documentation changes?

This should work:
Updated link
http://cockroach-docs-review.s3-website-us-east-1.amazonaws.com/69b4fcd097204a045da8d3698fbef5d97a7918c4/v21.1/build-a-spring-app-with-cockroachdb-jdbc.html?filters=cockroachcloud#step-6-run-the-application

[kai-niemi]

A reactive R2DBC example in roach-data makes a lot of sense in contrast to the other frameworks. Not sure yet how it will play with CRDB yet but could give it a go.

[timveil]

finished my R2DBC examples here https://github.com/timveil-cockroach/spring-examples/tree/master/reactive

not documented but functional for local insecure, local secure and free tier