sailfork / t3code-github / .repos/ alchemy-effect/ packages/ gcp/ src/ SQL/ User.ts

import * as sqladmin from "@distilled.cloud/gcp/sqladmin_v1";
import { isResolved } from "alchemy/Diff";
import { createPhysicalName } from "alchemy/PhysicalName";
import * as Provider from "alchemy/Provider";
import { Resource } from "alchemy/Resource";
import * as Data from "effect/Data";
import * as Effect from "effect/Effect";
import * as Redacted from "effect/Redacted";
import * as Schedule from "effect/Schedule";
import * as Stream from "effect/Stream";
import { GcpEnvironment } from "../Environment.ts";
import { ALCHEMY_LABEL_PREFIX } from "../Labels.ts";
import type { Providers } from "../Providers.ts";
import { recoverIfInstanceMissing, waitForSqlOperation } from "./operations.ts";

const MAX_NAME_LENGTH = 32;

const SYSTEM_USERS = new Set([
  "root",
  "postgres",
  "sqlserver",
  "cloudsqladmin",
  "cloudsqlsuperuser",
  "cloudsqlagent",
  "cloudsqliamuser",
  "cloudsqliamserviceaccount",
  "cloudsqliamgroup",
  "cloudsqlreplica",
  "cloudsqlreadonly",
  "cloudsqlapplier",
  "mysql.sys",
  "mysql.session",
  "mysql.infoschema",
]);

const AUTO_USER_TYPES = new Set(["CLOUD_IAM_GROUP_USER", "CLOUD_IAM_GROUP_SERVICE_ACCOUNT"]);

export type UserDeletionPolicy = "DELETE" | "ABANDON";

export type UserType = sqladmin.UserTypeEnum | (string & {});

export type DualPasswordType = sqladmin.UserDualPasswordTypeEnum | (string & {});

export type SqlServerUserDetails = {
  /**
   * Disable this SQL Server login.
   */
  disabled?: boolean;
  /**
   * SQL Server server-level roles (`sysadmin`, `dbcreator`, …).
   */
  serverRoles?: string[];
};

export type UserPasswordPolicy = {
  /**
   * Failed login attempts allowed before the user is locked.
   */
  allowedFailedAttempts?: number;
  /**
   * How long a password stays valid after it is set (duration string).
   */
  passwordExpirationDuration?: string;
  /**
   * Enable the failed-attempts lockout check.
   */
  enableFailedAttemptsCheck?: boolean;
  /**
   * Require the current password when changing it. MySQL only.
   */
  enablePasswordVerification?: boolean;
};

export type UserPasswordPolicyStatus = {
  /** Whether the user is locked out of login. */
  locked?: boolean;
  /** RFC3339 expiration of the current password. */
  passwordExpirationTime?: string;
};

