Use kinedb as the identity provider of your app

An app can let kinedb sign in its users. The app sends the browser to /authorize. The app then redeems the code at /token. It gets a bearer token of the user and an ID token. The app runs the queries of the user with QueryAs.

The app acts as the user, never as an all-rights account. The server runs each statement with the rights of the user only. It never uses the rights of the connection of the app.

Before you start, an admin must do these steps:

1. Set the issuer: SET CLUSTER cluster.auth.issuer = 'https://auth.example.com';. Until then, /authorize and /token answer 404.
2. Register the app and its return addresses with CREATE APP (Apps and return addresses).
3. For a confidential app, make a client secret with NEW CLIENT SECRET.
4. Give the connection of the app the right to run QueryAs: GRANT QUERY AS TO 'webapp' (GRANT QUERY AS).

The flow

1. The app makes a random PKCE verifier and keeps it. It sends the browser to /authorize with the code_challenge (the SHA-256 of the verifier) and code_challenge_method=S256. Authentication lists the parameters.
2. The user signs in on the login page of kinedb, with a password or a provider (Log in with an identity provider).
3. kinedb sends the browser to the return address of the app, with code, state and iss. The app must compare iss with its issuer.
4. The server of the app sends POST /token with the code and the verifier.
5. The app gets the access token and the ID token. It checks the ID token.
6. For each request of the user, the app sends a QueryAs frame with the access token.

The code is valid for 60 seconds and works one time on one node.

The request to /token

Send POST /token with the content type application/x-www-form-urlencoded. The body has at most 8 KiB. The request has no query string. Send each field one time. A field with an empty value counts as absent.

Field	Rule
grant_type	Required. Must be authorization_code. At most 64 bytes.
code	Required. The code from the return address.
redirect_uri	Required. The same address that the app sent to /authorize. At most 512 bytes.
code_verifier	Required. The PKCE verifier. At most 128 bytes.
client_id	The name of the app. At most 63 bytes. Required, unless the Authorization header holds it.
client_secret	The secret of a confidential app, with the method client_secret_post. At most 48 bytes.

The client authentication

• A public app has no client secret. It sends the client_id and the verifier. PKCE is the only proof. A public app that sends a secret gets invalid_client.
• A confidential app has a client secret (kdcs_ and 43 characters). It sends the secret in the body (client_secret_post) or in the header Authorization: Basic base64(client_id:client_secret) (client_secret_basic). Encode the two parts as a form first (RFC 6749 section 2.3.1). An app with a secret that sends none gets invalid_client.
• Use one method only. A request with a secret in the body and a Basic header gets invalid_request. A Basic client_id that differs from the body client_id gets invalid_request.
• A Basic header with an empty secret counts as no secret.
• The built-in app kinedb-admin, an app that is not known and an app that is disabled get invalid_client.
• A header over 512 bytes gets invalid_request. A Basic value that does not decode gets invalid_client.

Example of a confidential app:

curl -s https://auth.example.com/token \
  -u "shop:kdcs_…" \
  -d grant_type=authorization_code \
  -d code="$CODE" \
  -d redirect_uri="https://shop.example.com/callback" \
  -d code_verifier="$VERIFIER"

The answer

A good request gets 200 with this JSON body. The answer has Cache-Control: no-store.

{
  "access_token": "kdt1_…",
  "token_type": "Bearer",
  "expires_in": 3600,
  "id_token": "eyJ…"
}

• access_token is a normal bearer token of the user. It works for QueryAs, POST /sql and a WebSocket login. It lives at most 1 hour. It ends earlier at the EXPIRE AT of the credential, or 30 days after the login of the user.
• The answer has no refresh_token in this version. The grant refresh_token gets unsupported_grant_type.
• The app may rotate the access token with POST /token/refresh, as with any bearer token (Authentication). A refresh never makes the session longer than 30 days after the login. To start a new sign-in without a user action, the app sends the browser to /authorize with prompt=none.
• id_token is a signed JWT for the app. It lives at most 1 hour. The app uses it to know who the user is. The app must not send it to kinedb as a token.

The claims of the ID token

