db-auth

Run the tests · the user admin page, the server's account page, is an app of its own: db-auth-admin

Auth for triad.js apps, or any web page, with their own MySQL or PostgreSQL database, reached from the browser through db-connector. Every end user is a user of that server, and logging in is connecting as that user: the server checks the credentials and keeps users apart. Users may only EXECUTE the procedures meant for them, never touch a table. The user's text is encrypted in the browser with a key the server never sees.

The login screens are db-auth's user admin page, not the apps': it registers users, logs them in and keeps the login in the browser, for every page on its origin, and apps resume it with dbAuth.resume, drawing no login forms of their own. Its users are MySQL's, so there is one db-auth per MySQL server: its tables and procedures are in the database db_auth_b39cca5fbda0df05, migrated by webway, and every app on the server uses it. A user registered in one app works in all of them, while each app keeps its data in its own database, where the account name is the only link. MyDay is the first app on it.

Status: an earlier version of migrations V1 to V7 has run on MySQL; the current migrations and the calls have only run against the tests' fakes.

Running locally

1. MySQL 8.0.27 or later, for example with Homebrew. Its root is the admin who migrates; it needs SET_ANY_DEFINER, which root has.

   brew install mysql
   brew services start mysql
   mysql_secure_installation
   

2. The relay, on the same machine. It listens on ws://localhost:8080 and forwards to MySQL on 127.0.0.1:3306:

   git clone https://codeberg.org/joexo/db-ws-relay.git
   cd db-ws-relay
   npm install
   npm start
   

3. The user admin page: run db-auth-admin against ws://localhost:8080, and migrate as root when it asks; then register and log in there.

4. The tests: open test.html; they need neither MySQL nor the relay.

Getting started

1. Run MySQL and db-ws-relay on one host.

2. Pick a name for the app's database, readable and unique, such as yourapp_ and 16 random hex digits. db-auth's database, db_auth_b39cca5fbda0df05, is the server's, the same for every app on it, so a user registered once works in all of them.

3. Describe the app's database for webway, and write its migrations, such as migrations/V1__hello_world.js. It lets every user read the app's database's versions, through db-auth's database:

   var database = {
     database: 'yourapp_1a7d2145c1800e78',
     migrations: ['migrations/V1__hello_world.js']
   };
   

   webway.migrations.push(`
     SELECT 'Hello World!';
     CALL ::authDatabase.auth_grant_to_users(DATABASE(), 'schema_history_versions');
   `);
   

4. Drop in webway and db-auth, after triad.js. db-auth needs no set-up; the app hands its migrations db-auth's database as a param:

   withFiles(['https://spa-mvc-pages.duckdns.org/webway/webway.js', 'https://spa-mvc-pages.duckdns.org/db-auth/db-auth.js'], function () {
     database.params = { authDatabase: dbAuth.schema.database };
   });
   

5. When a page loads, read the browser's login with dbAuth.keptLogin(relay), for the relay or dbAuth.lastRelay(). Without one, link to the user admin page with #relay= and the relay, and connect nobody: dbAuth.onLoginChange(relay, …) tells the page when the user has logged in there. With one, connect as that user, with openAccessKey's password and no default database, check both databases with webway.check(connection, dbAuth.schema, …) and webway.check(connection, database, …), then resume the session with dbAuth.resume({ relay, database }). A login MySQL denies is forgotten; either database behind is how the app knows to ask for an admin.

6. Migrate the app's database as a database admin, with a connection that has multipleStatements: true. db-auth's database is the user admin page's to migrate, before any app's, as the apps' migrations use its role and its grant procedure; an app only checks it, and says so when it's behind:

   webway.migrate(connection, database, function (err, version) {});
   