export type UserProps = {
  /**
   * Cloud SQL instance id (the `{instance}` segment of
   * `projects/{project}/instances/{instance}`). Full resource names are
   * accepted and reduced to the last path segment. Immutable — changing
   * it replaces the user.
   */
  instance: string;
  /**
   * Database user name. If omitted, a unique name is generated from the
   * stack, stage, and logical id. MySQL limits names to 32 characters.
   * Immutable — changing it replaces the user.
   */
  userName?: string;
  /**
   * Host the user can connect from. MySQL `BUILT_IN` users only; omit
   * for PostgreSQL and SQL Server. Immutable — changing it replaces the
   * user. `"%"` allows any host.
   */
  host?: string;
  /**
   * Password for `BUILT_IN` users. Write-only — never returned by the
   * API. Required for PostgreSQL built-in users. Omit for IAM user
   * types. Updating this value rotates the password. Accepts a
   * `Redacted` value (e.g. `Alchemy.Random`'s `text`).
   */
  password?: string | Redacted.Redacted<string>;
  /**
   * Authentication type (`BUILT_IN`, `CLOUD_IAM_USER`,
   * `CLOUD_IAM_SERVICE_ACCOUNT`, `CLOUD_IAM_GROUP`, …). Immutable —
   * changing it replaces the user.
   * @default "BUILT_IN"
   */
  type?: UserType;
  /**
   * PostgreSQL / MySQL database roles to grant (`cloudsqlsuperuser`, …).
   */
  databaseRoles?: string[];
  /**
   * User-level password validation policy (MySQL).
   */
  passwordPolicy?: UserPasswordPolicy;
  /**
   * Dual password status (`NO_DUAL_PASSWORD`, `DUAL_PASSWORD`, …).
   */
  dualPasswordType?: DualPasswordType;
  /**
   * SQL Server–only login details. Ignored on MySQL and PostgreSQL.
   */
  sqlserverUserDetails?: SqlServerUserDetails;
  /**
   * Full email for an IAM user. MySQL IAM users only.
   */
  iamEmail?: string;
  /**
   * What to do on destroy. `ABANDON` removes the user from state without
   * calling the Cloud SQL API — useful when the parent instance is being
   * deleted, or when a PostgreSQL user still holds roles.
   * @default "DELETE"
   */
  deletionPolicy?: UserDeletionPolicy;
};

export type User = Resource<
  "GCP.SQL.User",
  UserProps,
  {
    /** Database user name. */
    userName: string;
    /** Host the user can connect from, if set. */
    host: string | undefined;
    /** Cloud SQL instance id. */
    instance: string;
    /** Project id. */
    project: string;
    /** Authentication type. */
    type: string | undefined;
    /** IAM email, if this is an IAM user. */
    iamEmail: string | undefined;
    /** Granted database roles. */
    databaseRoles: string[] | undefined;
    /** Password policy currently on the user. */
    passwordPolicy: (UserPasswordPolicy & { status?: UserPasswordPolicyStatus }) | undefined;
    /** Dual password status. */
    dualPasswordType: string | undefined;
    /** SQL Server details, if this is a SQL Server login. */
    sqlserverUserDetails: SqlServerUserDetails | undefined;
    /** IAM group availability (`ACTIVE`, `INACTIVE`, …). */
    iamStatus: string | undefined;
    /** HTTP etag. */
    etag: string | undefined;
  },
  never,
  Providers
>;

/**
 * A database user on a Cloud SQL instance.
 *
 * Cloud SQL users have no labels field. `list` enumerates non-system
 * users on alchemy-labeled instances so `pnpm nuke:gcp` can find leaked
 * rows without dropping `root` / `postgres` / `cloudsqladmin`.
 *
 * Changing `instance`, `userName`, `host`, or `type` replaces the user.
 * `password`, `databaseRoles`, and `passwordPolicy` update in place.
 *
 * ### Creating a User
 * **Example:** Generated name on a Cloud SQL instance
 * ```typescript
 * const instance = yield* GCP.SQL.Instance("AppDb", {
 *   tier: "db-f1-micro",
 *   backup: { enabled: false },
 * });
 * const appUser = yield* GCP.SQL.User("AppUser", {
 *   instance: instance.instanceName,
 *   password: "change-me",
 * });
 * ```
 *
 * **Example:** Explicit MySQL user and host
 * ```typescript
 * const appUser = yield* GCP.SQL.User("AppUser", {
 *   instance: instance.instanceName,
 *   userName: "app",
 *   host: "%",
 *   password: "change-me",
 *   type: "BUILT_IN",
 * });
 * ```
 *
 * ### IAM Users
 * **Example:** Cloud IAM database user
 * ```typescript
 * const iamUser = yield* GCP.SQL.User("IamUser", {
 *   instance: instance.instanceName,
 *   userName: "alice@example.com",
 *   type: "CLOUD_IAM_USER",
 * });
 * ```
 *
 * @resource
 * @category SQL
 */
export const User = Resource<User>("GCP.SQL.User");

