Trouble with Keycloak 20.0.1 as a Azure webapp and pgsql backend

Hi, having trouble getting an azure webapp with Keycloak docker image running with pgsql backend. Running fine without pgsql backend, but fails when trying to configure this. Anyone able to tell me what I am doing wrong?

Deploying with terraform and these environmental variables/app settings for pgsql:

KC_DB = "postgres"
KC_DB_URL_DATABASE = "keycloak"
KC_DB_URL_HOST = "mypgsql.postgres.database.azure.com"
KC_DB_URL_PORT             = 5432
KC_DB_URL_PROPERTIES = "?sslmode=require" (tried with and without this setting)
KC_DB_SCHEMA               = "public"
KC_DB_USERNAME = "user"
KC_DB_PASSWORD = "pass"
KC_TRANSACTION_XA_ENABLED = false (tried with and without this setting)

Error I am getting is:

ERROR: URL format error; must be “jdbc:h2:{ {.|mem:}[name] | [file:]fileName | {tcp|ssl}:[//]server[:port][,server2[:port]]/name }[;key=value…]” but is “jdbc:postgresql://mypgsql.postgres.database.azure.com:5432/keycloak” [90046-214]

1 Like

This happened to me too

I believe you need to put Download | pgJDBC in a folder called providers with an XML file

<?xml version="1.0" ?>
<module xmlns="urn:jboss:module:1.3" name="com.postgres">
 <resources>
  <resource-root path="postgresql-42.2.20.jar" />
 </resources>
 <dependencies>
  <module name="javax.api"/>
  <module name="javax.transaction.api"/>
 </dependencies>
</module>

If that dosen’t work than maybe your DB is using ipv6 which you’d have to add

JAVA_OPTS = “-D java.net.preferIPv6Addresses=true”

That fixed it for me
Hope that helps

Thank you for replying, I am really stuck here so I appreciate all help I can get. Have not had time to test your suggestion yet, but I am not sure if this is really the problem.

When I try running keycloak in docker locally on my own PC towards the azure postgres server it works fine, creating tables in postgres. But not when running docker image from a azure web app. I have set the postgres server to accept all connections coming from azure environment, but still no luck.

This works locally when running in dev mode:

docker run -p 8080:8080 --name kcpg -d -e KEYCLOAK_ADMIN=admin -e KEYCLOAK_ADMIN_PASSWORD=admin -e KC_DB=postgres -e KC_DB_URL=jdbc:postgresql://mypgsql.postgres.database.azure.com:5432/keycloak?user=myadmin&password=mypass&sslmode=require -e KC_DB_USERNAME=myadmin -e KC_DB_PASSWORD=mypass quay.io/keycloak/keycloak:latest start-dev

What command are you using to run the container with the postgres db?

I would recommend following the steps to create your own, optimized image for production. Try the steps here: Running Keycloak in a container - Keycloak

I finally found out how to solve it. I found and used the same connectionstring generated in the keycloak docker running locally using azure pgsql as backend. Using the same constring worked perfect when deploying azure web app with terraform.

jdbc:postgresql://mypgsql.postgres.database.azure.com:5432/keycloak?adaptiveFetch=false&adaptiveFetchMaximum=-1&adaptiveFetchMinimum=0&allowEncodingChanges=false&ApplicationName=PostgreSQL+JDBC+Driver&autosave=never&binaryTransfer=true&binaryTransferDisable=&binaryTransferEnable=&cancelSignalTimeout=10&cleanupSavepoints=false&connectTimeout=10&databaseMetadataCacheFields=65536&databaseMetadataCacheFieldsMiB=5&defaultRowFetchSize=0&disableColumnSanitiser=false&escapeSyntaxCallMode=select&groupStartupParameters=false&gssEncMode=allow&gsslib=auto&hideUnprivilegedObjects=false&hostRecheckSeconds=10&jaasApplicationName=pgjdbc&jaasLogin=true&loadBalanceHosts=false&loginTimeout=0&logServerErrorDetail=true&logUnclosedConnections=false&preferQueryMode=extended&preparedStatementCacheQueries=256&preparedStatementCacheSizeMiB=5&prepareThreshold=5&quoteReturningIdentifiers=true&readOnly=false&readOnlyMode=transaction&receiveBufferSize=-1&reWriteBatchedInserts=false&sendBufferSize=-1&socketTimeout=0&sslResponseTimeout=5000&sspiServiceClass=POSTGRES&targetServerType=any&tcpKeepAlive=false&tcpNoDelay=true&unknownLength=2147483647&useSpnego=false&xmlFactoryFactory=

It is a half page long, but it works.

Could anyone please post the ms sql equivalent connectionstring for me?

I still have not managed to get it to run against ms sql, so if you have; can you please check in

/admin/master/console/#/master/providers

and under “connectionsJpa” you will find the connectionstring. If anyone can give me a clue to how this one looks like, it will be greatly appreciated.