1import * as sqladmin from "@distilled.cloud/gcp/sqladmin_v1";
2import * as Data from "effect/Data";
3import * as Effect from "effect/Effect";
4import * as Redacted from "effect/Redacted";
5import * as Schedule from "effect/Schedule";
6import * as Stream from "effect/Stream";
7import { isResolved } from "../../Diff.ts";
8import { createPhysicalName } from "../../PhysicalName.ts";
9import * as Provider from "../../Provider.ts";
10import { Resource } from "../../Resource.ts";
11import { GcpEnvironment } from "../Environment.ts";
12import { ALCHEMY_LABEL_PREFIX } from "../Labels.ts";
13import type { Providers } from "../Providers.ts";
14import { recoverIfInstanceMissing, waitForSqlOperation } from "./operations.ts";
15
16const MAX_NAME_LENGTH = 32;
17
18const SYSTEM_USERS = new Set([
19 "root",
20 "postgres",
21 "sqlserver",
22 "cloudsqladmin",
23 "cloudsqlsuperuser",
24 "cloudsqlagent",
25 "cloudsqliamuser",
26 "cloudsqliamserviceaccount",
27 "cloudsqliamgroup",
28 "cloudsqlreplica",
29 "cloudsqlreadonly",
30 "cloudsqlapplier",
31 "mysql.sys",
32 "mysql.session",
33 "mysql.infoschema",
34]);
35
36const AUTO_USER_TYPES = new Set([
37 "CLOUD_IAM_GROUP_USER",
38 "CLOUD_IAM_GROUP_SERVICE_ACCOUNT",
39]);
40
41export type UserDeletionPolicy = "DELETE" | "ABANDON";
42
43export type UserType = sqladmin.UserTypeEnum | (string & {});
44
45export type DualPasswordType =
46 | sqladmin.UserDualPasswordTypeEnum
47 | (string & {});
48
49export type SqlServerUserDetails = {
50 /**
51 * Disable this SQL Server login.
52 */
53 disabled?: boolean;
54 /**
55 * SQL Server server-level roles (`sysadmin`, `dbcreator`, …).
56 */
57 serverRoles?: string[];
58};
59
60export type UserPasswordPolicy = {
61 /**
62 * Failed login attempts allowed before the user is locked.
63 */
64 allowedFailedAttempts?: number;
65 /**
66 * How long a password stays valid after it is set (duration string).
67 */
68 passwordExpirationDuration?: string;
69 /**
70 * Enable the failed-attempts lockout check.
71 */
72 enableFailedAttemptsCheck?: boolean;
73 /**
74 * Require the current password when changing it. MySQL only.
75 */
76 enablePasswordVerification?: boolean;
77};
78
79export type UserPasswordPolicyStatus = {
80 /** Whether the user is locked out of login. */
81 locked?: boolean;
82 /** RFC3339 expiration of the current password. */
83 passwordExpirationTime?: string;
84};
85
86export type UserProps = {
87 /**
88 * Cloud SQL instance id (the `{instance}` segment of
89 * `projects/{project}/instances/{instance}`). Full resource names are
90 * accepted and reduced to the last path segment. Immutable — changing
91 * it replaces the user.
92 */
93 instance: string;
94 /**
95 * Database user name. If omitted, a unique name is generated from the
96 * stack, stage, and logical id. MySQL limits names to 32 characters.
97 * Immutable — changing it replaces the user.
98 */
99 userName?: string;
100 /**
101 * Host the user can connect from. MySQL `BUILT_IN` users only; omit
102 * for PostgreSQL and SQL Server. Immutable — changing it replaces the
103 * user. `"%"` allows any host.
104 */
105 host?: string;
106 /**
107 * Password for `BUILT_IN` users. Write-only — never returned by the
108 * API. Required for PostgreSQL built-in users. Omit for IAM user
109 * types. Updating this value rotates the password. Accepts a
110 * `Redacted` value (e.g. `Alchemy.Random`'s `text`).
111 */
112 password?: string | Redacted.Redacted<string>;
113 /**
114 * Authentication type (`BUILT_IN`, `CLOUD_IAM_USER`,
115 * `CLOUD_IAM_SERVICE_ACCOUNT`, `CLOUD_IAM_GROUP`, …). Immutable —
116 * changing it replaces the user.
117 * @default "BUILT_IN"
118 */
119 type?: UserType;
120 /**
121 * PostgreSQL / MySQL database roles to grant (`cloudsqlsuperuser`, …).
122 */
123 databaseRoles?: string[];
124 /**
125 * User-level password validation policy (MySQL).
126 */
127 passwordPolicy?: UserPasswordPolicy;
128 /**
129 * Dual password status (`NO_DUAL_PASSWORD`, `DUAL_PASSWORD`, …).
130 */
131 dualPasswordType?: DualPasswordType;
132 /**
133 * SQL Server–only login details. Ignored on MySQL and PostgreSQL.
134 */
135 sqlserverUserDetails?: SqlServerUserDetails;
136 /**
137 * Full email for an IAM user. MySQL IAM users only.
138 */
139 iamEmail?: string;
140 /**
141 * What to do on destroy. `ABANDON` removes the user from state without
142 * calling the Cloud SQL API — useful when the parent instance is being
143 * deleted, or when a PostgreSQL user still holds roles.
144 * @default "DELETE"
145 */
146 deletionPolicy?: UserDeletionPolicy;
147};
148
149export type User = Resource<
150 "GCP.SQL.User",
151 UserProps,
152 {
153 /** Database user name. */
154 userName: string;
155 /** Host the user can connect from, if set. */
156 host: string | undefined;
157 /** Cloud SQL instance id. */
158 instance: string;
159 /** Project id. */
160 project: string;
161 /** Authentication type. */
162 type: string | undefined;
163 /** IAM email, if this is an IAM user. */
164 iamEmail: string | undefined;
165 /** Granted database roles. */
166 databaseRoles: string[] | undefined;
167 /** Password policy currently on the user. */
168 passwordPolicy:
169 | (UserPasswordPolicy & { status?: UserPasswordPolicyStatus })
170 | undefined;
171 /** Dual password status. */
172 dualPasswordType: string | undefined;
173 /** SQL Server details, if this is a SQL Server login. */
174 sqlserverUserDetails: SqlServerUserDetails | undefined;
175 /** IAM group availability (`ACTIVE`, `INACTIVE`, …). */
176 iamStatus: string | undefined;
177 /** HTTP etag. */
178 etag: string | undefined;
179 },
180 never,
181 Providers
182>;
183
184/**
185 * A database user on a Cloud SQL instance.
186 *
187 * Cloud SQL users have no labels field. `list` enumerates non-system
188 * users on alchemy-labeled instances so `pnpm nuke:gcp` can find leaked
189 * rows without dropping `root` / `postgres` / `cloudsqladmin`.
190 *
191 * Changing `instance`, `userName`, `host`, or `type` replaces the user.
192 * `password`, `databaseRoles`, and `passwordPolicy` update in place.
193 *
194 * ### Creating a User
195 * **Example:** Generated name on a Cloud SQL instance
196 * ```typescript
197 * const instance = yield* GCP.SQL.Instance("AppDb", {
198 * tier: "db-f1-micro",
199 * backupEnabled: false,
200 * });
201 * const appUser = yield* GCP.SQL.User("AppUser", {
202 * instance: instance.instanceName,
203 * password: "change-me",
204 * });
205 * ```
206 *
207 * **Example:** Explicit MySQL user and host
208 * ```typescript
209 * const appUser = yield* GCP.SQL.User("AppUser", {
210 * instance: instance.instanceName,
211 * userName: "app",
212 * host: "%",
213 * password: "change-me",
214 * type: "BUILT_IN",
215 * });
216 * ```
217 *
218 * ### IAM Users
219 * **Example:** Cloud IAM database user
220 * ```typescript
221 * const iamUser = yield* GCP.SQL.User("IamUser", {
222 * instance: instance.instanceName,
223 * userName: "alice@example.com",
224 * type: "CLOUD_IAM_USER",
225 * });
226 * ```
227 *
228 * @resource
229 * @category SQL
230 */
231export const User = Resource<User>("GCP.SQL.User");
232
233export class UserNotResolved extends Data.TaggedError(
234 "GCP.SQL.UserNotResolved",
235)<{
236 instance: string;
237 userName: string;
238 host: string | undefined;
239}> {}
240
241export class UserStillExists extends Data.TaggedError(
242 "GCP.SQL.UserStillExists",
243)<{
244 instance: string;
245 userName: string;
246 host: string | undefined;
247}> {}
248
249const lastSegment = (value: string) => {
250 const trimmed = value.replace(/\/+$/, "");
251 const parts = trimmed.split("/");
252 return parts[parts.length - 1] || trimmed;
253};
254
255const instanceIdOf = (value: string) => lastSegment(value);
256
257const normalizeHost = (host: string | undefined) => host ?? "";
258
259const typeOf = (type: string | undefined) => (type ?? "BUILT_IN").toUpperCase();
260
261const isIamType = (type: string | undefined) => {
262 const value = typeOf(type);
263 return value.startsWith("CLOUD_IAM") || value === "ENTRAID_USER";
264};
265
266const hasAlchemyInstanceLabels = (
267 labels: Record<string, string | undefined> | null | undefined,
268) =>
269 Object.keys(labels ?? {}).some((key) => key.startsWith(ALCHEMY_LABEL_PREFIX));
270
271const isManagedUser = (user: sqladmin.User) => {
272 const name = (user.name ?? "").toLowerCase();
273 if (name.length === 0) return false;
274 if (SYSTEM_USERS.has(name)) return false;
275 if (name.startsWith("cloudsql")) return false;
276 if (name.startsWith("mysql.")) return false;
277 if (user.type !== undefined && AUTO_USER_TYPES.has(user.type)) return false;
278 return true;
279};
280
281const toSqlIdentifier = (name: string) => {
282 let next = name
283 .toLowerCase()
284 .replace(/[^a-z0-9_]/g, "_")
285 .replace(/_+/g, "_")
286 .replace(/^_+|_+$/g, "");
287 if (!/^[a-z]/.test(next)) next = `u${next}`;
288 next = next.slice(0, MAX_NAME_LENGTH).replace(/_+$/g, "");
289 return next.length > 0 ? next : "dbuser";
290};
291
292const toUserName = (
293 id: string,
294 userName: string | undefined,
295 existing?: string,
296) =>
297 Effect.gen(function* () {
298 if (userName !== undefined) return userName;
299 if (existing !== undefined) return existing;
300 return toSqlIdentifier(
301 yield* createPhysicalName({
302 id,
303 maxLength: MAX_NAME_LENGTH,
304 lowercase: true,
305 delimiter: "_",
306 }),
307 );
308 });
309
310const rolesKey = (roles: string[] | undefined) =>
311 JSON.stringify([...(roles ?? [])].map((role) => role.toLowerCase()).sort());
312
313const policyKey = (
314 policy:
315 | UserPasswordPolicy
316 | sqladmin.UserPasswordValidationPolicy
317 | undefined,
318) =>
319 JSON.stringify({
320 allowedFailedAttempts: policy?.allowedFailedAttempts ?? null,
321 passwordExpirationDuration: policy?.passwordExpirationDuration ?? "",
322 enableFailedAttemptsCheck: policy?.enableFailedAttemptsCheck === true,
323 enablePasswordVerification: policy?.enablePasswordVerification === true,
324 });
325
326const sqlServerKey = (details: SqlServerUserDetails | undefined) =>
327 JSON.stringify({
328 disabled: details?.disabled === true,
329 serverRoles: [...(details?.serverRoles ?? [])]
330 .map((role) => role.toLowerCase())
331 .sort(),
332 });
333
334const dualPasswordOf = (value: string | undefined) =>
335 (value ?? "DUAL_PASSWORD_TYPE_UNSPECIFIED").toUpperCase();
336
337const toPasswordPolicy = (
338 policy:
339 | UserPasswordPolicy
340 | sqladmin.UserPasswordValidationPolicy
341 | undefined,
342): sqladmin.UserPasswordValidationPolicy | undefined => {
343 if (policy === undefined) return undefined;
344 return {
345 allowedFailedAttempts: policy.allowedFailedAttempts,
346 passwordExpirationDuration: policy.passwordExpirationDuration,
347 enableFailedAttemptsCheck: policy.enableFailedAttemptsCheck,
348 enablePasswordVerification: policy.enablePasswordVerification,
349 };
350};
351
352const toAttrs = (live: sqladmin.User, project: string, instance: string) => ({
353 userName: live.name ?? "",
354 host: live.host,
355 instance: live.instance ?? instance,
356 project: live.project ?? project,
357 type: live.type,
358 iamEmail: live.iamEmail,
359 databaseRoles: live.databaseRoles,
360 passwordPolicy: live.passwordPolicy,
361 dualPasswordType: live.dualPasswordType,
362 sqlserverUserDetails: live.sqlserverUserDetails,
363 iamStatus: live.iamStatus,
364 etag: live.etag,
365});
366
367const matchesUser = (
368 live: sqladmin.User,
369 userName: string,
370 host: string | undefined,
371) =>
372 (live.name ?? "") === userName &&
373 (host === undefined || normalizeHost(live.host) === normalizeHost(host));
374
375const getByName = (
376 project: string,
377 instance: string,
378 userName: string,
379 host: string | undefined,
380) =>
381 sqladmin
382 .getUsers({
383 project,
384 instance,
385 name: userName,
386 ...(normalizeHost(host) ? { host: normalizeHost(host) } : {}),
387 })
388 .pipe(
389 Effect.catchTag("NotFound", () => Effect.succeed(undefined)),
390 recoverIfInstanceMissing(project, instance, () => undefined),
391 Effect.flatMap((live) => {
392 if (live !== undefined && matchesUser(live, userName, host)) {
393 return Effect.succeed(live);
394 }
395 return sqladmin.listUsers({ project, instance }).pipe(
396 Effect.map((page) =>
397 (page.items ?? []).find((item) =>
398 matchesUser(item, userName, host),
399 ),
400 ),
401 Effect.catchTag("NotFound", () => Effect.succeed(undefined)),
402 recoverIfInstanceMissing(project, instance, () => undefined),
403 );
404 }),
405 );
406
407const waitForOperation = (
408 project: string,
409 operation: sqladmin.Operation,
410 options?: { notFoundOk?: boolean },
411) =>
412 waitForSqlOperation(project, operation, {
413 budget: "10 minutes",
414 notFoundOk: options?.notFoundOk,
415 });
416
417const waitUntilExists = (
418 project: string,
419 instance: string,
420 userName: string,
421 host: string | undefined,
422) =>
423 getByName(project, instance, userName, host).pipe(
424 Effect.flatMap((user) =>
425 user
426 ? Effect.succeed(user)
427 : Effect.fail(new UserNotResolved({ instance, userName, host })),
428 ),
429 Effect.retry({
430 while: (error) => error._tag === "GCP.SQL.UserNotResolved",
431 times: 8,
432 schedule: Schedule.spaced("1 second"),
433 }),
434 );
435
436const waitUntilGone = (
437 project: string,
438 instance: string,
439 userName: string,
440 host: string | undefined,
441) =>
442 getByName(project, instance, userName, host).pipe(
443 Effect.flatMap((user) =>
444 user === undefined
445 ? Effect.void
446 : Effect.fail(new UserStillExists({ instance, userName, host })),
447 ),
448 Effect.retry({
449 while: (error) => error._tag === "GCP.SQL.UserStillExists",
450 times: 10,
451 schedule: Schedule.spaced("1 second"),
452 }),
453 );
454
455const passwordValue = (password: string | Redacted.Redacted<string>) =>
456 Redacted.isRedacted(password) ? Redacted.value(password) : password;
457
458const toBody = (
459 news: UserProps,
460 userName: string,
461 instance: string,
462 project: string,
463 current?: sqladmin.User,
464 options?: { includePassword?: boolean },
465): sqladmin.User => {
466 const type = news.type ?? current?.type;
467 const includePassword =
468 options?.includePassword !== false &&
469 news.password !== undefined &&
470 !isIamType(type);
471 return {
472 name: userName,
473 instance,
474 project,
475 host: news.host ?? current?.host,
476 type,
477 password:
478 includePassword && news.password !== undefined
479 ? passwordValue(news.password)
480 : undefined,
481 // Echoing the observed roles back is rejected ("Invalid request to
482 // update database roles"); send roles only when they are declared.
483 databaseRoles: news.databaseRoles,
484 passwordPolicy: toPasswordPolicy(
485 news.passwordPolicy ?? current?.passwordPolicy,
486 ),
487 dualPasswordType: news.dualPasswordType ?? current?.dualPasswordType,
488 sqlserverUserDetails:
489 news.sqlserverUserDetails ?? current?.sqlserverUserDetails,
490 iamEmail: news.iamEmail ?? current?.iamEmail,
491 };
492};
493
494const recoverIfPresent = <E>(
495 project: string,
496 instance: string,
497 userName: string,
498 host: string | undefined,
499 error: E,
500) =>
501 getByName(project, instance, userName, host).pipe(
502 Effect.flatMap((existing) => (existing ? Effect.void : Effect.fail(error))),
503 );
504
505export const UserProvider = () =>
506 Provider.succeed(User, {
507 stables: ["userName", "host", "instance", "project"],
508
509 diff: Effect.fn(function* ({ news, olds, output }) {
510 if (!isResolved(news)) return undefined;
511 const previousInstance = olds?.instance ?? output?.instance;
512 const nextInstance = news.instance;
513 const previousName = olds?.userName ?? output?.userName;
514 const nextName = news.userName ?? previousName;
515 const previousHost = olds?.host ?? output?.host;
516 const nextHost = news.host ?? previousHost;
517 const previousType = olds?.type ?? output?.type;
518 const nextType = news.type ?? previousType;
519 const instanceChanged =
520 previousInstance !== undefined &&
521 instanceIdOf(previousInstance) !== instanceIdOf(nextInstance);
522 const nameChanged =
523 previousName !== undefined &&
524 nextName !== undefined &&
525 previousName !== nextName;
526 const hostChanged =
527 previousHost !== undefined &&
528 nextHost !== undefined &&
529 normalizeHost(previousHost) !== normalizeHost(nextHost);
530 const typeChanged =
531 previousType !== undefined &&
532 nextType !== undefined &&
533 typeOf(previousType) !== typeOf(nextType);
534 if (!instanceChanged && !nameChanged && !hostChanged && !typeChanged) {
535 return undefined;
536 }
537 const identityChanged = instanceChanged || nameChanged || hostChanged;
538 return {
539 action: "replace" as const,
540 deleteFirst: typeChanged && !identityChanged,
541 };
542 }),
543
544 read: Effect.fn(function* ({ id, olds, output }) {
545 const env = yield* GcpEnvironment.current;
546 const instance = instanceIdOf(olds?.instance ?? output?.instance ?? "");
547 if (instance.length === 0) return undefined;
548 const userName = yield* toUserName(id, olds?.userName, output?.userName);
549 const host = olds?.host ?? output?.host;
550 const existing = yield* getByName(env.project, instance, userName, host);
551 if (existing === undefined) return undefined;
552 return toAttrs(existing, env.project, instance);
553 }),
554
555 list: () =>
556 Effect.gen(function* () {
557 const env = yield* GcpEnvironment.current;
558 const instances = yield* sqladmin.listInstances
559 .items({
560 project: env.project,
561 maxResults: 1000,
562 filter: "instanceType:CLOUD_SQL_INSTANCE",
563 })
564 .pipe(
565 Stream.filter((instance) =>
566 hasAlchemyInstanceLabels(instance.settings?.userLabels),
567 ),
568 Stream.runCollect,
569 Effect.map((chunk) => Array.from(chunk)),
570 Effect.catchTag("NotFound", () =>
571 Effect.succeed([] as sqladmin.DatabaseInstance[]),
572 ),
573 );
574 const pages = yield* Effect.forEach(
575 instances,
576 (instance) => {
577 const instanceName = instance.name;
578 if (instanceName === undefined || instanceName.length === 0) {
579 return Effect.succeed([] as User["Attributes"][]);
580 }
581 return sqladmin
582 .listUsers({
583 project: env.project,
584 instance: instanceName,
585 })
586 .pipe(
587 Effect.map((page) =>
588 (page.items ?? [])
589 .filter(isManagedUser)
590 .map((user) => toAttrs(user, env.project, instanceName)),
591 ),
592 // The instance may be deleted mid-listing.
593 Effect.catchTag("NotFound", () =>
594 Effect.succeed([] as User["Attributes"][]),
595 ),
596 recoverIfInstanceMissing(
597 env.project,
598 instanceName,
599 () => [] as User["Attributes"][],
600 ),
601 );
602 },
603 { concurrency: 4 },
604 );
605 return pages.flat();
606 }),
607
608 reconcile: Effect.fn(function* ({ id, news, output, olds }) {
609 const env = yield* GcpEnvironment.current;
610 const instance = instanceIdOf(news.instance);
611 const userName = yield* toUserName(id, news.userName, output?.userName);
612 const host = news.host ?? output?.host;
613
614 let current = yield* getByName(env.project, instance, userName, host);
615 let inserted = false;
616
617 if (current === undefined) {
618 const created = yield* sqladmin
619 .insertUsers({
620 project: env.project,
621 instance,
622 body: toBody(news, userName, instance, env.project),
623 })
624 .pipe(
625 Effect.flatMap((operation) =>
626 waitForOperation(env.project, operation).pipe(Effect.as(true)),
627 ),
628 Effect.catchTag("Conflict", (error) =>
629 recoverIfPresent(
630 env.project,
631 instance,
632 userName,
633 host,
634 error,
635 ).pipe(Effect.as(false)),
636 ),
637 Effect.catchTag("BadRequest", (error) =>
638 recoverIfPresent(
639 env.project,
640 instance,
641 userName,
642 host,
643 error,
644 ).pipe(Effect.as(false)),
645 ),
646 Effect.retry({
647 while: (error) => error._tag === "Conflict",
648 times: 8,
649 schedule: Schedule.spaced("5 seconds"),
650 }),
651 );
652 current = yield* waitUntilExists(env.project, instance, userName, host);
653 inserted = created;
654 }
655
656 const passwordChanged =
657 news.password !== undefined &&
658 !inserted &&
659 !isIamType(news.type ?? current.type) &&
660 (olds?.password === undefined ||
661 passwordValue(olds.password) !== passwordValue(news.password));
662 const rolesChanged =
663 news.databaseRoles !== undefined &&
664 rolesKey(current.databaseRoles) !== rolesKey(news.databaseRoles);
665 const policyChanged =
666 news.passwordPolicy !== undefined &&
667 policyKey(current.passwordPolicy) !== policyKey(news.passwordPolicy);
668 const dualPasswordChanged =
669 news.dualPasswordType !== undefined &&
670 dualPasswordOf(current.dualPasswordType) !==
671 dualPasswordOf(news.dualPasswordType);
672 const sqlServerChanged =
673 news.sqlserverUserDetails !== undefined &&
674 sqlServerKey(current.sqlserverUserDetails) !==
675 sqlServerKey(news.sqlserverUserDetails);
676 const iamEmailChanged =
677 news.iamEmail !== undefined &&
678 (current.iamEmail ?? "") !== news.iamEmail;
679
680 if (
681 passwordChanged ||
682 rolesChanged ||
683 policyChanged ||
684 dualPasswordChanged ||
685 sqlServerChanged ||
686 iamEmailChanged
687 ) {
688 const patched = yield* sqladmin.updateUsers({
689 project: env.project,
690 instance,
691 name: userName,
692 ...(normalizeHost(host) ? { host: normalizeHost(host) } : {}),
693 revokeExistingRoles: rolesChanged ? true : undefined,
694 body: toBody(news, userName, instance, env.project, current, {
695 includePassword: passwordChanged,
696 }),
697 });
698 yield* waitForOperation(env.project, patched);
699 current = yield* waitUntilExists(env.project, instance, userName, host);
700 }
701
702 return toAttrs(current, env.project, instance);
703 }),
704
705 delete: Effect.fn(function* ({ olds, output }) {
706 if (olds.deletionPolicy === "ABANDON") return;
707 const project = output.project;
708 const instance = instanceIdOf(output.instance);
709 const userName = output.userName;
710 const host = output.host;
711 yield* sqladmin
712 .deleteUsers({
713 project,
714 instance,
715 name: userName,
716 ...(normalizeHost(host) ? { host: normalizeHost(host) } : {}),
717 })
718 .pipe(
719 Effect.flatMap((operation) =>
720 waitForOperation(project, operation, { notFoundOk: true }),
721 ),
722 Effect.catchTag("NotFound", () => Effect.void),
723 recoverIfInstanceMissing(project, instance, () => undefined),
724 Effect.retry({
725 while: (error) => error._tag === "Conflict",
726 times: 8,
727 schedule: Schedule.spaced("5 seconds"),
728 }),
729 );
730 yield* waitUntilGone(project, instance, userName, host);
731 }),
732 });