filter out random request ID value

feat(secret-rotation): MySQL Secret Rotation v2

backend/src/@types/fastify.d.ts

@@ -83,6 +83,7 @@ import { TOrgAdminServiceFactory } from "@app/services/org-admin/org-admin-servi
 import { TPkiAlertServiceFactory } from "@app/services/pki-alert/pki-alert-service";
 import { TPkiCollectionServiceFactory } from "@app/services/pki-collection/pki-collection-service";
 import { TPkiSubscriberServiceFactory } from "@app/services/pki-subscriber/pki-subscriber-service";
+import { TPkiTemplatesServiceFactory } from "@app/services/pki-templates/pki-templates-service";
 import { TProjectServiceFactory } from "@app/services/project/project-service";
 import { TProjectBotServiceFactory } from "@app/services/project-bot/project-bot-service";
 import { TProjectEnvServiceFactory } from "@app/services/project-env/project-env-service";
@@ -271,6 +272,7 @@ declare module "fastify" {
       assumePrivileges: TAssumePrivilegeServiceFactory;
       githubOrgSync: TGithubOrgSyncServiceFactory;
       internalCertificateAuthority: TInternalCertificateAuthorityServiceFactory;
+      pkiTemplate: TPkiTemplatesServiceFactory;
     };
     // this is exclusive use for middlewares in which we need to inject data
     // everywhere else access using service layer

backend/src/@types/knex.d.ts

@@ -6,6 +6,9 @@ import {
   TAccessApprovalPoliciesApprovers,
   TAccessApprovalPoliciesApproversInsert,
   TAccessApprovalPoliciesApproversUpdate,
+  TAccessApprovalPoliciesBypassers,
+  TAccessApprovalPoliciesBypassersInsert,
+  TAccessApprovalPoliciesBypassersUpdate,
   TAccessApprovalPoliciesInsert,
   TAccessApprovalPoliciesUpdate,
   TAccessApprovalRequests,
@@ -276,6 +279,9 @@ import {
   TSecretApprovalPoliciesApprovers,
   TSecretApprovalPoliciesApproversInsert,
   TSecretApprovalPoliciesApproversUpdate,
+  TSecretApprovalPoliciesBypassers,
+  TSecretApprovalPoliciesBypassersInsert,
+  TSecretApprovalPoliciesBypassersUpdate,
   TSecretApprovalPoliciesInsert,
   TSecretApprovalPoliciesUpdate,
   TSecretApprovalRequests,
@@ -820,6 +826,12 @@ declare module "knex/types/tables" {
       TAccessApprovalPoliciesApproversUpdate
     >;
 
+    [TableName.AccessApprovalPolicyBypasser]: KnexOriginal.CompositeTableType<
+      TAccessApprovalPoliciesBypassers,
+      TAccessApprovalPoliciesBypassersInsert,
+      TAccessApprovalPoliciesBypassersUpdate
+    >;
+
     [TableName.AccessApprovalRequest]: KnexOriginal.CompositeTableType<
       TAccessApprovalRequests,
       TAccessApprovalRequestsInsert,
@@ -843,6 +855,11 @@ declare module "knex/types/tables" {
       TSecretApprovalPoliciesApproversInsert,
       TSecretApprovalPoliciesApproversUpdate
     >;
+    [TableName.SecretApprovalPolicyBypasser]: KnexOriginal.CompositeTableType<
+      TSecretApprovalPoliciesBypassers,
+      TSecretApprovalPoliciesBypassersInsert,
+      TSecretApprovalPoliciesBypassersUpdate
+    >;
     [TableName.SecretApprovalRequest]: KnexOriginal.CompositeTableType<
       TSecretApprovalRequests,
       TSecretApprovalRequestsInsert,

backend/src/db/migrations/20250527030702_policy-bypassers.ts

@@ -0,0 +1,48 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+import { createOnUpdateTrigger, dropOnUpdateTrigger } from "../utils";
+
+export async function up(knex: Knex): Promise<void> {
+  if (!(await knex.schema.hasTable(TableName.AccessApprovalPolicyBypasser))) {
+    await knex.schema.createTable(TableName.AccessApprovalPolicyBypasser, (t) => {
+      t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+
+      t.uuid("bypasserGroupId").nullable();
+      t.foreign("bypasserGroupId").references("id").inTable(TableName.Groups).onDelete("CASCADE");
+
+      t.uuid("bypasserUserId").nullable();
+      t.foreign("bypasserUserId").references("id").inTable(TableName.Users).onDelete("CASCADE");
+
+      t.uuid("policyId").notNullable();
+      t.foreign("policyId").references("id").inTable(TableName.AccessApprovalPolicy).onDelete("CASCADE");
+      t.timestamps(true, true, true);
+    });
+    await createOnUpdateTrigger(knex, TableName.AccessApprovalPolicyBypasser);
+  }
+
+  if (!(await knex.schema.hasTable(TableName.SecretApprovalPolicyBypasser))) {
+    await knex.schema.createTable(TableName.SecretApprovalPolicyBypasser, (t) => {
+      t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+
+      t.uuid("bypasserGroupId").nullable();
+      t.foreign("bypasserGroupId").references("id").inTable(TableName.Groups).onDelete("CASCADE");
+
+      t.uuid("bypasserUserId").nullable();
+      t.foreign("bypasserUserId").references("id").inTable(TableName.Users).onDelete("CASCADE");
+
+      t.uuid("policyId").notNullable();
+      t.foreign("policyId").references("id").inTable(TableName.SecretApprovalPolicy).onDelete("CASCADE");
+      t.timestamps(true, true, true);
+    });
+    await createOnUpdateTrigger(knex, TableName.SecretApprovalPolicyBypasser);
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  await knex.schema.dropTableIfExists(TableName.SecretApprovalPolicyBypasser);
+  await knex.schema.dropTableIfExists(TableName.AccessApprovalPolicyBypasser);
+
+  await dropOnUpdateTrigger(knex, TableName.SecretApprovalPolicyBypasser);
+  await dropOnUpdateTrigger(knex, TableName.AccessApprovalPolicyBypasser);
+}

backend/src/db/migrations/20250527164523_add-mi-access-token-period.ts

@@ -0,0 +1,139 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  if (!(await knex.schema.hasColumn(TableName.IdentityAccessToken, "accessTokenPeriod"))) {
+    await knex.schema.alterTable(TableName.IdentityAccessToken, (t) => {
+      t.bigInteger("accessTokenPeriod").defaultTo(0).notNullable();
+    });
+  }
+
+  if (!(await knex.schema.hasColumn(TableName.IdentityUniversalAuth, "accessTokenPeriod"))) {
+    await knex.schema.alterTable(TableName.IdentityUniversalAuth, (t) => {
+      t.bigInteger("accessTokenPeriod").defaultTo(0).notNullable();
+    });
+  }
+
+  if (!(await knex.schema.hasColumn(TableName.IdentityAwsAuth, "accessTokenPeriod"))) {
+    await knex.schema.alterTable(TableName.IdentityAwsAuth, (t) => {
+      t.bigInteger("accessTokenPeriod").defaultTo(0).notNullable();
+    });
+  }
+
+  if (!(await knex.schema.hasColumn(TableName.IdentityOidcAuth, "accessTokenPeriod"))) {
+    await knex.schema.alterTable(TableName.IdentityOidcAuth, (t) => {
+      t.bigInteger("accessTokenPeriod").defaultTo(0).notNullable();
+    });
+  }
+
+  if (!(await knex.schema.hasColumn(TableName.IdentityAzureAuth, "accessTokenPeriod"))) {
+    await knex.schema.alterTable(TableName.IdentityAzureAuth, (t) => {
+      t.bigInteger("accessTokenPeriod").defaultTo(0).notNullable();
+    });
+  }
+
+  if (!(await knex.schema.hasColumn(TableName.IdentityGcpAuth, "accessTokenPeriod"))) {
+    await knex.schema.alterTable(TableName.IdentityGcpAuth, (t) => {
+      t.bigInteger("accessTokenPeriod").defaultTo(0).notNullable();
+    });
+  }
+
+  if (!(await knex.schema.hasColumn(TableName.IdentityJwtAuth, "accessTokenPeriod"))) {
+    await knex.schema.alterTable(TableName.IdentityJwtAuth, (t) => {
+      t.bigInteger("accessTokenPeriod").defaultTo(0).notNullable();
+    });
+  }
+
+  if (!(await knex.schema.hasColumn(TableName.IdentityKubernetesAuth, "accessTokenPeriod"))) {
+    await knex.schema.alterTable(TableName.IdentityKubernetesAuth, (t) => {
+      t.bigInteger("accessTokenPeriod").defaultTo(0).notNullable();
+    });
+  }
+
+  if (!(await knex.schema.hasColumn(TableName.IdentityLdapAuth, "accessTokenPeriod"))) {
+    await knex.schema.alterTable(TableName.IdentityLdapAuth, (t) => {
+      t.bigInteger("accessTokenPeriod").defaultTo(0).notNullable();
+    });
+  }
+
+  if (!(await knex.schema.hasColumn(TableName.IdentityOciAuth, "accessTokenPeriod"))) {
+    await knex.schema.alterTable(TableName.IdentityOciAuth, (t) => {
+      t.bigInteger("accessTokenPeriod").defaultTo(0).notNullable();
+    });
+  }
+
+  if (!(await knex.schema.hasColumn(TableName.IdentityTokenAuth, "accessTokenPeriod"))) {
+    await knex.schema.alterTable(TableName.IdentityTokenAuth, (t) => {
+      t.bigInteger("accessTokenPeriod").defaultTo(0).notNullable();
+    });
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  if (await knex.schema.hasColumn(TableName.IdentityAccessToken, "accessTokenPeriod")) {
+    await knex.schema.alterTable(TableName.IdentityAccessToken, (t) => {
+      t.dropColumn("accessTokenPeriod");
+    });
+  }
+
+  if (await knex.schema.hasColumn(TableName.IdentityUniversalAuth, "accessTokenPeriod")) {
+    await knex.schema.alterTable(TableName.IdentityUniversalAuth, (t) => {
+      t.dropColumn("accessTokenPeriod");
+    });
+  }
+
+  if (await knex.schema.hasColumn(TableName.IdentityAwsAuth, "accessTokenPeriod")) {
+    await knex.schema.alterTable(TableName.IdentityAwsAuth, (t) => {
+      t.dropColumn("accessTokenPeriod");
+    });
+  }
+
+  if (await knex.schema.hasColumn(TableName.IdentityOidcAuth, "accessTokenPeriod")) {
+    await knex.schema.alterTable(TableName.IdentityOidcAuth, (t) => {
+      t.dropColumn("accessTokenPeriod");
+    });
+  }
+
+  if (await knex.schema.hasColumn(TableName.IdentityAzureAuth, "accessTokenPeriod")) {
+    await knex.schema.alterTable(TableName.IdentityAzureAuth, (t) => {
+      t.dropColumn("accessTokenPeriod");
+    });
+  }
+
+  if (await knex.schema.hasColumn(TableName.IdentityGcpAuth, "accessTokenPeriod")) {
+    await knex.schema.alterTable(TableName.IdentityGcpAuth, (t) => {
+      t.dropColumn("accessTokenPeriod");
+    });
+  }
+
+  if (await knex.schema.hasColumn(TableName.IdentityJwtAuth, "accessTokenPeriod")) {
+    await knex.schema.alterTable(TableName.IdentityJwtAuth, (t) => {
+      t.dropColumn("accessTokenPeriod");
+    });
+  }
+
+  if (await knex.schema.hasColumn(TableName.IdentityKubernetesAuth, "accessTokenPeriod")) {
+    await knex.schema.alterTable(TableName.IdentityKubernetesAuth, (t) => {
+      t.dropColumn("accessTokenPeriod");
+    });
+  }
+
+  if (await knex.schema.hasColumn(TableName.IdentityLdapAuth, "accessTokenPeriod")) {
+    await knex.schema.alterTable(TableName.IdentityLdapAuth, (t) => {
+      t.dropColumn("accessTokenPeriod");
+    });
+  }
+
+  if (await knex.schema.hasColumn(TableName.IdentityOciAuth, "accessTokenPeriod")) {
+    await knex.schema.alterTable(TableName.IdentityOciAuth, (t) => {
+      t.dropColumn("accessTokenPeriod");
+    });
+  }
+
+  if (await knex.schema.hasColumn(TableName.IdentityTokenAuth, "accessTokenPeriod")) {
+    await knex.schema.alterTable(TableName.IdentityTokenAuth, (t) => {
+      t.dropColumn("accessTokenPeriod");
+    });
+  }
+}

backend/src/db/migrations/20250528145356_add-template-slug.ts

@@ -0,0 +1,24 @@
+import slugify from "@sindresorhus/slugify";
+import { Knex } from "knex";
+
+import { alphaNumericNanoId } from "@app/lib/nanoid";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  const hasNameCol = await knex.schema.hasColumn(TableName.CertificateTemplate, "name");
+  if (hasNameCol) {
+    const templates = await knex(TableName.CertificateTemplate).select("id", "name");
+    await Promise.all(
+      templates.map((el) => {
+        const slugifiedName = el.name
+          ? slugify(`${el.name.slice(0, 16)}-${alphaNumericNanoId(8)}`)
+          : slugify(alphaNumericNanoId(12));
+
+        return knex(TableName.CertificateTemplate).where({ id: el.id }).update({ name: slugifiedName });
+      })
+    );
+  }
+}
+
+export async function down(): Promise<void> {}

backend/src/db/migrations/20250528183744_remove-encrypted-salt-from-shared-secret.ts

@@ -0,0 +1,27 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  if (await knex.schema.hasTable(TableName.SecretSharing)) {
+    const hasEncryptedSalt = await knex.schema.hasColumn(TableName.SecretSharing, "encryptedSalt");
+
+    if (hasEncryptedSalt) {
+      await knex.schema.alterTable(TableName.SecretSharing, (t) => {
+        t.dropColumn("encryptedSalt");
+      });
+    }
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  if (await knex.schema.hasTable(TableName.SecretSharing)) {
+    const hasEncryptedSalt = await knex.schema.hasColumn(TableName.SecretSharing, "encryptedSalt");
+
+    if (!hasEncryptedSalt) {
+      await knex.schema.alterTable(TableName.SecretSharing, (t) => {
+        t.binary("encryptedSalt").nullable();
+      });
+    }
+  }
+}

backend/src/db/migrations/20250530152721_add-access-approval-request-deleted-at.ts

@@ -0,0 +1,63 @@
+import { Knex } from "knex";
+
+import { ApprovalStatus } from "@app/ee/services/secret-approval-request/secret-approval-request-types";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  const hasPrivilegeDeletedAtColumn = await knex.schema.hasColumn(
+    TableName.AccessApprovalRequest,
+    "privilegeDeletedAt"
+  );
+  const hasStatusColumn = await knex.schema.hasColumn(TableName.AccessApprovalRequest, "status");
+
+  if (!hasPrivilegeDeletedAtColumn) {
+    await knex.schema.alterTable(TableName.AccessApprovalRequest, (t) => {
+      t.timestamp("privilegeDeletedAt").nullable();
+    });
+  }
+
+  if (!hasStatusColumn) {
+    await knex.schema.alterTable(TableName.AccessApprovalRequest, (t) => {
+      t.string("status").defaultTo(ApprovalStatus.PENDING).notNullable();
+    });
+
+    // Update existing rows based on business logic
+    // If privilegeId is not null, set status to "approved"
+    await knex(TableName.AccessApprovalRequest).whereNotNull("privilegeId").update({ status: ApprovalStatus.APPROVED });
+
+    // If privilegeId is null and there's a rejected reviewer, set to "rejected"
+    const rejectedRequestIds = await knex(TableName.AccessApprovalRequestReviewer)
+      .select("requestId")
+      .where("status", "rejected")
+      .distinct()
+      .pluck("requestId");
+
+    if (rejectedRequestIds.length > 0) {
+      await knex(TableName.AccessApprovalRequest)
+        .whereNull("privilegeId")
+        .whereIn("id", rejectedRequestIds)
+        .update({ status: ApprovalStatus.REJECTED });
+    }
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  const hasPrivilegeDeletedAtColumn = await knex.schema.hasColumn(
+    TableName.AccessApprovalRequest,
+    "privilegeDeletedAt"
+  );
+  const hasStatusColumn = await knex.schema.hasColumn(TableName.AccessApprovalRequest, "status");
+
+  if (hasPrivilegeDeletedAtColumn) {
+    await knex.schema.alterTable(TableName.AccessApprovalRequest, (t) => {
+      t.dropColumn("privilegeDeletedAt");
+    });
+  }
+
+  if (hasStatusColumn) {
+    await knex.schema.alterTable(TableName.AccessApprovalRequest, (t) => {
+      t.dropColumn("status");
+    });
+  }
+}

backend/src/db/schemas/access-approval-policies-bypassers.ts

@@ -0,0 +1,26 @@
+// Code generated by automation script, DO NOT EDIT.
+// Automated by pulling database and generating zod schema
+// To update. Just run npm run generate:schema
+// Written by akhilmhdh.
+
+import { z } from "zod";
+
+import { TImmutableDBKeys } from "./models";
+
+export const AccessApprovalPoliciesBypassersSchema = z.object({
+  id: z.string().uuid(),
+  bypasserGroupId: z.string().uuid().nullable().optional(),
+  bypasserUserId: z.string().uuid().nullable().optional(),
+  policyId: z.string().uuid(),
+  createdAt: z.date(),
+  updatedAt: z.date()
+});
+
+export type TAccessApprovalPoliciesBypassers = z.infer<typeof AccessApprovalPoliciesBypassersSchema>;
+export type TAccessApprovalPoliciesBypassersInsert = Omit<
+  z.input<typeof AccessApprovalPoliciesBypassersSchema>,
+  TImmutableDBKeys
+>;
+export type TAccessApprovalPoliciesBypassersUpdate = Partial<
+  Omit<z.input<typeof AccessApprovalPoliciesBypassersSchema>, TImmutableDBKeys>
+>;

backend/src/db/schemas/access-approval-requests.ts

@@ -18,7 +18,9 @@ export const AccessApprovalRequestsSchema = z.object({
   createdAt: z.date(),
   updatedAt: z.date(),
   requestedByUserId: z.string().uuid(),
-  note: z.string().nullable().optional()
+  note: z.string().nullable().optional(),
+  privilegeDeletedAt: z.date().nullable().optional(),
+  status: z.string().default("pending")
 });
 
 export type TAccessApprovalRequests = z.infer<typeof AccessApprovalRequestsSchema>;

backend/src/db/schemas/identity-access-tokens.ts

@@ -21,7 +21,8 @@ export const IdentityAccessTokensSchema = z.object({
   createdAt: z.date(),
   updatedAt: z.date(),
   name: z.string().nullable().optional(),
-  authMethod: z.string()
+  authMethod: z.string(),
+  accessTokenPeriod: z.coerce.number().default(0)
 });
 
 export type TIdentityAccessTokens = z.infer<typeof IdentityAccessTokensSchema>;

backend/src/db/schemas/identity-aws-auths.ts

@@ -19,7 +19,8 @@ export const IdentityAwsAuthsSchema = z.object({
   type: z.string(),
   stsEndpoint: z.string(),
   allowedPrincipalArns: z.string(),
-  allowedAccountIds: z.string()
+  allowedAccountIds: z.string(),
+  accessTokenPeriod: z.coerce.number().default(0)
 });
 
 export type TIdentityAwsAuths = z.infer<typeof IdentityAwsAuthsSchema>;

backend/src/db/schemas/identity-azure-auths.ts

@@ -18,7 +18,8 @@ export const IdentityAzureAuthsSchema = z.object({
   identityId: z.string().uuid(),
   tenantId: z.string(),
   resource: z.string(),
-  allowedServicePrincipalIds: z.string()
+  allowedServicePrincipalIds: z.string(),
+  accessTokenPeriod: z.coerce.number().default(0)
 });
 
 export type TIdentityAzureAuths = z.infer<typeof IdentityAzureAuthsSchema>;

backend/src/db/schemas/identity-gcp-auths.ts

@@ -19,7 +19,8 @@ export const IdentityGcpAuthsSchema = z.object({
   type: z.string(),
   allowedServiceAccounts: z.string().nullable().optional(),
   allowedProjects: z.string().nullable().optional(),
-  allowedZones: z.string().nullable().optional()
+  allowedZones: z.string().nullable().optional(),
+  accessTokenPeriod: z.coerce.number().default(0)
 });
 
 export type TIdentityGcpAuths = z.infer<typeof IdentityGcpAuthsSchema>;

backend/src/db/schemas/identity-jwt-auths.ts

@@ -25,7 +25,8 @@ export const IdentityJwtAuthsSchema = z.object({
   boundClaims: z.unknown(),
   boundSubject: z.string(),
   createdAt: z.date(),
-  updatedAt: z.date()
+  updatedAt: z.date(),
+  accessTokenPeriod: z.coerce.number().default(0)
 });
 
 export type TIdentityJwtAuths = z.infer<typeof IdentityJwtAuthsSchema>;

backend/src/db/schemas/identity-kubernetes-auths.ts

@@ -30,7 +30,8 @@ export const IdentityKubernetesAuthsSchema = z.object({
   allowedAudience: z.string(),
   encryptedKubernetesTokenReviewerJwt: zodBuffer.nullable().optional(),
   encryptedKubernetesCaCertificate: zodBuffer.nullable().optional(),
-  gatewayId: z.string().uuid().nullable().optional()
+  gatewayId: z.string().uuid().nullable().optional(),
+  accessTokenPeriod: z.coerce.number().default(0)
 });
 
 export type TIdentityKubernetesAuths = z.infer<typeof IdentityKubernetesAuthsSchema>;

backend/src/db/schemas/identity-ldap-auths.ts

@@ -24,7 +24,8 @@ export const IdentityLdapAuthsSchema = z.object({
   searchFilter: z.string(),
   allowedFields: z.unknown().nullable().optional(),
   createdAt: z.date(),
-  updatedAt: z.date()
+  updatedAt: z.date(),
+  accessTokenPeriod: z.coerce.number().default(0)
 });
 
 export type TIdentityLdapAuths = z.infer<typeof IdentityLdapAuthsSchema>;

backend/src/db/schemas/identity-oci-auths.ts

@@ -18,7 +18,8 @@ export const IdentityOciAuthsSchema = z.object({
   identityId: z.string().uuid(),
   type: z.string(),
   tenancyOcid: z.string(),
-  allowedUsernames: z.string().nullable().optional()
+  allowedUsernames: z.string().nullable().optional(),
+  accessTokenPeriod: z.coerce.number().default(0)
 });
 
 export type TIdentityOciAuths = z.infer<typeof IdentityOciAuthsSchema>;

backend/src/db/schemas/identity-oidc-auths.ts

@@ -27,7 +27,8 @@ export const IdentityOidcAuthsSchema = z.object({
   createdAt: z.date(),
   updatedAt: z.date(),
   encryptedCaCertificate: zodBuffer.nullable().optional(),
-  claimMetadataMapping: z.unknown().nullable().optional()
+  claimMetadataMapping: z.unknown().nullable().optional(),
+  accessTokenPeriod: z.coerce.number().default(0)
 });
 
 export type TIdentityOidcAuths = z.infer<typeof IdentityOidcAuthsSchema>;

backend/src/db/schemas/identity-token-auths.ts

@@ -15,7 +15,8 @@ export const IdentityTokenAuthsSchema = z.object({
   accessTokenTrustedIps: z.unknown(),
   createdAt: z.date(),
   updatedAt: z.date(),
-  identityId: z.string().uuid()
+  identityId: z.string().uuid(),
+  accessTokenPeriod: z.coerce.number().default(0)
 });
 
 export type TIdentityTokenAuths = z.infer<typeof IdentityTokenAuthsSchema>;

backend/src/db/schemas/identity-universal-auths.ts

@@ -17,7 +17,8 @@ export const IdentityUniversalAuthsSchema = z.object({
   accessTokenTrustedIps: z.unknown(),
   createdAt: z.date(),
   updatedAt: z.date(),
-  identityId: z.string().uuid()
+  identityId: z.string().uuid(),
+  accessTokenPeriod: z.coerce.number().default(0)
 });
 
 export type TIdentityUniversalAuths = z.infer<typeof IdentityUniversalAuthsSchema>;

backend/src/db/schemas/index.ts

@@ -1,5 +1,6 @@
 export * from "./access-approval-policies";
 export * from "./access-approval-policies-approvers";
+export * from "./access-approval-policies-bypassers";
 export * from "./access-approval-requests";
 export * from "./access-approval-requests-reviewers";
 export * from "./api-keys";
@@ -92,6 +93,7 @@ export * from "./saml-configs";
 export * from "./scim-tokens";
 export * from "./secret-approval-policies";
 export * from "./secret-approval-policies-approvers";
+export * from "./secret-approval-policies-bypassers";
 export * from "./secret-approval-request-secret-tags";
 export * from "./secret-approval-request-secret-tags-v2";
 export * from "./secret-approval-requests";

backend/src/db/schemas/models.ts

@@ -95,10 +95,12 @@ export enum TableName {
   ScimToken = "scim_tokens",
   AccessApprovalPolicy = "access_approval_policies",
   AccessApprovalPolicyApprover = "access_approval_policies_approvers",
+  AccessApprovalPolicyBypasser = "access_approval_policies_bypassers",
   AccessApprovalRequest = "access_approval_requests",
   AccessApprovalRequestReviewer = "access_approval_requests_reviewers",
   SecretApprovalPolicy = "secret_approval_policies",
   SecretApprovalPolicyApprover = "secret_approval_policies_approvers",
+  SecretApprovalPolicyBypasser = "secret_approval_policies_bypassers",
   SecretApprovalRequest = "secret_approval_requests",
   SecretApprovalRequestReviewer = "secret_approval_requests_reviewers",
   SecretApprovalRequestSecret = "secret_approval_requests_secrets",

backend/src/db/schemas/secret-approval-policies-bypassers.ts

@@ -0,0 +1,26 @@
+// Code generated by automation script, DO NOT EDIT.
+// Automated by pulling database and generating zod schema
+// To update. Just run npm run generate:schema
+// Written by akhilmhdh.
+
+import { z } from "zod";
+
+import { TImmutableDBKeys } from "./models";
+
+export const SecretApprovalPoliciesBypassersSchema = z.object({
+  id: z.string().uuid(),
+  bypasserGroupId: z.string().uuid().nullable().optional(),
+  bypasserUserId: z.string().uuid().nullable().optional(),
+  policyId: z.string().uuid(),
+  createdAt: z.date(),
+  updatedAt: z.date()
+});
+
+export type TSecretApprovalPoliciesBypassers = z.infer<typeof SecretApprovalPoliciesBypassersSchema>;
+export type TSecretApprovalPoliciesBypassersInsert = Omit<
+  z.input<typeof SecretApprovalPoliciesBypassersSchema>,
+  TImmutableDBKeys
+>;
+export type TSecretApprovalPoliciesBypassersUpdate = Partial<
+  Omit<z.input<typeof SecretApprovalPoliciesBypassersSchema>, TImmutableDBKeys>
+>;

backend/src/db/schemas/secret-sharing.ts

@@ -28,7 +28,6 @@ export const SecretSharingSchema = z.object({
   encryptedSecret: zodBuffer.nullable().optional(),
   identifier: z.string().nullable().optional(),
   type: z.string().default("share"),
-  encryptedSalt: zodBuffer.nullable().optional(),
   authorizedEmails: z.unknown().nullable().optional()
 });
 

backend/src/ee/routes/v1/access-approval-policy-router.ts

@@ -1,7 +1,7 @@
 import { nanoid } from "nanoid";
 import { z } from "zod";
 
-import { ApproverType } from "@app/ee/services/access-approval-policy/access-approval-policy-types";
+import { ApproverType, BypasserType } from "@app/ee/services/access-approval-policy/access-approval-policy-types";
 import { EnforcementLevel } from "@app/lib/types";
 import { readLimit, writeLimit } from "@app/server/config/rateLimiter";
 import { verifyAuth } from "@app/server/plugins/auth/verify-auth";
@@ -24,10 +24,19 @@ export const registerAccessApprovalPolicyRouter = async (server: FastifyZodProvi
         approvers: z
           .discriminatedUnion("type", [
             z.object({ type: z.literal(ApproverType.Group), id: z.string() }),
-            z.object({ type: z.literal(ApproverType.User), id: z.string().optional(), name: z.string().optional() })
+            z.object({ type: z.literal(ApproverType.User), id: z.string().optional(), username: z.string().optional() })
           ])
           .array()
+          .max(100, "Cannot have more than 100 approvers")
           .min(1, { message: "At least one approver should be provided" }),
+        bypassers: z
+          .discriminatedUnion("type", [
+            z.object({ type: z.literal(BypasserType.Group), id: z.string() }),
+            z.object({ type: z.literal(BypasserType.User), id: z.string().optional(), username: z.string().optional() })
+          ])
+          .array()
+          .max(100, "Cannot have more than 100 bypassers")
+          .optional(),
         approvals: z.number().min(1).default(1),
         enforcementLevel: z.nativeEnum(EnforcementLevel).default(EnforcementLevel.Hard),
         allowedSelfApprovals: z.boolean().default(true)
@@ -72,7 +81,8 @@ export const registerAccessApprovalPolicyRouter = async (server: FastifyZodProvi
                 .object({ type: z.nativeEnum(ApproverType), id: z.string().nullable().optional() })
                 .array()
                 .nullable()
-                .optional()
+                .optional(),
+              bypassers: z.object({ type: z.nativeEnum(BypasserType), id: z.string().nullable().optional() }).array()
             })
             .array()
             .nullable()
@@ -143,10 +153,19 @@ export const registerAccessApprovalPolicyRouter = async (server: FastifyZodProvi
         approvers: z
           .discriminatedUnion("type", [
             z.object({ type: z.literal(ApproverType.Group), id: z.string() }),
-            z.object({ type: z.literal(ApproverType.User), id: z.string().optional(), name: z.string().optional() })
+            z.object({ type: z.literal(ApproverType.User), id: z.string().optional(), username: z.string().optional() })
           ])
           .array()
-          .min(1, { message: "At least one approver should be provided" }),
+          .min(1, { message: "At least one approver should be provided" })
+          .max(100, "Cannot have more than 100 approvers"),
+        bypassers: z
+          .discriminatedUnion("type", [
+            z.object({ type: z.literal(BypasserType.Group), id: z.string() }),
+            z.object({ type: z.literal(BypasserType.User), id: z.string().optional(), username: z.string().optional() })
+          ])
+          .array()
+          .max(100, "Cannot have more than 100 bypassers")
+          .optional(),
         approvals: z.number().min(1).optional(),
         enforcementLevel: z.nativeEnum(EnforcementLevel).default(EnforcementLevel.Hard),
         allowedSelfApprovals: z.boolean().default(true)
@@ -220,6 +239,15 @@ export const registerAccessApprovalPolicyRouter = async (server: FastifyZodProvi
               })
               .array()
               .nullable()
+              .optional(),
+            bypassers: z
+              .object({
+                type: z.nativeEnum(BypasserType),
+                id: z.string().nullable().optional(),
+                name: z.string().nullable().optional()
+              })
+              .array()
+              .nullable()
               .optional()
           })
         })

backend/src/ee/routes/v1/access-approval-request-router.ts

@@ -113,6 +113,7 @@ export const registerAccessApprovalRequestRouter = async (server: FastifyZodProv
               name: z.string(),
               approvals: z.number(),
               approvers: z.string().array(),
+              bypassers: z.string().array(),
               secretPath: z.string().nullish(),
               envId: z.string(),
               enforcementLevel: z.string(),

backend/src/ee/routes/v1/secret-approval-policy-router.ts

@@ -1,7 +1,7 @@
 import { nanoid } from "nanoid";
 import { z } from "zod";
 
-import { ApproverType } from "@app/ee/services/access-approval-policy/access-approval-policy-types";
+import { ApproverType, BypasserType } from "@app/ee/services/access-approval-policy/access-approval-policy-types";
 import { removeTrailingSlash } from "@app/lib/fn";
 import { EnforcementLevel } from "@app/lib/types";
 import { readLimit, writeLimit } from "@app/server/config/rateLimiter";
@@ -30,10 +30,19 @@ export const registerSecretApprovalPolicyRouter = async (server: FastifyZodProvi
         approvers: z
           .discriminatedUnion("type", [
             z.object({ type: z.literal(ApproverType.Group), id: z.string() }),
-            z.object({ type: z.literal(ApproverType.User), id: z.string().optional(), name: z.string().optional() })
+            z.object({ type: z.literal(ApproverType.User), id: z.string().optional(), username: z.string().optional() })
           ])
           .array()
-          .min(1, { message: "At least one approver should be provided" }),
+          .min(1, { message: "At least one approver should be provided" })
+          .max(100, "Cannot have more than 100 approvers"),
+        bypassers: z
+          .discriminatedUnion("type", [
+            z.object({ type: z.literal(BypasserType.Group), id: z.string() }),
+            z.object({ type: z.literal(BypasserType.User), id: z.string().optional(), username: z.string().optional() })
+          ])
+          .array()
+          .max(100, "Cannot have more than 100 bypassers")
+          .optional(),
         approvals: z.number().min(1).default(1),
         enforcementLevel: z.nativeEnum(EnforcementLevel).default(EnforcementLevel.Hard),
         allowedSelfApprovals: z.boolean().default(true)
@@ -75,10 +84,19 @@ export const registerSecretApprovalPolicyRouter = async (server: FastifyZodProvi
         approvers: z
           .discriminatedUnion("type", [
             z.object({ type: z.literal(ApproverType.Group), id: z.string() }),
-            z.object({ type: z.literal(ApproverType.User), id: z.string().optional(), name: z.string().optional() })
+            z.object({ type: z.literal(ApproverType.User), id: z.string().optional(), username: z.string().optional() })
           ])
           .array()
-          .min(1, { message: "At least one approver should be provided" }),
+          .min(1, { message: "At least one approver should be provided" })
+          .max(100, "Cannot have more than 100 approvers"),
+        bypassers: z
+          .discriminatedUnion("type", [
+            z.object({ type: z.literal(BypasserType.Group), id: z.string() }),
+            z.object({ type: z.literal(BypasserType.User), id: z.string().optional(), username: z.string().optional() })
+          ])
+          .array()
+          .max(100, "Cannot have more than 100 bypassers")
+          .optional(),
         approvals: z.number().min(1).default(1),
         secretPath: z
           .string()
@@ -157,6 +175,12 @@ export const registerSecretApprovalPolicyRouter = async (server: FastifyZodProvi
                   id: z.string().nullable().optional(),
                   type: z.nativeEnum(ApproverType)
                 })
+                .array(),
+              bypassers: z
+                .object({
+                  id: z.string().nullable().optional(),
+                  type: z.nativeEnum(BypasserType)
+                })
                 .array()
             })
             .array()
@@ -193,7 +217,14 @@ export const registerSecretApprovalPolicyRouter = async (server: FastifyZodProvi
               .object({
                 id: z.string().nullable().optional(),
                 type: z.nativeEnum(ApproverType),
-                name: z.string().nullable().optional()
+                username: z.string().nullable().optional()
+              })
+              .array(),
+            bypassers: z
+              .object({
+                id: z.string().nullable().optional(),
+                type: z.nativeEnum(BypasserType),
+                username: z.string().nullable().optional()
               })
               .array()
           })