Claim	Value
iss	The issuer of the cluster.
sub	A stable hash of the user id and the incarnation of the user. It is not the name. A user that is dropped and made again with the same name gets another sub.
aud	The name of the app.
exp, iat	The times, in seconds. The life is at most 1 hour.
nonce	The nonce of the /authorize request, when the app sent one.
preferred_username	The name of the user in kinedb.
email, email_verified	Present only under the rule below.
roles	The names of the roles of the user. At most 64, sorted. Absent when the user has no role.

The email rule. The claims email and email_verified come only from a login through a provider that verified the email of the user, and only when the app asked for the scope email. kinedb keeps no email: the claims come from the statement of the provider at that login. A login with a password never gives them. The claim email_verified is always true when present.

The roles are a hint only. The app may show them. The app must not use them to decide an access. The server never reads the roles from a token. It reads the rights of the user from its own stored data at each QueryAs.

Verify the ID token

1. Read the discovery document /.well-known/openid-configuration. It names the issuer, the jwks_uri, the userinfo_endpoint and the end_session_endpoint.
2. Get the key document /.well-known/jwks.json. Cache it for up to 5 minutes.
3. Find the key with the kid of the JWT header. The algorithm is ES256. Refuse any other algorithm.
4. Verify the signature. Check that iss is the issuer that the app knows, that aud is the name of the app, that exp is in the future, and that nonce is the value that the app sent.

A retired key stays in the key document until the tokens that it signed have expired. See Rotate the key and the overlap.

Run a query as the user

Send the access token in a QueryAs frame on the WebSocket connection of the app. The statement runs with the rights of the user (QueryAs).

// `client` is a WsClient, logged in as 'webapp', which holds GRANT QUERY AS.
// `access_token` is the `access_token` from POST /token.
let rows = client.query_as(&access_token, "SELECT id, total FROM orders")?;

The connection of the app needs GRANT QUERY AS. Without it, each QueryAs fails.

If every user gets "log in again": check GRANT QUERY AS on the user of the connection. Then run SHOW AUDIT WHERE action = 'query_as' to see the refused frames. The error text is the same for each cause, so the audit rows are the way to tell them apart.

CORS

A browser app can call /token from its own origin. kinedb sets the CORS headers when the client check finds an enabled app that allows the origin of the request. The origin must equal the scheme, the host and the port of an absolute return address of the app. The headers are Access-Control-Allow-Origin (the origin) and Vary: Origin.

• A wrong secret for an app that allows the origin gets the 401 with the CORS origin. The browser app can then read the error.
• OPTIONS /token is the preflight. It needs Access-Control-Request-Method: POST and an Origin of at most 512 bytes. An origin that an enabled app allows gets 204 with the method POST, the headers Authorization, Content-Type and Access-Control-Max-Age: 600. Any other preflight gets 403 with no CORS header. With no issuer, the preflight gets 404.
• A public app in a browser has no secret. Never put a client secret in a page of a browser.

Read the user information

The app can read the claims of the user at <issuer>/userinfo. Use the access token from /token. The route answers to GET and to POST.

• Send the token only in the header Authorization: Bearer <token>. A token in the query string or in the body gets 400. A POST must have an empty body.
• Only an access token from /token works. A token from a console or a CLI login gets 401.
• A token that ended gets 401. The token ends at its expiry, at a revoke, and at a logout of its session (Sign out).

curl -s https://auth.example.com/userinfo \
  -H "Authorization: Bearer $ACCESS_TOKEN"

A good request gets 200 with a JSON body. The answer has Cache-Control: no-store.

{
  "sub": "…",
  "preferred_username": "alice",
  "email": "alice@example.com",
  "email_verified": true
}

Claim	Value
sub	The same value as the sub of the ID token.
preferred_username	The name of the user in kinedb.
email, email_verified	Present only under the email rule below.

The email rule. It is the rule of the ID token. The claims email and email_verified are present only when the app asked for the scope email, and only when the provider verified the email at that login. A login with a password never gives them. kinedb keeps no email. The access token carries the two facts of that login, so /userinfo needs no stored fact. The answer has no roles, no nonce and no aud.

The error answers have a JSON body with one field, {"error":"<code>"}, and a header WWW-Authenticate:

Status	Cause	WWW-Authenticate
401	The token is not valid, has ended, or is not from /token. Each cause gives the same answer.	Bearer realm="kinedb", error="invalid_token"
401	The request has no Authorization header with the scheme Bearer.	Bearer realm="kinedb"
400	A query string, a body, or a token in two places. The error is invalid_request.	Bearer realm="kinedb", error="invalid_request"
429	A rate limit. The answer has Retry-After.	none
503	A fault on the node. The answer has Retry-After: 1.	none
404	The issuer is not set.	none

A browser app can call /userinfo from its own origin. kinedb uses the CORS rule of /token. kinedb sets Access-Control-Allow-Origin and Vary: Origin when an enabled app allows the origin. The preflight OPTIONS /userinfo needs Access-Control-Request-Method: GET or POST. It gets 204 with the headers Access-Control-Allow-Methods: GET, POST, Access-Control-Allow-Headers: Authorization and Access-Control-Max-Age: 600. Any other preflight gets 403 with no CORS header. kinedb never sets Access-Control-Allow-Credentials.

Sign out

The app ends the session of the user at <issuer>/end_session. This is the logout of OpenID Connect RP-Initiated Logout 1.0. The app sends the browser to the address:

https://auth.example.com/end_session?id_token_hint=<ID token>&post_logout_redirect_uri=https://shop.example.com/signed-out&state=abc

Parameter	Rule
id_token_hint	Optional. The ID token of the user. At most 8 KiB. An expired token still works, up to 30 days after its iat.
client_id	Optional. The name of the app. With a hint, it must equal the aud of the hint.
post_logout_redirect_uri	Optional. At most 512 bytes. It must be in the list of the return addresses of the app.
state	Optional. At most 512 printable bytes. kinedb sends it back with the redirect.

Send each parameter one time. kinedb ignores logout_hint and ui_locales. A POST to /end_session gets a redirect (303) to the GET with the same parameters. Use the GET.

What kinedb does:

• With a valid id_token_hint for the session of the browser, kinedb ends the session. It then sends the browser to post_logout_redirect_uri, with state.
• If the address is not in the list of the app, kinedb ends the session and shows the page "You are signed out". It does not redirect.
• With no hint, or a hint that is not valid or is for another user, kinedb asks the user first. It shows the confirm page and ends nothing until the user presses the button. A page of another site cannot end the session of the user. After the button, kinedb shows the page "You are signed out". It does not redirect.
• If the browser has no live session, nothing ends. A valid hint with a good address still gets the redirect.

The account chooser after a sign-out: an answer of /end_session that signs the user out also sets the cookie kinedb_signed_out (__Host-kinedb_signed_out behind https). The cookie lives for one hour. The next provider login of that browser sends prompt=select_account to the provider, and then clears the cookie. A provider that has no logout of its own, such as Google, keeps its own browser session. Without the prompt, the user would sign back in with no question, and the sign-out would look broken. With the prompt, the provider shows its account chooser. The confirm page sets no cookie, because it ends nothing. kinedb cannot end the session at such a provider. An app can also ask for the chooser itself: /authorize with prompt=select_account (or prompt=login) sends that prompt on to the provider. An explicit prompt of the app wins over the cookie.

What the logout ends:

• The SSO cookie of the browser. A stolen copy of the cookie ends too, on every node.
• Every access token that this session gave, on every node. This includes a token that the app refreshed. After the logout, QueryAs, POST /sql, a login by token and /userinfo refuse the token.
• A WebSocket connection that is already open stays open, as for POST /logout (Authentication).

What the logout does not end:

• The other sessions of the same user, and the CLI logins. Use ALTER USER … REVOKE SESSIONS to end all of them.
• The session at an external provider. kinedb sends no request to the provider. A user that signed in through a provider stays signed in there. The next sign-in at that provider can pass with no password prompt.

If the logout does not finish, kinedb shows an error page with 503 and no redirect. The cookie stays. Send the logout again. A second logout of an ended session is safe.

The pages. The two pages are static files in the folder of --auth-webroot (Run kinedb). Each page has a built-in default.

File	Page
logout.html	"You are signed out". It shows after a logout with no redirect.
logout-confirm.html	The confirm page. It asks the user to sign out.

The file logout-confirm.html must hold this form. Without it, the user cannot confirm.

<form method="post" action="/end_session">
  <input type="hidden" name="confirm" value="1">
  <button type="submit">Sign out</button>
</form>