export class UserNotResolved extends Data.TaggedError("GCP.SQL.UserNotResolved")<{
  instance: string;
  userName: string;
  host: string | undefined;
}> {}

export class UserStillExists extends Data.TaggedError("GCP.SQL.UserStillExists")<{
  instance: string;
  userName: string;
  host: string | undefined;
}> {}

const lastSegment = (value: string) => {
  const trimmed = value.replace(/\/+$/, "");
  const parts = trimmed.split("/");
  return parts[parts.length - 1] || trimmed;
};

const instanceIdOf = (value: string) => lastSegment(value);

const normalizeHost = (host: string | undefined) => host ?? "";

const typeOf = (type: string | undefined) => (type ?? "BUILT_IN").toUpperCase();

const isIamType = (type: string | undefined) => {
  const value = typeOf(type);
  return value.startsWith("CLOUD_IAM") || value === "ENTRAID_USER";
};

const hasAlchemyInstanceLabels = (labels: Record<string, string | undefined> | null | undefined) =>
  Object.keys(labels ?? {}).some((key) => key.startsWith(ALCHEMY_LABEL_PREFIX));

const isManagedUser = (user: sqladmin.User) => {
  const name = (user.name ?? "").toLowerCase();
  if (name.length === 0) return false;
  if (SYSTEM_USERS.has(name)) return false;
  if (name.startsWith("cloudsql")) return false;
  if (name.startsWith("mysql.")) return false;
  if (user.type !== undefined && AUTO_USER_TYPES.has(user.type)) return false;
  return true;
};

const toSqlIdentifier = (name: string) => {
  let next = name
    .toLowerCase()
    .replace(/[^a-z0-9_]/g, "_")
    .replace(/_+/g, "_")
    .replace(/^_+|_+$/g, "");
  if (!/^[a-z]/.test(next)) next = `u${next}`;
  next = next.slice(0, MAX_NAME_LENGTH).replace(/_+$/g, "");
  return next.length > 0 ? next : "dbuser";
};

const toUserName = (id: string, userName: string | undefined, existing?: string) =>
  Effect.gen(function* () {
    if (userName !== undefined) return userName;
    if (existing !== undefined) return existing;
    return toSqlIdentifier(
      yield* createPhysicalName({
        id,
        maxLength: MAX_NAME_LENGTH,
        lowercase: true,
        delimiter: "_",
      }),
    );
  });

const rolesKey = (roles: string[] | undefined) =>
  JSON.stringify([...(roles ?? [])].map((role) => role.toLowerCase()).sort());

const policyKey = (
  policy: UserPasswordPolicy | sqladmin.UserPasswordValidationPolicy | undefined,
) =>
  JSON.stringify({
    allowedFailedAttempts: policy?.allowedFailedAttempts ?? null,
    passwordExpirationDuration: policy?.passwordExpirationDuration ?? "",
    enableFailedAttemptsCheck: policy?.enableFailedAttemptsCheck === true,
    enablePasswordVerification: policy?.enablePasswordVerification === true,
  });

const sqlServerKey = (details: SqlServerUserDetails | undefined) =>
  JSON.stringify({
    disabled: details?.disabled === true,
    serverRoles: [...(details?.serverRoles ?? [])].map((role) => role.toLowerCase()).sort(),
  });

const dualPasswordOf = (value: string | undefined) =>
  (value ?? "DUAL_PASSWORD_TYPE_UNSPECIFIED").toUpperCase();

const toPasswordPolicy = (
  policy: UserPasswordPolicy | sqladmin.UserPasswordValidationPolicy | undefined,
): sqladmin.UserPasswordValidationPolicy | undefined => {
  if (policy === undefined) return undefined;
  return {
    allowedFailedAttempts: policy.allowedFailedAttempts,
    passwordExpirationDuration: policy.passwordExpirationDuration,
    enableFailedAttemptsCheck: policy.enableFailedAttemptsCheck,
    enablePasswordVerification: policy.enablePasswordVerification,
  };
};

