Update go.mdx

fix: share secret input now masks value onBlur

.github/workflows/build-staging-and-deploy-aws.yml

@@ -74,21 +74,21 @@ jobs:
         uses: pr-mpt/actions-commit-hash@v2
       - name: Download task definition
         run: |
-          aws ecs describe-task-definition --task-definition infisical-prod-platform --query taskDefinition > task-definition.json
+          aws ecs describe-task-definition --task-definition infisical-core-platform --query taskDefinition > task-definition.json
       - name: Render Amazon ECS task definition
         id: render-web-container
         uses: aws-actions/amazon-ecs-render-task-definition@v1
         with:
           task-definition: task-definition.json
-          container-name: infisical-prod-platform
+          container-name: infisical-core-platform
           image: infisical/staging_infisical:${{ steps.commit.outputs.short }}
           environment-variables: "LOG_LEVEL=info"
       - name: Deploy to Amazon ECS service
         uses: aws-actions/amazon-ecs-deploy-task-definition@v1
         with:
           task-definition: ${{ steps.render-web-container.outputs.task-definition }}
-          service: infisical-prod-platform
-          cluster: infisical-prod-platform
+          service: infisical-core-platform
+          cluster: infisical-core-platform
           wait-for-service-stability: true
 
   production-postgres-deployment:
@@ -122,19 +122,19 @@ jobs:
         uses: pr-mpt/actions-commit-hash@v2
       - name: Download task definition
         run: |
-          aws ecs describe-task-definition --task-definition infisical-prod-platform --query taskDefinition > task-definition.json
+          aws ecs describe-task-definition --task-definition infisical-core-platform --query taskDefinition > task-definition.json
       - name: Render Amazon ECS task definition
         id: render-web-container
         uses: aws-actions/amazon-ecs-render-task-definition@v1
         with:
           task-definition: task-definition.json
-          container-name: infisical-prod-platform
+          container-name: infisical-core-platform
           image: infisical/staging_infisical:${{ steps.commit.outputs.short }}
           environment-variables: "LOG_LEVEL=info"
       - name: Deploy to Amazon ECS service
         uses: aws-actions/amazon-ecs-deploy-task-definition@v1
         with:
           task-definition: ${{ steps.render-web-container.outputs.task-definition }}
-          service: infisical-prod-platform
-          cluster: infisical-prod-platform
+          service: infisical-core-platform
+          cluster: infisical-core-platform
           wait-for-service-stability: true

.github/workflows/check-api-for-breaking-changes.yml

@@ -40,13 +40,14 @@ jobs:
           REDIS_URL: redis://172.17.0.1:6379
           DB_CONNECTION_URI: postgres://infisical:infisical@172.17.0.1:5432/infisical?sslmode=disable
           JWT_AUTH_SECRET: something-random
+          ENCRYPTION_KEY: 4bnfe4e407b8921c104518903515b218
       - uses: actions/setup-go@v5
         with:
           go-version: '1.21.5'
       - name: Wait for container to be stable and check logs
         run: |
           SECONDS=0
-          HEALTHY=0
+ r        HEALTHY=0
           while [ $SECONDS -lt 60 ]; do
             if docker ps | grep infisical-api | grep -q healthy; then
               echo "Container is healthy."
@@ -73,4 +74,4 @@ jobs:
         run: |
           docker-compose -f "docker-compose.dev.yml" down
           docker stop infisical-api
-          docker remove infisical-api
+          docker remove infisical-api

.github/workflows/update-be-new-migration-latest-timestamp.yml

@@ -38,6 +38,16 @@ jobs:
           rm added_files.txt
           git commit -m "chore: renamed new migration files to latest timestamp (gh-action)"
 
+      - name: Get PR details
+        id: pr_details
+        run: |
+          PR_NUMBER=${{ github.event.pull_request.number }}
+          PR_MERGER=$(curl -s "https://api.github.com/repos/${{ github.repository }}/pulls/$PR_NUMBER" | jq -r '.merged_by.login')
+          
+          echo "PR Number: $PR_NUMBER"
+          echo "PR Merger: $PR_MERGER"
+          echo "pr_merger=$PR_MERGER" >> $GITHUB_OUTPUT
+
       - name: Create Pull Request
         if: env.SKIP_RENAME != 'true'
         uses: peter-evans/create-pull-request@v6
@@ -46,3 +56,4 @@ jobs:
           commit-message: 'chore: renamed new migration files to latest UTC (gh-action)'
           title: 'GH Action: rename new migration file timestamp'
           branch-suffix: timestamp
+          reviewers: ${{ steps.pr_details.outputs.pr_merger }}

.infisicalignore

@@ -3,4 +3,5 @@ frontend/src/views/Project/MembersPage/components/IdentityTab/components/Identit
 frontend/src/views/Project/MembersPage/components/IdentityTab/components/IdentityRoleForm/SpecificPrivilegeSection.tsx:generic-api-key:304
 frontend/src/views/Project/MembersPage/components/MemberListTab/MemberRoleForm/MemberRbacSection.tsx:generic-api-key:206
 frontend/src/views/Project/MembersPage/components/MemberListTab/MemberRoleForm/SpecificPrivilegeSection.tsx:generic-api-key:292
+docs/self-hosting/configuration/envars.mdx:generic-api-key:106
 frontend/src/views/Project/MembersPage/components/MemberListTab/MemberRoleForm/SpecificPrivilegeSection.tsx:generic-api-key:451

Dockerfile.standalone-infisical

@@ -1,7 +1,6 @@
 ARG POSTHOG_HOST=https://app.posthog.com
 ARG POSTHOG_API_KEY=posthog-api-key
 ARG INTERCOM_ID=intercom-id
-ARG SAML_ORG_SLUG=saml-org-slug-default
 
 FROM  node:20-alpine AS base
 
@@ -35,9 +34,7 @@ ENV NEXT_PUBLIC_POSTHOG_API_KEY $POSTHOG_API_KEY
 ARG INTERCOM_ID
 ENV NEXT_PUBLIC_INTERCOM_ID $INTERCOM_ID
 ARG INFISICAL_PLATFORM_VERSION
-ENV NEXT_PUBLIC_INFISICAL_PLATFORM_VERSION $INFISICAL_PLATFORM_VERSION
-ARG SAML_ORG_SLUG
-ENV NEXT_PUBLIC_SAML_ORG_SLUG=$SAML_ORG_SLUG 
+ENV NEXT_PUBLIC_INFISICAL_PLATFORM_VERSION $INFISICAL_PLATFORM_VERSION 
 
 # Build
 RUN npm run build
@@ -55,6 +52,7 @@ VOLUME /app/.next/cache/images
 COPY --chown=non-root-user:nodejs --chmod=555 frontend/scripts ./scripts
 COPY --from=frontend-builder /app/public ./public
 RUN chown non-root-user:nodejs ./public/data
+
 COPY --from=frontend-builder --chown=non-root-user:nodejs /app/.next/standalone ./
 COPY --from=frontend-builder --chown=non-root-user:nodejs /app/.next/static ./.next/static
 
@@ -93,9 +91,18 @@ RUN mkdir frontend-build
 
 # Production stage
 FROM base AS production
+RUN apk add --upgrade --no-cache ca-certificates
 RUN addgroup --system --gid 1001 nodejs \
   && adduser --system --uid 1001 non-root-user
 
+# Give non-root-user permission to update SSL certs
+RUN chown -R non-root-user /etc/ssl/certs
+RUN chown non-root-user /etc/ssl/certs/ca-certificates.crt
+RUN chmod -R u+rwx /etc/ssl/certs
+RUN chmod u+rw /etc/ssl/certs/ca-certificates.crt
+RUN chown non-root-user /usr/sbin/update-ca-certificates
+RUN chmod u+rx /usr/sbin/update-ca-certificates
+
 ## set pre baked keys
 ARG POSTHOG_API_KEY
 ENV NEXT_PUBLIC_POSTHOG_API_KEY=$POSTHOG_API_KEY \
@@ -103,9 +110,6 @@ ENV NEXT_PUBLIC_POSTHOG_API_KEY=$POSTHOG_API_KEY \
 ARG INTERCOM_ID=intercom-id
 ENV NEXT_PUBLIC_INTERCOM_ID=$INTERCOM_ID \
   BAKED_NEXT_PUBLIC_INTERCOM_ID=$INTERCOM_ID
-ARG SAML_ORG_SLUG
-ENV NEXT_PUBLIC_SAML_ORG_SLUG=$SAML_ORG_SLUG \
-  BAKED_NEXT_PUBLIC_SAML_ORG_SLUG=$SAML_ORG_SLUG
 
 WORKDIR / 
 

backend/e2e-test/mocks/keystore.ts

@@ -1,4 +1,5 @@
 import { TKeyStoreFactory } from "@app/keystore/keystore";
+import { Lock } from "@app/lib/red-lock";
 
 export const mockKeyStore = (): TKeyStoreFactory => {
   const store: Record<string, string | number | Buffer> = {};
@@ -25,6 +26,12 @@ export const mockKeyStore = (): TKeyStoreFactory => {
     },
     incrementBy: async () => {
       return 1;
-    }
+    },
+    acquireLock: () => {
+      return Promise.resolve({
+        release: () => {}
+      }) as Promise<Lock>;
+    },
+    waitTillReady: async () => {}
   };
 };

backend/package.json

@@ -95,11 +95,13 @@
     "axios": "^1.6.7",
     "axios-retry": "^4.0.0",
     "bcrypt": "^5.1.1",
-    "bullmq": "^5.3.3",
+    "bullmq": "^5.4.2",
     "cassandra-driver": "^4.7.2",
     "dotenv": "^16.4.1",
     "fastify": "^4.26.0",
     "fastify-plugin": "^4.5.1",
+    "google-auth-library": "^9.9.0",
+    "googleapis": "^137.1.0",
     "handlebars": "^4.7.8",
     "ioredis": "^5.3.2",
     "jmespath": "^0.16.0",
@@ -110,7 +112,7 @@
     "libsodium-wrappers": "^0.7.13",
     "lodash.isequal": "^4.5.0",
     "ms": "^2.1.3",
-    "mysql2": "^3.9.4",
+    "mysql2": "^3.9.8",
     "nanoid": "^5.0.4",
     "nodemailer": "^6.9.9",
     "ora": "^7.0.1",

backend/scripts/generate-schema-types.ts

@@ -35,6 +35,8 @@ const getZodPrimitiveType = (type: string) => {
       return "z.coerce.number()";
     case "text":
       return "z.string()";
+    case "bytea":
+      return "zodBuffer";
     default:
       throw new Error(`Invalid type: ${type}`);
   }
@@ -96,10 +98,15 @@ const main = async () => {
     const columnNames = Object.keys(columns);
 
     let schema = "";
+    const zodImportSet = new Set<string>();
     for (let colNum = 0; colNum < columnNames.length; colNum++) {
       const columnName = columnNames[colNum];
       const colInfo = columns[columnName];
       let ztype = getZodPrimitiveType(colInfo.type);
+      if (["zodBuffer"].includes(ztype)) {
+        zodImportSet.add(ztype);
+      }
+
       // don't put optional on id
       if (colInfo.defaultValue && columnName !== "id") {
         const { defaultValue } = colInfo;
@@ -121,6 +128,8 @@ const main = async () => {
       .split("_")
       .reduce((prev, curr) => prev + `${curr.at(0)?.toUpperCase()}${curr.slice(1).toLowerCase()}`, "");
 
+    const zodImports = Array.from(zodImportSet);
+
     // the insert and update are changed to zod input type to use default cases
     writeFileSync(
       path.join(__dirname, "../src/db/schemas", `${dashcase}.ts`),
@@ -131,6 +140,8 @@ const main = async () => {
 
 import { z } from "zod";
 
+${zodImports.length ? `import { ${zodImports.join(",")} } from \"@app/lib/zod\";` : ""}
+
 import { TImmutableDBKeys } from "./models";
 
 export const ${pascalCase}Schema = z.object({${schema}});

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

@@ -32,6 +32,10 @@ import { TAuthTokenServiceFactory } from "@app/services/auth-token/auth-token-se
 import { TGroupProjectServiceFactory } from "@app/services/group-project/group-project-service";
 import { TIdentityServiceFactory } from "@app/services/identity/identity-service";
 import { TIdentityAccessTokenServiceFactory } from "@app/services/identity-access-token/identity-access-token-service";
+import { TIdentityAwsAuthServiceFactory } from "@app/services/identity-aws-auth/identity-aws-auth-service";
+import { TIdentityAzureAuthServiceFactory } from "@app/services/identity-azure-auth/identity-azure-auth-service";
+import { TIdentityGcpAuthServiceFactory } from "@app/services/identity-gcp-auth/identity-gcp-auth-service";
+import { TIdentityKubernetesAuthServiceFactory } from "@app/services/identity-kubernetes-auth/identity-kubernetes-auth-service";
 import { TIdentityProjectServiceFactory } from "@app/services/identity-project/identity-project-service";
 import { TIdentityUaServiceFactory } from "@app/services/identity-ua/identity-ua-service";
 import { TIntegrationServiceFactory } from "@app/services/integration/integration-service";
@@ -48,6 +52,8 @@ import { TSecretServiceFactory } from "@app/services/secret/secret-service";
 import { TSecretBlindIndexServiceFactory } from "@app/services/secret-blind-index/secret-blind-index-service";
 import { TSecretFolderServiceFactory } from "@app/services/secret-folder/secret-folder-service";
 import { TSecretImportServiceFactory } from "@app/services/secret-import/secret-import-service";
+import { TSecretReplicationServiceFactory } from "@app/services/secret-replication/secret-replication-service";
+import { TSecretSharingServiceFactory } from "@app/services/secret-sharing/secret-sharing-service";
 import { TSecretTagServiceFactory } from "@app/services/secret-tag/secret-tag-service";
 import { TServiceTokenServiceFactory } from "@app/services/service-token/service-token-service";
 import { TSuperAdminServiceFactory } from "@app/services/super-admin/super-admin-service";
@@ -103,6 +109,7 @@ declare module "fastify" {
       projectKey: TProjectKeyServiceFactory;
       projectRole: TProjectRoleServiceFactory;
       secret: TSecretServiceFactory;
+      secretReplication: TSecretReplicationServiceFactory;
       secretTag: TSecretTagServiceFactory;
       secretImport: TSecretImportServiceFactory;
       projectBot: TProjectBotServiceFactory;
@@ -115,6 +122,10 @@ declare module "fastify" {
       identityAccessToken: TIdentityAccessTokenServiceFactory;
       identityProject: TIdentityProjectServiceFactory;
       identityUa: TIdentityUaServiceFactory;
+      identityKubernetesAuth: TIdentityKubernetesAuthServiceFactory;
+      identityGcpAuth: TIdentityGcpAuthServiceFactory;
+      identityAwsAuth: TIdentityAwsAuthServiceFactory;
+      identityAzureAuth: TIdentityAzureAuthServiceFactory;
       accessApprovalPolicy: TAccessApprovalPolicyServiceFactory;
       accessApprovalRequest: TAccessApprovalRequestServiceFactory;
       secretApprovalPolicy: TSecretApprovalPolicyServiceFactory;
@@ -135,6 +146,7 @@ declare module "fastify" {
       dynamicSecretLease: TDynamicSecretLeaseServiceFactory;
       projectUserAdditionalPrivilege: TProjectUserAdditionalPrivilegeServiceFactory;
       identityProjectAdditionalPrivilege: TIdentityProjectAdditionalPrivilegeServiceFactory;
+      secretSharing: TSecretSharingServiceFactory;
     };
     // 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

@@ -50,9 +50,6 @@ import {
   TGroupProjectMemberships,
   TGroupProjectMembershipsInsert,
   TGroupProjectMembershipsUpdate,
-  TGroupProjectUserAdditionalPrivilege,
-  TGroupProjectUserAdditionalPrivilegeInsert,
-  TGroupProjectUserAdditionalPrivilegeUpdate,
   TGroups,
   TGroupsInsert,
   TGroupsUpdate,
@@ -62,6 +59,18 @@ import {
   TIdentityAccessTokens,
   TIdentityAccessTokensInsert,
   TIdentityAccessTokensUpdate,
+  TIdentityAwsAuths,
+  TIdentityAwsAuthsInsert,
+  TIdentityAwsAuthsUpdate,
+  TIdentityAzureAuths,
+  TIdentityAzureAuthsInsert,
+  TIdentityAzureAuthsUpdate,
+  TIdentityGcpAuths,
+  TIdentityGcpAuthsInsert,
+  TIdentityGcpAuthsUpdate,
+  TIdentityKubernetesAuths,
+  TIdentityKubernetesAuthsInsert,
+  TIdentityKubernetesAuthsUpdate,
   TIdentityOrgMemberships,
   TIdentityOrgMembershipsInsert,
   TIdentityOrgMembershipsUpdate,
@@ -89,6 +98,15 @@ import {
   TIntegrations,
   TIntegrationsInsert,
   TIntegrationsUpdate,
+  TKmsKeys,
+  TKmsKeysInsert,
+  TKmsKeysUpdate,
+  TKmsKeyVersions,
+  TKmsKeyVersionsInsert,
+  TKmsKeyVersionsUpdate,
+  TKmsRootConfig,
+  TKmsRootConfigInsert,
+  TKmsRootConfigUpdate,
   TLdapConfigs,
   TLdapConfigsInsert,
   TLdapConfigsUpdate,
@@ -167,6 +185,9 @@ import {
   TSecretImports,
   TSecretImportsInsert,
   TSecretImportsUpdate,
+  TSecretReferences,
+  TSecretReferencesInsert,
+  TSecretReferencesUpdate,
   TSecretRotationOutputs,
   TSecretRotationOutputsInsert,
   TSecretRotationOutputsUpdate,
@@ -177,6 +198,9 @@ import {
   TSecretScanningGitRisks,
   TSecretScanningGitRisksInsert,
   TSecretScanningGitRisksUpdate,
+  TSecretSharing,
+  TSecretSharingInsert,
+  TSecretSharingUpdate,
   TSecretsInsert,
   TSecretSnapshotFolders,
   TSecretSnapshotFoldersInsert,
@@ -293,11 +317,6 @@ declare module "knex/types/tables" {
       TProjectUserMembershipRolesInsert,
       TProjectUserMembershipRolesUpdate
     >;
-    [TableName.GroupProjectUserAdditionalPrivilege]: Knex.CompositeTableType<
-      TGroupProjectUserAdditionalPrivilege,
-      TGroupProjectUserAdditionalPrivilegeInsert,
-      TGroupProjectUserAdditionalPrivilegeUpdate
-    >;
     [TableName.ProjectRoles]: Knex.CompositeTableType<TProjectRoles, TProjectRolesInsert, TProjectRolesUpdate>;
     [TableName.ProjectUserAdditionalPrivilege]: Knex.CompositeTableType<
       TProjectUserAdditionalPrivilege,
@@ -306,6 +325,11 @@ declare module "knex/types/tables" {
     >;
     [TableName.ProjectKeys]: Knex.CompositeTableType<TProjectKeys, TProjectKeysInsert, TProjectKeysUpdate>;
     [TableName.Secret]: Knex.CompositeTableType<TSecrets, TSecretsInsert, TSecretsUpdate>;
+    [TableName.SecretReference]: Knex.CompositeTableType<
+      TSecretReferences,
+      TSecretReferencesInsert,
+      TSecretReferencesUpdate
+    >;
     [TableName.SecretBlindIndex]: Knex.CompositeTableType<
       TSecretBlindIndexes,
       TSecretBlindIndexesInsert,
@@ -318,6 +342,7 @@ declare module "knex/types/tables" {
       TSecretFolderVersionsInsert,
       TSecretFolderVersionsUpdate
     >;
+    [TableName.SecretSharing]: Knex.CompositeTableType<TSecretSharing, TSecretSharingInsert, TSecretSharingUpdate>;
     [TableName.SecretTag]: Knex.CompositeTableType<TSecretTags, TSecretTagsInsert, TSecretTagsUpdate>;
     [TableName.SecretImport]: Knex.CompositeTableType<TSecretImports, TSecretImportsInsert, TSecretImportsUpdate>;
     [TableName.Integration]: Knex.CompositeTableType<TIntegrations, TIntegrationsInsert, TIntegrationsUpdate>;
@@ -334,6 +359,26 @@ declare module "knex/types/tables" {
       TIdentityUniversalAuthsInsert,
       TIdentityUniversalAuthsUpdate
     >;
+    [TableName.IdentityKubernetesAuth]: Knex.CompositeTableType<
+      TIdentityKubernetesAuths,
+      TIdentityKubernetesAuthsInsert,
+      TIdentityKubernetesAuthsUpdate
+    >;
+    [TableName.IdentityGcpAuth]: Knex.CompositeTableType<
+      TIdentityGcpAuths,
+      TIdentityGcpAuthsInsert,
+      TIdentityGcpAuthsUpdate
+    >;
+    [TableName.IdentityAwsAuth]: Knex.CompositeTableType<
+      TIdentityAwsAuths,
+      TIdentityAwsAuthsInsert,
+      TIdentityAwsAuthsUpdate
+    >;
+    [TableName.IdentityAzureAuth]: Knex.CompositeTableType<
+      TIdentityAzureAuths,
+      TIdentityAzureAuthsInsert,
+      TIdentityAzureAuthsUpdate
+    >;
     [TableName.IdentityUaClientSecret]: Knex.CompositeTableType<
       TIdentityUaClientSecrets,
       TIdentityUaClientSecretsInsert,
@@ -480,5 +525,13 @@ declare module "knex/types/tables" {
       TSecretVersionTagJunctionInsert,
       TSecretVersionTagJunctionUpdate
     >;
+    // KMS service
+    [TableName.KmsServerRootConfig]: Knex.CompositeTableType<
+      TKmsRootConfig,
+      TKmsRootConfigInsert,
+      TKmsRootConfigUpdate
+    >;
+    [TableName.KmsKey]: Knex.CompositeTableType<TKmsKeys, TKmsKeysInsert, TKmsKeysUpdate>;
+    [TableName.KmsKeyVersion]: Knex.CompositeTableType<TKmsKeyVersions, TKmsKeyVersionsInsert, TKmsKeyVersionsUpdate>;
   }
 }

backend/src/db/migrations/20240503202144_group-project-user-additional-privilege.ts

@@ -1,37 +0,0 @@
-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.GroupProjectUserAdditionalPrivilege))) {
-    await knex.schema.createTable(TableName.GroupProjectUserAdditionalPrivilege, (t) => {
-      t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
-      t.string("slug", 60).notNullable();
-
-      t.uuid("groupProjectMembershipId").notNullable();
-      t.foreign("groupProjectMembershipId")
-        .references("id")
-        .inTable(TableName.GroupProjectMembership)
-        .onDelete("CASCADE");
-
-      t.uuid("requestedByUserId").notNullable();
-      t.foreign("requestedByUserId").references("id").inTable(TableName.Users).onDelete("CASCADE");
-
-      t.boolean("isTemporary").notNullable().defaultTo(false);
-      t.string("temporaryMode");
-      t.string("temporaryRange"); // could be cron or relative time like 1H or 1minute etc
-      t.datetime("temporaryAccessStartTime");
-      t.datetime("temporaryAccessEndTime");
-      t.jsonb("permissions").notNullable();
-      t.timestamps(true, true, true);
-    });
-  }
-
-  await createOnUpdateTrigger(knex, TableName.GroupProjectUserAdditionalPrivilege);
-}
-
-export async function down(knex: Knex): Promise<void> {
-  await dropOnUpdateTrigger(knex, TableName.GroupProjectUserAdditionalPrivilege);
-  await knex.schema.dropTableIfExists(TableName.GroupProjectUserAdditionalPrivilege);
-}

backend/src/db/migrations/20240503204144_fix-db-reference-for-groups-and-project-memberships.ts

@@ -1,71 +0,0 @@
-import { Knex } from "knex";
-
-import { TableName } from "../schemas";
-
-export async function up(knex: Knex): Promise<void> {
-  // SecretApprovalPolicyApprover, approverUserId
-  if (!(await knex.schema.hasColumn(TableName.SecretApprovalPolicyApprover, "approverUserId"))) {
-    await knex.schema.alterTable(TableName.SecretApprovalPolicyApprover, (t) => {
-      t.uuid("approverId").nullable().alter();
-
-      t.uuid("approverUserId").nullable();
-      t.foreign("approverUserId").references("id").inTable(TableName.Users).onDelete("CASCADE");
-    });
-  }
-
-  // SecretApprovalRequest, statusChangeByUserId
-  if (!(await knex.schema.hasColumn(TableName.SecretApprovalRequest, "statusChangeByUserId"))) {
-    await knex.schema.alterTable(TableName.SecretApprovalRequest, (t) => {
-      t.uuid("statusChangeBy").nullable().alter();
-
-      t.uuid("statusChangeByUserId").nullable();
-      t.foreign("statusChangeByUserId").references("id").inTable(TableName.Users).onDelete("SET NULL");
-    });
-  }
-
-  // SecretApprovalRequest, committerUserId
-  if (!(await knex.schema.hasColumn(TableName.SecretApprovalRequest, "committerUserId"))) {
-    await knex.schema.alterTable(TableName.SecretApprovalRequest, (t) => {
-      t.uuid("committerId").nullable().alter();
-
-      t.uuid("committerUserId").nullable();
-      t.foreign("committerUserId").references("id").inTable(TableName.Users).onDelete("CASCADE");
-    });
-  }
-
-  // SecretApprovalRequestReviewer, memberUserId
-  if (!(await knex.schema.hasColumn(TableName.SecretApprovalRequestReviewer, "memberUserId"))) {
-    await knex.schema.alterTable(TableName.SecretApprovalRequestReviewer, (t) => {
-      t.uuid("member").nullable().alter();
-
-      t.uuid("memberUserId").nullable();
-      t.foreign("memberUserId").references("id").inTable(TableName.Users).onDelete("CASCADE");
-    });
-  }
-}
-
-export async function down(knex: Knex): Promise<void> {
-  if (await knex.schema.hasColumn(TableName.SecretApprovalPolicyApprover, "approverUserId")) {
-    await knex.schema.alterTable(TableName.SecretApprovalPolicyApprover, (t) => {
-      t.dropColumn("approverUserId");
-    });
-  }
-
-  if (await knex.schema.hasColumn(TableName.SecretApprovalRequest, "statusChangeByUserId")) {
-    await knex.schema.alterTable(TableName.SecretApprovalRequest, (t) => {
-      t.dropColumn("statusChangeByUserId");
-    });
-  }
-
-  if (await knex.schema.hasColumn(TableName.SecretApprovalRequest, "committerUserId")) {
-    await knex.schema.alterTable(TableName.SecretApprovalRequest, (t) => {
-      t.dropColumn("committerUserId");
-    });
-  }
-
-  if (await knex.schema.hasColumn(TableName.SecretApprovalRequestReviewer, "memberUserId")) {
-    await knex.schema.alterTable(TableName.SecretApprovalRequestReviewer, (t) => {
-      t.dropColumn("memberUserId");
-    });
-  }
-}

backend/src/db/migrations/20240507032811_trusted-saml-ldap-emails.ts

@@ -0,0 +1,54 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  const isUsersTablePresent = await knex.schema.hasTable(TableName.Users);
+  if (isUsersTablePresent) {
+    const hasIsEmailVerifiedColumn = await knex.schema.hasColumn(TableName.Users, "isEmailVerified");
+
+    if (!hasIsEmailVerifiedColumn) {
+      await knex.schema.alterTable(TableName.Users, (t) => {
+        t.boolean("isEmailVerified").defaultTo(false);
+      });
+    }
+
+    // Backfilling the isEmailVerified to true where isAccepted is true
+    await knex(TableName.Users).update({ isEmailVerified: true }).where("isAccepted", true);
+  }
+
+  const isUserAliasTablePresent = await knex.schema.hasTable(TableName.UserAliases);
+  if (isUserAliasTablePresent) {
+    await knex.schema.alterTable(TableName.UserAliases, (t) => {
+      t.string("username").nullable().alter();
+    });
+  }
+
+  const isSuperAdminTablePresent = await knex.schema.hasTable(TableName.SuperAdmin);
+  if (isSuperAdminTablePresent) {
+    await knex.schema.alterTable(TableName.SuperAdmin, (t) => {
+      t.boolean("trustSamlEmails").defaultTo(false);
+      t.boolean("trustLdapEmails").defaultTo(false);
+    });
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  if (await knex.schema.hasColumn(TableName.Users, "isEmailVerified")) {
+    await knex.schema.alterTable(TableName.Users, (t) => {
+      t.dropColumn("isEmailVerified");
+    });
+  }
+
+  if (await knex.schema.hasColumn(TableName.SuperAdmin, "trustSamlEmails")) {
+    await knex.schema.alterTable(TableName.SuperAdmin, (t) => {
+      t.dropColumn("trustSamlEmails");
+    });
+  }
+
+  if (await knex.schema.hasColumn(TableName.SuperAdmin, "trustLdapEmails")) {
+    await knex.schema.alterTable(TableName.SuperAdmin, (t) => {
+      t.dropColumn("trustLdapEmails");
+    });
+  }
+}

backend/src/db/migrations/20240507162140_access-approval-policy.ts

@@ -9,8 +9,9 @@ export async function up(knex: Knex): Promise<void> {
       t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
       t.string("name").notNullable();
       t.integer("approvals").defaultTo(1).notNullable();
-      t.uuid("envId").notNullable();
       t.string("secretPath");
+
+      t.uuid("envId").notNullable();
       t.foreign("envId").references("id").inTable(TableName.Environment).onDelete("CASCADE");
       t.timestamps(true, true, true);
     });
@@ -20,9 +21,8 @@ export async function up(knex: Knex): Promise<void> {
   if (!(await knex.schema.hasTable(TableName.AccessApprovalPolicyApprover))) {
     await knex.schema.createTable(TableName.AccessApprovalPolicyApprover, (t) => {
       t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
-
-      t.uuid("approverUserId").nullable();
-      t.foreign("approverUserId").references("id").inTable(TableName.Users).onDelete("CASCADE");
+      t.uuid("approverId").notNullable();
+      t.foreign("approverId").references("id").inTable(TableName.ProjectMembership).onDelete("CASCADE");
 
       t.uuid("policyId").notNullable();
       t.foreign("policyId").references("id").inTable(TableName.AccessApprovalPolicy).onDelete("CASCADE");

backend/src/db/migrations/20240507162141_access.ts

@@ -11,26 +11,11 @@ export async function up(knex: Knex): Promise<void> {
       t.uuid("policyId").notNullable();
       t.foreign("policyId").references("id").inTable(TableName.AccessApprovalPolicy).onDelete("CASCADE");
 
-      t.uuid("projectUserPrivilegeId").nullable();
-      t.foreign("projectUserPrivilegeId")
-        .references("id")
-        .inTable(TableName.ProjectUserAdditionalPrivilege)
-        .onDelete("CASCADE");
+      t.uuid("privilegeId").nullable();
+      t.foreign("privilegeId").references("id").inTable(TableName.ProjectUserAdditionalPrivilege).onDelete("CASCADE");
 
-      t.uuid("groupProjectUserPrivilegeId").nullable();
-      t.foreign("groupProjectUserPrivilegeId")
-        .references("id")
-        .inTable(TableName.GroupProjectUserAdditionalPrivilege)
-        .onDelete("CASCADE");
-
-      t.uuid("requestedByUserId").notNullable();
-      t.foreign("requestedByUserId").references("id").inTable(TableName.Users).onDelete("CASCADE");
-
-      t.uuid("projectMembershipId").nullable();
-      t.foreign("projectMembershipId").references("id").inTable(TableName.ProjectMembership).onDelete("CASCADE");
-
-      t.uuid("groupMembershipId").nullable();
-      t.foreign("groupMembershipId").references("id").inTable(TableName.GroupProjectMembership).onDelete("CASCADE");
+      t.uuid("requestedBy").notNullable();
+      t.foreign("requestedBy").references("id").inTable(TableName.ProjectMembership).onDelete("CASCADE");
 
       // We use these values to create the actual privilege at a later point in time.
       t.boolean("isTemporary").notNullable();
@@ -46,17 +31,14 @@ export async function up(knex: Knex): Promise<void> {
   if (!(await knex.schema.hasTable(TableName.AccessApprovalRequestReviewer))) {
     await knex.schema.createTable(TableName.AccessApprovalRequestReviewer, (t) => {
       t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
-
-      t.uuid("memberUserId").notNullable();
-      t.foreign("memberUserId").references("id").inTable(TableName.Users).onDelete("CASCADE");
-
+      t.uuid("member").notNullable();
+      t.foreign("member").references("id").inTable(TableName.ProjectMembership).onDelete("CASCADE");
       t.string("status").notNullable();
       t.uuid("requestId").notNullable();
       t.foreign("requestId").references("id").inTable(TableName.AccessApprovalRequest).onDelete("CASCADE");
       t.timestamps(true, true, true);
     });
   }
-
   await createOnUpdateTrigger(knex, TableName.AccessApprovalRequestReviewer);
 }
 

backend/src/db/migrations/20240507210655_identity-aws-auth.ts

@@ -0,0 +1,30 @@
+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.IdentityAwsAuth))) {
+    await knex.schema.createTable(TableName.IdentityAwsAuth, (t) => {
+      t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+      t.bigInteger("accessTokenTTL").defaultTo(7200).notNullable();
+      t.bigInteger("accessTokenMaxTTL").defaultTo(7200).notNullable();
+      t.bigInteger("accessTokenNumUsesLimit").defaultTo(0).notNullable();
+      t.jsonb("accessTokenTrustedIps").notNullable();
+      t.timestamps(true, true, true);
+      t.uuid("identityId").notNullable().unique();
+      t.foreign("identityId").references("id").inTable(TableName.Identity).onDelete("CASCADE");
+      t.string("type").notNullable();
+      t.string("stsEndpoint").notNullable();
+      t.string("allowedPrincipalArns").notNullable();
+      t.string("allowedAccountIds").notNullable();
+    });
+  }
+
+  await createOnUpdateTrigger(knex, TableName.IdentityAwsAuth);
+}
+
+export async function down(knex: Knex): Promise<void> {
+  await knex.schema.dropTableIfExists(TableName.IdentityAwsAuth);
+  await dropOnUpdateTrigger(knex, TableName.IdentityAwsAuth);
+}

backend/src/db/migrations/20240514041650_identity-gcp-auth.ts

@@ -0,0 +1,30 @@
+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.IdentityGcpAuth))) {
+    await knex.schema.createTable(TableName.IdentityGcpAuth, (t) => {
+      t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+      t.bigInteger("accessTokenTTL").defaultTo(7200).notNullable();
+      t.bigInteger("accessTokenMaxTTL").defaultTo(7200).notNullable();
+      t.bigInteger("accessTokenNumUsesLimit").defaultTo(0).notNullable();
+      t.jsonb("accessTokenTrustedIps").notNullable();
+      t.timestamps(true, true, true);
+      t.uuid("identityId").notNullable().unique();
+      t.foreign("identityId").references("id").inTable(TableName.Identity).onDelete("CASCADE");
+      t.string("type").notNullable();
+      t.string("allowedServiceAccounts").notNullable();
+      t.string("allowedProjects").notNullable();
+      t.string("allowedZones").notNullable(); // GCE only (fully qualified zone names)
+    });
+  }
+
+  await createOnUpdateTrigger(knex, TableName.IdentityGcpAuth);
+}
+
+export async function down(knex: Knex): Promise<void> {
+  await knex.schema.dropTableIfExists(TableName.IdentityGcpAuth);
+  await dropOnUpdateTrigger(knex, TableName.IdentityGcpAuth);
+}

backend/src/db/migrations/20240514141809_inline-secret-reference-sync.ts

@@ -0,0 +1,24 @@
+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.SecretReference))) {
+    await knex.schema.createTable(TableName.SecretReference, (t) => {
+      t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+      t.string("environment").notNullable();
+      t.string("secretPath").notNullable();
+      t.uuid("secretId").notNullable();
+      t.foreign("secretId").references("id").inTable(TableName.Secret).onDelete("CASCADE");
+      t.timestamps(true, true, true);
+    });
+
+    await createOnUpdateTrigger(knex, TableName.SecretReference);
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  await knex.schema.dropTableIfExists(TableName.SecretReference);
+  await dropOnUpdateTrigger(knex, TableName.SecretReference);
+}

backend/src/db/migrations/20240518142614_kubernetes-auth.ts

@@ -0,0 +1,36 @@
+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.IdentityKubernetesAuth))) {
+    await knex.schema.createTable(TableName.IdentityKubernetesAuth, (t) => {
+      t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+      t.bigInteger("accessTokenTTL").defaultTo(7200).notNullable();
+      t.bigInteger("accessTokenMaxTTL").defaultTo(7200).notNullable();
+      t.bigInteger("accessTokenNumUsesLimit").defaultTo(0).notNullable();
+      t.jsonb("accessTokenTrustedIps").notNullable();
+      t.timestamps(true, true, true);
+      t.uuid("identityId").notNullable().unique();
+      t.foreign("identityId").references("id").inTable(TableName.Identity).onDelete("CASCADE");
+      t.string("kubernetesHost").notNullable();
+      t.text("encryptedCaCert").notNullable();
+      t.string("caCertIV").notNullable();
+      t.string("caCertTag").notNullable();
+      t.text("encryptedTokenReviewerJwt").notNullable();
+      t.string("tokenReviewerJwtIV").notNullable();
+      t.string("tokenReviewerJwtTag").notNullable();
+      t.string("allowedNamespaces").notNullable();
+      t.string("allowedNames").notNullable();
+      t.string("allowedAudience").notNullable();
+    });
+  }
+
+  await createOnUpdateTrigger(knex, TableName.IdentityKubernetesAuth);
+}
+
+export async function down(knex: Knex): Promise<void> {
+  await knex.schema.dropTableIfExists(TableName.IdentityKubernetesAuth);
+  await dropOnUpdateTrigger(knex, TableName.IdentityKubernetesAuth);
+}

backend/src/db/migrations/20240520064127_add-integration-sync-status.ts

@@ -0,0 +1,43 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  const hasIsSyncedColumn = await knex.schema.hasColumn(TableName.Integration, "isSynced");
+  const hasSyncMessageColumn = await knex.schema.hasColumn(TableName.Integration, "syncMessage");
+  const hasLastSyncJobId = await knex.schema.hasColumn(TableName.Integration, "lastSyncJobId");
+
+  await knex.schema.alterTable(TableName.Integration, (t) => {
+    if (!hasIsSyncedColumn) {
+      t.boolean("isSynced").nullable();
+    }
+
+    if (!hasSyncMessageColumn) {
+      t.text("syncMessage").nullable();
+    }
+
+    if (!hasLastSyncJobId) {
+      t.string("lastSyncJobId").nullable();
+    }
+  });
+}
+
+export async function down(knex: Knex): Promise<void> {
+  const hasIsSyncedColumn = await knex.schema.hasColumn(TableName.Integration, "isSynced");
+  const hasSyncMessageColumn = await knex.schema.hasColumn(TableName.Integration, "syncMessage");
+  const hasLastSyncJobId = await knex.schema.hasColumn(TableName.Integration, "lastSyncJobId");
+
+  await knex.schema.alterTable(TableName.Integration, (t) => {
+    if (hasIsSyncedColumn) {
+      t.dropColumn("isSynced");
+    }
+
+    if (hasSyncMessageColumn) {
+      t.dropColumn("syncMessage");
+    }
+
+    if (hasLastSyncJobId) {
+      t.dropColumn("lastSyncJobId");
+    }
+  });
+}