7. Users register and log in on the user admin page, as below. An app's own procedures run as a definer of the app's own: a locked account its migrations create, with rights on its own database only, never db-auth's definer, which holds rights over the whole server. They reach users through CALL ::authDatabase.auth_grant_to_users(DATABASE(), 'procedure_name'), which grants them to db_auth_user_b39cca5fbda0df05, the role every user has:

   webway.migrations.push(`
     CREATE USER IF NOT EXISTS 'yourapp_definer_1a7d2145c1800e78'@'localhost' IDENTIFIED BY 'Aa1-kX7vQ2pLm9RtW4zH' ACCOUNT LOCK;
     GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON * TO 'yourapp_definer_1a7d2145c1800e78'@'localhost';
     CREATE DEFINER = 'yourapp_definer_1a7d2145c1800e78'@'localhost' PROCEDURE items_list()
       SQL SECURITY DEFINER
       SELECT id, title FROM items WHERE owner = SUBSTRING_INDEX(USER(), '@', 1);
     CALL ::authDatabase.auth_grant_to_users(DATABASE(), 'items_list');
   `);
   

Calls

Each calls back once, takes a db-connector connection where it needs one, and names db-auth's database, db_auth_b39cca5fbda0df05, in its calls. Each loads the part of db-auth it needs on its first call; a part that fails to load, and an error the call throws, go to its callback.

Call
schema, appUser, appPassword	Values, not calls: db-auth's database for webway, and the server's app user's login
register(connection, callback(err, { username, accessKey }))	As the app user: a new user. The username and the access key's login half come from the database, the encryption half from the browser
openAccessKey(accessKey, callback(err, { password, key }))	The MySQL password, and an AES-GCM key that can't be exported
startSession(connection, previousSessionId, callback(err, sessionId))	After logging in: replaces the previous session from this browser, if present
endSession(connection, sessionId, callback(err))	Ends this browser's session and its tracked connections, see Logging out
resumeSession(connection, sessionId, callback(err, isValid))	After a reconnect: whether this browser's session is still active
encrypt(key, { item, field, version }, text, callback(err, value)), decrypt(…, value, callback(err, text))	AES-GCM, bound to where the value belongs: a value changed, moved or served again from an older version fails to decrypt
passkeySupported, addPasskey, passkeyLogin, listPasskeys, removePasskey	Flow 2
pairing.createKeyPair, createSecret, open, offer, theirKey, keyCode, seal, deliver, collect, unseal, confirmationCode	Flow 3
deleteMe(connection, callback(err))	db-auth's rows for the user, then the user. The app deletes its own data first, with its own procedure
keptLogin(relay), keepLogin(relay, { username, accessKey, sessionId }), forgetLogin(relay), lastRelay(), keptRelays()	The kept login, in localStorage, read and written at once: the login for a relay, or null, the last relay one was kept for, and every relay with one
onLoginChange(relay, callback(login, relay))	Another page on the origin kept or forgot the login for relay, or, with relay null, for any relay; login is null once forgotten
resume({ relay, database }, callback(err, { username, password, key, id, connection }))	For apps: the kept login, connected as the user to database and its session resumed, never started. err.code is NOT_LOGGED_IN without a login, or one MySQL denies, which is forgotten, and the legacy LOGGED_IN_ELSEWHERE if this browser's session was replaced or ended

Registering

A user doesn't choose a username or password: both are generated when they register, and they log in with them, kept by their password manager.

• Username: an adjective and a noun, such as SleepyOtter, from 600 adjectives and 600 nouns. Each registration takes the next of the 360,000 pairs, in an order shuffled by a seed of each database's own. Only once all pairs are taken do numbers follow (SleepyOtter2), so names never run out. Usernames are case-sensitive, and needn't be secret.

• Access key, which serves as the password:

  Aa1-4c8e7f2a-9b1d-4e6f-a3c5-1d2e3f4a5b6c.0b7e5d2a-6c3f-4a1b-9d8e-2f4a6b8c0d1e
  

  Two random UUIDs, all hex and dashes, so nothing looks alike. The part before the dot is the login half, the user's MySQL password, made by the database from its cryptographic generator; Aa1- satisfies any validate_password policy. The part after it is the encryption half, made by the browser's crypto.randomUUID() and never sent anywhere; see End-to-end encryption. The database never stores the access key, and nobody can recover it; the browser keeps it once the user has logged in, for the user admin page's origin, until they log out.

