mirror of
https://github.com/coder/coder.git
synced 2026-06-03 13:08:25 +00:00
Yevhenii Shcherbina
c65996a041
Closes https://github.com/coder/internal/issues/780 ## Summary of changes: - added `user_secrets` table - `user_secrets` table contains `env_name` and `file_path` fields which are not used at the moment, but will be used in later PRs - `user_secrets` table doesn't contain `value_key_id`, I will add it in a separate migration in a dbcrypt PR - on one hand I don't want to add fields which are not used (because it's a risk smth may change in implementation later), on the other hand I don't want to add too many migrations for user secrets table - added unique sql indexes - added sql queries for CRUD operations on user-secrets - introduced new `ResourceUserSecret` resource - basic unit-tests for CRUD ops and authorization behavior - Role updates: - owner: - remove `ResourceUserSecret` from site-wide perms - add `ResourceUserSecret` to user-wide perms - orgAdmin - remove `ResourceUserSecret` from org-wide perms; seems it's not strictly required, because `ResourceUserSecret` is not tied to organization in dbauthz wrappers? - memberRole - no need to change memberRole because it implicitly has access to user-secrets thanks to the `allPermsExcept` - is it enough changes to roles? Main questions: - [ ] We will have 2 migrations for user-secrets: - initial migration (in current PR) - adding `value_key_id` in dbcrypt PR - is this approach reasonable? - [ ] Are changes to roles's permissions are correct? - [ ] Are changes in roles_test.go are correct? --------- Co-authored-by: Steven Masley <Emyrk@users.noreply.github.com>
35 lines
1.4 KiB
SQL
35 lines
1.4 KiB
SQL
-- Stores encrypted user secrets (global, available across all organizations)
|
|
CREATE TABLE user_secrets (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
description TEXT NOT NULL,
|
|
|
|
-- The encrypted secret value (base64-encoded encrypted data)
|
|
value TEXT NOT NULL,
|
|
|
|
-- Auto-injection settings
|
|
-- Environment variable name (e.g., "DATABASE_PASSWORD", "API_KEY")
|
|
-- Empty string means don't inject as env var
|
|
env_name TEXT NOT NULL DEFAULT '',
|
|
|
|
-- File path where secret should be written (e.g., "/home/coder/.ssh/id_rsa")
|
|
-- Empty string means don't inject as file
|
|
file_path TEXT NOT NULL DEFAULT '',
|
|
|
|
-- Timestamps
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
|
|
);
|
|
|
|
-- Unique constraint: user can't have duplicate secret names
|
|
CREATE UNIQUE INDEX user_secrets_user_name_idx ON user_secrets(user_id, name);
|
|
|
|
-- Unique constraint: user can't have duplicate env names
|
|
CREATE UNIQUE INDEX user_secrets_user_env_name_idx ON user_secrets(user_id, env_name)
|
|
WHERE env_name != '';
|
|
|
|
-- Unique constraint: user can't have duplicate file paths
|
|
CREATE UNIQUE INDEX user_secrets_user_file_path_idx ON user_secrets(user_id, file_path)
|
|
WHERE file_path != '';
|