Using Cloud SQL as database for Keycloak

Question

We are using kubernetes to host an instance of keycloak 4.0.0.Final together with a postgres 9.6 database as storage.

This works well, but now we want to move the database to a hosted Cloud SQL instance.

The Cloud SQL instance is setup, running and I can connect from my local machine with psql using SSL as well as through keycloak using non-SSL. Obviously, we want to connect using SSL for keycloak as well.

However, when I add the JDBC SSL configuration to keycloak:

ssl=true&sslmode=verify-ca&sslcert=/certs/client-cert.pem&sslkey=/certs/client-key.pem&sslrootcert=/certs/server-ca.pem

I get an error from keycloak saying:

org.postgresql.util.PSQLException: Could not read SSL key file /certs/client-key.pem

and in the stack trace:

Caused by: java.io.IOException: extra data given to DerValue constructor

Investigating this error has lead me to these reports, but not closer to finding a solution.

https://github.com/Graylog2/graylog2-server/issues/4304

Reading an X.509 certificate with Java

Did you get any progess on this? I'm trying to do the same on with a service running on the CloudRun, having a keycloak database hosted on a Cloud SQL instance. — Miguel Rueda, Apr 15 '21 at 23:06
This was so long ago I don't even remember, but I think not. — bialad, Apr 17 '21 at 11:15
@MiguelRueda, hi. I'm on a similar path right now. Cloud Run was my first choice too but [this thread](https://stackoverflow.com/questions/59130538/run-keycloak-with-googlecloud-run) made me reconsider the options. They say keycloak is a statefull application and Cloud Run is intended only for stateless. I guess by now you've figured this out by yourself. In my situation (I'm too new to GCP) I have to test out setups on App Engine and on Kubernetes Engine Autopilot - too many initial considerations to pick up easily which one to rely on in production. — johnniepop, Jan 06 '22 at 11:15

score 3 · Answer 1 · answered May 04 '21 at 18:12

Something that worked for me was creating a GAE(App Engine) service. In order to do that I activated the private IPv4 address on the Cloud SQL console and then create a VPC for serverless connection. The following was the docker file I used to build up keycloak's service.

FROM quay.io/keycloak/keycloak:latest

ENV DB_VENDOR postgres
ENV DB_ADDR <private_ipv4>
ENV DB_DATABASE <postgres_db>
ENV DB_SCHEMA public
ENV DB_USER postgres
ENV DB_PASSWORD postgres
ENV KEYCLOAK_USER admin
ENV KEYCLOAK_PASSWORD admin
ENV PROXY_ADDRESS_FORWARDING true
ENV JAVA_OPTS -server -Xms2048m -Xmx6144m -XX:MetaspaceSize=96M -XX:MaxMetaspaceSize=256m \
    -Djava.net.preferIPv4Stack=true -Djboss.modules.system.pkgs=org.jboss.byteman \
    -Djava.awt.headless=true

ENV PORT 8080 
EXPOSE $PORT

In order to build the service on the Google app's engine, you need to provide a flex env file like this:

runtime: custom
# https://cloud.google.com/appengine/docs/flexible/python/customizing-the-python-runtime
env: flex
service: neurorad-keycloak
manual_scaling:
     instances: 1
resources:
  cpu: 2
  memory_gb: 8
  disk_size_gb: 10
liveness_check:
  path: "/"
  check_interval_sec: 30
  timeout_sec: 10
  failure_threshold: 5
  success_threshold: 2
  initial_delay_sec: 300
readiness_check:
  path: "/"
  timeout_sec: 10
  check_interval_sec: 30
  failure_threshold: 5
  success_threshold: 2
  app_start_timeout_sec: 180

And finally build the service.

gcloud app deploy --appyaml=./app.yaml

I can confirm that this setup works. Interestingly with MySQL I always ended up with that admin user not being setup, which was kind of a lockup. — johnniepop, Jan 06 '22 at 14:52
The only thing missing from this example `app.yaml` file is the declaration of the VPC connection you mention in the beginning. Something like: `vpc_access_connector: name: "projects/PROJECT_ID/locations/REGION/connectors/CONNECTOR_NAME"` — johnniepop, Jan 06 '22 at 15:02

Using Cloud SQL as database for Keycloak

1 Answers1