Registering happens on the user admin page:

sequenceDiagram
  actor User
  participant Page as User admin page, in the browser
  participant Auth as db-auth
  participant DB as MySQL, through the relay
  Page->>DB: Connect as the app user
  Page->>Auth: register(connection)
  Auth->>DB: CALL register()
  DB-->>Auth: SleepyOtter and the login half Aa1-4c8e7f2a-…
  Auth->>Auth: Adds the encryption half, a UUID
  Auth-->>Page: SleepyOtter and the access key Aa1-4c8e7f2a-….0b7e5d2a-…
  Page->>User: Shows them once, in a form
  User->>Page: Saves them in the password manager, continues
  Page->>DB: Ends the app user's connection, logs in as flow 1

appConnection stands for a db-connector connection as the app user, and connection for one as the logged-in user. The form shows the access key in the clear, and its new-password field lets the password manager save it:

<form id="register">
  <input name="username" autocomplete="username" />
  <input name="accessKey" autocomplete="new-password" />
  <button>I've saved my access key</button>
</form>

dbAuth.register(appConnection, function (err, account) {
  form.username.value = account.username;
  form.accessKey.value = account.accessKey;
});

When the user continues, they log in as flow 1, with the account they just got.

register refuses beyond 5 registrations a second and 1,000 a day, for everyone together.

End-to-end encryption

The user's text is encrypted in the browser and only ever reaches the database as ciphertext, under a key the database never sees:

• The key comes from the access key's encryption half, a UUID the browser makes at registration: openAccessKey turns it into an AES-256-GCM key with HKDF. The key can't be exported, and lives in memory for the session only.
• Each value is encrypted on its own with encrypt, with a fresh random IV, and bound to where it belongs, { item, field, version }, as authenticated data. A value the server changes, moves to another item or field, or serves again from an older version fails to decrypt.
• What stays readable is the app's choice: whatever its procedures need to sort, filter or act on, such as a status or a due time, it stores unencrypted.
• The key reaches other devices only end to end: a passkey wraps the access key with a secret only the passkey can produce, and pairing seals it to the new device's key, under the QR code's secret.
• The database host is never trusted with a secret, only with keeping data. The bindings catch a value it changes or moves; it could still serve a whole row from before, with its older version, or leave a row out, so an app that must notice those keeps track of the latest versions itself. The browser code is trusted, and it's open source.
• A lost access key means the data is lost: nobody, the database's admin included, can decrypt it.

The kept login

Logging in happens on the user admin page, which keeps the login in localStorage, one per relay: { username, accessKey, sessionId }, under db-auth:login: and the relay. Tabs on the same origin share that session id. A different browser has a separate id and can stay logged in at the same time. Logging out ends only the current browser's session, and the browser tells each open page to close its connection.

• Only the user admin page starts a session. session_start(previous_session_id) replaces the supplied session for this MySQL user and returns a new id. It ends that browser's tracked connections; other browsers' sessions stay active. Apps only resume the session with dbAuth.resume. session_end(session_id) ends one browser's session on logout.
• Apps never connect as the app user. They check the databases' versions as the logged-in user, which V10 and each app's migrations grant to every user. The app user remains the user admin page's, for registering, the passkey login's lookup and the logging-in side of a QR code.
• The origin is the trust boundary. Every page on it can read every login, the access key included, as it could read the access key as it's typed anyway; a page on another origin can't. The login stays until the user logs out, including across restarts, so a stolen or physically compromised device is out of scope.

Login flows

Logging in, on the user admin page, always ends in a MySQL login with the access key's login half, and the login kept. The flows differ in how the access key reaches the browser:

1. Username and access key, filled by the password manager. The fallback for the others.
2. Passkey, which unwraps a stored copy of the access key.
3. Pairing by QR code, from a device that's logged in.

