Keycloak

1
<#import "/templates/guide.adoc" as tmpl>
2
<#import "/templates/kc.adoc" as kc>
3
<#import "/templates/options.adoc" as opts>
4
<#import "/templates/links.adoc" as links>
5
<#import "/templates/profile.adoc" as profile>
6

7
<@tmpl.guide
8
    title="Configuring the database"
9
    summary="An overview about how to configure relational databases"
10
    includedOptions="db db-* transaction-xa-enabled">
11

12
This {section} explains how to configure the {project_name} server to store data in a relational database.
13

14
== Supported databases
15

16
The server has built-in support for different databases. You can query the available databases by viewing the expected values for the `db` configuration option. The following table lists the supported databases and their tested versions.
17

18
[%autowidth]
19
|===
20
|Database | Option value | Tested Version
21

22
|MariaDB Server | `mariadb` | ${properties["mariadb.version"]}
23
|Microsoft SQL Server | `mssql` | ${properties["mssql.version"]?replace("-latest","")}
24
|MySQL | `mysql` | ${properties["mysql.version"]}
25
|Oracle Database | `oracle` | ${properties["oracledb.version"]}
26
|PostgreSQL | `postgres` | ${properties["postgresql.version"]}
27
|Amazon Aurora PostgreSQL | `postgres` | ${properties["aurora-postgresql.version"]}
28
|===
29

30
By default, the server uses the `dev-file` database. This is the default database that the server will use to persist data and
31
only exists for development use-cases. The `dev-file` database is not suitable for production use-cases, and must be replaced before deploying to production.
32

33
<@profile.ifProduct>
34

35
== Installing a database driver
36

37
Database drivers are shipped as part of {project_name} except for the Oracle Database and Micrsoft SQL Server drivers which need to be installed separately.
38

39
Install the necessary driver if you want to connect to one of these databases or skip this section if you want to connect to a different database for which the database driver is already included.
40

41
=== Installing the Oracle Database driver
42

43
To install the Oracle Database driver for {project_name}:
44

45
. Download the `ojdbc11` and `orai18n` JAR files from one of the following sources:
46

47
.. *Zipped JDBC driver and Companion Jars* version ${properties["oracle-jdbc.version"]} from the https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html[Oracle driver download page].
48

49
.. Maven Central via `link:++https://repo1.maven.org/maven2/com/oracle/database/jdbc/ojdbc11/${properties["oracle-jdbc.version"]}/ojdbc11-${properties["oracle-jdbc.version"]}.jar++[ojdbc11]` and `link:++https://repo1.maven.org/maven2/com/oracle/database/nls/orai18n/${properties["oracle-jdbc.version"]}/orai18n-${properties["oracle-jdbc.version"]}.jar++[orai18n]`.
50

51
.. Installation media recommended by the database vendor for the specific database in use.
52

53
. When running the unzipped distribution: Place the `ojdbc11` and `orai18n` JAR files in {project_name}'s `providers` folder
54

55
. When running containers: Build a custom {project_name} image and add the JARs in the `providers` folder. When building a custom image for the Keycloak Operator, those images need to be optimized images with all build-time options of Keycloak set.
56
+
57
A minimal Dockerfile to build an image which can be used with the {project_name} Operator and includes Oracle Database JDBC drivers downloaded from Maven Central looks like the following:
58
+
59
[source,dockerfile,subs="attributes+"]
60
----
61
FROM quay.io/keycloak/keycloak:{containerlabel}
62
ADD --chown=keycloak:keycloak https://repo1.maven.org/maven2/com/oracle/database/jdbc/ojdbc11/${properties["oracle-jdbc.version"]}/ojdbc11-${properties["oracle-jdbc.version"]}.jar /opt/keycloak/providers/ojdbc11.jar
63
ADD --chown=keycloak:keycloak https://repo1.maven.org/maven2/com/oracle/database/nls/orai18n/${properties["oracle-jdbc.version"]}/orai18n-${properties["oracle-jdbc.version"]}.jar /opt/keycloak/providers/orai18n.jar
64
# Setting the build parameter for the database:
65
ENV KC_DB=oracle
66
# Add all other build parameters needed, for example enable health and metrics:
67
ENV KC_HEALTH_ENABLED=true
68
ENV KC_METRICS_ENABLED=true
69
# To be able to use the image with the {project_name} Operator, it needs to be optimized, which requires {project_name}'s build step:
70
RUN /opt/keycloak/bin/kc.sh build
71
----
72
+
73
See the <@links.server id="containers" /> {section} for details on how to build optimized images.
74