The pages must not hold a token, a hint or a name. kinedb sends Referrer-Policy: same-origin with them. So the form POST carries the origin of the site, and a request to another site carries no referrer. The routes work with --no-web.

Use https. The hint is in the address, so a proxy or a log can keep it. The ID token of the hint is not a bearer token. It does not give access to kinedb.

The rate limits

• Each source address may send 60 requests to /token in a burst, and then 1 request each second. This holds also for the preflight. A request over the limit gets 429 with Retry-After. A preflight over the limit gets 429 with no CORS header. kinedb does not give a token back. The limit is on each node.
• /userinfo allows 120 requests in a burst for each source address, and then 1 request each 500 ms. /end_session allows 60 requests in a burst, and then 1 request each second. Both never give a request back. A request over the limit gets 429 with Retry-After.
• A logout also uses the limit of the user: 20 logouts in a burst, and then 1 each 6 seconds (Authentication). Past the limit, the session does not end and the page shows 429.
• The check of a client secret has its own limits, as the check of a password has: for the app and the source, for the source, and for the app across all sources. A wrong secret uses a token. A right secret gives it back. Past a limit, the answer is 429 with Retry-After.

The error answers

Each error answer is JSON with one field: {"error":"<code>"}. It has no text of the request. The answer has Cache-Control: no-store.

Status	error	Cause
400	invalid_request	The form is not valid: the content type, a query string, a body over 8 KiB, a field twice, a field over its size, a missing field, or two client methods.
400	unsupported_grant_type	The grant_type is not authorization_code.
400	invalid_grant	The code is not valid: bad, expired, used before, for another app or address, the verifier does not match, or the sign-in session of the code ended. All these causes give the same body.
401	invalid_client	The client check failed. A Basic request also gets WWW-Authenticate: Basic realm="kinedb".
429	temporarily_unavailable	A rate limit. The answer has Retry-After.
503	temporarily_unavailable	A fault on the node. The answer has Retry-After: 1.
404	none	The issuer is not set.

GET /token gets 405 with Allow: POST, OPTIONS.

After a 503, send the same request again. The server spends the code only after the steps that can fail before the token is made. A fault after the spend is rare. Then the app must start at /authorize again.

The audit

SHOW AUDIT records these actions. A row never holds a code, a verifier, a secret or a token.

Action	Code	When
TokenIssued	97	kinedb gave tokens. The actor is the user. The object is the app.
TokenRefused	98	kinedb refused a request. The actor is unknown. The row has a refusal code.
SessionEnded	99	A logout at /end_session ended a session. The actor is the user. The object is the app of a valid hint.
SessionEndRefused	100	/end_session refused or did not end a session. The actor is unknown. The row has a refusal code.
UserinfoRefused	101	/userinfo refused a request. The actor is unknown. The row has a refusal code. There is no row for a good request.

The refusal codes of the rows 98, 100 and 101:

Code	Name	Meaning
8	Throttled	A rate limit.
32	RequestInvalid	The form is not valid, the grant type is not known, or two client methods.
37	GrantInvalid	The code is not valid.
38	GrantReplayed	The node used the code before.
39	VerifierMismatch	The PKCE verifier does not match the challenge.
40	ClientInvalid	The app is not known, is disabled or is kinedb-admin, or the secret is missing or wrong.
41	AccessTokenInvalid	The token of /userinfo is not valid, has ended, or is not from /token.
42	LogoutHintInvalid	The id_token_hint is not valid, or it names another user than the session.

The row 100 also uses the codes 8 Throttled, 25 ReturnRefused (the address is not in the list of the app) and 32 RequestInvalid. The row 101 also uses the codes 8 and 32.

The audit rows of /token, /userinfo and /end_session are local to the node. A row names the app as its object when the request names a valid app name.

The limits of this version

• A replay on another node. Each node keeps its own list of used codes. A thief that has a code and its verifier can redeem it one time on each node, inside the 60 seconds of the code. PKCE and the short life of the code are the protection. Use https for the return address, and never log a code.
• A logout ends only one session. /end_session ends the SSO session of the browser and every access token that this session gave. It does not end another session of the user, a CLI login or the session at an external provider. An open WebSocket connection stays open. The user can end all sessions with ALTER USER … REVOKE SESSIONS.
• No refresh token. The app uses prompt=none at /authorize to renew the sign-in.