Flow 1: username and access key

sequenceDiagram
  participant PM as Password manager
  participant Page as User admin page, in the browser
  participant Auth as db-auth
  participant DB as MySQL, through the relay
  PM-->>Page: Fills username and access key
  Page->>Auth: openAccessKey(accessKey)
  Page->>DB: Connect as the user
  DB-->>Page: Logged in, or access denied
  Page->>Auth: startSession(connection, previousSessionId)
  Auth->>DB: CALL session_start(previousSessionId), replacing only that browser session
  Page->>Auth: keepLogin(relay, username, access key, session id)
  Note over Page,Auth: Every page on the origin is logged in

<form id="login">
  <input name="username" autocomplete="username" required />
  <input name="accessKey" autocomplete="current-password" required />
  <button>Log in</button>
</form>

dbAuth.openAccessKey(accessKey, function (err, opened) {
  dbConnector.connect({ relay: RELAY, user: username, password: opened.password, database: DATABASE }, function (err, connection) {
    var previous = dbAuth.keptLogin(RELAY);
    dbAuth.startSession(connection, previous && previous.sessionId, function (err, sessionId) {
      dbAuth.keepLogin(RELAY, { username: username, accessKey: accessKey, sessionId: sessionId });
    });
  });
});

Usernames are case-sensitive. The kept login, access key included, is what every page on the origin logs in with, until the user logs out on the user admin page. Apps resume it, which ends nothing; the legacy LOGGED_IN_ELSEWHERE code means this browser's session was replaced or ended:

dbAuth.resume({ relay: RELAY, database: DATABASE }, function (err, session) {});

Everything the user writes is encrypted before it leaves the browser, bound to where it belongs, with the session's key:

dbAuth.encrypt(opened.key, { item: 7, field: 'title', version: 1 }, 'Buy milk', function (err, value) {});
dbAuth.decrypt(opened.key, { item: 7, field: 'title', version: 1 }, value, function (err, text) {});

Flow 2: passkey

A passkey can't log in to MySQL, but with the WebAuthn PRF extension it gives a secret, after Face ID, Touch ID or a PIN, that unlocks the access key. While logged in, db-auth wraps the access key with a key made from that secret and stores it by the passkey's id; logging in fetches it as the app user and unwraps it. Without the passkey it's ciphertext under a 256-bit key. Passkeys that sync, as Apple's and Google's do, log the user in on their other devices without pairing.

PRF is in Chrome, Edge, Android and Safari's built-in passkeys, but newer in Firefox, so flow 1 stays the fallback. WebAuthn needs https or localhost. A passkey belongs to the user admin page's origin, so it logs in every app there.

sequenceDiagram
  participant Page as User admin page, in the browser
  participant PK as Passkey
  participant DB as MySQL, through the relay
  Note over Page,DB: Adding, while logged in
  Page->>PK: Create a passkey, with PRF
  PK-->>Page: Passkey id and secret, after Face ID, Touch ID or PIN
  Page->>DB: passkey_add(id, access key wrapped with the secret)
  Note over Page,DB: Logging in
  Page->>DB: Connect as the app user
  Page->>PK: Use a passkey, with PRF
  PK-->>Page: Passkey id and secret
  Page->>DB: passkey_wrapped_key(id)
  DB-->>Page: Username and wrapped access key
  Page->>Page: Unwraps it and logs in as flow 1

Offer passkeys unless passkeySupported answers false. Adding one takes the access key, which the user admin page has from the kept login:

dbAuth.passkeySupported(function (err, supported) {});
dbAuth.addPasskey(connection, { username: username, accessKey: accessKey }, 'db-auth', function (err, credentialId) {});
dbAuth.passkeyLogin(appConnection, function (err, account) {});

passkeyLogin gives back the username and access key, to log in with as flow 1. listPasskeys(connection, callback) and removePasskey(connection, id, callback) manage them; removing deletes the stored copy, and the passkey itself stays in the password manager.