backend/src/ee/routes/v1/secret-approval-request-router.ts

@@ -47,6 +47,11 @@ export const registerSecretApprovalRequestRouter = async (server: FastifyZodProv
                   userId: z.string().nullable().optional()
                 })
                 .array(),
+              bypassers: z
+                .object({
+                  userId: z.string().nullable().optional()
+                })
+                .array(),
               secretPath: z.string().optional().nullable(),
               enforcementLevel: z.string(),
               deletedAt: z.date().nullish(),
@@ -266,6 +271,7 @@ export const registerSecretApprovalRequestRouter = async (server: FastifyZodProv
                 name: z.string(),
                 approvals: z.number(),
                 approvers: approvalRequestUser.array(),
+                bypassers: approvalRequestUser.array(),
                 secretPath: z.string().optional().nullable(),
                 enforcementLevel: z.string(),
                 deletedAt: z.date().nullish(),

backend/src/ee/routes/v2/secret-rotation-v2-routers/index.ts

@@ -5,6 +5,7 @@ import { registerAwsIamUserSecretRotationRouter } from "./aws-iam-user-secret-ro
 import { registerAzureClientSecretRotationRouter } from "./azure-client-secret-rotation-router";
 import { registerLdapPasswordRotationRouter } from "./ldap-password-rotation-router";
 import { registerMsSqlCredentialsRotationRouter } from "./mssql-credentials-rotation-router";
+import { registerMySqlCredentialsRotationRouter } from "./mysql-credentials-rotation-router";
 import { registerPostgresCredentialsRotationRouter } from "./postgres-credentials-rotation-router";
 
 export * from "./secret-rotation-v2-router";
@@ -15,6 +16,7 @@ export const SECRET_ROTATION_REGISTER_ROUTER_MAP: Record<
 > = {
   [SecretRotation.PostgresCredentials]: registerPostgresCredentialsRotationRouter,
   [SecretRotation.MsSqlCredentials]: registerMsSqlCredentialsRotationRouter,
+  [SecretRotation.MySqlCredentials]: registerMySqlCredentialsRotationRouter,
   [SecretRotation.Auth0ClientSecret]: registerAuth0ClientSecretRotationRouter,
   [SecretRotation.AzureClientSecret]: registerAzureClientSecretRotationRouter,
   [SecretRotation.AwsIamUserSecret]: registerAwsIamUserSecretRotationRouter,

backend/src/ee/routes/v2/secret-rotation-v2-routers/mysql-credentials-rotation-router.ts

@@ -0,0 +1,19 @@
+import {
+  CreateMySqlCredentialsRotationSchema,
+  MySqlCredentialsRotationSchema,
+  UpdateMySqlCredentialsRotationSchema
+} from "@app/ee/services/secret-rotation-v2/mysql-credentials";
+import { SecretRotation } from "@app/ee/services/secret-rotation-v2/secret-rotation-v2-enums";
+import { SqlCredentialsRotationGeneratedCredentialsSchema } from "@app/ee/services/secret-rotation-v2/shared/sql-credentials";
+
+import { registerSecretRotationEndpoints } from "./secret-rotation-v2-endpoints";
+
+export const registerMySqlCredentialsRotationRouter = async (server: FastifyZodProvider) =>
+  registerSecretRotationEndpoints({
+    type: SecretRotation.MySqlCredentials,
+    server,
+    responseSchema: MySqlCredentialsRotationSchema,
+    createSchema: CreateMySqlCredentialsRotationSchema,
+    updateSchema: UpdateMySqlCredentialsRotationSchema,
+    generatedCredentialsSchema: SqlCredentialsRotationGeneratedCredentialsSchema
+  });

backend/src/ee/routes/v2/secret-rotation-v2-routers/secret-rotation-v2-router.ts

@@ -6,6 +6,7 @@ import { AwsIamUserSecretRotationListItemSchema } from "@app/ee/services/secret-
 import { AzureClientSecretRotationListItemSchema } from "@app/ee/services/secret-rotation-v2/azure-client-secret";
 import { LdapPasswordRotationListItemSchema } from "@app/ee/services/secret-rotation-v2/ldap-password";
 import { MsSqlCredentialsRotationListItemSchema } from "@app/ee/services/secret-rotation-v2/mssql-credentials";
+import { MySqlCredentialsRotationListItemSchema } from "@app/ee/services/secret-rotation-v2/mysql-credentials";
 import { PostgresCredentialsRotationListItemSchema } from "@app/ee/services/secret-rotation-v2/postgres-credentials";
 import { SecretRotationV2Schema } from "@app/ee/services/secret-rotation-v2/secret-rotation-v2-union-schema";
 import { ApiDocsTags, SecretRotations } from "@app/lib/api-docs";
@@ -16,6 +17,7 @@ import { AuthMode } from "@app/services/auth/auth-type";
 const SecretRotationV2OptionsSchema = z.discriminatedUnion("type", [
   PostgresCredentialsRotationListItemSchema,
   MsSqlCredentialsRotationListItemSchema,
+  MySqlCredentialsRotationListItemSchema,
   Auth0ClientSecretRotationListItemSchema,
   AzureClientSecretRotationListItemSchema,
   AwsIamUserSecretRotationListItemSchema,

backend/src/ee/services/access-approval-policy/access-approval-policy-approver-dal.ts

@@ -8,3 +8,10 @@ export const accessApprovalPolicyApproverDALFactory = (db: TDbClient) => {
   const accessApprovalPolicyApproverOrm = ormify(db, TableName.AccessApprovalPolicyApprover);
   return { ...accessApprovalPolicyApproverOrm };
 };
+
+export type TAccessApprovalPolicyBypasserDALFactory = ReturnType<typeof accessApprovalPolicyBypasserDALFactory>;
+
+export const accessApprovalPolicyBypasserDALFactory = (db: TDbClient) => {
+  const accessApprovalPolicyBypasserOrm = ormify(db, TableName.AccessApprovalPolicyBypasser);
+  return { ...accessApprovalPolicyBypasserOrm };
+};

backend/src/ee/services/access-approval-policy/access-approval-policy-dal.ts

@@ -1,11 +1,11 @@
 import { Knex } from "knex";
 
 import { TDbClient } from "@app/db";
-import { AccessApprovalPoliciesSchema, TableName, TAccessApprovalPolicies } from "@app/db/schemas";
+import { AccessApprovalPoliciesSchema, TableName, TAccessApprovalPolicies, TUsers } from "@app/db/schemas";
 import { DatabaseError } from "@app/lib/errors";
 import { buildFindFilter, ormify, selectAllTableCols, sqlNestRelationships, TFindFilter } from "@app/lib/knex";
 
-import { ApproverType } from "./access-approval-policy-types";
+import { ApproverType, BypasserType } from "./access-approval-policy-types";
 
 export type TAccessApprovalPolicyDALFactory = ReturnType<typeof accessApprovalPolicyDALFactory>;
 
@@ -34,9 +34,22 @@ export const accessApprovalPolicyDALFactory = (db: TDbClient) => {
         `${TableName.AccessApprovalPolicyApprover}.policyId`
       )
       .leftJoin(TableName.Users, `${TableName.AccessApprovalPolicyApprover}.approverUserId`, `${TableName.Users}.id`)
+      .leftJoin(
+        TableName.AccessApprovalPolicyBypasser,
+        `${TableName.AccessApprovalPolicy}.id`,
+        `${TableName.AccessApprovalPolicyBypasser}.policyId`
+      )
+      .leftJoin<TUsers>(
+        db(TableName.Users).as("bypasserUsers"),
+        `${TableName.AccessApprovalPolicyBypasser}.bypasserUserId`,
+        `bypasserUsers.id`
+      )
       .select(tx.ref("username").withSchema(TableName.Users).as("approverUsername"))
+      .select(tx.ref("username").withSchema("bypasserUsers").as("bypasserUsername"))
       .select(tx.ref("approverUserId").withSchema(TableName.AccessApprovalPolicyApprover))
       .select(tx.ref("approverGroupId").withSchema(TableName.AccessApprovalPolicyApprover))
+      .select(tx.ref("bypasserUserId").withSchema(TableName.AccessApprovalPolicyBypasser))
+      .select(tx.ref("bypasserGroupId").withSchema(TableName.AccessApprovalPolicyBypasser))
       .select(tx.ref("name").withSchema(TableName.Environment).as("envName"))
       .select(tx.ref("slug").withSchema(TableName.Environment).as("envSlug"))
       .select(tx.ref("id").withSchema(TableName.Environment).as("envId"))
@@ -129,6 +142,23 @@ export const accessApprovalPolicyDALFactory = (db: TDbClient) => {
               id,
               type: ApproverType.Group
             })
+          },
+          {
+            key: "bypasserUserId",
+            label: "bypassers" as const,
+            mapper: ({ bypasserUserId: id, bypasserUsername }) => ({
+              id,
+              type: BypasserType.User,
+              name: bypasserUsername
+            })
+          },
+          {
+            key: "bypasserGroupId",
+            label: "bypassers" as const,
+            mapper: ({ bypasserGroupId: id }) => ({
+              id,
+              type: BypasserType.Group
+            })
           }
         ]
       });
@@ -144,5 +174,28 @@ export const accessApprovalPolicyDALFactory = (db: TDbClient) => {
     return softDeletedPolicy;
   };
 
-  return { ...accessApprovalPolicyOrm, find, findById, softDeleteById };
+  const findLastValidPolicy = async ({ envId, secretPath }: { envId: string; secretPath: string }, tx?: Knex) => {
+    try {
+      const result = await (tx || db.replicaNode())(TableName.AccessApprovalPolicy)
+        .where(
+          // eslint-disable-next-line @typescript-eslint/no-misused-promises
+          buildFindFilter(
+            {
+              envId,
+              secretPath
+            },
+            TableName.AccessApprovalPolicy
+          )
+        )
+        .orderBy("deletedAt", "desc")
+        .orderByRaw(`"deletedAt" IS NULL`)
+        .first();
+
+      return result;
+    } catch (error) {
+      throw new DatabaseError({ error, name: "FindLastValidPolicy" });
+    }
+  };
+
+  return { ...accessApprovalPolicyOrm, find, findById, softDeleteById, findLastValidPolicy };
 };

backend/src/ee/services/access-approval-policy/access-approval-policy-service.ts

@@ -2,8 +2,9 @@ import { ForbiddenError } from "@casl/ability";
 
 import { ActionProjectType } from "@app/db/schemas";
 import { TPermissionServiceFactory } from "@app/ee/services/permission/permission-service";
-import { ProjectPermissionApprovalActions, ProjectPermissionSub } from "@app/ee/services/permission/project-permission";
+import { ProjectPermissionActions, ProjectPermissionSub } from "@app/ee/services/permission/project-permission";
 import { BadRequestError, ForbiddenRequestError, NotFoundError } from "@app/lib/errors";
+import { TOrgMembershipDALFactory } from "@app/services/org-membership/org-membership-dal";
 import { TProjectDALFactory } from "@app/services/project/project-dal";
 import { TProjectEnvDALFactory } from "@app/services/project-env/project-env-dal";
 import { TProjectMembershipDALFactory } from "@app/services/project-membership/project-membership-dal";
@@ -14,10 +15,14 @@ import { TAccessApprovalRequestReviewerDALFactory } from "../access-approval-req
 import { ApprovalStatus } from "../access-approval-request/access-approval-request-types";
 import { TGroupDALFactory } from "../group/group-dal";
 import { TProjectUserAdditionalPrivilegeDALFactory } from "../project-user-additional-privilege/project-user-additional-privilege-dal";
