Skip to content
Percona Software for MySQL Documentation

Rate this page
☆ ☆ ☆ ☆ ☆
Thanks for your feedback
Thank you! The feedback has been submitted.

For help, click the link below to get free database assistance or contact our experts for personalized support.

Get help from Percona

Get started with OpenID Connect authentication

This quickstart sets up OpenID Connect (OIDC) authentication against a Keycloak realm. You install the server-side plugin, configure trust for Keycloak, create a MySQL user, obtain an ID token, and connect. Allow about 20 minutes. For production guidance, the configuration schema, and troubleshooting, see OpenID Connect authentication.

Before you begin

Gather the resources in the following list:

  • Percona Server for MySQL 8.4 with the auth_openid_connect.so library in plugin_dir.

  • A reachable Keycloak server with a configured realm, an OIDC client, and at least one user assigned to a group.

  • TLS, a Unix domain socket, or shared memory between the MySQL client and the server.

  • curl and jq on the workstation that obtains the ID token.

  • A MySQL administrator account with the INSERT privilege on mysql.plugin, the SYSTEM_VARIABLES_ADMIN privilege, and the CREATE USER privilege.

Quickstart values

The page uses the following example values. Replace each one with the equivalent value from your environment.

Item Example value Description
Keycloak host keycloak.example.com Hostname of the Keycloak server
Realm master Keycloak realm name
OIDC client ID mysql-oidc Client registered in the realm
IDP name in MySQL my-keycloak Top-level key in the JSON configuration
Keycloak username alice User in the realm
sub claim value 4c28d537-a635-4b6d-957f-58e3c8860bcc User UUID issued by Keycloak
Keycloak group /accounting Group assigned to the user
MySQL role accounting Role mapped to the group

Install the OIDC plugin

Load the plugin during the current server session:

INSTALL PLUGIN auth_openid_connect SONAME 'auth_openid_connect.so';
Expected output 
Query OK, 0 rows affected (0.01 sec)

Confirm the plugin loaded:

SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME = 'auth_openid_connect';
Expected output 
+---------------------+---------------+
| PLUGIN_NAME         | PLUGIN_STATUS |
+---------------------+---------------+
| auth_openid_connect | ACTIVE        |
+---------------------+---------------+
1 row in set (0.00 sec)

To load the plugin at server start instead, see Install the plugin.

Configure trust for Keycloak

Set the auth_openid_connect_configuration system variable to a JSON document that describes the Keycloak realm. The plugin fetches signing keys from the JWKS endpoint of the realm:

SET GLOBAL auth_openid_connect_configuration = 'JSON://{
  "my-keycloak": {
    "issuer-name": "https://keycloak.example.com/realms/master",
    "jwks-url": "https://keycloak.example.com/realms/master/protocol/openid-connect/certs",
    "audiences": ["account"],
    "group-claim": "groups",
    "group-role": [
      { "/accounting": "accounting" }
    ]
  }
}';
Expected output 
Query OK, 0 rows affected (0.01 sec)

SET GLOBAL lasts only until the next restart. To persist the configuration, run the same statement with SET PERSIST_ONLY. See Persist the configuration.

Create a role, a database, and an OIDC user

Create a small database that the OIDC user will read through the role:

CREATE DATABASE oidc_demo;
CREATE TABLE oidc_demo.invoices (id INT, amount DECIMAL(10, 2));
INSERT INTO oidc_demo.invoices VALUES (1, 99.95), (2, 145.00);
Expected output 
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 2 rows affected (0.00 sec)

Create the MySQL role and grant access to the table:

CREATE ROLE accounting;
GRANT SELECT ON oidc_demo.* TO accounting;
Expected output 
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

Create the OIDC-authenticated user. Replace the user value with the sub claim that Keycloak issues for the account:

CREATE USER 'alice'@'%'
  IDENTIFIED WITH 'auth_openid_connect'
  AS '{"identity_provider": "my-keycloak", "user": "4c28d537-a635-4b6d-957f-58e3c8860bcc"}';
Expected output 
Query OK, 0 rows affected (0.01 sec)

The OIDC user does not need an explicit GRANT for oidc_demo. The user inherits access from the accounting role when the token includes the /accounting group.

Obtain an ID token from Keycloak

Run the following on the workstation. The script writes the ID token to /run/user/1000/id_token.jwt. Replace the placeholder password with the password for alice:

curl -s -X POST \
  https://keycloak.example.com/realms/master/protocol/openid-connect/token \
  -d 'grant_type=password' \
  -d 'client_id=mysql-oidc' \
  -d 'scope=openid' \
  -d 'username=alice' \
  -d 'password=<password>' \
  | jq -r .id_token > /run/user/1000/id_token.jwt

chmod 600 /run/user/1000/id_token.jwt

The password grant suits scripted demos. Production deployments use authorization code with PKCE or device authorization. For details on each step, see Obtain an Identity token.

Note

The plugin requires the ID token, not the access token. The example above selects the id_token field from the Keycloak response.

Connect with the OIDC token

Connect as alice and pass the token file:

mysql --host=mysql.example.com \
      --ssl-mode=REQUIRED \
      --user=alice \
      --authentication-openid-connect-client-id-token-file=/run/user/1000/id_token.jwt

A successful authentication opens the MySQL prompt. The plugin denies the connection on a missing token, an expired token, or an unsecured transport.

Verify the role mapping

Confirm the connected identity:

SELECT CURRENT_USER();
Expected output 
+----------------+
| CURRENT_USER() |
+----------------+
| alice@%        |
+----------------+
1 row in set (0.00 sec)

Activate the role and read from the table:

SET ROLE accounting;
SELECT * FROM oidc_demo.invoices;
Expected output 
Query OK, 0 rows affected (0.00 sec)

+------+--------+
| id   | amount |
+------+--------+
|    1 |  99.95 |
|    2 | 145.00 |
+------+--------+
2 rows in set (0.00 sec)

SET ROLE accounting succeeds because the token contains /accounting in the groups claim. A user without the group sees ERROR 3530 (HY000).

Clean up

Remove the demo objects when you finish:

DROP USER 'alice'@'%';
DROP ROLE accounting;
DROP DATABASE oidc_demo;
Expected output 
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)

To remove the plugin and any UDFs, see Uninstall the plugin.

Additional resources