75
Then continue configuring the database as described in the next section.
76

77
=== Installing the Microsoft SQL Server driver
78

79
To install the Microsoft SQL Server driver for {project_name}:
80

81
. Download the `mssql-jdbc` JAR file from one of the following sources:
82

83
.. Download a version from the https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server[Microsoft JDBC Driver for SQL Server page].
84

85
.. Maven Central via `link:++https://repo1.maven.org/maven2/com/microsoft/sqlserver/mssql-jdbc/${properties["mssql-jdbc.version"]}/mssql-jdbc-${properties["mssql-jdbc.version"]}.jar++[mssql-jdbc]`.
86

87
.. Installation media recommended by the database vendor for the specific database in use.
88

89
. When running the unzipped distribution: Place the `mssql-jdbc` in {project_name}'s `providers` folder
90

91
. When running containers: Build a custom {project_name} image and add the JARs in the `providers` folder. When building a custom image for the {project_name} Operator, those images need to be optimized images with all build-time options of {project_name} set.
92
+
93
A minimal Dockerfile to build an image which can be used with the {project_name} Operator and includes Microsoft SQL Server JDBC drivers downloaded from Maven Central looks like the following:
94
+
95
[source,dockerfile,subs="attributes+"]
96
----
97
FROM quay.io/keycloak/keycloak:{containerlabel}
98
ADD --chown=keycloak:keycloak https://repo1.maven.org/maven2/com/microsoft/sqlserver/mssql-jdbc/${properties["mssql-jdbc.version"]}/mssql-jdbc-${properties["mssql-jdbc.version"]}.jar /opt/keycloak/providers/mssql-jdbc.jar
99
# Setting the build parameter for the database:
100
ENV KC_DB=mssql
101
# Add all other build parameters needed, for example enable health and metrics:
102
ENV KC_HEALTH_ENABLED=true
103
ENV KC_METRICS_ENABLED=true
104
# To be able to use the image with the {project_name} Operator, it needs to be optimized, which requires {project_name}'s build step:
105
RUN /opt/keycloak/bin/kc.sh build
106
----
107
+
108
See the <@links.server id="containers" /> {section} for details on how to build optimized images.
109

110
Then continue configuring the database as described in the next section.
111

112
</@profile.ifProduct>
113

114
== Configuring a database
115

116
For each supported database, the server provides some opinionated defaults to simplify database configuration. You complete the configuration by providing some key settings such as the database host and credentials.
117

118
. Start the server and set the basic options to configure a database
119
+
120
<@kc.start parameters="--db postgres --db-url-host mypostgres --db-username myuser --db-password change_me"/>
121
+
122

123
This command includes the minimum settings needed to connect to the database.
124

125
The default schema is `keycloak`, but you can change it by using the `db-schema` configuration option.
126

127
[WARNING]
128
Do NOT use the `--optimized` flag for the `start` command if you want to use a particular DB (except the H2).
129
Executing the build phase before starting the server instance is necessary.
130
You can achieve it either by starting the instance without the `--optimized` flag,
131
or by executing the `build` command before the optimized start.
132
For more information, see <@links.server id="configuration"/>.
133

134
== Overriding default connection settings
135

136
The server uses JDBC as the underlying technology to communicate with the database. If the default connection settings are insufficient, you can specify a JDBC URL using the `db-url` configuration option.
137

138
The following is a sample command for a PostgreSQL database.
139

140
<@kc.start parameters="--db postgres --db-url jdbc:postgresql://mypostgres/mydatabase"/>
141

142
Be aware that you need to escape characters when invoking commands containing special shell characters such as `;` using the CLI, so you might want to set it in the configuration file instead.
143

144
== Overriding the default JDBC driver
145

146
The server uses a default JDBC driver accordingly to the database you chose.
147

148
To set a different driver you can set the `db-driver` with the fully qualified class name of the JDBC driver:
149

150
<@kc.start parameters="--db postgres --db-driver=my.Driver"/>
151