Flow 3: pairing by QR code

The new device makes a key pair; the logged-in device seals the username and access key to its public key and leaves the envelope in the database for five minutes. Either device can show the QR code.

The QR code is the only channel the database can't touch, so besides the pairing id it carries a random secret. Everything that passes through the database is bound to it: the offered key carries an HMAC, and the envelope only opens with the secret. Someone who photographs the QR code knows the secret too, so the user also compares six-digit codes before trusting a key or an envelope that came through the database.

The new device shows the QR code:

sequenceDiagram
  participant New as New device
  participant DB as MySQL, through the relay
  participant Old as Logged-in device
  New->>DB: As the app user, pairing_open(public key)
  New->>New: Shows a QR code: pairing id, public key, secret
  Old->>New: Scans it
  Old->>DB: As the user, pairing_deliver(envelope sealed to the key, with the secret)
  Old->>Old: Shows the confirmation code
  New->>DB: pairing_collect, until the envelope is there
  New->>New: Opens it with the secret, shows the confirmation code
  Note over New,Old: The user checks the codes match
  New->>DB: Logs in as flow 1

The logged-in device shows the QR code:

sequenceDiagram
  participant Old as Logged-in device
  participant DB as MySQL, through the relay
  participant New as New device
  Old->>DB: As the user, pairing_open()
  Old->>Old: Shows a QR code: pairing id, secret
  New->>Old: Scans it
  New->>DB: As the app user, pairing_offer(public key and its HMAC)
  New->>New: Shows the key code
  Old->>DB: pairing_key, until the key is there
  Old->>Old: Checks the HMAC, shows the key code
  Note over New,Old: The user checks the key codes match
  Old->>DB: pairing_deliver(envelope sealed to the key, with the secret)
  Old->>Old: Shows the confirmation code
  New->>DB: pairing_collect, until the envelope is there
  New->>New: Opens it with the secret, shows the confirmation code
  Note over New,Old: The user checks the codes match
  New->>DB: Logs in as flow 1

showQr stands for the app's QR code, and qr for what the other device scanned from it. The new device polls collect, and in the second direction the logged-in device polls theirKey, until there's an answer.

The user admin page makes the QR code a link to itself, with the relay and the pairing in its fragment, which never reaches a server: a camera scanning it opens the user admin page on the other device, which takes the link off the address bar and connects by itself. So the user has one action, Log in with a QR code, which always shows one, and whether the showing device is logged in picks the direction. Logged in, it shows the second direction's QR code, without a key, sealing the kept access key, so nobody enters it; not logged in, the first direction's, and the device that opens it seals its kept login, or logs in first. Only a side without a kept login ever enters the access key.

The new device shows the QR code:

dbAuth.pairing.createKeyPair(function (err, pair) {
  dbAuth.pairing.createSecret(function (err, secret) {
    dbAuth.pairing.open(appConnection, pair.publicKey, function (err, pairingId) {
      showQr({ pairing: pairingId, key: pair.publicKey, secret: secret });
    });
  });
});

dbAuth.pairing.seal(qr.key, { username: username, accessKey: accessKey }, qr.secret, function (err, envelope) {
  dbAuth.pairing.deliver(connection, qr.pairing, envelope, function (err, delivered) {});
});

The logged-in device shows the QR code:

dbAuth.pairing.createSecret(function (err, secret) {
  dbAuth.pairing.open(connection, null, function (err, pairingId) {
    showQr({ pairing: pairingId, secret: secret });
  });
});

dbAuth.pairing.createKeyPair(function (err, pair) {
  dbAuth.pairing.offer(appConnection, qr.pairing, pair.publicKey, qr.secret, function (err, offered) {});
});

dbAuth.pairing.theirKey(connection, pairingId, secret, function (err, newDeviceKey) {
  dbAuth.pairing.keyCode(newDeviceKey, function (err, code) {});
});

Both devices show keyCode of the new device's key, and the logged-in device seals and delivers only once the user confirms they match.

