| 1 | -- Single sign-on: a workspace's own identity provider, over OpenID |
| 2 | -- Connect, OAuth 2.0 or SAML 2.0, on any paid plan. People sign in to g1t |
| 3 | -- as themselves, then prove themselves to the workspace's provider to use |
| 4 | -- the workspace; it never signs anyone in to g1t. Every timestamp is RFC |
| 5 | -- 3339 UTC. See src/sso.rs. |
| 6 | |
| 7 | -- A workspace's connection to its identity provider: one each. |
| 8 | CREATE TABLE sso_connections ( |
| 9 | id TEXT PRIMARY KEY, |
| 10 | workspace_id TEXT NOT NULL UNIQUE REFERENCES workspaces (id) ON DELETE CASCADE, |
| 11 | -- oidc, oauth2 or saml. |
| 12 | protocol TEXT NOT NULL, |
| 13 | -- What the protocol needs, as JSON: issuer and client id (oidc); the |
| 14 | -- authorize, token and userinfo endpoints and claim names (oauth2); the |
| 15 | -- provider's entity id, sign-in URL and signing certificates (saml). |
| 16 | config TEXT NOT NULL, |
| 17 | -- The client secret (oidc, oauth2), sealed with AES-256-GCM under |
| 18 | -- IDENTITY_KEY and bound to the connection's id. Never read back out. |
| 19 | client_secret TEXT, |
| 20 | -- Whether anyone the provider signs in may join as a member. |
| 21 | allow_join INTEGER NOT NULL DEFAULT 1, |
| 22 | -- When someone first signed in through it, and who: required before it |
| 23 | -- can be required of members. |
| 24 | tested_at TEXT, |
| 25 | tested_by TEXT, |
| 26 | created_at TEXT NOT NULL, |
| 27 | updated_at TEXT NOT NULL |
| 28 | ); |
| 29 | |
| 30 | -- Members of a workspace that requires single sign-on use it only from a |
| 31 | -- session that signed in through its provider (owners always can, so a |
| 32 | -- broken provider never locks a workspace out). Read with the memberships. |
| 33 | ALTER TABLE workspaces ADD COLUMN require_sso INTEGER NOT NULL DEFAULT 0; |
| 34 | |
| 35 | -- Who a person is at a workspace's provider: its subject, linked to their |
| 36 | -- g1t account the first time they sign in through it. One each way. |
| 37 | CREATE TABLE sso_identities ( |
| 38 | connection_id TEXT NOT NULL REFERENCES sso_connections (id) ON DELETE CASCADE, |
| 39 | user_id TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE, |
| 40 | -- The provider's stable id for the person (`sub`, or SAML's NameID). |
| 41 | subject TEXT NOT NULL, |
| 42 | -- The address the provider gave, for showing; never used to sign in. |
| 43 | email TEXT, |
| 44 | created_at TEXT NOT NULL, |
| 45 | last_used_at TEXT NOT NULL, |
| 46 | PRIMARY KEY (connection_id, user_id), |
| 47 | UNIQUE (connection_id, subject) |
| 48 | ); |
| 49 | |
| 50 | -- A g1t session that signed in through a workspace's provider, until it |
| 51 | -- has to again. |
| 52 | CREATE TABLE sso_sessions ( |
| 53 | -- SHA-256 of the session token, as sessions.id. |
| 54 | session_id TEXT NOT NULL, |
| 55 | workspace_id TEXT NOT NULL REFERENCES workspaces (id) ON DELETE CASCADE, |
| 56 | expires_at TEXT NOT NULL, |
| 57 | PRIMARY KEY (session_id, workspace_id) |
| 58 | ); |
| 59 | |
| 60 | -- A trip to the provider under way: usable once, for ten minutes. Bound to |
| 61 | -- the session that started it, so what comes back counts for it alone. |
| 62 | CREATE TABLE sso_states ( |
| 63 | -- SHA-256 of the OAuth state, or SAML's request id. |
| 64 | id TEXT PRIMARY KEY, |
| 65 | connection_id TEXT NOT NULL REFERENCES sso_connections (id) ON DELETE CASCADE, |
| 66 | session_id TEXT NOT NULL, |
| 67 | user_id TEXT NOT NULL, |
| 68 | -- The PKCE verifier and OpenID nonce (oidc, oauth2). |
| 69 | verifier TEXT, |
| 70 | nonce TEXT, |
| 71 | -- Exactly the callback (or SAML's assertion consumer service) sent. |
| 72 | redirect_uri TEXT NOT NULL, |
| 73 | -- SAML: the entity id g1t named itself, which the assertion's audience |
| 74 | -- must be. |
| 75 | audience TEXT, |
| 76 | next TEXT NOT NULL, |
| 77 | expires_at TEXT NOT NULL |
| 78 | ); |