const toAttrs = (live: sqladmin.User, project: string, instance: string) => ({
  userName: live.name ?? "",
  host: live.host,
  instance: live.instance ?? instance,
  project: live.project ?? project,
  type: live.type,
  iamEmail: live.iamEmail,
  databaseRoles: live.databaseRoles,
  passwordPolicy: live.passwordPolicy,
  dualPasswordType: live.dualPasswordType,
  sqlserverUserDetails: live.sqlserverUserDetails,
  iamStatus: live.iamStatus,
  etag: live.etag,
});

const matchesUser = (live: sqladmin.User, userName: string, host: string | undefined) =>
  (live.name ?? "") === userName &&
  (host === undefined || normalizeHost(live.host) === normalizeHost(host));

const getByName = (project: string, instance: string, userName: string, host: string | undefined) =>
  sqladmin
    .getUsers({
      project,
      instance,
      name: userName,
      ...(normalizeHost(host) ? { host: normalizeHost(host) } : {}),
    })
    .pipe(
      Effect.catchTag("NotFound", () => Effect.undefined),
      recoverIfInstanceMissing(project, instance, () => undefined),
      Effect.flatMap((live) => {
        if (live !== undefined && matchesUser(live, userName, host)) {
          return Effect.succeed(live);
        }
        return sqladmin.listUsers({ project, instance }).pipe(
          Effect.map((page) =>
            (page.items ?? []).find((item) => matchesUser(item, userName, host)),
          ),
          Effect.catchTag("NotFound", () => Effect.undefined),
          recoverIfInstanceMissing(project, instance, () => undefined),
        );
      }),
    );

const waitForOperation = (
  project: string,
  operation: sqladmin.Operation,
  options?: { notFoundOk?: boolean },
) =>
  waitForSqlOperation(project, operation, {
    budget: "10 minutes",
    notFoundOk: options?.notFoundOk,
  });

const waitUntilExists = (
  project: string,
  instance: string,
  userName: string,
  host: string | undefined,
) =>
  getByName(project, instance, userName, host).pipe(
    Effect.flatMap((user) =>
      user ? Effect.succeed(user) : Effect.fail(new UserNotResolved({ instance, userName, host })),
    ),
    Effect.retry({
      while: (error) => error._tag === "GCP.SQL.UserNotResolved",
      times: 8,
      schedule: Schedule.spaced("1 second"),
    }),
  );

const waitUntilGone = (
  project: string,
  instance: string,
  userName: string,
  host: string | undefined,
) =>
  getByName(project, instance, userName, host).pipe(
    Effect.flatMap((user) =>
      user === undefined
        ? Effect.void
        : Effect.fail(new UserStillExists({ instance, userName, host })),
    ),
    Effect.retry({
      while: (error) => error._tag === "GCP.SQL.UserStillExists",
      times: 10,
      schedule: Schedule.spaced("1 second"),
    }),
  );

const passwordValue = (password: string | Redacted.Redacted<string>) =>
  Redacted.isRedacted(password) ? Redacted.value(password) : password;

const toBody = (
  news: UserProps,
  userName: string,
  instance: string,
  project: string,
  current?: sqladmin.User,
  options?: { includePassword?: boolean },
): sqladmin.User => {
  const type = news.type ?? current?.type;
  const includePassword =
    options?.includePassword !== false && news.password !== undefined && !isIamType(type);
  return {
    name: userName,
    instance,
    project,
    host: news.host ?? current?.host,
    type,
    password:
      includePassword && news.password !== undefined ? passwordValue(news.password) : undefined,
    // Echoing the observed roles back is rejected ("Invalid request to
    // update database roles"); send roles only when they are declared.
    databaseRoles: news.databaseRoles,
    passwordPolicy: toPasswordPolicy(news.passwordPolicy ?? current?.passwordPolicy),
    dualPasswordType: news.dualPasswordType ?? current?.dualPasswordType,
    sqlserverUserDetails: news.sqlserverUserDetails ?? current?.sqlserverUserDetails,
    iamEmail: news.iamEmail ?? current?.iamEmail,
  };
};