152
Regardless of the driver you set, the default driver is always available at runtime.
153

154
Only set this property if you really need to. For instance, when leveraging the capabilities from a JDBC Driver Wrapper for
155
a specific cloud database service.
156

157
== Configuring Unicode support for the database
158

159
Unicode support for all fields depends on whether the database allows VARCHAR and CHAR fields to use the Unicode character set.
160

161
* If these fields can be set, Unicode is likely to work, usually at the expense of field length.
162
* If the database only supports Unicode in the NVARCHAR and NCHAR fields, Unicode support for all text fields is unlikely to work because the server schema uses VARCHAR and CHAR fields extensively.
163

164
The database schema provides support for Unicode strings only for the following special fields:
165

166
* *Realms*: display name, HTML display name, localization texts (keys and values)
167

168
* *Federation* Providers: display name
169

170
* *Users*: username, given name, last name, attribute names and values
171

172
* *Groups*: name, attribute names and values
173

174
* *Roles*: name
175

176
* Descriptions of objects
177

178
Otherwise, characters are limited to those contained in database encoding, which is often 8-bit. However, for some database systems, you can enable UTF-8 encoding of Unicode characters and use the full Unicode character set in all text fields. For a given database, this choice might result in a shorter maximum string length than the maximum string length supported by 8-bit encodings.
179

180
=== Configuring Unicode support for an Oracle database
181

182
Unicode characters are supported in an Oracle database if the database was created with Unicode support in the VARCHAR and CHAR fields. For example, you configured AL32UTF8 as the database character set. In this case, the JDBC driver requires no special settings.
183

184
If the database was not created with Unicode support, you need to configure the JDBC driver to support Unicode characters in the special fields. You configure two properties. Note that you can configure these properties as system properties or as connection properties.
185

186
. Set `oracle.jdbc.defaultNChar` to `true`.
187

188
. Optionally, set `oracle.jdbc.convertNcharLiterals` to `true`.
189
+
190
[NOTE]
191
====
192
For details on these properties and any performance implications, see the Oracle JDBC driver configuration documentation.
193
====
194

195
=== Unicode support for a Microsoft SQL Server database
196

197
Unicode characters are supported only for the special fields for a Microsoft SQL Server database. The database requires no special settings.
198

199
The `sendStringParametersAsUnicode` property of JDBC driver should be set to `false` to significantly improve performance. Without this parameter,
200
the Microsoft SQL Server might be unable to use indexes.
201

202
=== Configuring Unicode support for a MySQL database
203

204
Unicode characters are supported in a MySQL database if the database was created with Unicode support in the VARCHAR and CHAR fields when using the CREATE DATABASE command.
205

206
Note that the utf8mb4 character set is not supported due to different storage requirements for the utf8 character set. See MySQL documentation for details. In that situation, the length restriction on non-special fields does not apply because columns are created to accommodate the number of characters, not bytes.  If the database default character set does not allow Unicode storage, only the special fields allow storing Unicode values.
207

208
. Start MySQL Server.
209
. Under JDBC driver settings, locate the *JDBC connection settings*.
210
. Add this connection property: `characterEncoding=UTF-8`
211

212
=== Configuring Unicode support for a PostgreSQL database
213

214
Unicode is supported for a PostgreSQL database when the database character set is UTF8. Unicode characters can be used in any field with no reduction of field length for non-special fields. The JDBC driver requires no special settings. The character set is determined when the PostgreSQL database is created.
215

216
. Check the default character set for a PostgreSQL cluster by entering the following SQL command.
217
+
218
[source]
219
----
220
show server_encoding;
221
----
222

223
. If the default character set is not UTF 8, create the database with the UTF8 as the default character set using a command such as:
224

225
+
226
[source]
227
----
228
create database keycloak with encoding 'UTF8';
229
----
230

231
[[preparing-keycloak-for-amazon-aurora-postgresql]]
232
== Preparing for Amazon Aurora PostgreSQL
233

234
When using Amazon Aurora PostgreSQL, the https://github.com/awslabs/aws-advanced-jdbc-wrapper[Amazon Web Services JDBC Driver] offers additional features like transfer of database connections when a writer instance changes in a Multi-AZ setup.
235
This driver is not part of the distribution and needs to be installed before it can be used.
236

237
To install this driver, apply the following steps:
238

