codit/backend/migrations/005_ssh_access_profile_grant_only.sql

CREATE TEMP TABLE IF NOT EXISTS ssh_access_profile_explicit_grant_map (
  profile_public_id TEXT PRIMARY KEY,
  grant_public_id TEXT NOT NULL
);

DELETE FROM ssh_access_profile_explicit_grant_map;

INSERT INTO ssh_access_profile_explicit_grant_map (profile_public_id, grant_public_id)
SELECT public_id, lower(hex(randomblob(16)))
FROM ssh_access_profiles
WHERE auth_method = 'managed_ssh_cert'
  AND COALESCE(ssh_principal_mode, 'explicit') <> 'grant'
  AND json_array_length(COALESCE(ssh_principals_json, '[]')) > 0;

INSERT INTO ssh_principal_grants (
  public_id,
  principal,
  principals_json,
  valid_after,
  valid_before,
  max_cert_valid_seconds,
  max_uses,
  used_count,
  disabled,
  reason,
  created_by_user_public_id,
  created_at,
  updated_at,
  last_used_at
)
SELECT
  m.grant_public_id,
  p.name,
  p.ssh_principals_json,
  0,
  0,
  p.max_valid_seconds,
  0,
  0,
  0,
  'Migrated from explicit SSH access profile principals',
  NULLIF(p.created_by_subject_id, ''),
  p.created_at,
  p.updated_at,
  0
FROM ssh_access_profile_explicit_grant_map m
JOIN ssh_access_profiles p ON p.public_id = m.profile_public_id;

INSERT OR IGNORE INTO ssh_principal_grant_targets (
  grant_public_id,
  target_type,
  target_public_id,
  created_at
)
SELECT
  m.grant_public_id,
  t.target_type,
  t.target_public_id,
  t.created_at
FROM ssh_access_profile_explicit_grant_map m
JOIN ssh_access_profile_targets t ON t.profile_public_id = m.profile_public_id;

INSERT OR IGNORE INTO ssh_principal_grant_targets (
  grant_public_id,
  target_type,
  target_public_id,
  created_at
)
SELECT
  m.grant_public_id,
  'user',
  p.owner_user_public_id,
  p.created_at
FROM ssh_access_profile_explicit_grant_map m
JOIN ssh_access_profiles p ON p.public_id = m.profile_public_id
WHERE p.owner_scope = 'user'
  AND NULLIF(p.owner_user_public_id, '') IS NOT NULL;

UPDATE ssh_access_profiles
SET ssh_principal_grant_ids_json = (
  SELECT json_array(grant_public_id)
  FROM ssh_access_profile_explicit_grant_map
  WHERE profile_public_id = ssh_access_profiles.public_id
)
WHERE public_id IN (
  SELECT profile_public_id
  FROM ssh_access_profile_explicit_grant_map
);

PRAGMA foreign_keys = OFF;

CREATE TABLE ssh_access_profiles_new (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  server_public_id TEXT NOT NULL,
  name TEXT NOT NULL,
  description TEXT NOT NULL DEFAULT '',
  remote_username TEXT NOT NULL,
  auth_method TEXT NOT NULL,
  second_factor_mode TEXT NOT NULL DEFAULT 'none',
  owner_scope TEXT NOT NULL DEFAULT 'user',
  owner_user_public_id TEXT DEFAULT '',
  allow_user_edit INTEGER NOT NULL DEFAULT 0,
  enabled INTEGER NOT NULL DEFAULT 1,
  secret_public_id TEXT DEFAULT '',
  auth_public_key TEXT NOT NULL DEFAULT '',
  auth_public_key_fingerprint TEXT NOT NULL DEFAULT '',
  ssh_user_ca_public_id TEXT DEFAULT '',
  ssh_principal_grant_ids_json TEXT NOT NULL DEFAULT '[]',
  default_valid_seconds INTEGER NOT NULL DEFAULT 3600,
  max_valid_seconds INTEGER NOT NULL DEFAULT 3600,
  created_by_kind TEXT NOT NULL DEFAULT 'user',
  created_by_subject_id TEXT NOT NULL DEFAULT '',
  created_by_subject_name TEXT NOT NULL DEFAULT '',
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  FOREIGN KEY (server_public_id) REFERENCES ssh_servers(public_id) ON DELETE CASCADE,
  FOREIGN KEY (secret_public_id) REFERENCES ssh_secrets(public_id) ON DELETE SET NULL,
  FOREIGN KEY (ssh_user_ca_public_id) REFERENCES ssh_user_cas(public_id) ON DELETE SET NULL
);

INSERT INTO ssh_access_profiles_new (
  id,
  public_id,
  server_public_id,
  name,
  description,
  remote_username,
  auth_method,
  second_factor_mode,
  owner_scope,
  owner_user_public_id,
  allow_user_edit,
  enabled,
  secret_public_id,
  auth_public_key,
  auth_public_key_fingerprint,
  ssh_user_ca_public_id,
  ssh_principal_grant_ids_json,
  default_valid_seconds,
  max_valid_seconds,
  created_by_kind,
  created_by_subject_id,
  created_by_subject_name,
  created_at,
  updated_at
)
SELECT
  id,
  public_id,
  server_public_id,
  name,
  description,
  remote_username,
  auth_method,
  second_factor_mode,
  owner_scope,
  owner_user_public_id,
  allow_user_edit,
  enabled,
  secret_public_id,
  auth_public_key,
  auth_public_key_fingerprint,
  ssh_user_ca_public_id,
  ssh_principal_grant_ids_json,
  default_valid_seconds,
  max_valid_seconds,
  created_by_kind,
  created_by_subject_id,
  created_by_subject_name,
  created_at,
  updated_at
FROM ssh_access_profiles;

DROP TABLE ssh_access_profiles;

ALTER TABLE ssh_access_profiles_new RENAME TO ssh_access_profiles;

CREATE INDEX IF NOT EXISTS idx_ssh_access_profiles_server ON ssh_access_profiles(server_public_id);
CREATE INDEX IF NOT EXISTS idx_ssh_access_profiles_owner_scope ON ssh_access_profiles(owner_scope);
CREATE INDEX IF NOT EXISTS idx_ssh_access_profiles_owner_user ON ssh_access_profiles(owner_user_public_id);
CREATE INDEX IF NOT EXISTS idx_ssh_access_profiles_enabled ON ssh_access_profiles(enabled);

PRAGMA foreign_keys = ON;

DROP TABLE ssh_access_profile_explicit_grant_map;