backend/src/db/migrations/20240522193447_index-audit-logs-project-id-org-id.ts

@@ -0,0 +1,26 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  const doesOrgIdExist = await knex.schema.hasColumn(TableName.AuditLog, "orgId");
+  const doesProjectIdExist = await knex.schema.hasColumn(TableName.AuditLog, "projectId");
+  if (await knex.schema.hasTable(TableName.AuditLog)) {
+    await knex.schema.alterTable(TableName.AuditLog, (t) => {
+      if (doesProjectIdExist) t.index("projectId");
+      if (doesOrgIdExist) t.index("orgId");
+    });
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  const doesOrgIdExist = await knex.schema.hasColumn(TableName.AuditLog, "orgId");
+  const doesProjectIdExist = await knex.schema.hasColumn(TableName.AuditLog, "projectId");
+
+  if (await knex.schema.hasTable(TableName.AuditLog)) {
+    await knex.schema.alterTable(TableName.AuditLog, (t) => {
+      if (doesProjectIdExist) t.dropIndex("projectId");
+      if (doesOrgIdExist) t.dropIndex("orgId");
+    });
+  }
+}

backend/src/db/migrations/20240522203425_index-secret-snapshot-secrets-envid.ts

@@ -0,0 +1,22 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  const doesEnvIdExist = await knex.schema.hasColumn(TableName.SnapshotSecret, "envId");
+  if (await knex.schema.hasTable(TableName.SnapshotSecret)) {
+    await knex.schema.alterTable(TableName.SnapshotSecret, (t) => {
+      if (doesEnvIdExist) t.index("envId");
+    });
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  const doesEnvIdExist = await knex.schema.hasColumn(TableName.SnapshotSecret, "envId");
+
+  if (await knex.schema.hasTable(TableName.SnapshotSecret)) {
+    await knex.schema.alterTable(TableName.SnapshotSecret, (t) => {
+      if (doesEnvIdExist) t.dropIndex("envId");
+    });
+  }
+}

backend/src/db/migrations/20240522204414_index-secret-version-envId.ts

@@ -0,0 +1,22 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  const doesEnvIdExist = await knex.schema.hasColumn(TableName.SecretVersion, "envId");
+  if (await knex.schema.hasTable(TableName.SecretVersion)) {
+    await knex.schema.alterTable(TableName.SecretVersion, (t) => {
+      if (doesEnvIdExist) t.index("envId");
+    });
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  const doesEnvIdExist = await knex.schema.hasColumn(TableName.SecretVersion, "envId");
+
+  if (await knex.schema.hasTable(TableName.SecretVersion)) {
+    await knex.schema.alterTable(TableName.SecretVersion, (t) => {
+      if (doesEnvIdExist) t.dropIndex("envId");
+    });
+  }
+}

backend/src/db/migrations/20240522212706_secret-snapshot-secrets-index-on-snapshotId.ts

@@ -0,0 +1,21 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  const doesSnapshotIdExist = await knex.schema.hasColumn(TableName.SnapshotSecret, "snapshotId");
+  if (await knex.schema.hasTable(TableName.SnapshotSecret)) {
+    await knex.schema.alterTable(TableName.SnapshotSecret, (t) => {
+      if (doesSnapshotIdExist) t.index("snapshotId");
+    });
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  const doesSnapshotIdExist = await knex.schema.hasColumn(TableName.SnapshotSecret, "snapshotId");
+  if (await knex.schema.hasTable(TableName.SnapshotSecret)) {
+    await knex.schema.alterTable(TableName.SnapshotSecret, (t) => {
+      if (doesSnapshotIdExist) t.dropIndex("snapshotId");
+    });
+  }
+}

backend/src/db/migrations/20240522221147_secret-snapshot-folder-index-on-snapshotId.ts

@@ -0,0 +1,21 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  const doesSnapshotIdExist = await knex.schema.hasColumn(TableName.SnapshotFolder, "snapshotId");
+  if (await knex.schema.hasTable(TableName.SnapshotFolder)) {
+    await knex.schema.alterTable(TableName.SnapshotFolder, (t) => {
+      if (doesSnapshotIdExist) t.index("snapshotId");
+    });
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  const doesSnapshotIdExist = await knex.schema.hasColumn(TableName.SnapshotFolder, "snapshotId");
+  if (await knex.schema.hasTable(TableName.SnapshotFolder)) {
+    await knex.schema.alterTable(TableName.SnapshotFolder, (t) => {
+      if (doesSnapshotIdExist) t.dropIndex("snapshotId");
+    });
+  }
+}

backend/src/db/migrations/20240522225402_secrets-index-on-folder-id-user-id.ts

@@ -0,0 +1,24 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  const doesFolderIdExist = await knex.schema.hasColumn(TableName.Secret, "folderId");
+  const doesUserIdExist = await knex.schema.hasColumn(TableName.Secret, "userId");
+  if (await knex.schema.hasTable(TableName.Secret)) {
+    await knex.schema.alterTable(TableName.Secret, (t) => {
+      if (doesFolderIdExist && doesUserIdExist) t.index(["folderId", "userId"]);
+    });
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  const doesFolderIdExist = await knex.schema.hasColumn(TableName.Secret, "folderId");
+  const doesUserIdExist = await knex.schema.hasColumn(TableName.Secret, "userId");
+
+  if (await knex.schema.hasTable(TableName.Secret)) {
+    await knex.schema.alterTable(TableName.Secret, (t) => {
+      if (doesUserIdExist && doesFolderIdExist) t.dropIndex(["folderId", "userId"]);
+    });
+  }
+}

backend/src/db/migrations/20240523003158_audit-log-add-expireAt-index.ts

@@ -0,0 +1,22 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  const doesExpireAtExist = await knex.schema.hasColumn(TableName.AuditLog, "expiresAt");
+  if (await knex.schema.hasTable(TableName.AuditLog)) {
+    await knex.schema.alterTable(TableName.AuditLog, (t) => {
+      if (doesExpireAtExist) t.index("expiresAt");
+    });
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  const doesExpireAtExist = await knex.schema.hasColumn(TableName.AuditLog, "expiresAt");
+
+  if (await knex.schema.hasTable(TableName.AuditLog)) {
+    await knex.schema.alterTable(TableName.AuditLog, (t) => {
+      if (doesExpireAtExist) t.dropIndex("expiresAt");
+    });
+  }
+}

backend/src/db/migrations/20240527073740_identity-azure-auth.ts

@@ -0,0 +1,29 @@
+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.IdentityAzureAuth))) {
+    await knex.schema.createTable(TableName.IdentityAzureAuth, (t) => {
+      t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+      t.bigInteger("accessTokenTTL").defaultTo(7200).notNullable();
+      t.bigInteger("accessTokenMaxTTL").defaultTo(7200).notNullable();
+      t.bigInteger("accessTokenNumUsesLimit").defaultTo(0).notNullable();
+      t.jsonb("accessTokenTrustedIps").notNullable();
+      t.timestamps(true, true, true);
+      t.uuid("identityId").notNullable().unique();
+      t.foreign("identityId").references("id").inTable(TableName.Identity).onDelete("CASCADE");
+      t.string("tenantId").notNullable();
+      t.string("resource").notNullable();
+      t.string("allowedServicePrincipalIds").notNullable();
+    });
+  }
+
+  await createOnUpdateTrigger(knex, TableName.IdentityAzureAuth);
+}
+
+export async function down(knex: Knex): Promise<void> {
+  await knex.schema.dropTableIfExists(TableName.IdentityAzureAuth);
+  await dropOnUpdateTrigger(knex, TableName.IdentityAzureAuth);
+}

backend/src/db/migrations/20240528153905_add-user-account-mfa-locking.ts

@@ -0,0 +1,43 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  const hasConsecutiveFailedMfaAttempts = await knex.schema.hasColumn(TableName.Users, "consecutiveFailedMfaAttempts");
+  const hasIsLocked = await knex.schema.hasColumn(TableName.Users, "isLocked");
+  const hasTemporaryLockDateEnd = await knex.schema.hasColumn(TableName.Users, "temporaryLockDateEnd");
+
+  await knex.schema.alterTable(TableName.Users, (t) => {
+    if (!hasConsecutiveFailedMfaAttempts) {
+      t.integer("consecutiveFailedMfaAttempts").defaultTo(0);
+    }
+
+    if (!hasIsLocked) {
+      t.boolean("isLocked").defaultTo(false);
+    }
+
+    if (!hasTemporaryLockDateEnd) {
+      t.dateTime("temporaryLockDateEnd").nullable();
+    }
+  });
+}
+
+export async function down(knex: Knex): Promise<void> {
+  const hasConsecutiveFailedMfaAttempts = await knex.schema.hasColumn(TableName.Users, "consecutiveFailedMfaAttempts");
+  const hasIsLocked = await knex.schema.hasColumn(TableName.Users, "isLocked");
+  const hasTemporaryLockDateEnd = await knex.schema.hasColumn(TableName.Users, "temporaryLockDateEnd");
+
+  await knex.schema.alterTable(TableName.Users, (t) => {
+    if (hasConsecutiveFailedMfaAttempts) {
+      t.dropColumn("consecutiveFailedMfaAttempts");
+    }
+
+    if (hasIsLocked) {
+      t.dropColumn("isLocked");
+    }
+
+    if (hasTemporaryLockDateEnd) {
+      t.dropColumn("temporaryLockDateEnd");
+    }
+  });
+}

backend/src/db/migrations/20240528190137_secret_sharing.ts

@@ -0,0 +1,29 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+import { createOnUpdateTrigger } from "../utils";
+
+export async function up(knex: Knex): Promise<void> {
+  if (!(await knex.schema.hasTable(TableName.SecretSharing))) {
+    await knex.schema.createTable(TableName.SecretSharing, (t) => {
+      t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+      t.string("name").notNullable();
+      t.text("encryptedValue").notNullable();
+      t.text("iv").notNullable();
+      t.text("tag").notNullable();
+      t.text("hashedHex").notNullable();
+      t.timestamp("expiresAt").notNullable();
+      t.uuid("userId").notNullable();
+      t.uuid("orgId").notNullable();
+      t.foreign("userId").references("id").inTable(TableName.Users).onDelete("CASCADE");
+      t.foreign("orgId").references("id").inTable(TableName.Organization).onDelete("CASCADE");
+      t.timestamps(true, true, true);
+    });
+
+    await createOnUpdateTrigger(knex, TableName.SecretSharing);
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  await knex.schema.dropTableIfExists(TableName.SecretSharing);
+}

backend/src/db/migrations/20240529060752_snap-shot-secret-index-secretversionid.ts

@@ -0,0 +1,21 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  const doesSecretVersionIdExist = await knex.schema.hasColumn(TableName.SnapshotSecret, "secretVersionId");
+  if (await knex.schema.hasTable(TableName.SnapshotSecret)) {
+    await knex.schema.alterTable(TableName.SnapshotSecret, (t) => {
+      if (doesSecretVersionIdExist) t.index("secretVersionId");
+    });
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  const doesSecretVersionIdExist = await knex.schema.hasColumn(TableName.SnapshotSecret, "secretVersionId");
+  if (await knex.schema.hasTable(TableName.SnapshotSecret)) {
+    await knex.schema.alterTable(TableName.SnapshotSecret, (t) => {
+      if (doesSecretVersionIdExist) t.dropIndex("secretVersionId");
+    });
+  }
+}

backend/src/db/migrations/20240529203152_secret_sharing.ts

@@ -0,0 +1,29 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+import { createOnUpdateTrigger } from "../utils";
+
+export async function up(knex: Knex): Promise<void> {
+  if (!(await knex.schema.hasTable(TableName.SecretSharing))) {
+    await knex.schema.createTable(TableName.SecretSharing, (t) => {
+      t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+      t.string("name").notNullable();
+      t.text("encryptedValue").notNullable();
+      t.text("iv").notNullable();
+      t.text("tag").notNullable();
+      t.text("hashedHex").notNullable();
+      t.timestamp("expiresAt").notNullable();
+      t.uuid("userId").notNullable();
+      t.uuid("orgId").notNullable();
+      t.foreign("userId").references("id").inTable(TableName.Users).onDelete("CASCADE");
+      t.foreign("orgId").references("id").inTable(TableName.Organization).onDelete("CASCADE");
+      t.timestamps(true, true, true);
+    });
+
+    await createOnUpdateTrigger(knex, TableName.SecretSharing);
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  await knex.schema.dropTableIfExists(TableName.SecretSharing);
+}

backend/src/db/migrations/20240530044702_universal-text-in-secret-sharing.ts

@@ -0,0 +1,33 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  const hasExpiresAfterViewsColumn = await knex.schema.hasColumn(TableName.SecretSharing, "expiresAfterViews");
+  const hasSecretNameColumn = await knex.schema.hasColumn(TableName.SecretSharing, "name");
+
+  await knex.schema.alterTable(TableName.SecretSharing, (t) => {
+    if (!hasExpiresAfterViewsColumn) {
+      t.integer("expiresAfterViews");
+    }
+
+    if (hasSecretNameColumn) {
+      t.dropColumn("name");
+    }
+  });
+}
+
+export async function down(knex: Knex): Promise<void> {
+  const hasExpiresAfterViewsColumn = await knex.schema.hasColumn(TableName.SecretSharing, "expiresAfterViews");
+  const hasSecretNameColumn = await knex.schema.hasColumn(TableName.SecretSharing, "name");
+
+  await knex.schema.alterTable(TableName.SecretSharing, (t) => {
+    if (hasExpiresAfterViewsColumn) {
+      t.dropColumn("expiresAfterViews");
+    }
+
+    if (!hasSecretNameColumn) {
+      t.string("name").notNullable();
+    }
+  });
+}

backend/src/db/migrations/20240531220007_secret-replication.ts

@@ -0,0 +1,85 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  const doesSecretImportIsReplicationExist = await knex.schema.hasColumn(TableName.SecretImport, "isReplication");
+  const doesSecretImportIsReplicationSuccessExist = await knex.schema.hasColumn(
+    TableName.SecretImport,
+    "isReplicationSuccess"
+  );
+  const doesSecretImportReplicationStatusExist = await knex.schema.hasColumn(
+    TableName.SecretImport,
+    "replicationStatus"
+  );
+  const doesSecretImportLastReplicatedExist = await knex.schema.hasColumn(TableName.SecretImport, "lastReplicated");
+  const doesSecretImportIsReservedExist = await knex.schema.hasColumn(TableName.SecretImport, "isReserved");
+
+  if (await knex.schema.hasTable(TableName.SecretImport)) {
+    await knex.schema.alterTable(TableName.SecretImport, (t) => {
+      if (!doesSecretImportIsReplicationExist) t.boolean("isReplication").defaultTo(false);
+      if (!doesSecretImportIsReplicationSuccessExist) t.boolean("isReplicationSuccess").nullable();
+      if (!doesSecretImportReplicationStatusExist) t.text("replicationStatus").nullable();
+      if (!doesSecretImportLastReplicatedExist) t.datetime("lastReplicated").nullable();
+      if (!doesSecretImportIsReservedExist) t.boolean("isReserved").defaultTo(false);
+    });
+  }
+
+  const doesSecretFolderReservedExist = await knex.schema.hasColumn(TableName.SecretFolder, "isReserved");
+  if (await knex.schema.hasTable(TableName.SecretFolder)) {
+    await knex.schema.alterTable(TableName.SecretFolder, (t) => {
+      if (!doesSecretFolderReservedExist) t.boolean("isReserved").defaultTo(false);
+    });
+  }
+
+  const doesSecretApprovalRequestIsReplicatedExist = await knex.schema.hasColumn(
+    TableName.SecretApprovalRequest,
+    "isReplicated"
+  );
+  if (await knex.schema.hasTable(TableName.SecretApprovalRequest)) {
+    await knex.schema.alterTable(TableName.SecretApprovalRequest, (t) => {
+      if (!doesSecretApprovalRequestIsReplicatedExist) t.boolean("isReplicated");
+    });
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  const doesSecretImportIsReplicationExist = await knex.schema.hasColumn(TableName.SecretImport, "isReplication");
+  const doesSecretImportIsReplicationSuccessExist = await knex.schema.hasColumn(
+    TableName.SecretImport,
+    "isReplicationSuccess"
+  );
+  const doesSecretImportReplicationStatusExist = await knex.schema.hasColumn(
+    TableName.SecretImport,
+    "replicationStatus"
+  );
+  const doesSecretImportLastReplicatedExist = await knex.schema.hasColumn(TableName.SecretImport, "lastReplicated");
+  const doesSecretImportIsReservedExist = await knex.schema.hasColumn(TableName.SecretImport, "isReserved");
+
+  if (await knex.schema.hasTable(TableName.SecretImport)) {
+    await knex.schema.alterTable(TableName.SecretImport, (t) => {
+      if (doesSecretImportIsReplicationExist) t.dropColumn("isReplication");
+      if (doesSecretImportIsReplicationSuccessExist) t.dropColumn("isReplicationSuccess");
+      if (doesSecretImportReplicationStatusExist) t.dropColumn("replicationStatus");
+      if (doesSecretImportLastReplicatedExist) t.dropColumn("lastReplicated");
+      if (doesSecretImportIsReservedExist) t.dropColumn("isReserved");
+    });
+  }
+
+  const doesSecretFolderReservedExist = await knex.schema.hasColumn(TableName.SecretFolder, "isReserved");
+  if (await knex.schema.hasTable(TableName.SecretFolder)) {
+    await knex.schema.alterTable(TableName.SecretFolder, (t) => {
+      if (doesSecretFolderReservedExist) t.dropColumn("isReserved");
+    });
+  }
+
+  const doesSecretApprovalRequestIsReplicatedExist = await knex.schema.hasColumn(
+    TableName.SecretApprovalRequest,
+    "isReplicated"
+  );
+  if (await knex.schema.hasTable(TableName.SecretApprovalRequest)) {
+    await knex.schema.alterTable(TableName.SecretApprovalRequest, (t) => {
+      if (doesSecretApprovalRequestIsReplicatedExist) t.dropColumn("isReplicated");
+    });
+  }
+}

backend/src/db/migrations/20240603075514_kms.ts

@@ -0,0 +1,56 @@
+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.KmsServerRootConfig))) {
+    await knex.schema.createTable(TableName.KmsServerRootConfig, (t) => {
+      t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+      t.binary("encryptedRootKey").notNullable();
+    });
+  }
+
+  await createOnUpdateTrigger(knex, TableName.KmsServerRootConfig);
+
+  if (!(await knex.schema.hasTable(TableName.KmsKey))) {
+    await knex.schema.createTable(TableName.KmsKey, (t) => {
+      t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+      t.binary("encryptedKey").notNullable();
+      t.string("encryptionAlgorithm").notNullable();
+      t.integer("version").defaultTo(1).notNullable();
+      t.string("description");
+      t.boolean("isDisabled").defaultTo(false);
+      t.boolean("isReserved").defaultTo(true);
+      t.string("projectId");
+      t.foreign("projectId").references("id").inTable(TableName.Project).onDelete("CASCADE");
+      t.uuid("orgId");
+      t.foreign("orgId").references("id").inTable(TableName.Organization).onDelete("CASCADE");
+    });
+  }
+
+  await createOnUpdateTrigger(knex, TableName.KmsKey);
+
+  if (!(await knex.schema.hasTable(TableName.KmsKeyVersion))) {
+    await knex.schema.createTable(TableName.KmsKeyVersion, (t) => {
+      t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+      t.binary("encryptedKey").notNullable();
+      t.integer("version").notNullable();
+      t.uuid("kmsKeyId").notNullable();
+      t.foreign("kmsKeyId").references("id").inTable(TableName.KmsKey).onDelete("CASCADE");
+    });
+  }
+
+  await createOnUpdateTrigger(knex, TableName.KmsKeyVersion);
+}
+
+export async function down(knex: Knex): Promise<void> {
+  await knex.schema.dropTableIfExists(TableName.KmsServerRootConfig);
+  await dropOnUpdateTrigger(knex, TableName.KmsServerRootConfig);
+
+  await knex.schema.dropTableIfExists(TableName.KmsKeyVersion);
+  await dropOnUpdateTrigger(knex, TableName.KmsKeyVersion);
+
+  await knex.schema.dropTableIfExists(TableName.KmsKey);
+  await dropOnUpdateTrigger(knex, TableName.KmsKey);
+}

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

@@ -9,7 +9,7 @@ import { TImmutableDBKeys } from "./models";
 
 export const AccessApprovalPoliciesApproversSchema = z.object({
   id: z.string().uuid(),
-  approverUserId: z.string().uuid().nullable().optional(),
+  approverId: z.string().uuid(),
   policyId: z.string().uuid(),
   createdAt: z.date(),
   updatedAt: z.date()

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

@@ -11,8 +11,8 @@ export const AccessApprovalPoliciesSchema = z.object({
   id: z.string().uuid(),
   name: z.string(),
   approvals: z.number().default(1),
-  envId: z.string().uuid(),
   secretPath: z.string().nullable().optional(),
+  envId: z.string().uuid(),
   createdAt: z.date(),
   updatedAt: z.date()
 });

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

@@ -9,7 +9,7 @@ import { TImmutableDBKeys } from "./models";
 
 export const AccessApprovalRequestsReviewersSchema = z.object({
   id: z.string().uuid(),
-  memberUserId: z.string().uuid(),
+  member: z.string().uuid(),
   status: z.string(),
   requestId: z.string().uuid(),
   createdAt: z.date(),

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

@@ -10,11 +10,8 @@ import { TImmutableDBKeys } from "./models";
 export const AccessApprovalRequestsSchema = z.object({
   id: z.string().uuid(),
   policyId: z.string().uuid(),
-  projectUserPrivilegeId: z.string().uuid().nullable().optional(),
-  groupProjectUserPrivilegeId: z.string().uuid().nullable().optional(),
-  requestedByUserId: z.string().uuid(),
-  projectMembershipId: z.string().uuid().nullable().optional(),
-  groupMembershipId: z.string().uuid().nullable().optional(),
+  privilegeId: z.string().uuid().nullable().optional(),
+  requestedBy: z.string().uuid(),
   isTemporary: z.boolean(),
   temporaryRange: z.string().nullable().optional(),
   permissions: z.unknown(),

backend/src/db/schemas/group-project-user-additional-privilege.ts

@@ -1,32 +0,0 @@
-// 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 GroupProjectUserAdditionalPrivilegeSchema = z.object({
-  id: z.string().uuid(),
-  slug: z.string(),
-  groupProjectMembershipId: z.string().uuid(),
-  requestedByUserId: z.string().uuid(),
-  isTemporary: z.boolean().default(false),
-  temporaryMode: z.string().nullable().optional(),
-  temporaryRange: z.string().nullable().optional(),
-  temporaryAccessStartTime: z.date().nullable().optional(),
-  temporaryAccessEndTime: z.date().nullable().optional(),
-  permissions: z.unknown(),
-  createdAt: z.date(),
-  updatedAt: z.date()
-});
-
-export type TGroupProjectUserAdditionalPrivilege = z.infer<typeof GroupProjectUserAdditionalPrivilegeSchema>;
-export type TGroupProjectUserAdditionalPrivilegeInsert = Omit<
-  z.input<typeof GroupProjectUserAdditionalPrivilegeSchema>,
-  TImmutableDBKeys
->;
-export type TGroupProjectUserAdditionalPrivilegeUpdate = Partial<
-  Omit<z.input<typeof GroupProjectUserAdditionalPrivilegeSchema>, TImmutableDBKeys>
->;

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

@@ -0,0 +1,27 @@
+// 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 IdentityAwsAuthsSchema = z.object({
+  id: z.string().uuid(),
+  accessTokenTTL: z.coerce.number().default(7200),
+  accessTokenMaxTTL: z.coerce.number().default(7200),
+  accessTokenNumUsesLimit: z.coerce.number().default(0),
+  accessTokenTrustedIps: z.unknown(),
+  createdAt: z.date(),
+  updatedAt: z.date(),
+  identityId: z.string().uuid(),
+  type: z.string(),
+  stsEndpoint: z.string(),
+  allowedPrincipalArns: z.string(),
+  allowedAccountIds: z.string()
+});
+
+export type TIdentityAwsAuths = z.infer<typeof IdentityAwsAuthsSchema>;
+export type TIdentityAwsAuthsInsert = Omit<z.input<typeof IdentityAwsAuthsSchema>, TImmutableDBKeys>;
+export type TIdentityAwsAuthsUpdate = Partial<Omit<z.input<typeof IdentityAwsAuthsSchema>, TImmutableDBKeys>>;

backend/src/db/schemas/identity-azure-auths.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 IdentityAzureAuthsSchema = z.object({
+  id: z.string().uuid(),
+  accessTokenTTL: z.coerce.number().default(7200),
+  accessTokenMaxTTL: z.coerce.number().default(7200),
+  accessTokenNumUsesLimit: z.coerce.number().default(0),
+  accessTokenTrustedIps: z.unknown(),
+  createdAt: z.date(),
+  updatedAt: z.date(),
+  identityId: z.string().uuid(),
+  tenantId: z.string(),
+  resource: z.string(),
+  allowedServicePrincipalIds: z.string()
+});
+
+export type TIdentityAzureAuths = z.infer<typeof IdentityAzureAuthsSchema>;
+export type TIdentityAzureAuthsInsert = Omit<z.input<typeof IdentityAzureAuthsSchema>, TImmutableDBKeys>;
+export type TIdentityAzureAuthsUpdate = Partial<Omit<z.input<typeof IdentityAzureAuthsSchema>, TImmutableDBKeys>>;

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

@@ -0,0 +1,27 @@
+// 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 IdentityGcpAuthsSchema = z.object({
+  id: z.string().uuid(),
+  accessTokenTTL: z.coerce.number().default(7200),
+  accessTokenMaxTTL: z.coerce.number().default(7200),
+  accessTokenNumUsesLimit: z.coerce.number().default(0),
+  accessTokenTrustedIps: z.unknown(),
+  createdAt: z.date(),
+  updatedAt: z.date(),
+  identityId: z.string().uuid(),
+  type: z.string(),
+  allowedServiceAccounts: z.string(),
+  allowedProjects: z.string(),
+  allowedZones: z.string()
+});
+
+export type TIdentityGcpAuths = z.infer<typeof IdentityGcpAuthsSchema>;
+export type TIdentityGcpAuthsInsert = Omit<z.input<typeof IdentityGcpAuthsSchema>, TImmutableDBKeys>;
+export type TIdentityGcpAuthsUpdate = Partial<Omit<z.input<typeof IdentityGcpAuthsSchema>, TImmutableDBKeys>>;

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

@@ -0,0 +1,35 @@
+// 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 IdentityKubernetesAuthsSchema = z.object({
+  id: z.string().uuid(),
+  accessTokenTTL: z.coerce.number().default(7200),
+  accessTokenMaxTTL: z.coerce.number().default(7200),
+  accessTokenNumUsesLimit: z.coerce.number().default(0),
+  accessTokenTrustedIps: z.unknown(),
+  createdAt: z.date(),
+  updatedAt: z.date(),
+  identityId: z.string().uuid(),
+  kubernetesHost: z.string(),
+  encryptedCaCert: z.string(),
+  caCertIV: z.string(),
+  caCertTag: z.string(),
+  encryptedTokenReviewerJwt: z.string(),
+  tokenReviewerJwtIV: z.string(),
+  tokenReviewerJwtTag: z.string(),
+  allowedNamespaces: z.string(),
+  allowedNames: z.string(),
+  allowedAudience: z.string()
+});
+
+export type TIdentityKubernetesAuths = z.infer<typeof IdentityKubernetesAuthsSchema>;
+export type TIdentityKubernetesAuthsInsert = Omit<z.input<typeof IdentityKubernetesAuthsSchema>, TImmutableDBKeys>;
+export type TIdentityKubernetesAuthsUpdate = Partial<
+  Omit<z.input<typeof IdentityKubernetesAuthsSchema>, TImmutableDBKeys>
+>;

backend/src/db/schemas/index.ts

@@ -14,10 +14,13 @@ export * from "./git-app-install-sessions";
 export * from "./git-app-org";
 export * from "./group-project-membership-roles";
 export * from "./group-project-memberships";
-export * from "./group-project-user-additional-privilege";
 export * from "./groups";
 export * from "./identities";
 export * from "./identity-access-tokens";
+export * from "./identity-aws-auths";
+export * from "./identity-azure-auths";
+export * from "./identity-gcp-auths";
+export * from "./identity-kubernetes-auths";
 export * from "./identity-org-memberships";
 export * from "./identity-project-additional-privilege";
 export * from "./identity-project-membership-role";
@@ -27,6 +30,9 @@ export * from "./identity-universal-auths";
 export * from "./incident-contacts";
 export * from "./integration-auths";
 export * from "./integrations";
+export * from "./kms-key-versions";
+export * from "./kms-keys";
+export * from "./kms-root-config";
 export * from "./ldap-configs";
 export * from "./ldap-group-maps";
 export * from "./models";
@@ -54,9 +60,11 @@ export * from "./secret-blind-indexes";
 export * from "./secret-folder-versions";
 export * from "./secret-folders";
 export * from "./secret-imports";
+export * from "./secret-references";
 export * from "./secret-rotation-outputs";
 export * from "./secret-rotations";
 export * from "./secret-scanning-git-risks";
