Relates to CODAGT-779 Currently we can't tell whether the OAuth2 client of an MCP server config was entered by an admin, or registered by Coder with Dynamic Client Registration (RFC 7591). The next PR in the stack needs to know this, so it can register a new client when a config is updated. This PR adds `mcp_server_configs.oauth2_client_registration` (`manual` or `dynamic`) and returns it to admins on the API. Create stores `dynamic` when it registers the client, and `manual` otherwise. An update resets it to `manual` when it switches away from the `oauth2` auth type, or when it changes the client ID, secret, auth URL or token URL of a `dynamic` config, since the admin has replaced Coder's client. Existing rows default to `manual`, as there's nothing in the data that tells us which clients came from Dynamic Client Registration.
331 lines
8.5 KiB
SQL
331 lines
8.5 KiB
SQL
-- name: GetMCPServerConfigByID :one
|
|
SELECT
|
|
*
|
|
FROM
|
|
mcp_server_configs
|
|
WHERE
|
|
id = @id::uuid;
|
|
|
|
-- name: GetMCPServerConfigByIDForUpdate :one
|
|
SELECT
|
|
*
|
|
FROM
|
|
mcp_server_configs
|
|
WHERE
|
|
id = @id::uuid
|
|
FOR UPDATE;
|
|
|
|
-- name: GetMCPServerConfigByOrganizationAndSlug :one
|
|
SELECT
|
|
*
|
|
FROM
|
|
mcp_server_configs
|
|
WHERE
|
|
organization_id = @organization_id::uuid
|
|
AND slug = @slug::text;
|
|
|
|
-- name: GetMCPServerConfigsByOrganization :many
|
|
SELECT
|
|
*
|
|
FROM
|
|
mcp_server_configs
|
|
WHERE
|
|
organization_id = @organization_id::uuid
|
|
-- Authorize Filter clause will be injected below in GetAuthorizedMCPServerConfigs
|
|
-- @authorize_filter
|
|
ORDER BY
|
|
display_name ASC;
|
|
|
|
-- name: GetEnabledMCPServerConfigsByOrganization :many
|
|
SELECT
|
|
*
|
|
FROM
|
|
mcp_server_configs
|
|
WHERE
|
|
organization_id = @organization_id::uuid
|
|
AND enabled = TRUE
|
|
ORDER BY
|
|
display_name ASC;
|
|
|
|
-- name: GetEnabledMCPServerConfigsByOrganizationAndIDs :many
|
|
SELECT
|
|
*
|
|
FROM
|
|
mcp_server_configs
|
|
WHERE
|
|
organization_id = @organization_id::uuid
|
|
AND id = ANY(@ids::uuid[])
|
|
AND enabled = TRUE
|
|
ORDER BY
|
|
display_name ASC;
|
|
|
|
-- name: GetForcedMCPServerConfigsByOrganization :many
|
|
SELECT
|
|
*
|
|
FROM
|
|
mcp_server_configs
|
|
WHERE
|
|
organization_id = @organization_id::uuid
|
|
AND enabled = TRUE
|
|
AND availability = 'force_on'
|
|
ORDER BY
|
|
display_name ASC;
|
|
|
|
-- name: InsertMCPServerConfig :one
|
|
INSERT INTO mcp_server_configs (
|
|
id,
|
|
organization_id,
|
|
display_name,
|
|
slug,
|
|
description,
|
|
icon_url,
|
|
transport,
|
|
url,
|
|
auth_type,
|
|
oauth2_client_id,
|
|
oauth2_client_secret,
|
|
oauth2_client_secret_key_id,
|
|
oauth2_auth_url,
|
|
oauth2_token_url,
|
|
oauth2_revocation_url,
|
|
oauth2_resource,
|
|
oauth2_scopes,
|
|
oauth2_client_registration,
|
|
api_key_header,
|
|
api_key_value,
|
|
api_key_value_key_id,
|
|
custom_headers,
|
|
custom_headers_key_id,
|
|
signing_secret,
|
|
signing_secret_key_id,
|
|
tool_allow_list,
|
|
tool_deny_list,
|
|
availability,
|
|
enabled,
|
|
model_intent,
|
|
allow_in_plan_mode,
|
|
forward_coder_headers,
|
|
group_acl,
|
|
user_acl,
|
|
created_by,
|
|
updated_by
|
|
) VALUES (
|
|
@id::uuid,
|
|
@organization_id::uuid,
|
|
@display_name::text,
|
|
@slug::text,
|
|
@description::text,
|
|
@icon_url::text,
|
|
@transport::text,
|
|
@url::text,
|
|
@auth_type::text,
|
|
@oauth2_client_id::text,
|
|
@oauth2_client_secret::text,
|
|
sqlc.narg('oauth2_client_secret_key_id')::text,
|
|
@oauth2_auth_url::text,
|
|
@oauth2_token_url::text,
|
|
@oauth2_revocation_url::text,
|
|
@oauth2_resource::text,
|
|
@oauth2_scopes::text,
|
|
@oauth2_client_registration::text,
|
|
@api_key_header::text,
|
|
@api_key_value::text,
|
|
sqlc.narg('api_key_value_key_id')::text,
|
|
@custom_headers::text,
|
|
sqlc.narg('custom_headers_key_id')::text,
|
|
@signing_secret::text,
|
|
sqlc.narg('signing_secret_key_id')::text,
|
|
@tool_allow_list::text[],
|
|
@tool_deny_list::text[],
|
|
@availability::text,
|
|
@enabled::boolean,
|
|
@model_intent::boolean,
|
|
@allow_in_plan_mode::boolean,
|
|
@forward_coder_headers::boolean,
|
|
@group_acl,
|
|
@user_acl,
|
|
@created_by::uuid,
|
|
@updated_by::uuid
|
|
)
|
|
RETURNING
|
|
*;
|
|
|
|
-- name: UpdateMCPServerConfig :one
|
|
UPDATE
|
|
mcp_server_configs
|
|
SET
|
|
display_name = @display_name::text,
|
|
slug = @slug::text,
|
|
description = @description::text,
|
|
icon_url = @icon_url::text,
|
|
transport = @transport::text,
|
|
url = @url::text,
|
|
auth_type = @auth_type::text,
|
|
oauth2_client_id = @oauth2_client_id::text,
|
|
oauth2_client_secret = @oauth2_client_secret::text,
|
|
oauth2_client_secret_key_id = sqlc.narg('oauth2_client_secret_key_id')::text,
|
|
oauth2_auth_url = @oauth2_auth_url::text,
|
|
oauth2_token_url = @oauth2_token_url::text,
|
|
oauth2_revocation_url = @oauth2_revocation_url::text,
|
|
oauth2_resource = @oauth2_resource::text,
|
|
oauth2_scopes = @oauth2_scopes::text,
|
|
oauth2_client_registration = @oauth2_client_registration::text,
|
|
api_key_header = @api_key_header::text,
|
|
api_key_value = @api_key_value::text,
|
|
api_key_value_key_id = sqlc.narg('api_key_value_key_id')::text,
|
|
custom_headers = @custom_headers::text,
|
|
custom_headers_key_id = sqlc.narg('custom_headers_key_id')::text,
|
|
signing_secret = @signing_secret::text,
|
|
signing_secret_key_id = sqlc.narg('signing_secret_key_id')::text,
|
|
tool_allow_list = @tool_allow_list::text[],
|
|
tool_deny_list = @tool_deny_list::text[],
|
|
availability = @availability::text,
|
|
enabled = @enabled::boolean,
|
|
model_intent = @model_intent::boolean,
|
|
allow_in_plan_mode = @allow_in_plan_mode::boolean,
|
|
forward_coder_headers = @forward_coder_headers::boolean,
|
|
updated_by = @updated_by::uuid,
|
|
updated_at = NOW()
|
|
WHERE
|
|
id = @id::uuid
|
|
RETURNING
|
|
*;
|
|
|
|
-- name: UpdateMCPServerConfigACLByID :exec
|
|
UPDATE mcp_server_configs
|
|
SET
|
|
group_acl = @group_acl,
|
|
user_acl = @user_acl,
|
|
updated_by = @updated_by::uuid,
|
|
updated_at = NOW()
|
|
WHERE
|
|
id = @id::uuid;
|
|
|
|
-- name: DeleteMCPServerConfigByID :exec
|
|
DELETE FROM
|
|
mcp_server_configs
|
|
WHERE
|
|
id = @id::uuid;
|
|
|
|
-- name: GetMCPServerUserToken :one
|
|
SELECT
|
|
*
|
|
FROM
|
|
mcp_server_user_tokens
|
|
WHERE
|
|
mcp_server_config_id = @mcp_server_config_id::uuid
|
|
AND user_id = @user_id::uuid;
|
|
|
|
-- name: GetMCPServerUserTokenByID :one
|
|
SELECT
|
|
*
|
|
FROM
|
|
mcp_server_user_tokens
|
|
WHERE
|
|
id = @id::uuid;
|
|
|
|
-- name: GetMCPServerUserTokensByUserID :many
|
|
SELECT
|
|
*
|
|
FROM
|
|
mcp_server_user_tokens
|
|
WHERE
|
|
user_id = @user_id::uuid;
|
|
|
|
-- name: UpsertMCPServerUserToken :one
|
|
INSERT INTO mcp_server_user_tokens (
|
|
mcp_server_config_id,
|
|
user_id,
|
|
access_token,
|
|
access_token_key_id,
|
|
refresh_token,
|
|
refresh_token_key_id,
|
|
token_type,
|
|
expiry
|
|
) VALUES (
|
|
@mcp_server_config_id::uuid,
|
|
@user_id::uuid,
|
|
@access_token::text,
|
|
sqlc.narg('access_token_key_id')::text,
|
|
@refresh_token::text,
|
|
sqlc.narg('refresh_token_key_id')::text,
|
|
@token_type::text,
|
|
sqlc.narg('expiry')::timestamptz
|
|
)
|
|
ON CONFLICT (mcp_server_config_id, user_id) DO UPDATE SET
|
|
access_token = @access_token::text,
|
|
access_token_key_id = sqlc.narg('access_token_key_id')::text,
|
|
refresh_token = @refresh_token::text,
|
|
refresh_token_key_id = sqlc.narg('refresh_token_key_id')::text,
|
|
token_type = @token_type::text,
|
|
expiry = sqlc.narg('expiry')::timestamptz,
|
|
-- New token material means the user re-authenticated, so any
|
|
-- cached permanent refresh failure no longer applies.
|
|
oauth_refresh_failure_reason = '',
|
|
updated_at = NOW()
|
|
RETURNING
|
|
*;
|
|
|
|
-- name: UpdateMCPServerUserTokenFromRefresh :one
|
|
-- Refresh persistence must not recreate a token deleted by disconnect.
|
|
-- The optimistic lock also prevents stale refreshes from replacing newer tokens.
|
|
UPDATE mcp_server_user_tokens
|
|
SET
|
|
access_token = @access_token::text,
|
|
access_token_key_id = sqlc.narg('access_token_key_id')::text,
|
|
refresh_token = @refresh_token::text,
|
|
refresh_token_key_id = sqlc.narg('refresh_token_key_id')::text,
|
|
token_type = @token_type::text,
|
|
expiry = sqlc.narg('expiry')::timestamptz,
|
|
oauth_refresh_failure_reason = '',
|
|
updated_at = NOW()
|
|
WHERE
|
|
id = @id::uuid
|
|
AND updated_at = @updated_at::timestamptz
|
|
RETURNING
|
|
*;
|
|
|
|
-- name: MarkMCPServerUserTokenRefreshFailure :one
|
|
-- Records a permanent refresh failure (e.g. revoked grant) and clears
|
|
-- the dead token material so it is never attached to a request again.
|
|
-- The updated_at predicate provides optimistic concurrency: if another
|
|
-- request refreshed or replaced the token since it was read, this
|
|
-- update matches zero rows and returns sql.ErrNoRows.
|
|
UPDATE mcp_server_user_tokens
|
|
SET
|
|
access_token = '',
|
|
access_token_key_id = NULL,
|
|
refresh_token = '',
|
|
refresh_token_key_id = NULL,
|
|
expiry = NULL,
|
|
oauth_refresh_failure_reason = @oauth_refresh_failure_reason::text,
|
|
updated_at = NOW()
|
|
WHERE
|
|
id = @id::uuid
|
|
AND updated_at = @updated_at::timestamptz
|
|
RETURNING
|
|
*;
|
|
|
|
-- name: DeleteMCPServerUserToken :exec
|
|
DELETE FROM
|
|
mcp_server_user_tokens
|
|
WHERE
|
|
mcp_server_config_id = @mcp_server_config_id::uuid
|
|
AND user_id = @user_id::uuid;
|
|
|
|
-- name: DeleteMCPServerUserTokensByConfigID :exec
|
|
DELETE FROM
|
|
mcp_server_user_tokens
|
|
WHERE
|
|
mcp_server_config_id = @mcp_server_config_id::uuid;
|
|
|
|
-- name: CleanupDeletedMCPServerIDsFromChats :exec
|
|
UPDATE chats
|
|
SET mcp_server_ids = (
|
|
SELECT COALESCE(array_agg(sid), '{}')
|
|
FROM unnest(chats.mcp_server_ids) AS sid
|
|
WHERE sid IN (SELECT id FROM mcp_server_configs)
|
|
)
|
|
WHERE mcp_server_ids != '{}'
|
|
AND NOT (mcp_server_ids <@ COALESCE((SELECT array_agg(id) FROM mcp_server_configs), '{}'));
|