Ryan Malloy
32ea1c1c84
Bugs found by running the real test suite after merging upstream: - types/types_clone.go, types/types_view.go: extend the regeneration guard struct literals to include the new Groups field, and add a UserView.Groups() accessor. Generated files normally rebuilt via cloner / viewer; touched by hand here pending make generate. - db/db.go: the migration adding the groups column ran after 202505141324, which calls ListUsers() through the User struct that now includes Groups. Move the column-add to 202505141323 so the schema is in place before any migration loads users. Register the new ID in the FK-disabled migration list. - db/db.go: 202507021200 recreates all tables from inline SQL during the SQLite schema migration; add groups to both the CREATE TABLE users statement and the INSERT INTO users ... SELECT FROM users_old so the column survives the recreation. Also fix a copy-paste bug in the Rollback closure that referenced tx instead of db. - db/schema.sql: add the groups column to the canonical schema so squibble.Validate accepts databases produced by the new migration chain. Verified against all 7 historical sqlite dumps in hscontrol/db/testdata/sqlite. - types/users_test.go: the casby-oidc-claim case now exercises group storage; update the want to include the JSON-encoded groups column. - integration/oidc_groups_test.go: replace the aspirational draft (which referenced assertNoErr, scenario.usernames, hsic.WithTLS and other symbols that do not exist) with a focused test that follows the auth_oidc_test.go pattern. Verifies the groups column directly via sqlite3 inside the headscale container since the gRPC User message does not expose Groups.
116 lines
3.6 KiB
SQL
116 lines
3.6 KiB
SQL
-- This file is the representation of the SQLite schema of Headscale.
|
|
-- It is the "source of truth" and is used to validate any migrations
|
|
-- that are run against the database to ensure it ends in the expected state.
|
|
|
|
CREATE TABLE migrations(id text,PRIMARY KEY(id));
|
|
|
|
CREATE TABLE users(
|
|
id integer PRIMARY KEY AUTOINCREMENT,
|
|
name text,
|
|
display_name text,
|
|
email text,
|
|
provider_identifier text,
|
|
provider text,
|
|
profile_pic_url text,
|
|
groups text,
|
|
|
|
created_at datetime,
|
|
updated_at datetime,
|
|
deleted_at datetime
|
|
);
|
|
CREATE INDEX idx_users_deleted_at ON users(deleted_at);
|
|
|
|
|
|
-- The following three UNIQUE indexes work together to enforce the user identity model:
|
|
--
|
|
-- 1. Users can be either local (provider_identifier is NULL) or from external providers (provider_identifier set)
|
|
-- 2. Each external provider identifier must be unique across the system
|
|
-- 3. Local usernames must be unique among local users
|
|
-- 4. The same username can exist across different providers with different identifiers
|
|
--
|
|
-- Examples:
|
|
-- - Can create local user "alice" (provider_identifier=NULL)
|
|
-- - Can create external user "alice" with GitHub (name="alice", provider_identifier="alice_github")
|
|
-- - Can create external user "alice" with Google (name="alice", provider_identifier="alice_google")
|
|
-- - Cannot create another local user "alice" (blocked by idx_name_no_provider_identifier)
|
|
-- - Cannot create another user with provider_identifier="alice_github" (blocked by idx_provider_identifier)
|
|
-- - Cannot create user "bob" with provider_identifier="alice_github" (blocked by idx_name_provider_identifier)
|
|
CREATE UNIQUE INDEX idx_provider_identifier ON users(provider_identifier) WHERE provider_identifier IS NOT NULL;
|
|
CREATE UNIQUE INDEX idx_name_provider_identifier ON users(name, provider_identifier);
|
|
CREATE UNIQUE INDEX idx_name_no_provider_identifier ON users(name) WHERE provider_identifier IS NULL;
|
|
|
|
CREATE TABLE pre_auth_keys(
|
|
id integer PRIMARY KEY AUTOINCREMENT,
|
|
key text,
|
|
prefix text,
|
|
hash blob,
|
|
user_id integer,
|
|
reusable numeric,
|
|
ephemeral numeric DEFAULT false,
|
|
used numeric DEFAULT false,
|
|
tags text,
|
|
expiration datetime,
|
|
|
|
created_at datetime,
|
|
|
|
CONSTRAINT fk_pre_auth_keys_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
|
|
);
|
|
CREATE UNIQUE INDEX idx_pre_auth_keys_prefix ON pre_auth_keys(prefix) WHERE prefix IS NOT NULL AND prefix != '';
|
|
|
|
CREATE TABLE api_keys(
|
|
id integer PRIMARY KEY AUTOINCREMENT,
|
|
prefix text,
|
|
hash blob,
|
|
expiration datetime,
|
|
last_seen datetime,
|
|
|
|
created_at datetime
|
|
);
|
|
CREATE UNIQUE INDEX idx_api_keys_prefix ON api_keys(prefix);
|
|
|
|
CREATE TABLE nodes(
|
|
id integer PRIMARY KEY AUTOINCREMENT,
|
|
machine_key text,
|
|
node_key text,
|
|
disco_key text,
|
|
|
|
endpoints text,
|
|
host_info text,
|
|
ipv4 text,
|
|
ipv6 text,
|
|
hostname text,
|
|
given_name varchar(63),
|
|
-- user_id is NULL for tagged nodes (owned by tags, not a user).
|
|
-- Only set for user-owned nodes (no tags).
|
|
user_id integer,
|
|
register_method text,
|
|
tags text,
|
|
auth_key_id integer,
|
|
last_seen datetime,
|
|
expiry datetime,
|
|
approved_routes text,
|
|
|
|
created_at datetime,
|
|
updated_at datetime,
|
|
deleted_at datetime,
|
|
|
|
CONSTRAINT fk_nodes_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_nodes_auth_key FOREIGN KEY(auth_key_id) REFERENCES pre_auth_keys(id)
|
|
);
|
|
|
|
CREATE TABLE policies(
|
|
id integer PRIMARY KEY AUTOINCREMENT,
|
|
data text,
|
|
|
|
created_at datetime,
|
|
updated_at datetime,
|
|
deleted_at datetime
|
|
);
|
|
CREATE INDEX idx_policies_deleted_at ON policies(deleted_at);
|
|
|
|
CREATE TABLE database_versions(
|
|
id integer PRIMARY KEY,
|
|
version text NOT NULL,
|
|
updated_at datetime
|
|
);
|