vsql_oauth2 extension for MySQL

vsql_oauth2 lets an account sign in with a JWT instead of a password. The extension verifies the token’s signature, against keys fetched from a JWKS endpoint or one static public key, checks that the token has not expired, and maps a claim inside it to a VillageSQL account. Your identity provider stays the only place that issues credentials.


Maintainer	VillageSQL
Source and full documentation	villagesql/vsql-oauth2
License	GPL-2.0
Capability	Preview

This extension uses a preview capability, so vsql_allow_preview_extensions must be ON. Start the server with --vsql_allow_preview_extensions=ON, or run SET PERSIST vsql_allow_preview_extensions = ON. INSTALL EXTENSION is refused otherwise.

Install

vsql_oauth2.veb is already in the server’s lib/veb/ directory if you installed VillageSQL with the install script, the Docker image, or a release tarball. Install it into the server with one statement:

INSTALL EXTENSION vsql_oauth2;

Confirm it is there:

SELECT EXTENSION_NAME
FROM INFORMATION_SCHEMA.EXTENSIONS
WHERE EXTENSION_NAME = 'vsql_oauth2';

+----------------+
| EXTENSION_NAME |
+----------------+
| vsql_oauth2    |
+----------------+

To build it yourself, follow the build instructions in the repository.

What it adds

It adds an authentication plugin rather than SQL functions. Name the plugin when you create the account, and that account then authenticates by presenting a token:

CREATE USER 'analyst'@'%' IDENTIFIED WITH vsql_oauth2;

SELECT user, plugin FROM mysql.user WHERE user = 'analyst';

+---------+-------------+
| user    | plugin      |
+---------+-------------+
| analyst | vsql_oauth2 |
+---------+-------------+

The session runs as the account the token names, not as the account that connected. The claim in username_claim, sub by default, supplies that name. When its value is analyst, the statement above is all you need. When it names something else, such as an email address, create that account too and let the connecting account proxy onto it:

CREATE USER 'oidc_user'@'%' IDENTIFIED WITH vsql_oauth2;
CREATE USER 'alice@example.com' ACCOUNT LOCK;
GRANT SELECT ON app.* TO 'alice@example.com';
GRANT PROXY ON 'alice@example.com' TO 'oidc_user'@'%';

ACCOUNT LOCK stops anyone signing in as alice@example.com directly, and the proxy grant still works for a locked account. A server running validate_password refuses to create an account without a password, locked or not, answering ERROR 1819 (HY000): Your password does not satisfy the current policy requirements. Where that applies, give the account a password nobody holds. Without the proxy grant the login is refused with ERROR 6126 (HY000): Access denied for user 'oidc_user'@'localhost', missing proxy privilege. Everything else is configuration.

Setting	Default	What it controls
`vsql_oauth2.jwks_url`	empty	Where signing keys are fetched from. Takes precedence over a static key
`vsql_oauth2.public_key`	empty	A static public key, for a provider with no JWKS endpoint
`vsql_oauth2.jwks_refresh_interval`	`3600`	How often those keys are refetched, in seconds
`vsql_oauth2.jwks_http_timeout`	`5`	How long a fetch may take, in seconds
`vsql_oauth2.issuer`	empty	The `iss` claim a token must carry, when set
`vsql_oauth2.audience`	empty	The `aud` claim a token must carry, when set
`vsql_oauth2.username_claim`	`sub`	Which claim names the account
`vsql_oauth2.roles_claim`	empty	Which claim carries roles
`vsql_oauth2.roles_filter`	empty	Which of those roles are considered
`vsql_oauth2.roles_transform_pattern`, `vsql_oauth2.roles_transform_replacement`	empty	How a provider’s role name becomes a database role name
`vsql_oauth2.auto_create`	`OFF`	Whether an unknown subject gets an account at first login
`vsql_oauth2.auto_grant`	`OFF`	`OFF`/`ON`/`SYNC` — `OFF` activates only already-granted roles; `ON` also grants the claimed roles that exist as DB roles; `SYNC` does that and revokes granted roles the token no longer claims, except default roles

RSA signatures are accepted as RS256, RS384, and RS512, and ECDSA as ES256, ES384, and ES512.

auto_create and auto_grant are off by default, and that default is the safe one. Turning them on means your identity provider decides who has an account here and what it can reach, so scope roles_filter before you do. auto_grant = SYNC goes further: it also revokes any role the account holds that the token does not claim, except roles set as the account’s DEFAULT ROLE by an operator.

A setting written with SET PERSIST survives a restart, but UNINSTALL EXTENSION deletes it. After an uninstall and reinstall every setting is back at its default, and no restart brings the old value back.

Getting Started

Server Development

Using Extensions

Creating Extensions

Install

What it adds

See also

​Install

​What it adds

​See also

Install

What it adds

See also