+export * from "./secret-sharing";
 export * from "./secret-snapshot-folders";
 export * from "./secret-snapshot-secrets";
 export * from "./secret-snapshots";

backend/src/db/schemas/integrations.ts

@@ -28,7 +28,10 @@ export const IntegrationsSchema = z.object({
   secretPath: z.string().default("/"),
   createdAt: z.date(),
   updatedAt: z.date(),
-  lastUsed: z.date().nullable().optional()
+  lastUsed: z.date().nullable().optional(),
+  isSynced: z.boolean().nullable().optional(),
+  syncMessage: z.string().nullable().optional(),
+  lastSyncJobId: z.string().nullable().optional()
 });
 
 export type TIntegrations = z.infer<typeof IntegrationsSchema>;

backend/src/db/schemas/kms-key-versions.ts

@@ -0,0 +1,21 @@
+// 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 { zodBuffer } from "@app/lib/zod";
+
+import { TImmutableDBKeys } from "./models";
+
+export const KmsKeyVersionsSchema = z.object({
+  id: z.string().uuid(),
+  encryptedKey: zodBuffer,
+  version: z.number(),
+  kmsKeyId: z.string().uuid()
+});
+
+export type TKmsKeyVersions = z.infer<typeof KmsKeyVersionsSchema>;
+export type TKmsKeyVersionsInsert = Omit<z.input<typeof KmsKeyVersionsSchema>, TImmutableDBKeys>;
+export type TKmsKeyVersionsUpdate = Partial<Omit<z.input<typeof KmsKeyVersionsSchema>, TImmutableDBKeys>>;

backend/src/db/schemas/kms-keys.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 { zodBuffer } from "@app/lib/zod";
+
+import { TImmutableDBKeys } from "./models";
+
+export const KmsKeysSchema = z.object({
+  id: z.string().uuid(),
+  encryptedKey: zodBuffer,
+  encryptionAlgorithm: z.string(),
+  version: z.number().default(1),
+  description: z.string().nullable().optional(),
+  isDisabled: z.boolean().default(false).nullable().optional(),
+  isReserved: z.boolean().default(true).nullable().optional(),
+  projectId: z.string().nullable().optional(),
+  orgId: z.string().uuid().nullable().optional()
+});
+
+export type TKmsKeys = z.infer<typeof KmsKeysSchema>;
+export type TKmsKeysInsert = Omit<z.input<typeof KmsKeysSchema>, TImmutableDBKeys>;
+export type TKmsKeysUpdate = Partial<Omit<z.input<typeof KmsKeysSchema>, TImmutableDBKeys>>;

backend/src/db/schemas/kms-root-config.ts

@@ -0,0 +1,19 @@
+// 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 { zodBuffer } from "@app/lib/zod";
+
+import { TImmutableDBKeys } from "./models";
+
+export const KmsRootConfigSchema = z.object({
+  id: z.string().uuid(),
+  encryptedRootKey: zodBuffer
+});
+
+export type TKmsRootConfig = z.infer<typeof KmsRootConfigSchema>;
+export type TKmsRootConfigInsert = Omit<z.input<typeof KmsRootConfigSchema>, TImmutableDBKeys>;
+export type TKmsRootConfigUpdate = Partial<Omit<z.input<typeof KmsRootConfigSchema>, TImmutableDBKeys>>;

backend/src/db/schemas/models.ts

@@ -25,10 +25,11 @@ export enum TableName {
   ProjectMembership = "project_memberships",
   ProjectRoles = "project_roles",
   ProjectUserAdditionalPrivilege = "project_user_additional_privilege",
-  GroupProjectUserAdditionalPrivilege = "group_project_user_additional_privilege",
   ProjectUserMembershipRole = "project_user_membership_roles",
   ProjectKeys = "project_keys",
   Secret = "secrets",
+  SecretReference = "secret_references",
+  SecretSharing = "secret_sharing",
   SecretBlindIndex = "secret_blind_indexes",
   SecretVersion = "secret_versions",
   SecretFolder = "secret_folders",
@@ -45,7 +46,11 @@ export enum TableName {
   Identity = "identities",
   IdentityAccessToken = "identity_access_tokens",
   IdentityUniversalAuth = "identity_universal_auths",
+  IdentityKubernetesAuth = "identity_kubernetes_auths",
+  IdentityGcpAuth = "identity_gcp_auths",
+  IdentityAzureAuth = "identity_azure_auths",
   IdentityUaClientSecret = "identity_ua_client_secrets",
+  IdentityAwsAuth = "identity_aws_auths",
   IdentityOrgMembership = "identity_org_memberships",
   IdentityProjectMembership = "identity_project_memberships",
   IdentityProjectMembershipRole = "identity_project_membership_role",
@@ -76,7 +81,11 @@ export enum TableName {
   DynamicSecretLease = "dynamic_secret_leases",
   // junction tables with tags
   JnSecretTag = "secret_tag_junction",
-  SecretVersionTag = "secret_version_tag_junction"
+  SecretVersionTag = "secret_version_tag_junction",
+  // KMS Service
+  KmsServerRootConfig = "kms_root_config",
+  KmsKey = "kms_keys",
+  KmsKeyVersion = "kms_key_versions"
 }
 
 export type TImmutableDBKeys = "id" | "createdAt" | "updatedAt";
@@ -143,5 +152,9 @@ export enum ProjectUpgradeStatus {
 }
 
 export enum IdentityAuthMethod {
-  Univeral = "universal-auth"
+  Univeral = "universal-auth",
+  KUBERNETES_AUTH = "kubernetes-auth",
+  GCP_AUTH = "gcp-auth",
+  AWS_AUTH = "aws-auth",
+  AZURE_AUTH = "azure-auth"
 }

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

@@ -9,11 +9,10 @@ import { TImmutableDBKeys } from "./models";
 
 export const SecretApprovalPoliciesApproversSchema = z.object({
   id: z.string().uuid(),
-  approverId: z.string().uuid().nullable().optional(),
+  approverId: z.string().uuid(),
   policyId: z.string().uuid(),
   createdAt: z.date(),
-  updatedAt: z.date(),
-  approverUserId: z.string().uuid().nullable().optional()
+  updatedAt: z.date()
 });
 
 export type TSecretApprovalPoliciesApprovers = z.infer<typeof SecretApprovalPoliciesApproversSchema>;

backend/src/db/schemas/secret-approval-requests-reviewers.ts

@@ -9,12 +9,11 @@ import { TImmutableDBKeys } from "./models";
 
 export const SecretApprovalRequestsReviewersSchema = z.object({
   id: z.string().uuid(),
-  member: z.string().uuid().nullable().optional(),
+  member: z.string().uuid(),
   status: z.string(),
   requestId: z.string().uuid(),
   createdAt: z.date(),
-  updatedAt: z.date(),
-  memberUserId: z.string().uuid().nullable().optional()
+  updatedAt: z.date()
 });
 
 export type TSecretApprovalRequestsReviewers = z.infer<typeof SecretApprovalRequestsReviewersSchema>;

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

@@ -16,11 +16,10 @@ export const SecretApprovalRequestsSchema = z.object({
   slug: z.string(),
   folderId: z.string().uuid(),
   statusChangeBy: z.string().uuid().nullable().optional(),
-  committerId: z.string().uuid().nullable().optional(),
+  committerId: z.string().uuid(),
   createdAt: z.date(),
   updatedAt: z.date(),
-  statusChangeByUserId: z.string().uuid().nullable().optional(),
-  committerUserId: z.string().uuid().nullable().optional()
+  isReplicated: z.boolean().nullable().optional()
 });
 
 export type TSecretApprovalRequests = z.infer<typeof SecretApprovalRequestsSchema>;

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

@@ -14,7 +14,8 @@ export const SecretFoldersSchema = z.object({
   createdAt: z.date(),
   updatedAt: z.date(),
   envId: z.string().uuid(),
-  parentId: z.string().uuid().nullable().optional()
+  parentId: z.string().uuid().nullable().optional(),
+  isReserved: z.boolean().default(false).nullable().optional()
 });
 
 export type TSecretFolders = z.infer<typeof SecretFoldersSchema>;

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

@@ -15,7 +15,12 @@ export const SecretImportsSchema = z.object({
   position: z.number(),
   createdAt: z.date(),
   updatedAt: z.date(),
-  folderId: z.string().uuid()
+  folderId: z.string().uuid(),
+  isReplication: z.boolean().default(false).nullable().optional(),
+  isReplicationSuccess: z.boolean().nullable().optional(),
+  replicationStatus: z.string().nullable().optional(),
+  lastReplicated: z.date().nullable().optional(),
+  isReserved: z.boolean().default(false).nullable().optional()
 });
 
 export type TSecretImports = z.infer<typeof SecretImportsSchema>;

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

@@ -0,0 +1,21 @@
+// 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 SecretReferencesSchema = z.object({
+  id: z.string().uuid(),
+  environment: z.string(),
+  secretPath: z.string(),
+  secretId: z.string().uuid(),
+  createdAt: z.date(),
+  updatedAt: z.date()
+});
+
+export type TSecretReferences = z.infer<typeof SecretReferencesSchema>;
+export type TSecretReferencesInsert = Omit<z.input<typeof SecretReferencesSchema>, TImmutableDBKeys>;
+export type TSecretReferencesUpdate = Partial<Omit<z.input<typeof SecretReferencesSchema>, TImmutableDBKeys>>;

backend/src/db/schemas/secret-sharing.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 SecretSharingSchema = z.object({
+  id: z.string().uuid(),
+  encryptedValue: z.string(),
+  iv: z.string(),
+  tag: z.string(),
+  hashedHex: z.string(),
+  expiresAt: z.date(),
+  userId: z.string().uuid(),
+  orgId: z.string().uuid(),
+  createdAt: z.date(),
+  updatedAt: z.date(),
+  expiresAfterViews: z.number().nullable().optional()
+});
+
+export type TSecretSharing = z.infer<typeof SecretSharingSchema>;
+export type TSecretSharingInsert = Omit<z.input<typeof SecretSharingSchema>, TImmutableDBKeys>;
+export type TSecretSharingUpdate = Partial<Omit<z.input<typeof SecretSharingSchema>, TImmutableDBKeys>>;

backend/src/db/schemas/super-admin.ts

@@ -14,7 +14,9 @@ export const SuperAdminSchema = z.object({
   createdAt: z.date(),
   updatedAt: z.date(),
   allowedSignUpDomain: z.string().nullable().optional(),
-  instanceId: z.string().uuid().default("00000000-0000-0000-0000-000000000000")
+  instanceId: z.string().uuid().default("00000000-0000-0000-0000-000000000000"),
+  trustSamlEmails: z.boolean().default(false).nullable().optional(),
+  trustLdapEmails: z.boolean().default(false).nullable().optional()
 });
 
 export type TSuperAdmin = z.infer<typeof SuperAdminSchema>;

backend/src/db/schemas/user-aliases.ts

@@ -10,7 +10,7 @@ import { TImmutableDBKeys } from "./models";
 export const UserAliasesSchema = z.object({
   id: z.string().uuid(),
   userId: z.string().uuid(),
-  username: z.string(),
+  username: z.string().nullable().optional(),
   aliasType: z.string(),
   externalId: z.string(),
   emails: z.string().array().nullable().optional(),

backend/src/db/schemas/users.ts

@@ -21,7 +21,11 @@ export const UsersSchema = z.object({
   createdAt: z.date(),
   updatedAt: z.date(),
   isGhost: z.boolean().default(false),
-  username: z.string()
+  username: z.string(),
+  isEmailVerified: z.boolean().default(false).nullable().optional(),
+  consecutiveFailedMfaAttempts: z.number().default(0).nullable().optional(),
+  isLocked: z.boolean().default(false).nullable().optional(),
+  temporaryLockDateEnd: z.date().nullable().optional()
 });
 
 export type TUsers = z.infer<typeof UsersSchema>;

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

@@ -53,9 +53,7 @@ export const registerAccessApprovalPolicyRouter = async (server: FastifyZodProvi
       }),
       response: {
         200: z.object({
-          approvals: sapPubSchema
-            .extend({ approvers: z.string().nullish().array(), secretPath: z.string().optional() })
-            .array()
+          approvals: sapPubSchema.extend({ approvers: z.string().array(), secretPath: z.string().optional() }).array()
         })
       }
     },

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

@@ -74,7 +74,7 @@ export const registerAccessApprovalRequestRouter = async (server: FastifyZodProv
     schema: {
       querystring: z.object({
         projectSlug: z.string().trim(),
-        authorUserId: z.string().trim().optional(),
+        authorProjectMembershipId: z.string().trim().optional(),
         envSlug: z.string().trim().optional()
       }),
       response: {
@@ -84,8 +84,7 @@ export const registerAccessApprovalRequestRouter = async (server: FastifyZodProv
             isApproved: z.boolean(),
             privilege: z
               .object({
-                projectMembershipId: z.string().nullish(),
-                groupMembershipId: z.string().nullish(),
+                membershipId: z.string(),
                 isTemporary: z.boolean(),
                 temporaryMode: z.string().nullish(),
                 temporaryRange: z.string().nullish(),
@@ -116,8 +115,8 @@ export const registerAccessApprovalRequestRouter = async (server: FastifyZodProv
     handler: async (req) => {
       const { requests } = await server.services.accessApprovalRequest.listApprovalRequests({
         projectSlug: req.query.projectSlug,
+        authorProjectMembershipId: req.query.authorProjectMembershipId,
         envSlug: req.query.envSlug,
-        authorUserId: req.query.authorUserId,
         actor: req.permission.type,
         actorId: req.permission.id,
         actorOrgId: req.permission.orgId,
@@ -128,37 +127,6 @@ export const registerAccessApprovalRequestRouter = async (server: FastifyZodProv
     }
   });
 
-  server.route({
-    url: "/:requestId",
-    method: "DELETE",
-    schema: {
-      params: z.object({
-        requestId: z.string().trim()
-      }),
-      querystring: z.object({
-        projectSlug: z.string().trim()
-      }),
-      response: {
-        200: z.object({
-          request: AccessApprovalRequestsSchema
-        })
-      }
-    },
-    onRequest: verifyAuth([AuthMode.JWT]),
-    handler: async (req) => {
-      const { request } = await server.services.accessApprovalRequest.deleteAccessApprovalRequest({
-        actor: req.permission.type,
-        actorId: req.permission.id,
-        actorOrgId: req.permission.orgId,
-        actorAuthMethod: req.permission.authMethod,
-        requestId: req.params.requestId,
-        projectSlug: req.query.projectSlug
-      });
-
-      return { request };
-    }
-  });
-
   server.route({
     url: "/:requestId/review",
     method: "POST",

backend/src/ee/routes/v1/identity-project-additional-privilege-router.ts

@@ -1,16 +1,19 @@
-import { MongoAbility, RawRuleOf } from "@casl/ability";
-import { PackRule, packRules, unpackRules } from "@casl/ability/extra";
+import { packRules } from "@casl/ability/extra";
 import slugify from "@sindresorhus/slugify";
 import ms from "ms";
 import { z } from "zod";
 
-import { IdentityProjectAdditionalPrivilegeSchema } from "@app/db/schemas";
 import { IdentityProjectAdditionalPrivilegeTemporaryMode } from "@app/ee/services/identity-project-additional-privilege/identity-project-additional-privilege-types";
-import { ProjectPermissionSet } from "@app/ee/services/permission/project-permission";
 import { IDENTITY_ADDITIONAL_PRIVILEGE } from "@app/lib/api-docs";
+import { BadRequestError } from "@app/lib/errors";
 import { alphaNumericNanoId } from "@app/lib/nanoid";
 import { readLimit, writeLimit } from "@app/server/config/rateLimiter";
 import { verifyAuth } from "@app/server/plugins/auth/verify-auth";
+import {
+  ProjectPermissionSchema,
+  ProjectSpecificPrivilegePermissionSchema,
+  SanitizedIdentityPrivilegeSchema
+} from "@app/server/routes/sanitizedSchemas";
 import { AuthMode } from "@app/services/auth/auth-type";
 
 export const registerIdentityProjectAdditionalPrivilegeRouter = async (server: FastifyZodProvider) => {
@@ -41,16 +44,33 @@ export const registerIdentityProjectAdditionalPrivilegeRouter = async (server: F
           })
           .optional()
           .describe(IDENTITY_ADDITIONAL_PRIVILEGE.CREATE.slug),
-        permissions: z.any().array().describe(IDENTITY_ADDITIONAL_PRIVILEGE.CREATE.permissions)
+        permissions: ProjectPermissionSchema.array()
+          .describe(IDENTITY_ADDITIONAL_PRIVILEGE.CREATE.permissions)
+          .optional(),
+        privilegePermission: ProjectSpecificPrivilegePermissionSchema.describe(
+          IDENTITY_ADDITIONAL_PRIVILEGE.CREATE.privilegePermission
+        ).optional()
       }),
       response: {
         200: z.object({
-          privilege: IdentityProjectAdditionalPrivilegeSchema
+          privilege: SanitizedIdentityPrivilegeSchema
         })
       }
     },
     onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
     handler: async (req) => {
+      const { permissions, privilegePermission } = req.body;
+      if (!permissions && !privilegePermission) {
+        throw new BadRequestError({ message: "Permission or privilegePermission must be provided" });
+      }
+
+      const permission = privilegePermission
+        ? privilegePermission.actions.map((action) => ({
+            action,
+            subject: privilegePermission.subject,
+            conditions: privilegePermission.conditions
+          }))
+        : permissions!;
       const privilege = await server.services.identityProjectAdditionalPrivilege.create({
         actorId: req.permission.id,
         actor: req.permission.type,
@@ -59,7 +79,7 @@ export const registerIdentityProjectAdditionalPrivilegeRouter = async (server: F
         ...req.body,
         slug: req.body.slug ? slugify(req.body.slug) : slugify(alphaNumericNanoId(12)),
         isTemporary: false,
-        permissions: JSON.stringify(packRules(req.body.permissions))
+        permissions: JSON.stringify(packRules(permission))
       });
       return { privilege };
     }
@@ -92,7 +112,12 @@ export const registerIdentityProjectAdditionalPrivilegeRouter = async (server: F
           })
           .optional()
           .describe(IDENTITY_ADDITIONAL_PRIVILEGE.CREATE.slug),
-        permissions: z.any().array().describe(IDENTITY_ADDITIONAL_PRIVILEGE.CREATE.permissions),
+        permissions: ProjectPermissionSchema.array()
+          .describe(IDENTITY_ADDITIONAL_PRIVILEGE.CREATE.permissions)
+          .optional(),
+        privilegePermission: ProjectSpecificPrivilegePermissionSchema.describe(
+          IDENTITY_ADDITIONAL_PRIVILEGE.CREATE.privilegePermission
+        ).optional(),
         temporaryMode: z
           .nativeEnum(IdentityProjectAdditionalPrivilegeTemporaryMode)
           .describe(IDENTITY_ADDITIONAL_PRIVILEGE.CREATE.temporaryMode),
@@ -107,12 +132,25 @@ export const registerIdentityProjectAdditionalPrivilegeRouter = async (server: F
       }),
       response: {
         200: z.object({
-          privilege: IdentityProjectAdditionalPrivilegeSchema
+          privilege: SanitizedIdentityPrivilegeSchema
         })
       }
     },
     onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
     handler: async (req) => {
+      const { permissions, privilegePermission } = req.body;
+      if (!permissions && !privilegePermission) {
+        throw new BadRequestError({ message: "Permission or privilegePermission must be provided" });
+      }
+
+      const permission = privilegePermission
+        ? privilegePermission.actions.map((action) => ({
+            action,
+            subject: privilegePermission.subject,
+            conditions: privilegePermission.conditions
+          }))
+        : permissions!;
+
       const privilege = await server.services.identityProjectAdditionalPrivilege.create({
         actorId: req.permission.id,
         actor: req.permission.type,
@@ -121,7 +159,7 @@ export const registerIdentityProjectAdditionalPrivilegeRouter = async (server: F
         ...req.body,
         slug: req.body.slug ? slugify(req.body.slug) : slugify(alphaNumericNanoId(12)),
         isTemporary: true,
-        permissions: JSON.stringify(packRules(req.body.permissions))
+        permissions: JSON.stringify(packRules(permission))
       });
       return { privilege };
     }
@@ -157,14 +195,17 @@ export const registerIdentityProjectAdditionalPrivilegeRouter = async (server: F
                 message: "Slug must be a valid slug"
               })
               .describe(IDENTITY_ADDITIONAL_PRIVILEGE.UPDATE.newSlug),
-            permissions: z.any().array().describe(IDENTITY_ADDITIONAL_PRIVILEGE.UPDATE.permissions),
+            permissions: ProjectPermissionSchema.array().describe(IDENTITY_ADDITIONAL_PRIVILEGE.UPDATE.permissions),
+            privilegePermission: ProjectSpecificPrivilegePermissionSchema.describe(
+              IDENTITY_ADDITIONAL_PRIVILEGE.UPDATE.privilegePermission
+            ).optional(),
             isTemporary: z.boolean().describe(IDENTITY_ADDITIONAL_PRIVILEGE.UPDATE.isTemporary),
             temporaryMode: z
               .nativeEnum(IdentityProjectAdditionalPrivilegeTemporaryMode)
               .describe(IDENTITY_ADDITIONAL_PRIVILEGE.UPDATE.temporaryMode),
             temporaryRange: z
               .string()
-              .refine((val) => ms(val) > 0, "Temporary range must be a positive number")
+              .refine((val) => typeof val === "undefined" || ms(val) > 0, "Temporary range must be a positive number")
               .describe(IDENTITY_ADDITIONAL_PRIVILEGE.UPDATE.temporaryRange),
             temporaryAccessStartTime: z
               .string()
@@ -175,13 +216,24 @@ export const registerIdentityProjectAdditionalPrivilegeRouter = async (server: F
       }),
       response: {
         200: z.object({
-          privilege: IdentityProjectAdditionalPrivilegeSchema
+          privilege: SanitizedIdentityPrivilegeSchema
         })
       }
     },
     onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
     handler: async (req) => {
-      const updatedInfo = req.body.privilegeDetails;
+      const { permissions, privilegePermission, ...updatedInfo } = req.body.privilegeDetails;
+      if (!permissions && !privilegePermission) {
+        throw new BadRequestError({ message: "Permission or privilegePermission must be provided" });
+      }
+
+      const permission = privilegePermission
+        ? privilegePermission.actions.map((action) => ({
+            action,
+            subject: privilegePermission.subject,
+            conditions: privilegePermission.conditions
+          }))
+        : permissions!;
       const privilege = await server.services.identityProjectAdditionalPrivilege.updateBySlug({
         actorId: req.permission.id,
         actor: req.permission.type,
@@ -192,7 +244,7 @@ export const registerIdentityProjectAdditionalPrivilegeRouter = async (server: F
         projectSlug: req.body.projectSlug,
         data: {
           ...updatedInfo,
-          permissions: updatedInfo?.permissions ? JSON.stringify(packRules(updatedInfo.permissions)) : undefined
+          permissions: permission ? JSON.stringify(packRules(permission)) : undefined
         }
       });
       return { privilege };
@@ -219,7 +271,7 @@ export const registerIdentityProjectAdditionalPrivilegeRouter = async (server: F
       }),
       response: {
         200: z.object({
-          privilege: IdentityProjectAdditionalPrivilegeSchema
+          privilege: SanitizedIdentityPrivilegeSchema
         })
       }
     },
@@ -260,7 +312,7 @@ export const registerIdentityProjectAdditionalPrivilegeRouter = async (server: F
       }),
       response: {
         200: z.object({
-          privilege: IdentityProjectAdditionalPrivilegeSchema
+          privilege: SanitizedIdentityPrivilegeSchema
         })
       }
     },
@@ -293,16 +345,11 @@ export const registerIdentityProjectAdditionalPrivilegeRouter = async (server: F
       ],
       querystring: z.object({
         identityId: z.string().min(1).describe(IDENTITY_ADDITIONAL_PRIVILEGE.LIST.identityId),
-        projectSlug: z.string().min(1).describe(IDENTITY_ADDITIONAL_PRIVILEGE.LIST.projectSlug),
-        unpacked: z
-          .enum(["false", "true"])
-          .transform((el) => el === "true")
-          .default("true")
-          .describe(IDENTITY_ADDITIONAL_PRIVILEGE.LIST.unpacked)
+        projectSlug: z.string().min(1).describe(IDENTITY_ADDITIONAL_PRIVILEGE.LIST.projectSlug)
       }),
       response: {
         200: z.object({
-          privileges: IdentityProjectAdditionalPrivilegeSchema.array()
+          privileges: SanitizedIdentityPrivilegeSchema.array()
         })
       }
     },
@@ -315,15 +362,9 @@ export const registerIdentityProjectAdditionalPrivilegeRouter = async (server: F
         actorOrgId: req.permission.orgId,
         ...req.query
       });
-      if (req.query.unpacked) {
-        return {
-          privileges: privileges.map(({ permissions, ...el }) => ({
-            ...el,
-            permissions: unpackRules(permissions as PackRule<RawRuleOf<MongoAbility<ProjectPermissionSet>>>[])
-          }))
-        };
-      }
-      return { privileges };
+      return {
+        privileges
+      };
     }
   });
 };

backend/src/ee/routes/v1/index.ts

@@ -1,6 +1,6 @@
-import { registerAuditLogStreamRouter } from "./audit-log-stream-router";
 import { registerAccessApprovalPolicyRouter } from "./access-approval-policy-router";
 import { registerAccessApprovalRequestRouter } from "./access-approval-request-router";
+import { registerAuditLogStreamRouter } from "./audit-log-stream-router";
 import { registerDynamicSecretLeaseRouter } from "./dynamic-secret-lease-router";
 import { registerDynamicSecretRouter } from "./dynamic-secret-router";
 import { registerGroupRouter } from "./group-router";

backend/src/ee/routes/v1/ldap-router.ts

@@ -18,6 +18,7 @@ import { LdapConfigsSchema, LdapGroupMapsSchema } from "@app/db/schemas";
 import { TLDAPConfig } from "@app/ee/services/ldap-config/ldap-config-types";
 import { isValidLdapFilter, searchGroups } from "@app/ee/services/ldap-config/ldap-fns";
 import { getConfig } from "@app/lib/config/env";
+import { BadRequestError } from "@app/lib/errors";
 import { logger } from "@app/lib/logger";
 import { readLimit, writeLimit } from "@app/server/config/rateLimiter";
 import { verifyAuth } from "@app/server/plugins/auth/verify-auth";