const recoverIfPresent = <E>(
  project: string,
  instance: string,
  userName: string,
  host: string | undefined,
  error: E,
) =>
  getByName(project, instance, userName, host).pipe(
    Effect.flatMap((existing) => (existing ? Effect.void : Effect.fail(error))),
  );

export const UserProvider = () =>
  Provider.succeed(User, {
    stables: ["userName", "host", "instance", "project"],

    diff: Effect.fn(function* ({ news, olds, output }) {
      if (!isResolved(news)) return undefined;
      const previousInstance = olds?.instance ?? output?.instance;
      const nextInstance = news.instance;
      const previousName = olds?.userName ?? output?.userName;
      const nextName = news.userName ?? previousName;
      const previousHost = olds?.host ?? output?.host;
      const nextHost = news.host ?? previousHost;
      const previousType = olds?.type ?? output?.type;
      const nextType = news.type ?? previousType;
      const instanceChanged =
        previousInstance !== undefined &&
        instanceIdOf(previousInstance) !== instanceIdOf(nextInstance);
      const nameChanged =
        previousName !== undefined && nextName !== undefined && previousName !== nextName;
      const hostChanged =
        previousHost !== undefined &&
        nextHost !== undefined &&
        normalizeHost(previousHost) !== normalizeHost(nextHost);
      const typeChanged =
        previousType !== undefined &&
        nextType !== undefined &&
        typeOf(previousType) !== typeOf(nextType);
      if (!instanceChanged && !nameChanged && !hostChanged && !typeChanged) {
        return undefined;
      }
      const identityChanged = instanceChanged || nameChanged || hostChanged;
      return {
        action: "replace" as const,
        deleteFirst: typeChanged && !identityChanged,
      };
    }),

    read: Effect.fn(function* ({ id, olds, output }) {
      const env = yield* GcpEnvironment.current;
      const instance = instanceIdOf(olds?.instance ?? output?.instance ?? "");
      if (instance.length === 0) return undefined;
      const userName = yield* toUserName(id, olds?.userName, output?.userName);
      const host = olds?.host ?? output?.host;
      const existing = yield* getByName(env.project, instance, userName, host);
      if (existing === undefined) return undefined;
      return toAttrs(existing, env.project, instance);
    }),

    list: () =>
      Effect.gen(function* () {
        const env = yield* GcpEnvironment.current;
        const instances = yield* sqladmin.listInstances
          .items({
            project: env.project,
            maxResults: 1000,
            filter: "instanceType:CLOUD_SQL_INSTANCE",
          })
          .pipe(
            Stream.filter((instance) => hasAlchemyInstanceLabels(instance.settings?.userLabels)),
            Stream.runCollect,
            Effect.map((chunk) => Array.from(chunk)),
            Effect.catchTag("NotFound", () => Effect.succeed([] as sqladmin.DatabaseInstance[])),
          );
        const pages = yield* Effect.forEach(
          instances,
          (instance) => {
            const instanceName = instance.name;
            if (instanceName === undefined || instanceName.length === 0) {
              return Effect.succeed([] as User["Attributes"][]);
            }
            return sqladmin
              .listUsers({
                project: env.project,
                instance: instanceName,
              })
              .pipe(
                Effect.map((page) =>
                  (page.items ?? [])
                    .filter(isManagedUser)
                    .map((user) => toAttrs(user, env.project, instanceName)),
                ),
                // The instance may be deleted mid-listing.
                Effect.catchTag("NotFound", () => Effect.succeed([] as User["Attributes"][])),
                recoverIfInstanceMissing(
                  env.project,
                  instanceName,
                  () => [] as User["Attributes"][],
                ),
              );
          },
          { concurrency: 4 },
        );
        return pages.flat();
      }),

    reconcile: Effect.fn(function* ({ id, news, output, olds }) {
      const env = yield* GcpEnvironment.current;
      const instance = instanceIdOf(news.instance);
      const userName = yield* toUserName(id, news.userName, output?.userName);
      const host = news.host ?? output?.host;

      let current = yield* getByName(env.project, instance, userName, host);
      let inserted = false;

      if (current === undefined) {
        const created = yield* sqladmin
          .insertUsers({
            project: env.project,
            instance,
            body: toBody(news, userName, instance, env.project),
          })
          .pipe(
            Effect.flatMap((operation) =>
              waitForOperation(env.project, operation).pipe(Effect.as(true)),
            ),
            Effect.catchTags({
              Conflict: (error) =>
                recoverIfPresent(env.project, instance, userName, host, error).pipe(
                  Effect.as(false),
                ),
              BadRequest: (error) =>
                recoverIfPresent(env.project, instance, userName, host, error).pipe(
                  Effect.as(false),
                ),
            }),
            Effect.retry({
              while: (error) => error._tag === "Conflict",
              times: 8,
              schedule: Schedule.spaced("5 seconds"),
            }),
          );
        current = yield* waitUntilExists(env.project, instance, userName, host);
        inserted = created;
      }

      const passwordChanged =
        news.password !== undefined &&
        !inserted &&
        !isIamType(news.type ?? current.type) &&
        (olds?.password === undefined ||
          passwordValue(olds.password) !== passwordValue(news.password));
      const rolesChanged =
        news.databaseRoles !== undefined &&
        rolesKey(current.databaseRoles) !== rolesKey(news.databaseRoles);
      const policyChanged =
        news.passwordPolicy !== undefined &&
        policyKey(current.passwordPolicy) !== policyKey(news.passwordPolicy);
      const dualPasswordChanged =
        news.dualPasswordType !== undefined &&
        dualPasswordOf(current.dualPasswordType) !== dualPasswordOf(news.dualPasswordType);
      const sqlServerChanged =
        news.sqlserverUserDetails !== undefined &&
        sqlServerKey(current.sqlserverUserDetails) !== sqlServerKey(news.sqlserverUserDetails);
      const iamEmailChanged =
        news.iamEmail !== undefined && (current.iamEmail ?? "") !== news.iamEmail;

      if (
        passwordChanged ||
        rolesChanged ||
        policyChanged ||
        dualPasswordChanged ||
        sqlServerChanged ||
        iamEmailChanged
      ) {
        const patched = yield* sqladmin.updateUsers({
          project: env.project,
          instance,
          name: userName,
          ...(normalizeHost(host) ? { host: normalizeHost(host) } : {}),
          revokeExistingRoles: rolesChanged ? true : undefined,
          body: toBody(news, userName, instance, env.project, current, {
            includePassword: passwordChanged,
          }),
        });
        yield* waitForOperation(env.project, patched);
        current = yield* waitUntilExists(env.project, instance, userName, host);
      }

      return toAttrs(current, env.project, instance);
    }),

    delete: Effect.fn(function* ({ olds, output }) {
      if (olds.deletionPolicy === "ABANDON") return;
      const project = output.project;
      const instance = instanceIdOf(output.instance);
      const userName = output.userName;
      const host = output.host;
      yield* sqladmin
        .deleteUsers({
          project,
          instance,
          name: userName,
          ...(normalizeHost(host) ? { host: normalizeHost(host) } : {}),
        })
        .pipe(
          Effect.flatMap((operation) => waitForOperation(project, operation, { notFoundOk: true })),
          Effect.catchTag("NotFound", () => Effect.void),
          recoverIfInstanceMissing(project, instance, () => undefined),
          Effect.retry({
            while: (error) => error._tag === "Conflict",
            times: 8,
            schedule: Schedule.spaced("5 seconds"),
          }),
        );
      yield* waitUntilGone(project, instance, userName, host);
    }),
  });