-import { TAccessApprovalPolicyApproverDALFactory } from "./access-approval-policy-approver-dal";
+import {
+  TAccessApprovalPolicyApproverDALFactory,
+  TAccessApprovalPolicyBypasserDALFactory
+} from "./access-approval-policy-approver-dal";
 import { TAccessApprovalPolicyDALFactory } from "./access-approval-policy-dal";
 import {
   ApproverType,
+  BypasserType,
   TCreateAccessApprovalPolicy,
   TDeleteAccessApprovalPolicy,
   TGetAccessApprovalPolicyByIdDTO,
@@ -32,12 +37,14 @@ type TAccessApprovalPolicyServiceFactoryDep = {
   accessApprovalPolicyDAL: TAccessApprovalPolicyDALFactory;
   projectEnvDAL: Pick<TProjectEnvDALFactory, "find" | "findOne">;
   accessApprovalPolicyApproverDAL: TAccessApprovalPolicyApproverDALFactory;
+  accessApprovalPolicyBypasserDAL: TAccessApprovalPolicyBypasserDALFactory;
   projectMembershipDAL: Pick<TProjectMembershipDALFactory, "find">;
   groupDAL: TGroupDALFactory;
   userDAL: Pick<TUserDALFactory, "find">;
   accessApprovalRequestDAL: Pick<TAccessApprovalRequestDALFactory, "update" | "find">;
   additionalPrivilegeDAL: Pick<TProjectUserAdditionalPrivilegeDALFactory, "delete">;
   accessApprovalRequestReviewerDAL: Pick<TAccessApprovalRequestReviewerDALFactory, "update">;
+  orgMembershipDAL: Pick<TOrgMembershipDALFactory, "find">;
 };
 
 export type TAccessApprovalPolicyServiceFactory = ReturnType<typeof accessApprovalPolicyServiceFactory>;
@@ -45,6 +52,7 @@ export type TAccessApprovalPolicyServiceFactory = ReturnType<typeof accessApprov
 export const accessApprovalPolicyServiceFactory = ({
   accessApprovalPolicyDAL,
   accessApprovalPolicyApproverDAL,
+  accessApprovalPolicyBypasserDAL,
   groupDAL,
   permissionService,
   projectEnvDAL,
@@ -52,7 +60,8 @@ export const accessApprovalPolicyServiceFactory = ({
   userDAL,
   accessApprovalRequestDAL,
   additionalPrivilegeDAL,
-  accessApprovalRequestReviewerDAL
+  accessApprovalRequestReviewerDAL,
+  orgMembershipDAL
 }: TAccessApprovalPolicyServiceFactoryDep) => {
   const createAccessApprovalPolicy = async ({
     name,
@@ -63,6 +72,7 @@ export const accessApprovalPolicyServiceFactory = ({
     actorAuthMethod,
     approvals,
     approvers,
+    bypassers,
     projectSlug,
     environment,
     enforcementLevel,
@@ -82,7 +92,7 @@ export const accessApprovalPolicyServiceFactory = ({
       .filter(Boolean) as string[];
 
     const userApproverNames = approvers
-      .map((approver) => (approver.type === ApproverType.User ? approver.name : undefined))
+      .map((approver) => (approver.type === ApproverType.User ? approver.username : undefined))
       .filter(Boolean) as string[];
 
     if (!groupApprovers && approvals > userApprovers.length + userApproverNames.length)
@@ -98,7 +108,7 @@ export const accessApprovalPolicyServiceFactory = ({
     });
 
     ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionApprovalActions.Create,
+      ProjectPermissionActions.Create,
       ProjectPermissionSub.SecretApproval
     );
     const env = await projectEnvDAL.findOne({ slug: environment, projectId: project.id });
@@ -147,6 +157,44 @@ export const accessApprovalPolicyServiceFactory = ({
       .map((user) => user.id);
     verifyAllApprovers.push(...verifyGroupApprovers);
 
+    let groupBypassers: string[] = [];
+    let bypasserUserIds: string[] = [];
+
+    if (bypassers && bypassers.length) {
+      groupBypassers = bypassers
+        .filter((bypasser) => bypasser.type === BypasserType.Group)
+        .map((bypasser) => bypasser.id) as string[];
+
+      const userBypassers = bypassers
+        .filter((bypasser) => bypasser.type === BypasserType.User)
+        .map((bypasser) => bypasser.id)
+        .filter(Boolean) as string[];
+
+      const userBypasserNames = bypassers
+        .map((bypasser) => (bypasser.type === BypasserType.User ? bypasser.username : undefined))
+        .filter(Boolean) as string[];
+
+      bypasserUserIds = userBypassers;
+      if (userBypasserNames.length) {
+        const bypasserUsers = await userDAL.find({
+          $in: {
+            username: userBypasserNames
+          }
+        });
+
+        const bypasserNamesFromDb = bypasserUsers.map((user) => user.username);
+        const invalidUsernames = userBypasserNames.filter((username) => !bypasserNamesFromDb.includes(username));
+
+        if (invalidUsernames.length) {
+          throw new BadRequestError({
+            message: `Invalid bypasser user: ${invalidUsernames.join(", ")}`
+          });
+        }
+
+        bypasserUserIds = bypasserUserIds.concat(bypasserUsers.map((user) => user.id));
+      }
+    }
+
     const accessApproval = await accessApprovalPolicyDAL.transaction(async (tx) => {
       const doc = await accessApprovalPolicyDAL.create(
         {
@@ -159,6 +207,7 @@ export const accessApprovalPolicyServiceFactory = ({
         },
         tx
       );
+
       if (approverUserIds.length) {
         await accessApprovalPolicyApproverDAL.insertMany(
           approverUserIds.map((userId) => ({
@@ -179,8 +228,29 @@ export const accessApprovalPolicyServiceFactory = ({
         );
       }
 
+      if (bypasserUserIds.length) {
+        await accessApprovalPolicyBypasserDAL.insertMany(
+          bypasserUserIds.map((userId) => ({
+            bypasserUserId: userId,
+            policyId: doc.id
+          })),
+          tx
+        );
+      }
+
+      if (groupBypassers.length) {
+        await accessApprovalPolicyBypasserDAL.insertMany(
+          groupBypassers.map((groupId) => ({
+            bypasserGroupId: groupId,
+            policyId: doc.id
+          })),
+          tx
+        );
+      }
+
       return doc;
     });
+
     return { ...accessApproval, environment: env, projectId: project.id };
   };
 
@@ -211,6 +281,7 @@ export const accessApprovalPolicyServiceFactory = ({
   const updateAccessApprovalPolicy = async ({
     policyId,
     approvers,
+    bypassers,
     secretPath,
     name,
     actorId,
@@ -231,15 +302,15 @@ export const accessApprovalPolicyServiceFactory = ({
       .filter(Boolean) as string[];
 
     const userApproverNames = approvers
-      .map((approver) => (approver.type === ApproverType.User ? approver.name : undefined))
+      .map((approver) => (approver.type === ApproverType.User ? approver.username : undefined))
       .filter(Boolean) as string[];
 
     const accessApprovalPolicy = await accessApprovalPolicyDAL.findById(policyId);
-    const currentAppovals = approvals || accessApprovalPolicy.approvals;
+    const currentApprovals = approvals || accessApprovalPolicy.approvals;
     if (
       groupApprovers?.length === 0 &&
       userApprovers &&
-      currentAppovals > userApprovers.length + userApproverNames.length
+      currentApprovals > userApprovers.length + userApproverNames.length
     ) {
       throw new BadRequestError({ message: "Approvals cannot be greater than approvers" });
     }
@@ -256,10 +327,79 @@ export const accessApprovalPolicyServiceFactory = ({
       actionProjectType: ActionProjectType.SecretManager
     });
 
-    ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionApprovalActions.Edit,
-      ProjectPermissionSub.SecretApproval
-    );
+    ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Edit, ProjectPermissionSub.SecretApproval);
+
+    let groupBypassers: string[] = [];
+    let bypasserUserIds: string[] = [];
+
+    if (bypassers && bypassers.length) {
+      groupBypassers = bypassers
+        .filter((bypasser) => bypasser.type === BypasserType.Group)
+        .map((bypasser) => bypasser.id) as string[];
+
+      groupBypassers = [...new Set(groupBypassers)];
+
+      const userBypassers = bypassers
+        .filter((bypasser) => bypasser.type === BypasserType.User)
+        .map((bypasser) => bypasser.id)
+        .filter(Boolean) as string[];
+
+      const userBypasserNames = bypassers
+        .map((bypasser) => (bypasser.type === BypasserType.User ? bypasser.username : undefined))
+        .filter(Boolean) as string[];
+
+      bypasserUserIds = userBypassers;
+      if (userBypasserNames.length) {
+        const bypasserUsers = await userDAL.find({
+          $in: {
+            username: userBypasserNames
+          }
+        });
+
+        const bypasserNamesFromDb = bypasserUsers.map((user) => user.username);
+        const invalidUsernames = userBypasserNames.filter((username) => !bypasserNamesFromDb.includes(username));
+
+        if (invalidUsernames.length) {
+          throw new BadRequestError({
+            message: `Invalid bypasser user: ${invalidUsernames.join(", ")}`
+          });
+        }
+
+        bypasserUserIds = [...new Set(bypasserUserIds.concat(bypasserUsers.map((user) => user.id)))];
+      }
+
+      // Validate user bypassers
+      if (bypasserUserIds.length > 0) {
+        const orgMemberships = await orgMembershipDAL.find({
+          $in: { userId: bypasserUserIds },
+          orgId: actorOrgId
+        });
+
+        if (orgMemberships.length !== bypasserUserIds.length) {
+          const foundUserIdsInOrg = new Set(orgMemberships.map((mem) => mem.userId));
+          const missingUserIds = bypasserUserIds.filter((id) => !foundUserIdsInOrg.has(id));
+          throw new BadRequestError({
+            message: `One or more specified bypasser users are not part of the organization or do not exist. Invalid or non-member user IDs: ${missingUserIds.join(", ")}`
+          });
+        }
+      }
+
+      // Validate group bypassers
+      if (groupBypassers.length > 0) {
+        const orgGroups = await groupDAL.find({
+          $in: { id: groupBypassers },
+          orgId: actorOrgId
+        });
+
+        if (orgGroups.length !== groupBypassers.length) {
+          const foundGroupIdsInOrg = new Set(orgGroups.map((group) => group.id));
+          const missingGroupIds = groupBypassers.filter((id) => !foundGroupIdsInOrg.has(id));
+          throw new BadRequestError({
+            message: `One or more specified bypasser groups are not part of the organization or do not exist. Invalid or non-member group IDs: ${missingGroupIds.join(", ")}`
+          });
+        }
+      }
+    }
 
     const updatedPolicy = await accessApprovalPolicyDAL.transaction(async (tx) => {
       const doc = await accessApprovalPolicyDAL.updateById(
@@ -316,6 +456,28 @@ export const accessApprovalPolicyServiceFactory = ({
         );
       }
 
+      await accessApprovalPolicyBypasserDAL.delete({ policyId: doc.id }, tx);
+
+      if (bypasserUserIds.length) {
+        await accessApprovalPolicyBypasserDAL.insertMany(
+          bypasserUserIds.map((userId) => ({
+            bypasserUserId: userId,
+            policyId: doc.id
+          })),
+          tx
+        );
+      }
+
+      if (groupBypassers.length) {
+        await accessApprovalPolicyBypasserDAL.insertMany(
+          groupBypassers.map((groupId) => ({
+            bypasserGroupId: groupId,
+            policyId: doc.id
+          })),
+          tx
+        );
+      }
+
       return doc;
     });
     return {
@@ -344,7 +506,7 @@ export const accessApprovalPolicyServiceFactory = ({
       actionProjectType: ActionProjectType.SecretManager
     });
     ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionApprovalActions.Delete,
+      ProjectPermissionActions.Delete,
       ProjectPermissionSub.SecretApproval
     );
 
@@ -435,10 +597,7 @@ export const accessApprovalPolicyServiceFactory = ({
       actionProjectType: ActionProjectType.SecretManager
     });
 
-    ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionApprovalActions.Read,
-      ProjectPermissionSub.SecretApproval
-    );
+    ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Read, ProjectPermissionSub.SecretApproval);
 
     return policy;
   };

backend/src/ee/services/access-approval-policy/access-approval-policy-types.ts

@@ -18,11 +18,20 @@ export enum ApproverType {
   User = "user"
 }
 
+export enum BypasserType {
+  Group = "group",
+  User = "user"
+}
+
 export type TCreateAccessApprovalPolicy = {
   approvals: number;
   secretPath: string;
   environment: string;
-  approvers: ({ type: ApproverType.Group; id: string } | { type: ApproverType.User; id?: string; name?: string })[];
+  approvers: ({ type: ApproverType.Group; id: string } | { type: ApproverType.User; id?: string; username?: string })[];
+  bypassers?: (
+    | { type: BypasserType.Group; id: string }
+    | { type: BypasserType.User; id?: string; username?: string }
+  )[];
   projectSlug: string;
   name: string;
   enforcementLevel: EnforcementLevel;
@@ -32,7 +41,11 @@ export type TCreateAccessApprovalPolicy = {
 export type TUpdateAccessApprovalPolicy = {
   policyId: string;
   approvals?: number;
-  approvers: ({ type: ApproverType.Group; id: string } | { type: ApproverType.User; id?: string; name?: string })[];
+  approvers: ({ type: ApproverType.Group; id: string } | { type: ApproverType.User; id?: string; username?: string })[];
+  bypassers?: (
+    | { type: BypasserType.Group; id: string }
+    | { type: BypasserType.User; id?: string; username?: string }
+  )[];
   secretPath?: string;
   name?: string;
   enforcementLevel?: EnforcementLevel;

backend/src/ee/services/access-approval-request/access-approval-request-dal.ts

@@ -1,7 +1,13 @@
 import { Knex } from "knex";
 
 import { TDbClient } from "@app/db";
-import { AccessApprovalRequestsSchema, TableName, TAccessApprovalRequests, TUsers } from "@app/db/schemas";
+import {
+  AccessApprovalRequestsSchema,
+  TableName,
+  TAccessApprovalRequests,
+  TUserGroupMembership,
+  TUsers
+} from "@app/db/schemas";
 import { DatabaseError } from "@app/lib/errors";
 import { ormify, selectAllTableCols, sqlNestRelationships, TFindFilter } from "@app/lib/knex";
 
@@ -28,12 +34,12 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
           `${TableName.AccessApprovalRequest}.policyId`,
           `${TableName.AccessApprovalPolicy}.id`
         )
-
         .leftJoin(
           TableName.AccessApprovalRequestReviewer,
           `${TableName.AccessApprovalRequest}.id`,
           `${TableName.AccessApprovalRequestReviewer}.requestId`
         )
+
         .leftJoin(
           TableName.AccessApprovalPolicyApprover,
           `${TableName.AccessApprovalPolicy}.id`,
@@ -46,6 +52,17 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
         )
         .leftJoin(TableName.Users, `${TableName.UserGroupMembership}.userId`, `${TableName.Users}.id`)
 
+        .leftJoin(
+          TableName.AccessApprovalPolicyBypasser,
+          `${TableName.AccessApprovalPolicy}.id`,
+          `${TableName.AccessApprovalPolicyBypasser}.policyId`
+        )
+        .leftJoin<TUserGroupMembership>(
+          db(TableName.UserGroupMembership).as("bypasserUserGroupMembership"),
+          `${TableName.AccessApprovalPolicyBypasser}.bypasserGroupId`,
+          `bypasserUserGroupMembership.groupId`
+        )
+
         .join<TUsers>(
           db(TableName.Users).as("requestedByUser"),
           `${TableName.AccessApprovalRequest}.requestedByUserId`,
@@ -69,6 +86,9 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
         .select(db.ref("approverUserId").withSchema(TableName.AccessApprovalPolicyApprover))
         .select(db.ref("userId").withSchema(TableName.UserGroupMembership).as("approverGroupUserId"))
 
+        .select(db.ref("bypasserUserId").withSchema(TableName.AccessApprovalPolicyBypasser))
+        .select(db.ref("userId").withSchema("bypasserUserGroupMembership").as("bypasserGroupUserId"))
+
         .select(
           db.ref("projectId").withSchema(TableName.Environment),
           db.ref("slug").withSchema(TableName.Environment).as("envSlug"),
@@ -145,7 +165,7 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
               }
             : null,
 
-          isApproved: !!doc.policyDeletedAt || !!doc.privilegeId
+          isApproved: !!doc.policyDeletedAt || !!doc.privilegeId || doc.status !== ApprovalStatus.PENDING
         }),
         childrenMapper: [
           {
@@ -158,6 +178,12 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
             key: "approverGroupUserId",
             label: "approvers" as const,
             mapper: ({ approverGroupUserId }) => approverGroupUserId
+          },
+          { key: "bypasserUserId", label: "bypassers" as const, mapper: ({ bypasserUserId }) => bypasserUserId },
+          {
+            key: "bypasserGroupUserId",
+            label: "bypassers" as const,
+            mapper: ({ bypasserGroupUserId }) => bypasserGroupUserId
           }
         ]
       });
@@ -166,7 +192,7 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
 
       return formattedDocs.map((doc) => ({
         ...doc,
-        policy: { ...doc.policy, approvers: doc.approvers }
+        policy: { ...doc.policy, approvers: doc.approvers, bypassers: doc.bypassers }
       }));
     } catch (error) {
       throw new DatabaseError({ error, name: "FindRequestsWithPrivilege" });
@@ -193,7 +219,6 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
         `${TableName.AccessApprovalPolicy}.id`,
         `${TableName.AccessApprovalPolicyApprover}.policyId`
       )
-
       .leftJoin<TUsers>(
         db(TableName.Users).as("accessApprovalPolicyApproverUser"),
         `${TableName.AccessApprovalPolicyApprover}.approverUserId`,
@@ -204,13 +229,33 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
         `${TableName.AccessApprovalPolicyApprover}.approverGroupId`,
         `${TableName.UserGroupMembership}.groupId`
       )
-
       .leftJoin<TUsers>(
         db(TableName.Users).as("accessApprovalPolicyGroupApproverUser"),
         `${TableName.UserGroupMembership}.userId`,
         "accessApprovalPolicyGroupApproverUser.id"
       )
 
+      .leftJoin(
+        TableName.AccessApprovalPolicyBypasser,
+        `${TableName.AccessApprovalPolicy}.id`,
+        `${TableName.AccessApprovalPolicyBypasser}.policyId`
+      )
+      .leftJoin<TUsers>(
+        db(TableName.Users).as("accessApprovalPolicyBypasserUser"),
+        `${TableName.AccessApprovalPolicyBypasser}.bypasserUserId`,
+        "accessApprovalPolicyBypasserUser.id"
+      )
+      .leftJoin<TUserGroupMembership>(
+        db(TableName.UserGroupMembership).as("bypasserUserGroupMembership"),
+        `${TableName.AccessApprovalPolicyBypasser}.bypasserGroupId`,
+        `bypasserUserGroupMembership.groupId`
+      )
+      .leftJoin<TUsers>(
+        db(TableName.Users).as("accessApprovalPolicyGroupBypasserUser"),
+        `bypasserUserGroupMembership.userId`,
+        "accessApprovalPolicyGroupBypasserUser.id"
+      )
+
       .leftJoin(
         TableName.AccessApprovalRequestReviewer,
         `${TableName.AccessApprovalRequest}.id`,
@@ -241,6 +286,18 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
         tx.ref("firstName").withSchema("requestedByUser").as("requestedByUserFirstName"),
         tx.ref("lastName").withSchema("requestedByUser").as("requestedByUserLastName"),
 
+        // Bypassers
+        tx.ref("bypasserUserId").withSchema(TableName.AccessApprovalPolicyBypasser),
+        tx.ref("userId").withSchema("bypasserUserGroupMembership").as("bypasserGroupUserId"),
+        tx.ref("email").withSchema("accessApprovalPolicyBypasserUser").as("bypasserEmail"),
+        tx.ref("email").withSchema("accessApprovalPolicyGroupBypasserUser").as("bypasserGroupEmail"),
+        tx.ref("username").withSchema("accessApprovalPolicyBypasserUser").as("bypasserUsername"),
+        tx.ref("username").withSchema("accessApprovalPolicyGroupBypasserUser").as("bypasserGroupUsername"),
+        tx.ref("firstName").withSchema("accessApprovalPolicyBypasserUser").as("bypasserFirstName"),
+        tx.ref("firstName").withSchema("accessApprovalPolicyGroupBypasserUser").as("bypasserGroupFirstName"),
+        tx.ref("lastName").withSchema("accessApprovalPolicyBypasserUser").as("bypasserLastName"),
+        tx.ref("lastName").withSchema("accessApprovalPolicyGroupBypasserUser").as("bypasserGroupLastName"),
+
         tx.ref("reviewerUserId").withSchema(TableName.AccessApprovalRequestReviewer),
 
         tx.ref("status").withSchema(TableName.AccessApprovalRequestReviewer).as("reviewerStatus"),
@@ -265,7 +322,7 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
     try {
       const sql = findQuery({ [`${TableName.AccessApprovalRequest}.id` as "id"]: id }, tx || db.replicaNode());
       const docs = await sql;
-      const formatedDoc = sqlNestRelationships({
+      const formattedDoc = sqlNestRelationships({
         data: docs,
         key: "id",
         parentMapper: (el) => ({
@@ -335,13 +392,51 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
               lastName,
               username
             })
+          },
+          {
+            key: "bypasserUserId",
+            label: "bypassers" as const,
+            mapper: ({
+              bypasserUserId,
+              bypasserEmail: email,
+              bypasserUsername: username,
+              bypasserLastName: lastName,
+              bypasserFirstName: firstName
+            }) => ({
+              userId: bypasserUserId,
+              email,
+              firstName,
+              lastName,
+              username
+            })
+          },
+          {
+            key: "bypasserGroupUserId",
+            label: "bypassers" as const,
+            mapper: ({
+              userId,
+              bypasserGroupEmail: email,
+              bypasserGroupUsername: username,
+              bypasserGroupLastName: lastName,
+              bypasserFirstName: firstName
+            }) => ({
+              userId,
+              email,
+              firstName,
+              lastName,
+              username
+            })
           }
         ]
       });
-      if (!formatedDoc?.[0]) return;
+      if (!formattedDoc?.[0]) return;
       return {
-        ...formatedDoc[0],
-        policy: { ...formatedDoc[0].policy, approvers: formatedDoc[0].approvers }
+        ...formattedDoc[0],
+        policy: {
+          ...formattedDoc[0].policy,
+          approvers: formattedDoc[0].approvers,
+          bypassers: formattedDoc[0].bypassers
+        }
       };
     } catch (error) {
       throw new DatabaseError({ error, name: "FindByIdAccessApprovalRequest" });
@@ -392,14 +487,20 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
         ]
       });
 
-      // an approval is pending if there is no reviewer rejections and no privilege ID is set
+      // an approval is pending if there is no reviewer rejections, no privilege ID is set and the status is pending
       const pendingApprovals = formattedRequests.filter(
-        (req) => !req.privilegeId && !req.reviewers.some((r) => r.status === ApprovalStatus.REJECTED)
+        (req) =>
+          !req.privilegeId &&
+          !req.reviewers.some((r) => r.status === ApprovalStatus.REJECTED) &&
+          req.status === ApprovalStatus.PENDING
       );
 
-      // an approval is finalized if there are any rejections or a privilege ID is set
+      // an approval is finalized if there are any rejections, a privilege ID is set or the number of approvals is equal to the number of approvals required
       const finalizedApprovals = formattedRequests.filter(
-        (req) => req.privilegeId || req.reviewers.some((r) => r.status === ApprovalStatus.REJECTED)
+        (req) =>
+          req.privilegeId ||
+          req.reviewers.some((r) => r.status === ApprovalStatus.REJECTED) ||
+          req.status !== ApprovalStatus.PENDING
       );
 
       return { pendingCount: pendingApprovals.length, finalizedCount: finalizedApprovals.length };

backend/src/ee/services/access-approval-request/access-approval-request-service.ts

@@ -23,7 +23,6 @@ import { TAccessApprovalPolicyApproverDALFactory } from "../access-approval-poli
 import { TAccessApprovalPolicyDALFactory } from "../access-approval-policy/access-approval-policy-dal";
 import { TGroupDALFactory } from "../group/group-dal";
 import { TPermissionServiceFactory } from "../permission/permission-service";
-import { ProjectPermissionApprovalActions, ProjectPermissionSub } from "../permission/project-permission";
 import { TProjectUserAdditionalPrivilegeDALFactory } from "../project-user-additional-privilege/project-user-additional-privilege-dal";
 import { ProjectUserAdditionalPrivilegeTemporaryMode } from "../project-user-additional-privilege/project-user-additional-privilege-types";
 import { TAccessApprovalRequestDALFactory } from "./access-approval-request-dal";
@@ -57,7 +56,7 @@ type TSecretApprovalRequestServiceFactoryDep = {
     | "findOne"
     | "getCount"
   >;
-  accessApprovalPolicyDAL: Pick<TAccessApprovalPolicyDALFactory, "findOne" | "find">;
+  accessApprovalPolicyDAL: Pick<TAccessApprovalPolicyDALFactory, "findOne" | "find" | "findLastValidPolicy">;
   accessApprovalRequestReviewerDAL: Pick<
     TAccessApprovalRequestReviewerDALFactory,
     "create" | "find" | "findOne" | "transaction"
@@ -132,7 +131,7 @@ export const accessApprovalRequestServiceFactory = ({
 
     if (!environment) throw new NotFoundError({ message: `Environment with slug '${envSlug}' not found` });
 
-    const policy = await accessApprovalPolicyDAL.findOne({
+    const policy = await accessApprovalPolicyDAL.findLastValidPolicy({
       envId: environment.id,
       secretPath
     });
@@ -204,7 +203,7 @@ export const accessApprovalRequestServiceFactory = ({
 
           const isRejected = reviewers.some((reviewer) => reviewer.status === ApprovalStatus.REJECTED);
 
-          if (!isRejected) {
+          if (!isRejected && duplicateRequest.status === ApprovalStatus.PENDING) {
             throw new BadRequestError({ message: "You already have a pending access request with the same criteria" });
           }
         }
@@ -340,7 +339,7 @@ export const accessApprovalRequestServiceFactory = ({
       });
     }
 
-    const { membership, hasRole, permission } = await permissionService.getProjectPermission({
+    const { membership, hasRole } = await permissionService.getProjectPermission({
       actor,
       actorId,
       projectId: accessApprovalRequest.projectId,
@@ -355,13 +354,13 @@ export const accessApprovalRequestServiceFactory = ({
 
     const isSelfApproval = actorId === accessApprovalRequest.requestedByUserId;
     const isSoftEnforcement = policy.enforcementLevel === EnforcementLevel.Soft;
-    const canBypassApproval = permission.can(
-      ProjectPermissionApprovalActions.AllowAccessBypass,
-      ProjectPermissionSub.SecretApproval
-    );
-    const cannotBypassUnderSoftEnforcement = !(isSoftEnforcement && canBypassApproval);
+    const canBypass = !policy.bypassers.length || policy.bypassers.some((bypasser) => bypasser.userId === actorId);
+    const cannotBypassUnderSoftEnforcement = !(isSoftEnforcement && canBypass);
 
-    if (!policy.allowedSelfApprovals && isSelfApproval && cannotBypassUnderSoftEnforcement) {
+    const isApprover = policy.approvers.find((approver) => approver.userId === actorId);
+
+    // If user is (not an approver OR cant self approve) AND can't bypass policy
+    if ((!isApprover || (!policy.allowedSelfApprovals && isSelfApproval)) && cannotBypassUnderSoftEnforcement) {
       throw new BadRequestError({
         message: "Failed to review access approval request. Users are not authorized to review their own request."
       });
@@ -370,7 +369,7 @@ export const accessApprovalRequestServiceFactory = ({
     if (
       !hasRole(ProjectMembershipRole.Admin) &&
       accessApprovalRequest.requestedByUserId !== actorId && // The request wasn't made by the current user
-      !policy.approvers.find((approver) => approver.userId === actorId) // The request isn't performed by an assigned approver
+      !isApprover // The request isn't performed by an assigned approver
     ) {
       throw new ForbiddenRequestError({ message: "You are not authorized to approve this request" });
     }
@@ -478,7 +477,11 @@ export const accessApprovalRequestServiceFactory = ({
             );
             privilegeIdToSet = privilege.id;
           }
-          await accessApprovalRequestDAL.updateById(accessApprovalRequest.id, { privilegeId: privilegeIdToSet }, tx);
+          await accessApprovalRequestDAL.updateById(
+            accessApprovalRequest.id,
+            { privilegeId: privilegeIdToSet, status: ApprovalStatus.APPROVED },
+            tx
+          );
         }
       }
 

backend/src/ee/services/license/license-service.ts

@@ -17,7 +17,7 @@ import { TIdentityOrgDALFactory } from "@app/services/identity/identity-org-dal"
 import { TOrgDALFactory } from "@app/services/org/org-dal";
 import { TProjectDALFactory } from "@app/services/project/project-dal";
 
-import { OrgPermissionActions, OrgPermissionSubjects } from "../permission/org-permission";
+import { OrgPermissionBillingActions, OrgPermissionSubjects } from "../permission/org-permission";
 import { TPermissionServiceFactory } from "../permission/permission-service";
 import { BillingPlanRows, BillingPlanTableHead } from "./licence-enums";
 import { TLicenseDALFactory } from "./license-dal";
@@ -288,7 +288,7 @@ export const licenseServiceFactory = ({
     billingCycle
   }: TOrgPlansTableDTO) => {
     const { permission } = await permissionService.getOrgPermission(actor, actorId, orgId, actorAuthMethod, actorOrgId);
-    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Read, OrgPermissionSubjects.Billing);
+    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionBillingActions.Read, OrgPermissionSubjects.Billing);
     const { data } = await licenseServerCloudApi.request.get(
       `/api/license-server/v1/cloud-products?billing-cycle=${billingCycle}`
     );
@@ -310,8 +310,10 @@ export const licenseServiceFactory = ({
     success_url
   }: TStartOrgTrialDTO) => {
     const { permission } = await permissionService.getOrgPermission(actor, actorId, orgId, actorAuthMethod, actorOrgId);
-    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Create, OrgPermissionSubjects.Billing);
-    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Edit, OrgPermissionSubjects.Billing);
+    ForbiddenError.from(permission).throwUnlessCan(
+      OrgPermissionBillingActions.ManageBilling,
+      OrgPermissionSubjects.Billing
+    );
 
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) {
@@ -338,8 +340,10 @@ export const licenseServiceFactory = ({
     actorOrgId
   }: TCreateOrgPortalSession) => {
     const { permission } = await permissionService.getOrgPermission(actor, actorId, orgId, actorAuthMethod, actorOrgId);
-    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Create, OrgPermissionSubjects.Billing);
-    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Edit, OrgPermissionSubjects.Billing);
+    ForbiddenError.from(permission).throwUnlessCan(
+      OrgPermissionBillingActions.ManageBilling,
+      OrgPermissionSubjects.Billing
+    );
 
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) {
@@ -385,7 +389,7 @@ export const licenseServiceFactory = ({
 
   const getOrgBillingInfo = async ({ orgId, actor, actorId, actorAuthMethod, actorOrgId }: TGetOrgBillInfoDTO) => {
     const { permission } = await permissionService.getOrgPermission(actor, actorId, orgId, actorAuthMethod, actorOrgId);
-    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Read, OrgPermissionSubjects.Billing);
+    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionBillingActions.Read, OrgPermissionSubjects.Billing);
 
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) {
@@ -413,7 +417,7 @@ export const licenseServiceFactory = ({
   // returns org current plan feature table
   const getOrgPlanTable = async ({ orgId, actor, actorId, actorAuthMethod, actorOrgId }: TGetOrgBillInfoDTO) => {
     const { permission } = await permissionService.getOrgPermission(actor, actorId, orgId, actorAuthMethod, actorOrgId);
-    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Read, OrgPermissionSubjects.Billing);
+    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionBillingActions.Read, OrgPermissionSubjects.Billing);
 
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) {
@@ -484,7 +488,7 @@ export const licenseServiceFactory = ({
 
   const getOrgBillingDetails = async ({ orgId, actor, actorId, actorAuthMethod, actorOrgId }: TGetOrgBillInfoDTO) => {
     const { permission } = await permissionService.getOrgPermission(actor, actorId, orgId, actorAuthMethod, actorOrgId);
-    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Read, OrgPermissionSubjects.Billing);
+    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionBillingActions.Read, OrgPermissionSubjects.Billing);
 
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) {
@@ -509,7 +513,10 @@ export const licenseServiceFactory = ({
     email
   }: TUpdateOrgBillingDetailsDTO) => {
     const { permission } = await permissionService.getOrgPermission(actor, actorId, orgId, actorAuthMethod, actorOrgId);
-    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Read, OrgPermissionSubjects.Billing);
+    ForbiddenError.from(permission).throwUnlessCan(
+      OrgPermissionBillingActions.ManageBilling,
+      OrgPermissionSubjects.Billing
+    );
 
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) {
@@ -529,7 +536,7 @@ export const licenseServiceFactory = ({
 
   const getOrgPmtMethods = async ({ orgId, actor, actorId, actorAuthMethod, actorOrgId }: TOrgPmtMethodsDTO) => {
     const { permission } = await permissionService.getOrgPermission(actor, actorId, orgId, actorAuthMethod, actorOrgId);
-    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Read, OrgPermissionSubjects.Billing);
+    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionBillingActions.Read, OrgPermissionSubjects.Billing);
 
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) {
@@ -556,7 +563,10 @@ export const licenseServiceFactory = ({
     cancel_url
   }: TAddOrgPmtMethodDTO) => {
     const { permission } = await permissionService.getOrgPermission(actor, actorId, orgId, actorAuthMethod, actorOrgId);
-    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Read, OrgPermissionSubjects.Billing);
+    ForbiddenError.from(permission).throwUnlessCan(
+      OrgPermissionBillingActions.ManageBilling,
+      OrgPermissionSubjects.Billing
+    );
 
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) {
@@ -585,7 +595,10 @@ export const licenseServiceFactory = ({
     pmtMethodId
   }: TDelOrgPmtMethodDTO) => {
     const { permission } = await permissionService.getOrgPermission(actor, actorId, orgId, actorAuthMethod, actorOrgId);
-    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Read, OrgPermissionSubjects.Billing);
+    ForbiddenError.from(permission).throwUnlessCan(
+      OrgPermissionBillingActions.ManageBilling,
+      OrgPermissionSubjects.Billing
+    );
 
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) {
@@ -602,7 +615,7 @@ export const licenseServiceFactory = ({
 
   const getOrgTaxIds = async ({ orgId, actor, actorId, actorAuthMethod, actorOrgId }: TGetOrgTaxIdDTO) => {
     const { permission } = await permissionService.getOrgPermission(actor, actorId, orgId, actorAuthMethod, actorOrgId);
-    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Read, OrgPermissionSubjects.Billing);
+    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionBillingActions.Read, OrgPermissionSubjects.Billing);
 
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) {
@@ -620,7 +633,10 @@ export const licenseServiceFactory = ({
 
   const addOrgTaxId = async ({ actorId, actor, actorAuthMethod, actorOrgId, orgId, type, value }: TAddOrgTaxIdDTO) => {
     const { permission } = await permissionService.getOrgPermission(actor, actorId, orgId, actorAuthMethod, actorOrgId);
-    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Read, OrgPermissionSubjects.Billing);
+    ForbiddenError.from(permission).throwUnlessCan(
+      OrgPermissionBillingActions.ManageBilling,
+      OrgPermissionSubjects.Billing
+    );
 
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) {
@@ -641,7 +657,10 @@ export const licenseServiceFactory = ({
 
   const delOrgTaxId = async ({ orgId, actor, actorId, actorAuthMethod, actorOrgId, taxId }: TDelOrgTaxIdDTO) => {
     const { permission } = await permissionService.getOrgPermission(actor, actorId, orgId, actorAuthMethod, actorOrgId);
-    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Read, OrgPermissionSubjects.Billing);
+    ForbiddenError.from(permission).throwUnlessCan(
+      OrgPermissionBillingActions.ManageBilling,
+      OrgPermissionSubjects.Billing
+    );
 
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) {
@@ -658,7 +677,7 @@ export const licenseServiceFactory = ({
 
   const getOrgTaxInvoices = async ({ actorId, actor, actorOrgId, actorAuthMethod, orgId }: TOrgInvoiceDTO) => {
     const { permission } = await permissionService.getOrgPermission(actor, actorId, orgId, actorAuthMethod, actorOrgId);
-    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Read, OrgPermissionSubjects.Billing);
+    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionBillingActions.Read, OrgPermissionSubjects.Billing);
 
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) {
@@ -675,7 +694,7 @@ export const licenseServiceFactory = ({
 
   const getOrgLicenses = async ({ orgId, actor, actorId, actorAuthMethod, actorOrgId }: TOrgLicensesDTO) => {
     const { permission } = await permissionService.getOrgPermission(actor, actorId, orgId, actorAuthMethod, actorOrgId);
-    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Read, OrgPermissionSubjects.Billing);
+    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionBillingActions.Read, OrgPermissionSubjects.Billing);
 
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) {

backend/src/ee/services/oidc/oidc-config-service.ts

@@ -44,7 +44,6 @@ import {
   TOidcLoginDTO,
   TUpdateOidcCfgDTO
 } from "./oidc-config-types";
-import { logger } from "@app/lib/logger";
 
 type TOidcConfigServiceFactoryDep = {
   userDAL: Pick<
@@ -700,7 +699,6 @@ export const oidcConfigServiceFactory = ({
       // eslint-disable-next-line @typescript-eslint/no-explicit-any
       (_req: any, tokenSet: TokenSet, cb: any) => {
         const claims = tokenSet.claims();
-        logger.info(`User OIDC claims received for [orgId=${org.id}]`, JSON.stringify(claims, null, 2));
         if (!claims.email || !claims.given_name) {
           throw new BadRequestError({
             message: "Invalid request. Missing email or first name"

backend/src/ee/services/permission/default-roles.ts

@@ -2,7 +2,6 @@ import { AbilityBuilder, createMongoAbility, MongoAbility } from "@casl/ability"
 
 import {
   ProjectPermissionActions,
-  ProjectPermissionApprovalActions,
   ProjectPermissionCertificateActions,
   ProjectPermissionCmekActions,
   ProjectPermissionDynamicSecretActions,
@@ -11,6 +10,7 @@ import {
   ProjectPermissionKmipActions,
   ProjectPermissionMemberActions,
   ProjectPermissionPkiSubscriberActions,
+  ProjectPermissionPkiTemplateActions,
   ProjectPermissionSecretActions,
   ProjectPermissionSecretRotationActions,
   ProjectPermissionSecretSyncActions,
@@ -36,7 +36,6 @@ const buildAdminPermissionRules = () => {
     ProjectPermissionSub.AuditLogs,
     ProjectPermissionSub.IpAllowList,
     ProjectPermissionSub.CertificateAuthorities,
-    ProjectPermissionSub.CertificateTemplates,
     ProjectPermissionSub.PkiAlerts,
     ProjectPermissionSub.PkiCollections,
     ProjectPermissionSub.SshCertificateAuthorities,
@@ -57,12 +56,22 @@ const buildAdminPermissionRules = () => {
 
   can(
     [
-      ProjectPermissionApprovalActions.Read,
-      ProjectPermissionApprovalActions.Edit,
-      ProjectPermissionApprovalActions.Create,
-      ProjectPermissionApprovalActions.Delete,
-      ProjectPermissionApprovalActions.AllowChangeBypass,
-      ProjectPermissionApprovalActions.AllowAccessBypass
+      ProjectPermissionPkiTemplateActions.Read,
+      ProjectPermissionPkiTemplateActions.Edit,
+      ProjectPermissionPkiTemplateActions.Create,
+      ProjectPermissionPkiTemplateActions.Delete,
+      ProjectPermissionPkiTemplateActions.IssueCert,
+      ProjectPermissionPkiTemplateActions.ListCerts
+    ],
+    ProjectPermissionSub.CertificateTemplates
+  );
+
+  can(
+    [
+      ProjectPermissionActions.Read,
+      ProjectPermissionActions.Edit,
+      ProjectPermissionActions.Create,
+      ProjectPermissionActions.Delete
     ],
     ProjectPermissionSub.SecretApproval
   );
@@ -255,7 +264,7 @@ const buildMemberPermissionRules = () => {
     ProjectPermissionSub.SecretImports
   );
 
-  can([ProjectPermissionApprovalActions.Read], ProjectPermissionSub.SecretApproval);
+  can([ProjectPermissionActions.Read], ProjectPermissionSub.SecretApproval);
   can([ProjectPermissionSecretRotationActions.Read], ProjectPermissionSub.SecretRotation);
 
   can([ProjectPermissionActions.Read, ProjectPermissionActions.Create], ProjectPermissionSub.SecretRollback);
@@ -351,7 +360,7 @@ const buildMemberPermissionRules = () => {
     ProjectPermissionSub.Certificates
   );
 
-  can([ProjectPermissionActions.Read], ProjectPermissionSub.CertificateTemplates);
+  can([ProjectPermissionPkiTemplateActions.Read], ProjectPermissionSub.CertificateTemplates);
 
   can([ProjectPermissionActions.Read], ProjectPermissionSub.PkiAlerts);
   can([ProjectPermissionActions.Read], ProjectPermissionSub.PkiCollections);
@@ -403,7 +412,7 @@ const buildViewerPermissionRules = () => {
   can(ProjectPermissionActions.Read, ProjectPermissionSub.SecretFolders);
   can(ProjectPermissionDynamicSecretActions.ReadRootCredential, ProjectPermissionSub.DynamicSecrets);
   can(ProjectPermissionActions.Read, ProjectPermissionSub.SecretImports);
-  can(ProjectPermissionApprovalActions.Read, ProjectPermissionSub.SecretApproval);
+  can(ProjectPermissionActions.Read, ProjectPermissionSub.SecretApproval);
   can(ProjectPermissionActions.Read, ProjectPermissionSub.SecretRollback);
   can(ProjectPermissionSecretRotationActions.Read, ProjectPermissionSub.SecretRotation);
   can(ProjectPermissionMemberActions.Read, ProjectPermissionSub.Member);
@@ -420,6 +429,7 @@ const buildViewerPermissionRules = () => {
   can(ProjectPermissionActions.Read, ProjectPermissionSub.IpAllowList);
   can(ProjectPermissionActions.Read, ProjectPermissionSub.CertificateAuthorities);
   can(ProjectPermissionCertificateActions.Read, ProjectPermissionSub.Certificates);
+  can(ProjectPermissionPkiTemplateActions.Read, ProjectPermissionSub.CertificateTemplates);
   can(ProjectPermissionCmekActions.Read, ProjectPermissionSub.Cmek);
   can(ProjectPermissionActions.Read, ProjectPermissionSub.SshCertificates);
   can(ProjectPermissionActions.Read, ProjectPermissionSub.SshCertificateTemplates);

backend/src/ee/services/permission/org-permission.ts

@@ -67,6 +67,11 @@ export enum OrgPermissionGroupActions {
   RemoveMembers = "remove-members"
 }
 
+export enum OrgPermissionBillingActions {
+  Read = "read",
+  ManageBilling = "manage-billing"
+}
+
 export enum OrgPermissionSubjects {
   Workspace = "workspace",
   Role = "role",
@@ -107,7 +112,7 @@ export type OrgPermissionSet =
   | [OrgPermissionActions, OrgPermissionSubjects.Ldap]
   | [OrgPermissionGroupActions, OrgPermissionSubjects.Groups]
   | [OrgPermissionActions, OrgPermissionSubjects.SecretScanning]
-  | [OrgPermissionActions, OrgPermissionSubjects.Billing]
+  | [OrgPermissionBillingActions, OrgPermissionSubjects.Billing]
   | [OrgPermissionIdentityActions, OrgPermissionSubjects.Identity]
   | [OrgPermissionActions, OrgPermissionSubjects.Kms]
   | [OrgPermissionActions, OrgPermissionSubjects.AuditLogs]
@@ -298,10 +303,8 @@ const buildAdminPermission = () => {
   can(OrgPermissionGroupActions.AddMembers, OrgPermissionSubjects.Groups);
   can(OrgPermissionGroupActions.RemoveMembers, OrgPermissionSubjects.Groups);
 
-  can(OrgPermissionActions.Read, OrgPermissionSubjects.Billing);
-  can(OrgPermissionActions.Create, OrgPermissionSubjects.Billing);
-  can(OrgPermissionActions.Edit, OrgPermissionSubjects.Billing);
-  can(OrgPermissionActions.Delete, OrgPermissionSubjects.Billing);
+  can(OrgPermissionBillingActions.Read, OrgPermissionSubjects.Billing);
+  can(OrgPermissionBillingActions.ManageBilling, OrgPermissionSubjects.Billing);
 
   can(OrgPermissionIdentityActions.Read, OrgPermissionSubjects.Identity);
   can(OrgPermissionIdentityActions.Create, OrgPermissionSubjects.Identity);
@@ -362,7 +365,7 @@ const buildMemberPermission = () => {
   can(OrgPermissionGroupActions.Read, OrgPermissionSubjects.Groups);
   can(OrgPermissionActions.Read, OrgPermissionSubjects.Role);
   can(OrgPermissionActions.Read, OrgPermissionSubjects.Settings);
-  can(OrgPermissionActions.Read, OrgPermissionSubjects.Billing);
+  can(OrgPermissionBillingActions.Read, OrgPermissionSubjects.Billing);
   can(OrgPermissionActions.Read, OrgPermissionSubjects.IncidentAccount);
 
   can(OrgPermissionActions.Read, OrgPermissionSubjects.SecretScanning);

backend/src/ee/services/permission/project-permission.ts

@@ -34,15 +34,6 @@ export enum ProjectPermissionSecretActions {
   Delete = "delete"
 }
 
-export enum ProjectPermissionApprovalActions {
-  Read = "read",
-  Create = "create",
-  Edit = "edit",
-  Delete = "delete",
-  AllowChangeBypass = "allow-change-bypass",
-  AllowAccessBypass = "allow-access-bypass"
-}
-
 export enum ProjectPermissionCmekActions {
   Read = "read",
   Create = "create",
@@ -96,6 +87,15 @@ export enum ProjectPermissionSshHostActions {
   IssueHostCert = "issue-host-cert"
 }
 
+export enum ProjectPermissionPkiTemplateActions {
+  Read = "read",
+  Create = "create",
+  Edit = "edit",
+  Delete = "delete",
+  IssueCert = "issue-cert",
+  ListCerts = "list-certs"
+}
+
 export enum ProjectPermissionPkiSubscriberActions {
   Read = "read",
   Create = "create",
@@ -209,6 +209,11 @@ export type SshHostSubjectFields = {
   hostname: string;
 };
 
+export type PkiTemplateSubjectFields = {
+  name: string;
+  // (dangtony98): consider adding [commonName] as a subject field in the future
+};
+
 export type PkiSubscriberSubjectFields = {
   name: string;
   // (dangtony98): consider adding [commonName] as a subject field in the future
@@ -251,7 +256,7 @@ export type ProjectPermissionSet =
   | [ProjectPermissionActions, ProjectPermissionSub.IpAllowList]
   | [ProjectPermissionActions, ProjectPermissionSub.Settings]
   | [ProjectPermissionActions, ProjectPermissionSub.ServiceTokens]
-  | [ProjectPermissionApprovalActions, ProjectPermissionSub.SecretApproval]
+  | [ProjectPermissionActions, ProjectPermissionSub.SecretApproval]
   | [
       ProjectPermissionSecretRotationActions,
       (
@@ -265,7 +270,13 @@ export type ProjectPermissionSet =
     ]
   | [ProjectPermissionActions, ProjectPermissionSub.CertificateAuthorities]
   | [ProjectPermissionCertificateActions, ProjectPermissionSub.Certificates]
-  | [ProjectPermissionActions, ProjectPermissionSub.CertificateTemplates]
+  | [
+      ProjectPermissionPkiTemplateActions,
+      (
+        | ProjectPermissionSub.CertificateTemplates
+        | (ForcedSubject<ProjectPermissionSub.CertificateTemplates> & PkiTemplateSubjectFields)
+      )
+    ]
   | [ProjectPermissionActions, ProjectPermissionSub.SshCertificateAuthorities]
   | [ProjectPermissionActions, ProjectPermissionSub.SshCertificates]
   | [ProjectPermissionActions, ProjectPermissionSub.SshCertificateTemplates]
@@ -445,10 +456,25 @@ const PkiSubscriberConditionSchema = z
   })
   .partial();
 
+const PkiTemplateConditionSchema = z
+  .object({
+    name: z.union([
+      z.string(),
+      z
+        .object({
+          [PermissionConditionOperators.$EQ]: PermissionConditionSchema[PermissionConditionOperators.$EQ],
+          [PermissionConditionOperators.$GLOB]: PermissionConditionSchema[PermissionConditionOperators.$GLOB],
+          [PermissionConditionOperators.$IN]: PermissionConditionSchema[PermissionConditionOperators.$IN]
+        })
+        .partial()
+    ])
+  })
+  .partial();
+
 const GeneralPermissionSchema = [
   z.object({
     subject: z.literal(ProjectPermissionSub.SecretApproval).describe("The entity this permission pertains to."),
-    action: CASL_ACTION_SCHEMA_NATIVE_ENUM(ProjectPermissionApprovalActions).describe(
+    action: CASL_ACTION_SCHEMA_NATIVE_ENUM(ProjectPermissionActions).describe(
       "Describe what action an entity can take."
     )
   }),
@@ -536,12 +562,6 @@ const GeneralPermissionSchema = [
       "Describe what action an entity can take."
     )
   }),
-  z.object({
-    subject: z.literal(ProjectPermissionSub.CertificateTemplates).describe("The entity this permission pertains to."),
-    action: CASL_ACTION_SCHEMA_NATIVE_ENUM(ProjectPermissionActions).describe(
-      "Describe what action an entity can take."
-    )
-  }),
   z.object({
     subject: z
       .literal(ProjectPermissionSub.SshCertificateAuthorities)
@@ -719,6 +739,16 @@ export const ProjectPermissionV2Schema = z.discriminatedUnion("subject", [
       "When specified, only matching conditions will be allowed to access given resource."
     ).optional()
   }),
+  z.object({
+    subject: z.literal(ProjectPermissionSub.CertificateTemplates).describe("The entity this permission pertains to."),
+    action: CASL_ACTION_SCHEMA_NATIVE_ENUM(ProjectPermissionPkiTemplateActions).describe(
+      "Describe what action an entity can take."
+    ),
+    inverted: z.boolean().optional().describe("Whether rule allows or forbids."),
+    conditions: PkiTemplateConditionSchema.describe(
+      "When specified, only matching conditions will be allowed to access given resource."
+    ).optional()
+  }),
   z.object({
     subject: z.literal(ProjectPermissionSub.SecretRotation).describe("The entity this permission pertains to."),
     inverted: z.boolean().optional().describe("Whether rule allows or forbids."),
@@ -729,6 +759,7 @@ export const ProjectPermissionV2Schema = z.discriminatedUnion("subject", [
       "When specified, only matching conditions will be allowed to access given resource."
     ).optional()
   }),
+
   ...GeneralPermissionSchema
 ]);
 

backend/src/ee/services/project-user-additional-privilege/project-user-additional-privilege-service.ts

@@ -9,6 +9,7 @@ import { UnpackedPermissionSchema } from "@app/server/routes/sanitizedSchema/per
 import { ActorType } from "@app/services/auth/auth-type";
 import { TProjectMembershipDALFactory } from "@app/services/project-membership/project-membership-dal";
 
+import { TAccessApprovalRequestDALFactory } from "../access-approval-request/access-approval-request-dal";
 import { constructPermissionErrorMessage, validatePrivilegeChangeOperation } from "../permission/permission-fns";
 import { TPermissionServiceFactory } from "../permission/permission-service";
 import {
@@ -16,6 +17,7 @@ import {
   ProjectPermissionSet,
   ProjectPermissionSub
 } from "../permission/project-permission";
+import { ApprovalStatus } from "../secret-approval-request/secret-approval-request-types";
 import { TProjectUserAdditionalPrivilegeDALFactory } from "./project-user-additional-privilege-dal";
 import {
   ProjectUserAdditionalPrivilegeTemporaryMode,
@@ -30,6 +32,7 @@ type TProjectUserAdditionalPrivilegeServiceFactoryDep = {
   projectUserAdditionalPrivilegeDAL: TProjectUserAdditionalPrivilegeDALFactory;
   projectMembershipDAL: Pick<TProjectMembershipDALFactory, "findById" | "findOne">;
   permissionService: Pick<TPermissionServiceFactory, "getProjectPermission">;
+  accessApprovalRequestDAL: Pick<TAccessApprovalRequestDALFactory, "update">;
 };
 
 export type TProjectUserAdditionalPrivilegeServiceFactory = ReturnType<
@@ -44,7 +47,8 @@ const unpackPermissions = (permissions: unknown) =>
 export const projectUserAdditionalPrivilegeServiceFactory = ({
   projectUserAdditionalPrivilegeDAL,
   projectMembershipDAL,
-  permissionService
+  permissionService,
+  accessApprovalRequestDAL
 }: TProjectUserAdditionalPrivilegeServiceFactoryDep) => {
   const create = async ({
     slug,
@@ -279,6 +283,15 @@ export const projectUserAdditionalPrivilegeServiceFactory = ({
     });
     ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionMemberActions.Edit, ProjectPermissionSub.Member);
 
+    await accessApprovalRequestDAL.update(
+      {
+        privilegeId: userPrivilege.id
+      },
+      {
+        privilegeDeletedAt: new Date(),
+        status: ApprovalStatus.REJECTED
+      }
+    );
     const deletedPrivilege = await projectUserAdditionalPrivilegeDAL.deleteById(userPrivilege.id);
     return {
       ...deletedPrivilege,

backend/src/ee/services/secret-approval-policy/secret-approval-policy-approver-dal.ts

@@ -8,3 +8,10 @@ export const secretApprovalPolicyApproverDALFactory = (db: TDbClient) => {
   const sapApproverOrm = ormify(db, TableName.SecretApprovalPolicyApprover);
   return sapApproverOrm;
 };
+
+export type TSecretApprovalPolicyBypasserDALFactory = ReturnType<typeof secretApprovalPolicyBypasserDALFactory>;
+
+export const secretApprovalPolicyBypasserDALFactory = (db: TDbClient) => {
+  const sapBypasserOrm = ormify(db, TableName.SecretApprovalPolicyBypasser);
+  return sapBypasserOrm;
+};

backend/src/ee/services/secret-approval-policy/secret-approval-policy-dal.ts

@@ -1,11 +1,17 @@
 import { Knex } from "knex";
 
 import { TDbClient } from "@app/db";
-import { SecretApprovalPoliciesSchema, TableName, TSecretApprovalPolicies, TUsers } from "@app/db/schemas";
+import {
+  SecretApprovalPoliciesSchema,
+  TableName,
+  TSecretApprovalPolicies,
+  TUserGroupMembership,
+  TUsers
+} from "@app/db/schemas";
 import { DatabaseError } from "@app/lib/errors";
 import { buildFindFilter, ormify, selectAllTableCols, sqlNestRelationships, TFindFilter } from "@app/lib/knex";
 
-import { ApproverType } from "../access-approval-policy/access-approval-policy-types";
+import { ApproverType, BypasserType } from "../access-approval-policy/access-approval-policy-types";
 
 export type TSecretApprovalPolicyDALFactory = ReturnType<typeof secretApprovalPolicyDALFactory>;
 
@@ -43,6 +49,22 @@ export const secretApprovalPolicyDALFactory = (db: TDbClient) => {
         `${TableName.SecretApprovalPolicyApprover}.approverUserId`,
         "secretApprovalPolicyApproverUser.id"
       )
+      // Bypasser
+      .leftJoin(
+        TableName.SecretApprovalPolicyBypasser,
+        `${TableName.SecretApprovalPolicy}.id`,
+        `${TableName.SecretApprovalPolicyBypasser}.policyId`
+      )
+      .leftJoin<TUserGroupMembership>(
+        db(TableName.UserGroupMembership).as("bypasserUserGroupMembership"),
+        `${TableName.SecretApprovalPolicyBypasser}.bypasserGroupId`,
+        `bypasserUserGroupMembership.groupId`
+      )
+      .leftJoin<TUsers>(
+        db(TableName.Users).as("secretApprovalPolicyBypasserUser"),
+        `${TableName.SecretApprovalPolicyBypasser}.bypasserUserId`,
+        "secretApprovalPolicyBypasserUser.id"
+      )
       .leftJoin<TUsers>(TableName.Users, `${TableName.UserGroupMembership}.userId`, `${TableName.Users}.id`)
       .select(
         tx.ref("id").withSchema("secretApprovalPolicyApproverUser").as("approverUserId"),
@@ -58,6 +80,20 @@ export const secretApprovalPolicyDALFactory = (db: TDbClient) => {
         tx.ref("firstName").withSchema(TableName.Users).as("approverGroupFirstName"),
         tx.ref("lastName").withSchema(TableName.Users).as("approverGroupLastName")
       )
+      .select(
+        tx.ref("id").withSchema("secretApprovalPolicyBypasserUser").as("bypasserUserId"),
+        tx.ref("email").withSchema("secretApprovalPolicyBypasserUser").as("bypasserEmail"),
+        tx.ref("firstName").withSchema("secretApprovalPolicyBypasserUser").as("bypasserFirstName"),
+        tx.ref("username").withSchema("secretApprovalPolicyBypasserUser").as("bypasserUsername"),
+        tx.ref("lastName").withSchema("secretApprovalPolicyBypasserUser").as("bypasserLastName")
+      )
+      .select(
+        tx.ref("bypasserGroupId").withSchema(TableName.SecretApprovalPolicyBypasser),
+        tx.ref("userId").withSchema("bypasserUserGroupMembership").as("bypasserGroupUserId"),
+        tx.ref("email").withSchema(TableName.Users).as("bypasserGroupEmail"),
+        tx.ref("firstName").withSchema(TableName.Users).as("bypasserGroupFirstName"),
+        tx.ref("lastName").withSchema(TableName.Users).as("bypasserGroupLastName")
+      )
       .select(
         tx.ref("name").withSchema(TableName.Environment).as("envName"),
         tx.ref("slug").withSchema(TableName.Environment).as("envSlug"),
@@ -143,7 +179,7 @@ export const secretApprovalPolicyDALFactory = (db: TDbClient) => {
             label: "approvers" as const,
             mapper: ({ approverUserId: id, approverUsername }) => ({
               type: ApproverType.User,
-              name: approverUsername,
+              username: approverUsername,
               id
             })
           },
@@ -155,6 +191,23 @@ export const secretApprovalPolicyDALFactory = (db: TDbClient) => {
               id
             })
           },
+          {
+            key: "bypasserUserId",
+            label: "bypassers" as const,
+            mapper: ({ bypasserUserId: id, bypasserUsername }) => ({
+              type: BypasserType.User,
+              username: bypasserUsername,
+              id
+            })
+          },
+          {
+            key: "bypasserGroupId",
+            label: "bypassers" as const,
+            mapper: ({ bypasserGroupId: id }) => ({
+              type: BypasserType.Group,
+              id
+            })
+          },
           {
             key: "approverUserId",
             label: "userApprovers" as const,

backend/src/ee/services/secret-approval-policy/secret-approval-policy-service.ts

@@ -3,18 +3,21 @@ import picomatch from "picomatch";
 
 import { ActionProjectType } from "@app/db/schemas";
 import { TPermissionServiceFactory } from "@app/ee/services/permission/permission-service";
-import { ProjectPermissionApprovalActions, ProjectPermissionSub } from "@app/ee/services/permission/project-permission";
+import { ProjectPermissionActions, ProjectPermissionSub } from "@app/ee/services/permission/project-permission";
 import { BadRequestError, NotFoundError } from "@app/lib/errors";
 import { removeTrailingSlash } from "@app/lib/fn";
 import { containsGlobPatterns } from "@app/lib/picomatch";
 import { TProjectEnvDALFactory } from "@app/services/project-env/project-env-dal";
 import { TUserDALFactory } from "@app/services/user/user-dal";
 
-import { ApproverType } from "../access-approval-policy/access-approval-policy-types";
+import { ApproverType, BypasserType } from "../access-approval-policy/access-approval-policy-types";
 import { TLicenseServiceFactory } from "../license/license-service";
 import { TSecretApprovalRequestDALFactory } from "../secret-approval-request/secret-approval-request-dal";
 import { RequestState } from "../secret-approval-request/secret-approval-request-types";
-import { TSecretApprovalPolicyApproverDALFactory } from "./secret-approval-policy-approver-dal";
+import {
+  TSecretApprovalPolicyApproverDALFactory,
+  TSecretApprovalPolicyBypasserDALFactory
+} from "./secret-approval-policy-approver-dal";
 import { TSecretApprovalPolicyDALFactory } from "./secret-approval-policy-dal";
 import {
   TCreateSapDTO,
@@ -36,6 +39,7 @@ type TSecretApprovalPolicyServiceFactoryDep = {
   projectEnvDAL: Pick<TProjectEnvDALFactory, "findOne">;
   userDAL: Pick<TUserDALFactory, "find">;
   secretApprovalPolicyApproverDAL: TSecretApprovalPolicyApproverDALFactory;
+  secretApprovalPolicyBypasserDAL: TSecretApprovalPolicyBypasserDALFactory;
   licenseService: Pick<TLicenseServiceFactory, "getPlan">;
   secretApprovalRequestDAL: Pick<TSecretApprovalRequestDALFactory, "update">;
 };
@@ -46,6 +50,7 @@ export const secretApprovalPolicyServiceFactory = ({
   secretApprovalPolicyDAL,
   permissionService,
   secretApprovalPolicyApproverDAL,
+  secretApprovalPolicyBypasserDAL,
   projectEnvDAL,
   userDAL,
   licenseService,
@@ -59,6 +64,7 @@ export const secretApprovalPolicyServiceFactory = ({
     actorAuthMethod,
     approvals,
     approvers,
+    bypassers,
     projectId,
     secretPath,
     environment,
@@ -74,7 +80,7 @@ export const secretApprovalPolicyServiceFactory = ({
       .filter(Boolean) as string[];
 
     const userApproverNames = approvers
-      .map((approver) => (approver.type === ApproverType.User ? approver.name : undefined))
+      .map((approver) => (approver.type === ApproverType.User ? approver.username : undefined))
       .filter(Boolean) as string[];
 
     if (!groupApprovers.length && approvals > approvers.length)
@@ -89,7 +95,7 @@ export const secretApprovalPolicyServiceFactory = ({
       actionProjectType: ActionProjectType.SecretManager
     });
     ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionApprovalActions.Create,
+      ProjectPermissionActions.Create,
       ProjectPermissionSub.SecretApproval
     );
 
@@ -107,6 +113,44 @@ export const secretApprovalPolicyServiceFactory = ({
         message: `Environment with slug '${environment}' not found in project with ID ${projectId}`
       });
 
+    let groupBypassers: string[] = [];
+    let bypasserUserIds: string[] = [];
+
+    if (bypassers && bypassers.length) {
+      groupBypassers = bypassers
+        .filter((bypasser) => bypasser.type === BypasserType.Group)
+        .map((bypasser) => bypasser.id) as string[];
+
+      const userBypassers = bypassers
+        .filter((bypasser) => bypasser.type === BypasserType.User)
+        .map((bypasser) => bypasser.id)
+        .filter(Boolean) as string[];
+
+      const userBypasserNames = bypassers
+        .map((bypasser) => (bypasser.type === BypasserType.User ? bypasser.username : undefined))
+        .filter(Boolean) as string[];
+
+      bypasserUserIds = userBypassers;
+      if (userBypasserNames.length) {
+        const bypasserUsers = await userDAL.find({
+          $in: {
+            username: userBypasserNames
+          }
+        });
+
+        const bypasserNamesFromDb = bypasserUsers.map((user) => user.username);
+        const invalidUsernames = userBypasserNames.filter((username) => !bypasserNamesFromDb.includes(username));
+
+        if (invalidUsernames.length) {
+          throw new BadRequestError({
+            message: `Invalid bypasser user: ${invalidUsernames.join(", ")}`
+          });
+        }
+
+        bypasserUserIds = bypasserUserIds.concat(bypasserUsers.map((user) => user.id));
+      }
+    }
+
     const secretApproval = await secretApprovalPolicyDAL.transaction(async (tx) => {
       const doc = await secretApprovalPolicyDAL.create(
         {
@@ -158,6 +202,27 @@ export const secretApprovalPolicyServiceFactory = ({
         })),
         tx
       );
+
+      if (bypasserUserIds.length) {
+        await secretApprovalPolicyBypasserDAL.insertMany(
+          bypasserUserIds.map((userId) => ({
+            bypasserUserId: userId,
+            policyId: doc.id
+          })),
+          tx
+        );
+      }
+
+      if (groupBypassers.length) {
+        await secretApprovalPolicyBypasserDAL.insertMany(
+          groupBypassers.map((groupId) => ({
+            bypasserGroupId: groupId,
+            policyId: doc.id
+          })),
+          tx
+        );
+      }
+
       return doc;
     });
 
@@ -166,6 +231,7 @@ export const secretApprovalPolicyServiceFactory = ({
 
   const updateSecretApprovalPolicy = async ({
     approvers,
+    bypassers,
     secretPath,
     name,
     actorId,
@@ -186,7 +252,7 @@ export const secretApprovalPolicyServiceFactory = ({
       .filter(Boolean) as string[];
 
     const userApproverNames = approvers
-      .map((approver) => (approver.type === ApproverType.User ? approver.name : undefined))
+      .map((approver) => (approver.type === ApproverType.User ? approver.username : undefined))
       .filter(Boolean) as string[];
 
     const secretApprovalPolicy = await secretApprovalPolicyDAL.findById(secretPolicyId);
@@ -204,10 +270,7 @@ export const secretApprovalPolicyServiceFactory = ({
       actorOrgId,
       actionProjectType: ActionProjectType.SecretManager
     });
-    ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionApprovalActions.Edit,
-      ProjectPermissionSub.SecretApproval
-    );
+    ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Edit, ProjectPermissionSub.SecretApproval);
 
     const plan = await licenseService.getPlan(actorOrgId);
     if (!plan.secretApproval) {
@@ -217,6 +280,44 @@ export const secretApprovalPolicyServiceFactory = ({
       });
     }
 
+    let groupBypassers: string[] = [];
+    let bypasserUserIds: string[] = [];
+
+    if (bypassers && bypassers.length) {
+      groupBypassers = bypassers
+        .filter((bypasser) => bypasser.type === BypasserType.Group)
+        .map((bypasser) => bypasser.id) as string[];
+
+      const userBypassers = bypassers
+        .filter((bypasser) => bypasser.type === BypasserType.User)
+        .map((bypasser) => bypasser.id)
+        .filter(Boolean) as string[];
+
+      const userBypasserNames = bypassers
+        .map((bypasser) => (bypasser.type === BypasserType.User ? bypasser.username : undefined))
+        .filter(Boolean) as string[];
+
+      bypasserUserIds = userBypassers;
+      if (userBypasserNames.length) {
+        const bypasserUsers = await userDAL.find({
+          $in: {
+            username: userBypasserNames
+          }
+        });
+
+        const bypasserNamesFromDb = bypasserUsers.map((user) => user.username);
+        const invalidUsernames = userBypasserNames.filter((username) => !bypasserNamesFromDb.includes(username));
+
+        if (invalidUsernames.length) {
+          throw new BadRequestError({
+            message: `Invalid bypasser user: ${invalidUsernames.join(", ")}`
+          });
+        }
+
+        bypasserUserIds = bypasserUserIds.concat(bypasserUsers.map((user) => user.id));
+      }
+    }
+
     const updatedSap = await secretApprovalPolicyDAL.transaction(async (tx) => {
       const doc = await secretApprovalPolicyDAL.updateById(
         secretApprovalPolicy.id,
@@ -275,6 +376,28 @@ export const secretApprovalPolicyServiceFactory = ({
         );
       }
 
+      await secretApprovalPolicyBypasserDAL.delete({ policyId: doc.id }, tx);
+
+      if (bypasserUserIds.length) {
+        await secretApprovalPolicyBypasserDAL.insertMany(
+          bypasserUserIds.map((userId) => ({
+            bypasserUserId: userId,
+            policyId: doc.id
+          })),
+          tx
+        );
+      }
+
+      if (groupBypassers.length) {
+        await secretApprovalPolicyBypasserDAL.insertMany(
+          groupBypassers.map((groupId) => ({
+            bypasserGroupId: groupId,
+            policyId: doc.id
+          })),
+          tx
+        );
+      }
+
       return doc;
     });
     return {
@@ -304,7 +427,7 @@ export const secretApprovalPolicyServiceFactory = ({
       actionProjectType: ActionProjectType.SecretManager
     });
     ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionApprovalActions.Delete,
+      ProjectPermissionActions.Delete,
       ProjectPermissionSub.SecretApproval
     );
 
@@ -343,10 +466,7 @@ export const secretApprovalPolicyServiceFactory = ({
       actorOrgId,
       actionProjectType: ActionProjectType.SecretManager
     });
-    ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionApprovalActions.Read,
-      ProjectPermissionSub.SecretApproval
-    );
+    ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Read, ProjectPermissionSub.SecretApproval);
 
     const sapPolicies = await secretApprovalPolicyDAL.find({ projectId, deletedAt: null });
     return sapPolicies;
@@ -419,10 +539,7 @@ export const secretApprovalPolicyServiceFactory = ({
       actionProjectType: ActionProjectType.SecretManager
     });
 
-    ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionApprovalActions.Read,
-      ProjectPermissionSub.SecretApproval
-    );
+    ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Read, ProjectPermissionSub.SecretApproval);
 
     return sapPolicy;
   };

backend/src/ee/services/secret-approval-policy/secret-approval-policy-types.ts

@@ -1,12 +1,16 @@
 import { EnforcementLevel, TProjectPermission } from "@app/lib/types";
 
-import { ApproverType } from "../access-approval-policy/access-approval-policy-types";
+import { ApproverType, BypasserType } from "../access-approval-policy/access-approval-policy-types";
 
 export type TCreateSapDTO = {
   approvals: number;
   secretPath?: string | null;
   environment: string;
-  approvers: ({ type: ApproverType.Group; id: string } | { type: ApproverType.User; id?: string; name?: string })[];
+  approvers: ({ type: ApproverType.Group; id: string } | { type: ApproverType.User; id?: string; username?: string })[];
+  bypassers?: (
+    | { type: BypasserType.Group; id: string }
+    | { type: BypasserType.User; id?: string; username?: string }
+  )[];
   projectId: string;
   name: string;
   enforcementLevel: EnforcementLevel;
@@ -17,7 +21,11 @@ export type TUpdateSapDTO = {
   secretPolicyId: string;
   approvals?: number;
   secretPath?: string | null;
-  approvers: ({ type: ApproverType.Group; id: string } | { type: ApproverType.User; id?: string; name?: string })[];
+  approvers: ({ type: ApproverType.Group; id: string } | { type: ApproverType.User; id?: string; username?: string })[];
+  bypassers?: (
+    | { type: BypasserType.Group; id: string }
+    | { type: BypasserType.User; id?: string; username?: string }
+  )[];
   name?: string;
   enforcementLevel?: EnforcementLevel;
   allowedSelfApprovals?: boolean;

backend/src/ee/services/secret-approval-request/secret-approval-request-dal.ts

@@ -6,6 +6,7 @@ import {
   TableName,
   TSecretApprovalRequests,
   TSecretApprovalRequestsSecrets,
+  TUserGroupMembership,
   TUsers
 } from "@app/db/schemas";
 import { DatabaseError } from "@app/lib/errors";
@@ -58,16 +59,36 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
         `${TableName.SecretApprovalPolicyApprover}.approverUserId`,
         "secretApprovalPolicyApproverUser.id"
       )
-      .leftJoin(
-        TableName.UserGroupMembership,
+      .leftJoin<TUserGroupMembership>(
+        db(TableName.UserGroupMembership).as("approverUserGroupMembership"),
         `${TableName.SecretApprovalPolicyApprover}.approverGroupId`,
-        `${TableName.UserGroupMembership}.groupId`
+        `approverUserGroupMembership.groupId`
       )
       .leftJoin<TUsers>(
         db(TableName.Users).as("secretApprovalPolicyGroupApproverUser"),
-        `${TableName.UserGroupMembership}.userId`,
+        `approverUserGroupMembership.userId`,
         `secretApprovalPolicyGroupApproverUser.id`
       )
+      .leftJoin(
+        TableName.SecretApprovalPolicyBypasser,
+        `${TableName.SecretApprovalPolicy}.id`,
+        `${TableName.SecretApprovalPolicyBypasser}.policyId`
+      )
+      .leftJoin<TUsers>(
+        db(TableName.Users).as("secretApprovalPolicyBypasserUser"),
+        `${TableName.SecretApprovalPolicyBypasser}.bypasserUserId`,
+        "secretApprovalPolicyBypasserUser.id"
+      )
+      .leftJoin<TUserGroupMembership>(
+        db(TableName.UserGroupMembership).as("bypasserUserGroupMembership"),
+        `${TableName.SecretApprovalPolicyBypasser}.bypasserGroupId`,
+        `bypasserUserGroupMembership.groupId`
+      )
+      .leftJoin<TUsers>(
+        db(TableName.Users).as("secretApprovalPolicyGroupBypasserUser"),
+        `bypasserUserGroupMembership.userId`,
+        `secretApprovalPolicyGroupBypasserUser.id`
+      )
       .leftJoin(
         TableName.SecretApprovalRequestReviewer,
         `${TableName.SecretApprovalRequest}.id`,
@@ -81,7 +102,7 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
       .select(selectAllTableCols(TableName.SecretApprovalRequest))
       .select(
         tx.ref("approverUserId").withSchema(TableName.SecretApprovalPolicyApprover),
-        tx.ref("userId").withSchema(TableName.UserGroupMembership).as("approverGroupUserId"),
+        tx.ref("userId").withSchema("approverUserGroupMembership").as("approverGroupUserId"),
         tx.ref("email").withSchema("secretApprovalPolicyApproverUser").as("approverEmail"),
         tx.ref("email").withSchema("secretApprovalPolicyGroupApproverUser").as("approverGroupEmail"),
         tx.ref("username").withSchema("secretApprovalPolicyApproverUser").as("approverUsername"),
@@ -90,6 +111,20 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
         tx.ref("firstName").withSchema("secretApprovalPolicyGroupApproverUser").as("approverGroupFirstName"),
         tx.ref("lastName").withSchema("secretApprovalPolicyApproverUser").as("approverLastName"),
         tx.ref("lastName").withSchema("secretApprovalPolicyGroupApproverUser").as("approverGroupLastName"),
+
+        // Bypasser fields
+        tx.ref("bypasserUserId").withSchema(TableName.SecretApprovalPolicyBypasser),
+        tx.ref("bypasserGroupId").withSchema(TableName.SecretApprovalPolicyBypasser),
+        tx.ref("userId").withSchema("bypasserUserGroupMembership").as("bypasserGroupUserId"),
+        tx.ref("email").withSchema("secretApprovalPolicyBypasserUser").as("bypasserEmail"),
+        tx.ref("email").withSchema("secretApprovalPolicyGroupBypasserUser").as("bypasserGroupEmail"),
+        tx.ref("username").withSchema("secretApprovalPolicyBypasserUser").as("bypasserUsername"),
+        tx.ref("username").withSchema("secretApprovalPolicyGroupBypasserUser").as("bypasserGroupUsername"),
+        tx.ref("firstName").withSchema("secretApprovalPolicyBypasserUser").as("bypasserFirstName"),
+        tx.ref("firstName").withSchema("secretApprovalPolicyGroupBypasserUser").as("bypasserGroupFirstName"),
+        tx.ref("lastName").withSchema("secretApprovalPolicyBypasserUser").as("bypasserLastName"),
+        tx.ref("lastName").withSchema("secretApprovalPolicyGroupBypasserUser").as("bypasserGroupLastName"),
+
         tx.ref("email").withSchema("statusChangedByUser").as("statusChangedByUserEmail"),
         tx.ref("username").withSchema("statusChangedByUser").as("statusChangedByUserUsername"),
         tx.ref("firstName").withSchema("statusChangedByUser").as("statusChangedByUserFirstName"),
@@ -121,7 +156,7 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
     try {
       const sql = findQuery({ [`${TableName.SecretApprovalRequest}.id` as "id"]: id }, tx || db.replicaNode());
       const docs = await sql;
-      const formatedDoc = sqlNestRelationships({
+      const formattedDoc = sqlNestRelationships({
         data: docs,
         key: "id",
         parentMapper: (el) => ({
@@ -203,13 +238,51 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
               lastName,
               username
             })
+          },
+          {
+            key: "bypasserUserId",
+            label: "bypassers" as const,
+            mapper: ({
+              bypasserUserId: userId,
+              bypasserEmail: email,
+              bypasserUsername: username,
+              bypasserLastName: lastName,
+              bypasserFirstName: firstName
+            }) => ({
+              userId,
+              email,
+              firstName,
+              lastName,
+              username
+            })
+          },
+          {
+            key: "bypasserGroupUserId",
+            label: "bypassers" as const,
+            mapper: ({
+              bypasserGroupUserId: userId,
+              bypasserGroupEmail: email,
+              bypasserGroupUsername: username,
+              bypasserGroupLastName: lastName,
+              bypasserGroupFirstName: firstName
+            }) => ({
+              userId,
+              email,
+              firstName,
+              lastName,
+              username
+            })
           }
         ]
       });
-      if (!formatedDoc?.[0]) return;
+      if (!formattedDoc?.[0]) return;
       return {
-        ...formatedDoc[0],
-        policy: { ...formatedDoc[0].policy, approvers: formatedDoc[0].approvers }
+        ...formattedDoc[0],
+        policy: {
+          ...formattedDoc[0].policy,
+          approvers: formattedDoc[0].approvers,
+          bypassers: formattedDoc[0].bypassers
+        }
       };
     } catch (error) {
       throw new DatabaseError({ error, name: "FindByIdSAR" });
@@ -291,6 +364,16 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
           `${TableName.SecretApprovalPolicyApprover}.approverGroupId`,
           `${TableName.UserGroupMembership}.groupId`
         )
+        .leftJoin(
+          TableName.SecretApprovalPolicyBypasser,
+          `${TableName.SecretApprovalPolicy}.id`,
+          `${TableName.SecretApprovalPolicyBypasser}.policyId`
+        )
+        .leftJoin<TUserGroupMembership>(
+          db(TableName.UserGroupMembership).as("bypasserUserGroupMembership"),
+          `${TableName.SecretApprovalPolicyBypasser}.bypasserGroupId`,
+          `bypasserUserGroupMembership.groupId`
+        )
         .join<TUsers>(
           db(TableName.Users).as("committerUser"),
           `${TableName.SecretApprovalRequest}.committerUserId`,
@@ -342,6 +425,11 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
           db.ref("approvals").withSchema(TableName.SecretApprovalPolicy).as("policyApprovals"),
           db.ref("approverUserId").withSchema(TableName.SecretApprovalPolicyApprover),
           db.ref("userId").withSchema(TableName.UserGroupMembership).as("approverGroupUserId"),
+
+          // Bypasser fields
+          db.ref("bypasserUserId").withSchema(TableName.SecretApprovalPolicyBypasser),
+          db.ref("userId").withSchema("bypasserUserGroupMembership").as("bypasserGroupUserId"),
+
           db.ref("email").withSchema("committerUser").as("committerUserEmail"),
           db.ref("username").withSchema("committerUser").as("committerUserUsername"),
           db.ref("firstName").withSchema("committerUser").as("committerUserFirstName"),
@@ -355,7 +443,7 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
         .from<Awaited<typeof query>[number]>("w")
         .where("w.rank", ">=", offset)
         .andWhere("w.rank", "<", offset + limit);
-      const formatedDoc = sqlNestRelationships({
+      const formattedDoc = sqlNestRelationships({
         data: docs,
         key: "id",
         parentMapper: (el) => ({
@@ -403,12 +491,22 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
             key: "approverGroupUserId",
             label: "approvers" as const,
             mapper: ({ approverGroupUserId }) => ({ userId: approverGroupUserId })
+          },
+          {
+            key: "bypasserUserId",
+            label: "bypassers" as const,
+            mapper: ({ bypasserUserId }) => ({ userId: bypasserUserId })
+          },
+          {
+            key: "bypasserGroupUserId",
+            label: "bypassers" as const,
+            mapper: ({ bypasserGroupUserId }) => ({ userId: bypasserGroupUserId })
           }
         ]
       });
-      return formatedDoc.map((el) => ({
+      return formattedDoc.map((el) => ({
         ...el,
-        policy: { ...el.policy, approvers: el.approvers }
+        policy: { ...el.policy, approvers: el.approvers, bypassers: el.bypassers }
       }));
     } catch (error) {
       throw new DatabaseError({ error, name: "FindSAR" });
@@ -440,6 +538,16 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
           `${TableName.SecretApprovalPolicyApprover}.approverGroupId`,
           `${TableName.UserGroupMembership}.groupId`
         )
+        .leftJoin(
+          TableName.SecretApprovalPolicyBypasser,
+          `${TableName.SecretApprovalPolicy}.id`,
+          `${TableName.SecretApprovalPolicyBypasser}.policyId`
+        )
+        .leftJoin<TUserGroupMembership>(
+          db(TableName.UserGroupMembership).as("bypasserUserGroupMembership"),
+          `${TableName.SecretApprovalPolicyBypasser}.bypasserGroupId`,
+          `bypasserUserGroupMembership.groupId`
+        )
         .join<TUsers>(
           db(TableName.Users).as("committerUser"),
           `${TableName.SecretApprovalRequest}.committerUserId`,
@@ -491,6 +599,11 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
           db.ref("enforcementLevel").withSchema(TableName.SecretApprovalPolicy).as("policyEnforcementLevel"),
           db.ref("approverUserId").withSchema(TableName.SecretApprovalPolicyApprover),
           db.ref("userId").withSchema(TableName.UserGroupMembership).as("approverGroupUserId"),
+
+          // Bypasser
+          db.ref("bypasserUserId").withSchema(TableName.SecretApprovalPolicyBypasser),
+          db.ref("userId").withSchema("bypasserUserGroupMembership").as("bypasserGroupUserId"),
+
           db.ref("email").withSchema("committerUser").as("committerUserEmail"),
           db.ref("username").withSchema("committerUser").as("committerUserUsername"),
           db.ref("firstName").withSchema("committerUser").as("committerUserFirstName"),
@@ -504,7 +617,7 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
         .from<Awaited<typeof query>[number]>("w")
         .where("w.rank", ">=", offset)
         .andWhere("w.rank", "<", offset + limit);
-      const formatedDoc = sqlNestRelationships({
+      const formattedDoc = sqlNestRelationships({
         data: docs,
         key: "id",
         parentMapper: (el) => ({
@@ -554,12 +667,24 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
             mapper: ({ approverGroupUserId }) => ({
               userId: approverGroupUserId
             })
+          },
+          {
+            key: "bypasserUserId",
+            label: "bypassers" as const,
+            mapper: ({ bypasserUserId }) => ({ userId: bypasserUserId })
+          },
+          {
+            key: "bypasserGroupUserId",
+            label: "bypassers" as const,
+            mapper: ({ bypasserGroupUserId }) => ({
+              userId: bypasserGroupUserId
+            })
           }
         ]
       });
-      return formatedDoc.map((el) => ({
+      return formattedDoc.map((el) => ({
         ...el,
-        policy: { ...el.policy, approvers: el.approvers }
+        policy: { ...el.policy, approvers: el.approvers, bypassers: el.bypassers }
       }));
     } catch (error) {
       throw new DatabaseError({ error, name: "FindSAR" });

backend/src/ee/services/secret-approval-request/secret-approval-request-service.ts

@@ -62,11 +62,7 @@ import { TUserDALFactory } from "@app/services/user/user-dal";
 import { TLicenseServiceFactory } from "../license/license-service";
 import { throwIfMissingSecretReadValueOrDescribePermission } from "../permission/permission-fns";
 import { TPermissionServiceFactory } from "../permission/permission-service";
-import {
-  ProjectPermissionApprovalActions,
-  ProjectPermissionSecretActions,
-  ProjectPermissionSub
-} from "../permission/project-permission";
+import { ProjectPermissionSecretActions, ProjectPermissionSub } from "../permission/project-permission";
 import { TSecretApprovalPolicyDALFactory } from "../secret-approval-policy/secret-approval-policy-dal";
 import { TSecretSnapshotServiceFactory } from "../secret-snapshot/secret-snapshot-service";
 import { TSecretApprovalRequestDALFactory } from "./secret-approval-request-dal";
@@ -501,14 +497,14 @@ export const secretApprovalRequestServiceFactory = ({
       });
     }
 
-    const { policy, folderId, projectId } = secretApprovalRequest;
+    const { policy, folderId, projectId, bypassers } = secretApprovalRequest;
     if (policy.deletedAt) {
       throw new BadRequestError({
         message: "The policy associated with this secret approval request has been deleted."
       });
     }
 
-    const { hasRole, permission } = await permissionService.getProjectPermission({
+    const { hasRole } = await permissionService.getProjectPermission({
       actor: ActorType.USER,
       actorId,
       projectId,
@@ -534,14 +530,9 @@ export const secretApprovalRequestServiceFactory = ({
         approverId ? reviewers[approverId] === ApprovalStatus.APPROVED : false
       ).length;
     const isSoftEnforcement = secretApprovalRequest.policy.enforcementLevel === EnforcementLevel.Soft;
+    const canBypass = !bypassers.length || bypassers.some((bypasser) => bypasser.userId === actorId);
 
-    if (
-      !hasMinApproval &&
-      !(
-        isSoftEnforcement &&
-        permission.can(ProjectPermissionApprovalActions.AllowChangeBypass, ProjectPermissionSub.SecretApproval)
-      )
-    )
+    if (!hasMinApproval && !(isSoftEnforcement && canBypass))
       throw new BadRequestError({ message: "Doesn't have minimum approvals needed" });
 
     const { botKey, shouldUseSecretV2Bridge, project } = await projectBotService.getBotKey(projectId);

backend/src/ee/services/secret-rotation-v2/mysql-credentials/index.ts

@@ -0,0 +1,3 @@
+export * from "./mysql-credentials-rotation-constants";
+export * from "./mysql-credentials-rotation-schemas";
+export * from "./mysql-credentials-rotation-types";

backend/src/ee/services/secret-rotation-v2/mysql-credentials/mysql-credentials-rotation-constants.ts

@@ -0,0 +1,23 @@
+import { SecretRotation } from "@app/ee/services/secret-rotation-v2/secret-rotation-v2-enums";
+import { TSecretRotationV2ListItem } from "@app/ee/services/secret-rotation-v2/secret-rotation-v2-types";
+import { AppConnection } from "@app/services/app-connection/app-connection-enums";
+
+export const MYSQL_CREDENTIALS_ROTATION_LIST_OPTION: TSecretRotationV2ListItem = {
+  name: "MySQL Credentials",
+  type: SecretRotation.MySqlCredentials,
+  connection: AppConnection.MySql,
+  template: {
+    createUserStatement: `-- create user
+CREATE USER 'infisical_user'@'%' IDENTIFIED BY 'temporary_password';
+
+-- grant all privileges
+GRANT ALL PRIVILEGES ON my_database.* TO 'infisical_user'@'%';
+
+-- apply the privilege changes
+FLUSH PRIVILEGES;`,
+    secretsMapping: {
+      username: "MYSQL_USERNAME",
+      password: "MYSQL_PASSWORD"
+    }
+  }
+};

backend/src/ee/services/secret-rotation-v2/mysql-credentials/mysql-credentials-rotation-schemas.ts

@@ -0,0 +1,41 @@
+import { z } from "zod";
+
+import { SecretRotation } from "@app/ee/services/secret-rotation-v2/secret-rotation-v2-enums";
+import {
+  BaseCreateSecretRotationSchema,
+  BaseSecretRotationSchema,
+  BaseUpdateSecretRotationSchema
+} from "@app/ee/services/secret-rotation-v2/secret-rotation-v2-schemas";
+import {
+  SqlCredentialsRotationParametersSchema,
+  SqlCredentialsRotationSecretsMappingSchema,
+  SqlCredentialsRotationTemplateSchema
+} from "@app/ee/services/secret-rotation-v2/shared/sql-credentials";
+import { AppConnection } from "@app/services/app-connection/app-connection-enums";
+
+export const MySqlCredentialsRotationSchema = BaseSecretRotationSchema(SecretRotation.MySqlCredentials).extend({
+  type: z.literal(SecretRotation.MySqlCredentials),
+  parameters: SqlCredentialsRotationParametersSchema,
+  secretsMapping: SqlCredentialsRotationSecretsMappingSchema
+});
+
+export const CreateMySqlCredentialsRotationSchema = BaseCreateSecretRotationSchema(
+  SecretRotation.MySqlCredentials
+).extend({
+  parameters: SqlCredentialsRotationParametersSchema,
+  secretsMapping: SqlCredentialsRotationSecretsMappingSchema
+});
+
+export const UpdateMySqlCredentialsRotationSchema = BaseUpdateSecretRotationSchema(
+  SecretRotation.MySqlCredentials
+).extend({
+  parameters: SqlCredentialsRotationParametersSchema.optional(),
+  secretsMapping: SqlCredentialsRotationSecretsMappingSchema.optional()
+});
+
+export const MySqlCredentialsRotationListItemSchema = z.object({
+  name: z.literal("MySQL Credentials"),
+  connection: z.literal(AppConnection.MySql),
+  type: z.literal(SecretRotation.MySqlCredentials),
+  template: SqlCredentialsRotationTemplateSchema
+});

backend/src/ee/services/secret-rotation-v2/mysql-credentials/mysql-credentials-rotation-types.ts

@@ -0,0 +1,19 @@
+import { z } from "zod";
+
+import { TMySqlConnection } from "@app/services/app-connection/mysql";
+
+import {
+  CreateMySqlCredentialsRotationSchema,
+  MySqlCredentialsRotationListItemSchema,
+  MySqlCredentialsRotationSchema
+} from "./mysql-credentials-rotation-schemas";
+
+export type TMySqlCredentialsRotation = z.infer<typeof MySqlCredentialsRotationSchema>;
+
+export type TMySqlCredentialsRotationInput = z.infer<typeof CreateMySqlCredentialsRotationSchema>;
+
+export type TMySqlCredentialsRotationListItem = z.infer<typeof MySqlCredentialsRotationListItemSchema>;
+
+export type TMySqlCredentialsRotationWithConnection = TMySqlCredentialsRotation & {
+  connection: TMySqlConnection;
+};

backend/src/ee/services/secret-rotation-v2/secret-rotation-v2-enums.ts

@@ -1,6 +1,7 @@
 export enum SecretRotation {
   PostgresCredentials = "postgres-credentials",
   MsSqlCredentials = "mssql-credentials",
+  MySqlCredentials = "mysql-credentials",
   Auth0ClientSecret = "auth0-client-secret",
   AzureClientSecret = "azure-client-secret",
   AwsIamUserSecret = "aws-iam-user-secret",

backend/src/ee/services/secret-rotation-v2/secret-rotation-v2-fns.ts

@@ -9,6 +9,7 @@ import { AWS_IAM_USER_SECRET_ROTATION_LIST_OPTION } from "./aws-iam-user-secret"
 import { AZURE_CLIENT_SECRET_ROTATION_LIST_OPTION } from "./azure-client-secret";
 import { LDAP_PASSWORD_ROTATION_LIST_OPTION, TLdapPasswordRotation } from "./ldap-password";
 import { MSSQL_CREDENTIALS_ROTATION_LIST_OPTION } from "./mssql-credentials";
+import { MYSQL_CREDENTIALS_ROTATION_LIST_OPTION } from "./mysql-credentials";
 import { POSTGRES_CREDENTIALS_ROTATION_LIST_OPTION } from "./postgres-credentials";
 import { SecretRotation, SecretRotationStatus } from "./secret-rotation-v2-enums";
 import { TSecretRotationV2ServiceFactoryDep } from "./secret-rotation-v2-service";
@@ -23,6 +24,7 @@ import {
 const SECRET_ROTATION_LIST_OPTIONS: Record<SecretRotation, TSecretRotationV2ListItem> = {
   [SecretRotation.PostgresCredentials]: POSTGRES_CREDENTIALS_ROTATION_LIST_OPTION,
   [SecretRotation.MsSqlCredentials]: MSSQL_CREDENTIALS_ROTATION_LIST_OPTION,
+  [SecretRotation.MySqlCredentials]: MYSQL_CREDENTIALS_ROTATION_LIST_OPTION,
   [SecretRotation.Auth0ClientSecret]: AUTH0_CLIENT_SECRET_ROTATION_LIST_OPTION,
   [SecretRotation.AzureClientSecret]: AZURE_CLIENT_SECRET_ROTATION_LIST_OPTION,
   [SecretRotation.AwsIamUserSecret]: AWS_IAM_USER_SECRET_ROTATION_LIST_OPTION,

backend/src/ee/services/secret-rotation-v2/secret-rotation-v2-maps.ts

@@ -4,6 +4,7 @@ import { AppConnection } from "@app/services/app-connection/app-connection-enums
 export const SECRET_ROTATION_NAME_MAP: Record<SecretRotation, string> = {
   [SecretRotation.PostgresCredentials]: "PostgreSQL Credentials",
   [SecretRotation.MsSqlCredentials]: "Microsoft SQL Server Credentials",
+  [SecretRotation.MySqlCredentials]: "MySQL Credentials",
   [SecretRotation.Auth0ClientSecret]: "Auth0 Client Secret",
   [SecretRotation.AzureClientSecret]: "Azure Client Secret",
   [SecretRotation.AwsIamUserSecret]: "AWS IAM User Secret",
@@ -13,6 +14,7 @@ export const SECRET_ROTATION_NAME_MAP: Record<SecretRotation, string> = {
 export const SECRET_ROTATION_CONNECTION_MAP: Record<SecretRotation, AppConnection> = {
   [SecretRotation.PostgresCredentials]: AppConnection.Postgres,
   [SecretRotation.MsSqlCredentials]: AppConnection.MsSql,
+  [SecretRotation.MySqlCredentials]: AppConnection.MySql,
   [SecretRotation.Auth0ClientSecret]: AppConnection.Auth0,
   [SecretRotation.AzureClientSecret]: AppConnection.AzureClientSecrets,
   [SecretRotation.AwsIamUserSecret]: AppConnection.AWS,

backend/src/ee/services/secret-rotation-v2/secret-rotation-v2-service.ts

@@ -120,6 +120,7 @@ type TRotationFactoryImplementation = TRotationFactory<
 const SECRET_ROTATION_FACTORY_MAP: Record<SecretRotation, TRotationFactoryImplementation> = {
   [SecretRotation.PostgresCredentials]: sqlCredentialsRotationFactory as TRotationFactoryImplementation,
   [SecretRotation.MsSqlCredentials]: sqlCredentialsRotationFactory as TRotationFactoryImplementation,
+  [SecretRotation.MySqlCredentials]: sqlCredentialsRotationFactory as TRotationFactoryImplementation,
   [SecretRotation.Auth0ClientSecret]: auth0ClientSecretRotationFactory as TRotationFactoryImplementation,
   [SecretRotation.AzureClientSecret]: azureClientSecretRotationFactory as TRotationFactoryImplementation,
   [SecretRotation.AwsIamUserSecret]: awsIamUserSecretRotationFactory as TRotationFactoryImplementation,

backend/src/ee/services/secret-rotation-v2/secret-rotation-v2-types.ts

@@ -39,6 +39,12 @@ import {
   TMsSqlCredentialsRotationListItem,
   TMsSqlCredentialsRotationWithConnection
 } from "./mssql-credentials";
+import {
+  TMySqlCredentialsRotation,
+  TMySqlCredentialsRotationInput,
+  TMySqlCredentialsRotationListItem,
+  TMySqlCredentialsRotationWithConnection
+} from "./mysql-credentials";
 import {
   TPostgresCredentialsRotation,
   TPostgresCredentialsRotationInput,
@@ -51,6 +57,7 @@ import { SecretRotation } from "./secret-rotation-v2-enums";
 export type TSecretRotationV2 =
   | TPostgresCredentialsRotation
   | TMsSqlCredentialsRotation
+  | TMySqlCredentialsRotation
   | TAuth0ClientSecretRotation
   | TAzureClientSecretRotation
   | TLdapPasswordRotation
@@ -59,6 +66,7 @@ export type TSecretRotationV2 =
 export type TSecretRotationV2WithConnection =
   | TPostgresCredentialsRotationWithConnection
   | TMsSqlCredentialsRotationWithConnection
+  | TMySqlCredentialsRotationWithConnection
   | TAuth0ClientSecretRotationWithConnection
   | TAzureClientSecretRotationWithConnection
   | TLdapPasswordRotationWithConnection
@@ -74,6 +82,7 @@ export type TSecretRotationV2GeneratedCredentials =
 export type TSecretRotationV2Input =
   | TPostgresCredentialsRotationInput
   | TMsSqlCredentialsRotationInput
+  | TMySqlCredentialsRotationInput
   | TAuth0ClientSecretRotationInput
   | TAzureClientSecretRotationInput
   | TLdapPasswordRotationInput
@@ -82,6 +91,7 @@ export type TSecretRotationV2Input =
 export type TSecretRotationV2ListItem =
   | TPostgresCredentialsRotationListItem
   | TMsSqlCredentialsRotationListItem
+  | TMySqlCredentialsRotationListItem
   | TAuth0ClientSecretRotationListItem
   | TAzureClientSecretRotationListItem
   | TLdapPasswordRotationListItem

backend/src/ee/services/secret-rotation-v2/secret-rotation-v2-union-schema.ts

@@ -4,6 +4,7 @@ import { Auth0ClientSecretRotationSchema } from "@app/ee/services/secret-rotatio
 import { AzureClientSecretRotationSchema } from "@app/ee/services/secret-rotation-v2/azure-client-secret";
 import { LdapPasswordRotationSchema } from "@app/ee/services/secret-rotation-v2/ldap-password";
 import { MsSqlCredentialsRotationSchema } from "@app/ee/services/secret-rotation-v2/mssql-credentials";
+import { MySqlCredentialsRotationSchema } from "@app/ee/services/secret-rotation-v2/mysql-credentials";
 import { PostgresCredentialsRotationSchema } from "@app/ee/services/secret-rotation-v2/postgres-credentials";
 
 import { AwsIamUserSecretRotationSchema } from "./aws-iam-user-secret";
@@ -11,6 +12,7 @@ import { AwsIamUserSecretRotationSchema } from "./aws-iam-user-secret";
 export const SecretRotationV2Schema = z.discriminatedUnion("type", [
   PostgresCredentialsRotationSchema,
   MsSqlCredentialsRotationSchema,
+  MySqlCredentialsRotationSchema,
   Auth0ClientSecretRotationSchema,
   AzureClientSecretRotationSchema,
   LdapPasswordRotationSchema,

backend/src/ee/services/secret-rotation-v2/shared/sql-credentials/sql-credentials-rotation-types.ts

@@ -1,13 +1,15 @@
 import { z } from "zod";
 
 import { TMsSqlCredentialsRotationWithConnection } from "@app/ee/services/secret-rotation-v2/mssql-credentials";
+import { TMySqlCredentialsRotationWithConnection } from "@app/ee/services/secret-rotation-v2/mysql-credentials";
 import { TPostgresCredentialsRotationWithConnection } from "@app/ee/services/secret-rotation-v2/postgres-credentials";
 
 import { SqlCredentialsRotationGeneratedCredentialsSchema } from "./sql-credentials-rotation-schemas";
 
 export type TSqlCredentialsRotationWithConnection =
   | TPostgresCredentialsRotationWithConnection
-  | TMsSqlCredentialsRotationWithConnection;
+  | TMsSqlCredentialsRotationWithConnection
+  | TMySqlCredentialsRotationWithConnection;
 
 export type TSqlCredentialsRotationGeneratedCredentials = z.infer<
   typeof SqlCredentialsRotationGeneratedCredentialsSchema

backend/src/ee/services/secret-rotation/secret-rotation-queue/secret-rotation-queue-fn.ts

@@ -171,6 +171,13 @@ export const getDbSetQuery = (db: TDbProviderClients, variables: { username: str
     };
   }
 
+  if (db === TDbProviderClients.MySql) {
+    return {
+      query: `ALTER USER ??@'%' IDENTIFIED BY '${variables.password}'`,
+      variables: [variables.username]
+    };
+  }
+
   // add more based on client
   return {
     query: `ALTER USER ?? IDENTIFIED BY '${variables.password}'`,

backend/src/lib/api-docs/constants.ts

@@ -147,7 +147,9 @@ export const UNIVERSAL_AUTH = {
     accessTokenMaxTTL:
       "The maximum lifetime for an access token in seconds. This value will be referenced at renewal time.",
     accessTokenNumUsesLimit:
-      "The maximum number of times that an access token can be used; a value of 0 implies infinite number of uses."
+      "The maximum number of times that an access token can be used; a value of 0 implies infinite number of uses.",
+    accessTokenPeriod:
+      "The period for an access token in seconds. This value will be referenced at renewal time. Default value is 0."
   },
   RETRIEVE: {
     identityId: "The ID of the identity to retrieve the auth method for."
@@ -161,7 +163,8 @@ export const UNIVERSAL_AUTH = {
     accessTokenTrustedIps: "The new list of IPs or CIDR ranges that access tokens can be used from.",
     accessTokenTTL: "The new lifetime for an access token in seconds.",
     accessTokenMaxTTL: "The new maximum lifetime for an access token in seconds.",
-    accessTokenNumUsesLimit: "The new maximum number of times that an access token can be used."
+    accessTokenNumUsesLimit: "The new maximum number of times that an access token can be used.",
+    accessTokenPeriod: "The new period for an access token in seconds."
   },
   CREATE_CLIENT_SECRET: {
     identityId: "The ID of the identity to create a client secret for.",

backend/src/server/routes/index.ts

@@ -6,7 +6,10 @@ import { z } from "zod";
 import { registerCertificateEstRouter } from "@app/ee/routes/est/certificate-est-router";
 import { registerV1EERoutes } from "@app/ee/routes/v1";
 import { registerV2EERoutes } from "@app/ee/routes/v2";
-import { accessApprovalPolicyApproverDALFactory } from "@app/ee/services/access-approval-policy/access-approval-policy-approver-dal";
+import {
+  accessApprovalPolicyApproverDALFactory,
+  accessApprovalPolicyBypasserDALFactory
+} from "@app/ee/services/access-approval-policy/access-approval-policy-approver-dal";
 import { accessApprovalPolicyDALFactory } from "@app/ee/services/access-approval-policy/access-approval-policy-dal";
 import { accessApprovalPolicyServiceFactory } from "@app/ee/services/access-approval-policy/access-approval-policy-service";
 import { accessApprovalRequestDALFactory } from "@app/ee/services/access-approval-request/access-approval-request-dal";
@@ -67,7 +70,10 @@ import { samlConfigDALFactory } from "@app/ee/services/saml-config/saml-config-d
 import { samlConfigServiceFactory } from "@app/ee/services/saml-config/saml-config-service";
 import { scimDALFactory } from "@app/ee/services/scim/scim-dal";
 import { scimServiceFactory } from "@app/ee/services/scim/scim-service";
-import { secretApprovalPolicyApproverDALFactory } from "@app/ee/services/secret-approval-policy/secret-approval-policy-approver-dal";
+import {
+  secretApprovalPolicyApproverDALFactory,
+  secretApprovalPolicyBypasserDALFactory
+} from "@app/ee/services/secret-approval-policy/secret-approval-policy-approver-dal";
 import { secretApprovalPolicyDALFactory } from "@app/ee/services/secret-approval-policy/secret-approval-policy-dal";
 import { secretApprovalPolicyServiceFactory } from "@app/ee/services/secret-approval-policy/secret-approval-policy-service";
 import { secretApprovalRequestDALFactory } from "@app/ee/services/secret-approval-request/secret-approval-request-dal";
@@ -205,6 +211,8 @@ import { pkiCollectionServiceFactory } from "@app/services/pki-collection/pki-co
 import { pkiSubscriberDALFactory } from "@app/services/pki-subscriber/pki-subscriber-dal";
 import { pkiSubscriberQueueServiceFactory } from "@app/services/pki-subscriber/pki-subscriber-queue";
 import { pkiSubscriberServiceFactory } from "@app/services/pki-subscriber/pki-subscriber-service";
+import { pkiTemplatesDALFactory } from "@app/services/pki-templates/pki-templates-dal";
+import { pkiTemplatesServiceFactory } from "@app/services/pki-templates/pki-templates-service";
 import { projectDALFactory } from "@app/services/project/project-dal";
 import { projectQueueFactory } from "@app/services/project/project-queue";
 import { projectServiceFactory } from "@app/services/project/project-service";
@@ -385,9 +393,11 @@ export const registerRoutes = async (
   const accessApprovalPolicyDAL = accessApprovalPolicyDALFactory(db);
   const accessApprovalRequestDAL = accessApprovalRequestDALFactory(db);
   const accessApprovalPolicyApproverDAL = accessApprovalPolicyApproverDALFactory(db);
+  const accessApprovalPolicyBypasserDAL = accessApprovalPolicyBypasserDALFactory(db);
   const accessApprovalRequestReviewerDAL = accessApprovalRequestReviewerDALFactory(db);
 
   const sapApproverDAL = secretApprovalPolicyApproverDALFactory(db);
+  const sapBypasserDAL = secretApprovalPolicyBypasserDALFactory(db);
   const secretApprovalPolicyDAL = secretApprovalPolicyDALFactory(db);
   const secretApprovalRequestDAL = secretApprovalRequestDALFactory(db);
   const secretApprovalRequestReviewerDAL = secretApprovalRequestReviewerDALFactory(db);
@@ -519,6 +529,7 @@ export const registerRoutes = async (
   const secretApprovalPolicyService = secretApprovalPolicyServiceFactory({
     projectEnvDAL,
     secretApprovalPolicyApproverDAL: sapApproverDAL,
+    secretApprovalPolicyBypasserDAL: sapBypasserDAL,
     permissionService,
     secretApprovalPolicyDAL,
     licenseService,
@@ -794,7 +805,8 @@ export const registerRoutes = async (
   const projectUserAdditionalPrivilegeService = projectUserAdditionalPrivilegeServiceFactory({
     permissionService,
     projectMembershipDAL,
-    projectUserAdditionalPrivilegeDAL
+    projectUserAdditionalPrivilegeDAL,
+    accessApprovalRequestDAL
   });
   const projectKeyService = projectKeyServiceFactory({
     permissionService,
@@ -838,6 +850,7 @@ export const registerRoutes = async (
   const pkiCollectionDAL = pkiCollectionDALFactory(db);
   const pkiCollectionItemDAL = pkiCollectionItemDALFactory(db);
   const pkiSubscriberDAL = pkiSubscriberDALFactory(db);
+  const pkiTemplatesDAL = pkiTemplatesDALFactory(db);
 
   const certificateService = certificateServiceFactory({
     certificateDAL,
@@ -1218,6 +1231,7 @@ export const registerRoutes = async (
   const accessApprovalPolicyService = accessApprovalPolicyServiceFactory({
     accessApprovalPolicyDAL,
     accessApprovalPolicyApproverDAL,
+    accessApprovalPolicyBypasserDAL,
     groupDAL,
     permissionService,
     projectEnvDAL,
@@ -1226,7 +1240,8 @@ export const registerRoutes = async (
     userDAL,
     accessApprovalRequestDAL,
     additionalPrivilegeDAL: projectUserAdditionalPrivilegeDAL,
-    accessApprovalRequestReviewerDAL
+    accessApprovalRequestReviewerDAL,
+    orgMembershipDAL
   });
 
   const accessApprovalRequestService = accessApprovalRequestServiceFactory({
@@ -1743,6 +1758,21 @@ export const registerRoutes = async (
     internalCaFns
   });
 
+  const pkiTemplateService = pkiTemplatesServiceFactory({
+    pkiTemplatesDAL,
+    certificateAuthorityDAL,
+    certificateAuthorityCertDAL,
+    certificateAuthoritySecretDAL,
+    certificateAuthorityCrlDAL,
+    certificateDAL,
+    certificateBodyDAL,
+    certificateSecretDAL,
+    projectDAL,
+    kmsService,
+    permissionService,
+    internalCaFns
+  });
+
   await secretRotationV2QueueServiceFactory({
     secretRotationV2Service,
     secretRotationV2DAL,
@@ -1836,6 +1866,7 @@ export const registerRoutes = async (
     pkiAlert: pkiAlertService,
     pkiCollection: pkiCollectionService,
     pkiSubscriber: pkiSubscriberService,
+    pkiTemplate: pkiTemplateService,
     secretScanning: secretScanningService,
     license: licenseService,
     trustedIp: trustedIpService,

backend/src/server/routes/v1/app-connection-routers/app-connection-router.ts

@@ -43,6 +43,7 @@ import {
 } from "@app/services/app-connection/humanitec";
 import { LdapConnectionListItemSchema, SanitizedLdapConnectionSchema } from "@app/services/app-connection/ldap";
 import { MsSqlConnectionListItemSchema, SanitizedMsSqlConnectionSchema } from "@app/services/app-connection/mssql";
+import { MySqlConnectionListItemSchema, SanitizedMySqlConnectionSchema } from "@app/services/app-connection/mysql";
 import {
   PostgresConnectionListItemSchema,
   SanitizedPostgresConnectionSchema
@@ -75,6 +76,7 @@ const SanitizedAppConnectionSchema = z.union([
   ...SanitizedVercelConnectionSchema.options,
   ...SanitizedPostgresConnectionSchema.options,
   ...SanitizedMsSqlConnectionSchema.options,
+  ...SanitizedMySqlConnectionSchema.options,
   ...SanitizedCamundaConnectionSchema.options,
   ...SanitizedAuth0ConnectionSchema.options,
   ...SanitizedHCVaultConnectionSchema.options,
@@ -98,6 +100,7 @@ const AppConnectionOptionsSchema = z.discriminatedUnion("app", [
   VercelConnectionListItemSchema,
   PostgresConnectionListItemSchema,
   MsSqlConnectionListItemSchema,
+  MySqlConnectionListItemSchema,
   CamundaConnectionListItemSchema,
   Auth0ConnectionListItemSchema,
   HCVaultConnectionListItemSchema,

backend/src/server/routes/v1/app-connection-routers/index.ts

@@ -15,6 +15,7 @@ import { registerHCVaultConnectionRouter } from "./hc-vault-connection-router";
 import { registerHumanitecConnectionRouter } from "./humanitec-connection-router";
 import { registerLdapConnectionRouter } from "./ldap-connection-router";
 import { registerMsSqlConnectionRouter } from "./mssql-connection-router";
+import { registerMySqlConnectionRouter } from "./mysql-connection-router";
 import { registerPostgresConnectionRouter } from "./postgres-connection-router";
 import { registerTeamCityConnectionRouter } from "./teamcity-connection-router";
 import { registerTerraformCloudConnectionRouter } from "./terraform-cloud-router";
@@ -37,6 +38,7 @@ export const APP_CONNECTION_REGISTER_ROUTER_MAP: Record<AppConnection, (server:
     [AppConnection.Vercel]: registerVercelConnectionRouter,
     [AppConnection.Postgres]: registerPostgresConnectionRouter,
     [AppConnection.MsSql]: registerMsSqlConnectionRouter,
+    [AppConnection.MySql]: registerMySqlConnectionRouter,
     [AppConnection.Camunda]: registerCamundaConnectionRouter,
     [AppConnection.Windmill]: registerWindmillConnectionRouter,
     [AppConnection.Auth0]: registerAuth0ConnectionRouter,

backend/src/server/routes/v1/app-connection-routers/mysql-connection-router.ts

@@ -0,0 +1,18 @@
+import { AppConnection } from "@app/services/app-connection/app-connection-enums";
+import {
+  CreateMySqlConnectionSchema,
+  SanitizedMySqlConnectionSchema,
+  UpdateMySqlConnectionSchema
+} from "@app/services/app-connection/mysql";
+
+import { registerAppConnectionEndpoints } from "./app-connection-endpoints";
+
+export const registerMySqlConnectionRouter = async (server: FastifyZodProvider) => {
+  registerAppConnectionEndpoints({
+    app: AppConnection.MySql,
+    server,
+    sanitizedResponseSchema: SanitizedMySqlConnectionSchema,
+    createSchema: CreateMySqlConnectionSchema,
+    updateSchema: UpdateMySqlConnectionSchema
+  });
+};

backend/src/server/routes/v1/certificate-template-router.ts

@@ -5,6 +5,7 @@ import { EventType } from "@app/ee/services/audit-log/audit-log-types";
 import { ApiDocsTags, CERTIFICATE_TEMPLATES } from "@app/lib/api-docs";
 import { ms } from "@app/lib/ms";
 import { readLimit, writeLimit } from "@app/server/config/rateLimiter";
+import { slugSchema } from "@app/server/lib/schemas";
 import { verifyAuth } from "@app/server/plugins/auth/verify-auth";
 import { AuthMode } from "@app/services/auth/auth-type";
 import { CertExtendedKeyUsage, CertKeyUsage } from "@app/services/certificate/certificate-types";
@@ -72,7 +73,7 @@ export const registerCertificateTemplateRouter = async (server: FastifyZodProvid
       body: z.object({
         caId: z.string().describe(CERTIFICATE_TEMPLATES.CREATE.caId),
         pkiCollectionId: z.string().optional().describe(CERTIFICATE_TEMPLATES.CREATE.pkiCollectionId),
-        name: z.string().min(1).describe(CERTIFICATE_TEMPLATES.CREATE.name),
+        name: slugSchema().describe(CERTIFICATE_TEMPLATES.CREATE.name),
         commonName: validateTemplateRegexField.describe(CERTIFICATE_TEMPLATES.CREATE.commonName),
         subjectAlternativeName: validateTemplateRegexField.describe(
           CERTIFICATE_TEMPLATES.CREATE.subjectAlternativeName
@@ -141,7 +142,7 @@ export const registerCertificateTemplateRouter = async (server: FastifyZodProvid
       body: z.object({
         caId: z.string().optional().describe(CERTIFICATE_TEMPLATES.UPDATE.caId),
         pkiCollectionId: z.string().optional().describe(CERTIFICATE_TEMPLATES.UPDATE.pkiCollectionId),
-        name: z.string().min(1).optional().describe(CERTIFICATE_TEMPLATES.UPDATE.name),
+        name: slugSchema().optional().describe(CERTIFICATE_TEMPLATES.UPDATE.name),
         commonName: validateTemplateRegexField.optional().describe(CERTIFICATE_TEMPLATES.UPDATE.commonName),
         subjectAlternativeName: validateTemplateRegexField
           .optional()

backend/src/server/routes/v1/identity-universal-auth-router.ts

@@ -47,8 +47,15 @@ export const registerIdentityUaRouter = async (server: FastifyZodProvider) => {
       }
     },
     handler: async (req) => {
-      const { identityUa, accessToken, identityAccessToken, validClientSecretInfo, identityMembershipOrg } =
-        await server.services.identityUa.login(req.body.clientId, req.body.clientSecret, req.realIp);
+      const {
+        identityUa,
+        accessToken,
+        identityAccessToken,
+        validClientSecretInfo,
+        identityMembershipOrg,
+        accessTokenTTL,
+        accessTokenMaxTTL
+      } = await server.services.identityUa.login(req.body.clientId, req.body.clientSecret, req.realIp);
 
       await server.services.auditLog.createAuditLog({
         ...req.auditLogInfo,
@@ -63,11 +70,12 @@ export const registerIdentityUaRouter = async (server: FastifyZodProvider) => {
           }
         }
       });
+
       return {
         accessToken,
         tokenType: "Bearer" as const,
-        expiresIn: identityUa.accessTokenTTL,
-        accessTokenMaxTTL: identityUa.accessTokenMaxTTL
+        expiresIn: accessTokenTTL,
+        accessTokenMaxTTL
       };
     }
   });
@@ -128,7 +136,8 @@ export const registerIdentityUaRouter = async (server: FastifyZodProvider) => {
             .int()
             .min(0)
             .default(0)
-            .describe(UNIVERSAL_AUTH.ATTACH.accessTokenNumUsesLimit)
+            .describe(UNIVERSAL_AUTH.ATTACH.accessTokenNumUsesLimit),
+          accessTokenPeriod: z.number().int().min(0).default(0).describe(UNIVERSAL_AUTH.ATTACH.accessTokenPeriod)
         })
         .refine(
           (val) => val.accessTokenTTL <= val.accessTokenMaxTTL,
@@ -227,7 +236,14 @@ export const registerIdentityUaRouter = async (server: FastifyZodProvider) => {
             .min(0)
             .max(315360000)
             .optional()
-            .describe(UNIVERSAL_AUTH.UPDATE.accessTokenMaxTTL)
+            .describe(UNIVERSAL_AUTH.UPDATE.accessTokenMaxTTL),
+          accessTokenPeriod: z
+            .number()
+            .int()
+            .min(0)
+            .max(315360000)
+            .optional()
+            .describe(UNIVERSAL_AUTH.UPDATE.accessTokenPeriod)
         })
         .refine(
           (val) => (val.accessTokenMaxTTL && val.accessTokenTTL ? val.accessTokenTTL <= val.accessTokenMaxTTL : true),

backend/src/server/routes/v1/secret-sharing-router.ts

@@ -62,9 +62,7 @@ export const registerSecretSharingRouter = async (server: FastifyZodProvider) =>
       }),
       body: z.object({
         hashedHex: z.string().min(1).optional(),
-        password: z.string().optional(),
-        email: z.string().optional(),
-        hash: z.string().optional()
+        password: z.string().optional()
       }),
       response: {
         200: z.object({
@@ -91,8 +89,7 @@ export const registerSecretSharingRouter = async (server: FastifyZodProvider) =>
         hashedHex: req.body.hashedHex,
         password: req.body.password,
         orgId: req.permission?.orgId,
-        email: req.body.email,
-        hash: req.body.hash
+        actorId: req.permission?.id
       });
 
       if (sharedSecret.secret?.orgId) {
@@ -156,7 +153,13 @@ export const registerSecretSharingRouter = async (server: FastifyZodProvider) =>
         expiresAt: z.string(),
         expiresAfterViews: z.number().min(1).optional(),
         accessType: z.nativeEnum(SecretSharingAccessType).default(SecretSharingAccessType.Organization),
-        emails: z.string().email().array().max(100).optional()
+        emails: z
+          .string()
+          .email()
+          .array()
+          .max(100)
+          .optional()
+          .transform((val) => (val ? [...new Set(val)] : undefined))
       }),
       response: {
         200: z.object({

backend/src/server/routes/v2/index.ts

@@ -5,6 +5,7 @@ import { registerIdentityProjectRouter } from "./identity-project-router";
 import { registerMfaRouter } from "./mfa-router";
 import { registerOrgRouter } from "./organization-router";
 import { registerPasswordRouter } from "./password-router";
+import { registerPkiTemplatesRouter } from "./pki-templates-router";
 import { registerProjectMembershipRouter } from "./project-membership-router";
 import { registerProjectRouter } from "./project-router";
 import { registerServiceTokenRouter } from "./service-token-router";
@@ -15,7 +16,15 @@ export const registerV2Routes = async (server: FastifyZodProvider) => {
   await server.register(registerUserRouter, { prefix: "/users" });
   await server.register(registerServiceTokenRouter, { prefix: "/service-token" });
   await server.register(registerPasswordRouter, { prefix: "/password" });
-  await server.register(registerCaRouter, { prefix: "/pki/ca" });
+
+  await server.register(
+    async (pkiRouter) => {
+      await pkiRouter.register(registerCaRouter, { prefix: "/ca" });
+      await pkiRouter.register(registerPkiTemplatesRouter, { prefix: "/certificate-templates" });
+    },
+    { prefix: "/pki" }
+  );
+
   await server.register(
     async (orgRouter) => {
       await orgRouter.register(registerOrgRouter);

backend/src/server/routes/v2/pki-templates-router.ts

@@ -0,0 +1,309 @@
+import { z } from "zod";
+
+import { CertificateTemplatesSchema } from "@app/db/schemas";
+import { ApiDocsTags } from "@app/lib/api-docs";
+import { ms } from "@app/lib/ms";
+import { readLimit, writeLimit } from "@app/server/config/rateLimiter";
+import { slugSchema } from "@app/server/lib/schemas";
+import { verifyAuth } from "@app/server/plugins/auth/verify-auth";
+import { AuthMode } from "@app/services/auth/auth-type";
+import { CertExtendedKeyUsage, CertKeyUsage } from "@app/services/certificate/certificate-types";
+import {
+  validateAltNamesField,
+  validateCaDateField
+} from "@app/services/certificate-authority/certificate-authority-validators";
+import { validateTemplateRegexField } from "@app/services/certificate-template/certificate-template-validators";
+
+export const registerPkiTemplatesRouter = async (server: FastifyZodProvider) => {
+  server.route({
+    method: "POST",
+    url: "/",
+    config: {
+      rateLimit: writeLimit
+    },
+    schema: {
+      hide: false,
+      tags: [ApiDocsTags.PkiCertificateTemplates],
+      body: z.object({
+        name: slugSchema(),
+        caName: slugSchema({ field: "caName" }),
+        projectId: z.string(),
+        commonName: validateTemplateRegexField,
+        subjectAlternativeName: validateTemplateRegexField,
+        ttl: z.string().refine((val) => ms(val) > 0, "TTL must be a positive number"),
+        keyUsages: z
+          .nativeEnum(CertKeyUsage)
+          .array()
+          .optional()
+          .default([CertKeyUsage.DIGITAL_SIGNATURE, CertKeyUsage.KEY_ENCIPHERMENT]),
+        extendedKeyUsages: z.nativeEnum(CertExtendedKeyUsage).array().optional().default([])
+      }),
+      response: {
+        200: z.object({
+          certificateTemplate: CertificateTemplatesSchema
+        })
+      }
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    handler: async (req) => {
+      const certificateTemplate = await server.services.pkiTemplate.createTemplate({
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        actorAuthMethod: req.permission.authMethod,
+        actorOrgId: req.permission.orgId,
+        ...req.body
+      });
+
+      return { certificateTemplate };
+    }
+  });
+
+  server.route({
+    method: "PATCH",
+    url: "/:templateName",
+    config: {
+      rateLimit: writeLimit
+    },
+    schema: {
+      hide: false,
+      tags: [ApiDocsTags.PkiCertificateTemplates],
+      params: z.object({
+        templateName: slugSchema()
+      }),
+      body: z.object({
+        name: slugSchema().optional(),
+        caName: slugSchema(),
+        projectId: z.string(),
+        commonName: validateTemplateRegexField.optional(),
+        subjectAlternativeName: validateTemplateRegexField.optional(),
+        ttl: z
+          .string()
+          .refine((val) => ms(val) > 0, "TTL must be a positive number")
+          .optional(),
+        keyUsages: z
+          .nativeEnum(CertKeyUsage)
+          .array()
+          .optional()
+          .default([CertKeyUsage.DIGITAL_SIGNATURE, CertKeyUsage.KEY_ENCIPHERMENT]),
+        extendedKeyUsages: z.nativeEnum(CertExtendedKeyUsage).array().optional().default([])
+      }),
+      response: {
+        200: z.object({
+          certificateTemplate: CertificateTemplatesSchema
+        })
+      }
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    handler: async (req) => {
+      const certificateTemplate = await server.services.pkiTemplate.updateTemplate({
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        actorAuthMethod: req.permission.authMethod,
+        actorOrgId: req.permission.orgId,
+        templateName: req.params.templateName,
+        ...req.body
+      });
+
+      return { certificateTemplate };
+    }
+  });
+
+  server.route({
+    method: "DELETE",
+    url: "/:templateName",
+    config: {
+      rateLimit: writeLimit
+    },
+    schema: {
+      hide: false,
+      tags: [ApiDocsTags.PkiCertificateTemplates],
+      params: z.object({
+        templateName: z.string().min(1)
+      }),
+      body: z.object({
+        projectId: z.string()
+      }),
+      response: {
+        200: z.object({
+          certificateTemplate: CertificateTemplatesSchema
+        })
+      }
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    handler: async (req) => {
+      const certificateTemplate = await server.services.pkiTemplate.deleteTemplate({
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        actorAuthMethod: req.permission.authMethod,
+        actorOrgId: req.permission.orgId,
+        templateName: req.params.templateName,
+        projectId: req.body.projectId
+      });
+
+      return { certificateTemplate };
+    }
+  });
+
+  server.route({
+    method: "GET",
+    url: "/:templateName",
+    config: {
+      rateLimit: readLimit
+    },
+    schema: {
+      hide: false,
+      tags: [ApiDocsTags.PkiCertificateTemplates],
+      params: z.object({
+        templateName: slugSchema()
+      }),
+      querystring: z.object({
+        projectId: z.string()
+      }),
+      response: {
+        200: z.object({
+          certificateTemplate: CertificateTemplatesSchema.extend({
+            ca: z.object({ id: z.string(), name: z.string() })
+          })
+        })
+      }
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    handler: async (req) => {
+      const certificateTemplate = await server.services.pkiTemplate.getTemplateByName({
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        actorAuthMethod: req.permission.authMethod,
+        actorOrgId: req.permission.orgId,
+        templateName: req.params.templateName,
+        projectId: req.query.projectId
+      });
+
+      return { certificateTemplate };
+    }
+  });
+
+  server.route({
+    method: "GET",
+    url: "/",
+    config: {
+      rateLimit: readLimit
+    },
+    schema: {
+      hide: false,
+      tags: [ApiDocsTags.PkiCertificateTemplates],
+      querystring: z.object({
+        projectId: z.string(),
+        limit: z.coerce.number().default(100),
+        offset: z.coerce.number().default(0)
+      }),
+      response: {
+        200: z.object({
+          certificateTemplates: CertificateTemplatesSchema.extend({
+            ca: z.object({ id: z.string(), name: z.string() })
+          }).array(),
+          totalCount: z.number()
+        })
+      }
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    handler: async (req) => {
+      const { certificateTemplates, totalCount } = await server.services.pkiTemplate.listTemplate({
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        actorAuthMethod: req.permission.authMethod,
+        actorOrgId: req.permission.orgId,
+        ...req.query
+      });
+
+      return { certificateTemplates, totalCount };
+    }
+  });
+
+  server.route({
+    method: "POST",
+    url: "/:templateName/issue-certificate",
+    config: {
+      rateLimit: writeLimit
+    },
+    schema: {
+      hide: false,
+      tags: [ApiDocsTags.PkiCertificateTemplates],
+      params: z.object({
+        templateName: slugSchema()
+      }),
+      body: z.object({
+        projectId: z.string(),
+        commonName: validateTemplateRegexField,
+        ttl: z.string().refine((val) => ms(val) > 0, "TTL must be a positive number"),
+        keyUsages: z.nativeEnum(CertKeyUsage).array().optional(),
+        extendedKeyUsages: z.nativeEnum(CertExtendedKeyUsage).array().optional(),
+        notBefore: validateCaDateField.optional(),
+        notAfter: validateCaDateField.optional(),
+        altNames: validateAltNamesField
+      }),
+      response: {
+        200: z.object({
+          certificate: z.string().trim(),
+          issuingCaCertificate: z.string().trim(),
+          certificateChain: z.string().trim(),
+          privateKey: z.string().trim(),
+          serialNumber: z.string().trim()
+        })
+      }
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    handler: async (req) => {
+      const data = await server.services.pkiTemplate.issueCertificate({
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        actorAuthMethod: req.permission.authMethod,
+        actorOrgId: req.permission.orgId,
+        templateName: req.params.templateName,
+        ...req.body
+      });
+
+      return data;
+    }
+  });
+
+  server.route({
+    method: "POST",
+    url: "/:templateName/sign-certificate",
+    config: {
+      rateLimit: writeLimit
+    },
+    schema: {
+      hide: false,
+      tags: [ApiDocsTags.PkiCertificateTemplates],
+      params: z.object({
+        templateName: slugSchema()
+      }),
+      body: z.object({
+        projectId: z.string(),
+        ttl: z.string().refine((val) => ms(val) > 0, "TTL must be a positive number"),
+        csr: z.string().trim().min(1).max(4096)
+      }),
+      response: {
+        200: z.object({
+          certificate: z.string().trim(),
+          issuingCaCertificate: z.string().trim(),
+          certificateChain: z.string().trim(),
+          serialNumber: z.string().trim()
+        })
+      }
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    handler: async (req) => {
+      const data = await server.services.pkiTemplate.signCertificate({
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        actorAuthMethod: req.permission.authMethod,
+        actorOrgId: req.permission.orgId,
+        templateName: req.params.templateName,
+        ...req.body
+      });
+
+      return data;
+    }
+  });
+};

backend/src/services/app-connection/app-connection-enums.ts

@@ -11,6 +11,7 @@ export enum AppConnection {
   Vercel = "vercel",
   Postgres = "postgres",
   MsSql = "mssql",
+  MySql = "mysql",
   Camunda = "camunda",
   Windmill = "windmill",
   Auth0 = "auth0",

backend/src/services/app-connection/app-connection-fns.ts

@@ -64,6 +64,8 @@ import {
 } from "./humanitec";
 import { getLdapConnectionListItem, LdapConnectionMethod, validateLdapConnectionCredentials } from "./ldap";
 import { getMsSqlConnectionListItem, MsSqlConnectionMethod } from "./mssql";
+import { MySqlConnectionMethod } from "./mysql/mysql-connection-enums";
+import { getMySqlConnectionListItem } from "./mysql/mysql-connection-fns";
 import { getPostgresConnectionListItem, PostgresConnectionMethod } from "./postgres";
 import {
   getTeamCityConnectionListItem,
@@ -96,6 +98,7 @@ export const listAppConnectionOptions = () => {
     getVercelConnectionListItem(),
     getPostgresConnectionListItem(),
     getMsSqlConnectionListItem(),
+    getMySqlConnectionListItem(),
     getCamundaConnectionListItem(),
     getAzureClientSecretsConnectionListItem(),
     getWindmillConnectionListItem(),
@@ -166,6 +169,7 @@ export const validateAppConnectionCredentials = async (
     [AppConnection.Humanitec]: validateHumanitecConnectionCredentials as TAppConnectionCredentialsValidator,
     [AppConnection.Postgres]: validateSqlConnectionCredentials as TAppConnectionCredentialsValidator,
     [AppConnection.MsSql]: validateSqlConnectionCredentials as TAppConnectionCredentialsValidator,
+    [AppConnection.MySql]: validateSqlConnectionCredentials as TAppConnectionCredentialsValidator,
     [AppConnection.Camunda]: validateCamundaConnectionCredentials as TAppConnectionCredentialsValidator,
     [AppConnection.Vercel]: validateVercelConnectionCredentials as TAppConnectionCredentialsValidator,
     [AppConnection.TerraformCloud]: validateTerraformCloudConnectionCredentials as TAppConnectionCredentialsValidator,
@@ -208,6 +212,7 @@ export const getAppConnectionMethodName = (method: TAppConnection["method"]) =>
       return "API Token";
     case PostgresConnectionMethod.UsernameAndPassword:
     case MsSqlConnectionMethod.UsernameAndPassword:
+    case MySqlConnectionMethod.UsernameAndPassword:
       return "Username & Password";
     case WindmillConnectionMethod.AccessToken:
     case HCVaultConnectionMethod.AccessToken:
@@ -259,6 +264,7 @@ export const TRANSITION_CONNECTION_CREDENTIALS_TO_PLATFORM: Record<
   [AppConnection.Humanitec]: platformManagedCredentialsNotSupported,
   [AppConnection.Postgres]: transferSqlConnectionCredentialsToPlatform as TAppConnectionTransitionCredentialsToPlatform,
   [AppConnection.MsSql]: transferSqlConnectionCredentialsToPlatform as TAppConnectionTransitionCredentialsToPlatform,
+  [AppConnection.MySql]: transferSqlConnectionCredentialsToPlatform as TAppConnectionTransitionCredentialsToPlatform,
   [AppConnection.TerraformCloud]: platformManagedCredentialsNotSupported,
   [AppConnection.Camunda]: platformManagedCredentialsNotSupported,
   [AppConnection.Vercel]: platformManagedCredentialsNotSupported,

backend/src/services/app-connection/app-connection-maps.ts

@@ -13,6 +13,7 @@ export const APP_CONNECTION_NAME_MAP: Record<AppConnection, string> = {
   [AppConnection.Vercel]: "Vercel",
   [AppConnection.Postgres]: "PostgreSQL",
   [AppConnection.MsSql]: "Microsoft SQL Server",
+  [AppConnection.MySql]: "MySQL",
   [AppConnection.Camunda]: "Camunda",
   [AppConnection.Windmill]: "Windmill",
   [AppConnection.Auth0]: "Auth0",
@@ -43,5 +44,6 @@ export const APP_CONNECTION_PLAN_MAP: Record<AppConnection, AppConnectionPlanTyp
   [AppConnection.LDAP]: AppConnectionPlanType.Regular,
   [AppConnection.TeamCity]: AppConnectionPlanType.Regular,
   [AppConnection.OCI]: AppConnectionPlanType.Enterprise,
-  [AppConnection.OnePass]: AppConnectionPlanType.Regular
+  [AppConnection.OnePass]: AppConnectionPlanType.Regular,
+  [AppConnection.MySql]: AppConnectionPlanType.Regular
 };

backend/src/services/app-connection/app-connection-service.ts

@@ -55,6 +55,7 @@ import { ValidateHumanitecConnectionCredentialsSchema } from "./humanitec";
 import { humanitecConnectionService } from "./humanitec/humanitec-connection-service";
 import { ValidateLdapConnectionCredentialsSchema } from "./ldap";
 import { ValidateMsSqlConnectionCredentialsSchema } from "./mssql";
+import { ValidateMySqlConnectionCredentialsSchema } from "./mysql";
 import { ValidatePostgresConnectionCredentialsSchema } from "./postgres";
 import { ValidateTeamCityConnectionCredentialsSchema } from "./teamcity";
 import { teamcityConnectionService } from "./teamcity/teamcity-connection-service";
@@ -86,6 +87,7 @@ const VALIDATE_APP_CONNECTION_CREDENTIALS_MAP: Record<AppConnection, TValidateAp
   [AppConnection.Vercel]: ValidateVercelConnectionCredentialsSchema,
   [AppConnection.Postgres]: ValidatePostgresConnectionCredentialsSchema,
   [AppConnection.MsSql]: ValidateMsSqlConnectionCredentialsSchema,
+  [AppConnection.MySql]: ValidateMySqlConnectionCredentialsSchema,
   [AppConnection.Camunda]: ValidateCamundaConnectionCredentialsSchema,
   [AppConnection.AzureClientSecrets]: ValidateAzureClientSecretsConnectionCredentialsSchema,
   [AppConnection.Windmill]: ValidateWindmillConnectionCredentialsSchema,

backend/src/services/app-connection/app-connection-types.ts

@@ -88,6 +88,7 @@ import {
   TValidateLdapConnectionCredentialsSchema
 } from "./ldap";
 import { TMsSqlConnection, TMsSqlConnectionInput, TValidateMsSqlConnectionCredentialsSchema } from "./mssql";
+import { TMySqlConnection, TMySqlConnectionInput, TValidateMySqlConnectionCredentialsSchema } from "./mysql";
 import {
   TPostgresConnection,
   TPostgresConnectionInput,
@@ -130,6 +131,7 @@ export type TAppConnection = { id: string } & (
   | TVercelConnection
   | TPostgresConnection
   | TMsSqlConnection
+  | TMySqlConnection
   | TCamundaConnection
   | TAzureClientSecretsConnection
   | TWindmillConnection
@@ -143,7 +145,7 @@ export type TAppConnection = { id: string } & (
 
 export type TAppConnectionRaw = NonNullable<Awaited<ReturnType<TAppConnectionDALFactory["findById"]>>>;
 
-export type TSqlConnection = TPostgresConnection | TMsSqlConnection;
+export type TSqlConnection = TPostgresConnection | TMsSqlConnection | TMySqlConnection;
 
 export type TAppConnectionInput = { id: string } & (
   | TAwsConnectionInput
@@ -157,6 +159,7 @@ export type TAppConnectionInput = { id: string } & (
   | TVercelConnectionInput
   | TPostgresConnectionInput
   | TMsSqlConnectionInput
+  | TMySqlConnectionInput
   | TCamundaConnectionInput
   | TAzureClientSecretsConnectionInput
   | TWindmillConnectionInput
@@ -168,7 +171,7 @@ export type TAppConnectionInput = { id: string } & (
   | TOnePassConnectionInput
 );
 
-export type TSqlConnectionInput = TPostgresConnectionInput | TMsSqlConnectionInput;
+export type TSqlConnectionInput = TPostgresConnectionInput | TMsSqlConnectionInput | TMySqlConnectionInput;
 
 export type TCreateAppConnectionDTO = Pick<
   TAppConnectionInput,
@@ -211,6 +214,7 @@ export type TValidateAppConnectionCredentialsSchema =
   | TValidateHumanitecConnectionCredentialsSchema
   | TValidatePostgresConnectionCredentialsSchema
   | TValidateMsSqlConnectionCredentialsSchema
+  | TValidateMySqlConnectionCredentialsSchema
   | TValidateCamundaConnectionCredentialsSchema
   | TValidateVercelConnectionCredentialsSchema
   | TValidateTerraformCloudConnectionCredentialsSchema

backend/src/services/app-connection/mysql/index.ts

@@ -0,0 +1,4 @@
+export * from "./mysql-connection-enums";
+export * from "./mysql-connection-fns";
+export * from "./mysql-connection-schemas";
+export * from "./mysql-connection-types";

backend/src/services/app-connection/mysql/mysql-connection-enums.ts

@@ -0,0 +1,3 @@
+export enum MySqlConnectionMethod {
+  UsernameAndPassword = "username-and-password"
+}

backend/src/services/app-connection/mysql/mysql-connection-fns.ts

@@ -0,0 +1,12 @@
+import { AppConnection } from "@app/services/app-connection/app-connection-enums";
+
+import { MySqlConnectionMethod } from "./mysql-connection-enums";
+
+export const getMySqlConnectionListItem = () => {
+  return {
+    name: "MySQL" as const,
+    app: AppConnection.MySql as const,
+    methods: Object.values(MySqlConnectionMethod) as [MySqlConnectionMethod.UsernameAndPassword],
+    supportsPlatformManagement: true as const
+  };
+};

backend/src/services/app-connection/mysql/mysql-connection-schemas.ts

@@ -0,0 +1,66 @@
+import z from "zod";
+
+import { AppConnections } from "@app/lib/api-docs";
+import {
+  BaseAppConnectionSchema,
+  GenericCreateAppConnectionFieldsSchema,
+  GenericUpdateAppConnectionFieldsSchema
+} from "@app/services/app-connection/app-connection-schemas";
+
+import { AppConnection } from "../app-connection-enums";
+import { BaseSqlUsernameAndPasswordConnectionSchema } from "../shared/sql";
+import { MySqlConnectionMethod } from "./mysql-connection-enums";
+
+export const MySqlConnectionAccessTokenCredentialsSchema = BaseSqlUsernameAndPasswordConnectionSchema;
+
+const BaseMySqlConnectionSchema = BaseAppConnectionSchema.extend({ app: z.literal(AppConnection.MySql) });
+
+export const MySqlConnectionSchema = BaseMySqlConnectionSchema.extend({
+  method: z.literal(MySqlConnectionMethod.UsernameAndPassword),
+  credentials: MySqlConnectionAccessTokenCredentialsSchema
+});
+
+export const SanitizedMySqlConnectionSchema = z.discriminatedUnion("method", [
+  BaseMySqlConnectionSchema.extend({
+    method: z.literal(MySqlConnectionMethod.UsernameAndPassword),
+    credentials: MySqlConnectionAccessTokenCredentialsSchema.pick({
+      host: true,
+      database: true,
+      port: true,
+      username: true,
+      sslEnabled: true,
+      sslRejectUnauthorized: true,
+      sslCertificate: true
+    })
+  })
+]);
+
+export const ValidateMySqlConnectionCredentialsSchema = z.discriminatedUnion("method", [
+  z.object({
+    method: z
+      .literal(MySqlConnectionMethod.UsernameAndPassword)
+      .describe(AppConnections.CREATE(AppConnection.MySql).method),
+    credentials: MySqlConnectionAccessTokenCredentialsSchema.describe(
+      AppConnections.CREATE(AppConnection.MySql).credentials
+    )
+  })
+]);
+
+export const CreateMySqlConnectionSchema = ValidateMySqlConnectionCredentialsSchema.and(
+  GenericCreateAppConnectionFieldsSchema(AppConnection.MySql, { supportsPlatformManagedCredentials: true })
+);
+
+export const UpdateMySqlConnectionSchema = z
+  .object({
+    credentials: MySqlConnectionAccessTokenCredentialsSchema.optional().describe(
+      AppConnections.UPDATE(AppConnection.MySql).credentials
+    )
+  })
+  .and(GenericUpdateAppConnectionFieldsSchema(AppConnection.MySql, { supportsPlatformManagedCredentials: true }));
+
+export const MySqlConnectionListItemSchema = z.object({
+  name: z.literal("MySQL"),
+  app: z.literal(AppConnection.MySql),
+  methods: z.nativeEnum(MySqlConnectionMethod).array(),
+  supportsPlatformManagement: z.literal(true)
+});

backend/src/services/app-connection/mysql/mysql-connection-types.ts

@@ -0,0 +1,16 @@
+import z from "zod";
+
+import { AppConnection } from "../app-connection-enums";
+import {
+  CreateMySqlConnectionSchema,
+  MySqlConnectionSchema,
+  ValidateMySqlConnectionCredentialsSchema
+} from "./mysql-connection-schemas";
+
+export type TMySqlConnection = z.infer<typeof MySqlConnectionSchema>;
+
+export type TMySqlConnectionInput = z.infer<typeof CreateMySqlConnectionSchema> & {
+  app: AppConnection.MySql;
+};
+
+export type TValidateMySqlConnectionCredentialsSchema = typeof ValidateMySqlConnectionCredentialsSchema;

backend/src/services/app-connection/shared/sql/sql-connection-fns.ts

@@ -15,7 +15,8 @@ const EXTERNAL_REQUEST_TIMEOUT = 10 * 1000;
 
 const SQL_CONNECTION_CLIENT_MAP = {
   [AppConnection.Postgres]: "pg",
-  [AppConnection.MsSql]: "mssql"
+  [AppConnection.MsSql]: "mssql",
+  [AppConnection.MySql]: "mysql2"
 };
 
 const getConnectionConfig = ({
@@ -45,6 +46,17 @@ const getConnectionConfig = ({
           : { encrypt: false }
       };
     }
+    case AppConnection.MySql: {
+      return {
+        ssl: sslEnabled
+          ? {
+              rejectUnauthorized: sslRejectUnauthorized,
+              ca: sslCertificate,
+              servername: host
+            }
+          : false
+      };
+    }
     default:
       throw new Error(`Unhandled SQL Connection Config: ${app as AppConnection}`);
   }
@@ -101,7 +113,8 @@ export const SQL_CONNECTION_ALTER_LOGIN_STATEMENT: Record<
   (credentials: TSqlCredentialsRotationGeneratedCredentials[number]) => [string, Knex.RawBinding]
 > = {
   [AppConnection.Postgres]: ({ username, password }) => [`ALTER USER ?? WITH PASSWORD '${password}';`, [username]],
-  [AppConnection.MsSql]: ({ username, password }) => [`ALTER LOGIN ?? WITH PASSWORD = '${password}';`, [username]]
+  [AppConnection.MsSql]: ({ username, password }) => [`ALTER LOGIN ?? WITH PASSWORD = '${password}';`, [username]],
+  [AppConnection.MySql]: ({ username, password }) => [`ALTER USER ??@'%' IDENTIFIED BY '${password}';`, [username]]
 };
 
 export const transferSqlConnectionCredentialsToPlatform = async (

backend/src/services/certificate-authority/certificate-authority-service.ts

@@ -311,7 +311,6 @@ export const certificateAuthorityServiceFactory = ({
       }
 
       const updatedCa = await internalCertificateAuthorityService.updateCaById({
-        ...configuration,
         isInternal: true,
         enableDirectIssuance,
         caId: certificateAuthority.id,

backend/src/services/certificate-authority/internal/internal-certificate-authority-fns.ts

@@ -1,8 +1,10 @@
+/* eslint-disable no-bitwise */
 import * as x509 from "@peculiar/x509";
 import { KeyObject } from "crypto";
+import RE2 from "re2";
 import { z } from "zod";
 
-import { TPkiSubscribers } from "@app/db/schemas";
+import { TCertificateTemplates, TPkiSubscribers } from "@app/db/schemas";
 import { TCertificateAuthorityCrlDALFactory } from "@app/ee/services/certificate-authority-crl/certificate-authority-crl-dal";
 import { getConfig } from "@app/lib/config/env";
 import { BadRequestError } from "@app/lib/errors";
@@ -31,6 +33,7 @@ import {
   keyAlgorithmToAlgCfg
 } from "../certificate-authority-fns";
 import { TCertificateAuthoritySecretDALFactory } from "../certificate-authority-secret-dal";
+import { TIssueCertWithTemplateDTO } from "./internal-certificate-authority-types";
 
 type TInternalCertificateAuthorityFnsDeps = {
   certificateAuthorityDAL: Pick<TCertificateAuthorityDALFactory, "findByIdWithAssociatedCa" | "findById">;
@@ -257,7 +260,274 @@ export const InternalCertificateAuthorityFns = ({
     };
   };
 
+  const issueCertificateWithTemplate = async (
+    ca: Awaited<ReturnType<TCertificateAuthorityDALFactory["findByIdWithAssociatedCa"]>>,
+    certificateTemplate: TCertificateTemplates,
+    { altNames, commonName, ttl, extendedKeyUsages, keyUsages, notAfter, notBefore }: TIssueCertWithTemplateDTO
+  ) => {
+    if (ca.status !== CaStatus.ACTIVE) throw new BadRequestError({ message: "CA is not active" });
+    if (!ca.internalCa?.activeCaCertId)
+      throw new BadRequestError({ message: "CA does not have a certificate installed" });
+
+    const caCert = await certificateAuthorityCertDAL.findById(ca.internalCa.activeCaCertId);
+
+    const certificateManagerKmsId = await getProjectKmsCertificateKeyId({
+      projectId: ca.projectId,
+      projectDAL,
+      kmsService
+    });
+
+    const kmsDecryptor = await kmsService.decryptWithKmsKey({
+      kmsId: certificateManagerKmsId
+    });
+
+    const decryptedCaCert = await kmsDecryptor({
+      cipherTextBlob: caCert.encryptedCertificate
+    });
+
+    const caCertObj = new x509.X509Certificate(decryptedCaCert);
+    const notBeforeDate = notBefore ? new Date(notBefore) : new Date();
+
+    let notAfterDate = new Date(new Date().setFullYear(new Date().getFullYear() + 1));
+    if (notAfter) {
+      notAfterDate = new Date(notAfter);
+    } else if (ttl) {
+      notAfterDate = new Date(new Date().getTime() + ms(ttl));
+    }
+
+    const caCertNotBeforeDate = new Date(caCertObj.notBefore);
+    const caCertNotAfterDate = new Date(caCertObj.notAfter);
+
+    // check not before constraint
+    if (notBeforeDate < caCertNotBeforeDate) {
+      throw new BadRequestError({ message: "notBefore date is before CA certificate's notBefore date" });
+    }
+
+    // check not after constraint
+    if (notAfterDate > caCertNotAfterDate) {
+      throw new BadRequestError({ message: "notAfter date is after CA certificate's notAfter date" });
+    }
+
+    const commonNameRegex = new RE2(certificateTemplate.commonName);
+    if (!commonNameRegex.test(commonName)) {
+      throw new BadRequestError({
+        message: "Invalid common name based on template policy"
+      });
+    }
+
+    if (notAfterDate.getTime() - notBeforeDate.getTime() > ms(certificateTemplate.ttl)) {
+      throw new BadRequestError({
+        message: "Invalid validity date based on template policy"
+      });
+    }
+
+    const subjectAlternativeNameRegex = new RE2(certificateTemplate.subjectAlternativeName);
+    altNames.split(",").forEach((altName) => {
+      if (!subjectAlternativeNameRegex.test(altName)) {
+        throw new BadRequestError({
+          message: "Invalid subject alternative name based on template policy"
+        });
+      }
+    });
+
+    const alg = keyAlgorithmToAlgCfg(ca.internalCa.keyAlgorithm as CertKeyAlgorithm);
+    const leafKeys = await crypto.subtle.generateKey(alg, true, ["sign", "verify"]);
+
+    const csrObj = await x509.Pkcs10CertificateRequestGenerator.create({
+      name: `CN=${commonName}`,
+      keys: leafKeys,
+      signingAlgorithm: alg,
+      extensions: [
+        // eslint-disable-next-line no-bitwise
+        new x509.KeyUsagesExtension(x509.KeyUsageFlags.digitalSignature | x509.KeyUsageFlags.keyEncipherment)
+      ],
+      attributes: [new x509.ChallengePasswordAttribute("password")]
+    });
+
+    const { caPrivateKey, caSecret } = await getCaCredentials({
+      caId: ca.id,
+      certificateAuthorityDAL,
+      certificateAuthoritySecretDAL,
+      projectDAL,
+      kmsService
+    });
+
+    const caCrl = await certificateAuthorityCrlDAL.findOne({ caSecretId: caSecret.id });
+    const appCfg = getConfig();
+
+    const distributionPointUrl = `${appCfg.SITE_URL}/api/v1/pki/crl/${caCrl.id}/der`;
+    const caIssuerUrl = `${appCfg.SITE_URL}/api/v1/pki/ca/${ca.id}/certificates/${caCert.id}/der`;
+
+    const extensions: x509.Extension[] = [
+      new x509.BasicConstraintsExtension(false),
+      new x509.CRLDistributionPointsExtension([distributionPointUrl]),
+      await x509.AuthorityKeyIdentifierExtension.create(caCertObj, false),
+      await x509.SubjectKeyIdentifierExtension.create(csrObj.publicKey),
+      new x509.AuthorityInfoAccessExtension({
+        caIssuers: new x509.GeneralName("url", caIssuerUrl)
+      }),
+      new x509.CertificatePolicyExtension(["2.5.29.32.0"]) // anyPolicy
+    ];
+
+    let selectedKeyUsages: CertKeyUsage[] = keyUsages ?? [];
+    if (keyUsages === undefined && !certificateTemplate) {
+      selectedKeyUsages = [CertKeyUsage.DIGITAL_SIGNATURE, CertKeyUsage.KEY_ENCIPHERMENT];
+    }
+
+    if (keyUsages === undefined && certificateTemplate) {
+      selectedKeyUsages = (certificateTemplate.keyUsages ?? []) as CertKeyUsage[];
+    }
+
+    if (keyUsages?.length && certificateTemplate) {
+      const validKeyUsages = certificateTemplate.keyUsages || [];
+      if (keyUsages.some((keyUsage) => !validKeyUsages.includes(keyUsage))) {
+        throw new BadRequestError({
+          message: "Invalid key usage value based on template policy"
+        });
+      }
+      selectedKeyUsages = keyUsages;
+    }
+
+    const keyUsagesBitValue = selectedKeyUsages.reduce((accum, keyUsage) => accum | x509.KeyUsageFlags[keyUsage], 0);
+    if (keyUsagesBitValue) {
+      extensions.push(new x509.KeyUsagesExtension(keyUsagesBitValue, true));
+    }
+
+    // handle extended key usages
+    let selectedExtendedKeyUsages: CertExtendedKeyUsage[] = extendedKeyUsages ?? [];
+    if (extendedKeyUsages === undefined && certificateTemplate) {
+      selectedExtendedKeyUsages = (certificateTemplate.extendedKeyUsages ?? []) as CertExtendedKeyUsage[];
+    }
+
+    if (extendedKeyUsages?.length && certificateTemplate) {
+      const validExtendedKeyUsages = certificateTemplate.extendedKeyUsages || [];
+      if (extendedKeyUsages.some((eku) => !validExtendedKeyUsages.includes(eku))) {
+        throw new BadRequestError({
+          message: "Invalid extended key usage value based on template policy"
+        });
+      }
+      selectedExtendedKeyUsages = extendedKeyUsages;
+    }
+
+    if (selectedExtendedKeyUsages.length) {
+      extensions.push(
+        new x509.ExtendedKeyUsageExtension(
+          selectedExtendedKeyUsages.map((eku) => x509.ExtendedKeyUsage[eku]),
+          true
+        )
+      );
+    }
+
+    let altNamesArray: { type: "email" | "dns"; value: string }[] = [];
+
+    if (altNames) {
+      altNamesArray = altNames.split(",").map((altName) => {
+        if (z.string().email().safeParse(altName).success) {
+          return { type: "email", value: altName };
+        }
+
+        if (isFQDN(altName, { allow_wildcard: true })) {
+          return { type: "dns", value: altName };
+        }
+
+        throw new BadRequestError({ message: `Invalid SAN entry: ${altName}` });
+      });
+
+      const altNamesExtension = new x509.SubjectAlternativeNameExtension(altNamesArray, false);
+      extensions.push(altNamesExtension);
+    }
+
+    const serialNumber = createSerialNumber();
+    const leafCert = await x509.X509CertificateGenerator.create({
+      serialNumber,
+      subject: csrObj.subject,
+      issuer: caCertObj.subject,
+      notBefore: notBeforeDate,
+      notAfter: notAfterDate,
+      signingKey: caPrivateKey,
+      publicKey: csrObj.publicKey,
+      signingAlgorithm: alg,
+      extensions
+    });
+
+    const skLeafObj = KeyObject.from(leafKeys.privateKey);
+    const skLeaf = skLeafObj.export({ format: "pem", type: "pkcs8" }) as string;
+
+    const kmsEncryptor = await kmsService.encryptWithKmsKey({
+      kmsId: certificateManagerKmsId
+    });
+    const { cipherTextBlob: encryptedCertificate } = await kmsEncryptor({
+      plainText: Buffer.from(new Uint8Array(leafCert.rawData))
+    });
+    const { cipherTextBlob: encryptedPrivateKey } = await kmsEncryptor({
+      plainText: Buffer.from(skLeaf)
+    });
+
+    const { caCert: issuingCaCertificate, caCertChain } = await getCaCertChain({
+      caCertId: caCert.id,
+      certificateAuthorityDAL,
+      certificateAuthorityCertDAL,
+      projectDAL,
+      kmsService
+    });
+
+    const certificateChainPem = `${issuingCaCertificate}\n${caCertChain}`.trim();
+
+    const { cipherTextBlob: encryptedCertificateChain } = await kmsEncryptor({
+      plainText: Buffer.from(certificateChainPem)
+    });
+
+    await certificateDAL.transaction(async (tx) => {
+      const cert = await certificateDAL.create(
+        {
+          caId: ca.id,
+          caCertId: caCert.id,
+          status: CertStatus.ACTIVE,
+          friendlyName: commonName,
+          commonName,
+          altNames,
+          serialNumber,
+          notBefore: notBeforeDate,
+          notAfter: notAfterDate,
+          keyUsages: selectedKeyUsages,
+          extendedKeyUsages: selectedExtendedKeyUsages,
+          projectId: ca.projectId,
+          certificateTemplateId: certificateTemplate.id
+        },
+        tx
+      );
+
+      await certificateBodyDAL.create(
+        {
+          certId: cert.id,
+          encryptedCertificate,
+          encryptedCertificateChain
+        },
+        tx
+      );
+
+      await certificateSecretDAL.create(
+        {
+          certId: cert.id,
+          encryptedPrivateKey
+        },
+        tx
+      );
+    });
+
+    return {
+      certificate: leafCert.toString("pem"),
+      certificateChain: certificateChainPem,
+      issuingCaCertificate,
+      privateKey: skLeaf,
+      serialNumber,
+      ca,
+      template: certificateTemplate
+    };
+  };
+
   return {
-    issueCertificate
+    issueCertificate,
+    issueCertificateWithTemplate
   };
 };

backend/src/services/certificate-authority/internal/internal-certificate-authority-schemas.ts

@@ -55,8 +55,4 @@ export const CreateInternalCertificateAuthoritySchema = GenericCreateCertificate
   configuration: InternalCertificateAuthorityConfigurationSchema
 });
 
-export const UpdateInternalCertificateAuthoritySchema = GenericUpdateCertificateAuthorityFieldsSchema(
-  CaType.INTERNAL
-).extend({
-  configuration: InternalCertificateAuthorityConfigurationSchema.optional()
-});
+export const UpdateInternalCertificateAuthoritySchema = GenericUpdateCertificateAuthorityFieldsSchema(CaType.INTERNAL);

backend/src/services/certificate-authority/internal/internal-certificate-authority-service.ts

@@ -1,5 +1,5 @@
 /* eslint-disable no-bitwise */
-import { ForbiddenError } from "@casl/ability";
+import { ForbiddenError, subject } from "@casl/ability";
 import * as x509 from "@peculiar/x509";
 import slugify from "@sindresorhus/slugify";
 import crypto, { KeyObject } from "crypto";
@@ -16,6 +16,7 @@ import { TPermissionServiceFactory } from "@app/ee/services/permission/permissio
 import {
   ProjectPermissionActions,
   ProjectPermissionCertificateActions,
+  ProjectPermissionPkiTemplateActions,
   ProjectPermissionSub
 } from "@app/ee/services/permission/project-permission";
 import { extractX509CertFromChain } from "@app/lib/certificates/extract-certificate";
@@ -1952,15 +1953,15 @@ export const internalCertificateAuthorityServiceFactory = ({
       actionProjectType: ActionProjectType.CertificateManager
     });
 
-    ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionActions.Read,
-      ProjectPermissionSub.CertificateTemplates
-    );
-
     const certificateTemplates = await certificateTemplateDAL.find({ caId });
 
     return {
-      certificateTemplates,
+      certificateTemplates: certificateTemplates.filter((el) =>
+        permission.can(
+          ProjectPermissionPkiTemplateActions.Read,
+          subject(ProjectPermissionSub.CertificateTemplates, { name: el.name })
+        )
+      ),
       ca: expandInternalCa(ca)
     };
   };

backend/src/services/certificate-authority/internal/internal-certificate-authority-types.ts

@@ -221,3 +221,13 @@ export type TOrderCertificateForSubscriberDTO = {
   subscriberId: string;
   caType: CaType;
 };
+
+export type TIssueCertWithTemplateDTO = {
+  commonName: string;
+  altNames: string;
+  ttl: string;
+  notBefore?: string;
+  notAfter?: string;
+  keyUsages?: CertKeyUsage[];
+  extendedKeyUsages?: CertExtendedKeyUsage[];
+};

backend/src/services/certificate-template/certificate-template-schema.ts

@@ -18,3 +18,20 @@ export const sanitizedCertificateTemplate = CertificateTemplatesSchema.pick({
     caName: z.string()
   })
 );
+
+export const sanitizedCertificateTemplateV2 = CertificateTemplatesSchema.pick({
+  id: true,
+  caId: true,
+  name: true,
+  commonName: true,
+  subjectAlternativeName: true,
+  pkiCollectionId: true,
+  ttl: true,
+  keyUsages: true,
+  extendedKeyUsages: true
+}).merge(
+  z.object({
+    projectId: z.string(),
+    caName: z.string()
+  })
+);

backend/src/services/certificate-template/certificate-template-service.ts

@@ -1,11 +1,14 @@
-import { ForbiddenError } from "@casl/ability";
+import { ForbiddenError, subject } from "@casl/ability";
 import * as x509 from "@peculiar/x509";
 import bcrypt from "bcrypt";
 
 import { ActionProjectType, TCertificateTemplateEstConfigsUpdate } from "@app/db/schemas";
 import { TLicenseServiceFactory } from "@app/ee/services/license/license-service";
 import { TPermissionServiceFactory } from "@app/ee/services/permission/permission-service";
-import { ProjectPermissionActions, ProjectPermissionSub } from "@app/ee/services/permission/project-permission";
+import {
+  ProjectPermissionPkiTemplateActions,
+  ProjectPermissionSub
+} from "@app/ee/services/permission/project-permission";
 import { extractX509CertFromChain } from "@app/lib/certificates/extract-certificate";
 import { getConfig } from "@app/lib/config/env";
 import { BadRequestError, NotFoundError } from "@app/lib/errors";
@@ -78,8 +81,8 @@ export const certificateTemplateServiceFactory = ({
     });
 
     ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionActions.Create,
-      ProjectPermissionSub.CertificateTemplates
+      ProjectPermissionPkiTemplateActions.Create,
+      subject(ProjectPermissionSub.CertificateTemplates, { name })
     );
 
     return certificateTemplateDAL.transaction(async (tx) => {
@@ -140,8 +143,8 @@ export const certificateTemplateServiceFactory = ({
     });
 
     ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionActions.Edit,
-      ProjectPermissionSub.CertificateTemplates
+      ProjectPermissionPkiTemplateActions.Edit,
+      subject(ProjectPermissionSub.CertificateTemplates, { name: certTemplate.name })
     );
 
     if (caId) {
@@ -153,6 +156,13 @@ export const certificateTemplateServiceFactory = ({
       }
     }
 
+    if (name) {
+      ForbiddenError.from(permission).throwUnlessCan(
+        ProjectPermissionPkiTemplateActions.Create,
+        subject(ProjectPermissionSub.CertificateTemplates, { name })
+      );
+    }
+
     return certificateTemplateDAL.transaction(async (tx) => {
       await certificateTemplateDAL.updateById(
         certTemplate.id,
@@ -198,8 +208,8 @@ export const certificateTemplateServiceFactory = ({
     });
 
     ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionActions.Delete,
-      ProjectPermissionSub.CertificateTemplates
+      ProjectPermissionPkiTemplateActions.Delete,
+      subject(ProjectPermissionSub.CertificateTemplates, { name: certTemplate.name })
     );
 
     await certificateTemplateDAL.deleteById(certTemplate.id);
@@ -225,8 +235,8 @@ export const certificateTemplateServiceFactory = ({
     });
 
     ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionActions.Read,
-      ProjectPermissionSub.CertificateTemplates
+      ProjectPermissionPkiTemplateActions.Read,
+      subject(ProjectPermissionSub.CertificateTemplates, { name: certTemplate.name })
     );
 
     return certTemplate;
@@ -267,8 +277,8 @@ export const certificateTemplateServiceFactory = ({
     });
 
     ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionActions.Edit,
-      ProjectPermissionSub.CertificateTemplates
+      ProjectPermissionPkiTemplateActions.Edit,
+      subject(ProjectPermissionSub.CertificateTemplates, { name: certTemplate.name })
     );
 
     const appCfg = getConfig();
@@ -350,8 +360,8 @@ export const certificateTemplateServiceFactory = ({
     });
 
     ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionActions.Edit,
-      ProjectPermissionSub.CertificateTemplates
+      ProjectPermissionPkiTemplateActions.Edit,
+      subject(ProjectPermissionSub.CertificateTemplates, { name: certTemplate.name })
     );
 
     const originalCaEstConfig = await certificateTemplateEstConfigDAL.findOne({
@@ -430,8 +440,8 @@ export const certificateTemplateServiceFactory = ({
       });
 
       ForbiddenError.from(permission).throwUnlessCan(
-        ProjectPermissionActions.Edit,
-        ProjectPermissionSub.CertificateTemplates
+        ProjectPermissionPkiTemplateActions.Edit,
+        subject(ProjectPermissionSub.CertificateTemplates, { name: certTemplate.name })
       );
     }
 

backend/src/services/identity-access-token/identity-access-token-service.ts

@@ -96,10 +96,15 @@ export const identityAccessTokenServiceFactory = ({
     }
     await validateAccessTokenExp({ ...identityAccessToken, accessTokenNumUses });
 
-    const { accessTokenMaxTTL, createdAt: accessTokenCreatedAt, accessTokenTTL } = identityAccessToken;
+    const {
+      accessTokenMaxTTL,
+      createdAt: accessTokenCreatedAt,
+      accessTokenTTL,
+      accessTokenPeriod
+    } = identityAccessToken;
 
-    // max ttl checks - will it go above max ttl
-    if (Number(accessTokenMaxTTL) > 0) {
+    // Only enforce Max TTL for non-periodic tokens
+    if (Number(accessTokenMaxTTL) > 0 && Number(accessTokenPeriod) === 0) {
       const accessTokenCreated = new Date(accessTokenCreatedAt);
       const ttlInMilliseconds = Number(accessTokenMaxTTL) * 1000;
       const currentDate = new Date();
@@ -125,6 +130,18 @@ export const identityAccessTokenServiceFactory = ({
       accessTokenLastRenewedAt: new Date()
     });
 
+    const ttl = Number(accessTokenTTL);
+    const period = Number(accessTokenPeriod);
+
+    let expiresIn: number | undefined;
+    if (period > 0) {
+      expiresIn = period;
+    } else if (ttl > 0) {
+      expiresIn = ttl;
+    } else {
+      expiresIn = undefined;
+    }
+
     const renewedToken = jwt.sign(
       {
         identityId: decodedToken.identityId,
@@ -133,12 +150,7 @@ export const identityAccessTokenServiceFactory = ({
         authTokenType: AuthTokenType.IDENTITY_ACCESS_TOKEN
       } as TIdentityAccessTokenJwtPayload,
       appCfg.AUTH_SECRET,
-      // akhilmhdh: for non-expiry tokens you should not even set the value, including undefined. Even for undefined jsonwebtoken throws error
-      Number(identityAccessToken.accessTokenTTL) === 0
-        ? undefined
-        : {
-            expiresIn: Number(identityAccessToken.accessTokenTTL)
-          }
+      expiresIn !== undefined ? { expiresIn } : undefined
     );
 
     return { accessToken: renewedToken, identityAccessToken: updatedIdentityAccessToken };

backend/src/services/identity-kubernetes-auth/identity-kubernetes-auth-service.ts

@@ -2,6 +2,7 @@ import { ForbiddenError } from "@casl/ability";
 import axios, { AxiosError } from "axios";
 import https from "https";
 import jwt from "jsonwebtoken";
+import RE2 from "re2";
 
 import { IdentityAuthMethod, TIdentityKubernetesAuthsUpdate } from "@app/db/schemas";
 import { TGatewayDALFactory } from "@app/ee/services/gateway/gateway-dal";
@@ -185,7 +186,13 @@ export const identityKubernetesAuthServiceFactory = ({
       return res.data;
     };
 
-    const [k8sHost, k8sPort] = identityKubernetesAuth.kubernetesHost.split(":");
+    let { kubernetesHost } = identityKubernetesAuth;
+
+    if (kubernetesHost.startsWith("https://") || kubernetesHost.startsWith("http://")) {
+      kubernetesHost = new RE2("^https?:\\/\\/").replace(kubernetesHost, "");
+    }
+
+    const [k8sHost, k8sPort] = kubernetesHost.split(":");
 
     const data = identityKubernetesAuth.gatewayId
       ? await $gatewayProxyWrapper(

backend/src/services/identity-ua/identity-ua-service.ts

@@ -114,21 +114,36 @@ export const identityUaServiceFactory = ({
       });
     }
 
+    const accessTokenTTLParams =
+      Number(identityUa.accessTokenPeriod) === 0
+        ? {
+            accessTokenTTL: identityUa.accessTokenTTL,
+            accessTokenMaxTTL: identityUa.accessTokenMaxTTL
+          }
+        : {
+            accessTokenTTL: identityUa.accessTokenPeriod,
+            // Setting Max TTL to 2 × period ensures that clients can always renew their token
+            // at least once, and matches client logic that checks if renewing would exceed Max TTL.
+            accessTokenMaxTTL: 2 * identityUa.accessTokenPeriod
+          };
+
     const identityAccessToken = await identityUaDAL.transaction(async (tx) => {
       const uaClientSecretDoc = await identityUaClientSecretDAL.incrementUsage(validClientSecretInfo!.id, tx);
+
       const newToken = await identityAccessTokenDAL.create(
         {
           identityId: identityUa.identityId,
           isAccessTokenRevoked: false,
           identityUAClientSecretId: uaClientSecretDoc.id,
-          accessTokenTTL: identityUa.accessTokenTTL,
-          accessTokenMaxTTL: identityUa.accessTokenMaxTTL,
           accessTokenNumUses: 0,
           accessTokenNumUsesLimit: identityUa.accessTokenNumUsesLimit,
-          authMethod: IdentityAuthMethod.UNIVERSAL_AUTH
+          accessTokenPeriod: identityUa.accessTokenPeriod,
+          authMethod: IdentityAuthMethod.UNIVERSAL_AUTH,
+          ...accessTokenTTLParams
         },
         tx
       );
+
       return newToken;
     });
 
@@ -149,7 +164,14 @@ export const identityUaServiceFactory = ({
           }
     );
 
-    return { accessToken, identityUa, validClientSecretInfo, identityAccessToken, identityMembershipOrg };
+    return {
+      accessToken,
+      identityUa,
+      validClientSecretInfo,
+      identityAccessToken,
+      identityMembershipOrg,
+      ...accessTokenTTLParams
+    };
   };
 
   const attachUniversalAuth = async ({
@@ -163,7 +185,8 @@ export const identityUaServiceFactory = ({
     actorAuthMethod,
     actor,
     actorOrgId,
-    isActorSuperAdmin
+    isActorSuperAdmin,
+    accessTokenPeriod
   }: TAttachUaDTO) => {
     await validateIdentityUpdateForSuperAdminPrivileges(identityId, isActorSuperAdmin);
 
@@ -232,7 +255,8 @@ export const identityUaServiceFactory = ({
           accessTokenMaxTTL,
           accessTokenTTL,
           accessTokenNumUsesLimit,
-          accessTokenTrustedIps: JSON.stringify(reformattedAccessTokenTrustedIps)
+          accessTokenTrustedIps: JSON.stringify(reformattedAccessTokenTrustedIps),
+          accessTokenPeriod
         },
         tx
       );
@@ -248,6 +272,7 @@ export const identityUaServiceFactory = ({
     accessTokenTTL,
     accessTokenTrustedIps,
     clientSecretTrustedIps,
+    accessTokenPeriod,
     actorId,
     actorAuthMethod,
     actor,
@@ -324,6 +349,7 @@ export const identityUaServiceFactory = ({
       accessTokenMaxTTL,
       accessTokenTTL,
       accessTokenNumUsesLimit,
+      accessTokenPeriod,
       accessTokenTrustedIps: reformattedAccessTokenTrustedIps
         ? JSON.stringify(reformattedAccessTokenTrustedIps)
         : undefined

backend/src/services/identity-ua/identity-ua-types.ts

@@ -5,6 +5,7 @@ export type TAttachUaDTO = {
   accessTokenTTL: number;
   accessTokenMaxTTL: number;
   accessTokenNumUsesLimit: number;
+  accessTokenPeriod: number;
   clientSecretTrustedIps: { ipAddress: string }[];
   accessTokenTrustedIps: { ipAddress: string }[];
   isActorSuperAdmin?: boolean;
@@ -15,6 +16,7 @@ export type TUpdateUaDTO = {
   accessTokenTTL?: number;
   accessTokenMaxTTL?: number;
   accessTokenNumUsesLimit?: number;
+  accessTokenPeriod?: number;
   clientSecretTrustedIps?: { ipAddress: string }[];
   accessTokenTrustedIps?: { ipAddress: string }[];
 } & Omit<TProjectPermission, "projectId">;

backend/src/services/pki-subscriber/pki-subscriber-service.ts

@@ -137,6 +137,15 @@ export const pkiSubscriberServiceFactory = ({
       }
     }
 
+    const ca = await certificateAuthorityDAL.findById(caId);
+    if (!ca) {
+      throw new NotFoundError({ message: `CA with ID '${caId}' not found` });
+    }
+
+    if (ca.projectId !== projectId) {
+      throw new BadRequestError({ message: "CA does not belong to the project" });
+    }
+
     const newSubscriber = await pkiSubscriberDAL.create({
       caId,
       projectId,
@@ -245,6 +254,17 @@ export const pkiSubscriberServiceFactory = ({
       }
     }
 
+    if (caId) {
+      const ca = await certificateAuthorityDAL.findById(caId);
+      if (!ca) {
+        throw new NotFoundError({ message: `CA with ID '${caId}' not found` });
+      }
+
+      if (ca.projectId !== projectId) {
+        throw new BadRequestError({ message: "CA does not belong to the project" });
+      }
+    }
+
     const updatedSubscriber = await pkiSubscriberDAL.updateById(subscriber.id, {
       caId,
       name,

backend/src/services/pki-templates/pki-templates-dal.ts

@@ -0,0 +1,102 @@
+import { Knex } from "knex";
+import { Tables } from "knex/types/tables";
+
+import { TDbClient } from "@app/db";
+import { TableName } from "@app/db/schemas";
+import { DatabaseError } from "@app/lib/errors";
+import { buildFindFilter, ormify, selectAllTableCols, TFindFilter, TFindOpt, TFindReturn } from "@app/lib/knex";
+
+export type TPkiTemplatesDALFactory = ReturnType<typeof pkiTemplatesDALFactory>;
+
+export const pkiTemplatesDALFactory = (db: TDbClient) => {
+  const orm = ormify(db, TableName.CertificateTemplate);
+
+  const findOne = async (
+    filter: Partial<Tables[TableName.CertificateTemplate]["base"] & { projectId: string }>,
+    tx?: Knex
+  ) => {
+    try {
+      const { projectId, ...templateFilters } = filter;
+      const res = await (tx || db.replicaNode())(TableName.CertificateTemplate)
+        .join(
+          TableName.CertificateAuthority,
+          `${TableName.CertificateAuthority}.id`,
+          `${TableName.CertificateTemplate}.caId`
+        )
+        // eslint-disable-next-line @typescript-eslint/no-misused-promises
+        .where(buildFindFilter(templateFilters, TableName.CertificateTemplate))
+        .where((qb) => {
+          if (projectId) {
+            // eslint-disable-next-line @typescript-eslint/no-misused-promises
+            void qb.where(buildFindFilter({ projectId }, TableName.CertificateAuthority));
+          }
+        })
+        .select(selectAllTableCols(TableName.CertificateTemplate))
+        .select(db.ref("name").withSchema(TableName.CertificateAuthority).as("caName"))
+        .select(db.ref("projectId").withSchema(TableName.CertificateAuthority))
+        .first();
+
+      if (!res) return undefined;
+
+      return { ...res, ca: { id: res.caId, name: res.caName } };
+    } catch (error) {
+      throw new DatabaseError({ error, name: "Find one" });
+    }
+  };
+
+  const find = async <
+    TCount extends boolean = false,
+    TCountDistinct extends keyof Tables[TableName.CertificateTemplate]["base"] | undefined = undefined
+  >(
+    filter: TFindFilter<Tables[TableName.CertificateTemplate]["base"]> & { projectId: string },
+    {
+      offset,
+      limit,
+      sort,
+      count,
+      tx,
+      countDistinct
+    }: TFindOpt<Tables[TableName.CertificateTemplate]["base"], TCount, TCountDistinct> = {}
+  ) => {
+    try {
+      const { projectId, ...templateFilters } = filter;
+
+      const query = (tx || db.replicaNode())(TableName.CertificateTemplate)
+        .join(
+          TableName.CertificateAuthority,
+          `${TableName.CertificateAuthority}.id`,
+          `${TableName.CertificateTemplate}.caId`
+        )
+        // eslint-disable-next-line @typescript-eslint/no-misused-promises
+        .where(buildFindFilter(templateFilters, TableName.CertificateTemplate))
+        .where((qb) => {
+          if (projectId) {
+            // eslint-disable-next-line @typescript-eslint/no-misused-promises
+            void qb.where(buildFindFilter({ projectId }, TableName.CertificateAuthority));
+          }
+        })
+        .select(selectAllTableCols(TableName.CertificateTemplate))
+        .select(db.ref("projectId").withSchema(TableName.CertificateAuthority))
+        .select(db.ref("name").withSchema(TableName.CertificateAuthority).as("caName"));
+
+      if (countDistinct) {
+        void query.countDistinct(countDistinct);
+      } else if (count) {
+        void query.select(db.raw("COUNT(*) OVER() AS count"));
+      }
+
+      if (limit) void query.limit(limit);
+      if (offset) void query.offset(offset);
+      if (sort) {
+        void query.orderBy(sort.map(([column, order, nulls]) => ({ column: column as string, order, nulls })));
+      }
+
+      const res = (await query) as TFindReturn<typeof query, TCountDistinct extends undefined ? TCount : true>;
+      return res.map((el) => ({ ...el, ca: { id: el.caId, name: el.caName } }));
+    } catch (error) {
+      throw new DatabaseError({ error, name: "Find one" });
+    }
+  };
+
+  return { ...orm, find, findOne };
+};

backend/src/services/pki-templates/pki-templates-service.ts

@@ -0,0 +1,644 @@
+/* eslint-disable no-bitwise */
+import { ForbiddenError, subject } from "@casl/ability";
+import * as x509 from "@peculiar/x509";
+import RE2 from "re2";
+
+import { ActionProjectType } from "@app/db/schemas";
+import { TCertificateAuthorityCrlDALFactory } from "@app/ee/services/certificate-authority-crl/certificate-authority-crl-dal";
+import { TPermissionServiceFactory } from "@app/ee/services/permission/permission-service";
+import {
+  ProjectPermissionPkiTemplateActions,
+  ProjectPermissionSub
+} from "@app/ee/services/permission/project-permission";
+import { getConfig } from "@app/lib/config/env";
+import { BadRequestError, NotFoundError } from "@app/lib/errors";
+import { ms } from "@app/lib/ms";
+
+import { TCertificateBodyDALFactory } from "../certificate/certificate-body-dal";
+import { TCertificateDALFactory } from "../certificate/certificate-dal";
+import { TCertificateSecretDALFactory } from "../certificate/certificate-secret-dal";
+import {
+  CertExtendedKeyUsage,
+  CertExtendedKeyUsageOIDToName,
+  CertKeyAlgorithm,
+  CertKeyUsage,
+  CertStatus
+} from "../certificate/certificate-types";
+import { TCertificateAuthorityCertDALFactory } from "../certificate-authority/certificate-authority-cert-dal";
+import { TCertificateAuthorityDALFactory } from "../certificate-authority/certificate-authority-dal";
+import { CaStatus } from "../certificate-authority/certificate-authority-enums";
+import {
+  createSerialNumber,
+  expandInternalCa,
+  getCaCertChain,
+  getCaCredentials,
+  keyAlgorithmToAlgCfg,
+  parseDistinguishedName
+} from "../certificate-authority/certificate-authority-fns";
+import { TCertificateAuthoritySecretDALFactory } from "../certificate-authority/certificate-authority-secret-dal";
+import { InternalCertificateAuthorityFns } from "../certificate-authority/internal/internal-certificate-authority-fns";
+import { TKmsServiceFactory } from "../kms/kms-service";
+import { TProjectDALFactory } from "../project/project-dal";
+import { getProjectKmsCertificateKeyId } from "../project/project-fns";
+import { TPkiTemplatesDALFactory } from "./pki-templates-dal";
+import {
+  TCreatePkiTemplateDTO,
+  TDeletePkiTemplateDTO,
+  TGetPkiTemplateDTO,
+  TIssueCertPkiTemplateDTO,
+  TListPkiTemplateDTO,
+  TSignCertPkiTemplateDTO,
+  TUpdatePkiTemplateDTO
+} from "./pki-templates-types";
+
+type TPkiTemplatesServiceFactoryDep = {
+  pkiTemplatesDAL: TPkiTemplatesDALFactory;
+  permissionService: Pick<TPermissionServiceFactory, "getProjectPermission">;
+  certificateAuthorityDAL: Pick<
+    TCertificateAuthorityDALFactory,
+    | "findByIdWithAssociatedCa"
+    | "findById"
+    | "transaction"
+    | "create"
+    | "updateById"
+    | "findWithAssociatedCa"
+    | "findOne"
+  >;
+  internalCaFns: ReturnType<typeof InternalCertificateAuthorityFns>;
+  kmsService: Pick<TKmsServiceFactory, "generateKmsKey" | "decryptWithKmsKey" | "encryptWithKmsKey">;
+  certificateAuthorityCertDAL: Pick<TCertificateAuthorityCertDALFactory, "findById">;
+  certificateAuthoritySecretDAL: Pick<TCertificateAuthoritySecretDALFactory, "findOne">;
+  certificateAuthorityCrlDAL: Pick<TCertificateAuthorityCrlDALFactory, "findOne">;
+  certificateDAL: Pick<
+    TCertificateDALFactory,
+    "create" | "transaction" | "countCertificatesForPkiSubscriber" | "findLatestActiveCertForSubscriber" | "find"
+  >;
+  certificateSecretDAL: Pick<TCertificateSecretDALFactory, "create" | "findOne">;
+  certificateBodyDAL: Pick<TCertificateBodyDALFactory, "create" | "findOne">;
+  projectDAL: Pick<TProjectDALFactory, "findOne" | "updateById" | "transaction" | "findById" | "find">;
+};
+
+export type TPkiTemplatesServiceFactory = ReturnType<typeof pkiTemplatesServiceFactory>;
+
+export const pkiTemplatesServiceFactory = ({
+  pkiTemplatesDAL,
+  permissionService,
+  internalCaFns,
+  certificateAuthorityDAL,
+  certificateAuthorityCertDAL,
+  certificateAuthoritySecretDAL,
+  certificateAuthorityCrlDAL,
+  certificateDAL,
+  certificateBodyDAL,
+  kmsService,
+  projectDAL
+}: TPkiTemplatesServiceFactoryDep) => {
+  const createTemplate = async ({
+    actor,
+    actorId,
+    actorAuthMethod,
+    actorOrgId,
+    caName,
+    commonName,
+    extendedKeyUsages,
+    keyUsages,
+    name,
+    subjectAlternativeName,
+    ttl,
+    projectId
+  }: TCreatePkiTemplateDTO) => {
+    const ca = await certificateAuthorityDAL.findOne({ name: caName, projectId });
+    if (!ca) {
+      throw new NotFoundError({
+        message: `CA with name ${caName} not found`
+      });
+    }
+
+    const { permission } = await permissionService.getProjectPermission({
+      actor,
+      actorId,
+      projectId: ca.projectId,
+      actorAuthMethod,
+      actorOrgId,
+      actionProjectType: ActionProjectType.CertificateManager
+    });
+
+    ForbiddenError.from(permission).throwUnlessCan(
+      ProjectPermissionPkiTemplateActions.Create,
+      subject(ProjectPermissionSub.CertificateTemplates, { name })
+    );
+
+    const existingTemplate = await pkiTemplatesDAL.findOne({ name, projectId: ca.projectId });
+    if (existingTemplate) {
+      throw new BadRequestError({ message: `Template with name ${name} already exists.` });
+    }
+
+    const newTemplate = await pkiTemplatesDAL.create({
+      caId: ca.id,
+      name,
+      commonName,
+      subjectAlternativeName,
+      ttl,
+      keyUsages,
+      extendedKeyUsages
+    });
+    return newTemplate;
+  };
+
+  const updateTemplate = async ({
+    templateName,
+    actor,
+    actorId,
+    actorAuthMethod,
+    actorOrgId,
+    caName,
+    commonName,
+    extendedKeyUsages,
+    keyUsages,
+    name,
+    subjectAlternativeName,
+    ttl,
+    projectId
+  }: TUpdatePkiTemplateDTO) => {
+    const certTemplate = await pkiTemplatesDAL.findOne({ name: templateName, projectId });
+    if (!certTemplate) {
+      throw new NotFoundError({
+        message: `Certificate template with name ${templateName} not found`
+      });
+    }
+
+    const { permission } = await permissionService.getProjectPermission({
+      actor,
+      actorId,
+      projectId: certTemplate.projectId,
+      actorAuthMethod,
+      actorOrgId,
+      actionProjectType: ActionProjectType.CertificateManager
+    });
+
+    ForbiddenError.from(permission).throwUnlessCan(
+      ProjectPermissionPkiTemplateActions.Edit,
+      subject(ProjectPermissionSub.CertificateTemplates, { name: templateName })
+    );
+
+    let caId;
+    if (caName) {
+      const ca = await certificateAuthorityDAL.findOne({ name: caName, projectId });
+      if (!ca || ca.projectId !== certTemplate.projectId) {
+        throw new NotFoundError({
+          message: `CA with name ${caName} not found`
+        });
+      }
+      caId = ca.id;
+    }
+
+    if (name) {
+      ForbiddenError.from(permission).throwUnlessCan(
+        ProjectPermissionPkiTemplateActions.Edit,
+        subject(ProjectPermissionSub.CertificateTemplates, { name })
+      );
+
+      const existingTemplate = await pkiTemplatesDAL.findOne({ name, projectId });
+      if (existingTemplate && existingTemplate.id !== certTemplate.id) {
+        throw new BadRequestError({ message: `Template with name ${name} already exists.` });
+      }
+    }
+
+    const updatedTemplate = await pkiTemplatesDAL.updateById(certTemplate.id, {
+      caId,
+      name,
+      commonName,
+      subjectAlternativeName,
+      ttl,
+      keyUsages,
+      extendedKeyUsages
+    });
+    return updatedTemplate;
+  };
+
+  const deleteTemplate = async ({
+    templateName,
+    actor,
+    actorId,
+    actorAuthMethod,
+    actorOrgId,
+    projectId
+  }: TDeletePkiTemplateDTO) => {
+    const certTemplate = await pkiTemplatesDAL.findOne({ name: templateName, projectId });
+    if (!certTemplate) {
+      throw new NotFoundError({
+        message: `Certificate template with name ${templateName} not found`
+      });
+    }
+
+    const { permission } = await permissionService.getProjectPermission({
+      actor,
+      actorId,
+      projectId: certTemplate.projectId,
+      actorAuthMethod,
+      actorOrgId,
+      actionProjectType: ActionProjectType.CertificateManager
+    });
+
+    ForbiddenError.from(permission).throwUnlessCan(
+      ProjectPermissionPkiTemplateActions.Delete,
+      subject(ProjectPermissionSub.CertificateTemplates, { name: templateName })
+    );
+
+    const deletedTemplate = await pkiTemplatesDAL.deleteById(certTemplate.id);
+    return deletedTemplate;
+  };
+
+  const getTemplateByName = async ({
+    templateName,
+    actor,
+    actorId,
+    actorAuthMethod,
+    actorOrgId,
+    projectId
+  }: TGetPkiTemplateDTO) => {
+    const certTemplate = await pkiTemplatesDAL.findOne({ name: templateName, projectId });
+    if (!certTemplate) {
+      throw new NotFoundError({
+        message: `Certificate template with name ${templateName} not found`
+      });
+    }
+
+    const { permission } = await permissionService.getProjectPermission({
+      actor,
+      actorId,
+      projectId: certTemplate.projectId,
+      actorAuthMethod,
+      actorOrgId,
+      actionProjectType: ActionProjectType.CertificateManager
+    });
+
+    ForbiddenError.from(permission).throwUnlessCan(
+      ProjectPermissionPkiTemplateActions.Read,
+      subject(ProjectPermissionSub.CertificateTemplates, { name: templateName })
+    );
+
+    return certTemplate;
+  };
+
+  const listTemplate = async ({
+    actor,
+    actorId,
+    actorAuthMethod,
+    actorOrgId,
+    projectId,
+    limit,
+    offset
+  }: TListPkiTemplateDTO) => {
+    const { permission } = await permissionService.getProjectPermission({
+      actor,
+      actorId,
+      projectId,
+      actorAuthMethod,
+      actorOrgId,
+      actionProjectType: ActionProjectType.CertificateManager
+    });
+
+    const certTemplate = await pkiTemplatesDAL.find({ projectId }, { limit, offset, count: true });
+    return {
+      certificateTemplates: certTemplate.filter((el) =>
+        permission.can(
+          ProjectPermissionPkiTemplateActions.Read,
+          subject(ProjectPermissionSub.CertificateTemplates, { name: el.name })
+        )
+      ),
+      totalCount: Number(certTemplate?.[0]?.count ?? 0)
+    };
+  };
+
+  const issueCertificate = async ({
+    templateName,
+    projectId,
+    commonName,
+    altNames,
+    ttl,
+    notBefore,
+    notAfter,
+    actorId,
+    actorAuthMethod,
+    actor,
+    actorOrgId,
+    keyUsages,
+    extendedKeyUsages
+  }: TIssueCertPkiTemplateDTO) => {
+    const certTemplate = await pkiTemplatesDAL.findOne({ name: templateName, projectId });
+    if (!certTemplate) {
+      throw new NotFoundError({
+        message: `Certificate template with name ${templateName} not found`
+      });
+    }
+
+    const { permission } = await permissionService.getProjectPermission({
+      actor,
+      actorId,
+      projectId: certTemplate.projectId,
+      actorAuthMethod,
+      actorOrgId,
+      actionProjectType: ActionProjectType.CertificateManager
+    });
+
+    ForbiddenError.from(permission).throwUnlessCan(
+      ProjectPermissionPkiTemplateActions.IssueCert,
+      subject(ProjectPermissionSub.CertificateTemplates, { name: templateName })
+    );
+
+    const ca = await certificateAuthorityDAL.findByIdWithAssociatedCa(certTemplate.caId);
+    if (ca.internalCa?.id) {
+      return internalCaFns.issueCertificateWithTemplate(ca, certTemplate, {
+        altNames,
+        commonName,
+        ttl,
+        extendedKeyUsages,
+        keyUsages,
+        notAfter,
+        notBefore
+      });
+    }
+
+    throw new BadRequestError({ message: "CA does not support immediate issuance of certificates" });
+  };
+
+  const signCertificate = async ({
+    templateName,
+    csr,
+    projectId,
+    actorId,
+    actorAuthMethod,
+    actor,
+    actorOrgId,
+    ttl
+  }: TSignCertPkiTemplateDTO) => {
+    const certTemplate = await pkiTemplatesDAL.findOne({ name: templateName, projectId });
+    if (!certTemplate) {
+      throw new NotFoundError({
+        message: `Certificate template with name ${templateName} not found`
+      });
+    }
+
+    const { permission } = await permissionService.getProjectPermission({
+      actor,
+      actorId,
+      projectId: certTemplate.projectId,
+      actorAuthMethod,
+      actorOrgId,
+      actionProjectType: ActionProjectType.CertificateManager
+    });
+
+    ForbiddenError.from(permission).throwUnlessCan(
+      ProjectPermissionPkiTemplateActions.IssueCert,
+      subject(ProjectPermissionSub.CertificateTemplates, { name: templateName })
+    );
+
+    const appCfg = getConfig();
+
+    const ca = await certificateAuthorityDAL.findByIdWithAssociatedCa(certTemplate.caId);
+    if (!ca?.internalCa) throw new NotFoundError({ message: `CA with ID '${certTemplate.caId}' not found` });
+
+    if (ca.status !== CaStatus.ACTIVE) throw new BadRequestError({ message: "CA is not active" });
+    if (!ca.internalCa?.activeCaCertId)
+      throw new BadRequestError({ message: "CA does not have a certificate installed" });
+
+    const caCert = await certificateAuthorityCertDAL.findById(ca.internalCa.activeCaCertId);
+
+    const certificateManagerKmsId = await getProjectKmsCertificateKeyId({
+      projectId: ca.projectId,
+      projectDAL,
+      kmsService
+    });
+    const kmsDecryptor = await kmsService.decryptWithKmsKey({
+      kmsId: certificateManagerKmsId
+    });
+
+    const decryptedCaCert = await kmsDecryptor({
+      cipherTextBlob: caCert.encryptedCertificate
+    });
+
+    const caCertObj = new x509.X509Certificate(decryptedCaCert);
+    const notBeforeDate = new Date();
+    const notAfterDate = new Date(new Date().getTime() + ms(ttl ?? "0"));
+    const caCertNotBeforeDate = new Date(caCertObj.notBefore);
+    const caCertNotAfterDate = new Date(caCertObj.notAfter);
+
+    // check not before constraint
+    if (notBeforeDate < caCertNotBeforeDate) {
+      throw new BadRequestError({ message: "notBefore date is before CA certificate's notBefore date" });
+    }
+
+    // check not after constraint
+    if (notAfterDate > caCertNotAfterDate) {
+      throw new BadRequestError({ message: "notAfter date is after CA certificate's notAfter date" });
+    }
+
+    const alg = keyAlgorithmToAlgCfg(ca.internalCa.keyAlgorithm as CertKeyAlgorithm);
+
+    const csrObj = new x509.Pkcs10CertificateRequest(csr);
+    const dn = parseDistinguishedName(csrObj.subject);
+    const cn = dn.commonName;
+    if (!cn)
+      throw new BadRequestError({
+        message: "Missing common name on CSR"
+      });
+
+    const commonNameRegex = new RE2(certTemplate.commonName);
+    if (!commonNameRegex.test(cn)) {
+      throw new BadRequestError({
+        message: "Invalid common name based on template policy"
+      });
+    }
+
+    if (ms(ttl) > ms(certTemplate.ttl)) {
+      throw new BadRequestError({
+        message: "Invalid validity date based on template policy"
+      });
+    }
+
+    const { caPrivateKey, caSecret } = await getCaCredentials({
+      caId: ca.id,
+      certificateAuthorityDAL,
+      certificateAuthoritySecretDAL,
+      projectDAL,
+      kmsService
+    });
+
+    const caCrl = await certificateAuthorityCrlDAL.findOne({ caSecretId: caSecret.id });
+    const distributionPointUrl = `${appCfg.SITE_URL}/api/v1/pki/crl/${caCrl.id}/der`;
+    const caIssuerUrl = `${appCfg.SITE_URL}/api/v1/pki/ca/${ca.id}/certificates/${caCert.id}/der`;
+
+    const extensions: x509.Extension[] = [
+      new x509.BasicConstraintsExtension(false),
+      await x509.AuthorityKeyIdentifierExtension.create(caCertObj, false),
+      await x509.SubjectKeyIdentifierExtension.create(csrObj.publicKey),
+      new x509.CRLDistributionPointsExtension([distributionPointUrl]),
+      new x509.AuthorityInfoAccessExtension({
+        caIssuers: new x509.GeneralName("url", caIssuerUrl)
+      }),
+      new x509.CertificatePolicyExtension(["2.5.29.32.0"]) // anyPolicy
+    ];
+
+    // handle key usages
+    const csrKeyUsageExtension = csrObj.getExtension("2.5.29.15") as x509.KeyUsagesExtension | undefined; // Better to type as optional
+    let selectedKeyUsages: CertKeyUsage[] = [];
+    if (csrKeyUsageExtension && csrKeyUsageExtension.usages) {
+      selectedKeyUsages = Object.values(CertKeyUsage).filter(
+        (keyUsage) => (x509.KeyUsageFlags[keyUsage] & csrKeyUsageExtension.usages) !== 0
+      );
+      const validKeyUsages = certTemplate.keyUsages || [];
+      if (selectedKeyUsages.some((keyUsage) => !validKeyUsages.includes(keyUsage))) {
+        throw new BadRequestError({
+          message: "Invalid key usage value based on template policy"
+        });
+      }
+
+      const keyUsagesBitValue = selectedKeyUsages.reduce((accum, keyUsage) => accum | x509.KeyUsageFlags[keyUsage], 0);
+      if (keyUsagesBitValue) {
+        extensions.push(new x509.KeyUsagesExtension(keyUsagesBitValue, true));
+      }
+    }
+
+    // handle extended key usage
+    const csrExtendedKeyUsageExtension = csrObj.getExtension("2.5.29.37") as x509.ExtendedKeyUsageExtension | undefined;
+    let selectedExtendedKeyUsages: CertExtendedKeyUsage[] = [];
+    if (csrExtendedKeyUsageExtension && csrExtendedKeyUsageExtension.usages.length > 0) {
+      selectedExtendedKeyUsages = csrExtendedKeyUsageExtension.usages.map(
+        (ekuOid) => CertExtendedKeyUsageOIDToName[ekuOid as string]
+      );
+
+      if (selectedExtendedKeyUsages.some((eku) => !certTemplate?.extendedKeyUsages?.includes(eku))) {
+        throw new BadRequestError({
+          message: "Invalid extended key usage value based on subscriber's specified extended key usages"
+        });
+      }
+
+      if (selectedExtendedKeyUsages.length) {
+        extensions.push(
+          new x509.ExtendedKeyUsageExtension(
+            selectedExtendedKeyUsages.map((eku) => x509.ExtendedKeyUsage[eku]),
+            true
+          )
+        );
+      }
+    }
+
+    // attempt to read from CSR if altNames is not explicitly provided
+    let altNamesArray: {
+      type: "email" | "dns";
+      value: string;
+    }[] = [];
+
+    const sanExtension = csrObj.extensions.find((ext) => ext.type === "2.5.29.17");
+    if (sanExtension) {
+      const sanNames = new x509.GeneralNames(sanExtension.value);
+
+      altNamesArray = sanNames.items
+        .filter((value) => value.type === "email" || value.type === "dns")
+        .map((name) => ({
+          type: name.type as "email" | "dns",
+          value: name.value
+        }));
+    }
+
+    if (altNamesArray.length) {
+      const altNamesExtension = new x509.SubjectAlternativeNameExtension(altNamesArray, false);
+      extensions.push(altNamesExtension);
+    }
+
+    const subjectAlternativeNameRegex = new RE2(certTemplate.subjectAlternativeName);
+    altNamesArray.forEach((altName) => {
+      if (!subjectAlternativeNameRegex.test(altName.value)) {
+        throw new BadRequestError({
+          message: "Invalid subject alternative name based on template policy"
+        });
+      }
+    });
+
+    const serialNumber = createSerialNumber();
+    const leafCert = await x509.X509CertificateGenerator.create({
+      serialNumber,
+      subject: csrObj.subject,
+      issuer: caCertObj.subject,
+      notBefore: notBeforeDate,
+      notAfter: notAfterDate,
+      signingKey: caPrivateKey,
+      publicKey: csrObj.publicKey,
+      signingAlgorithm: alg,
+      extensions
+    });
+
+    const kmsEncryptor = await kmsService.encryptWithKmsKey({
+      kmsId: certificateManagerKmsId
+    });
+    const { cipherTextBlob: encryptedCertificate } = await kmsEncryptor({
+      plainText: Buffer.from(new Uint8Array(leafCert.rawData))
+    });
+
+    const { caCert: issuingCaCertificate, caCertChain } = await getCaCertChain({
+      caCertId: ca.internalCa.activeCaCertId,
+      certificateAuthorityDAL,
+      certificateAuthorityCertDAL,
+      projectDAL,
+      kmsService
+    });
+
+    const certificateChainPem = `${issuingCaCertificate}\n${caCertChain}`.trim();
+
+    const { cipherTextBlob: encryptedCertificateChain } = await kmsEncryptor({
+      plainText: Buffer.from(certificateChainPem)
+    });
+
+    await certificateDAL.transaction(async (tx) => {
+      const cert = await certificateDAL.create(
+        {
+          caId: ca.id,
+          caCertId: caCert.id,
+          status: CertStatus.ACTIVE,
+          friendlyName: cn,
+          commonName: cn,
+          altNames: altNamesArray.map((el) => el.value).join(","),
+          serialNumber,
+          notBefore: notBeforeDate,
+          notAfter: notAfterDate,
+          keyUsages: selectedKeyUsages,
+          extendedKeyUsages: selectedExtendedKeyUsages,
+          projectId
+        },
+        tx
+      );
+
+      await certificateBodyDAL.create(
+        {
+          certId: cert.id,
+          encryptedCertificate,
+          encryptedCertificateChain
+        },
+        tx
+      );
+
+      return cert;
+    });
+
+    return {
+      certificate: leafCert.toString("pem"),
+      certificateChain: `${issuingCaCertificate}\n${caCertChain}`.trim(),
+      issuingCaCertificate,
+      serialNumber,
+      ca: expandInternalCa(ca),
+      commonName: cn,
+      template: certTemplate
+    };
+  };
+
+  return {
+    createTemplate,
+    updateTemplate,
+    getTemplateByName,
+    listTemplate,
+    deleteTemplate,
+    signCertificate,
+    issueCertificate
+  };
+};

backend/src/services/pki-templates/pki-templates-types.ts

@@ -0,0 +1,53 @@
+import { TProjectPermission } from "@app/lib/types";
+import { CertExtendedKeyUsage, CertKeyUsage } from "@app/services/certificate/certificate-types";
+
+export type TCreatePkiTemplateDTO = {
+  caName: string;
+  name: string;
+  commonName: string;
+  subjectAlternativeName: string;
+  ttl: string;
+  keyUsages: CertKeyUsage[];
+  extendedKeyUsages: CertExtendedKeyUsage[];
+} & TProjectPermission;
+
+export type TUpdatePkiTemplateDTO = {
+  templateName: string;
+  caName?: string;
+  name?: string;
+  commonName?: string;
+  subjectAlternativeName?: string;
+  ttl?: string;
+  keyUsages?: CertKeyUsage[];
+  extendedKeyUsages?: CertExtendedKeyUsage[];
+} & TProjectPermission;
+
+export type TListPkiTemplateDTO = {
+  limit?: number;
+  offset?: number;
+} & TProjectPermission;
+
+export type TGetPkiTemplateDTO = {
+  templateName: string;
+} & TProjectPermission;
+
+export type TDeletePkiTemplateDTO = {
+  templateName: string;
+} & TProjectPermission;
+
+export type TIssueCertPkiTemplateDTO = {
+  templateName: string;
+  commonName: string;
+  altNames: string;
+  ttl: string;
+  notBefore?: string;
+  notAfter?: string;
+  keyUsages?: CertKeyUsage[];
+  extendedKeyUsages?: CertExtendedKeyUsage[];
+} & TProjectPermission;
+
+export type TSignCertPkiTemplateDTO = {
+  templateName: string;
+  csr: string;
+  ttl: string;
+} & TProjectPermission;

backend/src/services/project/project-service.ts

@@ -17,6 +17,7 @@ import {
   ProjectPermissionActions,
   ProjectPermissionCertificateActions,
   ProjectPermissionPkiSubscriberActions,
+  ProjectPermissionPkiTemplateActions,
   ProjectPermissionSecretActions,
   ProjectPermissionSshHostActions,
   ProjectPermissionSub
@@ -1131,15 +1132,15 @@ export const projectServiceFactory = ({
       actionProjectType: ActionProjectType.CertificateManager
     });
 
-    ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionActions.Read,
-      ProjectPermissionSub.CertificateTemplates
-    );
-
     const certificateTemplates = await certificateTemplateDAL.getCertTemplatesByProjectId(projectId);
 
     return {
-      certificateTemplates
+      certificateTemplates: certificateTemplates.filter((el) =>
+        permission.can(
+          ProjectPermissionPkiTemplateActions.Read,
+          subject(ProjectPermissionSub.CertificateTemplates, { name: el.name })
+        )
+      )
     };
   };
 

backend/src/services/secret-sharing/secret-sharing-service.ts

@@ -115,8 +115,6 @@ export const secretSharingServiceFactory = ({
 
     const encryptWithRoot = kmsService.encryptWithRootKey();
 
-    let salt: string | undefined;
-    let encryptedSalt: Buffer | undefined;
     const orgEmails = [];
 
     if (emails && emails.length > 0) {
@@ -133,10 +131,6 @@ export const secretSharingServiceFactory = ({
           });
         }
       }
-
-      // Generate salt for signing email hashes (if emails are provided)
-      salt = crypto.randomBytes(32).toString("hex");
-      encryptedSalt = encryptWithRoot(Buffer.from(salt));
     }
 
     const encryptedSecret = encryptWithRoot(Buffer.from(secretValue));
@@ -158,14 +152,13 @@ export const secretSharingServiceFactory = ({
       userId: actorId,
       orgId,
       accessType,
-      authorizedEmails: emails && emails.length > 0 ? JSON.stringify(emails) : undefined,
-      encryptedSalt
+      authorizedEmails: emails && emails.length > 0 ? JSON.stringify(emails) : undefined
     });
 
     const idToReturn = `${Buffer.from(newSharedSecret.identifier!, "hex").toString("base64url")}`;
 
     // Loop through recipients and send out emails with unique access links
-    if (emails && salt) {
+    if (emails) {
       const user = await userDAL.findById(actorId);
 
       if (!user) {
@@ -174,9 +167,6 @@ export const secretSharingServiceFactory = ({
 
       for await (const email of emails) {
         try {
-          const hmac = crypto.createHmac("sha256", salt).update(email);
-          const hash = hmac.digest("hex");
-
           // Only show the username to emails which are part of the organization
           const respondentUsername = orgEmails.includes(email) ? user.username : undefined;
 
@@ -186,7 +176,7 @@ export const secretSharingServiceFactory = ({
             substitutions: {
               name,
               respondentUsername,
-              secretRequestUrl: `${appCfg.SITE_URL}/shared/secret/${idToReturn}?email=${encodeURIComponent(email)}&hash=${hash}`
+              secretRequestUrl: `${appCfg.SITE_URL}/shared/secret/${idToReturn}`
             },
             template: SmtpTemplates.SecretRequestCompleted
           });
@@ -474,9 +464,8 @@ export const secretSharingServiceFactory = ({
     sharedSecretId,
     hashedHex,
     orgId,
-    password,
-    email,
-    hash
+    actorId,
+    password
   }: TGetActiveSharedSecretByIdDTO) => {
     const sharedSecret = isUuidV4(sharedSecretId)
       ? await secretSharingDAL.findOne({
@@ -506,6 +495,17 @@ export const secretSharingServiceFactory = ({
       throw new ForbiddenRequestError();
     }
 
+    // If the secret was shared with specific emails, verify that the current user's session email is authorized
+    if (sharedSecret.authorizedEmails && (sharedSecret.authorizedEmails as string[]).length > 0) {
+      if (!actorId) throw new UnauthorizedError();
+
+      const user = await userDAL.findById(actorId);
+      if (!user || !user.email) throw new UnauthorizedError();
+
+      if (!(sharedSecret.authorizedEmails as string[]).includes(user.email))
+        throw new UnauthorizedError({ message: "Email not authorized to view secret" });
+    }
+
     // all secrets pass through here, meaning we check if its expired first and then check if it needs verification
     // or can be safely sent to the client.
     if (expiresAt !== null && expiresAt < new Date()) {
@@ -524,31 +524,6 @@ export const secretSharingServiceFactory = ({
       });
     }
 
-    const decryptWithRoot = kmsService.decryptWithRootKey();
-
-    if (sharedSecret.authorizedEmails && sharedSecret.encryptedSalt) {
-      // Verify both params were passed
-      if (!email || !hash) {
-        throw new BadRequestError({
-          message: "This secret is email protected. Parameters must include email and hash."
-        });
-
-        // Verify that email is authorized to view shared secret
-      } else if (!(sharedSecret.authorizedEmails as string[]).includes(email)) {
-        throw new UnauthorizedError({ message: "Email not authorized to view secret" });
-
-        // Verify that hash matches
-      } else {
-        const salt = decryptWithRoot(sharedSecret.encryptedSalt).toString();
-        const hmac = crypto.createHmac("sha256", salt).update(email);
-        const rebuiltHash = hmac.digest("hex");
-
-        if (rebuiltHash !== hash) {
-          throw new UnauthorizedError({ message: "Email not authorized to view secret" });
-        }
-      }
-    }
-
     // Password checks
     const isPasswordProtected = Boolean(sharedSecret.password);
     const hasProvidedPassword = Boolean(password);
@@ -561,6 +536,8 @@ export const secretSharingServiceFactory = ({
       }
     }
 
+    const decryptWithRoot = kmsService.decryptWithRootKey();
+
     // If encryptedSecret is set, we know that this secret has been encrypted using KMS, and we can therefore do server-side decryption.
     let decryptedSecretValue: Buffer | undefined;
     if (sharedSecret.encryptedSecret) {

backend/src/services/secret-sharing/secret-sharing-types.ts

@@ -37,11 +37,8 @@ export type TGetActiveSharedSecretByIdDTO = {
   sharedSecretId: string;
   hashedHex?: string;
   orgId?: string;
+  actorId?: string;
   password?: string;
-
-  // For secrets shared with specific emails
-  email?: string;
-  hash?: string;
 };
 
 export type TValidateActiveSharedSecretDTO = TGetActiveSharedSecretByIdDTO & {