OAuth2 and JWT Logins for MySQL

An engineer leaves your company. You disable their single sign-on account, and their access to the wiki, the cloud console, and the CI system goes with it. Typically, access to database resources doesn't follow such a simple plan. Database user and role maintenance too often happens within the database, oblivious to single sign-on. User accounts carry a password which has to be managed and rotated (and the password is probably already sprawled out across shell history and a shared password manager).

An easier way to manage this is to bring the identity-provider model to the database. This is what the VillageSQL Server vsql-oauth2 extension does. It adds an authentication method that accepts an OAuth2/OpenID Connect token (a JWT) from your identity provider in place of a password. Your identity provider decides who can log in, MySQL privileges decide what they can do inside, and disabling someone stops them getting another token. The token already in hand works until it expires, so your provider's token lifetime dictates the access window.

VillageSQL is the innovation platform for MySQL that adds an extension framework (similar to PostgreSQL's extension framework) to enable permissionless innovation. Instead of waiting for a feature to be implemented in a future version of MySQL in a few years, new functionality can be dynamically added to the version of MySQL you run today. VillageSQL Server supports MySQL 8.4, 9.7, and Percona Server 8.4. The vsql-oauth2 extension is an example of what is meant by permissionless innovation.

The rest of this post shows how the vsql-oauth2 extension works. The examples use Microsoft Entra ID, because Entra puts readable role names directly into the token. We start with the simple case: install the extension, point it at Entra's signing keys, and log in to the database with a token instead of a password. We then add role mapping, so a person the database has never seen before can log in for the first time and come away with an account and the roles their identity provider assigned them.

Set up a working demo of passwordless MySQL logins on my machine, using the
vsql-oauth2 extension for VillageSQL. Act as my own identity provider so no real
IdP is needed. Work only against a local throwaway server — if the only
VillageSQL or MySQL server you find looks like something I depend on, stop and
ask me before touching it.

Do all of this yourself, and show me the real output of each step:

1. Find a VillageSQL server, or install one. The install script needs a method:
   `curl -fsSL https://install.villagesql.com | INSTALL_METHOD=prebuilt bash`.
   Start it with `--vsql_allow_preview_extensions=ON`; on a server already
   running, `SET PERSIST vsql_allow_preview_extensions=ON` takes effect at once.
   Confirm with `SELECT VERSION()` before continuing.

2. Install the extension: `INSTALL EXTENSION vsql_oauth2;`. It ships with the
   server, so there is nothing to download. Read
   `INFORMATION_SCHEMA.EXTENSION_REGISTRATION` and
   `SHOW GLOBAL VARIABLES LIKE 'vsql_oauth2.%'` and tell me what settings exist.

3. Stand in for the identity provider. Generate an RSA keypair with openssl, and
   write a small script that mints signed RS256 JWTs from a claims payload, each
   header carrying a `kid`. Use openssl only — do not install a JWT library.
   Then publish the public key the way a real provider does: serve a JWKS
   document over HTTP on localhost, holding the key as a JWK with `kty`, `n`,
   `e` and that same `kid`. Let the operating system choose the port rather than
   picking one yourself, and tell me the URL.

4. Do the basic case. Set `issuer`, `audience` and `username_claim`, and point
   `jwks_url` at the JWKS URL from step 3. Create an account bound to
   `vsql_oauth2`, create the account it maps onto, and grant the proxy. Then log
   in with a token in place of the password and show me
   `SELECT CURRENT_USER(), @@external_user;`. Show me your JWKS server's own log
   as evidence that the database really fetched the keys.

5. Do the joiner case. Turn on `roles_claim`, `roles_filter`,
   `roles_transform_pattern`, `roles_transform_replacement`, `auto_create` and
   `auto_grant`. Create two roles named in the rewritten form the transform
   produces, then log in as a user who has never touched this database with a
   token carrying a roles claim. Show me that the account and the role grant both
   appeared, and that the role is active for the session.

6. Try to break it. Take a token you have just shown works and change exactly
   one thing at a time: expire it, alter the issuer, alter the audience, name a
   `kid` your JWKS document does not carry, strip the signature with
   `alg: none`, and re-sign it with HMAC using the public key as the shared
   secret. Show me all six refused. Every refusal prints the same message, so
   for each one name the single change you made, and log in with the unchanged
   token in the same run to prove the refusal came from that change.

Then give me a table of what you ran and what came back, tell me anything that
did not behave the way this asked, and drop every user, role and setting you
created, stop the JWKS server, and leave my server back where it started.

Log in without a database password

Below is the server-side setup for the basic case, using Microsoft Entra ID:

INSTALL EXTENSION vsql_oauth2;

SET GLOBAL vsql_oauth2.issuer   = 'https://login.microsoftonline.com/<tenant-guid>/v2.0';
SET GLOBAL vsql_oauth2.jwks_url = 'https://login.microsoftonline.com/<tenant-guid>/discovery/v2.0/keys';
SET GLOBAL vsql_oauth2.audience = '<database-app-client-id>';
SET GLOBAL vsql_oauth2.username_claim = 'preferred_username';

CREATE USER oauth_user IDENTIFIED WITH vsql_oauth2;
CREATE USER 'dana@myco.example';
GRANT SELECT ON *.* TO 'dana@myco.example';
GRANT PROXY ON 'dana@myco.example' TO oauth_user;

jwks_url is Entra's key endpoint. The extension fetches the signing keys from it and refreshes them hourly by default, so Entra can rotate its keys without anyone touching the database. The public_key setting is the alternative: it pins one key you paste in yourself, which suits a self-signed test and stops working at the provider's next rotation. Set both and jwks_url wins.

Point audience at the app registration that stands for the database, and send Entra's access token for that app rather than the id_token. Only the access token carries App Roles under the readable names the next section filters on. The README's provider settings cover the app registrations Entra needs and the values for other providers.

From there, the standard mysql client logs in with a token where the password would normally go. Passing it through MYSQL_PWD keeps it out of the process list:

$ MYSQL_PWD='<the JWT>' mysql --enable-cleartext-plugin --user=oauth_user \
    -e "SELECT CURRENT_USER(), @@external_user;"
+---------------------+-------------------+
| CURRENT_USER()      | @@external_user   |
+---------------------+-------------------+
| dana@myco.example@% | dana@myco.example |
+---------------------+-------------------+

The credential is a short-lived token from your identity provider, so there is no password to store or rotate. Where the token comes from depends on who is connecting, e.g., a person at a shell, a CI job, or an application. The extension takes the same token in all cases. Obtaining it is ordinary OAuth for your provider.

The vsql-oauth2 README has the commands for all three. It also covers the vsql_oauth_client plugin, which fetches the token itself so it never touches the command line. However you send it, the token travels in cleartext at the protocol level, so the connection has to run over TLS.

You connect as oauth_user, which is bound to the extension and holds no privileges of its own. The username_claim setting says which claim names the account to run as, and GRANT PROXY is what allows the switch. Entra's sub is an opaque identifier, so this example reads preferred_username and gets Dana's sign-in name. The session then has dana@myco.example's privileges. @@external_user reports the identity that arrived in the token, so the audit trail keeps it even though the session runs as another account.

The token has to be signed with one of your provider's published keys for any of this to happen, and the extension accepts RSA and ECDSA signatures only. An unsigned token is refused before any signature check runs, and so is one that switches to a symmetric algorithm hoping the server will reuse your public key as a shared secret.

Easy database access management

The basic case maps a token to an account you created ahead of time. The extension can go further and take its cues from the App Roles Entra assigned the person signing in. You tell it which claim to read and how to rewrite the names, then create the roles those App Roles map onto:

SET GLOBAL vsql_oauth2.roles_claim = 'roles';
SET GLOBAL vsql_oauth2.roles_filter = 'mysql-grp-.*';
SET GLOBAL vsql_oauth2.roles_transform_pattern = '-';
SET GLOBAL vsql_oauth2.roles_transform_replacement = '_';
SET GLOBAL vsql_oauth2.auto_create = ON;
SET GLOBAL vsql_oauth2.auto_grant = ON;

CREATE ROLE mysql_grp_dba;

The two transform settings rewrite each matched App Role before it becomes a role name, turning mysql-grp-dba into mysql_grp_dba. Now someone who has never touched this database logs in with a token that says preferred_username: alice@myco.example, roles: [mysql-grp-dba]:

$ MYSQL_PWD='<her JWT>' mysql --enable-cleartext-plugin --user='alice@myco.example' \
    -e "SELECT CURRENT_USER(); SELECT CURRENT_ROLE();"
+----------------------+
| CURRENT_USER()       |
+----------------------+
| alice@myco.example@% |
+----------------------+
+---------------------+
| CURRENT_ROLE()      |
+---------------------+
| `mysql_grp_dba`@`%` |
+---------------------+

One login created the account, granted the role her App Role entitles her to, and activated it for the session. An auto-created account runs as itself, so this path needs no proxy grant. The DBA never saw a ticket and never ran a CREATE USER. The DBA's job moves up a level: grant each role its privileges once, and let the identity provider say who holds the App Role. When an App Role later disappears from someone's token, that role stops activating at their logins, but the granted membership stays until a human revokes it.

Alice had no account, so auto_create did all of it. auto_grant covers the other case, someone who already has an account, and it grants the roles their token claims each time they log in. Both default to off and work independently, so you can turn on either one without the other. If you leave them off, tokens only ever activate roles you granted by hand, and unknown users stay unknown.

Try it out

Please try out vsql-oauth2 against your identity provider, tell us how it goes, especially which claim layouts your tokens carry. If you are not on Entra, the same settings apply — your provider's discovery document (/.well-known/openid-configuration) gives the issuer and jwks_url values. We would love to hear from you on Discord or leave an issue on vsql-oauth2.

To get started with VillageSQL Server, go to villagesql.com.