Then, in both directions, the new device collects and opens the envelope, both devices show confirmationCode, and the new device logs in as flow 1 only once the user confirms they match:

dbAuth.pairing.collect(appConnection, pairingId, function (err, envelope) {
  dbAuth.pairing.unseal(pair.keyPair, envelope, secret, function (err, account) {});
  dbAuth.pairing.confirmationCode(pair.publicKey, envelope, function (err, code) {});
});

Logging out

Logging out happens on the user admin page, and ends this browser's session only: the user stays logged in in other browsers. It logs out every page on the origin, in every tab, as they all share the kept login.

sequenceDiagram
  participant Page as User admin page, in the browser
  participant Auth as db-auth
  participant DB as MySQL, through the relay
  participant App as App pages on the same origin
  Page->>Auth: endSession(connection, sessionId)
  Auth->>DB: CALL session_end(sessionId)
  DB-->>App: Kills the connections of this browser's session
  Page->>Auth: forgetLogin(relay)
  Auth-->>App: onLoginChange(relay), with login null
  App->>App: Ends its connection, and shows itself logged out
  Note over Page,App: Other browsers' sessions stay logged in

session_end kills the connections the session tracks, the ones startSession and resumeSession made it, in every tab and app, then deletes the session, so it can't be resumed. Forgetting the login removes it from localStorage, which tells every other page on the origin. The user admin page ends the session on its own connection, or, without one, on a connection it resumes from the kept login first:

var login = dbAuth.keptLogin(RELAY);
dbAuth.endSession(connection, login.sessionId, function (err) {
  connection.end(function () {});
});
dbAuth.forgetLogin(RELAY);

An app listens for the login to go, and ends its own connection then. A page that misses it finds its connection killed, and resuming answers NOT_LOGGED_IN, as there's no login kept:

dbAuth.onLoginChange(RELAY, function (login) {
  if (!login) {
    session.connection.end(function () {});
  }
});

Deleting the user with deleteMe logs out every browser: delete_me kills the connections of all the user's sessions before it drops the user.

Security

What a deployment needs, besides the flows above:

• wss:// to the relay, and the relay next to MySQL. mysql-ws speaks MySQL's protocol without MySQL's own TLS, and a full login asks MySQL for its RSA key, which mysql-ws can't check: only an encrypted path to a trusted relay keeps passwords from someone in between.
• No anonymous or proxy accounts on the server. Procedures know their caller by USER(), which is the account's name only then. mysql_secure_installation removes anonymous accounts.
• No NO_BACKSLASH_ESCAPES while migrating. The statements db-auth builds quote with QUOTE(), which escapes with backslashes.
• One origin per set of trusted pages. The kept login is readable by every page on the user admin page's origin, so put only pages you trust there; an app on another origin can't share it.
• The admin is the server's. db_auth_definer_b39cca5fbda0df05, shared by every app on the server, as db_auth_b39cca5fbda0df05 is, may create, alter and drop any account without SYSTEM_USER, and end any connection, so no procedure ever passes it anything a caller sends.

The app user's password is public, the same in every copy of db-auth, so everything the app user may do is limited, as are users, whom anyone can create:

	Limit
Connections	20 for the app user, for all the server's apps together, 3 per user
Registering	5 a second, 1,000 a day, for everyone together
Pairing	3 a second for everyone together, five minutes each, so at most 900 open at once
Passkeys	10 per user

Generated passwords and the app user's never expire.

Tests

test.html covers loading, registering, the access key, encryption, sessions, pairing, passkeys and leaving, with a fake connection, a fake authenticator and the browser's own crypto. Open it locally or online; locally, it loads triad.js and testrunner from folders beside this one, as online.

Stored procedures

Each scopes itself to its caller, from USER(), and runs with db_auth_definer_b39cca5fbda0df05's rights; none takes a user id. They are plain SQL: no variables, loops or conditions, with three exceptions MySQL leaves no way around. auth_exec passes its statement through a session variable, as PREPARE requires; auth_kill_session_connections walks one browser session's connections with a cursor, as KILL ends one at a time; and register keeps the new password in a variable, as the same secret goes into CREATE USER and back to the caller. Limits are a CHECK constraint or a condition in the statement, not checks that raise errors.

