mirror of
https://github.com/coder/coder.git
synced 2025-07-03 16:13:58 +00:00
26438aa91f
When creating IDP sync mappings, these are the values that can be selected from. These are the values that can be mapped from in org/group/role sync.
113 lines
3.1 KiB
SQL
113 lines
3.1 KiB
SQL
-- name: GetUserLinkByLinkedID :one
|
|
SELECT
|
|
user_links.*
|
|
FROM
|
|
user_links
|
|
INNER JOIN
|
|
users ON user_links.user_id = users.id
|
|
WHERE
|
|
linked_id = $1
|
|
AND
|
|
deleted = false;
|
|
|
|
-- name: GetUserLinkByUserIDLoginType :one
|
|
SELECT
|
|
*
|
|
FROM
|
|
user_links
|
|
WHERE
|
|
user_id = $1 AND login_type = $2;
|
|
|
|
-- name: GetUserLinksByUserID :many
|
|
SELECT * FROM user_links WHERE user_id = $1;
|
|
|
|
-- name: InsertUserLink :one
|
|
INSERT INTO
|
|
user_links (
|
|
user_id,
|
|
login_type,
|
|
linked_id,
|
|
oauth_access_token,
|
|
oauth_access_token_key_id,
|
|
oauth_refresh_token,
|
|
oauth_refresh_token_key_id,
|
|
oauth_expiry,
|
|
claims
|
|
)
|
|
VALUES
|
|
( $1, $2, $3, $4, $5, $6, $7, $8, $9 ) RETURNING *;
|
|
|
|
-- name: UpdateUserLinkedID :one
|
|
UPDATE
|
|
user_links
|
|
SET
|
|
linked_id = $1
|
|
WHERE
|
|
user_id = $2 AND login_type = $3 RETURNING *;
|
|
|
|
-- name: UpdateUserLink :one
|
|
UPDATE
|
|
user_links
|
|
SET
|
|
oauth_access_token = $1,
|
|
oauth_access_token_key_id = $2,
|
|
oauth_refresh_token = $3,
|
|
oauth_refresh_token_key_id = $4,
|
|
oauth_expiry = $5,
|
|
claims = $6
|
|
WHERE
|
|
user_id = $7 AND login_type = $8 RETURNING *;
|
|
|
|
-- name: OIDCClaimFields :many
|
|
-- OIDCClaimFields returns a list of distinct keys in the the merged_claims fields.
|
|
-- This query is used to generate the list of available sync fields for idp sync settings.
|
|
SELECT
|
|
DISTINCT jsonb_object_keys(claims->'merged_claims')
|
|
FROM
|
|
user_links
|
|
WHERE
|
|
-- Only return rows where the top level key exists
|
|
claims ? 'merged_claims' AND
|
|
-- 'null' is the default value for the id_token_claims field
|
|
-- jsonb 'null' is not the same as SQL NULL. Strip these out.
|
|
jsonb_typeof(claims->'merged_claims') != 'null' AND
|
|
login_type = 'oidc'
|
|
AND CASE WHEN @organization_id :: uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN
|
|
user_links.user_id = ANY(SELECT organization_members.user_id FROM organization_members WHERE organization_id = @organization_id)
|
|
ELSE true
|
|
END
|
|
;
|
|
|
|
-- name: OIDCClaimFieldValues :many
|
|
SELECT
|
|
-- DISTINCT to remove duplicates
|
|
DISTINCT jsonb_array_elements_text(CASE
|
|
-- When the type is an array, filter out any non-string elements.
|
|
-- This is to keep the return type consistent.
|
|
WHEN jsonb_typeof(claims->'merged_claims'->sqlc.arg('claim_field')::text) = 'array' THEN
|
|
(
|
|
SELECT
|
|
jsonb_agg(element)
|
|
FROM
|
|
jsonb_array_elements(claims->'merged_claims'->sqlc.arg('claim_field')::text) AS element
|
|
WHERE
|
|
-- Filtering out non-string elements
|
|
jsonb_typeof(element) = 'string'
|
|
)
|
|
-- Some IDPs return a single string instead of an array of strings.
|
|
WHEN jsonb_typeof(claims->'merged_claims'->sqlc.arg('claim_field')::text) = 'string' THEN
|
|
jsonb_build_array(claims->'merged_claims'->sqlc.arg('claim_field')::text)
|
|
END)
|
|
FROM
|
|
user_links
|
|
WHERE
|
|
-- IDP sync only supports string and array (of string) types
|
|
jsonb_typeof(claims->'merged_claims'->sqlc.arg('claim_field')::text) = ANY(ARRAY['string', 'array'])
|
|
AND login_type = 'oidc'
|
|
AND CASE
|
|
WHEN @organization_id :: uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN
|
|
user_links.user_id = ANY(SELECT organization_members.user_id FROM organization_members WHERE organization_id = @organization_id)
|
|
ELSE true
|
|
END
|
|
;
|