239
. When running the unzipped distribution: Download the JAR file from the https://github.com/awslabs/aws-advanced-jdbc-wrapper/releases/[Amazon Web Services JDBC Driver releases page] and place it in {project_name}'s `providers` folder.
240

241
. When running containers: Build a custom {project_name} image and add the JAR in the `providers` folder.
242
+
243
A minimal Dockerfile to build an image which can be used with the {project_name} Operator looks like the following:
244
+
245
[source,dockerfile,subs="attributes+"]
246
----
247
FROM quay.io/keycloak/keycloak:{containerlabel}
248
ADD --chmod=0666 https://github.com/awslabs/aws-advanced-jdbc-wrapper/releases/download/${properties["aws-jdbc-wrapper.version"]}/aws-advanced-jdbc-wrapper-${properties["aws-jdbc-wrapper.version"]}.jar /opt/keycloak/providers/aws-advanced-jdbc-wrapper.jar
249
----
250
+
251
See the <@links.server id="containers" /> {section} for details on how to build optimized images, and the <@links.operator id="customizing-keycloak" /> {section} on how to run optimized and non-optimized images with the {project_name} Operator.
252
. Configure {project_name} to run with the following parameters:
253
`db-url`:: Insert `aws-wrapper` to the regular PostgreSQL JDBC URL resulting in a URL like `+jdbc:aws-wrapper:postgresql://...+`.
254
`db-driver`:: Set to `software.amazon.jdbc.Driver` to use the AWS JDBC wrapper.
255
`transaction-xa-enabled`:: Set to `false`, as the Amazon Web Services JDBC Driver does not support XA transactions.
256

257
== Preparing for MySQL server
258

259
Beginning with MySQL 8.0.30, MySQL supports generated invisible primary keys for any InnoDB table that is created without an explicit primary key (more information https://dev.mysql.com/doc/refman/8.0/en/create-table-gipks.html[here]).
260
If this feature is enabled, the database schema initialization and also migrations will fail with the error message `Multiple primary key defined (1068)`.
261
You then need to disable it by setting the parameter `sql_generate_invisible_primary_key` to `OFF` in your MySQL server configuration before installing or upgrading Keycloak.
262

263
== Changing database locking timeout in a cluster configuration
264

265
Because cluster nodes can boot concurrently, they take extra time for database actions. For example, a booting server instance may perform some database migration, importing, or first time initializations. A database lock prevents start actions from conflicting with each other when cluster nodes boot up concurrently.
266

267
The maximum timeout for this lock is 900 seconds. If a node waits on this lock for more than the timeout, the boot fails. The need to change the default value is unlikely, but you can change it by entering this command:
268

269
<@kc.start parameters="--spi-dblock-jpa-lock-wait-timeout 900"/>
270

271
== Using Database Vendors without XA transaction support
272
{project_name} uses XA transactions and the appropriate database drivers by default. Certain vendors, such as Azure SQL and MariaDB Galera, do not support or rely on the XA transaction mechanism. To use Keycloak without XA transaction support using the appropriate JDBC driver, enter the following command:
273

274
<@kc.build parameters="--db=<vendor> --transaction-xa-enabled=false"/>
275

276
{project_name} automatically chooses the appropriate JDBC driver for your vendor.
277

278
== Setting JPA provider configuration option for migrationStrategy
279

280
To setup the JPA migrationStrategy (manual/update/validate) you should setup JPA provider as follows:
281

282
.Setting the `migration-strategy` for the `quarkus` provider of the `connections-jpa` SPI
283
<@kc.start parameters="--spi-connections-jpa-quarkus-migration-strategy=manual"/>
284

285
If you want to get a SQL file for DB initialization, too, you have to add this additional SPI initializeEmpty (true/false):
286

287
.Setting the `initialize-empty` for the `quarkus` provider of the `connections-jpa` SPI
288
<@kc.start parameters="--spi-connections-jpa-quarkus-initialize-empty=false"/>
289

290
In the same way the migrationExport to point to a specific file and location:
291

292
.Setting the `migration-export` for the `quarkus` provider of the `connections-jpa` SPI
293
<@kc.start parameters="--spi-connections-jpa-quarkus-migration-export=<path>/<file.sql>"/>
294

295
</@tmpl.guide>
296