Procedure	Called by
register()	App user	A new user, with a generated username and login half. At most 5 a second and 1,000 a day, for everyone together
session_start(previous_session_id)	User	Replaces only the supplied session for this user, kills its tracked connections, and returns a new session id
session_resume(session_id)	User	Records this connection for an active browser session and reports whether it is valid
session_end(session_id)	User	Kills this browser session's tracked connections and ends the session
pairing_open(public_key)	App user or user	A pairing for five minutes, with the new device's key or none yet. At most 3 a second, for everyone together
pairing_offer(pairing_id, public_key)	App user	The new device's key and its HMAC, once
pairing_key(pairing_id)	User	The offered key
pairing_deliver(pairing_id, envelope)	User	The sealed username and access key, once
pairing_collect(pairing_id)	App user	The envelope; collecting it ends the pairing
passkey_add(credential_id, wrapped_key)	User	Stores the caller's wrapped access key, at most 10 per user
passkey_wrapped_key(credential_id)	App user	The username and wrapped access key for a passkey
schema_history_versions()	App user and users	webway's: the database's migrations, for webway.check when a page loads. Users since V10, as apps check as the logged-in user
passkey_list(), passkey_remove(credential_id)	User	The caller's passkeys
delete_me()	User	Ends all of the caller's sessions, deletes passkeys, then drops the caller's user

Nobody is granted db-auth's helpers: auth_exec runs a statement that had to be built, auth_limit counts calls against a limit, auth_grant_to_users(database, procedure) grants a procedure, db-auth's or an app's, to the users' role db_auth_user_b39cca5fbda0df05, the function auth_username(number) makes names such as SleepyOtter, and auth_kill_session_connections ends only the named session's other connections. V8 creates the app user with plain statements of its own.

Schema

db-auth's tables, in fifth normal form and without NULLs. owner is a MySQL account's name, which a foreign key can't point at.

Users: their browser sessions, and their passkeys' wrapped access keys.

erDiagram
  direction TB
  MYSQL_ACCOUNT ||--o{ sessions : "browser sessions"
  sessions ||--o{ session_connections : "open connections"
  MYSQL_ACCOUNT ||--o{ passkeys : "wrapped access keys"
  MYSQL_ACCOUNT {
    varchar user PK "an end user"
  }
  sessions {
    varchar owner PK "part of composite key"
    char session_id PK "part of composite key"
    datetime started_on
  }
  session_connections {
    varchar owner PK "part of composite key"
    char session_id PK "part of composite key"
    bigint connection_id PK "part of composite key"
    datetime server_started_on
  }
  passkeys {
    varchar credential_id PK
    varchar owner
    varchar wrapped_key
    datetime created_on
  }

Registering: the words usernames are made of, and the limits on registering and pairing. The users' role is the account db_auth_user_b39cca5fbda0df05.

erDiagram
  direction TB
  adjectives {
    varchar word PK
  }
  nouns {
    varchar word PK
  }
  username_sequence {
    tinyint id PK
    bigint last_number "the last name handed out"
    int seed "shuffles the order"
  }
  limits {
    varchar action PK
    datetime window_start PK
    int calls "at most max_calls"
    int max_calls
  }

Pairing: a pairing, then the new device's key, then the envelope sealed to it.

erDiagram
  direction TB
  pairings ||--o| pairing_keys : "offered key"
  pairing_keys ||--o| pairing_envelopes : "sealed to"
  pairings {
    char pairing_id PK
    bigint number UK "finds the row just added"
    datetime expires_on
  }
  pairing_keys {
    char pairing_id PK,FK
    varchar public_key "with its HMAC"
  }
  pairing_envelopes {
    char pairing_id PK,FK
    varchar envelope
  }