@@ -52,6 +53,7 @@ export const registerLdapRouter = async (server: FastifyZodProvider) => {
       // eslint-disable-next-line
       async (req: IncomingMessage, user, cb) => {
         try {
+          if (!user.email) throw new BadRequestError({ message: "Invalid request. Missing email." });
           const ldapConfig = (req as unknown as FastifyRequest).ldapConfig as TLDAPConfig;
 
           let groups: { dn: string; cn: string }[] | undefined;
@@ -74,7 +76,7 @@ export const registerLdapRouter = async (server: FastifyZodProvider) => {
             username: user.uid,
             firstName: user.givenName ?? user.cn ?? "",
             lastName: user.sn ?? "",
-            emails: user.mail ? [user.mail] : [],
+            email: user.mail,
             groups,
             relayState: ((req as unknown as FastifyRequest).body as { RelayState?: string }).RelayState,
             orgId: (req as unknown as FastifyRequest).ldapConfig.organization

backend/src/ee/routes/v1/org-role-router.ts

@@ -23,7 +23,7 @@ export const registerOrgRoleRouter = async (server: FastifyZodProvider) => {
           .min(1)
           .trim()
           .refine(
-            (val) => !Object.keys(OrgMembershipRole).includes(val),
+            (val) => !Object.values(OrgMembershipRole).includes(val as OrgMembershipRole),
             "Please choose a different slug, the slug you have entered is reserved"
           )
           .refine((v) => slugify(v) === v, {

backend/src/ee/routes/v1/project-role-router.ts

@@ -1,146 +1,232 @@
+import { packRules } from "@casl/ability/extra";
+import slugify from "@sindresorhus/slugify";
 import { z } from "zod";
 
-import { ProjectMembershipsSchema, ProjectRolesSchema } from "@app/db/schemas";
+import { ProjectMembershipRole, ProjectMembershipsSchema, ProjectRolesSchema } from "@app/db/schemas";
+import { PROJECT_ROLE } from "@app/lib/api-docs";
 import { readLimit, writeLimit } from "@app/server/config/rateLimiter";
 import { verifyAuth } from "@app/server/plugins/auth/verify-auth";
+import { ProjectPermissionSchema, SanitizedRoleSchema } from "@app/server/routes/sanitizedSchemas";
 import { AuthMode } from "@app/services/auth/auth-type";
 
 export const registerProjectRoleRouter = async (server: FastifyZodProvider) => {
   server.route({
     method: "POST",
-    url: "/:projectId/roles",
+    url: "/:projectSlug/roles",
     config: {
       rateLimit: writeLimit
     },
     schema: {
+      description: "Create a project role",
+      security: [
+        {
+          bearerAuth: []
+        }
+      ],
       params: z.object({
-        projectId: z.string().trim()
+        projectSlug: z.string().trim().describe(PROJECT_ROLE.CREATE.projectSlug)
       }),
       body: z.object({
-        slug: z.string().trim(),
-        name: z.string().trim(),
-        description: z.string().trim().optional(),
-        permissions: z.any().array()
+        slug: z
+          .string()
+          .toLowerCase()
+          .trim()
+          .min(1)
+          .refine(
+            (val) => !Object.values(ProjectMembershipRole).includes(val as ProjectMembershipRole),
+            "Please choose a different slug, the slug you have entered is reserved"
+          )
+          .refine((v) => slugify(v) === v, {
+            message: "Slug must be a valid"
+          })
+          .describe(PROJECT_ROLE.CREATE.slug),
+        name: z.string().min(1).trim().describe(PROJECT_ROLE.CREATE.name),
+        description: z.string().trim().optional().describe(PROJECT_ROLE.CREATE.description),
+        permissions: ProjectPermissionSchema.array().describe(PROJECT_ROLE.CREATE.permissions)
       }),
       response: {
         200: z.object({
-          role: ProjectRolesSchema
+          role: SanitizedRoleSchema
         })
       }
     },
-    onRequest: verifyAuth([AuthMode.JWT]),
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
     handler: async (req) => {
-      const role = await server.services.projectRole.createRole(
-        req.permission.type,
-        req.permission.id,
-        req.params.projectId,
-        req.body,
-        req.permission.authMethod,
-        req.permission.orgId
-      );
+      const role = await server.services.projectRole.createRole({
+        actorAuthMethod: req.permission.authMethod,
+        actorId: req.permission.id,
+        actorOrgId: req.permission.orgId,
+        actor: req.permission.type,
+        projectSlug: req.params.projectSlug,
+        data: {
+          ...req.body,
+          permissions: JSON.stringify(packRules(req.body.permissions))
+        }
+      });
       return { role };
     }
   });
 
   server.route({
     method: "PATCH",
-    url: "/:projectId/roles/:roleId",
+    url: "/:projectSlug/roles/:roleId",
     config: {
       rateLimit: writeLimit
     },
     schema: {
+      description: "Update a project role",
+      security: [
+        {
+          bearerAuth: []
+        }
+      ],
       params: z.object({
-        projectId: z.string().trim(),
-        roleId: z.string().trim()
+        projectSlug: z.string().trim().describe(PROJECT_ROLE.UPDATE.projectSlug),
+        roleId: z.string().trim().describe(PROJECT_ROLE.UPDATE.roleId)
       }),
       body: z.object({
-        slug: z.string().trim().optional(),
-        name: z.string().trim().optional(),
-        description: z.string().trim().optional(),
-        permissions: z.any().array()
+        slug: z
+          .string()
+          .toLowerCase()
+          .trim()
+          .optional()
+          .describe(PROJECT_ROLE.UPDATE.slug)
+          .refine(
+            (val) =>
+              typeof val === "undefined" ||
+              !Object.values(ProjectMembershipRole).includes(val as ProjectMembershipRole),
+            "Please choose a different slug, the slug you have entered is reserved"
+          )
+          .refine((val) => typeof val === "undefined" || slugify(val) === val, {
+            message: "Slug must be a valid"
+          }),
+        name: z.string().trim().optional().describe(PROJECT_ROLE.UPDATE.name),
+        permissions: ProjectPermissionSchema.array().describe(PROJECT_ROLE.UPDATE.permissions)
       }),
       response: {
         200: z.object({
-          role: ProjectRolesSchema
+          role: SanitizedRoleSchema
         })
       }
     },
-    onRequest: verifyAuth([AuthMode.JWT]),
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
     handler: async (req) => {
-      const role = await server.services.projectRole.updateRole(
-        req.permission.type,
-        req.permission.id,
-        req.params.projectId,
-        req.params.roleId,
-        req.body,
-        req.permission.authMethod,
-        req.permission.orgId
-      );
+      const role = await server.services.projectRole.updateRole({
+        actorAuthMethod: req.permission.authMethod,
+        actorId: req.permission.id,
+        actorOrgId: req.permission.orgId,
+        actor: req.permission.type,
+        projectSlug: req.params.projectSlug,
+        roleId: req.params.roleId,
+        data: {
+          ...req.body,
+          permissions: JSON.stringify(packRules(req.body.permissions))
+        }
+      });
       return { role };
     }
   });
 
   server.route({
     method: "DELETE",
-    url: "/:projectId/roles/:roleId",
+    url: "/:projectSlug/roles/:roleId",
     config: {
       rateLimit: writeLimit
     },
     schema: {
+      description: "Delete a project role",
+      security: [
+        {
+          bearerAuth: []
+        }
+      ],
       params: z.object({
-        projectId: z.string().trim(),
-        roleId: z.string().trim()
+        projectSlug: z.string().trim().describe(PROJECT_ROLE.DELETE.projectSlug),
+        roleId: z.string().trim().describe(PROJECT_ROLE.DELETE.roleId)
       }),
       response: {
         200: z.object({
-          role: ProjectRolesSchema
+          role: SanitizedRoleSchema
         })
       }
     },
-    onRequest: verifyAuth([AuthMode.JWT]),
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
     handler: async (req) => {
-      const role = await server.services.projectRole.deleteRole(
-        req.permission.type,
-        req.permission.id,
-        req.params.projectId,
-        req.params.roleId,
-        req.permission.authMethod,
-        req.permission.orgId
-      );
+      const role = await server.services.projectRole.deleteRole({
+        actorAuthMethod: req.permission.authMethod,
+        actorId: req.permission.id,
+        actorOrgId: req.permission.orgId,
+        actor: req.permission.type,
+        projectSlug: req.params.projectSlug,
+        roleId: req.params.roleId
+      });
       return { role };
     }
   });
 
   server.route({
     method: "GET",
-    url: "/:projectId/roles",
+    url: "/:projectSlug/roles",
+    config: {
+      rateLimit: readLimit
+    },
+    schema: {
+      description: "List project role",
+      security: [
+        {
+          bearerAuth: []
+        }
+      ],
+      params: z.object({
+        projectSlug: z.string().trim().describe(PROJECT_ROLE.LIST.projectSlug)
+      }),
+      response: {
+        200: z.object({
+          roles: ProjectRolesSchema.omit({ permissions: true }).array()
+        })
+      }
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    handler: async (req) => {
+      const roles = await server.services.projectRole.listRoles({
+        actorAuthMethod: req.permission.authMethod,
+        actorId: req.permission.id,
+        actorOrgId: req.permission.orgId,
+        actor: req.permission.type,
+        projectSlug: req.params.projectSlug
+      });
+      return { roles };
+    }
+  });
+
+  server.route({
+    method: "GET",
+    url: "/:projectSlug/roles/slug/:slug",
     config: {
       rateLimit: readLimit
     },
     schema: {
       params: z.object({
-        projectId: z.string().trim()
+        projectSlug: z.string().trim().describe(PROJECT_ROLE.GET_ROLE_BY_SLUG.projectSlug),
+        slug: z.string().trim().describe(PROJECT_ROLE.GET_ROLE_BY_SLUG.roleSlug)
       }),
       response: {
         200: z.object({
-          data: z.object({
-            roles: ProjectRolesSchema.omit({ permissions: true })
-              .merge(z.object({ permissions: z.unknown() }))
-              .array()
-          })
+          role: SanitizedRoleSchema
         })
       }
     },
-    onRequest: verifyAuth([AuthMode.JWT]),
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
     handler: async (req) => {
-      const roles = await server.services.projectRole.listRoles(
-        req.permission.type,
-        req.permission.id,
-        req.params.projectId,
-        req.permission.authMethod,
-        req.permission.orgId
-      );
-      return { data: { roles } };
+      const role = await server.services.projectRole.getRoleBySlug({
+        actorAuthMethod: req.permission.authMethod,
+        actorId: req.permission.id,
+        actorOrgId: req.permission.orgId,
+        actor: req.permission.type,
+        projectSlug: req.params.projectSlug,
+        roleSlug: req.params.slug
+      });
+      return { role };
     }
   });
 

backend/src/ee/routes/v1/saml-router.ts

@@ -102,12 +102,12 @@ export const registerSamlRouter = async (server: FastifyZodProvider) => {
           if (!profile) throw new BadRequestError({ message: "Missing profile" });
           const email = profile?.email ?? (profile?.emailAddress as string); // emailRippling is added because in Rippling the field `email` reserved
 
-          if (!profile.email || !profile.firstName) {
+          if (!email || !profile.firstName) {
             throw new BadRequestError({ message: "Invalid request. Missing email or first name" });
           }
 
           const { isUserCompleted, providerAuthToken } = await server.services.saml.samlLogin({
-            username: profile.nameID ?? email,
+            externalId: profile.nameID,
             email,
             firstName: profile.firstName as string,
             lastName: profile.lastName as string,

backend/src/ee/routes/v1/scim-router.ts

@@ -153,7 +153,7 @@ export const registerScimRouter = async (server: FastifyZodProvider) => {
     onRequest: verifyAuth([AuthMode.SCIM_TOKEN]),
     handler: async (req) => {
       const users = await req.server.services.scim.listScimUsers({
-        offset: req.query.startIndex,
+        startIndex: req.query.startIndex,
         limit: req.query.count,
         filter: req.query.filter,
         orgId: req.permission.orgId
@@ -163,11 +163,11 @@ export const registerScimRouter = async (server: FastifyZodProvider) => {
   });
 
   server.route({
-    url: "/Users/:userId",
+    url: "/Users/:orgMembershipId",
     method: "GET",
     schema: {
       params: z.object({
-        userId: z.string().trim()
+        orgMembershipId: z.string().trim()
       }),
       response: {
         201: z.object({
@@ -193,7 +193,7 @@ export const registerScimRouter = async (server: FastifyZodProvider) => {
     onRequest: verifyAuth([AuthMode.SCIM_TOKEN]),
     handler: async (req) => {
       const user = await req.server.services.scim.getScimUser({
-        userId: req.params.userId,
+        orgMembershipId: req.params.orgMembershipId,
         orgId: req.permission.orgId
       });
       return user;
@@ -249,7 +249,7 @@ export const registerScimRouter = async (server: FastifyZodProvider) => {
       const primaryEmail = req.body.emails?.find((email) => email.primary)?.value;
 
       const user = await req.server.services.scim.createScimUser({
-        username: req.body.userName,
+        externalId: req.body.userName,
         email: primaryEmail,
         firstName: req.body.name.givenName,
         lastName: req.body.name.familyName,
@@ -261,11 +261,11 @@ export const registerScimRouter = async (server: FastifyZodProvider) => {
   });
 
   server.route({
-    url: "/Users/:userId",
+    url: "/Users/:orgMembershipId",
     method: "DELETE",
     schema: {
       params: z.object({
-        userId: z.string().trim()
+        orgMembershipId: z.string().trim()
       }),
       response: {
         200: z.object({})
@@ -274,7 +274,7 @@ export const registerScimRouter = async (server: FastifyZodProvider) => {
     onRequest: verifyAuth([AuthMode.SCIM_TOKEN]),
     handler: async (req) => {
       const user = await req.server.services.scim.deleteScimUser({
-        userId: req.params.userId,
+        orgMembershipId: req.params.orgMembershipId,
         orgId: req.permission.orgId
       });
 
@@ -361,7 +361,7 @@ export const registerScimRouter = async (server: FastifyZodProvider) => {
     handler: async (req) => {
       const groups = await req.server.services.scim.listScimGroups({
         orgId: req.permission.orgId,
-        offset: req.query.startIndex,
+        startIndex: req.query.startIndex,
         limit: req.query.count
       });
 
@@ -416,10 +416,10 @@ export const registerScimRouter = async (server: FastifyZodProvider) => {
         displayName: z.string().trim(),
         members: z.array(
           z.object({
-            value: z.string(), // infisical userId
+            value: z.string(), // infisical orgMembershipId
             display: z.string()
           })
-        ) // note: is this where members are added to group?
+        )
       }),
       response: {
         200: z.object({
@@ -534,11 +534,11 @@ export const registerScimRouter = async (server: FastifyZodProvider) => {
   });
 
   server.route({
-    url: "/Users/:userId",
+    url: "/Users/:orgMembershipId",
     method: "PUT",
     schema: {
       params: z.object({
-        userId: z.string().trim()
+        orgMembershipId: z.string().trim()
       }),
       body: z.object({
         schemas: z.array(z.string()),
@@ -575,7 +575,7 @@ export const registerScimRouter = async (server: FastifyZodProvider) => {
     onRequest: verifyAuth([AuthMode.SCIM_TOKEN]),
     handler: async (req) => {
       const user = await req.server.services.scim.replaceScimUser({
-        userId: req.params.userId,
+        orgMembershipId: req.params.orgMembershipId,
         orgId: req.permission.orgId,
         active: req.body.active
       });

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

@@ -130,7 +130,7 @@ export const registerSecretApprovalPolicyRouter = async (server: FastifyZodProvi
       }),
       response: {
         200: z.object({
-          approvals: sapPubSchema.merge(z.object({ approvers: z.string().nullish().array() })).array()
+          approvals: sapPubSchema.merge(z.object({ approvers: z.string().array() })).array()
         })
       }
     },
@@ -161,7 +161,7 @@ export const registerSecretApprovalPolicyRouter = async (server: FastifyZodProvi
       }),
       response: {
         200: z.object({
-          policy: sapPubSchema.merge(z.object({ approvers: z.string().nullish().array() })).optional()
+          policy: sapPubSchema.merge(z.object({ approvers: z.string().array() })).optional()
         })
       }
     },

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

@@ -32,22 +32,20 @@ export const registerSecretApprovalRequestRouter = async (server: FastifyZodProv
       }),
       response: {
         200: z.object({
-          approvals: SecretApprovalRequestsSchema.merge(
-            z.object({
-              // secretPath: z.string(),
-              policy: z.object({
-                id: z.string(),
-                name: z.string(),
-                approvals: z.number(),
-                approvers: z.string().array(),
-                secretPath: z.string().optional().nullable()
-              }),
-              commits: z.object({ op: z.string(), secretId: z.string().nullable().optional() }).array(),
-              environment: z.string(),
-              reviewers: z.object({ member: z.string(), status: z.string() }).array(),
-              approvers: z.string().array()
-            })
-          ).array()
+          approvals: SecretApprovalRequestsSchema.extend({
+            // secretPath: z.string(),
+            policy: z.object({
+              id: z.string(),
+              name: z.string(),
+              approvals: z.number(),
+              approvers: z.string().array(),
+              secretPath: z.string().optional().nullable()
+            }),
+            commits: z.object({ op: z.string(), secretId: z.string().nullable().optional() }).array(),
+            environment: z.string(),
+            reviewers: z.object({ member: z.string(), status: z.string() }).array(),
+            approvers: z.string().array()
+          }).array()
         })
       }
     },
@@ -197,7 +195,7 @@ export const registerSecretApprovalRequestRouter = async (server: FastifyZodProv
           type: isClosing ? EventType.SECRET_APPROVAL_CLOSED : EventType.SECRET_APPROVAL_REOPENED,
           // eslint-disable-next-line
           metadata: {
-            [isClosing ? ("closedBy" as const) : ("reopenedBy" as const)]: approval.statusChangeByUserId as string,
+            [isClosing ? ("closedBy" as const) : ("reopenedBy" as const)]: approval.statusChangeBy as string,
             secretApprovalRequestId: approval.id,
             secretApprovalRequestSlug: approval.slug
             // eslint-disable-next-line

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

@@ -20,7 +20,7 @@ export const accessApprovalPolicyDALFactory = (db: TDbClient) => {
         `${TableName.AccessApprovalPolicy}.id`,
         `${TableName.AccessApprovalPolicyApprover}.policyId`
       )
-      .select(tx.ref("approverUserId").withSchema(TableName.AccessApprovalPolicyApprover))
+      .select(tx.ref("approverId").withSchema(TableName.AccessApprovalPolicyApprover))
       .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"))
@@ -38,12 +38,12 @@ export const accessApprovalPolicyDALFactory = (db: TDbClient) => {
       const formatedDoc = mergeOneToManyRelation(
         doc,
         "id",
-        ({ approverUserId, envId, envName: name, envSlug: slug, ...el }) => ({
+        ({ approverId, envId, envName: name, envSlug: slug, ...el }) => ({
           ...el,
           envId,
           environment: { id: envId, name, slug }
         }),
-        ({ approverUserId }) => approverUserId,
+        ({ approverId }) => approverId,
         "approvers"
       );
       return formatedDoc?.[0];
@@ -58,12 +58,12 @@ export const accessApprovalPolicyDALFactory = (db: TDbClient) => {
       const formatedDoc = mergeOneToManyRelation(
         docs,
         "id",
-        ({ approverUserId, envId, envName: name, envSlug: slug, ...el }) => ({
+        ({ approverId, envId, envName: name, envSlug: slug, ...el }) => ({
           ...el,
           envId,
           environment: { id: envId, name, slug }
         }),
-        ({ approverUserId }) => approverUserId,
+        ({ approverId }) => approverId,
         "approvers"
       );
       return formatedDoc.map((policy) => ({ ...policy, secretPath: policy.secretPath || undefined }));

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

@@ -5,7 +5,7 @@ import { ProjectPermissionActions, ProjectPermissionSub } from "@app/ee/services
 import { BadRequestError } from "@app/lib/errors";
 import { TProjectDALFactory } from "@app/services/project/project-dal";
 import { TProjectEnvDALFactory } from "@app/services/project-env/project-env-dal";
-import { TUserDALFactory } from "@app/services/user/user-dal";
+import { TProjectMembershipDALFactory } from "@app/services/project-membership/project-membership-dal";
 
 import { TAccessApprovalPolicyApproverDALFactory } from "./access-approval-policy-approver-dal";
 import { TAccessApprovalPolicyDALFactory } from "./access-approval-policy-dal";
@@ -24,7 +24,7 @@ type TSecretApprovalPolicyServiceFactoryDep = {
   accessApprovalPolicyDAL: TAccessApprovalPolicyDALFactory;
   projectEnvDAL: Pick<TProjectEnvDALFactory, "find" | "findOne">;
   accessApprovalPolicyApproverDAL: TAccessApprovalPolicyApproverDALFactory;
-  userDAL: Pick<TUserDALFactory, "findUsersByProjectId">;
+  projectMembershipDAL: Pick<TProjectMembershipDALFactory, "find">;
 };
 
 export type TAccessApprovalPolicyServiceFactory = ReturnType<typeof accessApprovalPolicyServiceFactory>;
@@ -34,8 +34,8 @@ export const accessApprovalPolicyServiceFactory = ({
   accessApprovalPolicyApproverDAL,
   permissionService,
   projectEnvDAL,
-  userDAL,
-  projectDAL
+  projectDAL,
+  projectMembershipDAL
 }: TSecretApprovalPolicyServiceFactoryDep) => {
   const createAccessApprovalPolicy = async ({
     name,
@@ -69,13 +69,12 @@ export const accessApprovalPolicyServiceFactory = ({
     const env = await projectEnvDAL.findOne({ slug: environment, projectId: project.id });
     if (!env) throw new BadRequestError({ message: "Environment not found" });
 
-    // We need to get the users by project ID to ensure they are part of the project.
-    const accessApproverUsers = await userDAL.findUsersByProjectId(
-      project.id,
-      approvers.map((approverUserId) => approverUserId)
-    );
+    const secretApprovers = await projectMembershipDAL.find({
+      projectId: project.id,
+      $in: { id: approvers }
+    });
 
-    if (accessApproverUsers.length !== approvers.length) {
+    if (secretApprovers.length !== approvers.length) {
       throw new BadRequestError({ message: "Approver not found in project" });
     }
 
@@ -86,7 +85,7 @@ export const accessApprovalPolicyServiceFactory = ({
       secretPath,
       actorAuthMethod,
       permissionService,
-      userIds: accessApproverUsers.map((user) => user.id)
+      userIds: secretApprovers.map((approver) => approver.userId)
     });
 
     const accessApproval = await accessApprovalPolicyDAL.transaction(async (tx) => {
@@ -100,8 +99,8 @@ export const accessApprovalPolicyServiceFactory = ({
         tx
       );
       await accessApprovalPolicyApproverDAL.insertMany(
-        accessApproverUsers.map((user) => ({
-          approverUserId: user.id,
+        secretApprovers.map(({ id }) => ({
+          approverId: id,
           policyId: doc.id
         })),
         tx
@@ -170,9 +169,12 @@ export const accessApprovalPolicyServiceFactory = ({
       );
       if (approvers) {
         // Find the workspace project memberships of the users passed in the approvers array
-        const secretApproverUsers = await userDAL.findUsersByProjectId(
-          accessApprovalPolicy.projectId,
-          approvers.map((approverUserId) => approverUserId)
+        const secretApprovers = await projectMembershipDAL.find(
+          {
+            projectId: accessApprovalPolicy.projectId,
+            $in: { id: approvers }
+          },
+          { tx }
         );
 
         await verifyApprovers({
@@ -182,15 +184,15 @@ export const accessApprovalPolicyServiceFactory = ({
           secretPath: doc.secretPath!,
           actorAuthMethod,
           permissionService,
-          userIds: secretApproverUsers.map((user) => user.id)
+          userIds: secretApprovers.map((approver) => approver.userId)
         });
 
-        if (secretApproverUsers.length !== approvers.length)
+        if (secretApprovers.length !== approvers.length)
           throw new BadRequestError({ message: "Approvals cannot be greater than approvers" });
         await accessApprovalPolicyApproverDAL.delete({ policyId: doc.id }, tx);
         await accessApprovalPolicyApproverDAL.insertMany(
-          secretApproverUsers.map((user) => ({
-            approverUserId: user.id,
+          secretApprovers.map(({ id }) => ({
+            approverId: id,
             policyId: doc.id
           })),
           tx

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

@@ -11,42 +11,6 @@ export type TAccessApprovalRequestDALFactory = ReturnType<typeof accessApprovalR
 
 export const accessApprovalRequestDALFactory = (db: TDbClient) => {
   const accessApprovalRequestOrm = ormify(db, TableName.AccessApprovalRequest);
-  const projectUserAdditionalPrivilegeOrm = ormify(db, TableName.ProjectUserAdditionalPrivilege);
-  const groupProjectUserAdditionalPrivilegeOrm = ormify(db, TableName.GroupProjectUserAdditionalPrivilege);
-
-  const deleteMany = async (filter: TFindFilter<TAccessApprovalRequests>, tx?: Knex) => {
-    const transaction = tx || (await db.transaction());
-
-    try {
-      const accessApprovalRequests = await accessApprovalRequestOrm.find(filter, { tx: transaction });
-
-      await projectUserAdditionalPrivilegeOrm.delete(
-        {
-          $in: {
-            id: accessApprovalRequests
-              .filter((req) => Boolean(req.projectUserPrivilegeId))
-              .map((req) => req.projectUserPrivilegeId!)
-          }
-        },
-        transaction
-      );
-
-      await groupProjectUserAdditionalPrivilegeOrm.delete(
-        {
-          $in: {
-            id: accessApprovalRequests
-              .filter((req) => Boolean(req.groupProjectUserPrivilegeId))
-              .map((req) => req.groupProjectUserPrivilegeId!)
-          }
-        },
-        transaction
-      );
-
-      return await accessApprovalRequestOrm.delete(filter, transaction);
-    } catch (error) {
-      throw new DatabaseError({ error, name: "DeleteManyAccessApprovalRequest" });
-    }
-  };
 
   const findRequestsWithPrivilegeByPolicyIds = async (policyIds: string[]) => {
     try {
@@ -55,14 +19,9 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
 
         .leftJoin(
           TableName.ProjectUserAdditionalPrivilege,
-          `${TableName.AccessApprovalRequest}.projectUserPrivilegeId`,
+          `${TableName.AccessApprovalRequest}.privilegeId`,
           `${TableName.ProjectUserAdditionalPrivilege}.id`
         )
-        .leftJoin(
-          TableName.GroupProjectUserAdditionalPrivilege,
-          `${TableName.AccessApprovalRequest}.groupProjectUserPrivilegeId`,
-          `${TableName.GroupProjectUserAdditionalPrivilege}.id`
-        )
         .leftJoin(
           TableName.AccessApprovalPolicy,
           `${TableName.AccessApprovalRequest}.policyId`,
@@ -91,7 +50,7 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
           db.ref("envId").withSchema(TableName.AccessApprovalPolicy).as("policyEnvId")
         )
 
-        .select(db.ref("approverUserId").withSchema(TableName.AccessApprovalPolicyApprover))
+        .select(db.ref("approverId").withSchema(TableName.AccessApprovalPolicyApprover))
 
         .select(
           db.ref("projectId").withSchema(TableName.Environment),
@@ -100,85 +59,32 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
         )
 
         .select(
-          db.ref("memberUserId").withSchema(TableName.AccessApprovalRequestReviewer).as("reviewerUserId"),
+          db.ref("member").withSchema(TableName.AccessApprovalRequestReviewer).as("reviewerMemberId"),
           db.ref("status").withSchema(TableName.AccessApprovalRequestReviewer).as("reviewerStatus")
         )
 
-        // Project user additional privilege
         .select(
           db
             .ref("projectMembershipId")
             .withSchema(TableName.ProjectUserAdditionalPrivilege)
-            .as("projectPrivilegeProjectMembershipId"),
-
-          db.ref("isTemporary").withSchema(TableName.ProjectUserAdditionalPrivilege).as("projectPrivilegeIsTemporary"),
-
-          db
-            .ref("temporaryMode")
-            .withSchema(TableName.ProjectUserAdditionalPrivilege)
-            .as("projectPrivilegeTemporaryMode"),
-
-          db
-            .ref("temporaryRange")
-            .withSchema(TableName.ProjectUserAdditionalPrivilege)
-            .as("projectPrivilegeTemporaryRange"),
-
+            .as("privilegeMembershipId"),
+          db.ref("isTemporary").withSchema(TableName.ProjectUserAdditionalPrivilege).as("privilegeIsTemporary"),
+          db.ref("temporaryMode").withSchema(TableName.ProjectUserAdditionalPrivilege).as("privilegeTemporaryMode"),
+          db.ref("temporaryRange").withSchema(TableName.ProjectUserAdditionalPrivilege).as("privilegeTemporaryRange"),
           db
             .ref("temporaryAccessStartTime")
             .withSchema(TableName.ProjectUserAdditionalPrivilege)
-            .as("projectPrivilegeTemporaryAccessStartTime"),
-
+            .as("privilegeTemporaryAccessStartTime"),
           db
             .ref("temporaryAccessEndTime")
             .withSchema(TableName.ProjectUserAdditionalPrivilege)
-            .as("projectPrivilegeTemporaryAccessEndTime"),
+            .as("privilegeTemporaryAccessEndTime"),
 
-          db.ref("permissions").withSchema(TableName.ProjectUserAdditionalPrivilege).as("projectPrivilegePermissions")
-        )
-        // Group project user additional privilege
-        .select(
-          db
-            .ref("groupProjectMembershipId")
-            .withSchema(TableName.GroupProjectUserAdditionalPrivilege)
-            .as("groupPrivilegeGroupProjectMembershipId"),
-
-          db
-            .ref("requestedByUserId")
-            .withSchema(TableName.GroupProjectUserAdditionalPrivilege)
-            .as("groupPrivilegeRequestedByUserId"),
-
-          db
-            .ref("isTemporary")
-            .withSchema(TableName.GroupProjectUserAdditionalPrivilege)
-            .as("groupPrivilegeIsTemporary"),
-
-          db
-            .ref("temporaryMode")
-            .withSchema(TableName.GroupProjectUserAdditionalPrivilege)
-            .as("groupPrivilegeTemporaryMode"),
-
-          db
-            .ref("temporaryRange")
-            .withSchema(TableName.GroupProjectUserAdditionalPrivilege)
-            .as("groupPrivilegeTemporaryRange"),
-
-          db
-            .ref("temporaryAccessStartTime")
-            .withSchema(TableName.GroupProjectUserAdditionalPrivilege)
-            .as("groupPrivilegeTemporaryAccessStartTime"),
-
-          db
-            .ref("temporaryAccessEndTime")
-            .withSchema(TableName.GroupProjectUserAdditionalPrivilege)
-            .as("groupPrivilegeTemporaryAccessEndTime"),
-          db
-            .ref("permissions")
-            .withSchema(TableName.GroupProjectUserAdditionalPrivilege)
-            .as("groupPrivilegePermissions")
+          db.ref("permissions").withSchema(TableName.ProjectUserAdditionalPrivilege).as("privilegePermissions")
         )
         .orderBy(`${TableName.AccessApprovalRequest}.createdAt`, "desc");
 
-      const projectUserFormattedDocs = sqlNestRelationships({
+      const formattedDocs = sqlNestRelationships({
         data: docs,
         key: "id",
         parentMapper: (doc) => ({
@@ -193,49 +99,33 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
             secretPath: doc.policySecretPath,
             envId: doc.policyEnvId
           },
-          // eslint-disable-next-line no-nested-ternary
-          privilege: doc.projectUserPrivilegeId
+          privilege: doc.privilegeId
             ? {
-                projectMembershipId: doc.projectMembershipId,
-                groupMembershipId: null,
-                requestedByUserId: null,
-                isTemporary: doc.projectPrivilegeIsTemporary,
-                temporaryMode: doc.projectPrivilegeTemporaryMode,
-                temporaryRange: doc.projectPrivilegeTemporaryRange,
-                temporaryAccessStartTime: doc.projectPrivilegeTemporaryAccessStartTime,
-                temporaryAccessEndTime: doc.projectPrivilegeTemporaryAccessEndTime,
-                permissions: doc.projectPrivilegePermissions
+                membershipId: doc.privilegeMembershipId,
+                isTemporary: doc.privilegeIsTemporary,
+                temporaryMode: doc.privilegeTemporaryMode,
+                temporaryRange: doc.privilegeTemporaryRange,
+                temporaryAccessStartTime: doc.privilegeTemporaryAccessStartTime,
+                temporaryAccessEndTime: doc.privilegeTemporaryAccessEndTime,
+                permissions: doc.privilegePermissions
               }
-            : doc.groupProjectUserPrivilegeId
-              ? {
-                  groupMembershipId: doc.groupPrivilegeGroupProjectMembershipId,
-                  requestedByUserId: doc.groupPrivilegeRequestedByUserId,
-                  projectMembershipId: null,
-                  isTemporary: doc.groupPrivilegeIsTemporary,
-                  temporaryMode: doc.groupPrivilegeTemporaryMode,
-                  temporaryRange: doc.groupPrivilegeTemporaryRange,
-                  temporaryAccessStartTime: doc.groupPrivilegeTemporaryAccessStartTime,
-                  temporaryAccessEndTime: doc.groupPrivilegeTemporaryAccessEndTime,
-                  permissions: doc.groupPrivilegePermissions
-                }
-              : null,
+            : null,
 
-          isApproved: Boolean(doc.projectUserPrivilegeId || doc.groupProjectUserPrivilegeId)
+          isApproved: !!doc.privilegeId
         }),
         childrenMapper: [
           {
-            key: "reviewerUserId",
+            key: "reviewerMemberId",
             label: "reviewers" as const,
-            mapper: ({ reviewerUserId, reviewerStatus: status }) =>
-              reviewerUserId ? { member: reviewerUserId, status } : undefined
+            mapper: ({ reviewerMemberId: member, reviewerStatus: status }) => (member ? { member, status } : undefined)
           },
-          { key: "approverUserId", label: "approvers" as const, mapper: ({ approverUserId }) => approverUserId }
+          { key: "approverId", label: "approvers" as const, mapper: ({ approverId }) => approverId }
         ]
       });
 
-      if (!projectUserFormattedDocs) return [];
+      if (!formattedDocs) return [];
 
-      return projectUserFormattedDocs.map((doc) => ({
+      return formattedDocs.map((doc) => ({
         ...doc,
         policy: { ...doc.policy, approvers: doc.approvers }
       }));
@@ -267,7 +157,7 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
       .leftJoin(TableName.Environment, `${TableName.AccessApprovalPolicy}.envId`, `${TableName.Environment}.id`)
       .select(selectAllTableCols(TableName.AccessApprovalRequest))
       .select(
-        tx.ref("memberUserId").withSchema(TableName.AccessApprovalRequestReviewer).as("reviewerUserId"),
+        tx.ref("member").withSchema(TableName.AccessApprovalRequestReviewer).as("reviewerMemberId"),
         tx.ref("status").withSchema(TableName.AccessApprovalRequestReviewer).as("reviewerStatus"),
         tx.ref("id").withSchema(TableName.AccessApprovalPolicy).as("policyId"),
         tx.ref("name").withSchema(TableName.AccessApprovalPolicy).as("policyName"),
@@ -275,7 +165,7 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
         tx.ref("slug").withSchema(TableName.Environment).as("environment"),
         tx.ref("secretPath").withSchema(TableName.AccessApprovalPolicy).as("policySecretPath"),
         tx.ref("approvals").withSchema(TableName.AccessApprovalPolicy).as("policyApprovals"),
-        tx.ref("approverUserId").withSchema(TableName.AccessApprovalPolicyApprover)
+        tx.ref("approverId").withSchema(TableName.AccessApprovalPolicyApprover)
       );
 
   const findById = async (id: string, tx?: Knex) => {
@@ -298,12 +188,11 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
         }),
         childrenMapper: [
           {
-            key: "reviewerUserId",
+            key: "reviewerMemberId",
             label: "reviewers" as const,
-            mapper: ({ reviewerUserId, reviewerStatus: status }) =>
-              reviewerUserId ? { member: reviewerUserId, status } : undefined
+            mapper: ({ reviewerMemberId: member, reviewerStatus: status }) => (member ? { member, status } : undefined)
           },
-          { key: "approverUserId", label: "approvers" as const, mapper: ({ approverUserId }) => approverUserId }
+          { key: "approverId", label: "approvers" as const, mapper: ({ approverId }) => approverId }
         ]
       });
       if (!formatedDoc?.[0]) return;
@@ -325,6 +214,12 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
           `${TableName.AccessApprovalPolicy}.id`
         )
         .leftJoin(TableName.Environment, `${TableName.AccessApprovalPolicy}.envId`, `${TableName.Environment}.id`)
+        .leftJoin(
+          TableName.ProjectUserAdditionalPrivilege,
+          `${TableName.AccessApprovalRequest}.privilegeId`,
+          `${TableName.ProjectUserAdditionalPrivilege}.id`
+        )
+
         .leftJoin(
           TableName.AccessApprovalRequestReviewer,
           `${TableName.AccessApprovalRequest}.id`,
@@ -334,7 +229,7 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
         .where(`${TableName.Environment}.projectId`, projectId)
         .select(selectAllTableCols(TableName.AccessApprovalRequest))
         .select(db.ref("status").withSchema(TableName.AccessApprovalRequestReviewer).as("reviewerStatus"))
-        .select(db.ref("memberUserId").withSchema(TableName.AccessApprovalRequestReviewer).as("memberUserId"));
+        .select(db.ref("member").withSchema(TableName.AccessApprovalRequestReviewer).as("reviewerMemberId"));
 
       const formattedRequests = sqlNestRelationships({
         data: accessRequests,
@@ -344,28 +239,21 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
         }),
         childrenMapper: [
           {
-            key: "memberUserId",
+            key: "reviewerMemberId",
             label: "reviewers" as const,
-            mapper: ({ memberUserId, reviewerStatus: status }) =>
-              memberUserId ? { member: memberUserId, status } : undefined
+            mapper: ({ reviewerMemberId: member, reviewerStatus: status }) => (member ? { member, status } : undefined)
           }
         ]
       });
 
       // an approval is pending if there is no reviewer rejections and no privilege ID is set
       const pendingApprovals = formattedRequests.filter(
-        (req) =>
-          !req.projectUserPrivilegeId &&
-          !req.groupProjectUserPrivilegeId &&
-          !req.reviewers.some((r) => r.status === ApprovalStatus.REJECTED)
+        (req) => !req.privilegeId && !req.reviewers.some((r) => r.status === ApprovalStatus.REJECTED)
       );
 
       // an approval is finalized if there are any rejections or a privilege ID is set
       const finalizedApprovals = formattedRequests.filter(
-        (req) =>
-          req.projectUserPrivilegeId ||
-          req.groupProjectUserPrivilegeId ||
-          req.reviewers.some((r) => r.status === ApprovalStatus.REJECTED)
+        (req) => req.privilegeId || req.reviewers.some((r) => r.status === ApprovalStatus.REJECTED)
       );
 
       return { pendingCount: pendingApprovals.length, finalizedCount: finalizedApprovals.length };
@@ -374,5 +262,5 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
     }
   };
 
-  return { ...accessApprovalRequestOrm, findById, findRequestsWithPrivilegeByPolicyIds, getCount, delete: deleteMany };
+  return { ...accessApprovalRequestOrm, findById, findRequestsWithPrivilegeByPolicyIds, getCount };
 };

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

@@ -1,8 +1,7 @@
-import { ForbiddenError } from "@casl/ability";
 import slugify from "@sindresorhus/slugify";
 import ms from "ms";
 
-import { ProjectMembershipRole, TProjectUserAdditionalPrivilege } from "@app/db/schemas";
+import { ProjectMembershipRole } from "@app/db/schemas";
 import { getConfig } from "@app/lib/config/env";
 import { BadRequestError, UnauthorizedError } from "@app/lib/errors";
 import { alphaNumericNanoId } from "@app/lib/nanoid";
@@ -15,9 +14,7 @@ import { TUserDALFactory } from "@app/services/user/user-dal";
 import { TAccessApprovalPolicyApproverDALFactory } from "../access-approval-policy/access-approval-policy-approver-dal";
 import { TAccessApprovalPolicyDALFactory } from "../access-approval-policy/access-approval-policy-dal";
 import { verifyApprovers } from "../access-approval-policy/access-approval-policy-fns";
-import { TGroupProjectUserAdditionalPrivilegeDALFactory } from "../group-project-user-additional-privilege/group-project-user-additional-privilege-dal";
 import { TPermissionServiceFactory } from "../permission/permission-service";
-import { ProjectPermissionActions, 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";
@@ -26,15 +23,13 @@ import { TAccessApprovalRequestReviewerDALFactory } from "./access-approval-requ
 import {
   ApprovalStatus,
   TCreateAccessApprovalRequestDTO,
-  TDeleteApprovalRequestDTO,
   TGetAccessRequestCountDTO,
   TListApprovalRequestsDTO,
   TReviewAccessRequestDTO
 } from "./access-approval-request-types";
 
-type TAccessApprovalRequestServiceFactoryDep = {
-  additionalPrivilegeDAL: Pick<TProjectUserAdditionalPrivilegeDALFactory, "create" | "findById" | "deleteById">;
-  groupAdditionalPrivilegeDAL: TGroupProjectUserAdditionalPrivilegeDALFactory;
+type TSecretApprovalRequestServiceFactoryDep = {
+  additionalPrivilegeDAL: Pick<TProjectUserAdditionalPrivilegeDALFactory, "create" | "findById">;
   permissionService: Pick<TPermissionServiceFactory, "getProjectPermission">;
   accessApprovalPolicyApproverDAL: Pick<TAccessApprovalPolicyApproverDALFactory, "find">;
   projectEnvDAL: Pick<TProjectEnvDALFactory, "findOne">;
@@ -49,7 +44,6 @@ type TAccessApprovalRequestServiceFactoryDep = {
     | "updateById"
     | "findOne"
     | "getCount"
-    | "deleteById"
   >;
   accessApprovalPolicyDAL: Pick<TAccessApprovalPolicyDALFactory, "findOne" | "find">;
   accessApprovalRequestReviewerDAL: Pick<
@@ -58,10 +52,7 @@ type TAccessApprovalRequestServiceFactoryDep = {
   >;
   projectMembershipDAL: Pick<TProjectMembershipDALFactory, "findById">;
   smtpService: Pick<TSmtpService, "sendMail">;
-  userDAL: Pick<
-    TUserDALFactory,
-    "findUserByProjectMembershipId" | "findUsersByProjectMembershipIds" | "findUsersByProjectId" | "findUserByProjectId"
-  >;
+  userDAL: Pick<TUserDALFactory, "findUserByProjectMembershipId" | "findUsersByProjectMembershipIds">;
 };
 
 export type TAccessApprovalRequestServiceFactory = ReturnType<typeof accessApprovalRequestServiceFactory>;
@@ -71,7 +62,6 @@ export const accessApprovalRequestServiceFactory = ({
   projectEnvDAL,
   permissionService,
   accessApprovalRequestDAL,
-  groupAdditionalPrivilegeDAL,
   accessApprovalRequestReviewerDAL,
   projectMembershipDAL,
   accessApprovalPolicyDAL,
@@ -79,7 +69,7 @@ export const accessApprovalRequestServiceFactory = ({
   additionalPrivilegeDAL,
   smtpService,
   userDAL
-}: TAccessApprovalRequestServiceFactoryDep) => {
+}: TSecretApprovalRequestServiceFactoryDep) => {
   const createAccessApprovalRequest = async ({
     isTemporary,
     temporaryRange,
@@ -104,6 +94,9 @@ export const accessApprovalRequestServiceFactory = ({
     );
     if (!membership) throw new UnauthorizedError({ message: "You are not a member of this project" });
 
+    const requestedByUser = await userDAL.findUserByProjectMembershipId(membership.id);
+    if (!requestedByUser) throw new UnauthorizedError({ message: "User not found" });
+
     await projectDAL.checkProjectUpgradeStatus(project.id);
 
     const { envSlug, secretPath, accessTypes } = verifyRequestedPermissions({ permissions: requestedPermissions });
@@ -121,43 +114,25 @@ export const accessApprovalRequestServiceFactory = ({
       policyId: policy.id
     });
 
-    if (approvers.some((approver) => !approver.approverUserId)) {
-      throw new BadRequestError({ message: "Policy approvers must be assigned to users" });
-    }
-
-    const approverUsers = await userDAL.findUsersByProjectId(
-      project.id,
-      approvers.map((approver) => approver.approverUserId!)
+    const approverUsers = await userDAL.findUsersByProjectMembershipIds(
+      approvers.map((approver) => approver.approverId)
     );
 
-    const requestedByUser = await userDAL.findUserByProjectId(project.id, actorId);
-
-    if (!requestedByUser) throw new BadRequestError({ message: "User not found in project" });
-
     const duplicateRequests = await accessApprovalRequestDAL.find({
       policyId: policy.id,
-      requestedByUserId: actorId,
+      requestedBy: membership.id,
       permissions: JSON.stringify(requestedPermissions),
       isTemporary
     });
 
     if (duplicateRequests?.length > 0) {
       for await (const duplicateRequest of duplicateRequests) {
-        let foundPrivilege: Pick<
-          TProjectUserAdditionalPrivilege,
-          "temporaryAccessEndTime" | "isTemporary" | "id"
-        > | null = null;
+        if (duplicateRequest.privilegeId) {
+          const privilege = await additionalPrivilegeDAL.findById(duplicateRequest.privilegeId);
 
-        if (duplicateRequest.projectUserPrivilegeId) {
-          foundPrivilege = await additionalPrivilegeDAL.findById(duplicateRequest.projectUserPrivilegeId);
-        } else if (duplicateRequest.groupProjectUserPrivilegeId) {
-          foundPrivilege = await groupAdditionalPrivilegeDAL.findById(duplicateRequest.groupProjectUserPrivilegeId);
-        }
+          const isExpired = new Date() > new Date(privilege.temporaryAccessEndTime || ("" as string));
 
-        if (foundPrivilege) {
-          const isExpired = new Date() > new Date(foundPrivilege.temporaryAccessEndTime || ("" as string));
-
-          if (!isExpired || !foundPrivilege.isTemporary) {
+          if (!isExpired || !privilege.isTemporary) {
             throw new BadRequestError({ message: "You already have an active privilege with the same criteria" });
           }
         } else {
@@ -175,18 +150,10 @@ export const accessApprovalRequestServiceFactory = ({
     }
 
     const approval = await accessApprovalRequestDAL.transaction(async (tx) => {
-      const requesterUser = await userDAL.findUserByProjectId(project.id, actorId);
-
-      if (!requesterUser?.projectMembershipId && !requesterUser?.groupProjectMembershipId) {
-        throw new BadRequestError({ message: "You don't have a membership for this project" });
-      }
-
       const approvalRequest = await accessApprovalRequestDAL.create(
         {
-          projectMembershipId: requesterUser.projectMembershipId || null,
-          groupMembershipId: requesterUser.groupProjectMembershipId || null,
           policyId: policy.id,
-          requestedByUserId: actorId, // This is the user ID of the person who made the request
+          requestedBy: membership.id,
           temporaryRange: temporaryRange || null,
           permissions: JSON.stringify(requestedPermissions),
           isTemporary
@@ -220,62 +187,9 @@ export const accessApprovalRequestServiceFactory = ({
     return { request: approval };
   };
 
-  const deleteAccessApprovalRequest = async ({
-    projectSlug,
-    actor,
-    requestId,
-    actorOrgId,
-    actorId,
-    actorAuthMethod
-  }: TDeleteApprovalRequestDTO) => {
-    const project = await projectDAL.findProjectBySlug(projectSlug, actorOrgId);
-    if (!project) throw new UnauthorizedError({ message: "Project not found" });
-
-    const { membership, permission } = await permissionService.getProjectPermission(
-      actor,
-      actorId,
-      project.id,
-      actorAuthMethod,
-      actorOrgId
-    );
-    if (!membership) throw new UnauthorizedError({ message: "You are not a member of this project" });
-
-    ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionActions.Delete,
-      ProjectPermissionSub.SecretApproval
-    );
-
-    const accessApprovalRequest = await accessApprovalRequestDAL.findById(requestId);
-
-    if (!accessApprovalRequest?.projectUserPrivilegeId && !accessApprovalRequest?.groupProjectUserPrivilegeId) {
-      throw new BadRequestError({ message: "Access request must be approved to be deleted" });
-    }
-
-    if (accessApprovalRequest?.projectId !== project.id) {
-      throw new UnauthorizedError({ message: "Request not found in project" });
-    }
-
-    const approvers = await accessApprovalPolicyApproverDAL.find({
-      policyId: accessApprovalRequest.policyId
-    });
-
-    // make sure the actor (actorId) is an approver
-    if (!approvers.some((approver) => approver.approverUserId === actorId)) {
-      throw new UnauthorizedError({ message: "Only policy approvers can delete access requests" });
-    }
-
-    if (accessApprovalRequest.projectUserPrivilegeId) {
-      await additionalPrivilegeDAL.deleteById(accessApprovalRequest.projectUserPrivilegeId);
-    } else if (accessApprovalRequest.groupProjectUserPrivilegeId) {
-      await groupAdditionalPrivilegeDAL.deleteById(accessApprovalRequest.groupProjectUserPrivilegeId);
-    }
-
-    return { request: accessApprovalRequest };
-  };
-
   const listApprovalRequests = async ({
     projectSlug,
-    authorUserId,
+    authorProjectMembershipId,
     envSlug,
     actor,
     actorOrgId,
@@ -297,8 +211,13 @@ export const accessApprovalRequestServiceFactory = ({
     const policies = await accessApprovalPolicyDAL.find({ projectId: project.id });
     let requests = await accessApprovalRequestDAL.findRequestsWithPrivilegeByPolicyIds(policies.map((p) => p.id));
 
-    if (authorUserId) requests = requests.filter((request) => request.requestedByUserId === authorUserId);
-    if (envSlug) requests = requests.filter((request) => request.environment === envSlug);
+    if (authorProjectMembershipId) {
+      requests = requests.filter((request) => request.requestedBy === authorProjectMembershipId);
+    }
+
+    if (envSlug) {
+      requests = requests.filter((request) => request.environment === envSlug);
+    }
 
     return { requests };
   };
@@ -327,8 +246,8 @@ export const accessApprovalRequestServiceFactory = ({
 
     if (
       !hasRole(ProjectMembershipRole.Admin) &&
-      accessApprovalRequest.requestedByUserId !== actorId && // The request wasn't made by the current user
-      !policy.approvers.find((approverUserId) => approverUserId === membership.id) // The request isn't performed by an assigned approver
+      accessApprovalRequest.requestedBy !== membership.id && // The request wasn't made by the current user
+      !policy.approvers.find((approverId) => approverId === membership.id) // The request isn't performed by an assigned approver
     ) {
       throw new UnauthorizedError({ message: "You are not authorized to approve this request" });
     }
@@ -354,7 +273,7 @@ export const accessApprovalRequestServiceFactory = ({
       const review = await accessApprovalRequestReviewerDAL.findOne(
         {
           requestId: accessApprovalRequest.id,
-          memberUserId: actorId
+          member: membership.id
         },
         tx
       );
@@ -363,7 +282,7 @@ export const accessApprovalRequestServiceFactory = ({
           {
             status,
             requestId: accessApprovalRequest.id,
-            memberUserId: actorId
+            member: membership.id
           },
           tx
         );
@@ -378,92 +297,41 @@ export const accessApprovalRequestServiceFactory = ({
             throw new BadRequestError({ message: "Temporary range is required for temporary access" });
           }
 
-          let projectUserPrivilegeId: string | null = null;
-          let groupProjectMembershipId: string | null = null;
+          let privilegeId: string | null = null;
 
-          if (!accessApprovalRequest.groupMembershipId && !accessApprovalRequest.projectMembershipId) {
-            throw new BadRequestError({ message: "Project membership or group membership is required" });
-          }
-
-          // Permanent access
           if (!accessApprovalRequest.isTemporary && !accessApprovalRequest.temporaryRange) {
-            if (accessApprovalRequest.groupMembershipId) {
-              // Group user privilege
-              const groupProjectUserAdditionalPrivilege = await groupAdditionalPrivilegeDAL.create(
-                {
-                  groupProjectMembershipId: accessApprovalRequest.groupMembershipId,
-                  requestedByUserId: accessApprovalRequest.requestedByUserId,
-                  slug: `requested-privilege-${slugify(alphaNumericNanoId(12))}`,
-                  permissions: JSON.stringify(accessApprovalRequest.permissions)
-                },
-                tx
-              );
-
-              groupProjectMembershipId = groupProjectUserAdditionalPrivilege.id;
-            } else {
-              // Project user privilege
-              const privilege = await additionalPrivilegeDAL.create(
-                {
-                  projectMembershipId: accessApprovalRequest.projectMembershipId!,
-                  slug: `requested-privilege-${slugify(alphaNumericNanoId(12))}`,
-                  permissions: JSON.stringify(accessApprovalRequest.permissions)
-                },
-                tx
-              );
-              projectUserPrivilegeId = privilege.id;
-            }
+            // Permanent access
+            const privilege = await additionalPrivilegeDAL.create(
+              {
+                projectMembershipId: accessApprovalRequest.requestedBy,
+                slug: `requested-privilege-${slugify(alphaNumericNanoId(12))}`,
+                permissions: JSON.stringify(accessApprovalRequest.permissions)
+              },
+              tx
+            );
+            privilegeId = privilege.id;
           } else {
             // Temporary access
             const relativeTempAllocatedTimeInMs = ms(accessApprovalRequest.temporaryRange!);
             const startTime = new Date();
 
-            if (accessApprovalRequest.groupMembershipId) {
-              //  Group user privilege
-              const groupProjectUserAdditionalPrivilege = await groupAdditionalPrivilegeDAL.create(
-                {
-                  groupProjectMembershipId: accessApprovalRequest.groupMembershipId,
-                  requestedByUserId: accessApprovalRequest.requestedByUserId,
-                  slug: `requested-privilege-${slugify(alphaNumericNanoId(12))}`,
-                  permissions: JSON.stringify(accessApprovalRequest.permissions),
-                  isTemporary: true,
-                  temporaryMode: ProjectUserAdditionalPrivilegeTemporaryMode.Relative,
-                  temporaryRange: accessApprovalRequest.temporaryRange!,
-                  temporaryAccessStartTime: startTime,
-                  temporaryAccessEndTime: new Date(new Date(startTime).getTime() + relativeTempAllocatedTimeInMs)
-                },
-                tx
-              );
-
-              groupProjectMembershipId = groupProjectUserAdditionalPrivilege.id;
-            } else {
-              const privilege = await additionalPrivilegeDAL.create(
-                {
-                  projectMembershipId: accessApprovalRequest.projectMembershipId!,
-                  slug: `requested-privilege-${slugify(alphaNumericNanoId(12))}`,
-                  permissions: JSON.stringify(accessApprovalRequest.permissions),
-                  isTemporary: true,
-                  temporaryMode: ProjectUserAdditionalPrivilegeTemporaryMode.Relative,
-                  temporaryRange: accessApprovalRequest.temporaryRange!,
-                  temporaryAccessStartTime: startTime,
-                  temporaryAccessEndTime: new Date(new Date(startTime).getTime() + relativeTempAllocatedTimeInMs)
-                },
-                tx
-              );
-              projectUserPrivilegeId = privilege.id;
-            }
-          }
-
-          if (projectUserPrivilegeId) {
-            await accessApprovalRequestDAL.updateById(accessApprovalRequest.id, { projectUserPrivilegeId }, tx);
-          } else if (groupProjectMembershipId) {
-            await accessApprovalRequestDAL.updateById(
-              accessApprovalRequest.id,
-              { groupProjectUserPrivilegeId: groupProjectMembershipId },
+            const privilege = await additionalPrivilegeDAL.create(
+              {
+                projectMembershipId: accessApprovalRequest.requestedBy,
+                slug: `requested-privilege-${slugify(alphaNumericNanoId(12))}`,
+                permissions: JSON.stringify(accessApprovalRequest.permissions),
+                isTemporary: true,
+                temporaryMode: ProjectUserAdditionalPrivilegeTemporaryMode.Relative,
+                temporaryRange: accessApprovalRequest.temporaryRange!,
+                temporaryAccessStartTime: startTime,
+                temporaryAccessEndTime: new Date(new Date(startTime).getTime() + relativeTempAllocatedTimeInMs)
+              },
               tx
             );
-          } else {
-            throw new BadRequestError({ message: "No privilege was created" });
+            privilegeId = privilege.id;
           }
+
+          await accessApprovalRequestDAL.updateById(accessApprovalRequest.id, { privilegeId }, tx);
         }
 
         return newReview;
@@ -496,7 +364,6 @@ export const accessApprovalRequestServiceFactory = ({
     createAccessApprovalRequest,
     listApprovalRequests,
     reviewAccessRequest,
-    deleteAccessApprovalRequest,
     getCount
   };
 };

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

@@ -28,11 +28,6 @@ export type TCreateAccessApprovalRequestDTO = {
 
 export type TListApprovalRequestsDTO = {
   projectSlug: string;
-  authorUserId?: string;
+  authorProjectMembershipId?: string;
   envSlug?: string;
 } & Omit<TProjectPermission, "projectId">;
-
-export type TDeleteApprovalRequestDTO = {
-  requestId: string;
-  projectSlug: string;
-} & Omit<TProjectPermission, "projectId">;

backend/src/ee/services/audit-log/audit-log-queue.ts

@@ -3,7 +3,6 @@ import { RawAxiosRequestHeaders } from "axios";
 import { SecretKeyEncoding } from "@app/db/schemas";
 import { request } from "@app/lib/config/request";
 import { infisicalSymmetricDecrypt } from "@app/lib/crypto/encryption";
-import { logger } from "@app/lib/logger";
 import { QueueJobs, QueueName, TQueueServiceFactory } from "@app/queue";
 import { TProjectDALFactory } from "@app/services/project/project-dal";
 
@@ -113,35 +112,7 @@ export const auditLogQueueServiceFactory = ({
     );
   });
 
-  queueService.start(QueueName.AuditLogPrune, async () => {
-    logger.info(`${QueueName.AuditLogPrune}: queue task started`);
-    await auditLogDAL.pruneAuditLog();
-    logger.info(`${QueueName.AuditLogPrune}: queue task completed`);
-  });
-
-  // we do a repeat cron job in utc timezone at 12 Midnight each day
-  const startAuditLogPruneJob = async () => {
-    // clear previous job
-    await queueService.stopRepeatableJob(
-      QueueName.AuditLogPrune,
-      QueueJobs.AuditLogPrune,
-      { pattern: "0 0 * * *", utc: true },
-      QueueName.AuditLogPrune // just a job id
-    );
-
-    await queueService.queue(QueueName.AuditLogPrune, QueueJobs.AuditLogPrune, undefined, {
-      delay: 5000,
-      jobId: QueueName.AuditLogPrune,
-      repeat: { pattern: "0 0 * * *", utc: true }
-    });
-  };
-
-  queueService.listen(QueueName.AuditLogPrune, "failed", (err) => {
-    logger.error(err?.failedReason, `${QueueName.AuditLogPrune}: log pruning failed`);
-  });
-
   return {
-    pushToLog,
-    startAuditLogPruneJob
+    pushToLog
   };
 };

backend/src/ee/services/audit-log/audit-log-types.ts

@@ -51,6 +51,7 @@ export enum EventType {
   UNAUTHORIZE_INTEGRATION = "unauthorize-integration",
   CREATE_INTEGRATION = "create-integration",
   DELETE_INTEGRATION = "delete-integration",
+  MANUAL_SYNC_INTEGRATION = "manual-sync-integration",
   ADD_TRUSTED_IP = "add-trusted-ip",
   UPDATE_TRUSTED_IP = "update-trusted-ip",
   DELETE_TRUSTED_IP = "delete-trusted-ip",
@@ -63,9 +64,25 @@ export enum EventType {
   ADD_IDENTITY_UNIVERSAL_AUTH = "add-identity-universal-auth",
   UPDATE_IDENTITY_UNIVERSAL_AUTH = "update-identity-universal-auth",
   GET_IDENTITY_UNIVERSAL_AUTH = "get-identity-universal-auth",
+  LOGIN_IDENTITY_KUBERNETES_AUTH = "login-identity-kubernetes-auth",
+  ADD_IDENTITY_KUBERNETES_AUTH = "add-identity-kubernetes-auth",
+  UPDATE_IDENTITY_KUBENETES_AUTH = "update-identity-kubernetes-auth",
+  GET_IDENTITY_KUBERNETES_AUTH = "get-identity-kubernetes-auth",
   CREATE_IDENTITY_UNIVERSAL_AUTH_CLIENT_SECRET = "create-identity-universal-auth-client-secret",
   REVOKE_IDENTITY_UNIVERSAL_AUTH_CLIENT_SECRET = "revoke-identity-universal-auth-client-secret",
   GET_IDENTITY_UNIVERSAL_AUTH_CLIENT_SECRETS = "get-identity-universal-auth-client-secret",
+  LOGIN_IDENTITY_GCP_AUTH = "login-identity-gcp-auth",
+  ADD_IDENTITY_GCP_AUTH = "add-identity-gcp-auth",
+  UPDATE_IDENTITY_GCP_AUTH = "update-identity-gcp-auth",
+  GET_IDENTITY_GCP_AUTH = "get-identity-gcp-auth",
+  LOGIN_IDENTITY_AWS_AUTH = "login-identity-aws-auth",
+  ADD_IDENTITY_AWS_AUTH = "add-identity-aws-auth",
+  UPDATE_IDENTITY_AWS_AUTH = "update-identity-aws-auth",
+  GET_IDENTITY_AWS_AUTH = "get-identity-aws-auth",
+  LOGIN_IDENTITY_AZURE_AUTH = "login-identity-azure-auth",
+  ADD_IDENTITY_AZURE_AUTH = "add-identity-azure-auth",
+  UPDATE_IDENTITY_AZURE_AUTH = "update-identity-azure-auth",
+  GET_IDENTITY_AZURE_AUTH = "get-identity-azure-auth",
   CREATE_ENVIRONMENT = "create-environment",
   UPDATE_ENVIRONMENT = "update-environment",
   DELETE_ENVIRONMENT = "delete-environment",
@@ -269,6 +286,25 @@ interface DeleteIntegrationEvent {
   };
 }
 
+interface ManualSyncIntegrationEvent {
+  type: EventType.MANUAL_SYNC_INTEGRATION;
+  metadata: {
+    integrationId: string;
+    integration: string;
+    environment: string;
+    secretPath: string;
+    url?: string;
+    app?: string;
+    appId?: string;
+    targetEnvironment?: string;
+    targetEnvironmentId?: string;
+    targetService?: string;
+    targetServiceId?: string;
+    path?: string;
+    region?: string;
+  };
+}
+
 interface AddTrustedIPEvent {
   type: EventType.ADD_TRUSTED_IP;
   metadata: {
@@ -383,6 +419,50 @@ interface GetIdentityUniversalAuthEvent {
   };
 }
 
+interface LoginIdentityKubernetesAuthEvent {
+  type: EventType.LOGIN_IDENTITY_KUBERNETES_AUTH;
+  metadata: {
+    identityId: string;
+    identityKubernetesAuthId: string;
+    identityAccessTokenId: string;
+  };
+}
+
+interface AddIdentityKubernetesAuthEvent {
+  type: EventType.ADD_IDENTITY_KUBERNETES_AUTH;
+  metadata: {
+    identityId: string;
+    kubernetesHost: string;
+    allowedNamespaces: string;
+    allowedNames: string;
+    accessTokenTTL: number;
+    accessTokenMaxTTL: number;
+    accessTokenNumUsesLimit: number;
+    accessTokenTrustedIps: Array<TIdentityTrustedIp>;
+  };
+}
+
+interface UpdateIdentityKubernetesAuthEvent {
+  type: EventType.UPDATE_IDENTITY_KUBENETES_AUTH;
+  metadata: {
+    identityId: string;
+    kubernetesHost?: string;
+    allowedNamespaces?: string;
+    allowedNames?: string;
+    accessTokenTTL?: number;
+    accessTokenMaxTTL?: number;
+    accessTokenNumUsesLimit?: number;
+    accessTokenTrustedIps?: Array<TIdentityTrustedIp>;
+  };
+}
+
+interface GetIdentityKubernetesAuthEvent {
+  type: EventType.GET_IDENTITY_KUBERNETES_AUTH;
+  metadata: {
+    identityId: string;
+  };
+}
+
 interface CreateIdentityUniversalAuthClientSecretEvent {
   type: EventType.CREATE_IDENTITY_UNIVERSAL_AUTH_CLIENT_SECRET;
   metadata: {
@@ -406,6 +486,138 @@ interface RevokeIdentityUniversalAuthClientSecretEvent {
   };
 }
 
+interface LoginIdentityGcpAuthEvent {
+  type: EventType.LOGIN_IDENTITY_GCP_AUTH;
+  metadata: {
+    identityId: string;
+    identityGcpAuthId: string;
+    identityAccessTokenId: string;
+  };
+}
+
+interface AddIdentityGcpAuthEvent {
+  type: EventType.ADD_IDENTITY_GCP_AUTH;
+  metadata: {
+    identityId: string;
+    type: string;
+    allowedServiceAccounts: string;
+    allowedProjects: string;
+    allowedZones: string;
+    accessTokenTTL: number;
+    accessTokenMaxTTL: number;
+    accessTokenNumUsesLimit: number;
+    accessTokenTrustedIps: Array<TIdentityTrustedIp>;
+  };
+}
+
+interface UpdateIdentityGcpAuthEvent {
+  type: EventType.UPDATE_IDENTITY_GCP_AUTH;
+  metadata: {
+    identityId: string;
+    type?: string;
+    allowedServiceAccounts?: string;
+    allowedProjects?: string;
+    allowedZones?: string;
+    accessTokenTTL?: number;
+    accessTokenMaxTTL?: number;
+    accessTokenNumUsesLimit?: number;
+    accessTokenTrustedIps?: Array<TIdentityTrustedIp>;
+  };
+}
+
+interface GetIdentityGcpAuthEvent {
+  type: EventType.GET_IDENTITY_GCP_AUTH;
+  metadata: {
+    identityId: string;
+  };
+}
+
+interface LoginIdentityAwsAuthEvent {
+  type: EventType.LOGIN_IDENTITY_AWS_AUTH;
+  metadata: {
+    identityId: string;
+    identityAwsAuthId: string;
+    identityAccessTokenId: string;
+  };
+}
+
+interface AddIdentityAwsAuthEvent {
+  type: EventType.ADD_IDENTITY_AWS_AUTH;
+  metadata: {
+    identityId: string;
+    stsEndpoint: string;
+    allowedPrincipalArns: string;
+    allowedAccountIds: string;
+    accessTokenTTL: number;
+    accessTokenMaxTTL: number;
+    accessTokenNumUsesLimit: number;
+    accessTokenTrustedIps: Array<TIdentityTrustedIp>;
+  };
+}
+
+interface UpdateIdentityAwsAuthEvent {
+  type: EventType.UPDATE_IDENTITY_AWS_AUTH;
+  metadata: {
+    identityId: string;
+    stsEndpoint?: string;
+    allowedPrincipalArns?: string;
+    allowedAccountIds?: string;
+    accessTokenTTL?: number;
+    accessTokenMaxTTL?: number;
+    accessTokenNumUsesLimit?: number;
+    accessTokenTrustedIps?: Array<TIdentityTrustedIp>;
+  };
+}
+
+interface GetIdentityAwsAuthEvent {
+  type: EventType.GET_IDENTITY_AWS_AUTH;
+  metadata: {
+    identityId: string;
+  };
+}
+
+interface LoginIdentityAzureAuthEvent {
+  type: EventType.LOGIN_IDENTITY_AZURE_AUTH;
+  metadata: {
+    identityId: string;
+    identityAzureAuthId: string;
+    identityAccessTokenId: string;
+  };
+}
+
+interface AddIdentityAzureAuthEvent {
+  type: EventType.ADD_IDENTITY_AZURE_AUTH;
+  metadata: {
+    identityId: string;
+    tenantId: string;
+    resource: string;
+    accessTokenTTL: number;
+    accessTokenMaxTTL: number;
+    accessTokenNumUsesLimit: number;
+    accessTokenTrustedIps: Array<TIdentityTrustedIp>;
+  };
+}
+
+interface UpdateIdentityAzureAuthEvent {
+  type: EventType.UPDATE_IDENTITY_AZURE_AUTH;
+  metadata: {
+    identityId: string;
+    tenantId?: string;
+    resource?: string;
+    accessTokenTTL?: number;
+    accessTokenMaxTTL?: number;
+    accessTokenNumUsesLimit?: number;
+    accessTokenTrustedIps?: Array<TIdentityTrustedIp>;
+  };
+}
+
+interface GetIdentityAzureAuthEvent {
+  type: EventType.GET_IDENTITY_AZURE_AUTH;
+  metadata: {
+    identityId: string;
+  };
+}
+
 interface CreateEnvironmentEvent {
   type: EventType.CREATE_ENVIRONMENT;
   metadata: {
@@ -625,9 +837,9 @@ interface SecretApprovalReopened {
 interface SecretApprovalRequest {
   type: EventType.SECRET_APPROVAL_REQUEST;
   metadata: {
+    committedBy: string;
     secretApprovalRequestSlug: string;
     secretApprovalRequestId: string;
-    committedByUser?: string | null; // Needs to be nullable for backward compatibility
   };
 }
 
@@ -645,6 +857,7 @@ export type Event =
   | UnauthorizeIntegrationEvent
   | CreateIntegrationEvent
   | DeleteIntegrationEvent
+  | ManualSyncIntegrationEvent
   | AddTrustedIPEvent
   | UpdateTrustedIPEvent
   | DeleteTrustedIPEvent
@@ -657,9 +870,25 @@ export type Event =
   | AddIdentityUniversalAuthEvent
   | UpdateIdentityUniversalAuthEvent
   | GetIdentityUniversalAuthEvent
+  | LoginIdentityKubernetesAuthEvent
+  | AddIdentityKubernetesAuthEvent
+  | UpdateIdentityKubernetesAuthEvent
+  | GetIdentityKubernetesAuthEvent
   | CreateIdentityUniversalAuthClientSecretEvent
   | GetIdentityUniversalAuthClientSecretsEvent
   | RevokeIdentityUniversalAuthClientSecretEvent
+  | LoginIdentityGcpAuthEvent
+  | AddIdentityGcpAuthEvent
+  | UpdateIdentityGcpAuthEvent
+  | GetIdentityGcpAuthEvent
+  | LoginIdentityAwsAuthEvent
+  | AddIdentityAwsAuthEvent
+  | UpdateIdentityAwsAuthEvent
+  | GetIdentityAwsAuthEvent
+  | LoginIdentityAzureAuthEvent
+  | AddIdentityAzureAuthEvent
+  | UpdateIdentityAzureAuthEvent
+  | GetIdentityAzureAuthEvent
   | CreateEnvironmentEvent
   | UpdateEnvironmentEvent
   | DeleteEnvironmentEvent

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

@@ -1,12 +0,0 @@
-import { TDbClient } from "@app/db";
-import { TableName } from "@app/db/schemas";
-import { ormify } from "@app/lib/knex";
-
-export type TGroupProjectUserAdditionalPrivilegeDALFactory = ReturnType<
-  typeof groupProjectUserAdditionalPrivilegeDALFactory
->;
-
-export const groupProjectUserAdditionalPrivilegeDALFactory = (db: TDbClient) => {
-  const orm = ormify(db, TableName.GroupProjectUserAdditionalPrivilege);
-  return orm;
-};

backend/src/ee/services/group/group-dal.ts

@@ -5,78 +5,10 @@ import { TableName, TGroups } from "@app/db/schemas";
 import { DatabaseError } from "@app/lib/errors";
 import { buildFindFilter, ormify, selectAllTableCols, TFindFilter, TFindOpt } from "@app/lib/knex";
 
-import { TUserGroupMembershipDALFactory } from "./user-group-membership-dal";
-
 export type TGroupDALFactory = ReturnType<typeof groupDALFactory>;
 
-export const groupDALFactory = (db: TDbClient, userGroupMembershipDAL: TUserGroupMembershipDALFactory) => {
+export const groupDALFactory = (db: TDbClient) => {
   const groupOrm = ormify(db, TableName.Groups);
-  const groupMembershipOrm = ormify(db, TableName.GroupProjectMembership);
-  const accessApprovalRequestOrm = ormify(db, TableName.AccessApprovalRequest);
-  const secretApprovalRequestOrm = ormify(db, TableName.SecretApprovalRequest);
-
-  const deleteMany = async (filterQuery: TFindFilter<TGroups>, tx?: Knex) => {
-    const transaction = tx || (await db.transaction());
-
-    // Find all memberships
-    const groups = await groupOrm.find(filterQuery, { tx: transaction });
-
-    for await (const group of groups) {
-      // Find all the group memberships of the groups (a group membership is which projects the group is a part of)
-      const groupProjectMemberships = await groupMembershipOrm.find(
-        { groupId: group.id },
-        {
-          tx: transaction
-        }
-      );
-
-      // For each of those group memberships, we need to find all the members of the group that don't have a regular membership in the project
-      for await (const groupMembership of groupProjectMemberships) {
-        const members = await userGroupMembershipDAL.findGroupMembersNotInProject(
-          group.id,
-          groupMembership.projectId,
-          transaction
-        );
-
-        // We then delete all the access approval requests and secret approval requests associated with these members
-        await accessApprovalRequestOrm.delete(
-          {
-            groupMembershipId: groupMembership.id,
-            $in: {
-              requestedByUserId: members.map(({ user }) => user.id)
-            }
-          },
-          transaction
-        );
-
-        const policies = await (tx || db)(TableName.SecretApprovalPolicy)
-          .join(TableName.Environment, `${TableName.SecretApprovalPolicy}.envId`, `${TableName.Environment}.id`)
-          .where(`${TableName.Environment}.projectId`, groupMembership.projectId)
-          .select(selectAllTableCols(TableName.SecretApprovalPolicy));
-
-        await secretApprovalRequestOrm.delete(
-          {
-            $in: {
-              policyId: policies.map(({ id }) => id),
-              committerUserId: members.map(({ user }) => user.id)
-            }
-          },
-          transaction
-        );
-      }
-    }
-
-    await groupOrm.delete(
-      {
-        $in: {
-          id: groups.map((group) => group.id)
-        }
-      },
-      transaction
-    );
-
-    return groups;
-  };
 
   const findGroups = async (filter: TFindFilter<TGroups>, { offset, limit, sort, tx }: TFindOpt<TGroups> = {}) => {
     try {
@@ -190,10 +122,9 @@ export const groupDALFactory = (db: TDbClient, userGroupMembershipDAL: TUserGrou
   };
 
   return {
-    ...groupOrm,
     findGroups,
     findByOrgId,
     findAllGroupMembers,
-    delete: deleteMany
+    ...groupOrm
   };
 };

backend/src/ee/services/group/group-fns.ts

@@ -1,6 +1,6 @@
 import { Knex } from "knex";
 
-import { SecretKeyEncoding, TUsers } from "@app/db/schemas";
+import { SecretKeyEncoding, TableName, TUsers } from "@app/db/schemas";
 import { decryptAsymmetric, encryptAsymmetric, infisicalSymmetricDecrypt } from "@app/lib/crypto/encryption";
 import { BadRequestError, ScimRequestError } from "@app/lib/errors";
 
@@ -188,9 +188,9 @@ export const addUsersToGroupByUserIds = async ({
     // check if all user(s) are part of the organization
     const existingUserOrgMemberships = await orgDAL.findMembership(
       {
-        orgId: group.orgId,
+        [`${TableName.OrgMembership}.orgId` as "orgId"]: group.orgId,
         $in: {
-          userId: userIds
+          [`${TableName.OrgMembership}.userId` as "userId"]: userIds
         }
       },
       { tx }
@@ -266,9 +266,6 @@ export const removeUsersFromGroupByUserIds = async ({
   userIds,
   userDAL,
   userGroupMembershipDAL,
-  accessApprovalRequestDAL,
-  secretApprovalRequestDAL,
-  secretApprovalPolicyDAL,
   groupProjectDAL,
   projectKeyDAL,
   tx: outerTx
@@ -325,15 +322,19 @@ export const removeUsersFromGroupByUserIds = async ({
     });
 
     if (membersToRemoveFromGroupNonPending.length) {
-      const groupProjectMemberships = await groupProjectDAL.find(
-        {
-          groupId: group.id
-        },
-        { tx }
-      );
-
       // check which projects the group is part of
-      const projectIds = Array.from(new Set(groupProjectMemberships.map((gp) => gp.projectId)));
+      const projectIds = Array.from(
+        new Set(
+          (
+            await groupProjectDAL.find(
+              {
+                groupId: group.id
+              },
+              { tx }
+            )
+          ).map((gp) => gp.projectId)
+        )
+      );
 
       // TODO: this part can be optimized
       for await (const userId of userIds) {
@@ -352,35 +353,10 @@ export const removeUsersFromGroupByUserIds = async ({
           );
         }
 
-        await accessApprovalRequestDAL.delete(
-          {
-            $in: {
-              groupMembershipId: groupProjectMemberships
-                .filter((gp) => projectsToDeleteKeyFor.includes(gp.projectId))
-                .map((gp) => gp.id)
-            },
-            requestedByUserId: userId
-          },
-          tx
-        );
-
-        const projectSecretApprovalPolicies = await secretApprovalPolicyDAL.findByProjectIds(projectIds);
-        await secretApprovalRequestDAL.delete(
-          {
-            committerUserId: userId,
-            $in: {
-              policyId: projectSecretApprovalPolicies.map((p) => p.id)
-            }
-          },
-          tx
-        );
-
         await userGroupMembershipDAL.delete(
           {
             groupId: group.id,
-            $in: {
-              userId: membersToRemoveFromGroupNonPending.map((member) => member.id)
-            }
+            userId
           },
           tx
         );
@@ -388,15 +364,12 @@ export const removeUsersFromGroupByUserIds = async ({
     }
 
     if (membersToRemoveFromGroupPending.length) {
-      await userGroupMembershipDAL.delete(
-        {
-          groupId: group.id,
-          $in: {
-            userId: membersToRemoveFromGroupPending.map((member) => member.id)
-          }
-        },
-        tx
-      );
+      await userGroupMembershipDAL.delete({
+        groupId: group.id,
+        $in: {
+          userId: membersToRemoveFromGroupPending.map((member) => member.id)
+        }
+      });
     }
 
     return membersToRemoveFromGroupNonPending.concat(membersToRemoveFromGroupPending);

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

@@ -12,12 +12,9 @@ import { TProjectBotDALFactory } from "@app/services/project-bot/project-bot-dal
 import { TProjectKeyDALFactory } from "@app/services/project-key/project-key-dal";
 import { TUserDALFactory } from "@app/services/user/user-dal";
 
-import { TAccessApprovalRequestDALFactory } from "../access-approval-request/access-approval-request-dal";
 import { TLicenseServiceFactory } from "../license/license-service";
 import { OrgPermissionActions, OrgPermissionSubjects } from "../permission/org-permission";
 import { TPermissionServiceFactory } from "../permission/permission-service";
-import { TSecretApprovalPolicyDALFactory } from "../secret-approval-policy/secret-approval-policy-dal";
-import { TSecretApprovalRequestDALFactory } from "../secret-approval-request/secret-approval-request-dal";
 import { TGroupDALFactory } from "./group-dal";
 import { addUsersToGroupByUserIds, removeUsersFromGroupByUserIds } from "./group-fns";
 import {
@@ -44,9 +41,6 @@ type TGroupServiceFactoryDep = {
   projectKeyDAL: Pick<TProjectKeyDALFactory, "find" | "delete" | "findLatestProjectKey" | "insertMany">;
   permissionService: Pick<TPermissionServiceFactory, "getOrgPermission" | "getOrgPermissionByRole">;
   licenseService: Pick<TLicenseServiceFactory, "getPlan">;
-  secretApprovalRequestDAL: Pick<TSecretApprovalRequestDALFactory, "delete">;
-  accessApprovalRequestDAL: Pick<TAccessApprovalRequestDALFactory, "delete">;
-  secretApprovalPolicyDAL: Pick<TSecretApprovalPolicyDALFactory, "findByProjectIds">;
 };
 
 export type TGroupServiceFactory = ReturnType<typeof groupServiceFactory>;
@@ -56,9 +50,6 @@ export const groupServiceFactory = ({
   groupDAL,
   groupProjectDAL,
   orgDAL,
-  secretApprovalRequestDAL,
-  secretApprovalPolicyDAL,
-  accessApprovalRequestDAL,
   userGroupMembershipDAL,
   projectDAL,
   projectBotDAL,
@@ -337,9 +328,6 @@ export const groupServiceFactory = ({
       group,
       userIds: [user.id],
       userDAL,
-      accessApprovalRequestDAL,
-      secretApprovalPolicyDAL,
-      secretApprovalRequestDAL,
       userGroupMembershipDAL,
       groupProjectDAL,
       projectKeyDAL

backend/src/ee/services/group/group-types.ts

@@ -10,10 +10,6 @@ import { TProjectBotDALFactory } from "@app/services/project-bot/project-bot-dal
 import { TProjectKeyDALFactory } from "@app/services/project-key/project-key-dal";
 import { TUserDALFactory } from "@app/services/user/user-dal";
 
-import { TAccessApprovalRequestDALFactory } from "../access-approval-request/access-approval-request-dal";
-import { TSecretApprovalPolicyDALFactory } from "../secret-approval-policy/secret-approval-policy-dal";
-import { TSecretApprovalRequestDALFactory } from "../secret-approval-request/secret-approval-request-dal";
-
 export type TCreateGroupDTO = {
   name: string;
   slug?: string;
@@ -81,9 +77,6 @@ export type TRemoveUsersFromGroupByUserIds = {
   group: TGroups;
   userIds: string[];
   userDAL: Pick<TUserDALFactory, "find" | "transaction">;
-  accessApprovalRequestDAL: Pick<TAccessApprovalRequestDALFactory, "delete">;
-  secretApprovalRequestDAL: Pick<TSecretApprovalRequestDALFactory, "delete">;
-  secretApprovalPolicyDAL: Pick<TSecretApprovalPolicyDALFactory, "findByProjectIds">;
   userGroupMembershipDAL: Pick<TUserGroupMembershipDALFactory, "find" | "filterProjectsByUserMembership" | "delete">;
   groupProjectDAL: Pick<TGroupProjectDALFactory, "find">;
   projectKeyDAL: Pick<TProjectKeyDALFactory, "delete">;

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

@@ -1,5 +1,7 @@
-import { ForbiddenError } from "@casl/ability";
+import { ForbiddenError, MongoAbility, RawRuleOf } from "@casl/ability";
+import { PackRule, unpackRules } from "@casl/ability/extra";
 import ms from "ms";
+import { z } from "zod";
 
 import { isAtLeastAsPrivileged } from "@app/lib/casl";
 import { BadRequestError, ForbiddenRequestError } from "@app/lib/errors";
@@ -8,7 +10,7 @@ import { TIdentityProjectDALFactory } from "@app/services/identity-project/ident
 import { TProjectDALFactory } from "@app/services/project/project-dal";
 
 import { TPermissionServiceFactory } from "../permission/permission-service";
-import { ProjectPermissionActions, ProjectPermissionSub } from "../permission/project-permission";
+import { ProjectPermissionActions, ProjectPermissionSet, ProjectPermissionSub } from "../permission/project-permission";
 import { TIdentityProjectAdditionalPrivilegeDALFactory } from "./identity-project-additional-privilege-dal";
 import {
   IdentityProjectAdditionalPrivilegeTemporaryMode,
@@ -30,6 +32,27 @@ export type TIdentityProjectAdditionalPrivilegeServiceFactory = ReturnType<
   typeof identityProjectAdditionalPrivilegeServiceFactory
 >;
 
+// TODO(akhilmhdh): move this to more centralized
+export const UnpackedPermissionSchema = z.object({
+  subject: z.union([z.string().min(1), z.string().array()]).optional(),
+  action: z.union([z.string().min(1), z.string().array()]),
+  conditions: z
+    .object({
+      environment: z.string().optional(),
+      secretPath: z
+        .object({
+          $glob: z.string().min(1)
+        })
+        .optional()
+    })
+    .optional()
+});
+
+const unpackPermissions = (permissions: unknown) =>
+  UnpackedPermissionSchema.array().parse(
+    unpackRules((permissions || []) as PackRule<RawRuleOf<MongoAbility<ProjectPermissionSet>>>[])
+  );
+
 export const identityProjectAdditionalPrivilegeServiceFactory = ({
   identityProjectAdditionalPrivilegeDAL,
   identityProjectDAL,
@@ -86,7 +109,10 @@ export const identityProjectAdditionalPrivilegeServiceFactory = ({
         slug,
         permissions: customPermission
       });
-      return additionalPrivilege;
+      return {
+        ...additionalPrivilege,
+        permissions: unpackPermissions(additionalPrivilege.permissions)
+      };
     }
 
     const relativeTempAllocatedTimeInMs = ms(dto.temporaryRange);
@@ -100,7 +126,10 @@ export const identityProjectAdditionalPrivilegeServiceFactory = ({
       temporaryAccessStartTime: new Date(dto.temporaryAccessStartTime),
       temporaryAccessEndTime: new Date(new Date(dto.temporaryAccessStartTime).getTime() + relativeTempAllocatedTimeInMs)
     });
-    return additionalPrivilege;
+    return {
+      ...additionalPrivilege,
+      permissions: unpackPermissions(additionalPrivilege.permissions)
+    };
   };
 
   const updateBySlug = async ({
@@ -163,7 +192,11 @@ export const identityProjectAdditionalPrivilegeServiceFactory = ({
         temporaryAccessStartTime: new Date(temporaryAccessStartTime || ""),
         temporaryAccessEndTime: new Date(new Date(temporaryAccessStartTime || "").getTime() + ms(temporaryRange || ""))
       });
-      return additionalPrivilege;
+      return {
+        ...additionalPrivilege,
+
+        permissions: unpackPermissions(additionalPrivilege.permissions)
+      };
     }
 
     const additionalPrivilege = await identityProjectAdditionalPrivilegeDAL.updateById(identityPrivilege.id, {
@@ -174,7 +207,11 @@ export const identityProjectAdditionalPrivilegeServiceFactory = ({
       temporaryRange: null,
       temporaryMode: null
     });
-    return additionalPrivilege;
+    return {
+      ...additionalPrivilege,
+
+      permissions: unpackPermissions(additionalPrivilege.permissions)
+    };
   };
 
   const deleteBySlug = async ({
@@ -220,7 +257,11 @@ export const identityProjectAdditionalPrivilegeServiceFactory = ({
     if (!identityPrivilege) throw new BadRequestError({ message: "Identity additional privilege not found" });
 
     const deletedPrivilege = await identityProjectAdditionalPrivilegeDAL.deleteById(identityPrivilege.id);
-    return deletedPrivilege;
+    return {
+      ...deletedPrivilege,
+
+      permissions: unpackPermissions(deletedPrivilege.permissions)
+    };
   };
 
   const getPrivilegeDetailsBySlug = async ({
@@ -254,7 +295,10 @@ export const identityProjectAdditionalPrivilegeServiceFactory = ({
     });
     if (!identityPrivilege) throw new BadRequestError({ message: "Identity additional privilege not found" });
 
-    return identityPrivilege;
+    return {
+      ...identityPrivilege,
+      permissions: unpackPermissions(identityPrivilege.permissions)
+    };
   };
 
   const listIdentityProjectPrivileges = async ({
@@ -284,7 +328,11 @@ export const identityProjectAdditionalPrivilegeServiceFactory = ({
     const identityPrivileges = await identityProjectAdditionalPrivilegeDAL.find({
       projectMembershipId: identityProjectMembership.id
     });
-    return identityPrivileges;
+    return identityPrivileges.map((el) => ({
+      ...el,
+
+      permissions: unpackPermissions(el.permissions)
+    }));
   };
 
   return {

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

@@ -1,7 +1,14 @@
 import { ForbiddenError } from "@casl/ability";
 import jwt from "jsonwebtoken";
 
-import { OrgMembershipRole, OrgMembershipStatus, SecretKeyEncoding, TLdapConfigsUpdate } from "@app/db/schemas";
+import {
+  OrgMembershipRole,
+  OrgMembershipStatus,
+  SecretKeyEncoding,
+  TableName,
+  TLdapConfigsUpdate,
+  TUsers
+} from "@app/db/schemas";
 import { TGroupDALFactory } from "@app/ee/services/group/group-dal";
 import { addUsersToGroupByUserIds, removeUsersFromGroupByUserIds } from "@app/ee/services/group/group-fns";
 import { TUserGroupMembershipDALFactory } from "@app/ee/services/group/user-group-membership-dal";
@@ -19,19 +26,19 @@ import { AuthMethod, AuthTokenType } from "@app/services/auth/auth-type";
 import { TGroupProjectDALFactory } from "@app/services/group-project/group-project-dal";
 import { TOrgBotDALFactory } from "@app/services/org/org-bot-dal";
 import { TOrgDALFactory } from "@app/services/org/org-dal";
+import { TOrgMembershipDALFactory } from "@app/services/org-membership/org-membership-dal";
 import { TProjectDALFactory } from "@app/services/project/project-dal";
 import { TProjectBotDALFactory } from "@app/services/project-bot/project-bot-dal";
 import { TProjectKeyDALFactory } from "@app/services/project-key/project-key-dal";
+import { getServerCfg } from "@app/services/super-admin/super-admin-service";
 import { TUserDALFactory } from "@app/services/user/user-dal";
 import { normalizeUsername } from "@app/services/user/user-fns";
 import { TUserAliasDALFactory } from "@app/services/user-alias/user-alias-dal";
+import { UserAliasType } from "@app/services/user-alias/user-alias-types";
 
-import { TAccessApprovalRequestDALFactory } from "../access-approval-request/access-approval-request-dal";
 import { TLicenseServiceFactory } from "../license/license-service";
 import { OrgPermissionActions, OrgPermissionSubjects } from "../permission/org-permission";
 import { TPermissionServiceFactory } from "../permission/permission-service";
-import { TSecretApprovalPolicyDALFactory } from "../secret-approval-policy/secret-approval-policy-dal";
-import { TSecretApprovalRequestDALFactory } from "../secret-approval-request/secret-approval-request-dal";
 import { TLdapConfigDALFactory } from "./ldap-config-dal";
 import {
   TCreateLdapCfgDTO,
@@ -49,6 +56,7 @@ import { TLdapGroupMapDALFactory } from "./ldap-group-map-dal";
 type TLdapConfigServiceFactoryDep = {
   ldapConfigDAL: Pick<TLdapConfigDALFactory, "create" | "update" | "findOne">;
   ldapGroupMapDAL: Pick<TLdapGroupMapDALFactory, "find" | "create" | "delete" | "findLdapGroupMapsByLdapConfigId">;
+  orgMembershipDAL: Pick<TOrgMembershipDALFactory, "create">;
   orgDAL: Pick<
     TOrgDALFactory,
     "createMembership" | "updateMembershipById" | "findMembership" | "findOrgById" | "findOne" | "updateById"
@@ -70,9 +78,6 @@ type TLdapConfigServiceFactoryDep = {
   userAliasDAL: Pick<TUserAliasDALFactory, "create" | "findOne">;
   permissionService: Pick<TPermissionServiceFactory, "getOrgPermission">;
   licenseService: Pick<TLicenseServiceFactory, "getPlan">;
-  accessApprovalRequestDAL: Pick<TAccessApprovalRequestDALFactory, "delete">;
-  secretApprovalRequestDAL: Pick<TSecretApprovalRequestDALFactory, "delete">;
-  secretApprovalPolicyDAL: Pick<TSecretApprovalPolicyDALFactory, "findByProjectIds">;
 };
 
 export type TLdapConfigServiceFactory = ReturnType<typeof ldapConfigServiceFactory>;
@@ -81,12 +86,10 @@ export const ldapConfigServiceFactory = ({
   ldapConfigDAL,
   ldapGroupMapDAL,
   orgDAL,
+  orgMembershipDAL,
   orgBotDAL,
   groupDAL,
   groupProjectDAL,
-  accessApprovalRequestDAL,
-  secretApprovalPolicyDAL,
-  secretApprovalRequestDAL,
   projectKeyDAL,
   projectDAL,
   projectBotDAL,
@@ -388,16 +391,17 @@ export const ldapConfigServiceFactory = ({
     username,
     firstName,
     lastName,
-    emails,
+    email,
     groups,
     orgId,
     relayState
   }: TLdapLoginDTO) => {
     const appCfg = getConfig();
+    const serverCfg = await getServerCfg();
     let userAlias = await userAliasDAL.findOne({
       externalId,
       orgId,
-      aliasType: AuthMethod.LDAP
+      aliasType: UserAliasType.LDAP
     });
 
     const organization = await orgDAL.findOrgById(orgId);
@@ -405,7 +409,13 @@ export const ldapConfigServiceFactory = ({
 
     if (userAlias) {
       await userDAL.transaction(async (tx) => {
-        const [orgMembership] = await orgDAL.findMembership({ userId: userAlias.userId }, { tx });
+        const [orgMembership] = await orgDAL.findMembership(
+          {
+            [`${TableName.OrgMembership}.userId` as "userId"]: userAlias.userId,
+            [`${TableName.OrgMembership}.orgId` as "id"]: orgId
+          },
+          { tx }
+        );
         if (!orgMembership) {
           await orgDAL.createMembership(
             {
@@ -428,40 +438,75 @@ export const ldapConfigServiceFactory = ({
       });
     } else {
       userAlias = await userDAL.transaction(async (tx) => {
-        const uniqueUsername = await normalizeUsername(username, userDAL);
-        const newUser = await userDAL.create(
-          {
-            username: uniqueUsername,
-            email: emails[0],
-            firstName,
-            lastName,
-            authMethods: [AuthMethod.LDAP],
-            isGhost: false
-          },
-          tx
-        );
+        let newUser: TUsers | undefined;
+        if (serverCfg.trustSamlEmails) {
+          newUser = await userDAL.findOne(
+            {
+              email,
+              isEmailVerified: true
+            },
+            tx
+          );
+        }
+
+        if (!newUser) {
+          const uniqueUsername = await normalizeUsername(username, userDAL);
+          newUser = await userDAL.create(
+            {
+              username: serverCfg.trustLdapEmails ? email : uniqueUsername,
+              email,
+              isEmailVerified: serverCfg.trustLdapEmails,
+              firstName,
+              lastName,
+              authMethods: [],
+              isGhost: false
+            },
+            tx
+          );
+        }
+
         const newUserAlias = await userAliasDAL.create(
           {
             userId: newUser.id,
             username,
-            aliasType: AuthMethod.LDAP,
+            aliasType: UserAliasType.LDAP,
             externalId,
-            emails,
+            emails: [email],
             orgId
           },
           tx
         );
 
-        await orgDAL.createMembership(
+        const [orgMembership] = await orgDAL.findMembership(
           {
-            userId: newUser.id,
-            orgId,
-            role: OrgMembershipRole.Member,
-            status: OrgMembershipStatus.Invited
+            [`${TableName.OrgMembership}.userId` as "userId"]: newUser.id,
+            [`${TableName.OrgMembership}.orgId` as "id"]: orgId
           },
-          tx
+          { tx }
         );
 
+        if (!orgMembership) {
+          await orgMembershipDAL.create(
+            {
+              userId: userAlias.userId,
+              inviteEmail: email,
+              orgId,
+              role: OrgMembershipRole.Member,
+              status: newUser.isAccepted ? OrgMembershipStatus.Accepted : OrgMembershipStatus.Invited // if user is fully completed, then set status to accepted, otherwise set it to invited so we can update it later
+            },
+            tx
+          );
+          // Only update the membership to Accepted if the user account is already completed.
+        } else if (orgMembership.status === OrgMembershipStatus.Invited && newUser.isAccepted) {
+          await orgDAL.updateMembershipById(
+            orgMembership.id,
+            {
+              status: OrgMembershipStatus.Accepted
+            },
+            tx
+          );
+        }
+
         return newUserAlias;
       });
     }
@@ -533,10 +578,7 @@ export const ldapConfigServiceFactory = ({
               group,
               userIds: [newUser.id],
               userDAL,
-              secretApprovalRequestDAL,
-              accessApprovalRequestDAL,
               userGroupMembershipDAL,
-              secretApprovalPolicyDAL,
               groupProjectDAL,
               projectKeyDAL,
               tx
@@ -555,11 +597,14 @@ export const ldapConfigServiceFactory = ({
         authTokenType: AuthTokenType.PROVIDER_TOKEN,
         userId: user.id,
         username: user.username,
+        ...(user.email && { email: user.email, isEmailVerified: user.isEmailVerified }),
         firstName,
         lastName,
         organizationName: organization.name,
         organizationId: organization.id,
+        organizationSlug: organization.slug,
         authMethod: AuthMethod.LDAP,
+        authType: UserAliasType.LDAP,
         isUserCompleted,
         ...(relayState
           ? {

backend/src/ee/services/ldap-config/ldap-config-types.ts

@@ -51,7 +51,7 @@ export type TLdapLoginDTO = {
   username: string;
   firstName: string;
   lastName: string;
-  emails: string[];
+  email: string;
   orgId: string;
   groups?: {
     dn: string;

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

@@ -16,6 +16,8 @@ export const licenseDALFactory = (db: TDbClient) => {
             void bd.where({ orgId });
           }
         })
+        .join(TableName.Users, `${TableName.OrgMembership}.userId`, `${TableName.Users}.id`)
+        .where(`${TableName.Users}.isGhost`, false)
         .count();
       return doc?.[0].count;
     } catch (error) {

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

@@ -62,11 +62,6 @@ export const permissionDALFactory = (db: TDbClient) => {
           `${TableName.GroupProjectMembershipRole}.projectMembershipId`,
           `${TableName.GroupProjectMembership}.id`
         )
-        .leftJoin(
-          TableName.GroupProjectUserAdditionalPrivilege,
-          `${TableName.GroupProjectUserAdditionalPrivilege}.groupProjectMembershipId`,
-          `${TableName.GroupProjectMembership}.id`
-        )
         .leftJoin(
           TableName.ProjectRoles,
           `${TableName.GroupProjectMembershipRole}.customRoleId`,
@@ -82,34 +77,11 @@ export const permissionDALFactory = (db: TDbClient) => {
           db.ref("projectId").withSchema(TableName.GroupProjectMembership),
           db.ref("authEnforced").withSchema(TableName.Organization).as("orgAuthEnforced"),
           db.ref("orgId").withSchema(TableName.Project),
-          db.ref("slug").withSchema(TableName.ProjectRoles).as("customRoleSlug"),
-          db.ref("permissions").withSchema(TableName.ProjectRoles)
+          db.ref("slug").withSchema(TableName.ProjectRoles).as("customRoleSlug")
         )
-        .where(`${TableName.GroupProjectMembership}.projectId`, projectId)
-        .select(
-          db.ref("projectId").withSchema(TableName.GroupProjectMembership).as("groupMembershipProjectId"),
-          db.ref("id").withSchema(TableName.GroupProjectUserAdditionalPrivilege).as("userApId"),
-          db.ref("permissions").withSchema(TableName.GroupProjectUserAdditionalPrivilege).as("userApPermissions"),
-          db.ref("temporaryMode").withSchema(TableName.GroupProjectUserAdditionalPrivilege).as("userApTemporaryMode"),
-          db.ref("isTemporary").withSchema(TableName.GroupProjectUserAdditionalPrivilege).as("userApIsTemporary"),
-          db.ref("temporaryRange").withSchema(TableName.GroupProjectUserAdditionalPrivilege).as("userApTemporaryRange"),
-          db.ref("groupProjectMembershipId").withSchema(TableName.GroupProjectUserAdditionalPrivilege),
-          db
-            .ref("requestedByUserId")
-            .withSchema(TableName.GroupProjectUserAdditionalPrivilege)
-            .as("userApRequestedByUserId"),
+        .select("permissions");
 
-          db
-            .ref("temporaryAccessStartTime")
-            .withSchema(TableName.GroupProjectUserAdditionalPrivilege)
-            .as("userApTemporaryAccessStartTime"),
-          db
-            .ref("temporaryAccessEndTime")
-            .withSchema(TableName.GroupProjectUserAdditionalPrivilege)
-            .as("userApTemporaryAccessEndTime")
-        );
-
-      const projectMemberDocs = await db(TableName.ProjectMembership)
+      const docs = await db(TableName.ProjectMembership)
         .join(
           TableName.ProjectUserMembershipRole,
           `${TableName.ProjectUserMembershipRole}.projectMembershipId`,
@@ -155,7 +127,7 @@ export const permissionDALFactory = (db: TDbClient) => {
         );
 
       const permission = sqlNestRelationships({
-        data: projectMemberDocs,
+        data: docs,
         key: "projectId",
         parentMapper: ({ orgId, orgAuthEnforced, membershipId, membershipCreatedAt, membershipUpdatedAt }) => ({
           orgId,
@@ -222,33 +194,6 @@ export const permissionDALFactory = (db: TDbClient) => {
                     permissions: z.unknown(),
                     customRoleSlug: z.string().optional().nullable()
                   }).parse(data)
-              },
-              {
-                key: "userApId",
-                label: "additionalPrivileges" as const,
-                mapper: ({
-                  groupMembershipProjectId,
-                  groupProjectMembershipId,
-                  userApId,
-                  userApPermissions,
-                  userApRequestedByUserId,
-                  userApIsTemporary,
-                  userApTemporaryMode,
-                  userApTemporaryRange,
-                  userApTemporaryAccessEndTime,
-                  userApTemporaryAccessStartTime
-                }) => ({
-                  groupProjectMembershipId,
-                  groupMembershipProjectId,
-                  id: userApId,
-                  userId: userApRequestedByUserId,
-                  permissions: userApPermissions,
-                  temporaryRange: userApTemporaryRange,
-                  temporaryMode: userApTemporaryMode,
-                  temporaryAccessEndTime: userApTemporaryAccessEndTime,
-                  temporaryAccessStartTime: userApTemporaryAccessStartTime,
-                  isTemporary: userApIsTemporary
-                })
               }
             ]
           })
@@ -269,24 +214,15 @@ export const permissionDALFactory = (db: TDbClient) => {
             !isTemporary || (isTemporary && temporaryAccessEndTime && new Date() < temporaryAccessEndTime)
         ) ?? [];
 
-      const activeAdditionalPrivileges =
-        permission?.[0]?.additionalPrivileges?.filter(
-          ({ isTemporary, temporaryAccessEndTime }) =>
-            !isTemporary || (isTemporary && temporaryAccessEndTime && new Date() < temporaryAccessEndTime)
-        ) ?? [];
-      const activeGroupAdditionalPrivileges =
-        groupPermission?.[0]?.additionalPrivileges?.filter(
-          ({ isTemporary, temporaryAccessEndTime, groupProjectMembershipId, groupMembershipProjectId, userId: user }) =>
-            groupMembershipProjectId === projectId &&
-            !!groupProjectMembershipId &&
-            user === userId &&
-            (!isTemporary || (isTemporary && temporaryAccessEndTime && new Date() < temporaryAccessEndTime))
-        ) ?? [];
+      const activeAdditionalPrivileges = permission?.[0]?.additionalPrivileges?.filter(
+        ({ isTemporary, temporaryAccessEndTime }) =>
+          !isTemporary || (isTemporary && temporaryAccessEndTime && new Date() < temporaryAccessEndTime)
+      );
 
       return {
         ...(permission[0] || groupPermission[0]),
         roles: [...activeRoles, ...activeGroupRoles],
-        additionalPrivileges: [...activeAdditionalPrivileges, ...activeGroupAdditionalPrivileges]
+        additionalPrivileges: activeAdditionalPrivileges
       };
     } catch (error) {
       throw new DatabaseError({ error, name: "GetProjectPermission" });

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

@@ -90,10 +90,6 @@ export const projectUserAdditionalPrivilegeServiceFactory = ({
     const userPrivilege = await projectUserAdditionalPrivilegeDAL.findById(privilegeId);
     if (!userPrivilege) throw new BadRequestError({ message: "User additional privilege not found" });
 
-    // This is fine. This service is only used for direct user privileges, not group-based privileges
-    if (!userPrivilege.projectMembershipId)
-      throw new BadRequestError({ message: "Operation not supported for groups" });
-
     const projectMembership = await projectMembershipDAL.findById(userPrivilege.projectMembershipId);
     if (!projectMembership) throw new BadRequestError({ message: "Project membership not found" });
 
@@ -142,10 +138,6 @@ export const projectUserAdditionalPrivilegeServiceFactory = ({
     const userPrivilege = await projectUserAdditionalPrivilegeDAL.findById(privilegeId);
     if (!userPrivilege) throw new BadRequestError({ message: "User additional privilege not found" });
 
-    // This is fine. This service is only used for direct user privileges, not group-based privileges
-    if (!userPrivilege.projectMembershipId)
-      throw new BadRequestError({ message: "Operation not supported for groups" });
-
     const projectMembership = await projectMembershipDAL.findById(userPrivilege.projectMembershipId);
     if (!projectMembership) throw new BadRequestError({ message: "Project membership not found" });
 
@@ -172,10 +164,6 @@ export const projectUserAdditionalPrivilegeServiceFactory = ({
     const userPrivilege = await projectUserAdditionalPrivilegeDAL.findById(privilegeId);
     if (!userPrivilege) throw new BadRequestError({ message: "User additional privilege not found" });
 
-    // This is fine. This service is only used for direct user privileges, not group-based privileges
-    if (!userPrivilege.projectMembershipId)
-      throw new BadRequestError({ message: "Operation not supported for groups" });
-
     const projectMembership = await projectMembershipDAL.findById(userPrivilege.projectMembershipId);
     if (!projectMembership) throw new BadRequestError({ message: "Project membership not found" });
 

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

@@ -7,7 +7,8 @@ import {
   SecretKeyEncoding,
   TableName,
   TSamlConfigs,
-  TSamlConfigsUpdate
+  TSamlConfigsUpdate,
+  TUsers
 } from "@app/db/schemas";
 import { getConfig } from "@app/lib/config/env";
 import {
@@ -19,10 +20,18 @@ import {
   infisicalSymmetricEncypt
 } from "@app/lib/crypto/encryption";
 import { BadRequestError } from "@app/lib/errors";
-import { AuthMethod, AuthTokenType } from "@app/services/auth/auth-type";
+import { AuthTokenType } from "@app/services/auth/auth-type";
+import { TAuthTokenServiceFactory } from "@app/services/auth-token/auth-token-service";
+import { TokenType } from "@app/services/auth-token/auth-token-types";
 import { TOrgBotDALFactory } from "@app/services/org/org-bot-dal";
 import { TOrgDALFactory } from "@app/services/org/org-dal";
+import { TOrgMembershipDALFactory } from "@app/services/org-membership/org-membership-dal";
+import { SmtpTemplates, TSmtpService } from "@app/services/smtp/smtp-service";
+import { getServerCfg } from "@app/services/super-admin/super-admin-service";
 import { TUserDALFactory } from "@app/services/user/user-dal";
+import { normalizeUsername } from "@app/services/user/user-fns";
+import { TUserAliasDALFactory } from "@app/services/user-alias/user-alias-dal";
+import { UserAliasType } from "@app/services/user-alias/user-alias-types";
 
 import { TLicenseServiceFactory } from "../license/license-service";
 import { OrgPermissionActions, OrgPermissionSubjects } from "../permission/org-permission";
@@ -31,15 +40,19 @@ import { TSamlConfigDALFactory } from "./saml-config-dal";
 import { TCreateSamlCfgDTO, TGetSamlCfgDTO, TSamlLoginDTO, TUpdateSamlCfgDTO } from "./saml-config-types";
 
 type TSamlConfigServiceFactoryDep = {
-  samlConfigDAL: TSamlConfigDALFactory;
-  userDAL: Pick<TUserDALFactory, "create" | "findOne" | "transaction" | "updateById">;
+  samlConfigDAL: Pick<TSamlConfigDALFactory, "create" | "findOne" | "update" | "findById">;
+  userDAL: Pick<TUserDALFactory, "create" | "findOne" | "transaction" | "updateById" | "findById">;
+  userAliasDAL: Pick<TUserAliasDALFactory, "create" | "findOne">;
   orgDAL: Pick<
     TOrgDALFactory,
     "createMembership" | "updateMembershipById" | "findMembership" | "findOrgById" | "findOne" | "updateById"
   >;
+  orgMembershipDAL: Pick<TOrgMembershipDALFactory, "create">;
   orgBotDAL: Pick<TOrgBotDALFactory, "findOne" | "create" | "transaction">;
   permissionService: Pick<TPermissionServiceFactory, "getOrgPermission">;
   licenseService: Pick<TLicenseServiceFactory, "getPlan">;
+  tokenService: Pick<TAuthTokenServiceFactory, "createTokenForUser">;
+  smtpService: Pick<TSmtpService, "sendMail">;
 };
 
 export type TSamlConfigServiceFactory = ReturnType<typeof samlConfigServiceFactory>;
@@ -48,9 +61,13 @@ export const samlConfigServiceFactory = ({
   samlConfigDAL,
   orgBotDAL,
   orgDAL,
+  orgMembershipDAL,
   userDAL,
+  userAliasDAL,
   permissionService,
-  licenseService
+  licenseService,
+  tokenService,
+  smtpService
 }: TSamlConfigServiceFactoryDep) => {
   const createSamlCfg = async ({
     cert,
@@ -305,7 +322,7 @@ export const samlConfigServiceFactory = ({
   };
 
   const samlLogin = async ({
-    username,
+    externalId,
     email,
     firstName,
     lastName,
@@ -314,38 +331,40 @@ export const samlConfigServiceFactory = ({
     relayState
   }: TSamlLoginDTO) => {
     const appCfg = getConfig();
-    let user = await userDAL.findOne({ username });
+    const serverCfg = await getServerCfg();
+    const userAlias = await userAliasDAL.findOne({
+      externalId,
+      orgId,
+      aliasType: UserAliasType.SAML
+    });
 
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) throw new BadRequestError({ message: "Org not found" });
 
-    // TODO(dangtony98): remove this after aliases update
-    if (authProvider === AuthMethod.KEYCLOAK_SAML && appCfg.LICENSE_SERVER_KEY) {
-      throw new BadRequestError({ message: "Keycloak SAML is not yet available on Infisical Cloud" });
-    }
-
-    if (user) {
-      await userDAL.transaction(async (tx) => {
+    let user: TUsers;
+    if (userAlias) {
+      user = await userDAL.transaction(async (tx) => {
+        const foundUser = await userDAL.findById(userAlias.userId, tx);
         const [orgMembership] = await orgDAL.findMembership(
           {
-            userId: user.id,
+            [`${TableName.OrgMembership}.userId` as "userId"]: foundUser.id,
             [`${TableName.OrgMembership}.orgId` as "id"]: orgId
           },
           { tx }
         );
         if (!orgMembership) {
-          await orgDAL.createMembership(
+          await orgMembershipDAL.create(
             {
-              userId: user.id,
-              orgId,
+              userId: userAlias.userId,
               inviteEmail: email,
+              orgId,
               role: OrgMembershipRole.Member,
-              status: user.isAccepted ? OrgMembershipStatus.Accepted : OrgMembershipStatus.Invited // if user is fully completed, then set status to accepted, otherwise set it to invited so we can update it later
+              status: foundUser.isAccepted ? OrgMembershipStatus.Accepted : OrgMembershipStatus.Invited // if user is fully completed, then set status to accepted, otherwise set it to invited so we can update it later
             },
             tx
           );
           // Only update the membership to Accepted if the user account is already completed.
-        } else if (orgMembership.status === OrgMembershipStatus.Invited && user.isAccepted) {
+        } else if (orgMembership.status === OrgMembershipStatus.Invited && foundUser.isAccepted) {
           await orgDAL.updateMembershipById(
             orgMembership.id,
             {
@@ -354,40 +373,97 @@ export const samlConfigServiceFactory = ({
             tx
           );
         }
+
+        return foundUser;
       });
     } else {
       user = await userDAL.transaction(async (tx) => {
-        const newUser = await userDAL.create(
+        let newUser: TUsers | undefined;
+        if (serverCfg.trustSamlEmails) {
+          newUser = await userDAL.findOne(
+            {
+              email,
+              isEmailVerified: true
+            },
+            tx
+          );
+        }
+
+        if (!newUser) {
+          const uniqueUsername = await normalizeUsername(`${firstName ?? ""}-${lastName ?? ""}`, userDAL);
+          newUser = await userDAL.create(
+            {
+              username: serverCfg.trustSamlEmails ? email : uniqueUsername,
+              email,
+              isEmailVerified: serverCfg.trustSamlEmails,
+              firstName,
+              lastName,
+              authMethods: [],
+              isGhost: false
+            },
+            tx
+          );
+        }
+
+        await userAliasDAL.create(
           {
-            username,
-            email,
-            firstName,
-            lastName,
-            authMethods: [AuthMethod.EMAIL],
-            isGhost: false
+            userId: newUser.id,
+            aliasType: UserAliasType.SAML,
+            externalId,
+            emails: email ? [email] : [],
+            orgId
           },
           tx
         );
-        await orgDAL.createMembership({
-          inviteEmail: email,
-          orgId,
-          role: OrgMembershipRole.Member,
-          status: OrgMembershipStatus.Invited
-        });
+
+        const [orgMembership] = await orgDAL.findMembership(
+          {
+            [`${TableName.OrgMembership}.userId` as "userId"]: newUser.id,
+            [`${TableName.OrgMembership}.orgId` as "id"]: orgId
+          },
+          { tx }
+        );
+
+        if (!orgMembership) {
+          await orgMembershipDAL.create(
+            {
+              userId: newUser.id,
+              inviteEmail: email,
+              orgId,
+              role: OrgMembershipRole.Member,
+              status: newUser.isAccepted ? OrgMembershipStatus.Accepted : OrgMembershipStatus.Invited // if user is fully completed, then set status to accepted, otherwise set it to invited so we can update it later
+            },
+            tx
+          );
+          // Only update the membership to Accepted if the user account is already completed.
+        } else if (orgMembership.status === OrgMembershipStatus.Invited && newUser.isAccepted) {
+          await orgDAL.updateMembershipById(
+            orgMembership.id,
+            {
+              status: OrgMembershipStatus.Accepted
+            },
+            tx
+          );
+        }
+
         return newUser;
       });
     }
+
     const isUserCompleted = Boolean(user.isAccepted);
     const providerAuthToken = jwt.sign(
       {
         authTokenType: AuthTokenType.PROVIDER_TOKEN,
         userId: user.id,
         username: user.username,
+        ...(user.email && { email: user.email, isEmailVerified: user.isEmailVerified }),
         firstName,
         lastName,
         organizationName: organization.name,
         organizationId: organization.id,
+        organizationSlug: organization.slug,
         authMethod: authProvider,
+        authType: UserAliasType.SAML,
         isUserCompleted,
         ...(relayState
           ? {
@@ -403,6 +479,22 @@ export const samlConfigServiceFactory = ({
 
     await samlConfigDAL.update({ orgId }, { lastUsed: new Date() });
 
+    if (user.email && !user.isEmailVerified) {
+      const token = await tokenService.createTokenForUser({
+        type: TokenType.TOKEN_EMAIL_VERIFICATION,
+        userId: user.id
+      });
+
+      await smtpService.sendMail({
+        template: SmtpTemplates.EmailVerification,
+        subjectLine: "Infisical confirmation code",
+        recipients: [user.email],
+        substitutions: {
+          code: token
+        }
+      });
+    }
+
     return { isUserCompleted, providerAuthToken };
   };
 

backend/src/ee/services/saml-config/saml-config-types.ts

@@ -45,8 +45,8 @@ export type TGetSamlCfgDTO =
     };
 
 export type TSamlLoginDTO = {
-  username: string;
-  email?: string;
+  externalId: string;
+  email: string;
   firstName: string;
   lastName?: string;
   authProvider: string;

backend/src/ee/services/scim/scim-fns.ts

@@ -2,31 +2,31 @@ import { TListScimGroups, TListScimUsers, TScimGroup, TScimUser } from "./scim-t
 
 export const buildScimUserList = ({
   scimUsers,
-  offset,
+  startIndex,
   limit
 }: {
   scimUsers: TScimUser[];
-  offset: number;
+  startIndex: number;
   limit: number;
 }): TListScimUsers => {
   return {
     Resources: scimUsers,
     itemsPerPage: limit,
     schemas: ["urn:ietf:params:scim:api:messages:2.0:ListResponse"],
-    startIndex: offset,
+    startIndex,
     totalResults: scimUsers.length
   };
 };
 
 export const buildScimUser = ({
-  userId,
+  orgMembershipId,
   username,
   email,
   firstName,
   lastName,
   active
 }: {
-  userId: string;
+  orgMembershipId: string;
   username: string;
   email?: string | null;
   firstName: string;
@@ -35,7 +35,7 @@ export const buildScimUser = ({
 }): TScimUser => {
   const scimUser = {
     schemas: ["urn:ietf:params:scim:schemas:core:2.0:User"],
-    id: userId,
+    id: orgMembershipId,
     userName: username,
     displayName: `${firstName} ${lastName}`,
     name: {
@@ -65,18 +65,18 @@ export const buildScimUser = ({
 
 export const buildScimGroupList = ({
   scimGroups,
-  offset,
+  startIndex,
   limit
 }: {
   scimGroups: TScimGroup[];
-  offset: number;
+  startIndex: number;
   limit: number;
 }): TListScimGroups => {
   return {
     Resources: scimGroups,
     itemsPerPage: limit,
     schemas: ["urn:ietf:params:scim:api:messages:2.0:ListResponse"],
-    startIndex: offset,
+    startIndex,
     totalResults: scimGroups.length
   };
 };

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

@@ -2,7 +2,7 @@ import { ForbiddenError } from "@casl/ability";
 import slugify from "@sindresorhus/slugify";
 import jwt from "jsonwebtoken";
 
-import { OrgMembershipRole, OrgMembershipStatus, TableName, TGroups } from "@app/db/schemas";
+import { OrgMembershipRole, OrgMembershipStatus, TableName, TGroups, TOrgMemberships, TUsers } from "@app/db/schemas";
 import { TGroupDALFactory } from "@app/ee/services/group/group-dal";
 import { addUsersToGroupByUserIds, removeUsersFromGroupByUserIds } from "@app/ee/services/group/group-fns";
 import { TUserGroupMembershipDALFactory } from "@app/ee/services/group/user-group-membership-dal";
@@ -11,23 +11,25 @@ import { getConfig } from "@app/lib/config/env";
 import { BadRequestError, ScimRequestError, UnauthorizedError } from "@app/lib/errors";
 import { alphaNumericNanoId } from "@app/lib/nanoid";
 import { TOrgPermission } from "@app/lib/types";
-import { AuthMethod, AuthTokenType } from "@app/services/auth/auth-type";
+import { AuthTokenType } from "@app/services/auth/auth-type";
 import { TGroupProjectDALFactory } from "@app/services/group-project/group-project-dal";
 import { TOrgDALFactory } from "@app/services/org/org-dal";
-import { deleteOrgMembership } from "@app/services/org/org-fns";
+import { deleteOrgMembershipFn } from "@app/services/org/org-fns";
+import { TOrgMembershipDALFactory } from "@app/services/org-membership/org-membership-dal";
 import { TProjectDALFactory } from "@app/services/project/project-dal";
 import { TProjectBotDALFactory } from "@app/services/project-bot/project-bot-dal";
 import { TProjectKeyDALFactory } from "@app/services/project-key/project-key-dal";
 import { TProjectMembershipDALFactory } from "@app/services/project-membership/project-membership-dal";
 import { SmtpTemplates, TSmtpService } from "@app/services/smtp/smtp-service";
+import { getServerCfg } from "@app/services/super-admin/super-admin-service";
 import { TUserDALFactory } from "@app/services/user/user-dal";
+import { normalizeUsername } from "@app/services/user/user-fns";
+import { TUserAliasDALFactory } from "@app/services/user-alias/user-alias-dal";
+import { UserAliasType } from "@app/services/user-alias/user-alias-types";
 
-import { TAccessApprovalRequestDALFactory } from "../access-approval-request/access-approval-request-dal";
 import { TLicenseServiceFactory } from "../license/license-service";
 import { OrgPermissionActions, OrgPermissionSubjects } from "../permission/org-permission";
 import { TPermissionServiceFactory } from "../permission/permission-service";
-import { TSecretApprovalPolicyDALFactory } from "../secret-approval-policy/secret-approval-policy-dal";
-import { TSecretApprovalRequestDALFactory } from "../secret-approval-request/secret-approval-request-dal";
 import { buildScimGroup, buildScimGroupList, buildScimUser, buildScimUserList } from "./scim-fns";
 import {
   TCreateScimGroupDTO,
@@ -50,27 +52,32 @@ import {
 
 type TScimServiceFactoryDep = {
   scimDAL: Pick<TScimDALFactory, "create" | "find" | "findById" | "deleteById">;
-  userDAL: Pick<TUserDALFactory, "find" | "findOne" | "create" | "transaction" | "findUserEncKeyByUserIdsBatch">;
+  userDAL: Pick<
+    TUserDALFactory,
+    "find" | "findOne" | "create" | "transaction" | "findUserEncKeyByUserIdsBatch" | "findById"
+  >;
+  userAliasDAL: Pick<TUserAliasDALFactory, "findOne" | "create" | "delete">;
   orgDAL: Pick<
     TOrgDALFactory,
-    "createMembership" | "findById" | "findMembership" | "deleteMembershipById" | "transaction"
+    "createMembership" | "findById" | "findMembership" | "deleteMembershipById" | "transaction" | "updateMembershipById"
   >;
+  orgMembershipDAL: Pick<TOrgMembershipDALFactory, "find" | "findOne" | "create" | "updateById">;
   projectDAL: Pick<TProjectDALFactory, "find" | "findProjectGhostUser">;
-  projectMembershipDAL: Pick<TProjectMembershipDALFactory, "find" | "delete">;
+  projectMembershipDAL: Pick<TProjectMembershipDALFactory, "find" | "delete" | "findProjectMembershipsByUserId">;
   groupDAL: Pick<
     TGroupDALFactory,
     "create" | "findOne" | "findAllGroupMembers" | "update" | "delete" | "findGroups" | "transaction"
   >;
   groupProjectDAL: Pick<TGroupProjectDALFactory, "find">;
-  userGroupMembershipDAL: TUserGroupMembershipDALFactory; // TODO: Pick
+  userGroupMembershipDAL: Pick<
+    TUserGroupMembershipDALFactory,
+    "find" | "transaction" | "insertMany" | "filterProjectsByUserMembership" | "delete"
+  >;
   projectKeyDAL: Pick<TProjectKeyDALFactory, "find" | "findLatestProjectKey" | "insertMany" | "delete">;
   projectBotDAL: Pick<TProjectBotDALFactory, "findOne">;
-  licenseService: Pick<TLicenseServiceFactory, "getPlan">;
+  licenseService: Pick<TLicenseServiceFactory, "getPlan" | "updateSubscriptionOrgMemberCount">;
   permissionService: Pick<TPermissionServiceFactory, "getOrgPermission">;
-  secretApprovalRequestDAL: Pick<TSecretApprovalRequestDALFactory, "delete">;
-  accessApprovalRequestDAL: Pick<TAccessApprovalRequestDALFactory, "delete">;
-  secretApprovalPolicyDAL: Pick<TSecretApprovalPolicyDALFactory, "findByProjectIds">;
-  smtpService: TSmtpService;
+  smtpService: Pick<TSmtpService, "sendMail">;
 };
 
 export type TScimServiceFactory = ReturnType<typeof scimServiceFactory>;
@@ -79,7 +86,9 @@ export const scimServiceFactory = ({
   licenseService,
   scimDAL,
   userDAL,
+  userAliasDAL,
   orgDAL,
+  orgMembershipDAL,
   projectDAL,
   projectMembershipDAL,
   groupDAL,
@@ -87,9 +96,6 @@ export const scimServiceFactory = ({
   userGroupMembershipDAL,
   projectKeyDAL,
   projectBotDAL,
-  accessApprovalRequestDAL,
-  secretApprovalRequestDAL,
-  secretApprovalPolicyDAL,
   permissionService,
   smtpService
 }: TScimServiceFactoryDep) => {
@@ -169,7 +175,7 @@ export const scimServiceFactory = ({
   };
 
   // SCIM server endpoints
-  const listScimUsers = async ({ offset, limit, filter, orgId }: TListScimUsersDTO): Promise<TListScimUsers> => {
+  const listScimUsers = async ({ startIndex, limit, filter, orgId }: TListScimUsersDTO): Promise<TListScimUsers> => {
     const org = await orgDAL.findById(orgId);
 
     if (!org.scimEnabled)
@@ -187,11 +193,11 @@ export const scimServiceFactory = ({
         attributeName = "email";
       }
 
-      return { [attributeName]: parsedValue };
+      return { [attributeName]: parsedValue.replace(/"/g, "") };
     };
 
     const findOpts = {
-      ...(offset && { offset }),
+      ...(startIndex && { offset: startIndex - 1 }),
       ...(limit && { limit })
     };
 
@@ -203,10 +209,10 @@ export const scimServiceFactory = ({
       findOpts
     );
 
-    const scimUsers = users.map(({ userId, username, firstName, lastName, email }) =>
+    const scimUsers = users.map(({ id, externalId, username, firstName, lastName, email }) =>
       buildScimUser({
-        userId: userId ?? "",
-        username,
+        orgMembershipId: id ?? "",
+        username: externalId ?? username,
         firstName: firstName ?? "",
         lastName: lastName ?? "",
         email,
@@ -216,16 +222,16 @@ export const scimServiceFactory = ({
 
     return buildScimUserList({
       scimUsers,
-      offset,
+      startIndex,
       limit
     });
   };
 
-  const getScimUser = async ({ userId, orgId }: TGetScimUserDTO) => {
+  const getScimUser = async ({ orgMembershipId, orgId }: TGetScimUserDTO) => {
     const [membership] = await orgDAL
       .findMembership({
-        userId,
-        [`${TableName.OrgMembership}.orgId` as "id"]: orgId
+        [`${TableName.OrgMembership}.id` as "id"]: orgMembershipId,
+        [`${TableName.OrgMembership}.orgId` as "orgId"]: orgId
       })
       .catch(() => {
         throw new ScimRequestError({
@@ -247,8 +253,8 @@ export const scimServiceFactory = ({
       });
 
     return buildScimUser({
-      userId: membership.userId as string,
-      username: membership.username,
+      orgMembershipId: membership.id,
+      username: membership.externalId ?? membership.username,
       email: membership.email ?? "",
       firstName: membership.firstName as string,
       lastName: membership.lastName as string,
@@ -256,7 +262,9 @@ export const scimServiceFactory = ({
     });
   };
 
-  const createScimUser = async ({ username, email, firstName, lastName, orgId }: TCreateScimUserDTO) => {
+  const createScimUser = async ({ externalId, email, firstName, lastName, orgId }: TCreateScimUserDTO) => {
+    if (!email) throw new ScimRequestError({ detail: "Invalid request. Missing email.", status: 400 });
+
     const org = await orgDAL.findById(orgId);
 
     if (!org)
@@ -271,67 +279,121 @@ export const scimServiceFactory = ({
         status: 403
       });
 
-    let user = await userDAL.findOne({
-      username
+    const appCfg = getConfig();
+    const serverCfg = await getServerCfg();
+
+    const userAlias = await userAliasDAL.findOne({
+      externalId,
+      orgId,
+      aliasType: UserAliasType.SAML
     });
 
-    if (user) {
-      await userDAL.transaction(async (tx) => {
-        const [orgMembership] = await orgDAL.findMembership(
+    const { user: createdUser, orgMembership: createdOrgMembership } = await userDAL.transaction(async (tx) => {
+      let user: TUsers | undefined;
+      let orgMembership: TOrgMemberships;
+      if (userAlias) {
+        user = await userDAL.findById(userAlias.userId, tx);
+        orgMembership = await orgMembershipDAL.findOne(
           {
             userId: user.id,
-            [`${TableName.OrgMembership}.orgId` as "id"]: orgId
+            orgId
           },
-          { tx }
+          tx
         );
-        if (orgMembership)
-          throw new ScimRequestError({
-            detail: "User already exists in the database",
-            status: 409
-          });
 
         if (!orgMembership) {
-          await orgDAL.createMembership(
+          orgMembership = await orgMembershipDAL.create(
             {
-              userId: user.id,
-              orgId,
+              userId: userAlias.userId,
               inviteEmail: email,
+              orgId,
               role: OrgMembershipRole.Member,
-              status: OrgMembershipStatus.Invited
+              status: user.isAccepted ? OrgMembershipStatus.Accepted : OrgMembershipStatus.Invited // if user is fully completed, then set status to accepted, otherwise set it to invited so we can update it later
+            },
+            tx
+          );
+        } else if (orgMembership.status === OrgMembershipStatus.Invited && user.isAccepted) {
+          orgMembership = await orgMembershipDAL.updateById(
+            orgMembership.id,
+            {
+              status: OrgMembershipStatus.Accepted
             },
             tx
           );
         }
-      });
-    } else {
-      user = await userDAL.transaction(async (tx) => {
-        const newUser = await userDAL.create(
+      } else {
+        if (serverCfg.trustSamlEmails) {
+          user = await userDAL.findOne(
+            {
+              email,
+              isEmailVerified: true
+            },
+            tx
+          );
+        }
+
+        if (!user) {
+          const uniqueUsername = await normalizeUsername(`${firstName}-${lastName}`, userDAL);
+          user = await userDAL.create(
+            {
+              username: serverCfg.trustSamlEmails ? email : uniqueUsername,
+              email,
+              isEmailVerified: serverCfg.trustSamlEmails,
+              firstName,
+              lastName,
+              authMethods: [],
+              isGhost: false
+            },
+            tx
+          );
+        }
+
+        await userAliasDAL.create(
           {
-            username,
-            email,
-            firstName,
-            lastName,
-            authMethods: [AuthMethod.EMAIL],
-            isGhost: false
+            userId: user.id,
+            aliasType: UserAliasType.SAML,
+            externalId,
+            emails: email ? [email] : [],
+            orgId
           },
           tx
         );
 
-        await orgDAL.createMembership(
+        const [foundOrgMembership] = await orgDAL.findMembership(
           {
-            inviteEmail: email,
-            orgId,
-            userId: newUser.id,
-            role: OrgMembershipRole.Member,
-            status: OrgMembershipStatus.Invited
+            [`${TableName.OrgMembership}.userId` as "userId"]: user.id,
+            [`${TableName.OrgMembership}.orgId` as "id"]: orgId
           },
-          tx
+          { tx }
         );
-        return newUser;
-      });
-    }
 
-    const appCfg = getConfig();
+        orgMembership = foundOrgMembership;
+
+        if (!orgMembership) {
+          orgMembership = await orgMembershipDAL.create(
+            {
+              userId: user.id,
+              inviteEmail: email,
+              orgId,
+              role: OrgMembershipRole.Member,
+              status: user.isAccepted ? OrgMembershipStatus.Accepted : OrgMembershipStatus.Invited // if user is fully completed, then set status to accepted, otherwise set it to invited so we can update it later
+            },
+            tx
+          );
+          // Only update the membership to Accepted if the user account is already completed.
+        } else if (orgMembership.status === OrgMembershipStatus.Invited && user.isAccepted) {
+          orgMembership = await orgDAL.updateMembershipById(
+            orgMembership.id,
+            {
+              status: OrgMembershipStatus.Accepted
+            },
+            tx
+          );
+        }
+      }
+
+      return { user, orgMembership };
+    });
 
     if (email) {
       await smtpService.sendMail({
@@ -346,20 +408,20 @@ export const scimServiceFactory = ({
     }
 
     return buildScimUser({
-      userId: user.id,
-      username: user.username,
-      firstName: user.firstName as string,
-      lastName: user.lastName as string,
-      email: user.email ?? "",
+      orgMembershipId: createdOrgMembership.id,
+      username: externalId,
+      firstName: createdUser.firstName as string,
+      lastName: createdUser.lastName as string,
+      email: createdUser.email ?? "",
       active: true
     });
   };
 
-  const updateScimUser = async ({ userId, orgId, operations }: TUpdateScimUserDTO) => {
+  const updateScimUser = async ({ orgMembershipId, orgId, operations }: TUpdateScimUserDTO) => {
     const [membership] = await orgDAL
       .findMembership({
-        userId,
-        [`${TableName.OrgMembership}.orgId` as "id"]: orgId
+        [`${TableName.OrgMembership}.id` as "id"]: orgMembershipId,
+        [`${TableName.OrgMembership}.orgId` as "orgId"]: orgId
       })
       .catch(() => {
         throw new ScimRequestError({
@@ -395,18 +457,20 @@ export const scimServiceFactory = ({
     });
 
     if (!active) {
-      await deleteOrgMembership({
+      await deleteOrgMembershipFn({
         orgMembershipId: membership.id,
         orgId: membership.orgId,
         orgDAL,
-        projectDAL,
-        projectMembershipDAL
+        projectMembershipDAL,
+        projectKeyDAL,
+        userAliasDAL,
+        licenseService
       });
     }
 
     return buildScimUser({
-      userId: membership.userId as string,
-      username: membership.username,
+      orgMembershipId: membership.id,
+      username: membership.externalId ?? membership.username,
       email: membership.email,
       firstName: membership.firstName as string,
       lastName: membership.lastName as string,
@@ -414,11 +478,11 @@ export const scimServiceFactory = ({
     });
   };
 
-  const replaceScimUser = async ({ userId, active, orgId }: TReplaceScimUserDTO) => {
+  const replaceScimUser = async ({ orgMembershipId, active, orgId }: TReplaceScimUserDTO) => {
     const [membership] = await orgDAL
       .findMembership({
-        userId,
-        [`${TableName.OrgMembership}.orgId` as "id"]: orgId
+        [`${TableName.OrgMembership}.id` as "id"]: orgMembershipId,
+        [`${TableName.OrgMembership}.orgId` as "orgId"]: orgId
       })
       .catch(() => {
         throw new ScimRequestError({
@@ -440,19 +504,20 @@ export const scimServiceFactory = ({
       });
 
     if (!active) {
-      // tx
-      await deleteOrgMembership({
+      await deleteOrgMembershipFn({
         orgMembershipId: membership.id,
         orgId: membership.orgId,
         orgDAL,
-        projectDAL,
-        projectMembershipDAL
+        projectMembershipDAL,
+        projectKeyDAL,
+        userAliasDAL,
+        licenseService
       });
     }
 
     return buildScimUser({
-      userId: membership.userId as string,
-      username: membership.username,
+      orgMembershipId: membership.id,
+      username: membership.externalId ?? membership.username,
       email: membership.email,
       firstName: membership.firstName as string,
       lastName: membership.lastName as string,
@@ -460,18 +525,11 @@ export const scimServiceFactory = ({
     });
   };
 
-  const deleteScimUser = async ({ userId, orgId }: TDeleteScimUserDTO) => {
-    const [membership] = await orgDAL
-      .findMembership({
-        userId,
-        [`${TableName.OrgMembership}.orgId` as "id"]: orgId
-      })
-      .catch(() => {
-        throw new ScimRequestError({
-          detail: "User not found",
-          status: 404
-        });
-      });
+  const deleteScimUser = async ({ orgMembershipId, orgId }: TDeleteScimUserDTO) => {
+    const [membership] = await orgDAL.findMembership({
+      [`${TableName.OrgMembership}.id` as "id"]: orgMembershipId,
+      [`${TableName.OrgMembership}.orgId` as "orgId"]: orgId
+    });
 
     if (!membership)
       throw new ScimRequestError({
@@ -486,18 +544,20 @@ export const scimServiceFactory = ({
       });
     }
 
-    await deleteOrgMembership({
+    await deleteOrgMembershipFn({
       orgMembershipId: membership.id,
       orgId: membership.orgId,
       orgDAL,
-      projectDAL,
-      projectMembershipDAL
+      projectMembershipDAL,
+      projectKeyDAL,
+      userAliasDAL,
+      licenseService
     });
 
     return {}; // intentionally return empty object upon success
   };
 
-  const listScimGroups = async ({ orgId, offset, limit }: TListScimGroupsDTO) => {
+  const listScimGroups = async ({ orgId, startIndex, limit }: TListScimGroupsDTO) => {
     const plan = await licenseService.getPlan(orgId);
     if (!plan.groups)
       throw new BadRequestError({
@@ -518,21 +578,27 @@ export const scimServiceFactory = ({
         status: 403
       });
 
-    const groups = await groupDAL.findGroups({
-      orgId
-    });
+    const groups = await groupDAL.findGroups(
+      {
+        orgId
+      },
+      {
+        offset: startIndex - 1,
+        limit
+      }
+    );
 
     const scimGroups = groups.map((group) =>
       buildScimGroup({
         groupId: group.id,
         name: group.name,
-        members: []
+        members: [] // does this need to be populated?
       })
     );
 
     return buildScimGroupList({
       scimGroups,
-      offset,
+      startIndex,
       limit
     });
   };
@@ -571,9 +637,15 @@ export const scimServiceFactory = ({
       );
 
       if (members && members.length) {
+        const orgMemberships = await orgMembershipDAL.find({
+          $in: {
+            id: members.map((member) => member.value)
+          }
+        });
+
         const newMembers = await addUsersToGroupByUserIds({
           group,
-          userIds: members.map((member) => member.value),
+          userIds: orgMemberships.map((membership) => membership.userId as string),
           userDAL,
           userGroupMembershipDAL,
           orgDAL,
@@ -590,12 +662,19 @@ export const scimServiceFactory = ({
       return { group, newMembers: [] };
     });
 
+    const orgMemberships = await orgDAL.findMembership({
+      [`${TableName.OrgMembership}.orgId` as "orgId"]: orgId,
+      $in: {
+        [`${TableName.OrgMembership}.userId` as "userId"]: newGroup.newMembers.map((member) => member.id)
+      }
+    });
+
     return buildScimGroup({
       groupId: newGroup.group.id,
       name: newGroup.group.name,
-      members: newGroup.newMembers.map((member) => ({
-        value: member.id,
-        display: `${member.firstName} ${member.lastName}`
+      members: orgMemberships.map(({ id, firstName, lastName }) => ({
+        value: id,
+        display: `${firstName} ${lastName}`
       }))
     });
   };
@@ -624,15 +703,22 @@ export const scimServiceFactory = ({
       groupId: group.id
     });
 
+    const orgMemberships = await orgDAL.findMembership({
+      [`${TableName.OrgMembership}.orgId` as "orgId"]: orgId,
+      $in: {
+        [`${TableName.OrgMembership}.userId` as "userId"]: users
+          .filter((user) => user.isPartOfGroup)
+          .map((user) => user.id)
+      }
+    });
+
     return buildScimGroup({
       groupId: group.id,
       name: group.name,
-      members: users
-        .filter((user) => user.isPartOfGroup)
-        .map((user) => ({
-          value: user.id,
-          display: `${user.firstName} ${user.lastName}`
-        }))
+      members: orgMemberships.map(({ id, firstName, lastName }) => ({
+        value: id,
+        display: `${firstName} ${lastName}`
+      }))
     });
   };
 
@@ -676,7 +762,13 @@ export const scimServiceFactory = ({
       }
 
       if (members) {
-        const membersIdsSet = new Set(members.map((member) => member.value));
+        const orgMemberships = await orgMembershipDAL.find({
+          $in: {
+            id: members.map((member) => member.value)
+          }
+        });
+
+        const membersIdsSet = new Set(orgMemberships.map((orgMembership) => orgMembership.userId));
 
         const directMemberUserIds = (
           await userGroupMembershipDAL.find({
@@ -695,13 +787,13 @@ export const scimServiceFactory = ({
         const allMembersUserIds = directMemberUserIds.concat(pendingGroupAdditionsUserIds);
         const allMembersUserIdsSet = new Set(allMembersUserIds);
 
-        const toAddUserIds = members.filter((member) => !allMembersUserIdsSet.has(member.value));
+        const toAddUserIds = orgMemberships.filter((member) => !allMembersUserIdsSet.has(member.userId as string));
         const toRemoveUserIds = allMembersUserIds.filter((userId) => !membersIdsSet.has(userId));
 
         if (toAddUserIds.length) {
           await addUsersToGroupByUserIds({
             group,
-            userIds: toAddUserIds.map((member) => member.value),
+            userIds: toAddUserIds.map((member) => member.userId as string),
             userDAL,
             userGroupMembershipDAL,
             orgDAL,
@@ -719,9 +811,6 @@ export const scimServiceFactory = ({
             userIds: toRemoveUserIds,
             userDAL,
             userGroupMembershipDAL,
-            secretApprovalPolicyDAL,
-            accessApprovalRequestDAL,
-            secretApprovalRequestDAL,
             groupProjectDAL,
             projectKeyDAL,
             tx

backend/src/ee/services/scim/scim-types.ts

@@ -12,7 +12,7 @@ export type TDeleteScimTokenDTO = {
 // SCIM server endpoint types
 
 export type TListScimUsersDTO = {
-  offset: number;
+  startIndex: number;
   limit: number;
   filter?: string;
   orgId: string;
@@ -27,12 +27,12 @@ export type TListScimUsers = {
 };
 
 export type TGetScimUserDTO = {
-  userId: string;
+  orgMembershipId: string;
   orgId: string;
 };
 
 export type TCreateScimUserDTO = {
-  username: string;
+  externalId: string;
   email?: string;
   firstName: string;
   lastName: string;
@@ -40,7 +40,7 @@ export type TCreateScimUserDTO = {
 };
 
 export type TUpdateScimUserDTO = {
-  userId: string;
+  orgMembershipId: string;
   orgId: string;
   operations: {
     op: string;
@@ -54,18 +54,18 @@ export type TUpdateScimUserDTO = {
 };
 
 export type TReplaceScimUserDTO = {
-  userId: string;
+  orgMembershipId: string;
   active: boolean;
   orgId: string;
 };
 
 export type TDeleteScimUserDTO = {
-  userId: string;
+  orgMembershipId: string;
   orgId: string;
 };
 
 export type TListScimGroupsDTO = {
-  offset: number;
+  startIndex: number;
   limit: number;
   orgId: string;
 };

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

@@ -20,7 +20,7 @@ export const secretApprovalPolicyDALFactory = (db: TDbClient) => {
         `${TableName.SecretApprovalPolicy}.id`,
         `${TableName.SecretApprovalPolicyApprover}.policyId`
       )
-      .select(tx.ref("approverUserId").withSchema(TableName.SecretApprovalPolicyApprover))
+      .select(tx.ref("approverId").withSchema(TableName.SecretApprovalPolicyApprover))
       .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"))
@@ -33,18 +33,18 @@ export const secretApprovalPolicyDALFactory = (db: TDbClient) => {
       const doc = await sapFindQuery(tx || db, {
         [`${TableName.SecretApprovalPolicy}.id` as "id"]: id
       });
-      const formattedDoc = mergeOneToManyRelation(
+      const formatedDoc = mergeOneToManyRelation(
         doc,
         "id",
-        ({ approverUserId, envId, envName: name, envSlug: slug, ...el }) => ({
+        ({ approverId, envId, envName: name, envSlug: slug, ...el }) => ({
           ...el,
           envId,
           environment: { id: envId, name, slug }
         }),
-        ({ approverUserId }) => approverUserId,
+        ({ approverId }) => approverId,
         "approvers"
       );
-      return formattedDoc?.[0];
+      return formatedDoc?.[0];
     } catch (error) {
       throw new DatabaseError({ error, name: "FindById" });
     }
@@ -53,31 +53,22 @@ export const secretApprovalPolicyDALFactory = (db: TDbClient) => {
   const find = async (filter: TFindFilter<TSecretApprovalPolicies & { projectId: string }>, tx?: Knex) => {
     try {
       const docs = await sapFindQuery(tx || db, filter);
-      const formattedDoc = mergeOneToManyRelation(
+      const formatedDoc = mergeOneToManyRelation(
         docs,
         "id",
-        ({ approverUserId, envId, envName: name, envSlug: slug, ...el }) => ({
+        ({ approverId, envId, envName: name, envSlug: slug, ...el }) => ({
           ...el,
           envId,
           environment: { id: envId, name, slug }
         }),
-        ({ approverUserId }) => approverUserId,
+        ({ approverId }) => approverId,
         "approvers"
       );
-      return formattedDoc;
+      return formatedDoc;
     } catch (error) {
       throw new DatabaseError({ error, name: "Find" });
     }
   };
 
-  const findByProjectIds = async (projectIds: string[], tx?: Knex) => {
-    const policies = await (tx || db)(TableName.SecretApprovalPolicy)
-      .join(TableName.Environment, `${TableName.SecretApprovalPolicy}.envId`, `${TableName.Environment}.id`)
-      .whereIn(`${TableName.Environment}.projectId`, projectIds)
-      .select(selectAllTableCols(TableName.SecretApprovalPolicy));
-
-    return policies;
-  };
-
-  return { ...secretApprovalPolicyOrm, findById, find, findByProjectIds };
+  return { ...secretApprovalPolicyOrm, findById, find };
 };

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

@@ -7,7 +7,6 @@ import { BadRequestError } from "@app/lib/errors";
 import { containsGlobPatterns } from "@app/lib/picomatch";
 import { TProjectEnvDALFactory } from "@app/services/project-env/project-env-dal";
 import { TProjectMembershipDALFactory } from "@app/services/project-membership/project-membership-dal";
-import { TUserDALFactory } from "@app/services/user/user-dal";
 
 import { TSecretApprovalPolicyApproverDALFactory } from "./secret-approval-policy-approver-dal";
 import { TSecretApprovalPolicyDALFactory } from "./secret-approval-policy-dal";
@@ -30,7 +29,6 @@ type TSecretApprovalPolicyServiceFactoryDep = {
   projectEnvDAL: Pick<TProjectEnvDALFactory, "findOne">;
   secretApprovalPolicyApproverDAL: TSecretApprovalPolicyApproverDALFactory;
   projectMembershipDAL: Pick<TProjectMembershipDALFactory, "find">;
-  userDAL: Pick<TUserDALFactory, "findUsersByProjectId" | "findUserByProjectId">;
 };
 
 export type TSecretApprovalPolicyServiceFactory = ReturnType<typeof secretApprovalPolicyServiceFactory>;
@@ -40,7 +38,7 @@ export const secretApprovalPolicyServiceFactory = ({
   permissionService,
   secretApprovalPolicyApproverDAL,
   projectEnvDAL,
-  userDAL
+  projectMembershipDAL
 }: TSecretApprovalPolicyServiceFactoryDep) => {
   const createSecretApprovalPolicy = async ({
     name,
@@ -71,12 +69,11 @@ export const secretApprovalPolicyServiceFactory = ({
     const env = await projectEnvDAL.findOne({ slug: environment, projectId });
     if (!env) throw new BadRequestError({ message: "Environment not found" });
 
-    const secretApproverUsers = await userDAL.findUsersByProjectId(
+    const secretApprovers = await projectMembershipDAL.find({
       projectId,
-      approvers.map((approverUserId) => approverUserId)
-    );
-
-    if (secretApproverUsers.length !== approvers.length)
+      $in: { id: approvers }
+    });
+    if (secretApprovers.length !== approvers.length)
       throw new BadRequestError({ message: "Approver not found in project" });
 
     const secretApproval = await secretApprovalPolicyDAL.transaction(async (tx) => {
@@ -90,8 +87,8 @@ export const secretApprovalPolicyServiceFactory = ({
         tx
       );
       await secretApprovalPolicyApproverDAL.insertMany(
-        secretApproverUsers.map(({ id }) => ({
-          approverUserId: id,
+        secretApprovers.map(({ id }) => ({
+          approverId: id,
           policyId: doc.id
         })),
         tx
@@ -135,19 +132,21 @@ export const secretApprovalPolicyServiceFactory = ({
         tx
       );
       if (approvers) {
-        const secretApproverUsers = await userDAL.findUsersByProjectId(
-          secretApprovalPolicy.projectId,
-          approvers.map((approverUserId) => approverUserId)
+        const secretApprovers = await projectMembershipDAL.find(
+          {
+            projectId: secretApprovalPolicy.projectId,
+            $in: { id: approvers }
+          },
+          { tx }
         );
-
-        if (secretApproverUsers.length !== approvers.length)
+        if (secretApprovers.length !== approvers.length)
           throw new BadRequestError({ message: "Approver not found in project" });
-        if (doc.approvals > secretApproverUsers.length)
+        if (doc.approvals > secretApprovers.length)
           throw new BadRequestError({ message: "Approvals cannot be greater than approvers" });
         await secretApprovalPolicyApproverDAL.delete({ policyId: doc.id }, tx);
         await secretApprovalPolicyApproverDAL.insertMany(
-          secretApproverUsers.map((user) => ({
-            approverUserId: user.id,
+          secretApprovers.map(({ id }) => ({
+            approverId: id,
             policyId: doc.id
           })),
           tx

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

@@ -16,7 +16,7 @@ export type TSecretApprovalRequestDALFactory = ReturnType<typeof secretApprovalR
 
 type TFindQueryFilter = {
   projectId: string;
-  actorId: string;
+  membershipId: string;
   status?: RequestState;
   environment?: string;
   committer?: string;
@@ -49,7 +49,7 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
       )
       .select(selectAllTableCols(TableName.SecretApprovalRequest))
       .select(
-        tx.ref("memberUserId").withSchema(TableName.SecretApprovalRequestReviewer).as("reviewerMemberId"),
+        tx.ref("member").withSchema(TableName.SecretApprovalRequestReviewer).as("reviewerMemberId"),
         tx.ref("status").withSchema(TableName.SecretApprovalRequestReviewer).as("reviewerStatus"),
         tx.ref("id").withSchema(TableName.SecretApprovalPolicy).as("policyId"),
         tx.ref("name").withSchema(TableName.SecretApprovalPolicy).as("policyName"),
@@ -57,14 +57,14 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
         tx.ref("slug").withSchema(TableName.Environment).as("environment"),
         tx.ref("secretPath").withSchema(TableName.SecretApprovalPolicy).as("policySecretPath"),
         tx.ref("approvals").withSchema(TableName.SecretApprovalPolicy).as("policyApprovals"),
-        tx.ref("approverUserId").withSchema(TableName.SecretApprovalPolicyApprover)
+        tx.ref("approverId").withSchema(TableName.SecretApprovalPolicyApprover)
       );
 
   const findById = async (id: string, tx?: Knex) => {
     try {
       const sql = findQuery({ [`${TableName.SecretApprovalRequest}.id` as "id"]: id }, tx || db);
       const docs = await sql;
-      const formattedDoc = sqlNestRelationships({
+      const formatedDoc = sqlNestRelationships({
         data: docs,
         key: "id",
         parentMapper: (el) => ({
@@ -84,20 +84,20 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
             label: "reviewers" as const,
             mapper: ({ reviewerMemberId: member, reviewerStatus: status }) => (member ? { member, status } : undefined)
           },
-          { key: "approverUserId", label: "approvers" as const, mapper: ({ approverUserId }) => approverUserId }
+          { key: "approverId", label: "approvers" as const, mapper: ({ approverId }) => approverId }
         ]
       });
-      if (!formattedDoc?.[0]) return;
+      if (!formatedDoc?.[0]) return;
       return {
-        ...formattedDoc[0],
-        policy: { ...formattedDoc[0].policy, approvers: formattedDoc[0].approvers }
+        ...formatedDoc[0],
+        policy: { ...formatedDoc[0].policy, approvers: formatedDoc[0].approvers }
       };
     } catch (error) {
       throw new DatabaseError({ error, name: "FindByIdSAR" });
     }
   };
 
-  const findProjectRequestCount = async (projectId: string, approverUserId: string, tx?: Knex) => {
+  const findProjectRequestCount = async (projectId: string, membershipId: string, tx?: Knex) => {
     try {
       const docs = await (tx || db)
         .with(
@@ -110,12 +110,12 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
               `${TableName.SecretApprovalRequest}.policyId`,
               `${TableName.SecretApprovalPolicyApprover}.policyId`
             )
-            .where({ [`${TableName.Environment}.projectId` as "projectId"]: projectId })
+            .where({ projectId })
             .andWhere(
               (bd) =>
                 void bd
-                  .where(`${TableName.SecretApprovalPolicyApprover}.approverUserId`, approverUserId)
-                  .orWhere(`${TableName.SecretApprovalRequest}.committerUserId`, approverUserId)
+                  .where(`${TableName.SecretApprovalPolicyApprover}.approverId`, membershipId)
+                  .orWhere(`${TableName.SecretApprovalRequest}.committerId`, membershipId)
             )
             .select("status", `${TableName.SecretApprovalRequest}.id`)
             .groupBy(`${TableName.SecretApprovalRequest}.id`, "status")
@@ -142,7 +142,7 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
   };
 
   const findByProjectId = async (
-    { status, limit = 20, offset = 0, projectId, committer, environment, actorId }: TFindQueryFilter,
+    { status, limit = 20, offset = 0, projectId, committer, environment, membershipId }: TFindQueryFilter,
     tx?: Knex
   ) => {
     try {
@@ -173,21 +173,21 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
         )
         .where(
           stripUndefinedInWhere({
-            [`${TableName.Environment}.projectId`]: projectId,
+            projectId,
             [`${TableName.Environment}.slug` as "slug"]: environment,
             [`${TableName.SecretApprovalRequest}.status`]: status,
-            committerUserId: committer
+            committerId: committer
           })
         )
         .andWhere(
           (bd) =>
             void bd
-              .where(`${TableName.SecretApprovalPolicyApprover}.approverUserId`, actorId)
-              .orWhere(`${TableName.SecretApprovalRequest}.committerUserId`, actorId)
+              .where(`${TableName.SecretApprovalPolicyApprover}.approverId`, membershipId)
+              .orWhere(`${TableName.SecretApprovalRequest}.committerId`, membershipId)
         )
         .select(selectAllTableCols(TableName.SecretApprovalRequest))
         .select(
-          db.ref("projectId").withSchema(TableName.Environment).as("envProjectId"),
+          db.ref("projectId").withSchema(TableName.Environment),
           db.ref("slug").withSchema(TableName.Environment).as("environment"),
           db.ref("id").withSchema(TableName.SecretApprovalRequestReviewer).as("reviewerMemberId"),
           db.ref("status").withSchema(TableName.SecretApprovalRequestReviewer).as("reviewerStatus"),
@@ -201,7 +201,7 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
           ),
           db.ref("secretPath").withSchema(TableName.SecretApprovalPolicy).as("policySecretPath"),
           db.ref("approvals").withSchema(TableName.SecretApprovalPolicy).as("policyApprovals"),
-          db.ref("approverUserId").withSchema(TableName.SecretApprovalPolicyApprover)
+          db.ref("approverId").withSchema(TableName.SecretApprovalPolicyApprover)
         )
         .orderBy("createdAt", "desc");
 
@@ -217,7 +217,7 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
         parentMapper: (el) => ({
           ...SecretApprovalRequestsSchema.parse(el),
           environment: el.environment,
-          projectId: el.envProjectId,
+          projectId: el.projectId,
           policy: {
             id: el.policyId,
             name: el.policyName,
@@ -232,9 +232,9 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
             mapper: ({ reviewerMemberId: member, reviewerStatus: s }) => (member ? { member, status: s } : undefined)
           },
           {
-            key: "approverUserId",
+            key: "approverId",
             label: "approvers" as const,
-            mapper: ({ approverUserId }) => approverUserId
+            mapper: ({ approverId }) => approverId
           },
           {
             key: "commitId",

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

@@ -113,7 +113,7 @@ export const secretApprovalRequestSecretDALFactory = (db: TDbClient) => {
           db.ref("secretCommentTag").withSchema(TableName.SecretVersion).as("secVerCommentTag"),
           db.ref("secretCommentCiphertext").withSchema(TableName.SecretVersion).as("secVerCommentCiphertext")
         );
-      const formattedDoc = sqlNestRelationships({
+      const formatedDoc = sqlNestRelationships({
         data: doc,
         key: "id",
         parentMapper: (data) => SecretApprovalRequestsSecretsSchema.omit({ secretVersion: true }).parse(data),
@@ -212,7 +212,7 @@ export const secretApprovalRequestSecretDALFactory = (db: TDbClient) => {
           }
         ]
       });
-      return formattedDoc?.map(({ secret, secretVersion, ...el }) => ({
+      return formatedDoc?.map(({ secret, secretVersion, ...el }) => ({
         ...el,
         secret: secret?.[0],
         secretVersion: secretVersion?.[0]

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

@@ -7,14 +7,24 @@ import {
   SecretType,
   TSecretApprovalRequestsSecretsInsert
 } from "@app/db/schemas";
+import { decryptSymmetric128BitHexKeyUTF8 } from "@app/lib/crypto";
 import { BadRequestError, UnauthorizedError } from "@app/lib/errors";
 import { groupBy, pick, unique } from "@app/lib/fn";
 import { alphaNumericNanoId } from "@app/lib/nanoid";
 import { ActorType } from "@app/services/auth/auth-type";
 import { TProjectDALFactory } from "@app/services/project/project-dal";
+import { TProjectBotServiceFactory } from "@app/services/project-bot/project-bot-service";
 import { TSecretDALFactory } from "@app/services/secret/secret-dal";
+import {
+  fnSecretBlindIndexCheck,
+  fnSecretBlindIndexCheckV2,
+  fnSecretBulkDelete,
+  fnSecretBulkInsert,
+  fnSecretBulkUpdate,
+  getAllNestedSecretReferences
+} from "@app/services/secret/secret-fns";
 import { TSecretQueueFactory } from "@app/services/secret/secret-queue";
-import { TSecretServiceFactory } from "@app/services/secret/secret-service";
+import { SecretOperations } from "@app/services/secret/secret-types";
 import { TSecretVersionDALFactory } from "@app/services/secret/secret-version-dal";
 import { TSecretVersionTagDALFactory } from "@app/services/secret/secret-version-tag-dal";
 import { TSecretBlindIndexDALFactory } from "@app/services/secret-blind-index/secret-blind-index-dal";
@@ -29,7 +39,6 @@ import { TSecretApprovalRequestReviewerDALFactory } from "./secret-approval-requ
 import { TSecretApprovalRequestSecretDALFactory } from "./secret-approval-request-secret-dal";
 import {
   ApprovalStatus,
-  CommitType,
   RequestState,
   TApprovalRequestCountDTO,
   TGenerateSecretApprovalRequestDTO,
@@ -42,10 +51,11 @@ import {
 
 type TSecretApprovalRequestServiceFactoryDep = {
   permissionService: Pick<TPermissionServiceFactory, "getProjectPermission">;
+  projectBotService: Pick<TProjectBotServiceFactory, "getBotKey">;
   secretApprovalRequestDAL: TSecretApprovalRequestDALFactory;
   secretApprovalRequestSecretDAL: TSecretApprovalRequestSecretDALFactory;
   secretApprovalRequestReviewerDAL: TSecretApprovalRequestReviewerDALFactory;
-  folderDAL: Pick<TSecretFolderDALFactory, "findBySecretPath" | "findById" | "findSecretPathByFolderIds">;
+  folderDAL: Pick<TSecretFolderDALFactory, "findBySecretPath" | "findSecretPathByFolderIds">;
   secretDAL: TSecretDALFactory;
   secretTagDAL: Pick<TSecretTagDALFactory, "findManyTagsById" | "saveTagsToSecret" | "deleteTagsManySecret">;
   secretBlindIndexDAL: Pick<TSecretBlindIndexDALFactory, "findOne">;
@@ -53,15 +63,7 @@ type TSecretApprovalRequestServiceFactoryDep = {
   secretVersionDAL: Pick<TSecretVersionDALFactory, "findLatestVersionMany" | "insertMany">;
   secretVersionTagDAL: Pick<TSecretVersionTagDALFactory, "insertMany">;
   projectDAL: Pick<TProjectDALFactory, "checkProjectUpgradeStatus">;
-  secretService: Pick<
-    TSecretServiceFactory,
-    | "fnSecretBulkInsert"
-    | "fnSecretBulkUpdate"
-    | "fnSecretBlindIndexCheck"
-    | "fnSecretBulkDelete"
-    | "fnSecretBlindIndexCheckV2"
-  >;
-  secretQueueService: Pick<TSecretQueueFactory, "syncSecrets">;
+  secretQueueService: Pick<TSecretQueueFactory, "syncSecrets" | "removeSecretReminder">;
 };
 
 export type TSecretApprovalRequestServiceFactory = ReturnType<typeof secretApprovalRequestServiceFactory>;
@@ -78,14 +80,14 @@ export const secretApprovalRequestServiceFactory = ({
   projectDAL,
   permissionService,
   snapshotService,
-  secretService,
   secretVersionDAL,
-  secretQueueService
+  secretQueueService,
+  projectBotService
 }: TSecretApprovalRequestServiceFactoryDep) => {
   const requestCount = async ({ projectId, actor, actorId, actorOrgId, actorAuthMethod }: TApprovalRequestCountDTO) => {
     if (actor === ActorType.SERVICE) throw new BadRequestError({ message: "Cannot use service token" });
 
-    await permissionService.getProjectPermission(
+    const { membership } = await permissionService.getProjectPermission(
       actor as ActorType.USER,
       actorId,
       projectId,
@@ -93,7 +95,7 @@ export const secretApprovalRequestServiceFactory = ({
       actorOrgId
     );
 
-    const count = await secretApprovalRequestDAL.findProjectRequestCount(projectId, actorId);
+    const count = await secretApprovalRequestDAL.findProjectRequestCount(projectId, membership.id);
     return count;
   };
 
@@ -111,14 +113,19 @@ export const secretApprovalRequestServiceFactory = ({
   }: TListApprovalsDTO) => {
     if (actor === ActorType.SERVICE) throw new BadRequestError({ message: "Cannot use service token" });
 
-    await permissionService.getProjectPermission(actor, actorId, projectId, actorAuthMethod, actorOrgId);
-
+    const { membership } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
     const approvals = await secretApprovalRequestDAL.findByProjectId({
       projectId,
       committer,
       environment,
       status,
-      actorId,
+      membershipId: membership.id,
       limit,
       offset
     });
@@ -138,7 +145,7 @@ export const secretApprovalRequestServiceFactory = ({
     if (!secretApprovalRequest) throw new BadRequestError({ message: "Secret approval request not found" });
 
     const { policy } = secretApprovalRequest;
-    const { hasRole } = await permissionService.getProjectPermission(
+    const { membership, hasRole } = await permissionService.getProjectPermission(
       actor,
       actorId,
       secretApprovalRequest.projectId,
@@ -147,8 +154,8 @@ export const secretApprovalRequestServiceFactory = ({
     );
     if (
       !hasRole(ProjectMembershipRole.Admin) &&
-      secretApprovalRequest.committerUserId !== actorId &&
-      !policy.approvers.find((approverUserId) => approverUserId === actorId)
+      secretApprovalRequest.committerId !== membership.id &&
+      !policy.approvers.find((approverId) => approverId === membership.id)
     ) {
       throw new UnauthorizedError({ message: "User has no access" });
     }
@@ -173,7 +180,7 @@ export const secretApprovalRequestServiceFactory = ({
     if (actor !== ActorType.USER) throw new BadRequestError({ message: "Must be a user" });
 
     const { policy } = secretApprovalRequest;
-    const { hasRole } = await permissionService.getProjectPermission(
+    const { membership, hasRole } = await permissionService.getProjectPermission(
       ActorType.USER,
       actorId,
       secretApprovalRequest.projectId,
@@ -182,8 +189,8 @@ export const secretApprovalRequestServiceFactory = ({
     );
     if (
       !hasRole(ProjectMembershipRole.Admin) &&
-      secretApprovalRequest.committerUserId !== actorId &&
-      !policy.approvers.find((approverUserId) => approverUserId === actorId)
+      secretApprovalRequest.committerId !== membership.id &&
+      !policy.approvers.find((approverId) => approverId === membership.id)
     ) {
       throw new UnauthorizedError({ message: "User has no access" });
     }
@@ -191,7 +198,7 @@ export const secretApprovalRequestServiceFactory = ({
       const review = await secretApprovalRequestReviewerDAL.findOne(
         {
           requestId: secretApprovalRequest.id,
-          memberUserId: actorId
+          member: membership.id
         },
         tx
       );
@@ -200,7 +207,7 @@ export const secretApprovalRequestServiceFactory = ({
           {
             status,
             requestId: secretApprovalRequest.id,
-            memberUserId: actorId
+            member: membership.id
           },
           tx
         );
@@ -223,7 +230,7 @@ export const secretApprovalRequestServiceFactory = ({
     if (actor !== ActorType.USER) throw new BadRequestError({ message: "Must be a user" });
 
     const { policy } = secretApprovalRequest;
-    const { hasRole } = await permissionService.getProjectPermission(
+    const { membership, hasRole } = await permissionService.getProjectPermission(
       ActorType.USER,
       actorId,
       secretApprovalRequest.projectId,
@@ -232,8 +239,8 @@ export const secretApprovalRequestServiceFactory = ({
     );
     if (
       !hasRole(ProjectMembershipRole.Admin) &&
-      secretApprovalRequest.committerUserId !== actorId &&
-      !policy.approvers.find((approverUserId) => approverUserId === actorId)
+      secretApprovalRequest.committerId !== membership.id &&
+      !policy.approvers.find((approverId) => approverId === membership.id)
     ) {
       throw new UnauthorizedError({ message: "User has no access" });
     }
@@ -246,9 +253,8 @@ export const secretApprovalRequestServiceFactory = ({
 
     const updatedRequest = await secretApprovalRequestDAL.updateById(secretApprovalRequest.id, {
       status,
-      statusChangeByUserId: actorId
+      statusChangeBy: membership.id
     });
-
     return { ...secretApprovalRequest, ...updatedRequest };
   };
 
@@ -264,7 +270,7 @@ export const secretApprovalRequestServiceFactory = ({
     if (actor !== ActorType.USER) throw new BadRequestError({ message: "Must be a user" });
 
     const { policy, folderId, projectId } = secretApprovalRequest;
-    const { hasRole } = await permissionService.getProjectPermission(
+    const { membership, hasRole } = await permissionService.getProjectPermission(
       ActorType.USER,
       actorId,
       projectId,
@@ -274,8 +280,8 @@ export const secretApprovalRequestServiceFactory = ({
 
     if (
       !hasRole(ProjectMembershipRole.Admin) &&
-      secretApprovalRequest.committerUserId !== actorId &&
-      !policy.approvers.find((approverUserId) => approverUserId === actorId)
+      secretApprovalRequest.committerId !== membership.id &&
+      !policy.approvers.find((approverId) => approverId === membership.id)
     ) {
       throw new UnauthorizedError({ message: "User has no access" });
     }
@@ -286,18 +292,19 @@ export const secretApprovalRequestServiceFactory = ({
     const hasMinApproval =
       secretApprovalRequest.policy.approvals <=
       secretApprovalRequest.policy.approvers.filter(
-        (approverUserId) => reviewers[approverUserId.toString()] === ApprovalStatus.APPROVED
+        (approverId) => reviewers[approverId.toString()] === ApprovalStatus.APPROVED
       ).length;
 
     if (!hasMinApproval) throw new BadRequestError({ message: "Doesn't have minimum approvals needed" });
     const secretApprovalSecrets = await secretApprovalRequestSecretDAL.findByRequestId(secretApprovalRequest.id);
     if (!secretApprovalSecrets) throw new BadRequestError({ message: "No secrets found" });
 
-    const conflicts: Array<{ secretId: string; op: CommitType }> = [];
-    let secretCreationCommits = secretApprovalSecrets.filter(({ op }) => op === CommitType.Create);
+    const conflicts: Array<{ secretId: string; op: SecretOperations }> = [];
+    let secretCreationCommits = secretApprovalSecrets.filter(({ op }) => op === SecretOperations.Create);
     if (secretCreationCommits.length) {
-      const { secsGroupedByBlindIndex: conflictGroupByBlindIndex } = await secretService.fnSecretBlindIndexCheckV2({
+      const { secsGroupedByBlindIndex: conflictGroupByBlindIndex } = await fnSecretBlindIndexCheckV2({
         folderId,
+        secretDAL,
         inputSecrets: secretCreationCommits.map(({ secretBlindIndex }) => {
           if (!secretBlindIndex) {
             throw new BadRequestError({
@@ -310,17 +317,19 @@ export const secretApprovalRequestServiceFactory = ({
       secretCreationCommits
         .filter(({ secretBlindIndex }) => conflictGroupByBlindIndex[secretBlindIndex || ""])
         .forEach((el) => {
-          conflicts.push({ op: CommitType.Create, secretId: el.id });
+          conflicts.push({ op: SecretOperations.Create, secretId: el.id });
         });
       secretCreationCommits = secretCreationCommits.filter(
         ({ secretBlindIndex }) => !conflictGroupByBlindIndex[secretBlindIndex || ""]
       );
     }
 
-    let secretUpdationCommits = secretApprovalSecrets.filter(({ op }) => op === CommitType.Update);
+    let secretUpdationCommits = secretApprovalSecrets.filter(({ op }) => op === SecretOperations.Update);
     if (secretUpdationCommits.length) {
-      const { secsGroupedByBlindIndex: conflictGroupByBlindIndex } = await secretService.fnSecretBlindIndexCheckV2({
+      const { secsGroupedByBlindIndex: conflictGroupByBlindIndex } = await fnSecretBlindIndexCheckV2({
         folderId,
+        secretDAL,
+        userId: "",
         inputSecrets: secretUpdationCommits
           .filter(({ secretBlindIndex, secret }) => secret && secret.secretBlindIndex !== secretBlindIndex)
           .map(({ secretBlindIndex }) => {
@@ -338,7 +347,7 @@ export const secretApprovalRequestServiceFactory = ({
             (secretBlindIndex && conflictGroupByBlindIndex[secretBlindIndex]) || !secretId
         )
         .forEach((el) => {
-          conflicts.push({ op: CommitType.Update, secretId: el.id });
+          conflicts.push({ op: SecretOperations.Update, secretId: el.id });
         });
 
       secretUpdationCommits = secretUpdationCommits.filter(
@@ -347,11 +356,11 @@ export const secretApprovalRequestServiceFactory = ({
       );
     }
 
-    const secretDeletionCommits = secretApprovalSecrets.filter(({ op }) => op === CommitType.Delete);
-
+    const secretDeletionCommits = secretApprovalSecrets.filter(({ op }) => op === SecretOperations.Delete);
+    const botKey = await projectBotService.getBotKey(projectId).catch(() => null);
     const mergeStatus = await secretApprovalRequestDAL.transaction(async (tx) => {
       const newSecrets = secretCreationCommits.length
-        ? await secretService.fnSecretBulkInsert({
+        ? await fnSecretBulkInsert({
             tx,
             folderId,
             inputSecrets: secretCreationCommits.map((el) => ({
@@ -375,7 +384,17 @@ export const secretApprovalRequestServiceFactory = ({
               ]),
               tags: el?.tags.map(({ id }) => id),
               version: 1,
-              type: SecretType.Shared
+              type: SecretType.Shared,
+              references: botKey
+                ? getAllNestedSecretReferences(
+                    decryptSymmetric128BitHexKeyUTF8({
+                      ciphertext: el.secretValueCiphertext,
+                      iv: el.secretValueIV,
+                      tag: el.secretValueTag,
+                      key: botKey
+                    })
+                  )
+                : undefined
             })),
             secretDAL,
             secretVersionDAL,
@@ -384,7 +403,7 @@ export const secretApprovalRequestServiceFactory = ({
           })
         : [];
       const updatedSecrets = secretUpdationCommits.length
-        ? await secretService.fnSecretBulkUpdate({
+        ? await fnSecretBulkUpdate({
             folderId,
             projectId,
             tx,
@@ -410,7 +429,17 @@ export const secretApprovalRequestServiceFactory = ({
                   "secretReminderNote",
                   "secretReminderRepeatDays",
                   "secretBlindIndex"
-                ])
+                ]),
+                references: botKey
+                  ? getAllNestedSecretReferences(
+                      decryptSymmetric128BitHexKeyUTF8({
+                        ciphertext: el.secretValueCiphertext,
+                        iv: el.secretValueIV,
+                        tag: el.secretValueTag,
+                        key: botKey
+                      })
+                    )
+                  : undefined
               }
             })),
             secretDAL,
@@ -420,11 +449,13 @@ export const secretApprovalRequestServiceFactory = ({
           })
         : [];
       const deletedSecret = secretDeletionCommits.length
-        ? await secretService.fnSecretBulkDelete({
+        ? await fnSecretBulkDelete({
             projectId,
             folderId,
             tx,
             actorId: "",
+            secretDAL,
+            secretQueueService,
             inputSecrets: secretDeletionCommits.map(({ secretBlindIndex }) => {
               if (!secretBlindIndex) {
                 throw new BadRequestError({
@@ -441,7 +472,7 @@ export const secretApprovalRequestServiceFactory = ({
           conflicts: JSON.stringify(conflicts),
           hasMerged: true,
           status: RequestState.Closed,
-          statusChangeByUserId: actorId
+          statusChangeBy: membership.id
         },
         tx
       );
@@ -451,12 +482,14 @@ export const secretApprovalRequestServiceFactory = ({
       };
     });
     await snapshotService.performSnapshot(folderId);
-    const folder = await folderDAL.findById(folderId);
-    // TODO(akhilmhdh-pg):  change query to do secret path from folder
+    const [folder] = await folderDAL.findSecretPathByFolderIds(projectId, [folderId]);
+    if (!folder) throw new BadRequestError({ message: "Folder not found" });
     await secretQueueService.syncSecrets({
       projectId,
-      secretPath: "/",
-      environment: folder?.environment.envSlug as string
+      secretPath: folder.path,
+      environmentSlug: folder.environmentSlug,
+      actorId,
+      actor
     });
     return mergeStatus;
   };
@@ -476,7 +509,7 @@ export const secretApprovalRequestServiceFactory = ({
   }: TGenerateSecretApprovalRequestDTO) => {
     if (actor === ActorType.SERVICE) throw new BadRequestError({ message: "Cannot use service token" });
 
-    const { permission } = await permissionService.getProjectPermission(
+    const { permission, membership } = await permissionService.getProjectPermission(
       actor,
       actorId,
       projectId,
@@ -504,9 +537,9 @@ export const secretApprovalRequestServiceFactory = ({
     const commits: Omit<TSecretApprovalRequestsSecretsInsert, "requestId">[] = [];
     const commitTagIds: Record<string, string[]> = {};
     // for created secret approval change
-    const createdSecrets = data[CommitType.Create];
+    const createdSecrets = data[SecretOperations.Create];
     if (createdSecrets && createdSecrets?.length) {
-      const { keyName2BlindIndex } = await secretService.fnSecretBlindIndexCheck({
+      const { keyName2BlindIndex } = await fnSecretBlindIndexCheck({
         inputSecrets: createdSecrets,
         folderId,
         isNew: true,
@@ -517,7 +550,7 @@ export const secretApprovalRequestServiceFactory = ({
       commits.push(
         ...createdSecrets.map(({ secretName, ...el }) => ({
           ...el,
-          op: CommitType.Create as const,
+          op: SecretOperations.Create as const,
           version: 1,
           secretBlindIndex: keyName2BlindIndex[secretName],
           algorithm: SecretEncryptionAlgo.AES_256_GCM,
@@ -529,12 +562,12 @@ export const secretApprovalRequestServiceFactory = ({
       });
     }
     // not secret approval for update operations
-    const updatedSecrets = data[CommitType.Update];
+    const updatedSecrets = data[SecretOperations.Update];
     if (updatedSecrets && updatedSecrets?.length) {
       // get all blind index
       // Find all those secrets
       // if not throw not found
-      const { keyName2BlindIndex, secrets: secretsToBeUpdated } = await secretService.fnSecretBlindIndexCheck({
+      const { keyName2BlindIndex, secrets: secretsToBeUpdated } = await fnSecretBlindIndexCheck({
         inputSecrets: updatedSecrets,
         folderId,
         isNew: false,
@@ -545,8 +578,8 @@ export const secretApprovalRequestServiceFactory = ({
       // now find any secret that needs to update its name
       // same process as above
       const nameUpdatedSecrets = updatedSecrets.filter(({ newSecretName }) => Boolean(newSecretName));
-      const { keyName2BlindIndex: newKeyName2BlindIndex } = await secretService.fnSecretBlindIndexCheck({
-        inputSecrets: nameUpdatedSecrets,
+      const { keyName2BlindIndex: newKeyName2BlindIndex } = await fnSecretBlindIndexCheck({
+        inputSecrets: nameUpdatedSecrets.map(({ newSecretName }) => ({ secretName: newSecretName as string })),
         folderId,
         isNew: true,
         blindIndexCfg,
@@ -563,14 +596,14 @@ export const secretApprovalRequestServiceFactory = ({
           const secretId = secsGroupedByBlindIndex[keyName2BlindIndex[secretName]][0].id;
           const secretBlindIndex =
             newSecretName && newKeyName2BlindIndex[newSecretName]
-              ? newKeyName2BlindIndex?.[secretName]
+              ? newKeyName2BlindIndex?.[newSecretName]
               : keyName2BlindIndex[secretName];
           // add tags
           if (tagIds?.length) commitTagIds[keyName2BlindIndex[secretName]] = tagIds;
           return {
             ...latestSecretVersions[secretId],
             ...el,
-            op: CommitType.Update as const,
+            op: SecretOperations.Update as const,
             secret: secretId,
             secretVersion: latestSecretVersions[secretId].id,
             secretBlindIndex,
@@ -580,12 +613,12 @@ export const secretApprovalRequestServiceFactory = ({
       );
     }
     // deleted secrets
-    const deletedSecrets = data[CommitType.Delete];
+    const deletedSecrets = data[SecretOperations.Delete];
     if (deletedSecrets && deletedSecrets.length) {
       // get all blind index
       // Find all those secrets
       // if not throw not found
-      const { keyName2BlindIndex, secrets } = await secretService.fnSecretBlindIndexCheck({
+      const { keyName2BlindIndex, secrets } = await fnSecretBlindIndexCheck({
         inputSecrets: deletedSecrets,
         folderId,
         isNew: false,
@@ -606,7 +639,7 @@ export const secretApprovalRequestServiceFactory = ({
           if (!latestSecretVersions[secretId].secretBlindIndex)
             throw new BadRequestError({ message: "Failed to find secret blind index" });
           return {
-            op: CommitType.Delete as const,
+            op: SecretOperations.Delete as const,
             ...latestSecretVersions[secretId],
             secretBlindIndex: latestSecretVersions[secretId].secretBlindIndex as string,
             secret: secretId,
@@ -630,7 +663,7 @@ export const secretApprovalRequestServiceFactory = ({
           policyId: policy.id,
           status: "open",
           hasMerged: false,
-          committerUserId: actorId
+          committerId: membership.id
         },
         tx
       );