Merge pull request #2699 from Infisical/misc/address-remaining-ui-ux-issues-audit

misc: address other ui/ux issues with audit logs

.env.example

@@ -36,16 +36,22 @@ CLIENT_ID_HEROKU=
 CLIENT_ID_VERCEL=
 CLIENT_ID_NETLIFY=
 CLIENT_ID_GITHUB=
+CLIENT_ID_GITHUB_APP=
+CLIENT_SLUG_GITHUB_APP=
 CLIENT_ID_GITLAB=
 CLIENT_ID_BITBUCKET=
 CLIENT_SECRET_HEROKU=
 CLIENT_SECRET_VERCEL=
 CLIENT_SECRET_NETLIFY=
 CLIENT_SECRET_GITHUB=
+CLIENT_SECRET_GITHUB_APP=
 CLIENT_SECRET_GITLAB=
 CLIENT_SECRET_BITBUCKET=
 CLIENT_SLUG_VERCEL=
 
+CLIENT_PRIVATE_KEY_GITHUB_APP=
+CLIENT_APP_ID_GITHUB_APP=
+
 # Sentry (optional) for monitoring errors
 SENTRY_DSN=
 
@@ -72,3 +78,5 @@ PLAIN_API_KEY=
 PLAIN_WISH_LABEL_IDS=
 
 SSL_CLIENT_CERTIFICATE_HEADER_KEY=
+
+ENABLE_MSSQL_SECRET_ROTATION_ENCRYPT=

.env.migration.example

@@ -1 +1,2 @@
 DB_CONNECTION_URI=
+AUDIT_LOGS_DB_CONNECTION_URI=

.github/workflows/deployment-pipeline.yml

@@ -7,12 +7,12 @@ permissions:
 
 jobs:
   infisical-tests:
-    name: Run tests before deployment
+    name: Integration tests
     # https://docs.github.com/en/actions/using-workflows/reusing-workflows#overview
     uses: ./.github/workflows/run-backend-tests.yml
     
   infisical-image:
-    name: Build backend image
+    name: Build
     runs-on: ubuntu-latest
     needs: [infisical-tests]
     steps:
@@ -104,8 +104,8 @@ jobs:
           cluster: infisical-gamma-stage
           wait-for-service-stability: true
 
-  production-postgres-deployment:
-    name: Deploy to production
+  production-us:
+    name: US production deploy 
     runs-on: ubuntu-latest
     needs: [gamma-deployment]
     environment:
@@ -127,6 +127,7 @@ jobs:
       - name: Change directory to backend and install dependencies
         env:
           DB_CONNECTION_URI: ${{ secrets.DB_CONNECTION_URI }}
+          AUDIT_LOGS_DB_CONNECTION_URI: ${{ secrets.AUDIT_LOGS_DB_CONNECTION_URI }}
         run: |
           cd backend
           npm install
@@ -158,3 +159,54 @@ jobs:
           service: infisical-core-platform
           cluster: infisical-core-platform
           wait-for-service-stability: true
+
+  production-eu:
+      name: EU production deploy
+      runs-on: ubuntu-latest
+      needs: [production-us]
+      environment:
+        name: production-eu
+      steps:
+        - uses: twingate/github-action@v1
+          with:
+            service-key: ${{ secrets.TWINGATE_SERVICE_KEY }}
+        - name: Configure AWS Credentials 
+          uses: aws-actions/configure-aws-credentials@v4
+          with:
+            audience: sts.amazonaws.com
+            aws-region: eu-central-1
+            role-to-assume: arn:aws:iam::345594589636:role/gha-make-prod-deployment
+        - name: Checkout code
+          uses: actions/checkout@v2
+        - name: Setup Node.js environment
+          uses: actions/setup-node@v2
+          with:
+            node-version: "20"
+        - name: Change directory to backend and install dependencies
+          env:
+            DB_CONNECTION_URI: ${{ secrets.DB_CONNECTION_URI }}
+          run: |
+            cd backend
+            npm install
+            npm run migration:latest
+        - name: Save commit hashes for tag
+          id: commit
+          uses: pr-mpt/actions-commit-hash@v2
+        - name: Download task definition
+          run: |
+            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-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-core-platform
+            cluster: infisical-core-platform
+            wait-for-service-stability: true

Dockerfile.standalone-infisical

@@ -95,6 +95,10 @@ RUN mkdir frontend-build
 # Production stage
 FROM base AS production
 RUN apk add --upgrade --no-cache ca-certificates
+RUN apk add --no-cache bash curl && curl -1sLf \
+  'https://dl.cloudsmith.io/public/infisical/infisical-cli/setup.alpine.sh' | bash \
+  && apk add infisical=0.31.1 && apk add --no-cache git
+
 RUN addgroup --system --gid 1001 nodejs \
   && adduser --system --uid 1001 non-root-user
 

README.md

@@ -135,9 +135,7 @@ Lean about Infisical's code scanning feature [here](https://infisical.com/docs/c
 
 This repo available under the [MIT expat license](https://github.com/Infisical/infisical/blob/main/LICENSE), with the exception of the `ee` directory which will contain premium enterprise features requiring a Infisical license.
 
-If you are interested in managed Infisical Cloud of self-hosted Enterprise Offering, take a look at [our website](https://infisical.com/) or [book a meeting with us](https://infisical.cal.com/vlad/infisical-demo):
-
-<a href="[https://infisical.cal.com/vlad/infisical-demo](https://infisical.cal.com/vlad/infisical-demo)"><img alt="Schedule a meeting" src="https://cal.com/book-with-cal-dark.svg" /></a>
+If you are interested in managed Infisical Cloud of self-hosted Enterprise Offering, take a look at [our website](https://infisical.com/) or [book a meeting with us](https://infisical.cal.com/vlad/infisical-demo).
 
 ## Security
 
@@ -163,4 +161,3 @@ Not sure where to get started? You can:
 - [Twitter](https://twitter.com/infisical) for fast news
 - [YouTube](https://www.youtube.com/@infisical_os) for videos on secret management
 - [Blog](https://infisical.com/blog) for secret management insights, articles, tutorials, and updates
-- [Roadmap](https://www.notion.so/infisical/be2d2585a6694e40889b03aef96ea36b?v=5b19a8127d1a4060b54769567a8785fa) for planned features

backend/e2e-test/routes/v1/identity.spec.ts

@@ -34,7 +34,7 @@ describe("Identity v1", async () => {
   test("Create identity", async () => {
     const newIdentity = await createIdentity("mac1", OrgMembershipRole.Admin);
     expect(newIdentity.name).toBe("mac1");
-    expect(newIdentity.authMethod).toBeNull();
+    expect(newIdentity.authMethods).toEqual([]);
 
     await deleteIdentity(newIdentity.id);
   });
@@ -42,7 +42,7 @@ describe("Identity v1", async () => {
   test("Update identity", async () => {
     const newIdentity = await createIdentity("mac1", OrgMembershipRole.Admin);
     expect(newIdentity.name).toBe("mac1");
-    expect(newIdentity.authMethod).toBeNull();
+    expect(newIdentity.authMethods).toEqual([]);
 
     const updatedIdentity = await testServer.inject({
       method: "PATCH",

backend/e2e-test/routes/v1/login.spec.ts

@@ -39,8 +39,6 @@ describe("Login V1 Router", async () => {
     });
     expect(res.statusCode).toBe(200);
     const payload = JSON.parse(res.payload);
-    expect(payload).toHaveProperty("mfaEnabled");
     expect(payload).toHaveProperty("token");
-    expect(payload.mfaEnabled).toBeFalsy();
   });
 });

backend/e2e-test/routes/v3/secret-reference.spec.ts

@@ -56,7 +56,10 @@ describe("Secret expansion", () => {
       }
     ];
 
-    await Promise.all(secrets.map((el) => createSecretV2(el)));
+    for (const secret of secrets) {
+      // eslint-disable-next-line no-await-in-loop
+      await createSecretV2(secret);
+    }
 
     const expandedSecret = await getSecretByNameV2({
       environmentSlug: seedData1.environment.slug,
@@ -123,7 +126,10 @@ describe("Secret expansion", () => {
       }
     ];
 
-    await Promise.all(secrets.map((el) => createSecretV2(el)));
+    for (const secret of secrets) {
+      // eslint-disable-next-line no-await-in-loop
+      await createSecretV2(secret);
+    }
 
     const expandedSecret = await getSecretByNameV2({
       environmentSlug: seedData1.environment.slug,
@@ -190,7 +196,11 @@ describe("Secret expansion", () => {
       }
     ];
 
-    await Promise.all(secrets.map((el) => createSecretV2(el)));
+    for (const secret of secrets) {
+      // eslint-disable-next-line no-await-in-loop
+      await createSecretV2(secret);
+    }
+
     const secretImportFromProdToDev = await createSecretImport({
       environmentSlug: seedData1.environment.slug,
       workspaceId: projectId,
@@ -275,7 +285,11 @@ describe("Secret expansion", () => {
         }
       ];
 
-      await Promise.all(secrets.map((el) => createSecretV2(el)));
+      for (const secret of secrets) {
+        // eslint-disable-next-line no-await-in-loop
+        await createSecretV2(secret);
+      }
+
       const secretImportFromProdToDev = await createSecretImport({
         environmentSlug: seedData1.environment.slug,
         workspaceId: projectId,

backend/package.json

@@ -44,14 +44,21 @@
     "test:e2e-watch": "vitest -c vitest.e2e.config.ts --bail=1",
     "test:e2e-coverage": "vitest run --coverage -c vitest.e2e.config.ts",
     "generate:component": "tsx ./scripts/create-backend-file.ts",
-    "generate:schema": "tsx ./scripts/generate-schema-types.ts",
+    "generate:schema": "tsx ./scripts/generate-schema-types.ts && eslint --fix --ext ts ./src/db/schemas",
+    "auditlog-migration:latest": "knex --knexfile ./src/db/auditlog-knexfile.ts --client pg migrate:latest",
+    "auditlog-migration:up": "knex --knexfile ./src/db/auditlog-knexfile.ts --client pg migrate:up",
+    "auditlog-migration:down": "knex --knexfile ./src/db/auditlog-knexfile.ts --client pg migrate:down",
+    "auditlog-migration:list": "knex --knexfile ./src/db/auditlog-knexfile.ts --client pg migrate:list",
+    "auditlog-migration:status": "knex --knexfile ./src/db/auditlog-knexfile.ts --client pg migrate:status",
+    "auditlog-migration:rollback": "knex --knexfile ./src/db/auditlog-knexfile.ts migrate:rollback",
     "migration:new": "tsx ./scripts/create-migration.ts",
-    "migration:up": "knex --knexfile ./src/db/knexfile.ts --client pg migrate:up",
-    "migration:down": "knex --knexfile ./src/db/knexfile.ts --client pg migrate:down",
-    "migration:list": "knex --knexfile ./src/db/knexfile.ts --client pg migrate:list",
-    "migration:latest": "knex --knexfile ./src/db/knexfile.ts --client pg migrate:latest",
-    "migration:status": "knex --knexfile ./src/db/knexfile.ts --client pg migrate:status",
-    "migration:rollback": "knex --knexfile ./src/db/knexfile.ts migrate:rollback",
+    "migration:up": "npm run auditlog-migration:up && knex --knexfile ./src/db/knexfile.ts --client pg migrate:up",
+    "migration:down": "npm run auditlog-migration:down && knex --knexfile ./src/db/knexfile.ts --client pg migrate:down",
+    "migration:list": "npm run auditlog-migration:list && knex --knexfile ./src/db/knexfile.ts --client pg migrate:list",
+    "migration:latest": "npm run auditlog-migration:latest && knex --knexfile ./src/db/knexfile.ts --client pg migrate:latest",
+    "migration:status": "npm run auditlog-migration:status && knex --knexfile ./src/db/knexfile.ts --client pg migrate:status",
+    "migration:rollback": "npm run auditlog-migration:rollback && knex --knexfile ./src/db/knexfile.ts migrate:rollback",
+    "migrate:org": "tsx ./scripts/migrate-organization.ts",
     "seed:new": "tsx ./scripts/create-seed-file.ts",
     "seed": "knex --knexfile ./src/db/knexfile.ts --client pg seed:run",
     "db:reset": "npm run migration:rollback -- --all && npm run migration:latest"
@@ -119,12 +126,14 @@
     "@fastify/etag": "^5.1.0",
     "@fastify/formbody": "^7.4.0",
     "@fastify/helmet": "^11.1.1",
+    "@fastify/multipart": "8.3.0",
     "@fastify/passport": "^2.4.0",
     "@fastify/rate-limit": "^9.0.0",
     "@fastify/session": "^10.7.0",
     "@fastify/swagger": "^8.14.0",
     "@fastify/swagger-ui": "^2.1.0",
     "@node-saml/passport-saml": "^4.0.4",
+    "@octokit/auth-app": "^7.1.1",
     "@octokit/plugin-retry": "^5.0.5",
     "@octokit/rest": "^20.0.2",
     "@octokit/webhooks-types": "^7.3.1",
@@ -147,11 +156,12 @@
     "connect-redis": "^7.1.1",
     "cron": "^3.1.7",
     "dotenv": "^16.4.1",
-    "fastify": "^4.26.0",
+    "fastify": "^4.28.1",
     "fastify-plugin": "^4.5.1",
     "google-auth-library": "^9.9.0",
     "googleapis": "^137.1.0",
     "handlebars": "^4.7.8",
+    "hdb": "^0.19.10",
     "ioredis": "^5.3.2",
     "jmespath": "^0.16.0",
     "jsonwebtoken": "^9.0.2",
@@ -186,6 +196,7 @@
     "scim2-parse-filter": "^0.2.10",
     "sjcl": "^1.0.8",
     "smee-client": "^2.0.0",
+    "snowflake-sdk": "^1.14.0",
     "tedious": "^18.2.1",
     "tweetnacl": "^1.0.3",
     "tweetnacl-util": "^0.15.1",

backend/scripts/generate-schema-types.ts

@@ -90,7 +90,12 @@ const main = async () => {
       .whereRaw("table_schema =  current_schema()")
       .select<{ tableName: string }[]>("table_name as tableName")
       .orderBy("table_name")
-  ).filter((el) => !el.tableName.includes("_migrations"));
+  ).filter(
+    (el) =>
+      !el.tableName.includes("_migrations") &&
+      !el.tableName.includes("audit_logs_") &&
+      el.tableName !== "intermediate_audit_logs"
+  );
 
   for (let i = 0; i < tables.length; i += 1) {
     const { tableName } = tables[i];

backend/scripts/migrate-organization.ts

@@ -0,0 +1,84 @@
+/* eslint-disable */
+import promptSync from "prompt-sync";
+import { execSync } from "child_process";
+import path from "path";
+import { existsSync } from "fs";
+
+const prompt = promptSync({
+  sigint: true
+});
+
+const exportDb = () => {
+  const exportHost = prompt("Enter your Postgres Host to migrate from: ");
+  const exportPort = prompt("Enter your Postgres Port to migrate from [Default = 5432]: ") ?? "5432";
+  const exportUser = prompt("Enter your Postgres User to migrate from: [Default = infisical]: ") ?? "infisical";
+  const exportPassword = prompt("Enter your Postgres Password to migrate from: ");
+  const exportDatabase = prompt("Enter your Postgres Database to migrate from [Default = infisical]: ") ?? "infisical";
+
+  // we do not include the audit_log and secret_sharing entries
+  execSync(
+    `PGDATABASE="${exportDatabase}" PGPASSWORD="${exportPassword}" PGHOST="${exportHost}" PGPORT=${exportPort} PGUSER=${exportUser} pg_dump infisical --exclude-table-data="secret_sharing" --exclude-table-data="audit_log*" > ${path.join(
+      __dirname,
+      "../src/db/dump.sql"
+    )}`,
+    { stdio: "inherit" }
+  );
+};
+
+const importDbForOrg = () => {
+  const importHost = prompt("Enter your Postgres Host to migrate to: ");
+  const importPort = prompt("Enter your Postgres Port to migrate to [Default = 5432]: ") ?? "5432";
+  const importUser = prompt("Enter your Postgres User to migrate to: [Default = infisical]: ") ?? "infisical";
+  const importPassword = prompt("Enter your Postgres Password to migrate to: ");
+  const importDatabase = prompt("Enter your Postgres Database to migrate to [Default = infisical]: ") ?? "infisical";
+  const orgId = prompt("Enter the organization ID to migrate: ");
+
+  if (!existsSync(path.join(__dirname, "../src/db/dump.sql"))) {
+    console.log("File not found, please export the database first.");
+    return;
+  }
+
+  execSync(
+    `PGDATABASE="${importDatabase}" PGPASSWORD="${importPassword}" PGHOST="${importHost}" PGPORT=${importPort} PGUSER=${importUser} psql -f ${path.join(
+      __dirname,
+      "../src/db/dump.sql"
+    )}`
+  );
+
+  execSync(
+    `PGDATABASE="${importDatabase}" PGPASSWORD="${importPassword}" PGHOST="${importHost}" PGPORT=${importPort} PGUSER=${importUser} psql -c "DELETE FROM public.organizations WHERE id != '${orgId}'"`
+  );
+
+  // delete global/instance-level resources not relevant to the organization to migrate
+  // users
+  execSync(
+    `PGDATABASE="${importDatabase}" PGPASSWORD="${importPassword}" PGHOST="${importHost}" PGPORT=${importPort} PGUSER=${importUser} psql -c 'DELETE FROM users WHERE users.id NOT IN (SELECT org_memberships."userId" FROM org_memberships)'`
+  );
+
+  // identities
+  execSync(
+    `PGDATABASE="${importDatabase}" PGPASSWORD="${importPassword}" PGHOST="${importHost}" PGPORT=${importPort} PGUSER=${importUser} psql -c 'DELETE FROM identities WHERE id NOT IN (SELECT "identityId" FROM identity_org_memberships)'`
+  );
+
+  // reset slack configuration in superAdmin
+  execSync(
+    `PGDATABASE="${importDatabase}" PGPASSWORD="${importPassword}" PGHOST="${importHost}" PGPORT=${importPort} PGUSER=${importUser} psql -c 'UPDATE super_admin SET "encryptedSlackClientId" = null, "encryptedSlackClientSecret" = null'`
+  );
+
+  console.log("Organization migrated successfully.");
+};
+
+const main = () => {
+  const action = prompt(
+    "Enter the action to perform\n 1. Export from existing instance.\n 2. Import org to instance.\n \n Action: "
+  );
+  if (action === "1") {
+    exportDb();
+  } else if (action === "2") {
+    importDbForOrg();
+  } else {
+    console.log("Invalid action");
+  }
+};
+
+main();

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

@@ -13,6 +13,7 @@ import { TDynamicSecretLeaseServiceFactory } from "@app/ee/services/dynamic-secr
 import { TExternalKmsServiceFactory } from "@app/ee/services/external-kms/external-kms-service";
 import { TGroupServiceFactory } from "@app/ee/services/group/group-service";
 import { TIdentityProjectAdditionalPrivilegeServiceFactory } from "@app/ee/services/identity-project-additional-privilege/identity-project-additional-privilege-service";
+import { TIdentityProjectAdditionalPrivilegeV2ServiceFactory } from "@app/ee/services/identity-project-additional-privilege-v2/identity-project-additional-privilege-v2-service";
 import { TLdapConfigServiceFactory } from "@app/ee/services/ldap-config/ldap-config-service";
 import { TLicenseServiceFactory } from "@app/ee/services/license/license-service";
 import { TOidcConfigServiceFactory } from "@app/ee/services/oidc/oidc-config-service";
@@ -39,6 +40,7 @@ import { TCertificateServiceFactory } from "@app/services/certificate/certificat
 import { TCertificateAuthorityServiceFactory } from "@app/services/certificate-authority/certificate-authority-service";
 import { TCertificateTemplateServiceFactory } from "@app/services/certificate-template/certificate-template-service";
 import { TCmekServiceFactory } from "@app/services/cmek/cmek-service";
+import { TExternalGroupOrgRoleMappingServiceFactory } from "@app/services/external-group-org-role-mapping/external-group-org-role-mapping-service";
 import { TExternalMigrationServiceFactory } from "@app/services/external-migration/external-migration-service";
 import { TGroupProjectServiceFactory } from "@app/services/group-project/group-project-service";
 import { TIdentityServiceFactory } from "@app/services/identity/identity-service";
@@ -176,6 +178,7 @@ declare module "fastify" {
       dynamicSecretLease: TDynamicSecretLeaseServiceFactory;
       projectUserAdditionalPrivilege: TProjectUserAdditionalPrivilegeServiceFactory;
       identityProjectAdditionalPrivilege: TIdentityProjectAdditionalPrivilegeServiceFactory;
+      identityProjectAdditionalPrivilegeV2: TIdentityProjectAdditionalPrivilegeV2ServiceFactory;
       secretSharing: TSecretSharingServiceFactory;
       rateLimit: TRateLimitServiceFactory;
       userEngagement: TUserEngagementServiceFactory;
@@ -185,6 +188,7 @@ declare module "fastify" {
       workflowIntegration: TWorkflowIntegrationServiceFactory;
       cmek: TCmekServiceFactory;
       migration: TExternalMigrationServiceFactory;
+      externalGroupOrgRoleMapping: TExternalGroupOrgRoleMappingServiceFactory;
     };
     // this is exclusive use for middlewares in which we need to inject data
     // everywhere else access using service layer

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

@@ -0,0 +1,4 @@
+declare module "hdb" {
+  // eslint-disable-next-line @typescript-eslint/no-explicit-any -- Untyped, the function returns `any`.
+  function createClient(options): any;
+}

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

@@ -336,6 +336,11 @@ import {
   TWorkflowIntegrationsInsert,
   TWorkflowIntegrationsUpdate
 } from "@app/db/schemas";
+import {
+  TExternalGroupOrgRoleMappings,
+  TExternalGroupOrgRoleMappingsInsert,
+  TExternalGroupOrgRoleMappingsUpdate
+} from "@app/db/schemas/external-group-org-role-mappings";
 import {
   TSecretV2TagJunction,
   TSecretV2TagJunctionInsert,
@@ -808,5 +813,10 @@ declare module "knex/types/tables" {
       TWorkflowIntegrationsInsert,
       TWorkflowIntegrationsUpdate
     >;
+    [TableName.ExternalGroupOrgRoleMapping]: KnexOriginal.CompositeTableType<
+      TExternalGroupOrgRoleMappings,
+      TExternalGroupOrgRoleMappingsInsert,
+      TExternalGroupOrgRoleMappingsUpdate
+    >;
   }
 }

backend/src/db/auditlog-knexfile.ts

@@ -0,0 +1,75 @@
+// eslint-disable-next-line
+import "ts-node/register";
+
+import dotenv from "dotenv";
+import type { Knex } from "knex";
+import path from "path";
+
+// Update with your config settings. .
+dotenv.config({
+  path: path.join(__dirname, "../../../.env.migration")
+});
+dotenv.config({
+  path: path.join(__dirname, "../../../.env")
+});
+
+if (!process.env.AUDIT_LOGS_DB_CONNECTION_URI && !process.env.AUDIT_LOGS_DB_HOST) {
+  console.info("Dedicated audit log database not found. No further migrations necessary");
+  process.exit(0);
+}
+
+console.info("Executing migration on audit log database...");
+
+export default {
+  development: {
+    client: "postgres",
+    connection: {
+      connectionString: process.env.AUDIT_LOGS_DB_CONNECTION_URI,
+      host: process.env.AUDIT_LOGS_DB_HOST,
+      port: process.env.AUDIT_LOGS_DB_PORT,
+      user: process.env.AUDIT_LOGS_DB_USER,
+      database: process.env.AUDIT_LOGS_DB_NAME,
+      password: process.env.AUDIT_LOGS_DB_PASSWORD,
+      ssl: process.env.AUDIT_LOGS_DB_ROOT_CERT
+        ? {
+            rejectUnauthorized: true,
+            ca: Buffer.from(process.env.AUDIT_LOGS_DB_ROOT_CERT, "base64").toString("ascii")
+          }
+        : false
+    },
+    pool: {
+      min: 2,
+      max: 10
+    },
+    seeds: {
+      directory: "./seeds"
+    },
+    migrations: {
+      tableName: "infisical_migrations"
+    }
+  },
+  production: {
+    client: "postgres",
+    connection: {
+      connectionString: process.env.AUDIT_LOGS_DB_CONNECTION_URI,
+      host: process.env.AUDIT_LOGS_DB_HOST,
+      port: process.env.AUDIT_LOGS_DB_PORT,
+      user: process.env.AUDIT_LOGS_DB_USER,
+      database: process.env.AUDIT_LOGS_DB_NAME,
+      password: process.env.AUDIT_LOGS_DB_PASSWORD,
+      ssl: process.env.AUDIT_LOGS_DB_ROOT_CERT
+        ? {
+            rejectUnauthorized: true,
+            ca: Buffer.from(process.env.AUDIT_LOGS_DB_ROOT_CERT, "base64").toString("ascii")
+          }
+        : false
+    },
+    pool: {
+      min: 2,
+      max: 10
+    },
+    migrations: {
+      tableName: "infisical_migrations"
+    }
+  }
+} as Knex.Config;

backend/src/db/index.ts

@@ -1,2 +1,2 @@
 export type { TDbClient } from "./instance";
-export { initDbConnection } from "./instance";
+export { initAuditLogDbConnection, initDbConnection } from "./instance";

backend/src/db/instance.ts

@@ -70,3 +70,45 @@ export const initDbConnection = ({
 
   return db;
 };
+
+export const initAuditLogDbConnection = ({
+  dbConnectionUri,
+  dbRootCert
+}: {
+  dbConnectionUri: string;
+  dbRootCert?: string;
+}) => {
+  // akhilmhdh: the default Knex is knex.Knex<any, any[]>. but when assigned with knex({<config>}) the value is knex.Knex<any, unknown[]>
+  // this was causing issue with files like `snapshot-dal` `findRecursivelySnapshots` this i am explicitly putting the any and unknown[]
+  // eslint-disable-next-line
+  const db: Knex<any, unknown[]> = knex({
+    client: "pg",
+    connection: {
+      connectionString: dbConnectionUri,
+      host: process.env.AUDIT_LOGS_DB_HOST,
+      // @ts-expect-error I have no clue why only for the port there is a type error
+      // eslint-disable-next-line
+      port: process.env.AUDIT_LOGS_DB_PORT,
+      user: process.env.AUDIT_LOGS_DB_USER,
+      database: process.env.AUDIT_LOGS_DB_NAME,
+      password: process.env.AUDIT_LOGS_DB_PASSWORD,
+      ssl: dbRootCert
+        ? {
+            rejectUnauthorized: true,
+            ca: Buffer.from(dbRootCert, "base64").toString("ascii")
+          }
+        : false
+    }
+  });
+
+  // we add these overrides so that auditLogDb and the primary DB are interchangeable
+  db.primaryNode = () => {
+    return db;
+  };
+
+  db.replicaNode = () => {
+    return db;
+  };
+
+  return db;
+};

backend/src/db/manual-migrations/partition-audit-logs.ts

@@ -0,0 +1,161 @@
+import kx, { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+const INTERMEDIATE_AUDIT_LOG_TABLE = "intermediate_audit_logs";
+
+const formatPartitionDate = (date: Date) => {
+  const year = date.getFullYear();
+  const month = String(date.getMonth() + 1).padStart(2, "0");
+  const day = String(date.getDate()).padStart(2, "0");
+
+  return `${year}-${month}-${day}`;
+};
+
+const createAuditLogPartition = async (knex: Knex, startDate: Date, endDate: Date) => {
+  const startDateStr = formatPartitionDate(startDate);
+  const endDateStr = formatPartitionDate(endDate);
+
+  const partitionName = `${TableName.AuditLog}_${startDateStr.replace(/-/g, "")}_${endDateStr.replace(/-/g, "")}`;
+
+  await knex.schema.raw(
+    `CREATE TABLE ${partitionName} PARTITION OF ${TableName.AuditLog} FOR VALUES FROM ('${startDateStr}') TO ('${endDateStr}')`
+  );
+};
+
+const up = async (knex: Knex): Promise<void> => {
+  console.info("Dropping primary key of audit log table...");
+  await knex.schema.alterTable(TableName.AuditLog, (t) => {
+    // remove existing keys
+    t.dropPrimary();
+  });
+
+  // Get all indices of the audit log table and drop them
+  const indexNames: { rows: { indexname: string }[] } = await knex.raw(
+    `
+    SELECT indexname
+    FROM pg_indexes
+    WHERE tablename = '${TableName.AuditLog}'
+  `
+  );
+
+  console.log(
+    "Deleting existing audit log indices:",
+    indexNames.rows.map((e) => e.indexname)
+  );
+
+  for await (const row of indexNames.rows) {
+    await knex.raw(`DROP INDEX IF EXISTS ${row.indexname}`);
+  }
+
+  // renaming audit log to intermediate table
+  console.log("Renaming audit log table to the intermediate name");
+  await knex.schema.renameTable(TableName.AuditLog, INTERMEDIATE_AUDIT_LOG_TABLE);
+
+  if (!(await knex.schema.hasTable(TableName.AuditLog))) {
+    const createTableSql = knex.schema
+      .createTable(TableName.AuditLog, (t) => {
+        t.uuid("id").defaultTo(knex.fn.uuid());
+        t.string("actor").notNullable();
+        t.jsonb("actorMetadata").notNullable();
+        t.string("ipAddress");
+        t.string("eventType").notNullable();
+        t.jsonb("eventMetadata");
+        t.string("userAgent");
+        t.string("userAgentType");
+        t.datetime("expiresAt");
+        t.timestamps(true, true, true);
+        t.uuid("orgId");
+        t.string("projectId");
+        t.string("projectName");
+        t.primary(["id", "createdAt"]);
+      })
+      .toString();
+
+    console.info("Creating partition table...");
+    await knex.schema.raw(`
+        ${createTableSql} PARTITION BY RANGE ("createdAt");
+    `);
+
+    console.log("Adding indices...");
+    await knex.schema.alterTable(TableName.AuditLog, (t) => {
+      t.index(["projectId", "createdAt"]);
+      t.index(["orgId", "createdAt"]);
+      t.index("expiresAt");
+      t.index("orgId");
+      t.index("projectId");
+    });
+
+    console.log("Adding GIN indices...");
+
+    await knex.raw(
+      `CREATE INDEX IF NOT EXISTS "audit_logs_actorMetadata_idx" ON ${TableName.AuditLog} USING gin("actorMetadata" jsonb_path_ops)`
+    );
+    console.log("GIN index for actorMetadata done");
+
+    await knex.raw(
+      `CREATE INDEX IF NOT EXISTS "audit_logs_eventMetadata_idx" ON ${TableName.AuditLog} USING gin("eventMetadata" jsonb_path_ops)`
+    );
+    console.log("GIN index for eventMetadata done");
+
+    // create default partition
+    console.log("Creating default partition...");
+    await knex.schema.raw(`CREATE TABLE ${TableName.AuditLog}_default PARTITION OF ${TableName.AuditLog} DEFAULT`);
+
+    const nextDate = new Date();
+    nextDate.setDate(nextDate.getDate() + 1);
+    const nextDateStr = formatPartitionDate(nextDate);
+
+    console.log("Attaching existing audit log table as a partition...");
+    await knex.schema.raw(`
+    ALTER TABLE ${INTERMEDIATE_AUDIT_LOG_TABLE} ADD CONSTRAINT audit_log_old
+    CHECK ( "createdAt" < DATE '${nextDateStr}' );
+
+    ALTER TABLE ${TableName.AuditLog} ATTACH PARTITION ${INTERMEDIATE_AUDIT_LOG_TABLE}
+    FOR VALUES FROM (MINVALUE) TO ('${nextDateStr}' );
+    `);
+
+    // create partition from now until end of month
+    console.log("Creating audit log partitions ahead of time... next date:", nextDateStr);
+    await createAuditLogPartition(knex, nextDate, new Date(nextDate.getFullYear(), nextDate.getMonth() + 1));
+
+    // create partitions 4 years ahead
+    const partitionMonths = 4 * 12;
+    const partitionPromises: Promise<void>[] = [];
+    for (let x = 1; x <= partitionMonths; x += 1) {
+      partitionPromises.push(
+        createAuditLogPartition(
+          knex,
+          new Date(nextDate.getFullYear(), nextDate.getMonth() + x, 1),
+          new Date(nextDate.getFullYear(), nextDate.getMonth() + (x + 1), 1)
+        )
+      );
+    }
+
+    await Promise.all(partitionPromises);
+    console.log("Partition migration complete");
+  }
+};
+
+export const executeMigration = async (url: string) => {
+  console.log("Executing migration...");
+  const knex = kx({
+    client: "pg",
+    connection: url
+  });
+
+  await knex.transaction(async (tx) => {
+    await up(tx);
+  });
+};
+
+const dbUrl = process.env.AUDIT_LOGS_DB_CONNECTION_URI;
+if (!dbUrl) {
+  console.error("Please provide a DB connection URL to the AUDIT_LOGS_DB_CONNECTION_URI env");
+  process.exit(1);
+}
+
+void executeMigration(dbUrl).then(() => {
+  console.log("Migration: partition-audit-logs DONE");
+  process.exit(0);
+});

backend/src/db/migrations/20231222172455_integration.ts

@@ -9,7 +9,7 @@ export async function up(knex: Knex): Promise<void> {
       t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
       t.string("integration").notNullable();
       t.string("teamId"); // vercel-specific
-      t.string("url"); // for self hosted
+      t.string("url"); // for self-hosted
       t.string("namespace"); // hashicorp specific
       t.string("accountId"); // netlify
       t.text("refreshCiphertext");
@@ -36,7 +36,7 @@ export async function up(knex: Knex): Promise<void> {
     await knex.schema.createTable(TableName.Integration, (t) => {
       t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
       t.boolean("isActive").notNullable();
-      t.string("url"); // self hosted
+      t.string("url"); // self-hosted
       t.string("app"); // name of app in provider
       t.string("appId");
       t.string("targetEnvironment");

backend/src/db/migrations/20240925100349_managed-secret-sharing.ts

@@ -4,27 +4,40 @@ import { TableName } from "../schemas";
 
 export async function up(knex: Knex): Promise<void> {
   if (await knex.schema.hasTable(TableName.SecretSharing)) {
+    const hasEncryptedSecret = await knex.schema.hasColumn(TableName.SecretSharing, "encryptedSecret");
+    const hasIdentifier = await knex.schema.hasColumn(TableName.SecretSharing, "identifier");
+
     await knex.schema.alterTable(TableName.SecretSharing, (t) => {
       t.string("iv").nullable().alter();
       t.string("tag").nullable().alter();
       t.string("encryptedValue").nullable().alter();
 
-      t.binary("encryptedSecret").nullable();
+      if (!hasEncryptedSecret) {
+        t.binary("encryptedSecret").nullable();
+      }
       t.string("hashedHex").nullable().alter();
 
-      t.string("identifier", 64).nullable();
-      t.unique("identifier");
-      t.index("identifier");
+      if (!hasIdentifier) {
+        t.string("identifier", 64).nullable();
+        t.unique("identifier");
+        t.index("identifier");
+      }
     });
   }
 }
 
 export async function down(knex: Knex): Promise<void> {
+  const hasEncryptedSecret = await knex.schema.hasColumn(TableName.SecretSharing, "encryptedSecret");
+  const hasIdentifier = await knex.schema.hasColumn(TableName.SecretSharing, "identifier");
   if (await knex.schema.hasTable(TableName.SecretSharing)) {
     await knex.schema.alterTable(TableName.SecretSharing, (t) => {
-      t.dropColumn("encryptedSecret");
+      if (hasEncryptedSecret) {
+        t.dropColumn("encryptedSecret");
+      }
 
-      t.dropColumn("identifier");
+      if (hasIdentifier) {
+        t.dropColumn("identifier");
+      }
     });
   }
 }

backend/src/db/migrations/20241003220151_kms-key-cmek-alterations.ts

@@ -7,15 +7,18 @@ export async function up(knex: Knex): Promise<void> {
   if (await knex.schema.hasTable(TableName.KmsKey)) {
     const hasOrgId = await knex.schema.hasColumn(TableName.KmsKey, "orgId");
     const hasSlug = await knex.schema.hasColumn(TableName.KmsKey, "slug");
+    const hasProjectId = await knex.schema.hasColumn(TableName.KmsKey, "projectId");
 
     // drop constraint if exists (won't exist if rolled back, see below)
     await dropConstraintIfExists(TableName.KmsKey, "kms_keys_orgid_slug_unique", knex);
 
     // projectId for CMEK functionality
     await knex.schema.alterTable(TableName.KmsKey, (table) => {
-      table.string("projectId").nullable().references("id").inTable(TableName.Project).onDelete("CASCADE");
+      if (!hasProjectId) {
+        table.string("projectId").nullable().references("id").inTable(TableName.Project).onDelete("CASCADE");
+      }
 
-      if (hasOrgId) {
+      if (hasOrgId && hasSlug) {
         table.unique(["orgId", "projectId", "slug"]);
       }
 
@@ -30,6 +33,7 @@ export async function down(knex: Knex): Promise<void> {
   if (await knex.schema.hasTable(TableName.KmsKey)) {
     const hasOrgId = await knex.schema.hasColumn(TableName.KmsKey, "orgId");
     const hasName = await knex.schema.hasColumn(TableName.KmsKey, "name");
+    const hasProjectId = await knex.schema.hasColumn(TableName.KmsKey, "projectId");
 
     // remove projectId for CMEK functionality
     await knex.schema.alterTable(TableName.KmsKey, (table) => {
@@ -40,7 +44,9 @@ export async function down(knex: Knex): Promise<void> {
       if (hasOrgId) {
         table.dropUnique(["orgId", "projectId", "slug"]);
       }
-      table.dropColumn("projectId");
+      if (hasProjectId) {
+        table.dropColumn("projectId");
+      }
     });
   }
 }

backend/src/db/migrations/20241007052025_make-audit-log-independent.ts

@@ -0,0 +1,48 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  if (await knex.schema.hasTable(TableName.AuditLog)) {
+    const doesProjectIdExist = await knex.schema.hasColumn(TableName.AuditLog, "projectId");
+    const doesOrgIdExist = await knex.schema.hasColumn(TableName.AuditLog, "orgId");
+    const doesProjectNameExist = await knex.schema.hasColumn(TableName.AuditLog, "projectName");
+
+    await knex.schema.alterTable(TableName.AuditLog, (t) => {
+      if (doesOrgIdExist) {
+        t.dropForeign("orgId");
+      }
+
+      if (doesProjectIdExist) {
+        t.dropForeign("projectId");
+      }
+
+      // add normalized field
+      if (!doesProjectNameExist) {
+        t.string("projectName");
+      }
+    });
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  const doesProjectIdExist = await knex.schema.hasColumn(TableName.AuditLog, "projectId");
+  const doesOrgIdExist = await knex.schema.hasColumn(TableName.AuditLog, "orgId");
+  const doesProjectNameExist = await knex.schema.hasColumn(TableName.AuditLog, "projectName");
+
+  if (await knex.schema.hasTable(TableName.AuditLog)) {
+    await knex.schema.alterTable(TableName.AuditLog, (t) => {
+      if (doesOrgIdExist) {
+        t.foreign("orgId").references("id").inTable(TableName.Organization).onDelete("CASCADE");
+      }
+      if (doesProjectIdExist) {
+        t.foreign("projectId").references("id").inTable(TableName.Project).onDelete("CASCADE");
+      }
+
+      // remove normalized field
+      if (doesProjectNameExist) {
+        t.dropColumn("projectName");
+      }
+    });
+  }
+}

backend/src/db/migrations/20241007202149_default-org-membership-roles.ts

@@ -0,0 +1,29 @@
+import { Knex } from "knex";
+
+import { TableName } from "@app/db/schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  // org default role
+  if (await knex.schema.hasTable(TableName.Organization)) {
+    const hasDefaultRoleCol = await knex.schema.hasColumn(TableName.Organization, "defaultMembershipRole");
+
+    if (!hasDefaultRoleCol) {
+      await knex.schema.alterTable(TableName.Organization, (tb) => {
+        tb.string("defaultMembershipRole").notNullable().defaultTo("member");
+      });
+    }
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  // org default role
+  if (await knex.schema.hasTable(TableName.Organization)) {
+    const hasDefaultRoleCol = await knex.schema.hasColumn(TableName.Organization, "defaultMembershipRole");
+
+    if (hasDefaultRoleCol) {
+      await knex.schema.alterTable(TableName.Organization, (tb) => {
+        tb.dropColumn("defaultMembershipRole");
+      });
+    }
+  }
+}

backend/src/db/migrations/20241008172622_project-permission-split.ts

@@ -0,0 +1,101 @@
+/* eslint-disable no-await-in-loop */
+import { packRules, unpackRules } from "@casl/ability/extra";
+import { Knex } from "knex";
+
+import {
+  backfillPermissionV1SchemaToV2Schema,
+  ProjectPermissionSub
+} from "@app/ee/services/permission/project-permission";
+
+import { TableName } from "../schemas";
+
+const CHUNK_SIZE = 1000;
+export async function up(knex: Knex): Promise<void> {
+  const hasVersion = await knex.schema.hasColumn(TableName.ProjectRoles, "version");
+  if (!hasVersion) {
+    await knex.schema.alterTable(TableName.ProjectRoles, (t) => {
+      t.integer("version").defaultTo(1).notNullable();
+    });
+
+    const docs = await knex(TableName.ProjectRoles).select("*");
+    const updatedDocs = docs
+      .filter((i) => {
+        const permissionString = JSON.stringify(i.permissions || []);
+        return (
+          !permissionString.includes(ProjectPermissionSub.SecretImports) &&
+          !permissionString.includes(ProjectPermissionSub.DynamicSecrets)
+        );
+      })
+      .map((el) => ({
+        ...el,
+        // eslint-disable-next-line @typescript-eslint/ban-ts-comment
+        // @ts-ignore-error this is valid ts
+        permissions: JSON.stringify(packRules(backfillPermissionV1SchemaToV2Schema(unpackRules(el.permissions), true)))
+      }));
+    if (updatedDocs.length) {
+      for (let i = 0; i < updatedDocs.length; i += CHUNK_SIZE) {
+        const chunk = updatedDocs.slice(i, i + CHUNK_SIZE);
+        await knex(TableName.ProjectRoles).insert(chunk).onConflict("id").merge();
+      }
+    }
+
+    // secret permission is split into multiple ones like secrets, folders, imports and dynamic-secrets
+    // so we just find all the privileges with respective mapping and map it as needed
+    const identityPrivileges = await knex(TableName.IdentityProjectAdditionalPrivilege).select("*");
+    const updatedIdentityPrivilegesDocs = identityPrivileges
+      .filter((i) => {
+        const permissionString = JSON.stringify(i.permissions || []);
+        return (
+          !permissionString.includes(ProjectPermissionSub.SecretImports) &&
+          !permissionString.includes(ProjectPermissionSub.DynamicSecrets) &&
+          !permissionString.includes(ProjectPermissionSub.SecretFolders)
+        );
+      })
+      .map((el) => ({
+        ...el,
+        // eslint-disable-next-line @typescript-eslint/ban-ts-comment
+        // @ts-ignore-error this is valid ts
+        permissions: JSON.stringify(packRules(backfillPermissionV1SchemaToV2Schema(unpackRules(el.permissions))))
+      }));
+    if (updatedIdentityPrivilegesDocs.length) {
+      for (let i = 0; i < updatedIdentityPrivilegesDocs.length; i += CHUNK_SIZE) {
+        const chunk = updatedIdentityPrivilegesDocs.slice(i, i + CHUNK_SIZE);
+        await knex(TableName.IdentityProjectAdditionalPrivilege).insert(chunk).onConflict("id").merge();
+      }
+    }
+
+    const userPrivileges = await knex(TableName.ProjectUserAdditionalPrivilege).select("*");
+    const updatedUserPrivilegeDocs = userPrivileges
+      .filter((i) => {
+        const permissionString = JSON.stringify(i.permissions || []);
+        return (
+          !permissionString.includes(ProjectPermissionSub.SecretImports) &&
+          !permissionString.includes(ProjectPermissionSub.DynamicSecrets) &&
+          !permissionString.includes(ProjectPermissionSub.SecretFolders)
+        );
+      })
+      .map((el) => ({
+        ...el,
+        // eslint-disable-next-line @typescript-eslint/ban-ts-comment
+        // @ts-ignore-error this is valid ts
+        permissions: JSON.stringify(packRules(backfillPermissionV1SchemaToV2Schema(unpackRules(el.permissions))))
+      }));
+    if (docs.length) {
+      for (let i = 0; i < updatedUserPrivilegeDocs.length; i += CHUNK_SIZE) {
+        const chunk = updatedUserPrivilegeDocs.slice(i, i + CHUNK_SIZE);
+        await knex(TableName.ProjectUserAdditionalPrivilege).insert(chunk).onConflict("id").merge();
+      }
+    }
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  const hasVersion = await knex.schema.hasColumn(TableName.ProjectRoles, "version");
+  if (hasVersion) {
+    await knex.schema.alterTable(TableName.ProjectRoles, (t) => {
+      t.dropColumn("version");
+    });
+
+    // permission change can be ignored
+  }
+}

backend/src/db/migrations/20241014084900_identity-multiple-auth-methods.ts

@@ -0,0 +1,76 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+const BATCH_SIZE = 30_000;
+
+export async function up(knex: Knex): Promise<void> {
+  const hasAuthMethodColumnAccessToken = await knex.schema.hasColumn(TableName.IdentityAccessToken, "authMethod");
+
+  if (!hasAuthMethodColumnAccessToken) {
+    await knex.schema.alterTable(TableName.IdentityAccessToken, (t) => {
+      t.string("authMethod").nullable();
+    });
+
+    let nullableAccessTokens = await knex(TableName.IdentityAccessToken).whereNull("authMethod").limit(BATCH_SIZE);
+    let totalUpdated = 0;
+
+    do {
+      const batchIds = nullableAccessTokens.map((token) => token.id);
+
+      // ! Update the auth method column in batches for the current batch
+      // eslint-disable-next-line no-await-in-loop
+      await knex(TableName.IdentityAccessToken)
+        .whereIn("id", batchIds)
+        .update({
+          // eslint-disable-next-line @typescript-eslint/ban-ts-comment
+          // @ts-ignore because generate schema happens after this
+          authMethod: knex(TableName.Identity)
+            .select("authMethod")
+            .whereRaw(`${TableName.IdentityAccessToken}."identityId" = ${TableName.Identity}.id`)
+            .whereNotNull("authMethod")
+            .first()
+        });
+
+      // eslint-disable-next-line no-await-in-loop
+      nullableAccessTokens = await knex(TableName.IdentityAccessToken).whereNull("authMethod").limit(BATCH_SIZE);
+
+      totalUpdated += batchIds.length;
+      console.log(`Updated ${batchIds.length} access tokens in batch <> Total updated: ${totalUpdated}`);
+    } while (nullableAccessTokens.length > 0);
+
+    // ! We delete all access tokens where the identity has no auth method set!
+    // ! Which means un-configured identities that for some reason have access tokens, will have their access tokens deleted.
+    await knex(TableName.IdentityAccessToken)
+      .whereNotExists((queryBuilder) => {
+        void queryBuilder
+          .select("id")
+          .from(TableName.Identity)
+          .whereRaw(`${TableName.IdentityAccessToken}."identityId" = ${TableName.Identity}.id`)
+          .whereNotNull("authMethod");
+      })
+      .delete();
+
+    // Finally we set the authMethod to notNullable after populating the column.
+    // This will fail if the data is not populated correctly, so it's safe.
+    await knex.schema.alterTable(TableName.IdentityAccessToken, (t) => {
+      t.string("authMethod").notNullable().alter();
+    });
+  }
+
+  // ! We aren't dropping the authMethod column from the Identity itself, because we wan't to be able to easily rollback for the time being.
+}
+
+// eslint-disable-next-line @typescript-eslint/no-unused-vars
+export async function down(knex: Knex): Promise<void> {
+  const hasAuthMethodColumnAccessToken = await knex.schema.hasColumn(TableName.IdentityAccessToken, "authMethod");
+
+  if (hasAuthMethodColumnAccessToken) {
+    await knex.schema.alterTable(TableName.IdentityAccessToken, (t) => {
+      t.dropColumn("authMethod");
+    });
+  }
+}
+
+const config = { transaction: false };
+export { config };

backend/src/db/migrations/20241015084434_increase-identity-metadata-col-length.ts

@@ -0,0 +1,19 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  if (await knex.schema.hasColumn(TableName.IdentityMetadata, "value")) {
+    await knex.schema.alterTable(TableName.IdentityMetadata, (t) => {
+      t.string("value", 1020).alter();
+    });
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  if (await knex.schema.hasColumn(TableName.IdentityMetadata, "value")) {
+    await knex.schema.alterTable(TableName.IdentityMetadata, (t) => {
+      t.string("value", 255).alter();
+    });
+  }
+}

backend/src/db/migrations/20241015145450_external-group-org-role-mapping.ts

@@ -0,0 +1,32 @@
+import { Knex } from "knex";
+
+import { TableName } from "@app/db/schemas";
+import { createOnUpdateTrigger, dropOnUpdateTrigger } from "@app/db/utils";
+
+export async function up(knex: Knex): Promise<void> {
+  // add external group to org role mapping table
+  if (!(await knex.schema.hasTable(TableName.ExternalGroupOrgRoleMapping))) {
+    await knex.schema.createTable(TableName.ExternalGroupOrgRoleMapping, (t) => {
+      t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+      t.string("groupName").notNullable();
+      t.index("groupName");
+      t.string("role").notNullable();
+      t.uuid("roleId");
+      t.foreign("roleId").references("id").inTable(TableName.OrgRoles);
+      t.uuid("orgId").notNullable();
+      t.foreign("orgId").references("id").inTable(TableName.Organization).onDelete("CASCADE");
+      t.timestamps(true, true, true);
+      t.unique(["orgId", "groupName"]);
+    });
+
+    await createOnUpdateTrigger(knex, TableName.ExternalGroupOrgRoleMapping);
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  if (await knex.schema.hasTable(TableName.ExternalGroupOrgRoleMapping)) {
+    await dropOnUpdateTrigger(knex, TableName.ExternalGroupOrgRoleMapping);
+
+    await knex.schema.dropTable(TableName.ExternalGroupOrgRoleMapping);
+  }
+}

backend/src/db/migrations/20241016183616_add-org-enforce-mfa.ts

@@ -0,0 +1,19 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  if (!(await knex.schema.hasColumn(TableName.Organization, "enforceMfa"))) {
+    await knex.schema.alterTable(TableName.Organization, (tb) => {
+      tb.boolean("enforceMfa").defaultTo(false).notNullable();
+    });
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  if (await knex.schema.hasColumn(TableName.Organization, "enforceMfa")) {
+    await knex.schema.alterTable(TableName.Organization, (t) => {
+      t.dropColumn("enforceMfa");
+    });
+  }
+}

backend/src/db/migrations/20241021114650_add-missing-org-cascade-references.ts

@@ -0,0 +1,21 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  if (await knex.schema.hasColumn(TableName.SamlConfig, "orgId")) {
+    await knex.schema.alterTable(TableName.SamlConfig, (t) => {
+      t.dropForeign("orgId");
+      t.foreign("orgId").references("id").inTable(TableName.Organization).onDelete("CASCADE");
+    });
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  if (await knex.schema.hasColumn(TableName.SamlConfig, "orgId")) {
+    await knex.schema.alterTable(TableName.SamlConfig, (t) => {
+      t.dropForeign("orgId");
+      t.foreign("orgId").references("id").inTable(TableName.Organization);
+    });
+  }
+}

backend/src/db/schemas/audit-logs.ts

@@ -20,7 +20,8 @@ export const AuditLogsSchema = z.object({
   createdAt: z.date(),
   updatedAt: z.date(),
   orgId: z.string().uuid().nullable().optional(),
-  projectId: z.string().nullable().optional()
+  projectId: z.string().nullable().optional(),
+  projectName: z.string().nullable().optional()
 });
 
 export type TAuditLogs = z.infer<typeof AuditLogsSchema>;

backend/src/db/schemas/external-group-org-role-mappings.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 ExternalGroupOrgRoleMappingsSchema = z.object({
+  id: z.string().uuid(),
+  groupName: z.string(),
+  role: z.string(),
+  roleId: z.string().uuid().nullable().optional(),
+  orgId: z.string().uuid(),
+  createdAt: z.date(),
+  updatedAt: z.date()
+});
+
+export type TExternalGroupOrgRoleMappings = z.infer<typeof ExternalGroupOrgRoleMappingsSchema>;
+export type TExternalGroupOrgRoleMappingsInsert = Omit<
+  z.input<typeof ExternalGroupOrgRoleMappingsSchema>,
+  TImmutableDBKeys
+>;
+export type TExternalGroupOrgRoleMappingsUpdate = Partial<
+  Omit<z.input<typeof ExternalGroupOrgRoleMappingsSchema>, TImmutableDBKeys>
+>;

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

@@ -20,7 +20,8 @@ export const IdentityAccessTokensSchema = z.object({
   identityId: z.string().uuid(),
   createdAt: z.date(),
   updatedAt: z.date(),
-  name: z.string().nullable().optional()
+  name: z.string().nullable().optional(),
+  authMethod: z.string()
 });
 
 export type TIdentityAccessTokens = z.infer<typeof IdentityAccessTokensSchema>;

backend/src/db/schemas/models.ts

@@ -17,6 +17,7 @@ export enum TableName {
   Groups = "groups",
   GroupProjectMembership = "group_project_memberships",
   GroupProjectMembershipRole = "group_project_membership_roles",
+  ExternalGroupOrgRoleMapping = "external_group_org_role_mappings",
   UserGroupMembership = "user_group_membership",
   UserAliases = "user_aliases",
   UserEncryptionKey = "user_encryption_keys",
@@ -188,7 +189,7 @@ export enum ProjectUpgradeStatus {
 
 export enum IdentityAuthMethod {
   TOKEN_AUTH = "token-auth",
-  Univeral = "universal-auth",
+  UNIVERSAL_AUTH = "universal-auth",
   KUBERNETES_AUTH = "kubernetes-auth",
   GCP_AUTH = "gcp-auth",
   AWS_AUTH = "aws-auth",

backend/src/db/schemas/organizations.ts

@@ -19,7 +19,9 @@ export const OrganizationsSchema = z.object({
   authEnforced: z.boolean().default(false).nullable().optional(),
   scimEnabled: z.boolean().default(false).nullable().optional(),
   kmsDefaultKeyId: z.string().uuid().nullable().optional(),
-  kmsEncryptedDataKey: zodBuffer.nullable().optional()
+  kmsEncryptedDataKey: zodBuffer.nullable().optional(),
+  defaultMembershipRole: z.string().default("member"),
+  enforceMfa: z.boolean().default(false)
 });
 
 export type TOrganizations = z.infer<typeof OrganizationsSchema>;

backend/src/db/seeds/5-machine-identity.ts

@@ -16,7 +16,7 @@ export async function seed(knex: Knex): Promise<void> {
       // @ts-ignore
       id: seedData1.machineIdentity.id,
       name: seedData1.machineIdentity.name,
-      authMethod: IdentityAuthMethod.Univeral
+      authMethod: IdentityAuthMethod.UNIVERSAL_AUTH
     }
   ]);
   const identityUa = await knex(TableName.IdentityUniversalAuth)

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

@@ -165,7 +165,8 @@ export const registerGroupRouter = async (server: FastifyZodProvider) => {
       querystring: z.object({
         offset: z.coerce.number().min(0).max(100).default(0).describe(GROUPS.LIST_USERS.offset),
         limit: z.coerce.number().min(1).max(100).default(10).describe(GROUPS.LIST_USERS.limit),
-        username: z.string().optional().describe(GROUPS.LIST_USERS.username)
+        username: z.string().trim().optional().describe(GROUPS.LIST_USERS.username),
+        search: z.string().trim().optional().describe(GROUPS.LIST_USERS.search)
       }),
       response: {
         200: z.object({

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

@@ -1,9 +1,9 @@
-import { packRules } from "@casl/ability/extra";
 import slugify from "@sindresorhus/slugify";
 import ms from "ms";
 import { z } from "zod";
 
 import { IdentityProjectAdditionalPrivilegeTemporaryMode } from "@app/ee/services/identity-project-additional-privilege/identity-project-additional-privilege-types";
+import { backfillPermissionV1SchemaToV2Schema } from "@app/ee/services/permission/project-permission";
 import { IDENTITY_ADDITIONAL_PRIVILEGE } from "@app/lib/api-docs";
 import { UnauthorizedError } from "@app/lib/errors";
 import { alphaNumericNanoId } from "@app/lib/nanoid";
@@ -79,7 +79,9 @@ 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(permission))
+        // eslint-disable-next-line @typescript-eslint/ban-ts-comment
+        // @ts-ignore-error this is valid ts
+        permissions: backfillPermissionV1SchemaToV2Schema(permission)
       });
       return { privilege };
     }
@@ -159,7 +161,9 @@ 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(permission))
+        // eslint-disable-next-line @typescript-eslint/ban-ts-comment
+        // @ts-ignore-error this is valid ts
+        permissions: backfillPermissionV1SchemaToV2Schema(permission)
       });
       return { privilege };
     }
@@ -244,7 +248,13 @@ export const registerIdentityProjectAdditionalPrivilegeRouter = async (server: F
         projectSlug: req.body.projectSlug,
         data: {
           ...updatedInfo,
-          permissions: permission ? JSON.stringify(packRules(permission)) : undefined
+          // eslint-disable-next-line @typescript-eslint/ban-ts-comment
+          // @ts-ignore-error this is valid ts
+          permissions: permission
+            ? // eslint-disable-next-line @typescript-eslint/ban-ts-comment
+              // @ts-ignore-error this is valid ts
+              backfillPermissionV1SchemaToV2Schema(permission)
+            : undefined
         }
       });
       return { privilege };

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

@@ -81,9 +81,9 @@ export const registerV1EERoutes = async (server: FastifyZodProvider) => {
   await server.register(registerSecretVersionRouter, { prefix: "/secret" });
   await server.register(registerGroupRouter, { prefix: "/groups" });
   await server.register(registerAuditLogStreamRouter, { prefix: "/audit-log-streams" });
+  await server.register(registerUserAdditionalPrivilegeRouter, { prefix: "/user-project-additional-privilege" });
   await server.register(
     async (privilegeRouter) => {
-      await privilegeRouter.register(registerUserAdditionalPrivilegeRouter, { prefix: "/users" });
       await privilegeRouter.register(registerIdentityProjectAdditionalPrivilegeRouter, { prefix: "/identity" });
     },
     { prefix: "/additional-privilege" }

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

@@ -3,12 +3,16 @@ import slugify from "@sindresorhus/slugify";
 import { z } from "zod";
 
 import { ProjectMembershipRole, ProjectMembershipsSchema, ProjectRolesSchema } from "@app/db/schemas";
-import { ProjectPermissionSchema } from "@app/ee/services/permission/project-permission";
+import {
+  backfillPermissionV1SchemaToV2Schema,
+  ProjectPermissionV1Schema
+} from "@app/ee/services/permission/project-permission";
 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 { SanitizedRoleSchema } from "@app/server/routes/sanitizedSchemas";
+import { SanitizedRoleSchemaV1 } from "@app/server/routes/sanitizedSchemas";
 import { AuthMode } from "@app/services/auth/auth-type";
+import { ProjectRoleServiceIdentifierType } from "@app/services/project-role/project-role-types";
 
 export const registerProjectRoleRouter = async (server: FastifyZodProvider) => {
   server.route({
@@ -43,11 +47,11 @@ export const registerProjectRoleRouter = async (server: FastifyZodProvider) => {
           .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)
+        permissions: ProjectPermissionV1Schema.array().describe(PROJECT_ROLE.CREATE.permissions)
       }),
       response: {
         200: z.object({
-          role: SanitizedRoleSchema
+          role: SanitizedRoleSchemaV1
         })
       }
     },
@@ -58,12 +62,16 @@ export const registerProjectRoleRouter = async (server: FastifyZodProvider) => {
         actorId: req.permission.id,
         actorOrgId: req.permission.orgId,
         actor: req.permission.type,
-        projectSlug: req.params.projectSlug,
+        filter: {
+          type: ProjectRoleServiceIdentifierType.SLUG,
+          projectSlug: req.params.projectSlug
+        },
         data: {
           ...req.body,
-          permissions: JSON.stringify(packRules(req.body.permissions))
+          permissions: JSON.stringify(packRules(backfillPermissionV1SchemaToV2Schema(req.body.permissions, true)))
         }
       });
+
       return { role };
     }
   });
@@ -103,11 +111,11 @@ export const registerProjectRoleRouter = async (server: FastifyZodProvider) => {
           }),
         name: z.string().trim().optional().describe(PROJECT_ROLE.UPDATE.name),
         description: z.string().trim().optional().describe(PROJECT_ROLE.UPDATE.description),
-        permissions: ProjectPermissionSchema.array().describe(PROJECT_ROLE.UPDATE.permissions).optional()
+        permissions: ProjectPermissionV1Schema.array().describe(PROJECT_ROLE.UPDATE.permissions).optional()
       }),
       response: {
         200: z.object({
-          role: SanitizedRoleSchema
+          role: SanitizedRoleSchemaV1
         })
       }
     },
@@ -118,11 +126,12 @@ export const registerProjectRoleRouter = async (server: FastifyZodProvider) => {
         actorId: req.permission.id,
         actorOrgId: req.permission.orgId,
         actor: req.permission.type,
-        projectSlug: req.params.projectSlug,
         roleId: req.params.roleId,
         data: {
           ...req.body,
-          permissions: req.body.permissions ? JSON.stringify(packRules(req.body.permissions)) : undefined
+          permissions: req.body.permissions
+            ? JSON.stringify(packRules(backfillPermissionV1SchemaToV2Schema(req.body.permissions, true)))
+            : undefined
         }
       });
       return { role };
@@ -148,7 +157,7 @@ export const registerProjectRoleRouter = async (server: FastifyZodProvider) => {
       }),
       response: {
         200: z.object({
-          role: SanitizedRoleSchema
+          role: SanitizedRoleSchemaV1
         })
       }
     },
@@ -159,7 +168,6 @@ export const registerProjectRoleRouter = async (server: FastifyZodProvider) => {
         actorId: req.permission.id,
         actorOrgId: req.permission.orgId,
         actor: req.permission.type,
-        projectSlug: req.params.projectSlug,
         roleId: req.params.roleId
       });
       return { role };
@@ -195,7 +203,10 @@ export const registerProjectRoleRouter = async (server: FastifyZodProvider) => {
         actorId: req.permission.id,
         actorOrgId: req.permission.orgId,
         actor: req.permission.type,
-        projectSlug: req.params.projectSlug
+        filter: {
+          type: ProjectRoleServiceIdentifierType.SLUG,
+          projectSlug: req.params.projectSlug
+        }
       });
       return { roles };
     }
@@ -214,7 +225,7 @@ export const registerProjectRoleRouter = async (server: FastifyZodProvider) => {
       }),
       response: {
         200: z.object({
-          role: SanitizedRoleSchema
+          role: SanitizedRoleSchemaV1
         })
       }
     },
@@ -225,9 +236,13 @@ export const registerProjectRoleRouter = async (server: FastifyZodProvider) => {
         actorId: req.permission.id,
         actorOrgId: req.permission.orgId,
         actor: req.permission.type,
-        projectSlug: req.params.projectSlug,
+        filter: {
+          type: ProjectRoleServiceIdentifierType.SLUG,
+          projectSlug: req.params.projectSlug
+        },
         roleSlug: req.params.slug
       });
+
       return { role };
     }
   });

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

@@ -128,7 +128,10 @@ export const registerSamlRouter = async (server: FastifyZodProvider) => {
             .map((key) => {
               // for the ones like in format: http://schemas.xmlsoap.org/ws/2005/05/identity/claims/email
               const formatedKey = key.startsWith("http") ? key.split("/").at(-1) || "" : key;
-              return { key: formatedKey, value: String((profile.attributes as Record<string, string>)[key]) };
+              return {
+                key: formatedKey,
+                value: String((profile.attributes as Record<string, string>)[key]).substring(0, 1020)
+              };
             })
             .filter((el) => el.key && !["email", "firstName", "lastName"].includes(el.key));
 

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

@@ -20,7 +20,7 @@ const ScimUserSchema = z.object({
       z.object({
         primary: z.boolean(),
         value: z.string().email(),
-        type: z.string().trim()
+        type: z.string().trim().default("work")
       })
     )
     .optional(),
@@ -210,8 +210,7 @@ export const registerScimRouter = async (server: FastifyZodProvider) => {
           .array(
             z.object({
               primary: z.boolean(),
-              value: z.string().email(),
-              type: z.string().trim()
+              value: z.string().email()
             })
           )
           .optional(),
@@ -281,8 +280,7 @@ export const registerScimRouter = async (server: FastifyZodProvider) => {
           .array(
             z.object({
               primary: z.boolean(),
-              value: z.string().email(),
-              type: z.string().trim()
+              value: z.string().email()
             })
           )
           .optional(),
@@ -301,7 +299,7 @@ export const registerScimRouter = async (server: FastifyZodProvider) => {
             z.object({
               primary: z.boolean(),
               value: z.string().email(),
-              type: z.string().trim()
+              type: z.string().trim().default("work")
             })
           ),
           displayName: z.string().trim(),

backend/src/ee/routes/v1/secret-scanning-router.ts

@@ -2,6 +2,8 @@ import { z } from "zod";
 
 import { GitAppOrgSchema, SecretScanningGitRisksSchema } from "@app/db/schemas";
 import { SecretScanningRiskStatus } from "@app/ee/services/secret-scanning/secret-scanning-types";
+import { getConfig } from "@app/lib/config/env";
+import { BadRequestError } from "@app/lib/errors";
 import { readLimit, writeLimit } from "@app/server/config/rateLimiter";
 import { verifyAuth } from "@app/server/plugins/auth/verify-auth";
 import { AuthMode } from "@app/services/auth/auth-type";
@@ -23,6 +25,13 @@ export const registerSecretScanningRouter = async (server: FastifyZodProvider) =
     },
     onRequest: verifyAuth([AuthMode.JWT]),
     handler: async (req) => {
+      const appCfg = getConfig();
+      if (!appCfg.SECRET_SCANNING_ORG_WHITELIST?.includes(req.auth.orgId)) {
+        throw new BadRequestError({
+          message: "Secret scanning is temporarily unavailable."
+        });
+      }
+
       const session = await server.services.secretScanning.createInstallationSession({
         actor: req.permission.type,
         actorId: req.permission.id,
@@ -30,6 +39,7 @@ export const registerSecretScanningRouter = async (server: FastifyZodProvider) =
         actorOrgId: req.permission.orgId,
         orgId: req.body.organizationId
       });
+
       return session;
     }
   });

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

@@ -2,17 +2,18 @@ import slugify from "@sindresorhus/slugify";
 import ms from "ms";
 import { z } from "zod";
 
-import { ProjectUserAdditionalPrivilegeSchema } from "@app/db/schemas";
+import { ProjectPermissionV2Schema } from "@app/ee/services/permission/project-permission";
 import { ProjectUserAdditionalPrivilegeTemporaryMode } from "@app/ee/services/project-user-additional-privilege/project-user-additional-privilege-types";
 import { PROJECT_USER_ADDITIONAL_PRIVILEGE } from "@app/lib/api-docs";
 import { alphaNumericNanoId } from "@app/lib/nanoid";
 import { readLimit, writeLimit } from "@app/server/config/rateLimiter";
 import { verifyAuth } from "@app/server/plugins/auth/verify-auth";
+import { SanitizedUserProjectAdditionalPrivilegeSchema } from "@app/server/routes/santizedSchemas/user-additional-privilege";
 import { AuthMode } from "@app/services/auth/auth-type";
 
 export const registerUserAdditionalPrivilegeRouter = async (server: FastifyZodProvider) => {
   server.route({
-    url: "/permanent",
+    url: "/",
     method: "POST",
     config: {
       rateLimit: writeLimit
@@ -31,66 +32,30 @@ export const registerUserAdditionalPrivilegeRouter = async (server: FastifyZodPr
           })
           .optional()
           .describe(PROJECT_USER_ADDITIONAL_PRIVILEGE.CREATE.slug),
-        permissions: z.any().array().describe(PROJECT_USER_ADDITIONAL_PRIVILEGE.CREATE.permissions)
-      }),
-      response: {
-        200: z.object({
-          privilege: ProjectUserAdditionalPrivilegeSchema
-        })
-      }
-    },
-    onRequest: verifyAuth([AuthMode.JWT]),
-    handler: async (req) => {
-      const privilege = await server.services.projectUserAdditionalPrivilege.create({
-        actorId: req.permission.id,
-        actor: req.permission.type,
-        actorOrgId: req.permission.orgId,
-        actorAuthMethod: req.permission.authMethod,
-        ...req.body,
-        slug: req.body.slug ? slugify(req.body.slug) : slugify(alphaNumericNanoId(12)),
-        isTemporary: false,
-        permissions: JSON.stringify(req.body.permissions)
-      });
-      return { privilege };
-    }
-  });
-
-  server.route({
-    method: "POST",
-    url: "/temporary",
-    config: {
-      rateLimit: writeLimit
-    },
-    schema: {
-      body: z.object({
-        projectMembershipId: z.string().min(1).describe(PROJECT_USER_ADDITIONAL_PRIVILEGE.CREATE.projectMembershipId),
-        slug: z
-          .string()
-          .min(1)
-          .max(60)
-          .trim()
-          .refine((v) => v.toLowerCase() === v, "Slug must be lowercase")
-          .refine((v) => slugify(v) === v, {
-            message: "Slug must be a valid slug"
+        permissions: ProjectPermissionV2Schema.array().describe(PROJECT_USER_ADDITIONAL_PRIVILEGE.CREATE.permissions),
+        type: z.discriminatedUnion("isTemporary", [
+          z.object({
+            isTemporary: z.literal(false)
+          }),
+          z.object({
+            isTemporary: z.literal(true),
+            temporaryMode: z
+              .nativeEnum(ProjectUserAdditionalPrivilegeTemporaryMode)
+              .describe(PROJECT_USER_ADDITIONAL_PRIVILEGE.CREATE.temporaryMode),
+            temporaryRange: z
+              .string()
+              .refine((val) => ms(val) > 0, "Temporary range must be a positive number")
+              .describe(PROJECT_USER_ADDITIONAL_PRIVILEGE.CREATE.temporaryRange),
+            temporaryAccessStartTime: z
+              .string()
+              .datetime()
+              .describe(PROJECT_USER_ADDITIONAL_PRIVILEGE.CREATE.temporaryAccessStartTime)
           })
-          .optional()
-          .describe(PROJECT_USER_ADDITIONAL_PRIVILEGE.CREATE.slug),
-        permissions: z.any().array().describe(PROJECT_USER_ADDITIONAL_PRIVILEGE.CREATE.permissions),
-        temporaryMode: z
-          .nativeEnum(ProjectUserAdditionalPrivilegeTemporaryMode)
-          .describe(PROJECT_USER_ADDITIONAL_PRIVILEGE.CREATE.temporaryMode),
-        temporaryRange: z
-          .string()
-          .refine((val) => ms(val) > 0, "Temporary range must be a positive number")
-          .describe(PROJECT_USER_ADDITIONAL_PRIVILEGE.CREATE.temporaryRange),
-        temporaryAccessStartTime: z
-          .string()
-          .datetime()
-          .describe(PROJECT_USER_ADDITIONAL_PRIVILEGE.CREATE.temporaryAccessStartTime)
+        ])
       }),
       response: {
         200: z.object({
-          privilege: ProjectUserAdditionalPrivilegeSchema
+          privilege: SanitizedUserProjectAdditionalPrivilegeSchema
         })
       }
     },
@@ -101,10 +66,10 @@ export const registerUserAdditionalPrivilegeRouter = async (server: FastifyZodPr
         actor: req.permission.type,
         actorOrgId: req.permission.orgId,
         actorAuthMethod: req.permission.authMethod,
-        ...req.body,
-        slug: req.body.slug ? slugify(req.body.slug) : `privilege-${slugify(alphaNumericNanoId(12))}`,
-        isTemporary: true,
-        permissions: JSON.stringify(req.body.permissions)
+        projectMembershipId: req.body.projectMembershipId,
+        ...req.body.type,
+        slug: req.body.slug || slugify(alphaNumericNanoId(8)),
+        permissions: req.body.permissions
       });
       return { privilege };
     }
@@ -131,24 +96,31 @@ export const registerUserAdditionalPrivilegeRouter = async (server: FastifyZodPr
               message: "Slug must be a valid slug"
             })
             .describe(PROJECT_USER_ADDITIONAL_PRIVILEGE.UPDATE.slug),
-          permissions: z.any().array().describe(PROJECT_USER_ADDITIONAL_PRIVILEGE.UPDATE.permissions),
-          isTemporary: z.boolean().describe(PROJECT_USER_ADDITIONAL_PRIVILEGE.UPDATE.isTemporary),
-          temporaryMode: z
-            .nativeEnum(ProjectUserAdditionalPrivilegeTemporaryMode)
-            .describe(PROJECT_USER_ADDITIONAL_PRIVILEGE.UPDATE.temporaryMode),
-          temporaryRange: z
-            .string()
-            .refine((val) => ms(val) > 0, "Temporary range must be a positive number")
-            .describe(PROJECT_USER_ADDITIONAL_PRIVILEGE.UPDATE.temporaryRange),
-          temporaryAccessStartTime: z
-            .string()
-            .datetime()
-            .describe(PROJECT_USER_ADDITIONAL_PRIVILEGE.UPDATE.temporaryAccessStartTime)
+          permissions: ProjectPermissionV2Schema.array()
+            .optional()
+            .describe(PROJECT_USER_ADDITIONAL_PRIVILEGE.UPDATE.permissions),
+          type: z.discriminatedUnion("isTemporary", [
+            z.object({ isTemporary: z.literal(false).describe(PROJECT_USER_ADDITIONAL_PRIVILEGE.UPDATE.isTemporary) }),
+            z.object({
+              isTemporary: z.literal(true).describe(PROJECT_USER_ADDITIONAL_PRIVILEGE.UPDATE.isTemporary),
+              temporaryMode: z
+                .nativeEnum(ProjectUserAdditionalPrivilegeTemporaryMode)
+                .describe(PROJECT_USER_ADDITIONAL_PRIVILEGE.UPDATE.temporaryMode),
+              temporaryRange: z
+                .string()
+                .refine((val) => typeof val === "undefined" || ms(val) > 0, "Temporary range must be a positive number")
+                .describe(PROJECT_USER_ADDITIONAL_PRIVILEGE.UPDATE.temporaryRange),
+              temporaryAccessStartTime: z
+                .string()
+                .datetime()
+                .describe(PROJECT_USER_ADDITIONAL_PRIVILEGE.UPDATE.temporaryAccessStartTime)
+            })
+          ])
         })
         .partial(),
       response: {
         200: z.object({
-          privilege: ProjectUserAdditionalPrivilegeSchema
+          privilege: SanitizedUserProjectAdditionalPrivilegeSchema
         })
       }
     },
@@ -160,7 +132,12 @@ export const registerUserAdditionalPrivilegeRouter = async (server: FastifyZodPr
         actorOrgId: req.permission.orgId,
         actorAuthMethod: req.permission.authMethod,
         ...req.body,
-        permissions: req.body.permissions ? JSON.stringify(req.body.permissions) : undefined,
+        ...req.body.type,
+        permissions: req.body.permissions
+          ? // eslint-disable-next-line @typescript-eslint/ban-ts-comment
+            // @ts-ignore-error this is valid ts
+            req.body.permissions
+          : undefined,
         privilegeId: req.params.privilegeId
       });
       return { privilege };
@@ -179,7 +156,7 @@ export const registerUserAdditionalPrivilegeRouter = async (server: FastifyZodPr
       }),
       response: {
         200: z.object({
-          privilege: ProjectUserAdditionalPrivilegeSchema
+          privilege: SanitizedUserProjectAdditionalPrivilegeSchema
         })
       }
     },
@@ -208,7 +185,7 @@ export const registerUserAdditionalPrivilegeRouter = async (server: FastifyZodPr
       }),
       response: {
         200: z.object({
-          privileges: ProjectUserAdditionalPrivilegeSchema.array()
+          privileges: SanitizedUserProjectAdditionalPrivilegeSchema.omit({ permissions: true }).array()
         })
       }
     },
@@ -233,11 +210,11 @@ export const registerUserAdditionalPrivilegeRouter = async (server: FastifyZodPr
     },
     schema: {
       params: z.object({
-        privilegeId: z.string().describe(PROJECT_USER_ADDITIONAL_PRIVILEGE.GET_BY_PRIVILEGEID.privilegeId)
+        privilegeId: z.string().describe(PROJECT_USER_ADDITIONAL_PRIVILEGE.GET_BY_PRIVILEGE_ID.privilegeId)
       }),
       response: {
         200: z.object({
-          privilege: ProjectUserAdditionalPrivilegeSchema
+          privilege: SanitizedUserProjectAdditionalPrivilegeSchema
         })
       }
     },

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

@@ -0,0 +1,305 @@
+import slugify from "@sindresorhus/slugify";
+import ms from "ms";
+import { z } from "zod";
+
+import { IdentityProjectAdditionalPrivilegeTemporaryMode } from "@app/ee/services/identity-project-additional-privilege-v2/identity-project-additional-privilege-v2-types";
+import { ProjectPermissionV2Schema } from "@app/ee/services/permission/project-permission";
+import { IDENTITY_ADDITIONAL_PRIVILEGE_V2 } from "@app/lib/api-docs";
+import { alphaNumericNanoId } from "@app/lib/nanoid";
+import { readLimit, writeLimit } from "@app/server/config/rateLimiter";
+import { verifyAuth } from "@app/server/plugins/auth/verify-auth";
+import { SanitizedIdentityPrivilegeSchema } from "@app/server/routes/santizedSchemas/identitiy-additional-privilege";
+import { AuthMode } from "@app/services/auth/auth-type";
+
+export const registerIdentityProjectAdditionalPrivilegeRouter = async (server: FastifyZodProvider) => {
+  server.route({
+    method: "POST",
+    url: "/",
+    config: {
+      rateLimit: writeLimit
+    },
+    schema: {
+      description: "Add an additional privilege for identity.",
+      security: [
+        {
+          bearerAuth: []
+        }
+      ],
+      body: z.object({
+        identityId: z.string().min(1).describe(IDENTITY_ADDITIONAL_PRIVILEGE_V2.CREATE.identityId),
+        projectId: z.string().min(1).describe(IDENTITY_ADDITIONAL_PRIVILEGE_V2.CREATE.projectId),
+        slug: z
+          .string()
+          .min(1)
+          .max(60)
+          .trim()
+          .refine((val) => val.toLowerCase() === val, "Must be lowercase")
+          .refine((v) => slugify(v) === v, {
+            message: "Slug must be a valid slug"
+          })
+          .optional()
+          .describe(IDENTITY_ADDITIONAL_PRIVILEGE_V2.CREATE.slug),
+        permissions: ProjectPermissionV2Schema.array().describe(IDENTITY_ADDITIONAL_PRIVILEGE_V2.CREATE.permission),
+        type: z.discriminatedUnion("isTemporary", [
+          z.object({
+            isTemporary: z.literal(false)
+          }),
+          z.object({
+            isTemporary: z.literal(true),
+            temporaryMode: z
+              .nativeEnum(IdentityProjectAdditionalPrivilegeTemporaryMode)
+              .describe(IDENTITY_ADDITIONAL_PRIVILEGE_V2.CREATE.temporaryMode),
+            temporaryRange: z
+              .string()
+              .refine((val) => ms(val) > 0, "Temporary range must be a positive number")
+              .describe(IDENTITY_ADDITIONAL_PRIVILEGE_V2.CREATE.temporaryRange),
+            temporaryAccessStartTime: z
+              .string()
+              .datetime()
+              .describe(IDENTITY_ADDITIONAL_PRIVILEGE_V2.CREATE.temporaryAccessStartTime)
+          })
+        ])
+      }),
+      response: {
+        200: z.object({
+          privilege: SanitizedIdentityPrivilegeSchema
+        })
+      }
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    handler: async (req) => {
+      const privilege = await server.services.identityProjectAdditionalPrivilegeV2.create({
+        actorAuthMethod: req.permission.authMethod,
+        actorId: req.permission.id,
+        actorOrgId: req.permission.orgId,
+        actor: req.permission.type,
+        projectId: req.body.projectId,
+        identityId: req.body.identityId,
+        ...req.body.type,
+        slug: req.body.slug || slugify(alphaNumericNanoId(8)),
+        permissions: req.body.permissions
+      });
+      return { privilege };
+    }
+  });
+
+  server.route({
+    method: "PATCH",
+    url: "/:id",
+    config: {
+      rateLimit: writeLimit
+    },
+    schema: {
+      description: "Update a specific identity privilege.",
+      security: [
+        {
+          bearerAuth: []
+        }
+      ],
+      params: z.object({
+        id: z.string().trim().describe(IDENTITY_ADDITIONAL_PRIVILEGE_V2.UPDATE.id)
+      }),
+      body: z.object({
+        slug: z
+          .string()
+          .min(1)
+          .max(60)
+          .trim()
+          .refine((val) => val.toLowerCase() === val, "Must be lowercase")
+          .refine((v) => slugify(v) === v, {
+            message: "Slug must be a valid slug"
+          })
+          .describe(IDENTITY_ADDITIONAL_PRIVILEGE_V2.UPDATE.slug),
+        permissions: ProjectPermissionV2Schema.array()
+          .optional()
+          .describe(IDENTITY_ADDITIONAL_PRIVILEGE_V2.UPDATE.privilegePermission),
+        type: z.discriminatedUnion("isTemporary", [
+          z.object({ isTemporary: z.literal(false).describe(IDENTITY_ADDITIONAL_PRIVILEGE_V2.UPDATE.isTemporary) }),
+          z.object({
+            isTemporary: z.literal(true).describe(IDENTITY_ADDITIONAL_PRIVILEGE_V2.UPDATE.isTemporary),
+            temporaryMode: z
+              .nativeEnum(IdentityProjectAdditionalPrivilegeTemporaryMode)
+              .describe(IDENTITY_ADDITIONAL_PRIVILEGE_V2.UPDATE.temporaryMode),
+            temporaryRange: z
+              .string()
+              .refine((val) => typeof val === "undefined" || ms(val) > 0, "Temporary range must be a positive number")
+              .describe(IDENTITY_ADDITIONAL_PRIVILEGE_V2.UPDATE.temporaryRange),
+            temporaryAccessStartTime: z
+              .string()
+              .datetime()
+              .describe(IDENTITY_ADDITIONAL_PRIVILEGE_V2.UPDATE.temporaryAccessStartTime)
+          })
+        ])
+      }),
+      response: {
+        200: z.object({
+          privilege: SanitizedIdentityPrivilegeSchema
+        })
+      }
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    handler: async (req) => {
+      const privilege = await server.services.identityProjectAdditionalPrivilegeV2.updateById({
+        actorId: req.permission.id,
+        actor: req.permission.type,
+        actorOrgId: req.permission.orgId,
+        actorAuthMethod: req.permission.authMethod,
+        id: req.params.id,
+        data: {
+          ...req.body,
+          ...req.body.type,
+          permissions: req.body.permissions || undefined
+        }
+      });
+      return { privilege };
+    }
+  });
+
+  server.route({
+    method: "DELETE",
+    url: "/:id",
+    config: {
+      rateLimit: writeLimit
+    },
+    schema: {
+      description: "Delete the specified identity privilege.",
+      security: [
+        {
+          bearerAuth: []
+        }
+      ],
+      params: z.object({
+        id: z.string().trim().describe(IDENTITY_ADDITIONAL_PRIVILEGE_V2.DELETE.id)
+      }),
+      response: {
+        200: z.object({
+          privilege: SanitizedIdentityPrivilegeSchema
+        })
+      }
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    handler: async (req) => {
+      const privilege = await server.services.identityProjectAdditionalPrivilegeV2.deleteById({
+        actorId: req.permission.id,
+        actor: req.permission.type,
+        actorAuthMethod: req.permission.authMethod,
+        actorOrgId: req.permission.orgId,
+        id: req.params.id
+      });
+      return { privilege };
+    }
+  });
+
+  server.route({
+    method: "GET",
+    url: "/:id",
+    config: {
+      rateLimit: readLimit
+    },
+    schema: {
+      description: "Retrieve details of a specific privilege by id.",
+      security: [
+        {
+          bearerAuth: []
+        }
+      ],
+      params: z.object({
+        id: z.string().min(1).describe(IDENTITY_ADDITIONAL_PRIVILEGE_V2.GET_BY_ID.id)
+      }),
+      response: {
+        200: z.object({
+          privilege: SanitizedIdentityPrivilegeSchema
+        })
+      }
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    handler: async (req) => {
+      const privilege = await server.services.identityProjectAdditionalPrivilegeV2.getPrivilegeDetailsById({
+        actorId: req.permission.id,
+        actorAuthMethod: req.permission.authMethod,
+        actor: req.permission.type,
+        actorOrgId: req.permission.orgId,
+        id: req.params.id
+      });
+      return { privilege };
+    }
+  });
+
+  server.route({
+    method: "GET",
+    url: "/slug/:privilegeSlug",
+    config: {
+      rateLimit: readLimit
+    },
+    schema: {
+      description: "Retrieve details of a specific privilege by slug.",
+      security: [
+        {
+          bearerAuth: []
+        }
+      ],
+      params: z.object({
+        privilegeSlug: z.string().min(1).describe(IDENTITY_ADDITIONAL_PRIVILEGE_V2.GET_BY_SLUG.slug)
+      }),
+      querystring: z.object({
+        identityId: z.string().min(1).describe(IDENTITY_ADDITIONAL_PRIVILEGE_V2.GET_BY_SLUG.identityId),
+        projectSlug: z.string().min(1).describe(IDENTITY_ADDITIONAL_PRIVILEGE_V2.GET_BY_SLUG.projectSlug)
+      }),
+      response: {
+        200: z.object({
+          privilege: SanitizedIdentityPrivilegeSchema
+        })
+      }
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    handler: async (req) => {
+      const privilege = await server.services.identityProjectAdditionalPrivilegeV2.getPrivilegeDetailsBySlug({
+        actorId: req.permission.id,
+        actorAuthMethod: req.permission.authMethod,
+        actor: req.permission.type,
+        actorOrgId: req.permission.orgId,
+        slug: req.params.privilegeSlug,
+        ...req.query
+      });
+      return { privilege };
+    }
+  });
+
+  server.route({
+    method: "GET",
+    url: "/",
+    config: {
+      rateLimit: readLimit
+    },
+    schema: {
+      description: "List privileges for the specified identity by project.",
+      security: [
+        {
+          bearerAuth: []
+        }
+      ],
+      querystring: z.object({
+        identityId: z.string().min(1).describe(IDENTITY_ADDITIONAL_PRIVILEGE_V2.LIST.identityId),
+        projectId: z.string().min(1).describe(IDENTITY_ADDITIONAL_PRIVILEGE_V2.LIST.projectId)
+      }),
+      response: {
+        200: z.object({
+          privileges: SanitizedIdentityPrivilegeSchema.omit({ permissions: true }).array()
+        })
+      }
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    handler: async (req) => {
+      const privileges = await server.services.identityProjectAdditionalPrivilegeV2.listIdentityProjectPrivileges({
+        actorId: req.permission.id,
+        actor: req.permission.type,
+        actorAuthMethod: req.permission.authMethod,
+        actorOrgId: req.permission.orgId,
+        ...req.query
+      });
+      return {
+        privileges
+      };
+    }
+  });
+};

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

@@ -0,0 +1,16 @@
+import { registerIdentityProjectAdditionalPrivilegeRouter } from "./identity-project-additional-privilege-router";
+import { registerProjectRoleRouter } from "./project-role-router";
+
+export const registerV2EERoutes = async (server: FastifyZodProvider) => {
+  // org role starts with organization
+  await server.register(
+    async (projectRouter) => {
+      await projectRouter.register(registerProjectRoleRouter);
+    },
+    { prefix: "/workspace" }
+  );
+
+  await server.register(registerIdentityProjectAdditionalPrivilegeRouter, {
+    prefix: "/identity-project-additional-privilege"
+  });
+};

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

@@ -0,0 +1,242 @@
+import { packRules } from "@casl/ability/extra";
+import slugify from "@sindresorhus/slugify";
+import { z } from "zod";
+
+import { ProjectMembershipRole, ProjectRolesSchema } from "@app/db/schemas";
+import { ProjectPermissionV2Schema } from "@app/ee/services/permission/project-permission";
+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 { SanitizedRoleSchema } from "@app/server/routes/sanitizedSchemas";
+import { AuthMode } from "@app/services/auth/auth-type";
+import { ProjectRoleServiceIdentifierType } from "@app/services/project-role/project-role-types";
+
+export const registerProjectRoleRouter = async (server: FastifyZodProvider) => {
+  server.route({
+    method: "POST",
+    url: "/:projectId/roles",
+    config: {
+      rateLimit: writeLimit
+    },
+    schema: {
+      description: "Create a project role",
+      security: [
+        {
+          bearerAuth: []
+        }
+      ],
+      params: z.object({
+        projectId: z.string().trim().describe(PROJECT_ROLE.CREATE.projectId)
+      }),
+      body: z.object({
+        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: ProjectPermissionV2Schema.array().describe(PROJECT_ROLE.CREATE.permissions)
+      }),
+      response: {
+        200: z.object({
+          role: SanitizedRoleSchema
+        })
+      }
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    handler: async (req) => {
+      const role = await server.services.projectRole.createRole({
+        actorAuthMethod: req.permission.authMethod,
+        actorId: req.permission.id,
+        actorOrgId: req.permission.orgId,
+        actor: req.permission.type,
+        filter: {
+          type: ProjectRoleServiceIdentifierType.ID,
+          projectId: req.params.projectId
+        },
+        data: {
+          ...req.body,
+          permissions: JSON.stringify(packRules(req.body.permissions))
+        }
+      });
+      return { role };
+    }
+  });
+
+  server.route({
+    method: "PATCH",
+    url: "/:projectId/roles/:roleId",
+    config: {
+      rateLimit: writeLimit
+    },
+    schema: {
+      description: "Update a project role",
+      security: [
+        {
+          bearerAuth: []
+        }
+      ],
+      params: z.object({
+        projectId: z.string().trim().describe(PROJECT_ROLE.UPDATE.projectId),
+        roleId: z.string().trim().describe(PROJECT_ROLE.UPDATE.roleId)
+      }),
+      body: z.object({
+        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),
+        description: z.string().trim().optional().describe(PROJECT_ROLE.UPDATE.description),
+        permissions: ProjectPermissionV2Schema.array().describe(PROJECT_ROLE.UPDATE.permissions).optional()
+      }),
+      response: {
+        200: z.object({
+          role: SanitizedRoleSchema
+        })
+      }
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    handler: async (req) => {
+      const role = await server.services.projectRole.updateRole({
+        actorAuthMethod: req.permission.authMethod,
+        actorId: req.permission.id,
+        actorOrgId: req.permission.orgId,
+        actor: req.permission.type,
+        roleId: req.params.roleId,
+        data: {
+          ...req.body,
+          permissions: req.body.permissions ? JSON.stringify(packRules(req.body.permissions)) : undefined
+        }
+      });
+      return { role };
+    }
+  });
+
+  server.route({
+    method: "DELETE",
+    url: "/:projectId/roles/:roleId",
+    config: {
+      rateLimit: writeLimit
+    },
+    schema: {
+      description: "Delete a project role",
+      security: [
+        {
+          bearerAuth: []
+        }
+      ],
+      params: z.object({
+        projectId: z.string().trim().describe(PROJECT_ROLE.DELETE.projectId),
+        roleId: z.string().trim().describe(PROJECT_ROLE.DELETE.roleId)
+      }),
+      response: {
+        200: z.object({
+          role: SanitizedRoleSchema
+        })
+      }
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    handler: async (req) => {
+      const role = await server.services.projectRole.deleteRole({
+        actorAuthMethod: req.permission.authMethod,
+        actorId: req.permission.id,
+        actorOrgId: req.permission.orgId,
+        actor: req.permission.type,
+        roleId: req.params.roleId
+      });
+      return { role };
+    }
+  });
+
+  server.route({
+    method: "GET",
+    url: "/:projectId/roles",
+    config: {
+      rateLimit: readLimit
+    },
+    schema: {
+      description: "List project role",
+      security: [
+        {
+          bearerAuth: []
+        }
+      ],
+      params: z.object({
+        projectId: z.string().trim().describe(PROJECT_ROLE.LIST.projectId)
+      }),
+      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,
+        filter: {
+          type: ProjectRoleServiceIdentifierType.ID,
+          projectId: req.params.projectId
+        }
+      });
+      return { roles };
+    }
+  });
+
+  server.route({
+    method: "GET",
+    url: "/:projectId/roles/slug/:roleSlug",
+    config: {
+      rateLimit: readLimit
+    },
+    schema: {
+      params: z.object({
+        projectId: z.string().trim().describe(PROJECT_ROLE.GET_ROLE_BY_SLUG.projectId),
+        roleSlug: z.string().trim().describe(PROJECT_ROLE.GET_ROLE_BY_SLUG.roleSlug)
+      }),
+      response: {
+        200: z.object({
+          role: SanitizedRoleSchema
+        })
+      }
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    handler: async (req) => {
+      const role = await server.services.projectRole.getRoleBySlug({
+        actorAuthMethod: req.permission.authMethod,
+        actorId: req.permission.id,
+        actorOrgId: req.permission.orgId,
+        actor: req.permission.type,
+        filter: {
+          type: ProjectRoleServiceIdentifierType.ID,
+          projectId: req.params.projectId
+        },
+        roleSlug: req.params.roleSlug
+      });
+      return { role };
+    }
+  });
+};

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

@@ -14,7 +14,7 @@ export const accessApprovalPolicyDALFactory = (db: TDbClient) => {
 
   const accessApprovalPolicyFindQuery = async (
     tx: Knex,
-    filter: TFindFilter<TAccessApprovalPolicies>,
+    filter: TFindFilter<TAccessApprovalPolicies & { projectId: string }>,
     customFilter?: {
       policyId?: string;
     }

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

@@ -1,36 +0,0 @@
-import { ForbiddenError, subject } from "@casl/ability";
-
-import { ActorType } from "@app/services/auth/auth-type";
-
-import { ProjectPermissionActions, ProjectPermissionSub } from "../permission/project-permission";
-import { TIsApproversValid } from "./access-approval-policy-types";
-
-export const isApproversValid = async ({
-  userIds,
-  projectId,
-  orgId,
-  envSlug,
-  actorAuthMethod,
-  secretPath,
-  permissionService
-}: TIsApproversValid) => {
-  try {
-    for await (const userId of userIds) {
-      const { permission: approverPermission } = await permissionService.getProjectPermission(
-        ActorType.USER,
-        userId,
-        projectId,
-        actorAuthMethod,
-        orgId
-      );
-
-      ForbiddenError.from(approverPermission).throwUnlessCan(
-        ProjectPermissionActions.Create,
-        subject(ProjectPermissionSub.Secrets, { environment: envSlug, secretPath })
-      );
-    }
-  } catch {
-    return false;
-  }
-  return true;
-};

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

@@ -11,7 +11,6 @@ import { TUserDALFactory } from "@app/services/user/user-dal";
 import { TGroupDALFactory } from "../group/group-dal";
 import { TAccessApprovalPolicyApproverDALFactory } from "./access-approval-policy-approver-dal";
 import { TAccessApprovalPolicyDALFactory } from "./access-approval-policy-dal";
-import { isApproversValid } from "./access-approval-policy-fns";
 import {
   ApproverType,
   TCreateAccessApprovalPolicy,
@@ -58,7 +57,7 @@ export const accessApprovalPolicyServiceFactory = ({
     enforcementLevel
   }: TCreateAccessApprovalPolicy) => {
     const project = await projectDAL.findProjectBySlug(projectSlug, actorOrgId);
-    if (!project) throw new NotFoundError({ message: "Project not found" });
+    if (!project) throw new NotFoundError({ message: `Project with slug '${projectSlug}' not found` });
 
     // If there is a group approver people might be added to the group later to meet the approvers quota
     const groupApprovers = approvers
@@ -89,7 +88,7 @@ export const accessApprovalPolicyServiceFactory = ({
       ProjectPermissionSub.SecretApproval
     );
     const env = await projectEnvDAL.findOne({ slug: environment, projectId: project.id });
-    if (!env) throw new NotFoundError({ message: "Environment not found" });
+    if (!env) throw new NotFoundError({ message: `Environment with slug '${environment}' not found` });
 
     let approverUserIds = userApprovers;
     if (userApproverNames.length) {
@@ -124,7 +123,9 @@ export const accessApprovalPolicyServiceFactory = ({
     const verifyAllApprovers = [...approverUserIds];
 
     for (const groupId of groupApprovers) {
-      usersPromises.push(groupDAL.findAllGroupPossibleMembers({ orgId: actorOrgId, groupId, offset: 0 }));
+      usersPromises.push(
+        groupDAL.findAllGroupPossibleMembers({ orgId: actorOrgId, groupId, offset: 0 }).then((group) => group.members)
+      );
     }
     const verifyGroupApprovers = (await Promise.all(usersPromises))
       .flat()
@@ -132,22 +133,6 @@ export const accessApprovalPolicyServiceFactory = ({
       .map((user) => user.id);
     verifyAllApprovers.push(...verifyGroupApprovers);
 
-    const approversValid = await isApproversValid({
-      projectId: project.id,
-      orgId: actorOrgId,
-      envSlug: environment,
-      secretPath,
-      actorAuthMethod,
-      permissionService,
-      userIds: verifyAllApprovers
-    });
-
-    if (!approversValid) {
-      throw new BadRequestError({
-        message: "One or more approvers doesn't have access to be specified secret path"
-      });
-    }
-
     const accessApproval = await accessApprovalPolicyDAL.transaction(async (tx) => {
       const doc = await accessApprovalPolicyDAL.create(
         {
@@ -192,7 +177,7 @@ export const accessApprovalPolicyServiceFactory = ({
     projectSlug
   }: TListAccessApprovalPoliciesDTO) => {
     const project = await projectDAL.findProjectBySlug(projectSlug, actorOrgId);
-    if (!project) throw new NotFoundError({ message: "Project not found" });
+    if (!project) throw new NotFoundError({ message: `Project with slug '${projectSlug}' not found` });
 
     // Anyone in the project should be able to get the policies.
     /* const { permission } = */ await permissionService.getProjectPermission(
@@ -243,7 +228,9 @@ export const accessApprovalPolicyServiceFactory = ({
       throw new BadRequestError({ message: "Approvals cannot be greater than approvers" });
     }
 
-    if (!accessApprovalPolicy) throw new NotFoundError({ message: "Secret approval policy not found" });
+    if (!accessApprovalPolicy) {
+      throw new NotFoundError({ message: `Secret approval policy with ID '${policyId}' not found` });
+    }
     const { permission } = await permissionService.getProjectPermission(
       actor,
       actorId,
@@ -289,22 +276,6 @@ export const accessApprovalPolicyServiceFactory = ({
           userApproverIds = userApproverIds.concat(approverUsers.map((user) => user.id));
         }
 
-        const approversValid = await isApproversValid({
-          projectId: accessApprovalPolicy.projectId,
-          orgId: actorOrgId,
-          envSlug: accessApprovalPolicy.environment.slug,
-          secretPath: doc.secretPath!,
-          actorAuthMethod,
-          permissionService,
-          userIds: userApproverIds
-        });
-
-        if (!approversValid) {
-          throw new BadRequestError({
-            message: "One or more approvers doesn't have access to be specified secret path"
-          });
-        }
-
         await accessApprovalPolicyApproverDAL.insertMany(
           userApproverIds.map((userId) => ({
             approverUserId: userId,
@@ -315,41 +286,6 @@ export const accessApprovalPolicyServiceFactory = ({
       }
 
       if (groupApprovers) {
-        const usersPromises: Promise<
-          {
-            id: string;
-            email: string | null | undefined;
-            username: string;
-            firstName: string | null | undefined;
-            lastName: string | null | undefined;
-            isPartOfGroup: boolean;
-          }[]
-        >[] = [];
-
-        for (const groupId of groupApprovers) {
-          usersPromises.push(groupDAL.findAllGroupPossibleMembers({ orgId: actorOrgId, groupId, offset: 0 }));
-        }
-        const verifyGroupApprovers = (await Promise.all(usersPromises))
-          .flat()
-          .filter((user) => user.isPartOfGroup)
-          .map((user) => user.id);
-
-        const approversValid = await isApproversValid({
-          projectId: accessApprovalPolicy.projectId,
-          orgId: actorOrgId,
-          envSlug: accessApprovalPolicy.environment.slug,
-          secretPath: doc.secretPath!,
-          actorAuthMethod,
-          permissionService,
-          userIds: verifyGroupApprovers
-        });
-
-        if (!approversValid) {
-          throw new BadRequestError({
-            message: "One or more approvers doesn't have access to be specified secret path"
-          });
-        }
-
         await accessApprovalPolicyApproverDAL.insertMany(
           groupApprovers.map((groupId) => ({
             approverGroupId: groupId,
@@ -376,7 +312,7 @@ export const accessApprovalPolicyServiceFactory = ({
     actorOrgId
   }: TDeleteAccessApprovalPolicy) => {
     const policy = await accessApprovalPolicyDAL.findById(policyId);
-    if (!policy) throw new NotFoundError({ message: "Secret approval policy not found" });
+    if (!policy) throw new NotFoundError({ message: `Secret approval policy with ID '${policyId}' not found` });
 
     const { permission } = await permissionService.getProjectPermission(
       actor,
@@ -404,7 +340,7 @@ export const accessApprovalPolicyServiceFactory = ({
   }: TGetAccessPolicyCountByEnvironmentDTO) => {
     const project = await projectDAL.findProjectBySlug(projectSlug, actorOrgId);
 
-    if (!project) throw new NotFoundError({ message: "Project not found" });
+    if (!project) throw new NotFoundError({ message: `Project with slug '${projectSlug}' not found` });
 
     const { membership } = await permissionService.getProjectPermission(
       actor,
@@ -418,10 +354,10 @@ export const accessApprovalPolicyServiceFactory = ({
     }
 
     const environment = await projectEnvDAL.findOne({ projectId: project.id, slug: envSlug });
-    if (!environment) throw new NotFoundError({ message: "Environment not found" });
+    if (!environment) throw new NotFoundError({ message: `Environment with slug '${envSlug}' not found` });
 
     const policies = await accessApprovalPolicyDAL.find({ envId: environment.id, projectId: project.id });
-    if (!policies) throw new NotFoundError({ message: "No policies found" });
+    if (!policies) throw new NotFoundError({ message: `No policies found in environment with slug '${envSlug}'` });
 
     return { count: policies.length };
   };
@@ -437,7 +373,7 @@ export const accessApprovalPolicyServiceFactory = ({
 
     if (!policy) {
       throw new NotFoundError({
-        message: "Cannot find access approval policy"
+        message: `Cannot find access approval policy with ID ${policyId}`
       });
     }
 

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

@@ -17,7 +17,6 @@ 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 { isApproversValid } from "../access-approval-policy/access-approval-policy-fns";
 import { TGroupDALFactory } from "../group/group-dal";
 import { TPermissionServiceFactory } from "../permission/permission-service";
 import { TProjectUserAdditionalPrivilegeDALFactory } from "../project-user-additional-privilege/project-user-additional-privilege-dal";
@@ -78,7 +77,6 @@ export const accessApprovalRequestServiceFactory = ({
   permissionService,
   accessApprovalRequestDAL,
   accessApprovalRequestReviewerDAL,
-  projectMembershipDAL,
   accessApprovalPolicyDAL,
   accessApprovalPolicyApproverDAL,
   additionalPrivilegeDAL,
@@ -99,7 +97,7 @@ export const accessApprovalRequestServiceFactory = ({
   }: TCreateAccessApprovalRequestDTO) => {
     const cfg = getConfig();
     const project = await projectDAL.findProjectBySlug(projectSlug, actorOrgId);
-    if (!project) throw new NotFoundError({ message: "Project not found" });
+    if (!project) throw new NotFoundError({ message: `Project with slug '${projectSlug}' not found` });
 
     // Anyone can create an access approval request.
     const { membership } = await permissionService.getProjectPermission(
@@ -121,13 +119,17 @@ export const accessApprovalRequestServiceFactory = ({
     const { envSlug, secretPath, accessTypes } = verifyRequestedPermissions({ permissions: requestedPermissions });
     const environment = await projectEnvDAL.findOne({ projectId: project.id, slug: envSlug });
 
-    if (!environment) throw new NotFoundError({ message: "Environment not found" });
+    if (!environment) throw new NotFoundError({ message: `Environment with slug '${envSlug}' not found` });
 
     const policy = await accessApprovalPolicyDAL.findOne({
       envId: environment.id,
       secretPath
     });
-    if (!policy) throw new NotFoundError({ message: "No policy matching criteria was found." });
+    if (!policy) {
+      throw new NotFoundError({
+        message: `No policy in environment with slug '${environment.slug}' and with secret path '${secretPath}' was found.`
+      });
+    }
 
     const approverIds: string[] = [];
     const approverGroupIds: string[] = [];
@@ -147,10 +149,12 @@ export const accessApprovalRequestServiceFactory = ({
     const groupUsers = (
       await Promise.all(
         approverGroupIds.map((groupApproverId) =>
-          groupDAL.findAllGroupPossibleMembers({
-            orgId: actorOrgId,
-            groupId: groupApproverId
-          })
+          groupDAL
+            .findAllGroupPossibleMembers({
+              orgId: actorOrgId,
+              groupId: groupApproverId
+            })
+            .then((group) => group.members)
         )
       )
     ).flat();
@@ -264,7 +268,7 @@ export const accessApprovalRequestServiceFactory = ({
     actorAuthMethod
   }: TListApprovalRequestsDTO) => {
     const project = await projectDAL.findProjectBySlug(projectSlug, actorOrgId);
-    if (!project) throw new NotFoundError({ message: "Project not found" });
+    if (!project) throw new NotFoundError({ message: `Project with slug '${projectSlug}' not found` });
 
     const { membership } = await permissionService.getProjectPermission(
       actor,
@@ -300,7 +304,9 @@ export const accessApprovalRequestServiceFactory = ({
     actorOrgId
   }: TReviewAccessRequestDTO) => {
     const accessApprovalRequest = await accessApprovalRequestDAL.findById(requestId);
-    if (!accessApprovalRequest) throw new NotFoundError({ message: "Secret approval request not found" });
+    if (!accessApprovalRequest) {
+      throw new NotFoundError({ message: `Secret approval request with ID '${requestId}' not found` });
+    }
 
     const { policy } = accessApprovalRequest;
     const { membership, hasRole } = await permissionService.getProjectPermission(
@@ -323,22 +329,6 @@ export const accessApprovalRequestServiceFactory = ({
       throw new ForbiddenRequestError({ message: "You are not authorized to approve this request" });
     }
 
-    const reviewerProjectMembership = await projectMembershipDAL.findById(membership.id);
-
-    const approversValid = await isApproversValid({
-      projectId: accessApprovalRequest.projectId,
-      orgId: actorOrgId,
-      envSlug: accessApprovalRequest.environment,
-      secretPath: accessApprovalRequest.policy.secretPath!,
-      actorAuthMethod,
-      permissionService,
-      userIds: [reviewerProjectMembership.userId]
-    });
-
-    if (!approversValid) {
-      throw new ForbiddenRequestError({ message: "You don't have access to approve this request" });
-    }
-
     const existingReviews = await accessApprovalRequestReviewerDAL.find({ requestId: accessApprovalRequest.id });
     if (existingReviews.some((review) => review.status === ApprovalStatus.REJECTED)) {
       throw new BadRequestError({ message: "The request has already been rejected by another reviewer" });
@@ -421,7 +411,7 @@ export const accessApprovalRequestServiceFactory = ({
 
   const getCount = async ({ projectSlug, actor, actorAuthMethod, actorId, actorOrgId }: TGetAccessRequestCountDTO) => {
     const project = await projectDAL.findProjectBySlug(projectSlug, actorOrgId);
-    if (!project) throw new NotFoundError({ message: "Project not found" });
+    if (!project) throw new NotFoundError({ message: `Project with slug '${projectSlug}' not found` });
 
     const { membership } = await permissionService.getProjectPermission(
       actor,

backend/src/ee/services/audit-log-stream/audit-log-stream-service.ts

@@ -130,7 +130,7 @@ export const auditLogStreamServiceFactory = ({
       });
 
     const logStream = await auditLogStreamDAL.findById(id);
-    if (!logStream) throw new NotFoundError({ message: "Audit log stream not found" });
+    if (!logStream) throw new NotFoundError({ message: `Audit log stream with ID '${id}' not found` });
 
     const { orgId } = logStream;
     const { permission } = await permissionService.getOrgPermission(actor, actorId, orgId, actorAuthMethod, actorOrgId);
@@ -182,7 +182,7 @@ export const auditLogStreamServiceFactory = ({
     if (!actorOrgId) throw new UnauthorizedError({ message: "No organization ID attached to authentication token" });
 
     const logStream = await auditLogStreamDAL.findById(id);
-    if (!logStream) throw new NotFoundError({ message: "Audit log stream not found" });
+    if (!logStream) throw new NotFoundError({ message: `Audit log stream with ID '${id}' not found` });
 
     const { orgId } = logStream;
     const { permission } = await permissionService.getOrgPermission(actor, actorId, orgId, actorAuthMethod, actorOrgId);
@@ -194,7 +194,7 @@ export const auditLogStreamServiceFactory = ({
 
   const getById = async ({ id, actor, actorId, actorOrgId, actorAuthMethod }: TGetDetailsAuditLogStreamDTO) => {
     const logStream = await auditLogStreamDAL.findById(id);
-    if (!logStream) throw new NotFoundError({ message: "Audit log stream not found" });
+    if (!logStream) throw new NotFoundError({ message: `Audit log stream with ID '${id}' not found` });
 
     const { orgId } = logStream;
     const { permission } = await permissionService.getOrgPermission(actor, actorId, orgId, actorAuthMethod, actorOrgId);

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

@@ -1,8 +1,9 @@
-import { Knex } from "knex";
+// weird commonjs-related error in the CI requires us to do the import like this
+import knex from "knex";
 
 import { TDbClient } from "@app/db";
-import { AuditLogsSchema, TableName } from "@app/db/schemas";
-import { DatabaseError } from "@app/lib/errors";
+import { TableName } from "@app/db/schemas";
+import { DatabaseError, GatewayTimeoutError } from "@app/lib/errors";
 import { ormify, selectAllTableCols } from "@app/lib/knex";
 import { logger } from "@app/lib/logger";
 import { QueueName } from "@app/queue";
@@ -46,7 +47,7 @@ export const auditLogDALFactory = (db: TDbClient) => {
       eventType?: EventType[];
       eventMetadata?: Record<string, string>;
     },
-    tx?: Knex
+    tx?: knex.Knex
   ) => {
     if (!orgId && !projectId) {
       throw new Error("Either orgId or projectId must be provided");
@@ -55,11 +56,10 @@ export const auditLogDALFactory = (db: TDbClient) => {
     try {
       // Find statements
       const sqlQuery = (tx || db.replicaNode())(TableName.AuditLog)
-        .leftJoin(TableName.Project, `${TableName.AuditLog}.projectId`, `${TableName.Project}.id`)
         // eslint-disable-next-line func-names
         .where(function () {
           if (orgId) {
-            void this.where(`${TableName.Project}.orgId`, orgId).orWhere(`${TableName.AuditLog}.orgId`, orgId);
+            void this.where(`${TableName.AuditLog}.orgId`, orgId);
           } else if (projectId) {
             void this.where(`${TableName.AuditLog}.projectId`, projectId);
           }
@@ -72,23 +72,19 @@ export const auditLogDALFactory = (db: TDbClient) => {
       // Select statements
       void sqlQuery
         .select(selectAllTableCols(TableName.AuditLog))
-        .select(
-          db.ref("name").withSchema(TableName.Project).as("projectName"),
-          db.ref("slug").withSchema(TableName.Project).as("projectSlug")
-        )
         .limit(limit)
         .offset(offset)
         .orderBy(`${TableName.AuditLog}.createdAt`, "desc");
 
       // Special case: Filter by actor ID
       if (actorId) {
-        void sqlQuery.whereRaw(`"actorMetadata"->>'userId' = ?`, [actorId]);
+        void sqlQuery.whereRaw(`"actorMetadata" @> jsonb_build_object('userId', ?::text)`, [actorId]);
       }
 
       // Special case: Filter by key/value pairs in eventMetadata field
       if (eventMetadata && Object.keys(eventMetadata).length) {
         Object.entries(eventMetadata).forEach(([key, value]) => {
-          void sqlQuery.whereRaw(`"eventMetadata"->>'${key}' = ?`, [value]);
+          void sqlQuery.whereRaw(`"eventMetadata" @> jsonb_build_object(?::text, ?::text)`, [key, value]);
         });
       }
 
@@ -109,30 +105,25 @@ export const auditLogDALFactory = (db: TDbClient) => {
       if (endDate) {
         void sqlQuery.where(`${TableName.AuditLog}.createdAt`, "<=", endDate);
       }
-      const docs = await sqlQuery;
 
-      return docs.map((doc) => {
-        // Our type system refuses to acknowledge that the project name and slug are present in the doc, due to the disjointed query structure above.
-        // This is a quick and dirty way to get around the types.
-        const projectDoc = doc as unknown as { projectName: string; projectSlug: string };
+      // we timeout long running queries to prevent DB resource issues (2 minutes)
+      const docs = await sqlQuery.timeout(1000 * 120);
 
-        return {
-          ...AuditLogsSchema.parse(doc),
-          ...(projectDoc?.projectSlug && {
-            project: {
-              name: projectDoc.projectName,
-              slug: projectDoc.projectSlug
-            }
-          })
-        };
-      });
+      return docs;
     } catch (error) {
+      if (error instanceof knex.KnexTimeoutError) {
+        throw new GatewayTimeoutError({
+          error,
+          message: "Failed to fetch audit logs due to timeout. Add more search filters."
+        });
+      }
+
       throw new DatabaseError({ error });
     }
   };
 
   // delete all audit log that have expired
-  const pruneAuditLog = async (tx?: Knex) => {
+  const pruneAuditLog = async (tx?: knex.Knex) => {
     const AUDIT_LOG_PRUNE_BATCH_SIZE = 10000;
     const MAX_RETRY_ON_FAILURE = 3;
 
@@ -148,6 +139,7 @@ export const auditLogDALFactory = (db: TDbClient) => {
           .where("expiresAt", "<", today)
           .select("id")
           .limit(AUDIT_LOG_PRUNE_BATCH_SIZE);
+
         // eslint-disable-next-line no-await-in-loop
         deletedAuditLogIds = await (tx || db)(TableName.AuditLog)
           .whereIn("id", findExpiredLogSubQuery)

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

@@ -74,6 +74,7 @@ export const auditLogQueueServiceFactory = ({
       actorMetadata: actor.metadata,
       userAgent,
       projectId,
+      projectName: project?.name,
       ipAddress,
       orgId,
       eventType: event.type,

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

@@ -190,7 +190,9 @@ export enum EventType {
   DELETE_CMEK = "delete-cmek",
   GET_CMEKS = "get-cmeks",
   CMEK_ENCRYPT = "cmek-encrypt",
-  CMEK_DECRYPT = "cmek-decrypt"
+  CMEK_DECRYPT = "cmek-decrypt",
+  UPDATE_EXTERNAL_GROUP_ORG_ROLE_MAPPINGS = "update-external-group-org-role-mapping",
+  GET_EXTERNAL_GROUP_ORG_ROLE_MAPPINGS = "get-external-group-org-role-mapping"
 }
 
 interface UserActorMetadata {
@@ -1604,6 +1606,18 @@ interface CmekDecryptEvent {
   };
 }
 
+interface GetExternalGroupOrgRoleMappingsEvent {
+  type: EventType.GET_EXTERNAL_GROUP_ORG_ROLE_MAPPINGS;
+  metadata?: Record<string, never>; // not needed, based off orgId
+}
+
+interface UpdateExternalGroupOrgRoleMappingsEvent {
+  type: EventType.UPDATE_EXTERNAL_GROUP_ORG_ROLE_MAPPINGS;
+  metadata: {
+    mappings: { groupName: string; roleSlug: string }[];
+  };
+}
+
 export type Event =
   | GetSecretsEvent
   | GetSecretEvent
@@ -1750,4 +1764,6 @@ export type Event =
   | DeleteCmekEvent
   | GetCmeksEvent
   | CmekEncryptEvent
-  | CmekDecryptEvent;
+  | CmekDecryptEvent
+  | GetExternalGroupOrgRoleMappingsEvent
+  | UpdateExternalGroupOrgRoleMappingsEvent;

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

@@ -34,7 +34,7 @@ export const certificateAuthorityCrlServiceFactory = ({
    */
   const getCrlById = async (crlId: TGetCrlById) => {
     const caCrl = await certificateAuthorityCrlDAL.findById(crlId);
-    if (!caCrl) throw new NotFoundError({ message: "CRL not found" });
+    if (!caCrl) throw new NotFoundError({ message: `CRL with ID '${crlId}' not found` });
 
     const ca = await certificateAuthorityDAL.findById(caCrl.caId);
 
@@ -64,7 +64,7 @@ export const certificateAuthorityCrlServiceFactory = ({
    */
   const getCaCrls = async ({ caId, actorId, actorAuthMethod, actor, actorOrgId }: TGetCaCrlsDTO) => {
     const ca = await certificateAuthorityDAL.findById(caId);
-    if (!ca) throw new NotFoundError({ message: "CA not found" });
+    if (!ca) throw new NotFoundError({ message: `CA with ID '${caId}' not found` });
 
     const { permission } = await permissionService.getProjectPermission(
       actor,

backend/src/ee/services/certificate-est/certificate-est-service.ts

@@ -211,7 +211,7 @@ export const certificateEstServiceFactory = ({
     const certTemplate = await certificateTemplateDAL.findById(certificateTemplateId);
     if (!certTemplate) {
       throw new NotFoundError({
-        message: "Certificate template not found"
+        message: `Certificate template with ID '${certificateTemplateId}' not found`
       });
     }
 
@@ -236,7 +236,7 @@ export const certificateEstServiceFactory = ({
     const ca = await certificateAuthorityDAL.findById(certTemplate.caId);
     if (!ca) {
       throw new NotFoundError({
-        message: "Certificate Authority not found"
+        message: `Certificate Authority with ID '${certTemplate.caId}' not found`
       });
     }
 

backend/src/ee/services/dynamic-secret-lease/dynamic-secret-lease-service.ts

@@ -4,7 +4,10 @@ import ms from "ms";
 import { SecretKeyEncoding } from "@app/db/schemas";
 import { TLicenseServiceFactory } from "@app/ee/services/license/license-service";
 import { TPermissionServiceFactory } from "@app/ee/services/permission/permission-service";
-import { ProjectPermissionActions, ProjectPermissionSub } from "@app/ee/services/permission/project-permission";
+import {
+  ProjectPermissionDynamicSecretActions,
+  ProjectPermissionSub
+} from "@app/ee/services/permission/project-permission";
 import { getConfig } from "@app/lib/config/env";
 import { infisicalSymmetricDecrypt } from "@app/lib/crypto/encryption";
 import { BadRequestError, NotFoundError } from "@app/lib/errors";
@@ -61,7 +64,7 @@ export const dynamicSecretLeaseServiceFactory = ({
   }: TCreateDynamicSecretLeaseDTO) => {
     const appCfg = getConfig();
     const project = await projectDAL.findProjectBySlug(projectSlug, actorOrgId);
-    if (!project) throw new NotFoundError({ message: "Project not found" });
+    if (!project) throw new NotFoundError({ message: `Project with slug '${projectSlug}' not found` });
 
     const projectId = project.id;
     const { permission } = await permissionService.getProjectPermission(
@@ -72,8 +75,8 @@ export const dynamicSecretLeaseServiceFactory = ({
       actorOrgId
     );
     ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionActions.Read,
-      subject(ProjectPermissionSub.Secrets, { environment: environmentSlug, secretPath: path })
+      ProjectPermissionDynamicSecretActions.Lease,
+      subject(ProjectPermissionSub.DynamicSecrets, { environment: environmentSlug, secretPath: path })
     );
 
     const plan = await licenseService.getPlan(actorOrgId);
@@ -84,10 +87,16 @@ export const dynamicSecretLeaseServiceFactory = ({
     }
 
     const folder = await folderDAL.findBySecretPath(projectId, environmentSlug, path);
-    if (!folder) throw new NotFoundError({ message: "Folder not found" });
+    if (!folder)
+      throw new NotFoundError({
+        message: `Folder with path '${path}' in environment with slug '${environmentSlug}' not found`
+      });
 
     const dynamicSecretCfg = await dynamicSecretDAL.findOne({ name, folderId: folder.id });
-    if (!dynamicSecretCfg) throw new NotFoundError({ message: "Dynamic secret not found" });
+    if (!dynamicSecretCfg)
+      throw new NotFoundError({
+        message: `Dynamic secret with name '${name}' in folder with path '${path}' not found`
+      });
 
     const totalLeasesTaken = await dynamicSecretLeaseDAL.countLeasesForDynamicSecret(dynamicSecretCfg.id);
     if (totalLeasesTaken >= appCfg.MAX_LEASE_LIMIT)
@@ -134,7 +143,7 @@ export const dynamicSecretLeaseServiceFactory = ({
     leaseId
   }: TRenewDynamicSecretLeaseDTO) => {
     const project = await projectDAL.findProjectBySlug(projectSlug, actorOrgId);
-    if (!project) throw new NotFoundError({ message: "Project not found" });
+    if (!project) throw new NotFoundError({ message: `Project with slug '${projectSlug}' not found` });
 
     const projectId = project.id;
     const { permission } = await permissionService.getProjectPermission(
@@ -145,8 +154,8 @@ export const dynamicSecretLeaseServiceFactory = ({
       actorOrgId
     );
     ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionActions.Edit,
-      subject(ProjectPermissionSub.Secrets, { environment: environmentSlug, secretPath: path })
+      ProjectPermissionDynamicSecretActions.Lease,
+      subject(ProjectPermissionSub.DynamicSecrets, { environment: environmentSlug, secretPath: path })
     );
 
     const plan = await licenseService.getPlan(actorOrgId);
@@ -157,10 +166,15 @@ export const dynamicSecretLeaseServiceFactory = ({
     }
 
     const folder = await folderDAL.findBySecretPath(projectId, environmentSlug, path);
-    if (!folder) throw new NotFoundError({ message: "Folder not found" });
+    if (!folder)
+      throw new NotFoundError({
+        message: `Folder with path '${path}' in environment with slug '${environmentSlug}' not found`
+      });
 
     const dynamicSecretLease = await dynamicSecretLeaseDAL.findById(leaseId);
-    if (!dynamicSecretLease) throw new NotFoundError({ message: "Dynamic secret lease not found" });
+    if (!dynamicSecretLease) {
+      throw new NotFoundError({ message: `Dynamic secret lease with ID '${leaseId}' not found` });
+    }
 
     const dynamicSecretCfg = dynamicSecretLease.dynamicSecret;
     const selectedProvider = dynamicSecretProviders[dynamicSecretCfg.type as DynamicSecretProviders];
@@ -208,7 +222,7 @@ export const dynamicSecretLeaseServiceFactory = ({
     isForced
   }: TDeleteDynamicSecretLeaseDTO) => {
     const project = await projectDAL.findProjectBySlug(projectSlug, actorOrgId);
-    if (!project) throw new NotFoundError({ message: "Project not found" });
+    if (!project) throw new NotFoundError({ message: `Project with slug '${projectSlug}' not found` });
 
     const projectId = project.id;
     const { permission } = await permissionService.getProjectPermission(
@@ -219,15 +233,19 @@ export const dynamicSecretLeaseServiceFactory = ({
       actorOrgId
     );
     ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionActions.Delete,
-      subject(ProjectPermissionSub.Secrets, { environment: environmentSlug, secretPath: path })
+      ProjectPermissionDynamicSecretActions.Lease,
+      subject(ProjectPermissionSub.DynamicSecrets, { environment: environmentSlug, secretPath: path })
     );
 
     const folder = await folderDAL.findBySecretPath(projectId, environmentSlug, path);
-    if (!folder) throw new NotFoundError({ message: "Folder not found" });
+    if (!folder)
+      throw new NotFoundError({
+        message: `Folder with path '${path}' in environment with slug '${environmentSlug}' not found`
+      });
 
     const dynamicSecretLease = await dynamicSecretLeaseDAL.findById(leaseId);
-    if (!dynamicSecretLease) throw new NotFoundError({ message: "Dynamic secret lease not found" });
+    if (!dynamicSecretLease)
+      throw new NotFoundError({ message: `Dynamic secret lease with ID '${leaseId}' not found` });
 
     const dynamicSecretCfg = dynamicSecretLease.dynamicSecret;
     const selectedProvider = dynamicSecretProviders[dynamicSecretCfg.type as DynamicSecretProviders];
@@ -273,7 +291,7 @@ export const dynamicSecretLeaseServiceFactory = ({
     actorAuthMethod
   }: TListDynamicSecretLeasesDTO) => {
     const project = await projectDAL.findProjectBySlug(projectSlug, actorOrgId);
-    if (!project) throw new NotFoundError({ message: "Project not found" });
+    if (!project) throw new NotFoundError({ message: `Project with slug '${projectSlug}' not found` });
 
     const projectId = project.id;
     const { permission } = await permissionService.getProjectPermission(
@@ -284,15 +302,21 @@ export const dynamicSecretLeaseServiceFactory = ({
       actorOrgId
     );
     ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionActions.Read,
-      subject(ProjectPermissionSub.Secrets, { environment: environmentSlug, secretPath: path })
+      ProjectPermissionDynamicSecretActions.Lease,
+      subject(ProjectPermissionSub.DynamicSecrets, { environment: environmentSlug, secretPath: path })
     );
 
     const folder = await folderDAL.findBySecretPath(projectId, environmentSlug, path);
-    if (!folder) throw new NotFoundError({ message: "Folder not found" });
+    if (!folder)
+      throw new NotFoundError({
+        message: `Folder with path '${path}' in environment with slug '${environmentSlug}' not found`
+      });
 
     const dynamicSecretCfg = await dynamicSecretDAL.findOne({ name, folderId: folder.id });
-    if (!dynamicSecretCfg) throw new NotFoundError({ message: "Dynamic secret not found" });
+    if (!dynamicSecretCfg)
+      throw new NotFoundError({
+        message: `Dynamic secret with name '${name}' in folder with path '${path}' not found`
+      });
 
     const dynamicSecretLeases = await dynamicSecretLeaseDAL.find({ dynamicSecretId: dynamicSecretCfg.id });
     return dynamicSecretLeases;
@@ -309,7 +333,7 @@ export const dynamicSecretLeaseServiceFactory = ({
     actorAuthMethod
   }: TDetailsDynamicSecretLeaseDTO) => {
     const project = await projectDAL.findProjectBySlug(projectSlug, actorOrgId);
-    if (!project) throw new NotFoundError({ message: "Project not found" });
+    if (!project) throw new NotFoundError({ message: `Project with slug '${projectSlug}' not found` });
 
     const projectId = project.id;
     const { permission } = await permissionService.getProjectPermission(
@@ -320,15 +344,16 @@ export const dynamicSecretLeaseServiceFactory = ({
       actorOrgId
     );
     ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionActions.Read,
-      subject(ProjectPermissionSub.Secrets, { environment: environmentSlug, secretPath: path })
+      ProjectPermissionDynamicSecretActions.Lease,
+      subject(ProjectPermissionSub.DynamicSecrets, { environment: environmentSlug, secretPath: path })
     );
 
     const folder = await folderDAL.findBySecretPath(projectId, environmentSlug, path);
-    if (!folder) throw new NotFoundError({ message: "Folder not found" });
+    if (!folder) throw new NotFoundError({ message: `Folder with path '${path}' not found` });
 
     const dynamicSecretLease = await dynamicSecretLeaseDAL.findById(leaseId);
-    if (!dynamicSecretLease) throw new NotFoundError({ message: "Dynamic secret lease not found" });
+    if (!dynamicSecretLease)
+      throw new NotFoundError({ message: `Dynamic secret lease with ID '${leaseId}' not found` });
 
     return dynamicSecretLease;
   };

backend/src/ee/services/dynamic-secret/dynamic-secret-fns.ts

@@ -0,0 +1,20 @@
+import { getConfig } from "@app/lib/config/env";
+import { BadRequestError } from "@app/lib/errors";
+import { getDbConnectionHost } from "@app/lib/knex";
+
+export const verifyHostInputValidity = (host: string) => {
+  const appCfg = getConfig();
+  const dbHost = appCfg.DB_HOST || getDbConnectionHost(appCfg.DB_CONNECTION_URI);
+
+  if (
+    appCfg.isCloud &&
+    // localhost
+    // internal ips
+    (host === "host.docker.internal" || host.match(/^10\.\d+\.\d+\.\d+/) || host.match(/^192\.168\.\d+\.\d+/))
+  )
+    throw new BadRequestError({ message: "Invalid db host" });
+
+  if (host === "localhost" || host === "127.0.0.1" || dbHost === host) {
+    throw new BadRequestError({ message: "Invalid db host" });
+  }
+};

backend/src/ee/services/dynamic-secret/dynamic-secret-service.ts

@@ -3,10 +3,13 @@ import { ForbiddenError, subject } from "@casl/ability";
 import { SecretKeyEncoding } from "@app/db/schemas";
 import { TLicenseServiceFactory } from "@app/ee/services/license/license-service";
 import { TPermissionServiceFactory } from "@app/ee/services/permission/permission-service";
-import { ProjectPermissionActions, ProjectPermissionSub } from "@app/ee/services/permission/project-permission";
+import {
+  ProjectPermissionDynamicSecretActions,
+  ProjectPermissionSub
+} from "@app/ee/services/permission/project-permission";
 import { infisicalSymmetricDecrypt, infisicalSymmetricEncypt } from "@app/lib/crypto/encryption";
 import { BadRequestError, NotFoundError } from "@app/lib/errors";
-import { OrderByDirection } from "@app/lib/types";
+import { OrderByDirection, ProjectServiceActor } from "@app/lib/types";
 import { TProjectDALFactory } from "@app/services/project/project-dal";
 import { TSecretFolderDALFactory } from "@app/services/secret-folder/secret-folder-dal";
 
@@ -19,6 +22,7 @@ import {
   TDeleteDynamicSecretDTO,
   TDetailsDynamicSecretDTO,
   TGetDynamicSecretsCountDTO,
+  TListDynamicSecretsByFolderMappingsDTO,
   TListDynamicSecretsDTO,
   TListDynamicSecretsMultiEnvDTO,
   TUpdateDynamicSecretDTO
@@ -66,7 +70,7 @@ export const dynamicSecretServiceFactory = ({
     actorAuthMethod
   }: TCreateDynamicSecretDTO) => {
     const project = await projectDAL.findProjectBySlug(projectSlug, actorOrgId);
-    if (!project) throw new NotFoundError({ message: "Project not found" });
+    if (!project) throw new NotFoundError({ message: `Project with slug '${projectSlug}' not found` });
 
     const projectId = project.id;
     const { permission } = await permissionService.getProjectPermission(
@@ -77,8 +81,8 @@ export const dynamicSecretServiceFactory = ({
       actorOrgId
     );
     ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionActions.Create,
-      subject(ProjectPermissionSub.Secrets, { environment: environmentSlug, secretPath: path })
+      ProjectPermissionDynamicSecretActions.CreateRootCredential,
+      subject(ProjectPermissionSub.DynamicSecrets, { environment: environmentSlug, secretPath: path })
     );
 
     const plan = await licenseService.getPlan(actorOrgId);
@@ -89,7 +93,9 @@ export const dynamicSecretServiceFactory = ({
     }
 
     const folder = await folderDAL.findBySecretPath(projectId, environmentSlug, path);
-    if (!folder) throw new NotFoundError({ message: "Folder not found" });
+    if (!folder) {
+      throw new NotFoundError({ message: `Folder with path '${path}' in environment '${environmentSlug}' not found` });
+    }
 
     const existingDynamicSecret = await dynamicSecretDAL.findOne({ name, folderId: folder.id });
     if (existingDynamicSecret)
@@ -134,7 +140,7 @@ export const dynamicSecretServiceFactory = ({
     actorAuthMethod
   }: TUpdateDynamicSecretDTO) => {
     const project = await projectDAL.findProjectBySlug(projectSlug, actorOrgId);
-    if (!project) throw new NotFoundError({ message: "Project not found" });
+    if (!project) throw new NotFoundError({ message: `Project with slug '${projectSlug}' not found` });
 
     const projectId = project.id;
 
@@ -146,8 +152,8 @@ export const dynamicSecretServiceFactory = ({
       actorOrgId
     );
     ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionActions.Edit,
-      subject(ProjectPermissionSub.Secrets, { environment: environmentSlug, secretPath: path })
+      ProjectPermissionDynamicSecretActions.EditRootCredential,
+      subject(ProjectPermissionSub.DynamicSecrets, { environment: environmentSlug, secretPath: path })
     );
 
     const plan = await licenseService.getPlan(actorOrgId);
@@ -158,11 +164,15 @@ export const dynamicSecretServiceFactory = ({
     }
 
     const folder = await folderDAL.findBySecretPath(projectId, environmentSlug, path);
-    if (!folder) throw new NotFoundError({ message: "Folder not found" });
+    if (!folder)
+      throw new NotFoundError({ message: `Folder with path '${path}' in environment '${environmentSlug}' not found` });
 
     const dynamicSecretCfg = await dynamicSecretDAL.findOne({ name, folderId: folder.id });
-    if (!dynamicSecretCfg) throw new NotFoundError({ message: "Dynamic secret not found" });
-
+    if (!dynamicSecretCfg) {
+      throw new NotFoundError({
+        message: `Dynamic secret with name '${name}' in folder '${folder.path}' not found`
+      });
+    }
     if (newName) {
       const existingDynamicSecret = await dynamicSecretDAL.findOne({ name: newName, folderId: folder.id });
       if (existingDynamicSecret)
@@ -213,7 +223,7 @@ export const dynamicSecretServiceFactory = ({
     isForced
   }: TDeleteDynamicSecretDTO) => {
     const project = await projectDAL.findProjectBySlug(projectSlug, actorOrgId);
-    if (!project) throw new NotFoundError({ message: "Project not found" });
+    if (!project) throw new NotFoundError({ message: `Project with slug '${projectSlug}' not found` });
 
     const projectId = project.id;
 
@@ -225,15 +235,18 @@ export const dynamicSecretServiceFactory = ({
       actorOrgId
     );
     ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionActions.Edit,
-      subject(ProjectPermissionSub.Secrets, { environment: environmentSlug, secretPath: path })
+      ProjectPermissionDynamicSecretActions.DeleteRootCredential,
+      subject(ProjectPermissionSub.DynamicSecrets, { environment: environmentSlug, secretPath: path })
     );
 
     const folder = await folderDAL.findBySecretPath(projectId, environmentSlug, path);
-    if (!folder) throw new NotFoundError({ message: "Folder not found" });
+    if (!folder)
+      throw new NotFoundError({ message: `Folder with path '${path}' in environment '${environmentSlug}' not found` });
 
     const dynamicSecretCfg = await dynamicSecretDAL.findOne({ name, folderId: folder.id });
-    if (!dynamicSecretCfg) throw new BadRequestError({ message: "Dynamic secret not found" });
+    if (!dynamicSecretCfg) {
+      throw new NotFoundError({ message: `Dynamic secret with name '${name}' in folder '${folder.path}' not found` });
+    }
 
     const leases = await dynamicSecretLeaseDAL.find({ dynamicSecretId: dynamicSecretCfg.id });
     // when not forced we check with the external system to first remove the things
@@ -271,7 +284,7 @@ export const dynamicSecretServiceFactory = ({
     actor
   }: TDetailsDynamicSecretDTO) => {
     const project = await projectDAL.findProjectBySlug(projectSlug, actorOrgId);
-    if (!project) throw new NotFoundError({ message: "Project not found" });
+    if (!project) throw new NotFoundError({ message: `Project with slug '${projectSlug}' not found` });
 
     const projectId = project.id;
     const { permission } = await permissionService.getProjectPermission(
@@ -282,15 +295,22 @@ export const dynamicSecretServiceFactory = ({
       actorOrgId
     );
     ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionActions.Edit,
-      subject(ProjectPermissionSub.Secrets, { environment: environmentSlug, secretPath: path })
+      ProjectPermissionDynamicSecretActions.ReadRootCredential,
+      subject(ProjectPermissionSub.DynamicSecrets, { environment: environmentSlug, secretPath: path })
+    );
+    ForbiddenError.from(permission).throwUnlessCan(
+      ProjectPermissionDynamicSecretActions.EditRootCredential,
+      subject(ProjectPermissionSub.DynamicSecrets, { environment: environmentSlug, secretPath: path })
     );
 
     const folder = await folderDAL.findBySecretPath(projectId, environmentSlug, path);
-    if (!folder) throw new NotFoundError({ message: "Folder not found" });
+    if (!folder)
+      throw new NotFoundError({ message: `Folder with path '${path}' in environment '${environmentSlug}' not found` });
 
     const dynamicSecretCfg = await dynamicSecretDAL.findOne({ name, folderId: folder.id });
-    if (!dynamicSecretCfg) throw new NotFoundError({ message: "Dynamic secret not found" });
+    if (!dynamicSecretCfg) {
+      throw new NotFoundError({ message: `Dynamic secret with name '${name} in folder '${path}' not found` });
+    }
     const decryptedStoredInput = JSON.parse(
       infisicalSymmetricDecrypt({
         keyEncoding: dynamicSecretCfg.keyEncoding as SecretKeyEncoding,
@@ -328,14 +348,18 @@ export const dynamicSecretServiceFactory = ({
       // verify user has access to each env in request
       environmentSlugs.forEach((environmentSlug) =>
         ForbiddenError.from(permission).throwUnlessCan(
-          ProjectPermissionActions.Read,
-          subject(ProjectPermissionSub.Secrets, { environment: environmentSlug, secretPath: path })
+          ProjectPermissionDynamicSecretActions.ReadRootCredential,
+          subject(ProjectPermissionSub.DynamicSecrets, { environment: environmentSlug, secretPath: path })
         )
       );
     }
 
     const folders = await folderDAL.findBySecretPathMultiEnv(projectId, environmentSlugs, path);
-    if (!folders.length) throw new NotFoundError({ message: "Folders not found" });
+    if (!folders.length) {
+      throw new NotFoundError({
+        message: `Folders with path '${path}' in environments with slugs '${environmentSlugs.join(", ")}' not found`
+      });
+    }
 
     const dynamicSecretCfg = await dynamicSecretDAL.find(
       { $in: { folderId: folders.map((folder) => folder.id) }, $search: search ? { name: `%${search}%` } : undefined },
@@ -364,12 +388,14 @@ export const dynamicSecretServiceFactory = ({
       actorOrgId
     );
     ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionActions.Read,
-      subject(ProjectPermissionSub.Secrets, { environment: environmentSlug, secretPath: path })
+      ProjectPermissionDynamicSecretActions.ReadRootCredential,
+      subject(ProjectPermissionSub.DynamicSecrets, { environment: environmentSlug, secretPath: path })
     );
 
     const folder = await folderDAL.findBySecretPath(projectId, environmentSlug, path);
-    if (!folder) throw new NotFoundError({ message: "Folder not found" });
+    if (!folder) {
+      throw new NotFoundError({ message: `Folder with path '${path}' in environment '${environmentSlug}' not found` });
+    }
 
     const dynamicSecretCfg = await dynamicSecretDAL.find(
       { folderId: folder.id, $search: search ? { name: `%${search}%` } : undefined },
@@ -398,7 +424,7 @@ export const dynamicSecretServiceFactory = ({
     if (!projectId) {
       if (!projectSlug) throw new BadRequestError({ message: "Project ID or slug required" });
       const project = await projectDAL.findProjectBySlug(projectSlug, actorOrgId);
-      if (!project) throw new NotFoundError({ message: "Project not found" });
+      if (!project) throw new NotFoundError({ message: `Project with slug '${projectSlug}' not found` });
       projectId = project.id;
     }
 
@@ -410,12 +436,13 @@ export const dynamicSecretServiceFactory = ({
       actorOrgId
     );
     ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionActions.Read,
-      subject(ProjectPermissionSub.Secrets, { environment: environmentSlug, secretPath: path })
+      ProjectPermissionDynamicSecretActions.ReadRootCredential,
+      subject(ProjectPermissionSub.DynamicSecrets, { environment: environmentSlug, secretPath: path })
     );
 
     const folder = await folderDAL.findBySecretPath(projectId, environmentSlug, path);
-    if (!folder) throw new NotFoundError({ message: "Folder not found" });
+    if (!folder)
+      throw new NotFoundError({ message: `Folder with path '${path}' in environment '${environmentSlug}' not found` });
 
     const dynamicSecretCfg = await dynamicSecretDAL.find(
       { folderId: folder.id, $search: search ? { name: `%${search}%` } : undefined },
@@ -428,8 +455,44 @@ export const dynamicSecretServiceFactory = ({
     return dynamicSecretCfg;
   };
 
+  const listDynamicSecretsByFolderIds = async (
+    { folderMappings, filters, projectId }: TListDynamicSecretsByFolderMappingsDTO,
+    actor: ProjectServiceActor
+  ) => {
+    const { permission } = await permissionService.getProjectPermission(
+      actor.type,
+      actor.id,
+      projectId,
+      actor.authMethod,
+      actor.orgId
+    );
+
+    const userAccessibleFolderMappings = folderMappings.filter(({ path, environment }) =>
+      permission.can(
+        ProjectPermissionDynamicSecretActions.ReadRootCredential,
+        subject(ProjectPermissionSub.DynamicSecrets, { environment, secretPath: path })
+      )
+    );
+
+    const groupedFolderMappings = new Map(userAccessibleFolderMappings.map((path) => [path.folderId, path]));
+
+    const dynamicSecrets = await dynamicSecretDAL.listDynamicSecretsByFolderIds({
+      folderIds: userAccessibleFolderMappings.map(({ folderId }) => folderId),
+      ...filters
+    });
+
+    return dynamicSecrets.map((dynamicSecret) => {
+      const { environment, path } = groupedFolderMappings.get(dynamicSecret.folderId)!;
+      return {
+        ...dynamicSecret,
+        environment,
+        path
+      };
+    });
+  };
+
   // get dynamic secrets for multiple envs
-  const listDynamicSecretsByFolderIds = async ({
+  const listDynamicSecretsByEnvs = async ({
     actorAuthMethod,
     actorOrgId,
     actorId,
@@ -452,14 +515,17 @@ export const dynamicSecretServiceFactory = ({
       // verify user has access to each env in request
       environmentSlugs.forEach((environmentSlug) =>
         ForbiddenError.from(permission).throwUnlessCan(
-          ProjectPermissionActions.Read,
-          subject(ProjectPermissionSub.Secrets, { environment: environmentSlug, secretPath: path })
+          ProjectPermissionDynamicSecretActions.ReadRootCredential,
+          subject(ProjectPermissionSub.DynamicSecrets, { environment: environmentSlug, secretPath: path })
         )
       );
     }
 
     const folders = await folderDAL.findBySecretPathMultiEnv(projectId, environmentSlugs, path);
-    if (!folders.length) throw new NotFoundError({ message: "Folders not found" });
+    if (!folders.length)
+      throw new NotFoundError({
+        message: `Folders with path '${path} in environments with slugs '${environmentSlugs.join(", ")}' not found`
+      });
 
     const dynamicSecretCfg = await dynamicSecretDAL.listDynamicSecretsByFolderIds({
       folderIds: folders.map((folder) => folder.id),
@@ -492,9 +558,10 @@ export const dynamicSecretServiceFactory = ({
     deleteByName,
     getDetails,
     listDynamicSecretsByEnv,
-    listDynamicSecretsByFolderIds,
+    listDynamicSecretsByEnvs,
     getDynamicSecretCount,
     getCountMultiEnv,
-    fetchAzureEntraIdUsers
+    fetchAzureEntraIdUsers,
+    listDynamicSecretsByFolderIds
   };
 };

backend/src/ee/services/dynamic-secret/dynamic-secret-types.ts

@@ -48,17 +48,27 @@ export type TDetailsDynamicSecretDTO = {
   projectSlug: string;
 } & Omit<TProjectPermission, "projectId">;
 
-export type TListDynamicSecretsDTO = {
-  path: string;
-  environmentSlug: string;
-  projectSlug?: string;
-  projectId?: string;
+export type ListDynamicSecretsFilters = {
   offset?: number;
   limit?: number;
   orderBy?: SecretsOrderBy;
   orderDirection?: OrderByDirection;
   search?: string;
-} & Omit<TProjectPermission, "projectId">;
+};
+
+export type TListDynamicSecretsDTO = {
+  path: string;
+  environmentSlug: string;
+  projectSlug?: string;
+  projectId?: string;
+} & ListDynamicSecretsFilters &
+  Omit<TProjectPermission, "projectId">;
+
+export type TListDynamicSecretsByFolderMappingsDTO = {
+  projectId: string;
+  folderMappings: { folderId: string; path: string; environment: string }[];
+  filters: ListDynamicSecretsFilters;
+};
 
 export type TListDynamicSecretsMultiEnvDTO = Omit<
   TListDynamicSecretsDTO,

backend/src/ee/services/dynamic-secret/providers/elastic-search.ts

@@ -2,10 +2,9 @@ import { Client as ElasticSearchClient } from "@elastic/elasticsearch";
 import { customAlphabet } from "nanoid";
 import { z } from "zod";
 
-import { getConfig } from "@app/lib/config/env";
-import { BadRequestError } from "@app/lib/errors";
 import { alphaNumericNanoId } from "@app/lib/nanoid";
 
+import { verifyHostInputValidity } from "../dynamic-secret-fns";
 import { DynamicSecretElasticSearchSchema, ElasticSearchAuthTypes, TDynamicProviderFns } from "./models";
 
 const generatePassword = () => {
@@ -19,23 +18,8 @@ const generateUsername = () => {
 
 export const ElasticSearchProvider = (): TDynamicProviderFns => {
   const validateProviderInputs = async (inputs: unknown) => {
-    const appCfg = getConfig();
-    const isCloud = Boolean(appCfg.LICENSE_SERVER_KEY); // quick and dirty way to check if its cloud or not
-
     const providerInputs = await DynamicSecretElasticSearchSchema.parseAsync(inputs);
-    if (
-      isCloud &&
-      // localhost
-      // internal ips
-      (providerInputs.host === "host.docker.internal" ||
-        providerInputs.host.match(/^10\.\d+\.\d+\.\d+/) ||
-        providerInputs.host.match(/^192\.168\.\d+\.\d+/))
-    ) {
-      throw new BadRequestError({ message: "Invalid db host" });
-    }
-    if (providerInputs.host === "localhost" || providerInputs.host === "127.0.0.1") {
-      throw new BadRequestError({ message: "Invalid db host" });
-    }
+    verifyHostInputValidity(providerInputs.host);
 
     return providerInputs;
   };

backend/src/ee/services/dynamic-secret/providers/index.ts

@@ -1,3 +1,5 @@
+import { SnowflakeProvider } from "@app/ee/services/dynamic-secret/providers/snowflake";
+
 import { AwsElastiCacheDatabaseProvider } from "./aws-elasticache";
 import { AwsIamProvider } from "./aws-iam";
 import { AzureEntraIDProvider } from "./azure-entra-id";
@@ -9,6 +11,7 @@ import { MongoAtlasProvider } from "./mongo-atlas";
 import { MongoDBProvider } from "./mongo-db";
 import { RabbitMqProvider } from "./rabbit-mq";
 import { RedisDatabaseProvider } from "./redis";
+import { SapHanaProvider } from "./sap-hana";
 import { SqlDatabaseProvider } from "./sql-database";
 
 export const buildDynamicSecretProviders = () => ({
@@ -22,5 +25,7 @@ export const buildDynamicSecretProviders = () => ({
   [DynamicSecretProviders.ElasticSearch]: ElasticSearchProvider(),
   [DynamicSecretProviders.RabbitMq]: RabbitMqProvider(),
   [DynamicSecretProviders.AzureEntraID]: AzureEntraIDProvider(),
-  [DynamicSecretProviders.Ldap]: LdapProvider()
+  [DynamicSecretProviders.Ldap]: LdapProvider(),
+  [DynamicSecretProviders.SapHana]: SapHanaProvider(),
+  [DynamicSecretProviders.Snowflake]: SnowflakeProvider()
 });

backend/src/ee/services/dynamic-secret/providers/models.ts

@@ -166,6 +166,27 @@ export const DynamicSecretMongoDBSchema = z.object({
     )
 });
 
+export const DynamicSecretSapHanaSchema = z.object({
+  host: z.string().trim().toLowerCase(),
+  port: z.number(),
+  username: z.string().trim(),
+  password: z.string().trim(),
+  creationStatement: z.string().trim(),
+  revocationStatement: z.string().trim(),
+  renewStatement: z.string().trim().optional(),
+  ca: z.string().optional()
+});
+
+export const DynamicSecretSnowflakeSchema = z.object({
+  accountId: z.string().trim().min(1),
+  orgId: z.string().trim().min(1),
+  username: z.string().trim().min(1),
+  password: z.string().trim().min(1),
+  creationStatement: z.string().trim().min(1),
+  revocationStatement: z.string().trim().min(1),
+  renewStatement: z.string().trim().optional()
+});
+
 export const AzureEntraIDSchema = z.object({
   tenantId: z.string().trim().min(1),
   userId: z.string().trim().min(1),
@@ -196,7 +217,9 @@ export enum DynamicSecretProviders {
   MongoDB = "mongo-db",
   RabbitMq = "rabbit-mq",
   AzureEntraID = "azure-entra-id",
-  Ldap = "ldap"
+  Ldap = "ldap",
+  SapHana = "sap-hana",
+  Snowflake = "snowflake"
 }
 
 export const DynamicSecretProviderSchema = z.discriminatedUnion("type", [
@@ -204,13 +227,15 @@ export const DynamicSecretProviderSchema = z.discriminatedUnion("type", [
   z.object({ type: z.literal(DynamicSecretProviders.Cassandra), inputs: DynamicSecretCassandraSchema }),
   z.object({ type: z.literal(DynamicSecretProviders.AwsIam), inputs: DynamicSecretAwsIamSchema }),
   z.object({ type: z.literal(DynamicSecretProviders.Redis), inputs: DynamicSecretRedisDBSchema }),
+  z.object({ type: z.literal(DynamicSecretProviders.SapHana), inputs: DynamicSecretSapHanaSchema }),
   z.object({ type: z.literal(DynamicSecretProviders.AwsElastiCache), inputs: DynamicSecretAwsElastiCacheSchema }),
   z.object({ type: z.literal(DynamicSecretProviders.MongoAtlas), inputs: DynamicSecretMongoAtlasSchema }),
   z.object({ type: z.literal(DynamicSecretProviders.ElasticSearch), inputs: DynamicSecretElasticSearchSchema }),
   z.object({ type: z.literal(DynamicSecretProviders.MongoDB), inputs: DynamicSecretMongoDBSchema }),
   z.object({ type: z.literal(DynamicSecretProviders.RabbitMq), inputs: DynamicSecretRabbitMqSchema }),
   z.object({ type: z.literal(DynamicSecretProviders.AzureEntraID), inputs: AzureEntraIDSchema }),
-  z.object({ type: z.literal(DynamicSecretProviders.Ldap), inputs: LdapSchema })
+  z.object({ type: z.literal(DynamicSecretProviders.Ldap), inputs: LdapSchema }),
+  z.object({ type: z.literal(DynamicSecretProviders.Snowflake), inputs: DynamicSecretSnowflakeSchema })
 ]);
 
 export type TDynamicProviderFns = {

backend/src/ee/services/dynamic-secret/providers/mongo-db.ts

@@ -2,10 +2,9 @@ import { MongoClient } from "mongodb";
 import { customAlphabet } from "nanoid";
 import { z } from "zod";
 
-import { getConfig } from "@app/lib/config/env";
-import { BadRequestError } from "@app/lib/errors";
 import { alphaNumericNanoId } from "@app/lib/nanoid";
 
+import { verifyHostInputValidity } from "../dynamic-secret-fns";
 import { DynamicSecretMongoDBSchema, TDynamicProviderFns } from "./models";
 
 const generatePassword = (size = 48) => {
@@ -19,22 +18,8 @@ const generateUsername = () => {
 
 export const MongoDBProvider = (): TDynamicProviderFns => {
   const validateProviderInputs = async (inputs: unknown) => {
-    const appCfg = getConfig();
     const providerInputs = await DynamicSecretMongoDBSchema.parseAsync(inputs);
-    if (
-      appCfg.isCloud &&
-      // localhost
-      // internal ips
-      (providerInputs.host === "host.docker.internal" ||
-        providerInputs.host.match(/^10\.\d+\.\d+\.\d+/) ||
-        providerInputs.host.match(/^192\.168\.\d+\.\d+/))
-    )
-      throw new BadRequestError({ message: "Invalid db host" });
-
-    if (providerInputs.host === "localhost" || providerInputs.host === "127.0.0.1") {
-      throw new BadRequestError({ message: "Invalid db host" });
-    }
-
+    verifyHostInputValidity(providerInputs.host);
     return providerInputs;
   };
 

backend/src/ee/services/dynamic-secret/providers/rabbit-mq.ts

@@ -3,12 +3,11 @@ import https from "https";
 import { customAlphabet } from "nanoid";
 import { z } from "zod";
 
-import { getConfig } from "@app/lib/config/env";
-import { BadRequestError } from "@app/lib/errors";
 import { removeTrailingSlash } from "@app/lib/fn";
 import { logger } from "@app/lib/logger";
 import { alphaNumericNanoId } from "@app/lib/nanoid";
 
+import { verifyHostInputValidity } from "../dynamic-secret-fns";
 import { DynamicSecretRabbitMqSchema, TDynamicProviderFns } from "./models";
 
 const generatePassword = () => {
@@ -79,23 +78,8 @@ async function deleteRabbitMqUser({ axiosInstance, usernameToDelete }: TDeleteRa
 
 export const RabbitMqProvider = (): TDynamicProviderFns => {
   const validateProviderInputs = async (inputs: unknown) => {
-    const appCfg = getConfig();
-    const isCloud = Boolean(appCfg.LICENSE_SERVER_KEY); // quick and dirty way to check if its cloud or not
-
     const providerInputs = await DynamicSecretRabbitMqSchema.parseAsync(inputs);
-    if (
-      isCloud &&
-      // localhost
-      // internal ips
-      (providerInputs.host === "host.docker.internal" ||
-        providerInputs.host.match(/^10\.\d+\.\d+\.\d+/) ||
-        providerInputs.host.match(/^192\.168\.\d+\.\d+/))
-    ) {
-      throw new BadRequestError({ message: "Invalid db host" });
-    }
-    if (providerInputs.host === "localhost" || providerInputs.host === "127.0.0.1") {
-      throw new BadRequestError({ message: "Invalid db host" });
-    }
+    verifyHostInputValidity(providerInputs.host);
 
     return providerInputs;
   };

backend/src/ee/services/dynamic-secret/providers/redis.ts

@@ -3,11 +3,10 @@ import { Redis } from "ioredis";
 import { customAlphabet } from "nanoid";
 import { z } from "zod";
 
-import { getConfig } from "@app/lib/config/env";
 import { BadRequestError } from "@app/lib/errors";
-import { getDbConnectionHost } from "@app/lib/knex";
 import { alphaNumericNanoId } from "@app/lib/nanoid";
 
+import { verifyHostInputValidity } from "../dynamic-secret-fns";
 import { DynamicSecretRedisDBSchema, TDynamicProviderFns } from "./models";
 
 const generatePassword = () => {
@@ -51,22 +50,8 @@ const executeTransactions = async (connection: Redis, commands: string[]): Promi
 
 export const RedisDatabaseProvider = (): TDynamicProviderFns => {
   const validateProviderInputs = async (inputs: unknown) => {
-    const appCfg = getConfig();
-    const isCloud = Boolean(appCfg.LICENSE_SERVER_KEY); // quick and dirty way to check if its cloud or not
-    const dbHost = appCfg.DB_HOST || getDbConnectionHost(appCfg.DB_CONNECTION_URI);
-
     const providerInputs = await DynamicSecretRedisDBSchema.parseAsync(inputs);
-    if (
-      isCloud &&
-      // localhost
-      // internal ips
-      (providerInputs.host === "host.docker.internal" ||
-        providerInputs.host.match(/^10\.\d+\.\d+\.\d+/) ||
-        providerInputs.host.match(/^192\.168\.\d+\.\d+/))
-    )
-      throw new BadRequestError({ message: "Invalid db host" });
-    if (providerInputs.host === "localhost" || providerInputs.host === "127.0.0.1" || dbHost === providerInputs.host)
-      throw new BadRequestError({ message: "Invalid db host" });
+    verifyHostInputValidity(providerInputs.host);
     return providerInputs;
   };
 

backend/src/ee/services/dynamic-secret/providers/sap-hana.ts

@@ -0,0 +1,174 @@
+/* eslint-disable @typescript-eslint/no-explicit-any */
+/* eslint-disable @typescript-eslint/no-unsafe-member-access */
+/* eslint-disable @typescript-eslint/no-unsafe-call */
+/* eslint-disable @typescript-eslint/no-unsafe-return */
+/* eslint-disable @typescript-eslint/no-unsafe-assignment */
+
+import handlebars from "handlebars";
+import hdb from "hdb";
+import { customAlphabet } from "nanoid";
+import { z } from "zod";
+
+import { BadRequestError } from "@app/lib/errors";
+import { alphaNumericNanoId } from "@app/lib/nanoid";
+
+import { verifyHostInputValidity } from "../dynamic-secret-fns";
+import { DynamicSecretSapHanaSchema, TDynamicProviderFns } from "./models";
+
+const generatePassword = (size = 48) => {
+  const charset = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
+  return customAlphabet(charset, 48)(size);
+};
+
+const generateUsername = () => {
+  return alphaNumericNanoId(32);
+};
+
+export const SapHanaProvider = (): TDynamicProviderFns => {
+  const validateProviderInputs = async (inputs: unknown) => {
+    const providerInputs = await DynamicSecretSapHanaSchema.parseAsync(inputs);
+
+    verifyHostInputValidity(providerInputs.host);
+    return providerInputs;
+  };
+
+  const getClient = async (providerInputs: z.infer<typeof DynamicSecretSapHanaSchema>) => {
+    const client = hdb.createClient({
+      host: providerInputs.host,
+      port: providerInputs.port,
+      user: providerInputs.username,
+      password: providerInputs.password,
+      ...(providerInputs.ca
+        ? {
+            ca: providerInputs.ca
+          }
+        : {})
+    });
+
+    await new Promise((resolve, reject) => {
+      client.connect((err: any) => {
+        if (err) {
+          return reject(err);
+        }
+
+        if (client.readyState) {
+          return resolve(true);
+        }
+
+        reject(new Error("SAP HANA client not ready"));
+      });
+    });
+
+    return client;
+  };
+
+  const validateConnection = async (inputs: unknown) => {
+    const providerInputs = await validateProviderInputs(inputs);
+    const client = await getClient(providerInputs);
+
+    const testResult: boolean = await new Promise((resolve, reject) => {
+      client.exec("SELECT 1 FROM DUMMY;", (err: any) => {
+        if (err) {
+          reject();
+        }
+
+        resolve(true);
+      });
+    });
+
+    return testResult;
+  };
+
+  const create = async (inputs: unknown, expireAt: number) => {
+    const providerInputs = await validateProviderInputs(inputs);
+
+    const username = generateUsername();
+    const password = generatePassword();
+    const expiration = new Date(expireAt).toISOString();
+
+    const client = await getClient(providerInputs);
+    const creationStatement = handlebars.compile(providerInputs.creationStatement, { noEscape: true })({
+      username,
+      password,
+      expiration
+    });
+
+    const queries = creationStatement.toString().split(";").filter(Boolean);
+    for await (const query of queries) {
+      await new Promise((resolve, reject) => {
+        client.exec(query, (err: any) => {
+          if (err) {
+            reject(
+              new BadRequestError({
+                message: err.message
+              })
+            );
+          }
+          resolve(true);
+        });
+      });
+    }
+
+    return { entityId: username, data: { DB_USERNAME: username, DB_PASSWORD: password } };
+  };
+
+  const revoke = async (inputs: unknown, username: string) => {
+    const providerInputs = await validateProviderInputs(inputs);
+    const client = await getClient(providerInputs);
+    const revokeStatement = handlebars.compile(providerInputs.revocationStatement)({ username });
+    const queries = revokeStatement.toString().split(";").filter(Boolean);
+    for await (const query of queries) {
+      await new Promise((resolve, reject) => {
+        client.exec(query, (err: any) => {
+          if (err) {
+            reject(
+              new BadRequestError({
+                message: err.message
+              })
+            );
+          }
+          resolve(true);
+        });
+      });
+    }
+
+    return { entityId: username };
+  };
+
+  const renew = async (inputs: unknown, username: string, expireAt: number) => {
+    const providerInputs = await validateProviderInputs(inputs);
+    const client = await getClient(providerInputs);
+    try {
+      const expiration = new Date(expireAt).toISOString();
+
+      const renewStatement = handlebars.compile(providerInputs.renewStatement)({ username, expiration });
+      const queries = renewStatement.toString().split(";").filter(Boolean);
+      for await (const query of queries) {
+        await new Promise((resolve, reject) => {
+          client.exec(query, (err: any) => {
+            if (err) {
+              reject(
+                new BadRequestError({
+                  message: err.message
+                })
+              );
+            }
+            resolve(true);
+          });
+        });
+      }
+    } finally {
+      client.disconnect();
+    }
+
+    return { entityId: username };
+  };
+
+  return {
+    validateProviderInputs,
+    validateConnection,
+    create,
+    revoke,
+    renew
+  };
+};

backend/src/ee/services/dynamic-secret/providers/snowflake.ts

@@ -0,0 +1,174 @@
+import handlebars from "handlebars";
+import { customAlphabet } from "nanoid";
+import snowflake from "snowflake-sdk";
+import { z } from "zod";
+
+import { BadRequestError } from "@app/lib/errors";
+import { alphaNumericNanoId } from "@app/lib/nanoid";
+
+import { DynamicSecretSnowflakeSchema, TDynamicProviderFns } from "./models";
+
+// destroy client requires callback...
+const noop = () => {};
+
+const generatePassword = (size = 48) => {
+  const charset = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789-_.~!*$#";
+  return customAlphabet(charset, 48)(size);
+};
+
+const generateUsername = () => {
+  return `infisical_${alphaNumericNanoId(32)}`; // username must start with alpha character, hence prefix
+};
+
+const getDaysToExpiry = (expiryDate: Date) => {
+  const start = new Date().getTime();
+  const end = new Date(expiryDate).getTime();
+  const diffTime = Math.abs(end - start);
+
+  return Math.ceil(diffTime / (1000 * 60 * 60 * 24));
+};
+
+export const SnowflakeProvider = (): TDynamicProviderFns => {
+  const validateProviderInputs = async (inputs: unknown) => {
+    const providerInputs = await DynamicSecretSnowflakeSchema.parseAsync(inputs);
+    return providerInputs;
+  };
+
+  const getClient = async (providerInputs: z.infer<typeof DynamicSecretSnowflakeSchema>) => {
+    const client = snowflake.createConnection({
+      account: `${providerInputs.orgId}-${providerInputs.accountId}`,
+      username: providerInputs.username,
+      password: providerInputs.password,
+      application: "Infisical"
+    });
+
+    await client.connectAsync(noop);
+
+    return client;
+  };
+
+  const validateConnection = async (inputs: unknown) => {
+    const providerInputs = await validateProviderInputs(inputs);
+    const client = await getClient(providerInputs);
+
+    let isValidConnection: boolean;
+
+    try {
+      isValidConnection = await Promise.race([
+        client.isValidAsync(),
+        new Promise((resolve) => {
+          setTimeout(resolve, 10000);
+        }).then(() => {
+          throw new BadRequestError({ message: "Unable to establish connection - verify credentials" });
+        })
+      ]);
+    } finally {
+      client.destroy(noop);
+    }
+
+    return isValidConnection;
+  };
+
+  const create = async (inputs: unknown, expireAt: number) => {
+    const providerInputs = await validateProviderInputs(inputs);
+
+    const client = await getClient(providerInputs);
+
+    const username = generateUsername();
+    const password = generatePassword();
+
+    try {
+      const expiration = getDaysToExpiry(new Date(expireAt));
+      const creationStatement = handlebars.compile(providerInputs.creationStatement, { noEscape: true })({
+        username,
+        password,
+        expiration
+      });
+
+      await new Promise((resolve, reject) => {
+        client.execute({
+          sqlText: creationStatement,
+          complete(err) {
+            if (err) {
+              return reject(new BadRequestError({ name: "CreateLease", message: err.message }));
+            }
+
+            return resolve(true);
+          }
+        });
+      });
+    } finally {
+      client.destroy(noop);
+    }
+
+    return { entityId: username, data: { DB_USERNAME: username, DB_PASSWORD: password } };
+  };
+
+  const revoke = async (inputs: unknown, username: string) => {
+    const providerInputs = await validateProviderInputs(inputs);
+
+    const client = await getClient(providerInputs);
+
+    try {
+      const revokeStatement = handlebars.compile(providerInputs.revocationStatement)({ username });
+
+      await new Promise((resolve, reject) => {
+        client.execute({
+          sqlText: revokeStatement,
+          complete(err) {
+            if (err) {
+              return reject(new BadRequestError({ name: "RevokeLease", message: err.message }));
+            }
+
+            return resolve(true);
+          }
+        });
+      });
+    } finally {
+      client.destroy(noop);
+    }
+
+    return { entityId: username };
+  };
+
+  const renew = async (inputs: unknown, username: string, expireAt: number) => {
+    const providerInputs = await validateProviderInputs(inputs);
+
+    if (!providerInputs.renewStatement) return { entityId: username };
+
+    const client = await getClient(providerInputs);
+
+    try {
+      const expiration = getDaysToExpiry(new Date(expireAt));
+      const renewStatement = handlebars.compile(providerInputs.renewStatement)({
+        username,
+        expiration
+      });
+
+      await new Promise((resolve, reject) => {
+        client.execute({
+          sqlText: renewStatement,
+          complete(err) {
+            if (err) {
+              return reject(new BadRequestError({ name: "RenewLease", message: err.message }));
+            }
+
+            return resolve(true);
+          }
+        });
+      });
+    } finally {
+      client.destroy(noop);
+    }
+
+    return { entityId: username };
+  };
+
+  return {
+    validateProviderInputs,
+    validateConnection,
+    create,
+    revoke,
+    renew
+  };
+};

backend/src/ee/services/dynamic-secret/providers/sql-database.ts

@@ -3,11 +3,9 @@ import knex from "knex";
 import { customAlphabet } from "nanoid";
 import { z } from "zod";
 
-import { getConfig } from "@app/lib/config/env";
-import { BadRequestError } from "@app/lib/errors";
-import { getDbConnectionHost } from "@app/lib/knex";
 import { alphaNumericNanoId } from "@app/lib/nanoid";
 
+import { verifyHostInputValidity } from "../dynamic-secret-fns";
 import { DynamicSecretSqlDBSchema, SqlProviders, TDynamicProviderFns } from "./models";
 
 const EXTERNAL_REQUEST_TIMEOUT = 10 * 1000;
@@ -29,27 +27,8 @@ const generateUsername = (provider: SqlProviders) => {
 
 export const SqlDatabaseProvider = (): TDynamicProviderFns => {
   const validateProviderInputs = async (inputs: unknown) => {
-    const appCfg = getConfig();
-    const isCloud = Boolean(appCfg.LICENSE_SERVER_KEY); // quick and dirty way to check if its cloud or not
-    const dbHost = appCfg.DB_HOST || getDbConnectionHost(appCfg.DB_CONNECTION_URI);
-
     const providerInputs = await DynamicSecretSqlDBSchema.parseAsync(inputs);
-    if (
-      isCloud &&
-      // localhost
-      // internal ips
-      (providerInputs.host === "host.docker.internal" ||
-        providerInputs.host.match(/^10\.\d+\.\d+\.\d+/) ||
-        providerInputs.host.match(/^192\.168\.\d+\.\d+/))
-    )
-      throw new BadRequestError({ message: "Invalid db host" });
-    if (
-      providerInputs.host === "localhost" ||
-      providerInputs.host === "127.0.0.1" ||
-      // database infisical uses
-      dbHost === providerInputs.host
-    )
-      throw new BadRequestError({ message: "Invalid db host" });
+    verifyHostInputValidity(providerInputs.host);
     return providerInputs;
   };
 

backend/src/ee/services/external-kms/external-kms-service.ts

@@ -145,7 +145,7 @@ export const externalKmsServiceFactory = ({
     const kmsName = name ? slugify(name) : undefined;
 
     const externalKmsDoc = await externalKmsDAL.findOne({ kmsKeyId: kmsDoc.id });
-    if (!externalKmsDoc) throw new NotFoundError({ message: "External kms not found" });
+    if (!externalKmsDoc) throw new NotFoundError({ message: `External KMS with ID '${kmsId}' not found` });
 
     let sanitizedProviderInput = "";
     const { encryptor: orgDataKeyEncryptor, decryptor: orgDataKeyDecryptor } =
@@ -220,7 +220,7 @@ export const externalKmsServiceFactory = ({
     ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Delete, OrgPermissionSubjects.Kms);
 
     const externalKmsDoc = await externalKmsDAL.findOne({ kmsKeyId: kmsDoc.id });
-    if (!externalKmsDoc) throw new NotFoundError({ message: "External kms not found" });
+    if (!externalKmsDoc) throw new NotFoundError({ message: `External KMS with ID '${kmsId}' not found` });
 
     const externalKms = await externalKmsDAL.transaction(async (tx) => {
       const kms = await kmsDAL.deleteById(kmsDoc.id, tx);
@@ -258,7 +258,7 @@ export const externalKmsServiceFactory = ({
     ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Read, OrgPermissionSubjects.Kms);
 
     const externalKmsDoc = await externalKmsDAL.findOne({ kmsKeyId: kmsDoc.id });
-    if (!externalKmsDoc) throw new NotFoundError({ message: "External kms not found" });
+    if (!externalKmsDoc) throw new NotFoundError({ message: `External KMS with ID '${kmsId}' not found` });
 
     const { decryptor: orgDataKeyDecryptor } = await kmsService.createCipherPairWithDataKey({
       type: KmsDataKey.Organization,
@@ -298,7 +298,7 @@ export const externalKmsServiceFactory = ({
     ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Read, OrgPermissionSubjects.Kms);
 
     const externalKmsDoc = await externalKmsDAL.findOne({ kmsKeyId: kmsDoc.id });
-    if (!externalKmsDoc) throw new NotFoundError({ message: "External kms not found" });
+    if (!externalKmsDoc) throw new NotFoundError({ message: `External KMS with ID '${kmsDoc.id}' not found` });
 
     const { decryptor: orgDataKeyDecryptor } = await kmsService.createCipherPairWithDataKey({
       type: KmsDataKey.Organization,

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

@@ -65,16 +65,18 @@ export const groupDALFactory = (db: TDbClient) => {
     groupId,
     offset = 0,
     limit,
-    username
+    username, // depreciated in favor of search
+    search
   }: {
     orgId: string;
     groupId: string;
     offset?: number;
     limit?: number;
     username?: string;
+    search?: string;
   }) => {
     try {
-      let query = db
+      const query = db
         .replicaNode()(TableName.OrgMembership)
         .where(`${TableName.OrgMembership}.orgId`, orgId)
         .join(TableName.Users, `${TableName.OrgMembership}.userId`, `${TableName.Users}.id`)
@@ -92,31 +94,39 @@ export const groupDALFactory = (db: TDbClient) => {
           db.ref("username").withSchema(TableName.Users),
           db.ref("firstName").withSchema(TableName.Users),
           db.ref("lastName").withSchema(TableName.Users),
-          db.ref("id").withSchema(TableName.Users).as("userId")
+          db.ref("id").withSchema(TableName.Users).as("userId"),
+          db.raw(`count(*) OVER() as total_count`)
         )
         .where({ isGhost: false })
-        .offset(offset);
+        .offset(offset)
+        .orderBy("firstName", "asc");
 
       if (limit) {
-        query = query.limit(limit);
+        void query.limit(limit);
       }
 
-      if (username) {
-        query = query.andWhere(`${TableName.Users}.username`, "ilike", `%${username}%`);
+      if (search) {
+        void query.andWhereRaw(`CONCAT_WS(' ', "firstName", "lastName", "username") ilike '%${search}%'`);
+      } else if (username) {
+        void query.andWhere(`${TableName.Users}.username`, "ilike", `%${username}%`);
       }
 
       const members = await query;
 
-      return members.map(
-        ({ email, username: memberUsername, firstName, lastName, userId, groupId: memberGroupId }) => ({
-          id: userId,
-          email,
-          username: memberUsername,
-          firstName,
-          lastName,
-          isPartOfGroup: !!memberGroupId
-        })
-      );
+      return {
+        members: members.map(
+          ({ email, username: memberUsername, firstName, lastName, userId, groupId: memberGroupId }) => ({
+            id: userId,
+            email,
+            username: memberUsername,
+            firstName,
+            lastName,
+            isPartOfGroup: !!memberGroupId
+          })
+        ),
+        // @ts-expect-error col select is raw and not strongly typed
+        totalCount: Number(members?.[0]?.total_count ?? 0)
+      };
     } catch (error) {
       throw new DatabaseError({ error, name: "Find all org members" });
     }

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

@@ -74,7 +74,7 @@ const addAcceptedUsersToGroup = async ({
 
       if (!ghostUser) {
         throw new NotFoundError({
-          message: "Failed to find project owner"
+          message: `Failed to find project owner of project with ID '${projectId}'`
         });
       }
 
@@ -82,7 +82,7 @@ const addAcceptedUsersToGroup = async ({
 
       if (!ghostUserLatestKey) {
         throw new NotFoundError({
-          message: "Failed to find project owner's latest key"
+          message: `Failed to find project owner's latest key in project with ID '${projectId}'`
         });
       }
 
@@ -90,7 +90,7 @@ const addAcceptedUsersToGroup = async ({
 
       if (!bot) {
         throw new NotFoundError({
-          message: "Failed to find project bot"
+          message: `Failed to find project bot in project with ID '${projectId}'`
         });
       }
 

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

@@ -221,7 +221,8 @@ export const groupServiceFactory = ({
     actor,
     actorId,
     actorAuthMethod,
-    actorOrgId
+    actorOrgId,
+    search
   }: TListGroupUsersDTO) => {
     if (!actorOrgId) throw new UnauthorizedError({ message: "No organization ID provided in request" });
 
@@ -244,17 +245,16 @@ export const groupServiceFactory = ({
         message: `Failed to find group with ID ${id}`
       });
 
-    const users = await groupDAL.findAllGroupPossibleMembers({
+    const { members, totalCount } = await groupDAL.findAllGroupPossibleMembers({
       orgId: group.orgId,
       groupId: group.id,
       offset,
       limit,
-      username
+      username,
+      search
     });
 
-    const count = await orgDAL.countAllOrgMembers(group.orgId);
-
-    return { users, totalCount: count };
+    return { users: members, totalCount };
   };
 
   const addUserToGroup = async ({ id, username, actor, actorId, actorAuthMethod, actorOrgId }: TAddUserToGroupDTO) => {

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

@@ -38,6 +38,7 @@ export type TListGroupUsersDTO = {
   offset: number;
   limit: number;
   username?: string;
+  search?: string;
 } & TGenericPermission;
 
 export type TAddUserToGroupDTO = {

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

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

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

@@ -0,0 +1,343 @@
+import { ForbiddenError } from "@casl/ability";
+import { packRules } from "@casl/ability/extra";
+import ms from "ms";
+
+import { TableName } from "@app/db/schemas";
+import { isAtLeastAsPrivileged } from "@app/lib/casl";
+import { BadRequestError, ForbiddenRequestError, NotFoundError } from "@app/lib/errors";
+import { unpackPermissions } from "@app/server/routes/santizedSchemas/permission";
+import { ActorType } from "@app/services/auth/auth-type";
+import { TIdentityProjectDALFactory } from "@app/services/identity-project/identity-project-dal";
+import { TProjectDALFactory } from "@app/services/project/project-dal";
+
+import { TPermissionServiceFactory } from "../permission/permission-service";
+import { ProjectPermissionActions, ProjectPermissionSub } from "../permission/project-permission";
+import { TIdentityProjectAdditionalPrivilegeV2DALFactory } from "./identity-project-additional-privilege-v2-dal";
+import {
+  IdentityProjectAdditionalPrivilegeTemporaryMode,
+  TCreateIdentityPrivilegeDTO,
+  TDeleteIdentityPrivilegeByIdDTO,
+  TGetIdentityPrivilegeDetailsByIdDTO,
+  TGetIdentityPrivilegeDetailsBySlugDTO,
+  TListIdentityPrivilegesDTO,
+  TUpdateIdentityPrivilegeByIdDTO
+} from "./identity-project-additional-privilege-v2-types";
+
+type TIdentityProjectAdditionalPrivilegeV2ServiceFactoryDep = {
+  identityProjectAdditionalPrivilegeDAL: TIdentityProjectAdditionalPrivilegeV2DALFactory;
+  identityProjectDAL: Pick<TIdentityProjectDALFactory, "findOne" | "findById">;
+  projectDAL: Pick<TProjectDALFactory, "findProjectBySlug">;
+  permissionService: Pick<TPermissionServiceFactory, "getProjectPermission">;
+};
+
+export type TIdentityProjectAdditionalPrivilegeV2ServiceFactory = ReturnType<
+  typeof identityProjectAdditionalPrivilegeV2ServiceFactory
+>;
+
+export const identityProjectAdditionalPrivilegeV2ServiceFactory = ({
+  identityProjectAdditionalPrivilegeDAL,
+  identityProjectDAL,
+  projectDAL,
+  permissionService
+}: TIdentityProjectAdditionalPrivilegeV2ServiceFactoryDep) => {
+  const create = async ({
+    slug,
+    actor,
+    actorId,
+    projectId,
+    actorOrgId,
+    identityId,
+    permissions: customPermission,
+    actorAuthMethod,
+    ...dto
+  }: TCreateIdentityPrivilegeDTO) => {
+    const identityProjectMembership = await identityProjectDAL.findOne({ identityId, projectId });
+    if (!identityProjectMembership)
+      throw new NotFoundError({ message: `Failed to find identity with id ${identityId}` });
+
+    const { permission } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      identityProjectMembership.projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+    ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Edit, ProjectPermissionSub.Identity);
+    const { permission: targetIdentityPermission } = await permissionService.getProjectPermission(
+      ActorType.IDENTITY,
+      identityId,
+      identityProjectMembership.projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+
+    // we need to validate that the privilege given is not higher than the assigning users permission
+    // @ts-expect-error this is expected error because of one being really accurate rule definition other being a bit more broader. Both are valid casl rules
+    targetIdentityPermission.update(targetIdentityPermission.rules.concat(customPermission));
+    const hasRequiredPriviledges = isAtLeastAsPrivileged(permission, targetIdentityPermission);
+    if (!hasRequiredPriviledges)
+      throw new ForbiddenRequestError({ message: "Failed to update more privileged identity" });
+
+    const existingSlug = await identityProjectAdditionalPrivilegeDAL.findOne({
+      slug,
+      projectMembershipId: identityProjectMembership.id
+    });
+    if (existingSlug) throw new BadRequestError({ message: "Additional privilege with provided slug already exists" });
+
+    const packedPermission = JSON.stringify(packRules(customPermission));
+    if (!dto.isTemporary) {
+      const additionalPrivilege = await identityProjectAdditionalPrivilegeDAL.create({
+        projectMembershipId: identityProjectMembership.id,
+        slug,
+        permissions: packedPermission
+      });
+
+      return {
+        ...additionalPrivilege,
+        permissions: unpackPermissions(additionalPrivilege.permissions)
+      };
+    }
+
+    const relativeTempAllocatedTimeInMs = ms(dto.temporaryRange);
+    const additionalPrivilege = await identityProjectAdditionalPrivilegeDAL.create({
+      projectMembershipId: identityProjectMembership.id,
+      slug,
+      permissions: packedPermission,
+      isTemporary: true,
+      temporaryMode: IdentityProjectAdditionalPrivilegeTemporaryMode.Relative,
+      temporaryRange: dto.temporaryRange,
+      temporaryAccessStartTime: new Date(dto.temporaryAccessStartTime),
+      temporaryAccessEndTime: new Date(new Date(dto.temporaryAccessStartTime).getTime() + relativeTempAllocatedTimeInMs)
+    });
+    return {
+      ...additionalPrivilege,
+      permissions: unpackPermissions(additionalPrivilege.permissions)
+    };
+  };
+
+  const updateById = async ({
+    id,
+    data,
+    actorOrgId,
+    actor,
+    actorId,
+    actorAuthMethod
+  }: TUpdateIdentityPrivilegeByIdDTO) => {
+    const identityPrivilege = await identityProjectAdditionalPrivilegeDAL.findById(id);
+    if (!identityPrivilege) throw new NotFoundError({ message: `Identity privilege with ${id} not found` });
+
+    const identityProjectMembership = await identityProjectDAL.findOne({ id: identityPrivilege.projectMembershipId });
+    if (!identityProjectMembership)
+      throw new NotFoundError({
+        message: `Failed to find identity with membership ${identityPrivilege.projectMembershipId}`
+      });
+
+    const { permission } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      identityProjectMembership.projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+    ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Edit, ProjectPermissionSub.Identity);
+    const { permission: targetIdentityPermission } = await permissionService.getProjectPermission(
+      ActorType.IDENTITY,
+      identityProjectMembership.identityId,
+      identityProjectMembership.projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+
+    // we need to validate that the privilege given is not higher than the assigning users permission
+    // @ts-expect-error this is expected error because of one being really accurate rule definition other being a bit more broader. Both are valid casl rules
+    targetIdentityPermission.update(targetIdentityPermission.rules.concat(data.permissions || []));
+    const hasRequiredPriviledges = isAtLeastAsPrivileged(permission, targetIdentityPermission);
+    if (!hasRequiredPriviledges)
+      throw new ForbiddenRequestError({ message: "Failed to update more privileged identity" });
+
+    if (data?.slug) {
+      const existingSlug = await identityProjectAdditionalPrivilegeDAL.findOne({
+        slug: data.slug,
+        projectMembershipId: identityProjectMembership.id
+      });
+      if (existingSlug && existingSlug.id !== identityPrivilege.id)
+        throw new BadRequestError({ message: "Additional privilege with provided slug already exists" });
+    }
+
+    const isTemporary = typeof data?.isTemporary !== "undefined" ? data.isTemporary : identityPrivilege.isTemporary;
+    const packedPermission = data.permissions ? JSON.stringify(packRules(data.permissions)) : undefined;
+    if (isTemporary) {
+      const temporaryAccessStartTime = data?.temporaryAccessStartTime || identityPrivilege?.temporaryAccessStartTime;
+      const temporaryRange = data?.temporaryRange || identityPrivilege?.temporaryRange;
+      const additionalPrivilege = await identityProjectAdditionalPrivilegeDAL.updateById(identityPrivilege.id, {
+        slug: data.slug,
+        permissions: packedPermission,
+        isTemporary: data.isTemporary,
+        temporaryRange: data.temporaryRange,
+        temporaryMode: data.temporaryMode,
+        temporaryAccessStartTime: new Date(temporaryAccessStartTime || ""),
+        temporaryAccessEndTime: new Date(new Date(temporaryAccessStartTime || "").getTime() + ms(temporaryRange || ""))
+      });
+      return {
+        ...additionalPrivilege,
+        permissions: unpackPermissions(additionalPrivilege.permissions)
+      };
+    }
+
+    const additionalPrivilege = await identityProjectAdditionalPrivilegeDAL.updateById(identityPrivilege.id, {
+      slug: data.slug,
+      permissions: packedPermission,
+      isTemporary: false,
+      temporaryAccessStartTime: null,
+      temporaryAccessEndTime: null,
+      temporaryRange: null,
+      temporaryMode: null
+    });
+    return {
+      ...additionalPrivilege,
+      permissions: unpackPermissions(additionalPrivilege.permissions)
+    };
+  };
+
+  const deleteById = async ({ actorId, id, actor, actorOrgId, actorAuthMethod }: TDeleteIdentityPrivilegeByIdDTO) => {
+    const identityPrivilege = await identityProjectAdditionalPrivilegeDAL.findById(id);
+    if (!identityPrivilege) throw new NotFoundError({ message: `Identity privilege with ${id} not found` });
+
+    const identityProjectMembership = await identityProjectDAL.findOne({ id: identityPrivilege.projectMembershipId });
+    if (!identityProjectMembership)
+      throw new NotFoundError({
+        message: `Failed to find identity with membership ${identityPrivilege.projectMembershipId}`
+      });
+
+    const { permission } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      identityProjectMembership.projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+    ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Delete, ProjectPermissionSub.Identity);
+    const { permission: identityRolePermission } = await permissionService.getProjectPermission(
+      ActorType.IDENTITY,
+      identityProjectMembership.identityId,
+      identityProjectMembership.projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+    const hasRequiredPriviledges = isAtLeastAsPrivileged(permission, identityRolePermission);
+    if (!hasRequiredPriviledges)
+      throw new ForbiddenRequestError({ message: "Failed to update more privileged identity" });
+
+    const deletedPrivilege = await identityProjectAdditionalPrivilegeDAL.deleteById(identityPrivilege.id);
+    return {
+      ...deletedPrivilege,
+      permissions: unpackPermissions(deletedPrivilege.permissions)
+    };
+  };
+
+  const getPrivilegeDetailsById = async ({
+    id,
+    actorOrgId,
+    actor,
+    actorId,
+    actorAuthMethod
+  }: TGetIdentityPrivilegeDetailsByIdDTO) => {
+    const identityPrivilege = await identityProjectAdditionalPrivilegeDAL.findById(id);
+    if (!identityPrivilege) throw new NotFoundError({ message: `Identity privilege with ${id} not found` });
+
+    const identityProjectMembership = await identityProjectDAL.findOne({ id: identityPrivilege.projectMembershipId });
+    if (!identityProjectMembership)
+      throw new NotFoundError({
+        message: `Failed to find identity with membership ${identityPrivilege.projectMembershipId}`
+      });
+
+    const { permission } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      identityProjectMembership.projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+    ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Read, ProjectPermissionSub.Identity);
+
+    return {
+      ...identityPrivilege,
+      permissions: unpackPermissions(identityPrivilege.permissions)
+    };
+  };
+
+  const getPrivilegeDetailsBySlug = async ({
+    identityId,
+    slug,
+    projectSlug,
+    actorOrgId,
+    actor,
+    actorId,
+    actorAuthMethod
+  }: TGetIdentityPrivilegeDetailsBySlugDTO) => {
+    const project = await projectDAL.findProjectBySlug(projectSlug, actorOrgId);
+    if (!project) throw new NotFoundError({ message: `Project with slug ${slug} not found` });
+    const projectId = project.id;
+
+    const identityProjectMembership = await identityProjectDAL.findOne({ identityId, projectId });
+    if (!identityProjectMembership)
+      throw new NotFoundError({ message: `Failed to find identity with id ${identityId}` });
+    const { permission } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      identityProjectMembership.projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+    ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Read, ProjectPermissionSub.Identity);
+
+    const identityPrivilege = await identityProjectAdditionalPrivilegeDAL.findOne({
+      slug,
+      projectMembershipId: identityProjectMembership.id
+    });
+    if (!identityPrivilege) throw new NotFoundError({ message: "Identity additional privilege not found" });
+
+    return {
+      ...identityPrivilege,
+      permissions: unpackPermissions(identityPrivilege.permissions)
+    };
+  };
+
+  const listIdentityProjectPrivileges = async ({
+    identityId,
+    actorOrgId,
+    actor,
+    actorId,
+    actorAuthMethod,
+    projectId
+  }: TListIdentityPrivilegesDTO) => {
+    const identityProjectMembership = await identityProjectDAL.findOne({ identityId, projectId });
+    if (!identityProjectMembership)
+      throw new NotFoundError({ message: `Failed to find identity with id ${identityId}` });
+    const { permission } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      identityProjectMembership.projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+    ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Edit, ProjectPermissionSub.Identity);
+
+    const identityPrivileges = await identityProjectAdditionalPrivilegeDAL.find(
+      {
+        projectMembershipId: identityProjectMembership.id
+      },
+      { sort: [[`${TableName.IdentityProjectAdditionalPrivilege}.slug` as "slug", "asc"]] }
+    );
+    return identityPrivileges;
+  };
+
+  return {
+    getPrivilegeDetailsById,
+    getPrivilegeDetailsBySlug,
+    listIdentityProjectPrivileges,
+    create,
+    updateById,
+    deleteById
+  };
+};

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

@@ -0,0 +1,55 @@
+import { TProjectPermission } from "@app/lib/types";
+
+import { TProjectPermissionV2Schema } from "../permission/project-permission";
+
+export enum IdentityProjectAdditionalPrivilegeTemporaryMode {
+  Relative = "relative"
+}
+
+export type TCreateIdentityPrivilegeDTO = {
+  permissions: TProjectPermissionV2Schema[];
+  identityId: string;
+  projectId: string;
+  slug: string;
+} & (
+  | {
+      isTemporary: false;
+    }
+  | {
+      isTemporary: true;
+      temporaryMode: IdentityProjectAdditionalPrivilegeTemporaryMode.Relative;
+      temporaryRange: string;
+      temporaryAccessStartTime: string;
+    }
+) &
+  Omit<TProjectPermission, "projectId">;
+
+export type TUpdateIdentityPrivilegeByIdDTO = { id: string } & Omit<TProjectPermission, "projectId"> & {
+    data: Partial<{
+      permissions: TProjectPermissionV2Schema[];
+      slug: string;
+      isTemporary: boolean;
+      temporaryMode: IdentityProjectAdditionalPrivilegeTemporaryMode.Relative;
+      temporaryRange: string;
+      temporaryAccessStartTime: string;
+    }>;
+  };
+
+export type TDeleteIdentityPrivilegeByIdDTO = Omit<TProjectPermission, "projectId"> & {
+  id: string;
+};
+
+export type TGetIdentityPrivilegeDetailsByIdDTO = Omit<TProjectPermission, "projectId"> & {
+  id: string;
+};
+
+export type TListIdentityPrivilegesDTO = Omit<TProjectPermission, "projectId"> & {
+  identityId: string;
+  projectId: string;
+};
+
+export type TGetIdentityPrivilegeDetailsBySlugDTO = Omit<TProjectPermission, "projectId"> & {
+  slug: string;
+  identityId: string;
+  projectSlug: string;
+};

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

@@ -1,10 +1,10 @@
 import { ForbiddenError, MongoAbility, RawRuleOf } from "@casl/ability";
-import { PackRule, unpackRules } from "@casl/ability/extra";
+import { PackRule, packRules, unpackRules } from "@casl/ability/extra";
 import ms from "ms";
-import { z } from "zod";
 
 import { isAtLeastAsPrivileged } from "@app/lib/casl";
 import { BadRequestError, ForbiddenRequestError, NotFoundError } from "@app/lib/errors";
+import { UnpackedPermissionSchema } from "@app/server/routes/santizedSchemas/permission";
 import { ActorType } from "@app/services/auth/auth-type";
 import { TIdentityProjectDALFactory } from "@app/services/identity-project/identity-project-dal";
 import { TProjectDALFactory } from "@app/services/project/project-dal";
@@ -32,16 +32,6 @@ 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()])
-    .transform((el) => (typeof el !== "string" ? el[0] : el))
-    .optional(),
-  action: z.union([z.string().min(1), z.string().array()]).transform((el) => (typeof el === "string" ? [el] : el)),
-  conditions: z.unknown().optional()
-});
-
 const unpackPermissions = (permissions: unknown) =>
   UnpackedPermissionSchema.array().parse(
     unpackRules((permissions || []) as PackRule<RawRuleOf<MongoAbility<ProjectPermissionSet>>>[])
@@ -65,7 +55,7 @@ export const identityProjectAdditionalPrivilegeServiceFactory = ({
     ...dto
   }: TCreateIdentityPrivilegeDTO) => {
     const project = await projectDAL.findProjectBySlug(projectSlug, actorOrgId);
-    if (!project) throw new NotFoundError({ message: "Project not found" });
+    if (!project) throw new NotFoundError({ message: `Project with slug '${projectSlug}' not found` });
     const projectId = project.id;
 
     const identityProjectMembership = await identityProjectDAL.findOne({ identityId, projectId });
@@ -80,14 +70,18 @@ export const identityProjectAdditionalPrivilegeServiceFactory = ({
       actorOrgId
     );
     ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Edit, ProjectPermissionSub.Identity);
-    const { permission: identityRolePermission } = await permissionService.getProjectPermission(
+    const { permission: targetIdentityPermission } = await permissionService.getProjectPermission(
       ActorType.IDENTITY,
       identityId,
       identityProjectMembership.projectId,
       actorAuthMethod,
       actorOrgId
     );
-    const hasRequiredPriviledges = isAtLeastAsPrivileged(permission, identityRolePermission);
+
+    // we need to validate that the privilege given is not higher than the assigning users permission
+    // @ts-expect-error this is expected error because of one being really accurate rule definition other being a bit more broader. Both are valid casl rules
+    targetIdentityPermission.update(targetIdentityPermission.rules.concat(customPermission));
+    const hasRequiredPriviledges = isAtLeastAsPrivileged(permission, targetIdentityPermission);
     if (!hasRequiredPriviledges)
       throw new ForbiddenRequestError({ message: "Failed to update more privileged identity" });
 
@@ -97,11 +91,12 @@ export const identityProjectAdditionalPrivilegeServiceFactory = ({
     });
     if (existingSlug) throw new BadRequestError({ message: "Additional privilege of provided slug exist" });
 
+    const packedPermission = JSON.stringify(packRules(customPermission));
     if (!dto.isTemporary) {
       const additionalPrivilege = await identityProjectAdditionalPrivilegeDAL.create({
         projectMembershipId: identityProjectMembership.id,
         slug,
-        permissions: customPermission
+        permissions: packedPermission
       });
       return {
         ...additionalPrivilege,
@@ -113,7 +108,7 @@ export const identityProjectAdditionalPrivilegeServiceFactory = ({
     const additionalPrivilege = await identityProjectAdditionalPrivilegeDAL.create({
       projectMembershipId: identityProjectMembership.id,
       slug,
-      permissions: customPermission,
+      permissions: packedPermission,
       isTemporary: true,
       temporaryMode: IdentityProjectAdditionalPrivilegeTemporaryMode.Relative,
       temporaryRange: dto.temporaryRange,
@@ -137,7 +132,7 @@ export const identityProjectAdditionalPrivilegeServiceFactory = ({
     actorAuthMethod
   }: TUpdateIdentityPrivilegeDTO) => {
     const project = await projectDAL.findProjectBySlug(projectSlug, actorOrgId);
-    if (!project) throw new NotFoundError({ message: "Project not found" });
+    if (!project) throw new NotFoundError({ message: `Project with slug '${projectSlug}' not found` });
     const projectId = project.id;
 
     const identityProjectMembership = await identityProjectDAL.findOne({ identityId, projectId });
@@ -152,14 +147,19 @@ export const identityProjectAdditionalPrivilegeServiceFactory = ({
       actorOrgId
     );
     ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Edit, ProjectPermissionSub.Identity);
-    const { permission: identityRolePermission } = await permissionService.getProjectPermission(
+
+    const { permission: targetIdentityPermission } = await permissionService.getProjectPermission(
       ActorType.IDENTITY,
       identityProjectMembership.identityId,
       identityProjectMembership.projectId,
       actorAuthMethod,
       actorOrgId
     );
-    const hasRequiredPriviledges = isAtLeastAsPrivileged(permission, identityRolePermission);
+
+    // we need to validate that the privilege given is not higher than the assigning users permission
+    // @ts-expect-error this is expected error because of one being really accurate rule definition other being a bit more broader. Both are valid casl rules
+    targetIdentityPermission.update(targetIdentityPermission.rules.concat(data.permissions || []));
+    const hasRequiredPriviledges = isAtLeastAsPrivileged(permission, targetIdentityPermission);
     if (!hasRequiredPriviledges)
       throw new ForbiddenRequestError({ message: "Failed to update more privileged identity" });
 
@@ -167,7 +167,11 @@ export const identityProjectAdditionalPrivilegeServiceFactory = ({
       slug,
       projectMembershipId: identityProjectMembership.id
     });
-    if (!identityPrivilege) throw new NotFoundError({ message: "Identity additional privilege not found" });
+    if (!identityPrivilege) {
+      throw new NotFoundError({
+        message: `Identity additional privilege with slug '${slug}' not found for the specified identity with ID '${identityProjectMembership.identityId}'`
+      });
+    }
     if (data?.slug) {
       const existingSlug = await identityProjectAdditionalPrivilegeDAL.findOne({
         slug: data.slug,
@@ -178,23 +182,29 @@ export const identityProjectAdditionalPrivilegeServiceFactory = ({
     }
 
     const isTemporary = typeof data?.isTemporary !== "undefined" ? data.isTemporary : identityPrivilege.isTemporary;
+
+    const packedPermission = data.permissions ? JSON.stringify(packRules(data.permissions)) : undefined;
     if (isTemporary) {
       const temporaryAccessStartTime = data?.temporaryAccessStartTime || identityPrivilege?.temporaryAccessStartTime;
       const temporaryRange = data?.temporaryRange || identityPrivilege?.temporaryRange;
       const additionalPrivilege = await identityProjectAdditionalPrivilegeDAL.updateById(identityPrivilege.id, {
-        ...data,
+        slug: data.slug,
+        permissions: packedPermission,
+        isTemporary: data.isTemporary,
+        temporaryRange: data.temporaryRange,
+        temporaryMode: data.temporaryMode,
         temporaryAccessStartTime: new Date(temporaryAccessStartTime || ""),
         temporaryAccessEndTime: new Date(new Date(temporaryAccessStartTime || "").getTime() + ms(temporaryRange || ""))
       });
       return {
         ...additionalPrivilege,
-
         permissions: unpackPermissions(additionalPrivilege.permissions)
       };
     }
 
     const additionalPrivilege = await identityProjectAdditionalPrivilegeDAL.updateById(identityPrivilege.id, {
-      ...data,
+      slug: data.slug,
+      permissions: packedPermission,
       isTemporary: false,
       temporaryAccessStartTime: null,
       temporaryAccessEndTime: null,
@@ -203,7 +213,6 @@ export const identityProjectAdditionalPrivilegeServiceFactory = ({
     });
     return {
       ...additionalPrivilege,
-
       permissions: unpackPermissions(additionalPrivilege.permissions)
     };
   };
@@ -218,7 +227,7 @@ export const identityProjectAdditionalPrivilegeServiceFactory = ({
     actorAuthMethod
   }: TDeleteIdentityPrivilegeDTO) => {
     const project = await projectDAL.findProjectBySlug(projectSlug, actorOrgId);
-    if (!project) throw new NotFoundError({ message: "Project not found" });
+    if (!project) throw new NotFoundError({ message: `Project with slug '${projectSlug}' not found` });
     const projectId = project.id;
 
     const identityProjectMembership = await identityProjectDAL.findOne({ identityId, projectId });
@@ -248,7 +257,11 @@ export const identityProjectAdditionalPrivilegeServiceFactory = ({
       slug,
       projectMembershipId: identityProjectMembership.id
     });
-    if (!identityPrivilege) throw new NotFoundError({ message: "Identity additional privilege not found" });
+    if (!identityPrivilege) {
+      throw new NotFoundError({
+        message: `Identity additional privilege with slug '${slug}' not found for the specified identity with ID '${identityProjectMembership.identityId}'`
+      });
+    }
 
     const deletedPrivilege = await identityProjectAdditionalPrivilegeDAL.deleteById(identityPrivilege.id);
     return {
@@ -268,7 +281,7 @@ export const identityProjectAdditionalPrivilegeServiceFactory = ({
     actorAuthMethod
   }: TGetIdentityPrivilegeDetailsDTO) => {
     const project = await projectDAL.findProjectBySlug(projectSlug, actorOrgId);
-    if (!project) throw new NotFoundError({ message: "Project not found" });
+    if (!project) throw new NotFoundError({ message: `Project with slug '${projectSlug}' not found` });
     const projectId = project.id;
 
     const identityProjectMembership = await identityProjectDAL.findOne({ identityId, projectId });
@@ -281,14 +294,17 @@ export const identityProjectAdditionalPrivilegeServiceFactory = ({
       actorAuthMethod,
       actorOrgId
     );
-    ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Edit, ProjectPermissionSub.Identity);
+    ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Read, ProjectPermissionSub.Identity);
 
     const identityPrivilege = await identityProjectAdditionalPrivilegeDAL.findOne({
       slug,
       projectMembershipId: identityProjectMembership.id
     });
-    if (!identityPrivilege) throw new NotFoundError({ message: "Identity additional privilege not found" });
-
+    if (!identityPrivilege) {
+      throw new NotFoundError({
+        message: `Identity additional privilege with slug '${slug}' not found for the specified identity with ID '${identityProjectMembership.identityId}'`
+      });
+    }
     return {
       ...identityPrivilege,
       permissions: unpackPermissions(identityPrivilege.permissions)
@@ -304,7 +320,7 @@ export const identityProjectAdditionalPrivilegeServiceFactory = ({
     projectSlug
   }: TListIdentityPrivilegesDTO) => {
     const project = await projectDAL.findProjectBySlug(projectSlug, actorOrgId);
-    if (!project) throw new NotFoundError({ message: "Project not found" });
+    if (!project) throw new NotFoundError({ message: `Project with slug '${projectSlug}' not found` });
     const projectId = project.id;
 
     const identityProjectMembership = await identityProjectDAL.findOne({ identityId, projectId });
@@ -324,7 +340,6 @@ export const identityProjectAdditionalPrivilegeServiceFactory = ({
     });
     return identityPrivileges.map((el) => ({
       ...el,
-
       permissions: unpackPermissions(el.permissions)
     }));
   };

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

@@ -1,11 +1,13 @@
 import { TProjectPermission } from "@app/lib/types";
 
+import { TProjectPermissionV2Schema } from "../permission/project-permission";
+
 export enum IdentityProjectAdditionalPrivilegeTemporaryMode {
   Relative = "relative"
 }
 
 export type TCreateIdentityPrivilegeDTO = {
-  permissions: unknown;
+  permissions: TProjectPermissionV2Schema[];
   identityId: string;
   projectSlug: string;
   slug: string;
@@ -27,7 +29,7 @@ export type TUpdateIdentityPrivilegeDTO = { slug: string; identityId: string; pr
   "projectId"
 > & {
     data: Partial<{
-      permissions: unknown;
+      permissions: TProjectPermissionV2Schema[];
       slug: string;
       isTemporary: boolean;
       temporaryMode: IdentityProjectAdditionalPrivilegeTemporaryMode.Relative;

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

@@ -1,14 +1,7 @@
 import { ForbiddenError } from "@casl/ability";
 import jwt from "jsonwebtoken";
 
-import {
-  OrgMembershipRole,
-  OrgMembershipStatus,
-  SecretKeyEncoding,
-  TableName,
-  TLdapConfigsUpdate,
-  TUsers
-} from "@app/db/schemas";
+import { 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";
@@ -28,6 +21,7 @@ import { TokenType } from "@app/services/auth-token/auth-token-types";
 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 { getDefaultOrgMembershipRole } from "@app/services/org/org-role-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";
@@ -253,7 +247,11 @@ export const ldapConfigServiceFactory = ({
     };
 
     const orgBot = await orgBotDAL.findOne({ orgId });
-    if (!orgBot) throw new NotFoundError({ message: "Organization bot not found", name: "OrgBotNotFound" });
+    if (!orgBot)
+      throw new NotFoundError({
+        message: `Organization bot in organization with ID '${orgId}' not found`,
+        name: "OrgBotNotFound"
+      });
     const key = infisicalSymmetricDecrypt({
       ciphertext: orgBot.encryptedSymmetricKey,
       iv: orgBot.symmetricKeyIV,
@@ -289,10 +287,19 @@ export const ldapConfigServiceFactory = ({
 
   const getLdapCfg = async (filter: { orgId: string; isActive?: boolean; id?: string }) => {
     const ldapConfig = await ldapConfigDAL.findOne(filter);
-    if (!ldapConfig) throw new NotFoundError({ message: "Failed to find organization LDAP data" });
+    if (!ldapConfig) {
+      throw new NotFoundError({
+        message: `Failed to find organization LDAP data in organization with ID '${filter.orgId}'`
+      });
+    }
 
     const orgBot = await orgBotDAL.findOne({ orgId: ldapConfig.orgId });
-    if (!orgBot) throw new NotFoundError({ message: "Organization bot not found", name: "OrgBotNotFound" });
+    if (!orgBot) {
+      throw new NotFoundError({
+        message: `Organization bot not found in organization with ID ${ldapConfig.orgId}`,
+        name: "OrgBotNotFound"
+      });
+    }
 
     const key = infisicalSymmetricDecrypt({
       ciphertext: orgBot.encryptedSymmetricKey,
@@ -375,7 +382,7 @@ export const ldapConfigServiceFactory = ({
 
   const bootLdap = async (organizationSlug: string) => {
     const organization = await orgDAL.findOne({ slug: organizationSlug });
-    if (!organization) throw new NotFoundError({ message: "Organization not found" });
+    if (!organization) throw new NotFoundError({ message: `Organization with slug '${organizationSlug}' not found` });
 
     const ldapConfig = await getLdapCfg({
       orgId: organization.id,
@@ -432,7 +439,7 @@ export const ldapConfigServiceFactory = ({
     });
 
     const organization = await orgDAL.findOrgById(orgId);
-    if (!organization) throw new NotFoundError({ message: "Organization not found" });
+    if (!organization) throw new NotFoundError({ message: `Organization with ID '${orgId}' not found` });
 
     if (userAlias) {
       await userDAL.transaction(async (tx) => {
@@ -444,11 +451,14 @@ export const ldapConfigServiceFactory = ({
           { tx }
         );
         if (!orgMembership) {
+          const { role, roleId } = await getDefaultOrgMembershipRole(organization.defaultMembershipRole);
+
           await orgDAL.createMembership(
             {
               userId: userAlias.userId,
               orgId,
-              role: OrgMembershipRole.Member,
+              role,
+              roleId,
               status: OrgMembershipStatus.Accepted,
               isActive: true
             },
@@ -529,12 +539,15 @@ export const ldapConfigServiceFactory = ({
         );
 
         if (!orgMembership) {
+          const { role, roleId } = await getDefaultOrgMembershipRole(organization.defaultMembershipRole);
+
           await orgMembershipDAL.create(
             {
               userId: newUser.id,
               inviteEmail: email,
               orgId,
-              role: OrgMembershipRole.Member,
+              role,
+              roleId,
               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
               isActive: true
             },
@@ -700,7 +713,11 @@ export const ldapConfigServiceFactory = ({
       orgId
     });
 
-    if (!ldapConfig) throw new NotFoundError({ message: "Failed to find organization LDAP data" });
+    if (!ldapConfig) {
+      throw new NotFoundError({
+        message: `Failed to find organization LDAP data with ID '${ldapConfigId}' in organization with ID ${orgId}`
+      });
+    }
 
     const groupMaps = await ldapGroupMapDAL.findLdapGroupMapsByLdapConfigId(ldapConfigId);
 
@@ -747,7 +764,11 @@ export const ldapConfigServiceFactory = ({
     }
 
     const group = await groupDAL.findOne({ slug: groupSlug, orgId });
-    if (!group) throw new NotFoundError({ message: "Failed to find group" });
+    if (!group) {
+      throw new NotFoundError({
+        message: `Failed to find group with slug '${groupSlug}' in organization with ID '${orgId}'`
+      });
+    }
 
     const groupMap = await ldapGroupMapDAL.create({
       ldapConfigId,
@@ -781,7 +802,11 @@ export const ldapConfigServiceFactory = ({
       orgId
     });
 
-    if (!ldapConfig) throw new NotFoundError({ message: "Failed to find organization LDAP data" });
+    if (!ldapConfig) {
+      throw new NotFoundError({
+        message: `Failed to find organization LDAP data with ID '${ldapConfigId}' in organization with ID ${orgId}`
+      });
+    }
 
     const [deletedGroupMap] = await ldapGroupMapDAL.delete({
       ldapConfigId: ldapConfig.id,

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

@@ -46,7 +46,8 @@ export const getDefaultOnPremFeatures = (): TFeatureSet => ({
     writeLimit: 200,
     secretsLimit: 40
   },
-  pkiEst: false
+  pkiEst: false,
+  enforceMfa: false
 });
 
 export const setupLicenseRequestWithStore = (baseURL: string, refreshUrl: string, licenseKey: string) => {

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

@@ -129,7 +129,7 @@ export const licenseServiceFactory = ({
         }
       }
 
-      // this means this is self hosted oss version
+      // this means this is the self-hosted oss version
       // else it would reach catch statement
       isValidLicense = true;
     } catch (error) {
@@ -145,7 +145,7 @@ export const licenseServiceFactory = ({
         if (cachedPlan) return JSON.parse(cachedPlan) as TFeatureSet;
 
         const org = await orgDAL.findOrgById(orgId);
-        if (!org) throw new NotFoundError({ message: "Organization not found" });
+        if (!org) throw new NotFoundError({ message: `Organization with ID '${orgId}' not found` });
         const {
           data: { currentPlan }
         } = await licenseServerCloudApi.request.get<{ currentPlan: TFeatureSet }>(
@@ -204,7 +204,7 @@ export const licenseServiceFactory = ({
   const updateSubscriptionOrgMemberCount = async (orgId: string, tx?: Knex) => {
     if (instanceType === InstanceType.Cloud) {
       const org = await orgDAL.findOrgById(orgId);
-      if (!org) throw new NotFoundError({ message: "Organization not found" });
+      if (!org) throw new NotFoundError({ message: `Organization with ID '${orgId}' not found` });
 
       const quantity = await licenseDAL.countOfOrgMembers(orgId, tx);
       const quantityIdentities = await licenseDAL.countOrgUsersAndIdentities(orgId, tx);
@@ -267,7 +267,7 @@ export const licenseServiceFactory = ({
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) {
       throw new NotFoundError({
-        message: "Organization not found"
+        message: `Organization with ID '${orgId}' not found`
       });
     }
 
@@ -341,7 +341,7 @@ export const licenseServiceFactory = ({
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) {
       throw new NotFoundError({
-        message: "Organization not found"
+        message: `Organization with ID '${orgId}' not found`
       });
     }
     const { data } = await licenseServerCloudApi.request.get(
@@ -358,7 +358,7 @@ export const licenseServiceFactory = ({
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) {
       throw new NotFoundError({
-        message: "Organization not found"
+        message: `Organization with ID '${orgId}' not found`
       });
     }
     const { data } = await licenseServerCloudApi.request.get(
@@ -374,7 +374,7 @@ export const licenseServiceFactory = ({
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) {
       throw new NotFoundError({
-        message: "Organization not found"
+        message: `Organization with ID '${orgId}' not found`
       });
     }
 
@@ -399,7 +399,7 @@ export const licenseServiceFactory = ({
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) {
       throw new NotFoundError({
-        message: "Organization not found"
+        message: `Organization with ID '${orgId}' not found`
       });
     }
     const { data } = await licenseServerCloudApi.request.patch(
@@ -419,7 +419,7 @@ export const licenseServiceFactory = ({
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) {
       throw new NotFoundError({
-        message: "Organization not found"
+        message: `Organization with ID '${orgId}' not found`
       });
     }
 
@@ -446,7 +446,7 @@ export const licenseServiceFactory = ({
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) {
       throw new NotFoundError({
-        message: "Organization not found"
+        message: `Organization with ID '${orgId}' not found`
       });
     }
     const {
@@ -475,7 +475,7 @@ export const licenseServiceFactory = ({
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) {
       throw new NotFoundError({
-        message: "Organization not found"
+        message: `Organization with ID '${orgId}' not found`
       });
     }
 
@@ -492,7 +492,7 @@ export const licenseServiceFactory = ({
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) {
       throw new NotFoundError({
-        message: "Organization not found"
+        message: `Organization with ID '${orgId}' not found`
       });
     }
     const {
@@ -510,7 +510,7 @@ export const licenseServiceFactory = ({
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) {
       throw new NotFoundError({
-        message: "Organization not found"
+        message: `Organization with ID '${orgId}' not found`
       });
     }
 
@@ -531,7 +531,7 @@ export const licenseServiceFactory = ({
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) {
       throw new NotFoundError({
-        message: "Organization not found"
+        message: `Organization with ID '${orgId}' not found`
       });
     }
 
@@ -548,7 +548,7 @@ export const licenseServiceFactory = ({
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) {
       throw new NotFoundError({
-        message: "Organization not found"
+        message: `Organization with ID '${orgId}' not found`
       });
     }
 
@@ -565,7 +565,7 @@ export const licenseServiceFactory = ({
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) {
       throw new NotFoundError({
-        message: "Organization not found"
+        message: `Organization with ID '${orgId}' not found`
       });
     }
 

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

@@ -64,6 +64,7 @@ export type TFeatureSet = {
     secretsLimit: number;
   };
   pkiEst: boolean;
+  enforceMfa: boolean;
 };
 
 export type TOrgPlansTableDTO = {

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

@@ -3,7 +3,7 @@ import { ForbiddenError } from "@casl/ability";
 import jwt from "jsonwebtoken";
 import { Issuer, Issuer as OpenIdIssuer, Strategy as OpenIdStrategy, TokenSet } from "openid-client";
 
-import { OrgMembershipRole, OrgMembershipStatus, SecretKeyEncoding, TableName, TUsers } from "@app/db/schemas";
+import { OrgMembershipStatus, SecretKeyEncoding, TableName, TUsers } from "@app/db/schemas";
 import { TOidcConfigsUpdate } from "@app/db/schemas/oidc-configs";
 import { TLicenseServiceFactory } from "@app/ee/services/license/license-service";
 import { OrgPermissionActions, OrgPermissionSubjects } from "@app/ee/services/permission/org-permission";
@@ -23,6 +23,7 @@ import { TAuthTokenServiceFactory } from "@app/services/auth-token/auth-token-se
 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 { getDefaultOrgMembershipRole } from "@app/services/org/org-role-fns";
 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";
@@ -78,7 +79,7 @@ export const oidcConfigServiceFactory = ({
     const org = await orgDAL.findOne({ slug: dto.orgSlug });
     if (!org) {
       throw new NotFoundError({
-        message: "Organization not found",
+        message: `Organization with slug '${dto.orgSlug}' not found`,
         name: "OrgNotFound"
       });
     }
@@ -99,14 +100,17 @@ export const oidcConfigServiceFactory = ({
 
     if (!oidcCfg) {
       throw new NotFoundError({
-        message: "Failed to find organization OIDC configuration"
+        message: `OIDC configuration for organization with slug '${dto.orgSlug}' not found`
       });
     }
 
     // decrypt and return cfg
     const orgBot = await orgBotDAL.findOne({ orgId: oidcCfg.orgId });
     if (!orgBot) {
-      throw new NotFoundError({ message: "Organization bot not found", name: "OrgBotNotFound" });
+      throw new NotFoundError({
+        message: `Organization bot for organization with ID '${oidcCfg.orgId}' not found`,
+        name: "OrgBotNotFound"
+      });
     }
 
     const key = infisicalSymmetricDecrypt({
@@ -173,7 +177,7 @@ export const oidcConfigServiceFactory = ({
     });
 
     const organization = await orgDAL.findOrgById(orgId);
-    if (!organization) throw new NotFoundError({ message: "Organization not found" });
+    if (!organization) throw new NotFoundError({ message: `Organization with ID '${orgId}' not found` });
 
     let user: TUsers;
     if (userAlias) {
@@ -187,12 +191,15 @@ export const oidcConfigServiceFactory = ({
           { tx }
         );
         if (!orgMembership) {
+          const { role, roleId } = await getDefaultOrgMembershipRole(organization.defaultMembershipRole);
+
           await orgMembershipDAL.create(
             {
               userId: userAlias.userId,
               inviteEmail: email,
               orgId,
-              role: OrgMembershipRole.Member,
+              role,
+              roleId,
               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
               isActive: true
             },
@@ -261,12 +268,15 @@ export const oidcConfigServiceFactory = ({
         );
 
         if (!orgMembership) {
+          const { role, roleId } = await getDefaultOrgMembershipRole(organization.defaultMembershipRole);
+
           await orgMembershipDAL.create(
             {
               userId: newUser.id,
               inviteEmail: email,
               orgId,
-              role: OrgMembershipRole.Member,
+              role,
+              roleId,
               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
               isActive: true
             },
@@ -359,7 +369,7 @@ export const oidcConfigServiceFactory = ({
 
     if (!org) {
       throw new NotFoundError({
-        message: "Organization not found"
+        message: `Organization with slug '${orgSlug}' not found`
       });
     }
 
@@ -380,7 +390,11 @@ export const oidcConfigServiceFactory = ({
     ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Edit, OrgPermissionSubjects.Sso);
 
     const orgBot = await orgBotDAL.findOne({ orgId: org.id });
-    if (!orgBot) throw new NotFoundError({ message: "Organization bot not found", name: "OrgBotNotFound" });
+    if (!orgBot)
+      throw new NotFoundError({
+        message: `Organization bot for organization with ID '${org.id}' not found`,
+        name: "OrgBotNotFound"
+      });
     const key = infisicalSymmetricDecrypt({
       ciphertext: orgBot.encryptedSymmetricKey,
       iv: orgBot.symmetricKeyIV,
@@ -448,7 +462,7 @@ export const oidcConfigServiceFactory = ({
     });
     if (!org) {
       throw new NotFoundError({
-        message: "Organization not found"
+        message: `Organization with slug '${orgSlug}' not found`
       });
     }
 
@@ -554,7 +568,7 @@ export const oidcConfigServiceFactory = ({
 
     if (!org) {
       throw new NotFoundError({
-        message: "Organization not found."
+        message: `Organization with slug '${orgSlug}' not found`
       });
     }
 

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

@@ -64,10 +64,10 @@ export const permissionServiceFactory = ({
               permissions as PackRule<RawRuleOf<MongoAbility<OrgPermissionSet>>>[]
             );
           default:
-            throw new NotFoundError({ name: "OrgRoleInvalid", message: "Organization role not found" });
+            throw new NotFoundError({ name: "OrgRoleInvalid", message: `Organization role '${role}' not found` });
         }
       })
-      .reduce((curr, prev) => prev.concat(curr), []);
+      .reduce((prev, curr) => prev.concat(curr), []);
 
     return createMongoAbility<OrgPermissionSet>(rules, {
       conditionsMatcher
@@ -94,11 +94,11 @@ export const permissionServiceFactory = ({
           default:
             throw new NotFoundError({
               name: "ProjectRoleInvalid",
-              message: "Project role not found"
+              message: `Project role '${role}' not found`
             });
         }
       })
-      .reduce((curr, prev) => prev.concat(curr), []);
+      .reduce((prev, curr) => prev.concat(curr), []);
 
     return rules;
   };
@@ -145,7 +145,7 @@ export const permissionServiceFactory = ({
     const membership = await permissionDAL.getOrgIdentityPermission(identityId, orgId);
     if (!membership) throw new ForbiddenRequestError({ name: "Identity is not apart of this organization" });
     if (membership.role === OrgMembershipRole.Custom && !membership.permissions) {
-      throw new NotFoundError({ name: "Custom organization permission not found" });
+      throw new NotFoundError({ name: `Custom organization permission not found for identity ${identityId}` });
     }
     return {
       permission: buildOrgPermission([{ role: membership.role, permissions: membership.permissions }]),
@@ -179,7 +179,10 @@ export const permissionServiceFactory = ({
     const isCustomRole = !Object.values(OrgMembershipRole).includes(role as OrgMembershipRole);
     if (isCustomRole) {
       const orgRole = await orgRoleDAL.findOne({ slug: role, orgId });
-      if (!orgRole) throw new NotFoundError({ message: "Specified role was not found" });
+      if (!orgRole)
+        throw new NotFoundError({
+          message: `Specified role '${role}' was not found in the organization with ID '${orgId}'`
+        });
       return {
         permission: buildOrgPermission([{ role: OrgMembershipRole.Custom, permissions: orgRole.permissions }]),
         role: orgRole
@@ -264,7 +267,9 @@ export const permissionServiceFactory = ({
   ): Promise<TProjectPermissionRT<ActorType.IDENTITY>> => {
     const identityProjectPermission = await permissionDAL.getProjectIdentityPermission(identityId, projectId);
     if (!identityProjectPermission)
-      throw new ForbiddenRequestError({ name: "Identity is not a member of the specified project" });
+      throw new ForbiddenRequestError({
+        name: `Identity is not a member of the specified project with ID '${projectId}'`
+      });
 
     if (
       identityProjectPermission.roles.some(
@@ -326,7 +331,7 @@ export const permissionServiceFactory = ({
     actorOrgId: string | undefined
   ) => {
     const serviceToken = await serviceTokenDAL.findById(serviceTokenId);
-    if (!serviceToken) throw new NotFoundError({ message: "Service token not found" });
+    if (!serviceToken) throw new NotFoundError({ message: `Service token with ID '${serviceTokenId}' not found` });
 
     const serviceTokenProject = await projectDAL.findById(serviceToken.projectId);
 
@@ -337,11 +342,15 @@ export const permissionServiceFactory = ({
     }
 
     if (serviceToken.projectId !== projectId) {
-      throw new ForbiddenRequestError({ name: "Service token not a part of the specified project" });
+      throw new ForbiddenRequestError({
+        name: `Service token not a part of the specified project with ID ${projectId}`
+      });
     }
 
     if (serviceTokenProject.orgId !== actorOrgId) {
-      throw new ForbiddenRequestError({ message: "Service token not a part of the specified organization" });
+      throw new ForbiddenRequestError({
+        message: `Service token not a part of the specified organization with ID ${actorOrgId}`
+      });
     }
 
     const scopes = ServiceTokenScopes.parse(serviceToken.scopes || []);

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

@@ -11,8 +11,8 @@ export enum PermissionConditionOperators {
 }
 
 export const PermissionConditionSchema = {
-  [PermissionConditionOperators.$IN]: z.string().min(1).array(),
-  [PermissionConditionOperators.$ALL]: z.string().min(1).array(),
+  [PermissionConditionOperators.$IN]: z.string().trim().min(1).array(),
+  [PermissionConditionOperators.$ALL]: z.string().trim().min(1).array(),
   [PermissionConditionOperators.$REGEX]: z
     .string()
     .min(1)

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

@@ -1,9 +1,8 @@
 import { AbilityBuilder, createMongoAbility, ForcedSubject, MongoAbility } from "@casl/ability";
 import { z } from "zod";
 
-import { TableName } from "@app/db/schemas";
 import { conditionsMatcher } from "@app/lib/casl";
-import { BadRequestError } from "@app/lib/errors";
+import { UnpackedPermissionSchema } from "@app/server/routes/santizedSchemas/permission";
 
 import { PermissionConditionOperators, PermissionConditionSchema } from "./permission-types";
 
@@ -23,6 +22,14 @@ export enum ProjectPermissionCmekActions {
   Decrypt = "decrypt"
 }
 
+export enum ProjectPermissionDynamicSecretActions {
+  ReadRootCredential = "read-root-credential",
+  CreateRootCredential = "create-root-credential",
+  EditRootCredential = "edit-root-credential",
+  DeleteRootCredential = "delete-root-credential",
+  Lease = "lease"
+}
+
 export enum ProjectPermissionSub {
   Role = "role",
   Member = "member",
@@ -38,6 +45,8 @@ export enum ProjectPermissionSub {
   Project = "workspace",
   Secrets = "secrets",
   SecretFolders = "secret-folders",
+  SecretImports = "secret-imports",
+  DynamicSecrets = "dynamic-secrets",
   SecretRollback = "secret-rollback",
   SecretApproval = "secret-approval",
   SecretRotation = "secret-rotation",
@@ -54,19 +63,8 @@ export enum ProjectPermissionSub {
 export type SecretSubjectFields = {
   environment: string;
   secretPath: string;
-  // secretName: string;
-  // secretTags: string[];
-};
-
-export const CaslSecretsV2SubjectKnexMapper = (field: string) => {
-  switch (field) {
-    case "secretName":
-      return `${TableName.SecretV2}.key`;
-    case "secretTags":
-      return `${TableName.SecretTag}.slug`;
-    default:
-      break;
-  }
+  secretName?: string;
+  secretTags?: string[];
 };
 
 export type SecretFolderSubjectFields = {
@@ -74,6 +72,16 @@ export type SecretFolderSubjectFields = {
   secretPath: string;
 };
 
+export type DynamicSecretSubjectFields = {
+  environment: string;
+  secretPath: string;
+};
+
+export type SecretImportSubjectFields = {
+  environment: string;
+  secretPath: string;
+};
+
 export type ProjectPermissionSet =
   | [
       ProjectPermissionActions,
@@ -86,6 +94,20 @@ export type ProjectPermissionSet =
         | (ForcedSubject<ProjectPermissionSub.SecretFolders> & SecretFolderSubjectFields)
       )
     ]
+  | [
+      ProjectPermissionDynamicSecretActions,
+      (
+        | ProjectPermissionSub.DynamicSecrets
+        | (ForcedSubject<ProjectPermissionSub.DynamicSecrets> & DynamicSecretSubjectFields)
+      )
+    ]
+  | [
+      ProjectPermissionActions,
+      (
+        | ProjectPermissionSub.SecretImports
+        | (ForcedSubject<ProjectPermissionSub.SecretImports> & SecretImportSubjectFields)
+      )
+    ]
   | [ProjectPermissionActions, ProjectPermissionSub.Role]
   | [ProjectPermissionActions, ProjectPermissionSub.Tags]
   | [ProjectPermissionActions, ProjectPermissionSub.Member]
@@ -120,7 +142,9 @@ const CASL_ACTION_SCHEMA_NATIVE_ENUM = <ACTION extends z.EnumLike>(actions: ACTI
 const CASL_ACTION_SCHEMA_ENUM = <ACTION extends z.EnumValues>(actions: ACTION) =>
   z.union([z.enum(actions), z.enum(actions).array().min(1)]).transform((el) => (typeof el === "string" ? [el] : el));
 
-const SecretConditionSchema = z
+// akhilmhdh: don't modify this for v2
+// if you want to update create a new schema
+const SecretConditionV1Schema = z
   .object({
     environment: z.union([
       z.string(),
@@ -146,16 +170,50 @@ const SecretConditionSchema = z
   })
   .partial();
 
-export const ProjectPermissionSchema = z.discriminatedUnion("subject", [
-  z.object({
-    subject: z.literal(ProjectPermissionSub.Secrets).describe("The entity this permission pertains to."),
-    action: CASL_ACTION_SCHEMA_NATIVE_ENUM(ProjectPermissionActions).describe(
-      "Describe what action an entity can take."
-    ),
-    conditions: SecretConditionSchema.describe(
-      "When specified, only matching conditions will be allowed to access given resource."
-    ).optional()
-  }),
+const SecretConditionV2Schema = z
+  .object({
+    environment: z.union([
+      z.string(),
+      z
+        .object({
+          [PermissionConditionOperators.$EQ]: PermissionConditionSchema[PermissionConditionOperators.$EQ],
+          [PermissionConditionOperators.$NEQ]: PermissionConditionSchema[PermissionConditionOperators.$NEQ],
+          [PermissionConditionOperators.$IN]: PermissionConditionSchema[PermissionConditionOperators.$IN],
+          [PermissionConditionOperators.$GLOB]: PermissionConditionSchema[PermissionConditionOperators.$GLOB]
+        })
+        .partial()
+    ]),
+    secretPath: z.union([
+      z.string(),
+      z
+        .object({
+          [PermissionConditionOperators.$EQ]: PermissionConditionSchema[PermissionConditionOperators.$EQ],
+          [PermissionConditionOperators.$NEQ]: PermissionConditionSchema[PermissionConditionOperators.$NEQ],
+          [PermissionConditionOperators.$IN]: PermissionConditionSchema[PermissionConditionOperators.$IN],
+          [PermissionConditionOperators.$GLOB]: PermissionConditionSchema[PermissionConditionOperators.$GLOB]
+        })
+        .partial()
+    ]),
+    secretName: z.union([
+      z.string(),
+      z
+        .object({
+          [PermissionConditionOperators.$EQ]: PermissionConditionSchema[PermissionConditionOperators.$EQ],
+          [PermissionConditionOperators.$NEQ]: PermissionConditionSchema[PermissionConditionOperators.$NEQ],
+          [PermissionConditionOperators.$IN]: PermissionConditionSchema[PermissionConditionOperators.$IN],
+          [PermissionConditionOperators.$GLOB]: PermissionConditionSchema[PermissionConditionOperators.$GLOB]
+        })
+        .partial()
+    ]),
+    secretTags: z
+      .object({
+        [PermissionConditionOperators.$IN]: PermissionConditionSchema[PermissionConditionOperators.$IN]
+      })
+      .partial()
+  })
+  .partial();
+
+const GeneralPermissionSchema = [
   z.object({
     subject: z.literal(ProjectPermissionSub.SecretApproval).describe("The entity this permission pertains to."),
     action: CASL_ACTION_SCHEMA_NATIVE_ENUM(ProjectPermissionActions).describe(
@@ -259,7 +317,7 @@ export const ProjectPermissionSchema = z.discriminatedUnion("subject", [
     )
   }),
   z.object({
-    subject: z.literal(ProjectPermissionSub.CertificateTemplates).describe("The entity this permission pertains to. "),
+    subject: z.literal(ProjectPermissionSub.CertificateTemplates).describe("The entity this permission pertains to."),
     action: CASL_ACTION_SCHEMA_NATIVE_ENUM(ProjectPermissionActions).describe(
       "Describe what action an entity can take."
     )
@@ -288,26 +346,90 @@ export const ProjectPermissionSchema = z.discriminatedUnion("subject", [
       "Describe what action an entity can take."
     )
   }),
-  z.object({
-    subject: z.literal(ProjectPermissionSub.SecretFolders).describe("The entity this permission pertains to."),
-    action: CASL_ACTION_SCHEMA_ENUM([ProjectPermissionActions.Read]).describe(
-      "Describe what action an entity can take."
-    )
-  }),
   z.object({
     subject: z.literal(ProjectPermissionSub.Cmek).describe("The entity this permission pertains to."),
+    inverted: z.boolean().optional().describe("Whether rule allows or forbids."),
     action: CASL_ACTION_SCHEMA_NATIVE_ENUM(ProjectPermissionCmekActions).describe(
       "Describe what action an entity can take."
     )
   })
+];
+
+export const ProjectPermissionV1Schema = z.discriminatedUnion("subject", [
+  z.object({
+    subject: z.literal(ProjectPermissionSub.Secrets).describe("The entity this permission pertains to."),
+    inverted: z.boolean().optional().describe("Whether rule allows or forbids."),
+    action: CASL_ACTION_SCHEMA_NATIVE_ENUM(ProjectPermissionActions).describe(
+      "Describe what action an entity can take."
+    ),
+    conditions: SecretConditionV1Schema.describe(
+      "When specified, only matching conditions will be allowed to access given resource."
+    ).optional()
+  }),
+  z.object({
+    subject: z.literal(ProjectPermissionSub.SecretFolders).describe("The entity this permission pertains to."),
+    inverted: z.boolean().optional().describe("Whether rule allows or forbids."),
+    action: CASL_ACTION_SCHEMA_ENUM([ProjectPermissionActions.Read]).describe(
+      "Describe what action an entity can take."
+    )
+  }),
+  ...GeneralPermissionSchema
 ]);
 
+export const ProjectPermissionV2Schema = z.discriminatedUnion("subject", [
+  z.object({
+    subject: z.literal(ProjectPermissionSub.Secrets).describe("The entity this permission pertains to."),
+    inverted: z.boolean().optional().describe("Whether rule allows or forbids."),
+    action: CASL_ACTION_SCHEMA_NATIVE_ENUM(ProjectPermissionActions).describe(
+      "Describe what action an entity can take."
+    ),
+    conditions: SecretConditionV2Schema.describe(
+      "When specified, only matching conditions will be allowed to access given resource."
+    ).optional()
+  }),
+  z.object({
+    subject: z.literal(ProjectPermissionSub.SecretFolders).describe("The entity this permission pertains to."),
+    inverted: z.boolean().optional().describe("Whether rule allows or forbids."),
+    action: CASL_ACTION_SCHEMA_NATIVE_ENUM(ProjectPermissionActions).describe(
+      "Describe what action an entity can take."
+    ),
+    conditions: SecretConditionV1Schema.describe(
+      "When specified, only matching conditions will be allowed to access given resource."
+    ).optional()
+  }),
+  z.object({
+    subject: z.literal(ProjectPermissionSub.SecretImports).describe("The entity this permission pertains to."),
+    inverted: z.boolean().optional().describe("Whether rule allows or forbids."),
+    action: CASL_ACTION_SCHEMA_NATIVE_ENUM(ProjectPermissionActions).describe(
+      "Describe what action an entity can take."
+    ),
+    conditions: SecretConditionV1Schema.describe(
+      "When specified, only matching conditions will be allowed to access given resource."
+    ).optional()
+  }),
+  z.object({
+    subject: z.literal(ProjectPermissionSub.DynamicSecrets).describe("The entity this permission pertains to."),
+    inverted: z.boolean().optional().describe("Whether rule allows or forbids."),
+    action: CASL_ACTION_SCHEMA_NATIVE_ENUM(ProjectPermissionDynamicSecretActions).describe(
+      "Describe what action an entity can take."
+    ),
+    conditions: SecretConditionV1Schema.describe(
+      "When specified, only matching conditions will be allowed to access given resource."
+    ).optional()
+  }),
+  ...GeneralPermissionSchema
+]);
+
+export type TProjectPermissionV2Schema = z.infer<typeof ProjectPermissionV2Schema>;
+
 const buildAdminPermissionRules = () => {
   const { can, rules } = new AbilityBuilder<MongoAbility<ProjectPermissionSet>>(createMongoAbility);
 
   // Admins get full access to everything
   [
     ProjectPermissionSub.Secrets,
+    ProjectPermissionSub.SecretFolders,
+    ProjectPermissionSub.SecretImports,
     ProjectPermissionSub.SecretApproval,
     ProjectPermissionSub.SecretRotation,
     ProjectPermissionSub.Member,
@@ -339,6 +461,17 @@ const buildAdminPermissionRules = () => {
     );
   });
 
+  can(
+    [
+      ProjectPermissionDynamicSecretActions.ReadRootCredential,
+      ProjectPermissionDynamicSecretActions.EditRootCredential,
+      ProjectPermissionDynamicSecretActions.CreateRootCredential,
+      ProjectPermissionDynamicSecretActions.DeleteRootCredential,
+      ProjectPermissionDynamicSecretActions.Lease
+    ],
+    ProjectPermissionSub.DynamicSecrets
+  );
+
   can([ProjectPermissionActions.Edit, ProjectPermissionActions.Delete], ProjectPermissionSub.Project);
   can([ProjectPermissionActions.Read, ProjectPermissionActions.Create], ProjectPermissionSub.SecretRollback);
   can([ProjectPermissionActions.Edit], ProjectPermissionSub.Kms);
@@ -370,6 +503,34 @@ const buildMemberPermissionRules = () => {
     ],
     ProjectPermissionSub.Secrets
   );
+  can(
+    [
+      ProjectPermissionActions.Read,
+      ProjectPermissionActions.Edit,
+      ProjectPermissionActions.Create,
+      ProjectPermissionActions.Delete
+    ],
+    ProjectPermissionSub.SecretFolders
+  );
+  can(
+    [
+      ProjectPermissionDynamicSecretActions.ReadRootCredential,
+      ProjectPermissionDynamicSecretActions.EditRootCredential,
+      ProjectPermissionDynamicSecretActions.CreateRootCredential,
+      ProjectPermissionDynamicSecretActions.DeleteRootCredential,
+      ProjectPermissionDynamicSecretActions.Lease
+    ],
+    ProjectPermissionSub.DynamicSecrets
+  );
+  can(
+    [
+      ProjectPermissionActions.Read,
+      ProjectPermissionActions.Edit,
+      ProjectPermissionActions.Create,
+      ProjectPermissionActions.Delete
+    ],
+    ProjectPermissionSub.SecretImports
+  );
 
   can([ProjectPermissionActions.Read], ProjectPermissionSub.SecretApproval);
   can([ProjectPermissionActions.Read], ProjectPermissionSub.SecretRotation);
@@ -493,6 +654,9 @@ const buildViewerPermissionRules = () => {
   const { can, rules } = new AbilityBuilder<MongoAbility<ProjectPermissionSet>>(createMongoAbility);
 
   can(ProjectPermissionActions.Read, ProjectPermissionSub.Secrets);
+  can(ProjectPermissionActions.Read, ProjectPermissionSub.SecretFolders);
+  can(ProjectPermissionDynamicSecretActions.ReadRootCredential, ProjectPermissionSub.DynamicSecrets);
+  can(ProjectPermissionActions.Read, ProjectPermissionSub.SecretImports);
   can(ProjectPermissionActions.Read, ProjectPermissionSub.SecretApproval);
   can(ProjectPermissionActions.Read, ProjectPermissionSub.SecretRollback);
   can(ProjectPermissionActions.Read, ProjectPermissionSub.SecretRotation);
@@ -530,31 +694,35 @@ export const buildServiceTokenProjectPermission = (
   const canRead = permission.includes("read");
   const { can, build } = new AbilityBuilder<MongoAbility<ProjectPermissionSet>>(createMongoAbility);
   scopes.forEach(({ secretPath, environment }) => {
-    if (canWrite) {
-      // TODO: @Akhi
-      // @ts-expect-error type
-      can(ProjectPermissionActions.Edit, ProjectPermissionSub.Secrets, {
-        secretPath: { $glob: secretPath },
-        environment
-      });
-      // @ts-expect-error type
-      can(ProjectPermissionActions.Create, ProjectPermissionSub.Secrets, {
-        secretPath: { $glob: secretPath },
-        environment
-      });
-      // @ts-expect-error type
-      can(ProjectPermissionActions.Delete, ProjectPermissionSub.Secrets, {
-        secretPath: { $glob: secretPath },
-        environment
-      });
-    }
-    if (canRead) {
-      // @ts-expect-error type
-      can(ProjectPermissionActions.Read, ProjectPermissionSub.Secrets, {
-        secretPath: { $glob: secretPath },
-        environment
-      });
-    }
+    [ProjectPermissionSub.Secrets, ProjectPermissionSub.SecretImports, ProjectPermissionSub.SecretFolders].forEach(
+      (subject) => {
+        if (canWrite) {
+          // TODO: @Akhi
+          // @ts-expect-error type
+          can(ProjectPermissionActions.Edit, subject, {
+            secretPath: { $glob: secretPath },
+            environment
+          });
+          // @ts-expect-error type
+          can(ProjectPermissionActions.Create, subject, {
+            secretPath: { $glob: secretPath },
+            environment
+          });
+          // @ts-expect-error type
+          can(ProjectPermissionActions.Delete, subject, {
+            secretPath: { $glob: secretPath },
+            environment
+          });
+        }
+        if (canRead) {
+          // @ts-expect-error type
+          can(ProjectPermissionActions.Read, subject, {
+            secretPath: { $glob: secretPath },
+            environment
+          });
+        }
+      }
+    );
   });
 
   return build({ conditionsMatcher });
@@ -595,17 +763,63 @@ export const isAtLeastAsPrivilegedWorkspace = (
 };
 /* eslint-enable */
 
-export const SecretV2SubjectFieldMapper = (arg: string) => {
-  switch (arg) {
-    case "environment":
-      return null;
-    case "secretPath":
-      return null;
-    case "secretName":
-      return `${TableName.SecretV2}.key`;
-    case "secretTags":
-      return `${TableName.SecretTag}.slug`;
-    default:
-      throw new BadRequestError({ message: `Invalid dynamic knex operator field: ${arg}` });
+export const backfillPermissionV1SchemaToV2Schema = (
+  data: z.infer<typeof ProjectPermissionV1Schema>[],
+  dontRemoveReadFolderPermission?: boolean
+) => {
+  let formattedData = UnpackedPermissionSchema.array().parse(data);
+  const secretSubjects = formattedData.filter((el) => el.subject === ProjectPermissionSub.Secrets);
+
+  // this means the folder permission as readonly is set
+  const hasReadOnlyFolder = formattedData.filter((el) => el.subject === ProjectPermissionSub.SecretFolders);
+  const secretImportPolicies = secretSubjects.map(({ subject, ...el }) => ({
+    ...el,
+    subject: ProjectPermissionSub.SecretImports as const
+  }));
+
+  const secretFolderPolicies = secretSubjects
+    .map(({ subject, ...el }) => ({
+      ...el,
+      // read permission is not needed anymore
+      action: el.action.filter((caslAction) => caslAction !== ProjectPermissionActions.Read),
+      subject: ProjectPermissionSub.SecretFolders
+    }))
+    .filter((el) => el.action?.length > 0);
+
+  const dynamicSecretPolicies = secretSubjects.map(({ subject, ...el }) => {
+    const action = el.action.map((e) => {
+      switch (e) {
+        case ProjectPermissionActions.Edit:
+          return ProjectPermissionDynamicSecretActions.EditRootCredential;
+        case ProjectPermissionActions.Create:
+          return ProjectPermissionDynamicSecretActions.CreateRootCredential;
+        case ProjectPermissionActions.Delete:
+          return ProjectPermissionDynamicSecretActions.DeleteRootCredential;
+        case ProjectPermissionActions.Read:
+          return ProjectPermissionDynamicSecretActions.ReadRootCredential;
+        default:
+          return ProjectPermissionDynamicSecretActions.ReadRootCredential;
+      }
+    });
+
+    return {
+      ...el,
+      action: el.action.includes(ProjectPermissionActions.Edit)
+        ? [...action, ProjectPermissionDynamicSecretActions.Lease]
+        : action,
+      subject: ProjectPermissionSub.DynamicSecrets
+    };
+  });
+
+  if (!dontRemoveReadFolderPermission) {
+    formattedData = formattedData.filter((i) => i.subject !== ProjectPermissionSub.SecretFolders);
   }
+
+  return formattedData.concat(
+    // eslint-disable-next-line @typescript-eslint/ban-ts-comment
+    // @ts-ignore-error this is valid ts
+    secretImportPolicies,
+    dynamicSecretPolicies,
+    hasReadOnlyFolder.length ? [] : secretFolderPolicies
+  );
 };

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

@@ -1,11 +1,16 @@
-import { ForbiddenError } from "@casl/ability";
+import { ForbiddenError, MongoAbility, RawRuleOf } from "@casl/ability";
+import { PackRule, packRules, unpackRules } from "@casl/ability/extra";
 import ms from "ms";
 
-import { BadRequestError, NotFoundError } from "@app/lib/errors";
+import { TableName } from "@app/db/schemas";
+import { isAtLeastAsPrivileged } from "@app/lib/casl";
+import { BadRequestError, ForbiddenRequestError, NotFoundError } from "@app/lib/errors";
+import { UnpackedPermissionSchema } from "@app/server/routes/santizedSchemas/permission";
+import { ActorType } from "@app/services/auth/auth-type";
 import { TProjectMembershipDALFactory } from "@app/services/project-membership/project-membership-dal";
 
 import { TPermissionServiceFactory } from "../permission/permission-service";
-import { ProjectPermissionActions, ProjectPermissionSub } from "../permission/project-permission";
+import { ProjectPermissionActions, ProjectPermissionSet, ProjectPermissionSub } from "../permission/project-permission";
 import { TProjectUserAdditionalPrivilegeDALFactory } from "./project-user-additional-privilege-dal";
 import {
   ProjectUserAdditionalPrivilegeTemporaryMode,
@@ -26,6 +31,11 @@ export type TProjectUserAdditionalPrivilegeServiceFactory = ReturnType<
   typeof projectUserAdditionalPrivilegeServiceFactory
 >;
 
+const unpackPermissions = (permissions: unknown) =>
+  UnpackedPermissionSchema.array().parse(
+    unpackRules((permissions || []) as PackRule<RawRuleOf<MongoAbility<ProjectPermissionSet>>>[])
+  );
+
 export const projectUserAdditionalPrivilegeServiceFactory = ({
   projectUserAdditionalPrivilegeDAL,
   projectMembershipDAL,
@@ -42,7 +52,8 @@ export const projectUserAdditionalPrivilegeServiceFactory = ({
     ...dto
   }: TCreateUserPrivilegeDTO) => {
     const projectMembership = await projectMembershipDAL.findById(projectMembershipId);
-    if (!projectMembership) throw new NotFoundError({ message: "Project membership not found" });
+    if (!projectMembership)
+      throw new NotFoundError({ message: `Project membership with ID ${projectMembershipId} found` });
 
     const { permission } = await permissionService.getProjectPermission(
       actor,
@@ -52,22 +63,41 @@ export const projectUserAdditionalPrivilegeServiceFactory = ({
       actorOrgId
     );
     ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Edit, ProjectPermissionSub.Member);
+    const { permission: targetUserPermission } = await permissionService.getProjectPermission(
+      ActorType.USER,
+      projectMembership.userId,
+      projectMembership.projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+
+    // we need to validate that the privilege given is not higher than the assigning users permission
+    // @ts-expect-error this is expected error because of one being really accurate rule definition other being a bit more broader. Both are valid casl rules
+    targetUserPermission.update(targetUserPermission.rules.concat(customPermission));
+    const hasRequiredPriviledges = isAtLeastAsPrivileged(permission, targetUserPermission);
+    if (!hasRequiredPriviledges)
+      throw new ForbiddenRequestError({ message: "Failed to update more privileged identity" });
 
     const existingSlug = await projectUserAdditionalPrivilegeDAL.findOne({
       slug,
       projectId: projectMembership.projectId,
       userId: projectMembership.userId
     });
-    if (existingSlug) throw new BadRequestError({ message: "Additional privilege of provided slug exist" });
+    if (existingSlug)
+      throw new BadRequestError({ message: `Additional privilege with provided slug ${slug} already exists` });
 
+    const packedPermission = JSON.stringify(packRules(customPermission));
     if (!dto.isTemporary) {
       const additionalPrivilege = await projectUserAdditionalPrivilegeDAL.create({
         userId: projectMembership.userId,
         projectId: projectMembership.projectId,
         slug,
-        permissions: customPermission
+        permissions: packedPermission
       });
-      return additionalPrivilege;
+      return {
+        ...additionalPrivilege,
+        permissions: unpackPermissions(additionalPrivilege.permissions)
+      };
     }
 
     const relativeTempAllocatedTimeInMs = ms(dto.temporaryRange);
@@ -75,14 +105,17 @@ export const projectUserAdditionalPrivilegeServiceFactory = ({
       projectId: projectMembership.projectId,
       userId: projectMembership.userId,
       slug,
-      permissions: customPermission,
+      permissions: packedPermission,
       isTemporary: true,
       temporaryMode: ProjectUserAdditionalPrivilegeTemporaryMode.Relative,
       temporaryRange: dto.temporaryRange,
       temporaryAccessStartTime: new Date(dto.temporaryAccessStartTime),
       temporaryAccessEndTime: new Date(new Date(dto.temporaryAccessStartTime).getTime() + relativeTempAllocatedTimeInMs)
     });
-    return additionalPrivilege;
+    return {
+      ...additionalPrivilege,
+      permissions: unpackPermissions(additionalPrivilege.permissions)
+    };
   };
 
   const updateById = async ({
@@ -94,14 +127,18 @@ export const projectUserAdditionalPrivilegeServiceFactory = ({
     ...dto
   }: TUpdateUserPrivilegeDTO) => {
     const userPrivilege = await projectUserAdditionalPrivilegeDAL.findById(privilegeId);
-    if (!userPrivilege) throw new NotFoundError({ message: "User additional privilege not found" });
+    if (!userPrivilege)
+      throw new NotFoundError({ message: `User additional privilege with ID ${privilegeId} not found` });
 
     const projectMembership = await projectMembershipDAL.findOne({
       userId: userPrivilege.userId,
       projectId: userPrivilege.projectId
     });
 
-    if (!projectMembership) throw new NotFoundError({ message: "Project membership not found" });
+    if (!projectMembership)
+      throw new NotFoundError({
+        message: `Project membership for user with ID '${userPrivilege.userId}' not found in project with ID '${userPrivilege.projectId}'`
+      });
 
     const { permission } = await permissionService.getProjectPermission(
       actor,
@@ -111,6 +148,20 @@ export const projectUserAdditionalPrivilegeServiceFactory = ({
       actorOrgId
     );
     ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Edit, ProjectPermissionSub.Member);
+    const { permission: targetUserPermission } = await permissionService.getProjectPermission(
+      ActorType.USER,
+      projectMembership.userId,
+      projectMembership.projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+
+    // we need to validate that the privilege given is not higher than the assigning users permission
+    // @ts-expect-error this is expected error because of one being really accurate rule definition other being a bit more broader. Both are valid casl rules
+    targetUserPermission.update(targetUserPermission.rules.concat(dto.permissions || []));
+    const hasRequiredPriviledges = isAtLeastAsPrivileged(permission, targetUserPermission);
+    if (!hasRequiredPriviledges)
+      throw new ForbiddenRequestError({ message: "Failed to update more privileged identity" });
 
     if (dto?.slug) {
       const existingSlug = await projectUserAdditionalPrivilegeDAL.findOne({
@@ -119,41 +170,59 @@ export const projectUserAdditionalPrivilegeServiceFactory = ({
         projectId: projectMembership.projectId
       });
       if (existingSlug && existingSlug.id !== userPrivilege.id)
-        throw new BadRequestError({ message: "Additional privilege of provided slug exist" });
+        throw new BadRequestError({ message: `Additional privilege with provided slug ${dto.slug} already exists` });
     }
 
     const isTemporary = typeof dto?.isTemporary !== "undefined" ? dto.isTemporary : userPrivilege.isTemporary;
+
+    const packedPermission = dto.permissions && JSON.stringify(packRules(dto.permissions));
     if (isTemporary) {
       const temporaryAccessStartTime = dto?.temporaryAccessStartTime || userPrivilege?.temporaryAccessStartTime;
       const temporaryRange = dto?.temporaryRange || userPrivilege?.temporaryRange;
       const additionalPrivilege = await projectUserAdditionalPrivilegeDAL.updateById(userPrivilege.id, {
-        ...dto,
+        slug: dto.slug,
+        permissions: packedPermission,
+        isTemporary: dto.isTemporary,
+        temporaryRange: dto.temporaryRange,
+        temporaryMode: dto.temporaryMode,
         temporaryAccessStartTime: new Date(temporaryAccessStartTime || ""),
         temporaryAccessEndTime: new Date(new Date(temporaryAccessStartTime || "").getTime() + ms(temporaryRange || ""))
       });
-      return additionalPrivilege;
+
+      return {
+        ...additionalPrivilege,
+        permissions: unpackPermissions(additionalPrivilege.permissions)
+      };
     }
 
     const additionalPrivilege = await projectUserAdditionalPrivilegeDAL.updateById(userPrivilege.id, {
-      ...dto,
+      slug: dto.slug,
+      permissions: packedPermission,
       isTemporary: false,
       temporaryAccessStartTime: null,
       temporaryAccessEndTime: null,
       temporaryRange: null,
       temporaryMode: null
     });
-    return additionalPrivilege;
+    return {
+      ...additionalPrivilege,
+      permissions: unpackPermissions(additionalPrivilege.permissions)
+    };
   };
 
   const deleteById = async ({ actorId, actor, actorOrgId, actorAuthMethod, privilegeId }: TDeleteUserPrivilegeDTO) => {
     const userPrivilege = await projectUserAdditionalPrivilegeDAL.findById(privilegeId);
-    if (!userPrivilege) throw new NotFoundError({ message: "User additional privilege not found" });
+    if (!userPrivilege)
+      throw new NotFoundError({ message: `User additional privilege with ID ${privilegeId} not found` });
 
     const projectMembership = await projectMembershipDAL.findOne({
       userId: userPrivilege.userId,
       projectId: userPrivilege.projectId
     });
-    if (!projectMembership) throw new NotFoundError({ message: "Project membership not found" });
+    if (!projectMembership)
+      throw new NotFoundError({
+        message: `Project membership for user with ID '${userPrivilege.userId}' not found in project with ID '${userPrivilege.projectId}'`
+      });
 
     const { permission } = await permissionService.getProjectPermission(
       actor,
@@ -165,7 +234,10 @@ export const projectUserAdditionalPrivilegeServiceFactory = ({
     ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Edit, ProjectPermissionSub.Member);
 
     const deletedPrivilege = await projectUserAdditionalPrivilegeDAL.deleteById(userPrivilege.id);
-    return deletedPrivilege;
+    return {
+      ...deletedPrivilege,
+      permissions: unpackPermissions(deletedPrivilege.permissions)
+    };
   };
 
   const getPrivilegeDetailsById = async ({
@@ -176,13 +248,17 @@ export const projectUserAdditionalPrivilegeServiceFactory = ({
     actorAuthMethod
   }: TGetUserPrivilegeDetailsDTO) => {
     const userPrivilege = await projectUserAdditionalPrivilegeDAL.findById(privilegeId);
-    if (!userPrivilege) throw new NotFoundError({ message: "User additional privilege not found" });
+    if (!userPrivilege)
+      throw new NotFoundError({ message: `User additional privilege with ID  ${privilegeId} not found` });
 
     const projectMembership = await projectMembershipDAL.findOne({
       userId: userPrivilege.userId,
       projectId: userPrivilege.projectId
     });
-    if (!projectMembership) throw new NotFoundError({ message: "Project membership not found" });
+    if (!projectMembership)
+      throw new NotFoundError({
+        message: `Project membership for user with ID '${userPrivilege.userId}' not found in project with ID '${userPrivilege.projectId}'`
+      });
 
     const { permission } = await permissionService.getProjectPermission(
       actor,
@@ -193,7 +269,10 @@ export const projectUserAdditionalPrivilegeServiceFactory = ({
     );
     ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Read, ProjectPermissionSub.Member);
 
-    return userPrivilege;
+    return {
+      ...userPrivilege,
+      permissions: unpackPermissions(userPrivilege.permissions)
+    };
   };
 
   const listPrivileges = async ({
@@ -204,7 +283,8 @@ export const projectUserAdditionalPrivilegeServiceFactory = ({
     actorAuthMethod
   }: TListUserPrivilegesDTO) => {
     const projectMembership = await projectMembershipDAL.findById(projectMembershipId);
-    if (!projectMembership) throw new NotFoundError({ message: "Project membership not found" });
+    if (!projectMembership)
+      throw new NotFoundError({ message: `Project membership with ID ${projectMembershipId} not found` });
 
     const { permission } = await permissionService.getProjectPermission(
       actor,
@@ -215,10 +295,13 @@ export const projectUserAdditionalPrivilegeServiceFactory = ({
     );
     ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Read, ProjectPermissionSub.Member);
 
-    const userPrivileges = await projectUserAdditionalPrivilegeDAL.find({
-      userId: projectMembership.userId,
-      projectId: projectMembership.projectId
-    });
+    const userPrivileges = await projectUserAdditionalPrivilegeDAL.find(
+      {
+        userId: projectMembership.userId,
+        projectId: projectMembership.projectId
+      },
+      { sort: [[`${TableName.ProjectUserAdditionalPrivilege}.slug` as "slug", "asc"]] }
+    );
     return userPrivileges;
   };
 

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

@@ -1,18 +1,20 @@
 import { TProjectPermission } from "@app/lib/types";
 
+import { TProjectPermissionV2Schema } from "../permission/project-permission";
+
 export enum ProjectUserAdditionalPrivilegeTemporaryMode {
   Relative = "relative"
 }
 
 export type TCreateUserPrivilegeDTO = (
   | {
-      permissions: unknown;
+      permissions: TProjectPermissionV2Schema[];
       projectMembershipId: string;
       slug: string;
       isTemporary: false;
     }
   | {
-      permissions: unknown;
+      permissions: TProjectPermissionV2Schema[];
       projectMembershipId: string;
       slug: string;
       isTemporary: true;
@@ -25,7 +27,7 @@ export type TCreateUserPrivilegeDTO = (
 
 export type TUpdateUserPrivilegeDTO = { privilegeId: string } & Omit<TProjectPermission, "projectId"> &
   Partial<{
-    permissions: unknown;
+    permissions: TProjectPermissionV2Schema[];
     slug: string;
     isTemporary: boolean;
     temporaryMode: ProjectUserAdditionalPrivilegeTemporaryMode.Relative;

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

@@ -2,7 +2,6 @@ import { ForbiddenError } from "@casl/ability";
 import jwt from "jsonwebtoken";
 
 import {
-  OrgMembershipRole,
   OrgMembershipStatus,
   SecretKeyEncoding,
   TableName,
@@ -26,6 +25,7 @@ import { TokenType } from "@app/services/auth-token/auth-token-types";
 import { TIdentityMetadataDALFactory } from "@app/services/identity/identity-metadata-dal";
 import { TOrgBotDALFactory } from "@app/services/org/org-bot-dal";
 import { TOrgDALFactory } from "@app/services/org/org-dal";
+import { getDefaultOrgMembershipRole } from "@app/services/org/org-role-fns";
 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";
@@ -191,7 +191,11 @@ export const samlConfigServiceFactory = ({
 
     const updateQuery: TSamlConfigsUpdate = { authProvider, isActive, lastUsed: null };
     const orgBot = await orgBotDAL.findOne({ orgId });
-    if (!orgBot) throw new NotFoundError({ message: "Organization bot not found", name: "OrgBotNotFound" });
+    if (!orgBot)
+      throw new NotFoundError({
+        message: `Organization bot not found for organization with ID '${orgId}'`,
+        name: "OrgBotNotFound"
+      });
     const key = infisicalSymmetricDecrypt({
       ciphertext: orgBot.encryptedSymmetricKey,
       iv: orgBot.symmetricKeyIV,
@@ -257,7 +261,7 @@ export const samlConfigServiceFactory = ({
 
       ssoConfig = await samlConfigDAL.findById(id);
     }
-    if (!ssoConfig) throw new NotFoundError({ message: "Failed to find organization SSO data" });
+    if (!ssoConfig) throw new NotFoundError({ message: `Failed to find SSO data` });
 
     // when dto is type id means it's internally used
     if (dto.type === "org") {
@@ -283,7 +287,11 @@ export const samlConfigServiceFactory = ({
     } = ssoConfig;
 
     const orgBot = await orgBotDAL.findOne({ orgId: ssoConfig.orgId });
-    if (!orgBot) throw new NotFoundError({ message: "Organization bot not found", name: "OrgBotNotFound" });
+    if (!orgBot)
+      throw new NotFoundError({
+        message: `Organization bot not found in organization with ID '${ssoConfig.orgId}'`,
+        name: "OrgBotNotFound"
+      });
     const key = infisicalSymmetricDecrypt({
       ciphertext: orgBot.encryptedSymmetricKey,
       iv: orgBot.symmetricKeyIV,
@@ -355,7 +363,7 @@ export const samlConfigServiceFactory = ({
     });
 
     const organization = await orgDAL.findOrgById(orgId);
-    if (!organization) throw new NotFoundError({ message: "Organization not found" });
+    if (!organization) throw new NotFoundError({ message: `Organization with ID '${orgId}' not found` });
 
     let user: TUsers;
     if (userAlias) {
@@ -369,12 +377,15 @@ export const samlConfigServiceFactory = ({
           { tx }
         );
         if (!orgMembership) {
+          const { role, roleId } = await getDefaultOrgMembershipRole(organization.defaultMembershipRole);
+
           await orgMembershipDAL.create(
             {
               userId: userAlias.userId,
               inviteEmail: email,
               orgId,
-              role: OrgMembershipRole.Member,
+              role,
+              roleId,
               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
               isActive: true
             },
@@ -472,12 +483,15 @@ export const samlConfigServiceFactory = ({
         );
 
         if (!orgMembership) {
+          const { role, roleId } = await getDefaultOrgMembershipRole(organization.defaultMembershipRole);
+
           await orgMembershipDAL.create(
             {
               userId: newUser.id,
               inviteEmail: email,
               orgId,
-              role: OrgMembershipRole.Member,
+              role,
+              roleId,
               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
               isActive: true
             },

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

@@ -3,7 +3,7 @@ import slugify from "@sindresorhus/slugify";
 import jwt from "jsonwebtoken";
 import { scimPatch } from "scim-patch";
 
-import { OrgMembershipRole, OrgMembershipStatus, TableName, TOrgMemberships, TUsers } 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";
@@ -13,9 +13,11 @@ import { BadRequestError, NotFoundError, ScimRequestError, UnauthorizedError } f
 import { alphaNumericNanoId } from "@app/lib/nanoid";
 import { TOrgPermission } from "@app/lib/types";
 import { AuthTokenType } from "@app/services/auth/auth-type";
+import { TExternalGroupOrgRoleMappingDALFactory } from "@app/services/external-group-org-role-mapping/external-group-org-role-mapping-dal";
 import { TGroupProjectDALFactory } from "@app/services/group-project/group-project-dal";
 import { TOrgDALFactory } from "@app/services/org/org-dal";
 import { deleteOrgMembershipFn } from "@app/services/org/org-fns";
+import { getDefaultOrgMembershipRole } from "@app/services/org/org-role-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";
@@ -70,7 +72,10 @@ type TScimServiceFactoryDep = {
     | "transaction"
     | "updateMembershipById"
   >;
-  orgMembershipDAL: Pick<TOrgMembershipDALFactory, "find" | "findOne" | "create" | "updateById" | "findById">;
+  orgMembershipDAL: Pick<
+    TOrgMembershipDALFactory,
+    "find" | "findOne" | "create" | "updateById" | "findById" | "update"
+  >;
   projectDAL: Pick<TProjectDALFactory, "find" | "findProjectGhostUser">;
   projectMembershipDAL: Pick<TProjectMembershipDALFactory, "find" | "delete" | "findProjectMembershipsByUserId">;
   groupDAL: Pick<
@@ -101,6 +106,7 @@ type TScimServiceFactoryDep = {
   permissionService: Pick<TPermissionServiceFactory, "getOrgPermission">;
   smtpService: Pick<TSmtpService, "sendMail">;
   projectUserAdditionalPrivilegeDAL: Pick<TProjectUserAdditionalPrivilegeDALFactory, "delete">;
+  externalGroupOrgRoleMappingDAL: TExternalGroupOrgRoleMappingDALFactory;
 };
 
 export type TScimServiceFactory = ReturnType<typeof scimServiceFactory>;
@@ -121,7 +127,8 @@ export const scimServiceFactory = ({
   projectBotDAL,
   permissionService,
   projectUserAdditionalPrivilegeDAL,
-  smtpService
+  smtpService,
+  externalGroupOrgRoleMappingDAL
 }: TScimServiceFactoryDep) => {
   const createScimToken = async ({
     actor,
@@ -176,7 +183,7 @@ export const scimServiceFactory = ({
 
   const deleteScimToken = async ({ scimTokenId, actor, actorId, actorAuthMethod, actorOrgId }: TDeleteScimTokenDTO) => {
     let scimToken = await scimDAL.findById(scimTokenId);
-    if (!scimToken) throw new NotFoundError({ message: "Failed to find SCIM token to delete" });
+    if (!scimToken) throw new NotFoundError({ message: `SCIM token with ID '${scimTokenId}' not found` });
 
     const { permission } = await permissionService.getOrgPermission(
       actor,
@@ -318,12 +325,15 @@ export const scimServiceFactory = ({
         );
 
         if (!orgMembership) {
+          const { role, roleId } = await getDefaultOrgMembershipRole(org.defaultMembershipRole);
+
           orgMembership = await orgMembershipDAL.create(
             {
               userId: userAlias.userId,
               inviteEmail: email,
               orgId,
-              role: OrgMembershipRole.NoAccess,
+              role,
+              roleId,
               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
               isActive: true
             },
@@ -391,12 +401,15 @@ export const scimServiceFactory = ({
         orgMembership = foundOrgMembership;
 
         if (!orgMembership) {
+          const { role, roleId } = await getDefaultOrgMembershipRole(org.defaultMembershipRole);
+
           orgMembership = await orgMembershipDAL.create(
             {
               userId: user.id,
               inviteEmail: email,
               orgId,
-              role: OrgMembershipRole.Member,
+              role,
+              roleId,
               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
               isActive: true
             },
@@ -685,6 +698,43 @@ export const scimServiceFactory = ({
     });
   };
 
+  const $syncNewMembersRoles = async (group: TGroups, members: TScimGroup["members"]) => {
+    // this function handles configuring newly provisioned users org membership if an external group mapping exists
+
+    if (!members.length) return;
+
+    const externalGroupMapping = await externalGroupOrgRoleMappingDAL.findOne({
+      orgId: group.orgId,
+      groupName: group.name
+    });
+
+    // no mapping, user will have default org membership
+    if (!externalGroupMapping) return;
+
+    // only get org memberships that are new (invites)
+    const newOrgMemberships = await orgMembershipDAL.find({
+      status: "invited",
+      $in: {
+        id: members.map((member) => member.value)
+      }
+    });
+
+    if (!newOrgMemberships.length) return;
+
+    // set new membership roles to group mapping value
+    await orgMembershipDAL.update(
+      {
+        $in: {
+          id: newOrgMemberships.map((membership) => membership.id)
+        }
+      },
+      {
+        role: externalGroupMapping.role,
+        roleId: externalGroupMapping.roleId
+      }
+    );
+  };
+
   const createScimGroup = async ({ displayName, orgId, members }: TCreateScimGroupDTO) => {
     const plan = await licenseService.getPlan(orgId);
     if (!plan.groups)
@@ -738,6 +788,8 @@ export const scimServiceFactory = ({
           tx
         });
 
+        await $syncNewMembersRoles(group, members);
+
         return { group, newMembers };
       }
 
@@ -782,10 +834,12 @@ export const scimServiceFactory = ({
       });
     }
 
-    const users = await groupDAL.findAllGroupPossibleMembers({
-      orgId: group.orgId,
-      groupId: group.id
-    });
+    const users = await groupDAL
+      .findAllGroupPossibleMembers({
+        orgId: group.orgId,
+        groupId: group.id
+      })
+      .then((g) => g.members);
 
     const orgMemberships = await orgDAL.findMembership({
       [`${TableName.OrgMembership}.orgId` as "orgId"]: orgId,
@@ -813,22 +867,41 @@ export const scimServiceFactory = ({
     orgId: string,
     { displayName, members = [] }: { displayName: string; members: { value: string }[] }
   ) => {
-    const updatedGroup = await groupDAL.transaction(async (tx) => {
-      const [group] = await groupDAL.update(
-        {
-          id: groupId,
-          orgId
-        },
-        {
-          name: displayName
-        }
-      );
+    let group = await groupDAL.findOne({
+      id: groupId,
+      orgId
+    });
 
-      if (!group) {
-        throw new ScimRequestError({
-          detail: "Group Not Found",
-          status: 404
-        });
+    if (!group) {
+      throw new ScimRequestError({
+        detail: "Group Not Found",
+        status: 404
+      });
+    }
+
+    const updatedGroup = await groupDAL.transaction(async (tx) => {
+      if (group.name !== displayName) {
+        await externalGroupOrgRoleMappingDAL.update(
+          {
+            groupName: group.name,
+            orgId
+          },
+          {
+            groupName: displayName
+          }
+        );
+
+        const [modifiedGroup] = await groupDAL.update(
+          {
+            id: groupId,
+            orgId
+          },
+          {
+            name: displayName
+          }
+        );
+
+        group = modifiedGroup;
       }
 
       const orgMemberships = members.length
@@ -885,6 +958,8 @@ export const scimServiceFactory = ({
       return group;
     });
 
+    await $syncNewMembersRoles(group, members);
+
     return updatedGroup;
   };
 

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

@@ -14,7 +14,7 @@ export const secretApprovalPolicyDALFactory = (db: TDbClient) => {
 
   const secretApprovalPolicyFindQuery = (
     tx: Knex,
-    filter: TFindFilter<TSecretApprovalPolicies>,
+    filter: TFindFilter<TSecretApprovalPolicies & { projectId: string }>,
     customFilter?: {
       sapId?: string;
     }

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

@@ -1,4 +1,4 @@
-import { ForbiddenError, subject } from "@casl/ability";
+import { ForbiddenError } from "@casl/ability";
 import picomatch from "picomatch";
 
 import { TPermissionServiceFactory } from "@app/ee/services/permission/permission-service";
@@ -95,7 +95,10 @@ export const secretApprovalPolicyServiceFactory = ({
     }
 
     const env = await projectEnvDAL.findOne({ slug: environment, projectId });
-    if (!env) throw new NotFoundError({ message: "Environment not found" });
+    if (!env)
+      throw new NotFoundError({
+        message: `Environment with slug '${environment}' not found in project with ID ${projectId}`
+      });
 
     const secretApproval = await secretApprovalPolicyDAL.transaction(async (tx) => {
       const doc = await secretApprovalPolicyDAL.create(
@@ -178,7 +181,11 @@ export const secretApprovalPolicyServiceFactory = ({
       .filter(Boolean) as string[];
 
     const secretApprovalPolicy = await secretApprovalPolicyDAL.findById(secretPolicyId);
-    if (!secretApprovalPolicy) throw new NotFoundError({ message: "Secret approval policy not found" });
+    if (!secretApprovalPolicy) {
+      throw new NotFoundError({
+        message: `Secret approval policy with ID '${secretPolicyId}' not found`
+      });
+    }
 
     const { permission } = await permissionService.getProjectPermission(
       actor,
@@ -271,7 +278,8 @@ export const secretApprovalPolicyServiceFactory = ({
     actorOrgId
   }: TDeleteSapDTO) => {
     const sapPolicy = await secretApprovalPolicyDAL.findById(secretPolicyId);
-    if (!sapPolicy) throw new NotFoundError({ message: "Secret approval policy not found" });
+    if (!sapPolicy)
+      throw new NotFoundError({ message: `Secret approval policy with ID '${secretPolicyId}' not found` });
 
     const { permission } = await permissionService.getProjectPermission(
       actor,
@@ -320,7 +328,11 @@ export const secretApprovalPolicyServiceFactory = ({
   const getSecretApprovalPolicy = async (projectId: string, environment: string, path: string) => {
     const secretPath = removeTrailingSlash(path);
     const env = await projectEnvDAL.findOne({ slug: environment, projectId });
-    if (!env) throw new NotFoundError({ message: "Environment not found" });
+    if (!env) {
+      throw new NotFoundError({
+        message: `Environment with slug '${environment}' not found in project with ID ${projectId}`
+      });
+    }
 
     const policies = await secretApprovalPolicyDAL.find({ envId: env.id });
     if (!policies.length) return;
@@ -344,17 +356,8 @@ export const secretApprovalPolicyServiceFactory = ({
     environment,
     secretPath
   }: TGetBoardSapDTO) => {
-    const { permission } = await permissionService.getProjectPermission(
-      actor,
-      actorId,
-      projectId,
-      actorAuthMethod,
-      actorOrgId
-    );
-    ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionActions.Read,
-      subject(ProjectPermissionSub.Secrets, { secretPath, environment })
-    );
+    await permissionService.getProjectPermission(actor, actorId, projectId, actorAuthMethod, actorOrgId);
+
     return getSecretApprovalPolicy(projectId, environment, secretPath);
   };
 
@@ -369,7 +372,7 @@ export const secretApprovalPolicyServiceFactory = ({
 
     if (!sapPolicy) {
       throw new NotFoundError({
-        message: "Cannot find secret approval policy"
+        message: `Secret approval policy with ID '${sapId}' not found`
       });
     }
 

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

@@ -43,7 +43,7 @@ import {
   fnSecretBulkDelete as fnSecretV2BridgeBulkDelete,
   fnSecretBulkInsert as fnSecretV2BridgeBulkInsert,
   fnSecretBulkUpdate as fnSecretV2BridgeBulkUpdate,
-  getAllNestedSecretReferences as getAllNestedSecretReferencesV2Bridge
+  getAllSecretReferences as getAllSecretReferencesV2Bridge
 } from "@app/services/secret-v2-bridge/secret-v2-bridge-fns";
 import { TSecretVersionV2DALFactory } from "@app/services/secret-v2-bridge/secret-version-dal";
 import { TSecretVersionV2TagDALFactory } from "@app/services/secret-v2-bridge/secret-version-tag-dal";
@@ -204,7 +204,8 @@ export const secretApprovalRequestServiceFactory = ({
     if (actor === ActorType.SERVICE) throw new BadRequestError({ message: "Cannot use service token" });
 
     const secretApprovalRequest = await secretApprovalRequestDAL.findById(id);
-    if (!secretApprovalRequest) throw new NotFoundError({ message: "Secret approval request not found" });
+    if (!secretApprovalRequest)
+      throw new NotFoundError({ message: `Secret approval request with ID '${id}' not found` });
 
     const { projectId } = secretApprovalRequest;
     const { botKey, shouldUseSecretV2Bridge } = await projectBotService.getBotKey(projectId);
@@ -266,12 +267,13 @@ export const secretApprovalRequestServiceFactory = ({
                 : "",
               secretComment: el.secretVersion.encryptedComment
                 ? secretManagerDecryptor({ cipherTextBlob: el.secretVersion.encryptedComment }).toString()
-                : ""
+                : "",
+              tags: el.secretVersion.tags
             }
           : undefined
       }));
     } else {
-      if (!botKey) throw new NotFoundError({ message: "Project bot key not found" });
+      if (!botKey) throw new NotFoundError({ message: `Project bot key not found`, name: "BotKeyNotFound" }); // CLI depends on this error message. TODO(daniel): Make API check for name BotKeyNotFound instead of message
       const encrypedSecrets = await secretApprovalRequestSecretDAL.findByRequestId(secretApprovalRequest.id);
       secrets = encrypedSecrets.map((el) => ({
         ...el,
@@ -307,7 +309,9 @@ export const secretApprovalRequestServiceFactory = ({
     actorOrgId
   }: TReviewRequestDTO) => {
     const secretApprovalRequest = await secretApprovalRequestDAL.findById(approvalId);
-    if (!secretApprovalRequest) throw new NotFoundError({ message: "Secret approval request not found" });
+    if (!secretApprovalRequest) {
+      throw new NotFoundError({ message: `Secret approval request with ID '${approvalId}' not found` });
+    }
     if (actor !== ActorType.USER) throw new BadRequestError({ message: "Must be a user" });
 
     const plan = await licenseService.getPlan(actorOrgId);
@@ -365,7 +369,9 @@ export const secretApprovalRequestServiceFactory = ({
     actorAuthMethod
   }: TStatusChangeDTO) => {
     const secretApprovalRequest = await secretApprovalRequestDAL.findById(approvalId);
-    if (!secretApprovalRequest) throw new NotFoundError({ message: "Secret approval request not found" });
+    if (!secretApprovalRequest) {
+      throw new NotFoundError({ message: `Secret approval request with ID '${approvalId}' not found` });
+    }
     if (actor !== ActorType.USER) throw new BadRequestError({ message: "Must be a user" });
 
     const plan = await licenseService.getPlan(actorOrgId);
@@ -414,7 +420,8 @@ export const secretApprovalRequestServiceFactory = ({
     bypassReason
   }: TMergeSecretApprovalRequestDTO) => {
     const secretApprovalRequest = await secretApprovalRequestDAL.findById(approvalId);
-    if (!secretApprovalRequest) throw new NotFoundError({ message: "Secret approval request not found" });
+    if (!secretApprovalRequest)
+      throw new NotFoundError({ message: `Secret approval request with ID '${approvalId}' not found` });
     if (actor !== ActorType.USER) throw new BadRequestError({ message: "Must be a user" });
 
     const plan = await licenseService.getPlan(actorOrgId);
@@ -462,7 +469,9 @@ export const secretApprovalRequestServiceFactory = ({
       const secretApprovalSecrets = await secretApprovalRequestSecretDAL.findByRequestIdBridgeSecretV2(
         secretApprovalRequest.id
       );
-      if (!secretApprovalSecrets) throw new NotFoundError({ message: "No secrets found" });
+      if (!secretApprovalSecrets) {
+        throw new NotFoundError({ message: `No secrets found in secret change request with ID '${approvalId}'` });
+      }
 
       const { decryptor: secretManagerDecryptor } = await kmsService.createCipherPairWithDataKey({
         type: KmsDataKey.SecretManager,
@@ -523,11 +532,11 @@ export const secretApprovalRequestServiceFactory = ({
                 skipMultilineEncoding: el.skipMultilineEncoding,
                 key: el.key,
                 references: el.encryptedValue
-                  ? getAllNestedSecretReferencesV2Bridge(
+                  ? getAllSecretReferencesV2Bridge(
                       secretManagerDecryptor({
                         cipherTextBlob: el.encryptedValue
                       }).toString()
-                    )
+                    ).nestedReferences
                   : [],
                 type: SecretType.Shared
               })),
@@ -547,11 +556,11 @@ export const secretApprovalRequestServiceFactory = ({
                     ? {
                         encryptedValue: el.encryptedValue as Buffer,
                         references: el.encryptedValue
-                          ? getAllNestedSecretReferencesV2Bridge(
+                          ? getAllSecretReferencesV2Bridge(
                               secretManagerDecryptor({
                                 cipherTextBlob: el.encryptedValue
                               }).toString()
-                            )
+                            ).nestedReferences
                           : []
                       }
                     : {};
@@ -563,7 +572,7 @@ export const secretApprovalRequestServiceFactory = ({
                     reminderNote: el.reminderNote,
                     skipMultilineEncoding: el.skipMultilineEncoding,
                     key: el.key,
-                    tagIds: el?.tags.map(({ id }) => id),
+                    tags: el?.tags.map(({ id }) => id),
                     ...encryptedValue
                   }
                 };
@@ -602,7 +611,9 @@ export const secretApprovalRequestServiceFactory = ({
       });
     } else {
       const secretApprovalSecrets = await secretApprovalRequestSecretDAL.findByRequestId(secretApprovalRequest.id);
-      if (!secretApprovalSecrets) throw new NotFoundError({ message: "No secrets found" });
+      if (!secretApprovalSecrets) {
+        throw new NotFoundError({ message: `No secrets found in secret change request with ID '${approvalId}'` });
+      }
 
       const conflicts: Array<{ secretId: string; op: SecretOperations }> = [];
       let secretCreationCommits = secretApprovalSecrets.filter(({ op }) => op === SecretOperations.Create);
@@ -610,10 +621,10 @@ export const secretApprovalRequestServiceFactory = ({
         const { secsGroupedByBlindIndex: conflictGroupByBlindIndex } = await fnSecretBlindIndexCheckV2({
           folderId,
           secretDAL,
-          inputSecrets: secretCreationCommits.map(({ secretBlindIndex }) => {
+          inputSecrets: secretCreationCommits.map(({ secretBlindIndex, secret }) => {
             if (!secretBlindIndex) {
               throw new NotFoundError({
-                message: "Secret blind index not found"
+                message: `Secret blind index not found on secret with ID '${secret.id}`
               });
             }
             return { secretBlindIndex };
@@ -637,10 +648,10 @@ export const secretApprovalRequestServiceFactory = ({
           userId: "",
           inputSecrets: secretUpdationCommits
             .filter(({ secretBlindIndex, secret }) => secret && secret.secretBlindIndex !== secretBlindIndex)
-            .map(({ secretBlindIndex }) => {
+            .map(({ secretBlindIndex, secret }) => {
               if (!secretBlindIndex) {
                 throw new NotFoundError({
-                  message: "Secret blind index not found"
+                  message: `Secret blind index not found on secret with ID '${secret.id}`
                 });
               }
               return { secretBlindIndex };
@@ -760,10 +771,10 @@ export const secretApprovalRequestServiceFactory = ({
               actorId: "",
               secretDAL,
               secretQueueService,
-              inputSecrets: secretDeletionCommits.map(({ secretBlindIndex }) => {
+              inputSecrets: secretDeletionCommits.map(({ secretBlindIndex, secret }) => {
                 if (!secretBlindIndex) {
                   throw new NotFoundError({
-                    message: "Secret blind index not found"
+                    message: `Secret blind index not found on secret with ID '${secret.id}`
                   });
                 }
                 return { secretBlindIndex, type: SecretType.Shared };
@@ -789,7 +800,9 @@ export const secretApprovalRequestServiceFactory = ({
 
     await snapshotService.performSnapshot(folderId);
     const [folder] = await folderDAL.findSecretPathByFolderIds(projectId, [folderId]);
-    if (!folder) throw new NotFoundError({ message: "Folder not found" });
+    if (!folder) {
+      throw new NotFoundError({ message: `Folder with ID '${folderId}' not found in project with ID '${projectId}'` });
+    }
     await secretQueueService.syncSecrets({
       projectId,
       secretPath: folder.path,
@@ -861,14 +874,18 @@ export const secretApprovalRequestServiceFactory = ({
     const folder = await folderDAL.findBySecretPath(projectId, environment, secretPath);
     if (!folder)
       throw new NotFoundError({
-        message: "Folder not found for the given environment slug & secret path",
+        message: `Folder not found for environment with slug '${environment}' & secret path '${secretPath}'`,
         name: "GenSecretApproval"
       });
     const folderId = folder.id;
 
     const blindIndexCfg = await secretBlindIndexDAL.findOne({ projectId });
-    if (!blindIndexCfg) throw new NotFoundError({ message: "Blind index not found", name: "Update secret" });
-
+    if (!blindIndexCfg) {
+      throw new NotFoundError({
+        message: `Blind index not found for project with ID '${projectId}'`,
+        name: "Update secret"
+      });
+    }
     const commits: Omit<TSecretApprovalRequestsSecretsInsert, "requestId">[] = [];
     const commitTagIds: Record<string, string[]> = {};
     // for created secret approval change
@@ -961,7 +978,9 @@ export const secretApprovalRequestServiceFactory = ({
         secretDAL
       });
       const secretsGroupedByBlindIndex = groupBy(secrets, (i) => {
-        if (!i.secretBlindIndex) throw new NotFoundError({ message: "Secret blind index not found" });
+        if (!i.secretBlindIndex) {
+          throw new NotFoundError({ message: `Secret blind index not found for secret with ID '${i.id}'` });
+        }
         return i.secretBlindIndex;
       });
       const deletedSecretIds = deletedSecrets.map(
@@ -972,7 +991,7 @@ export const secretApprovalRequestServiceFactory = ({
         ...deletedSecrets.map((el) => {
           const secretId = secretsGroupedByBlindIndex[keyName2BlindIndex[el.secretName]][0].id;
           if (!latestSecretVersions[secretId].secretBlindIndex)
-            throw new NotFoundError({ message: "Secret blind index not found" });
+            throw new NotFoundError({ message: `Secret blind index not found for secret with ID '${secretId}'` });
           return {
             op: SecretOperations.Delete as const,
             ...latestSecretVersions[secretId],
@@ -988,7 +1007,7 @@ export const secretApprovalRequestServiceFactory = ({
 
     const tagIds = unique(Object.values(commitTagIds).flat());
     const tags = tagIds.length ? await secretTagDAL.findManyTagsById(projectId, tagIds) : [];
-    if (tagIds.length !== tags.length) throw new NotFoundError({ message: "Tag not found" });
+    if (tagIds.length !== tags.length) throw new NotFoundError({ message: "One or more tags not found" });
 
     const secretApprovalRequest = await secretApprovalRequestDAL.transaction(async (tx) => {
       const doc = await secretApprovalRequestDAL.create(
@@ -1054,7 +1073,7 @@ export const secretApprovalRequestServiceFactory = ({
 
       const commitsGroupByBlindIndex = groupBy(approvalCommits, (i) => {
         if (!i.secretBlindIndex) {
-          throw new NotFoundError({ message: "Secret blind index not found" });
+          throw new NotFoundError({ message: `Secret blind index not found for secret with ID '${i.id}'` });
         }
         return i.secretBlindIndex;
       });
@@ -1125,15 +1144,11 @@ export const secretApprovalRequestServiceFactory = ({
       actorAuthMethod,
       actorOrgId
     );
-    ForbiddenError.from(permission).throwUnlessCan(
-      ProjectPermissionActions.Read,
-      subject(ProjectPermissionSub.Secrets, { environment, secretPath })
-    );
 
     const folder = await folderDAL.findBySecretPath(projectId, environment, secretPath);
     if (!folder)
       throw new NotFoundError({
-        message: "Folder not found for the given environment slug & secret path",
+        message: `Folder not found for the environment slug '${environment}' & secret path '${secretPath}'`,
         name: "GenSecretApproval"
       });
     const folderId = folder.id;
@@ -1292,6 +1307,23 @@ export const secretApprovalRequestServiceFactory = ({
     const tagIds = unique(Object.values(commitTagIds).flat());
     const tags = tagIds.length ? await secretTagDAL.findManyTagsById(projectId, tagIds) : [];
     if (tagIds.length !== tags.length) throw new NotFoundError({ message: "Tag not found" });
+    const tagsGroupById = groupBy(tags, (i) => i.id);
+
+    commits.forEach((commit) => {
+      let action = ProjectPermissionActions.Create;
+      if (commit.op === SecretOperations.Update) action = ProjectPermissionActions.Edit;
+      if (commit.op === SecretOperations.Delete) action = ProjectPermissionActions.Delete;
+
+      ForbiddenError.from(permission).throwUnlessCan(
+        action,
+        subject(ProjectPermissionSub.Secrets, {
+          environment,
+          secretPath,
+          secretName: commit.key,
+          secretTags: commitTagIds?.[commit.key]?.map((secretTagId) => tagsGroupById[secretTagId][0].slug)
+        })
+      );
+    });
 
     const secretApprovalRequest = await secretApprovalRequestDAL.transaction(async (tx) => {
       const doc = await secretApprovalRequestDAL.create(

backend/src/ee/services/secret-replication/secret-replication-service.ts

@@ -28,8 +28,7 @@ import { TSecretV2BridgeDALFactory } from "@app/services/secret-v2-bridge/secret
 import {
   fnSecretBulkInsert as fnSecretV2BridgeBulkInsert,
   fnSecretBulkUpdate as fnSecretV2BridgeBulkUpdate,
-  getAllNestedSecretReferences,
-  getAllNestedSecretReferences as getAllNestedSecretReferencesV2Bridge
+  getAllSecretReferences
 } from "@app/services/secret-v2-bridge/secret-v2-bridge-fns";
 import { TSecretVersionV2DALFactory } from "@app/services/secret-v2-bridge/secret-version-dal";
 import { TSecretVersionV2TagDALFactory } from "@app/services/secret-v2-bridge/secret-version-tag-dal";
@@ -253,11 +252,12 @@ export const secretReplicationServiceFactory = ({
       const sourceLocalSecrets = await secretV2BridgeDAL.find({ folderId: folder.id, type: SecretType.Shared });
       const sourceSecretImports = await secretImportDAL.find({ folderId: folder.id });
       const sourceImportedSecrets = await fnSecretsV2FromImports({
-        allowedImports: sourceSecretImports,
+        secretImports: sourceSecretImports,
         secretDAL: secretV2BridgeDAL,
         folderDAL,
         secretImportDAL,
-        decryptor: (value) => (value ? secretManagerDecryptor({ cipherTextBlob: value }).toString() : "")
+        decryptor: (value) => (value ? secretManagerDecryptor({ cipherTextBlob: value }).toString() : ""),
+        hasSecretAccess: () => true
       });
       // secrets that gets replicated across imports
       const sourceDecryptedLocalSecrets = sourceLocalSecrets.map((el) => ({
@@ -295,7 +295,10 @@ export const secretReplicationServiceFactory = ({
             const [destinationFolder] = await folderDAL.findSecretPathByFolderIds(projectId, [
               destinationSecretImport.folderId
             ]);
-            if (!destinationFolder) throw new NotFoundError({ message: "Imported folder not found" });
+            if (!destinationFolder)
+              throw new NotFoundError({
+                message: `Imported folder with ID '${destinationSecretImport.folderId}' not found in project with ID ${projectId}`
+              });
 
             let destinationReplicationFolder = await folderDAL.findOne({
               parentId: destinationFolder.id,
@@ -416,7 +419,7 @@ export const secretReplicationServiceFactory = ({
                         encryptedValue: doc.encryptedValue,
                         encryptedComment: doc.encryptedComment,
                         skipMultilineEncoding: doc.skipMultilineEncoding,
-                        references: doc.secretValue ? getAllNestedSecretReferencesV2Bridge(doc.secretValue) : []
+                        references: doc.secretValue ? getAllSecretReferences(doc.secretValue).nestedReferences : []
                       };
                     })
                   });
@@ -442,7 +445,7 @@ export const secretReplicationServiceFactory = ({
                           encryptedValue: doc.encryptedValue as Buffer,
                           encryptedComment: doc.encryptedComment,
                           skipMultilineEncoding: doc.skipMultilineEncoding,
-                          references: doc.secretValue ? getAllNestedSecretReferencesV2Bridge(doc.secretValue) : []
+                          references: doc.secretValue ? getAllSecretReferences(doc.secretValue).nestedReferences : []
                         }
                       };
                     })
@@ -506,7 +509,7 @@ export const secretReplicationServiceFactory = ({
       return;
     }
 
-    if (!botKey) throw new NotFoundError({ message: "Project bot not found" });
+    if (!botKey) throw new NotFoundError({ message: `Bot key not found for project with ID ${projectId}` });
     // these are the secrets to be added in replicated folders
     const sourceLocalSecrets = await secretDAL.find({ folderId: folder.id, type: SecretType.Shared });
     const sourceSecretImports = await secretImportDAL.find({ folderId: folder.id });
@@ -545,7 +548,11 @@ export const secretReplicationServiceFactory = ({
           const [destinationFolder] = await folderDAL.findSecretPathByFolderIds(projectId, [
             destinationSecretImport.folderId
           ]);
-          if (!destinationFolder) throw new NotFoundError({ message: "Imported folder not found" });
+          if (!destinationFolder) {
+            throw new NotFoundError({
+              message: `Imported folder with ID '${destinationSecretImport.folderId}' not found in project with ID ${projectId}`
+            });
+          }
 
           let destinationReplicationFolder = await folderDAL.findOne({
             parentId: destinationFolder.id,
@@ -687,7 +694,7 @@ export const secretReplicationServiceFactory = ({
                       secretCommentTag: doc.secretCommentTag,
                       secretCommentCiphertext: doc.secretCommentCiphertext,
                       skipMultilineEncoding: doc.skipMultilineEncoding,
-                      references: getAllNestedSecretReferences(doc.secretValue)
+                      references: getAllSecretReferences(doc.secretValue).nestedReferences
                     };
                   })
                 });
@@ -723,7 +730,7 @@ export const secretReplicationServiceFactory = ({
                         secretCommentTag: doc.secretCommentTag,
                         secretCommentCiphertext: doc.secretCommentCiphertext,
                         skipMultilineEncoding: doc.skipMultilineEncoding,
-                        references: getAllNestedSecretReferences(doc.secretValue)
+                        references: getAllSecretReferences(doc.secretValue).nestedReferences
                       }
                     };
                   })

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

@@ -85,7 +85,8 @@ export const secretRotationDbFn = async ({
   password,
   username,
   client,
-  variables
+  variables,
+  options
 }: TSecretRotationDbFn) => {
   const appCfg = getConfig();
 
@@ -117,7 +118,8 @@ export const secretRotationDbFn = async ({
       password,
       connectionTimeoutMillis: EXTERNAL_REQUEST_TIMEOUT,
       ssl,
-      pool: { min: 0, max: 1 }
+      pool: { min: 0, max: 1 },
+      options
     }
   });
   const data = await db.raw(query, variables);
@@ -153,6 +155,14 @@ export const getDbSetQuery = (db: TDbProviderClients, variables: { username: str
       variables: [variables.username]
     };
   }
+
+  if (db === TDbProviderClients.MsSqlServer) {
+    return {
+      query: `ALTER LOGIN ?? WITH PASSWORD = '${variables.password}'`,
+      variables: [variables.username]
+    };
+  }
+
   // add more based on client
   return {
     query: `ALTER USER ?? IDENTIFIED BY '${variables.password}'`,

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

@@ -24,4 +24,5 @@ export type TSecretRotationDbFn = {
   query: string;
   variables: unknown[];
   ca?: string;
+  options?: Record<string, unknown>;
 };

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

@@ -94,7 +94,9 @@ export const secretRotationQueueFactory = ({
           // on prod it this will be in days, in development this will be second
           every: appCfg.NODE_ENV === "development" ? secondsToMillis(interval) : daysToMillisecond(interval),
           immediately: true
-        }
+        },
+        removeOnComplete: true,
+        removeOnFail: true
       }
     );
   };
@@ -114,6 +116,7 @@ export const secretRotationQueueFactory = ({
 
   queue.start(QueueName.SecretRotation, async (job) => {
     const { rotationId } = job.data;
+    const appCfg = getConfig();
     logger.info(`secretRotationQueue.process: [rotationDocument=${rotationId}]`);
     const secretRotation = await secretRotationDAL.findById(rotationId);
     const rotationProvider = rotationTemplates.find(({ name }) => name === secretRotation?.provider);
@@ -172,6 +175,15 @@ export const secretRotationQueueFactory = ({
         // set a random value for new password
         newCredential.internal.rotated_password = alphaNumericNanoId(32);
         const { admin_username: username, admin_password: password, host, database, port, ca } = newCredential.inputs;
+
+        const options =
+          provider.template.client === TDbProviderClients.MsSqlServer
+            ? ({
+                encrypt: appCfg.ENABLE_MSSQL_SECRET_ROTATION_ENCRYPT,
+                cryptoCredentialsDetails: ca ? { ca } : {}
+              } as Record<string, unknown>)
+            : undefined;
+
         const dbFunctionArg = {
           username,
           password,
@@ -179,8 +191,10 @@ export const secretRotationQueueFactory = ({
           database,
           port,
           ca: ca as string,
-          client: provider.template.client === TDbProviderClients.MySql ? "mysql2" : provider.template.client
+          client: provider.template.client === TDbProviderClients.MySql ? "mysql2" : provider.template.client,
+          options
         } as TSecretRotationDbFn;
+
         // set function
         await secretRotationDbFn({
           ...dbFunctionArg,
@@ -189,12 +203,17 @@ export const secretRotationQueueFactory = ({
             username: newCredential.internal.username as string
           })
         });
+
         // test function
+        const testQuery =
+          provider.template.client === TDbProviderClients.MsSqlServer ? "SELECT GETDATE()" : "SELECT NOW()";
+
         await secretRotationDbFn({
           ...dbFunctionArg,
-          query: "SELECT NOW()",
+          query: testQuery,
           variables: []
         });
+
         newCredential.outputs.db_username = newCredential.internal.username;
         newCredential.outputs.db_password = newCredential.internal.rotated_password;
         // clean up
@@ -332,7 +351,10 @@ export const secretRotationQueueFactory = ({
           );
         });
       } else {
-        if (!botKey) throw new NotFoundError({ message: "Project bot not found" });
+        if (!botKey)
+          throw new NotFoundError({
+            message: `Project bot not found for project with ID '${secretRotation.projectId}'`
+          });
         const encryptedSecrets = rotationOutputs.map(({ key: outputKey, secretId }) => ({
           secretId,
           value: encryptSymmetric128BitHexKeyUTF8(
@@ -372,7 +394,9 @@ export const secretRotationQueueFactory = ({
           );
           await secretVersionDAL.insertMany(
             updatedSecrets.map(({ id, updatedAt, createdAt, ...el }) => {
-              if (!el.secretBlindIndex) throw new NotFoundError({ message: "Secret blind index not found" });
+              if (!el.secretBlindIndex) {
+                throw new NotFoundError({ message: `Secret blind index not found on secret with ID '${id}` });
+              }
               return {
                 ...el,
                 secretId: id,

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

@@ -1,7 +1,7 @@
 import { ForbiddenError, subject } from "@casl/ability";
 import Ajv from "ajv";
 
-import { ProjectVersion } from "@app/db/schemas";
+import { ProjectVersion, TableName } from "@app/db/schemas";
 import { decryptSymmetric128BitHexKeyUTF8, infisicalSymmetricEncypt } from "@app/lib/crypto/encryption";
 import { BadRequestError, NotFoundError } from "@app/lib/errors";
 import { TProjectPermission } from "@app/lib/types";
@@ -94,28 +94,33 @@ export const secretRotationServiceFactory = ({
     );
 
     const folder = await folderDAL.findBySecretPath(projectId, environment, secretPath);
-    if (!folder) throw new NotFoundError({ message: "Secret path not found" });
+    if (!folder) {
+      throw new NotFoundError({
+        message: `Secret path with path '${secretPath}' not found in environment with slug '${environment}'`
+      });
+    }
     ForbiddenError.from(permission).throwUnlessCan(
       ProjectPermissionActions.Edit,
       subject(ProjectPermissionSub.Secrets, { environment, secretPath })
     );
+
     const project = await projectDAL.findById(projectId);
     const shouldUseBridge = project.version === ProjectVersion.V3;
 
     if (shouldUseBridge) {
       const selectedSecrets = await secretV2BridgeDAL.find({
         folderId: folder.id,
-        $in: { id: Object.values(outputs) }
+        $in: { [`${TableName.SecretV2}.id` as "id"]: Object.values(outputs) }
       });
       if (selectedSecrets.length !== Object.values(outputs).length)
-        throw new NotFoundError({ message: "Secrets not found" });
+        throw new NotFoundError({ message: `Secrets not found in folder with ID '${folder.id}'` });
     } else {
       const selectedSecrets = await secretDAL.find({
         folderId: folder.id,
         $in: { id: Object.values(outputs) }
       });
       if (selectedSecrets.length !== Object.values(outputs).length)
-        throw new NotFoundError({ message: "Secrets not found" });
+        throw new NotFoundError({ message: `Secrets not found in folder with ID '${folder.id}'` });
     }
 
     const plan = await licenseService.getPlan(project.orgId);
@@ -125,7 +130,7 @@ export const secretRotationServiceFactory = ({
       });
 
     const selectedTemplate = rotationTemplates.find(({ name }) => name === provider);
-    if (!selectedTemplate) throw new NotFoundError({ message: "Provider not found" });
+    if (!selectedTemplate) throw new NotFoundError({ message: `Provider with name '${provider}' not found` });
     const formattedInputs: Record<string, unknown> = {};
     Object.entries(inputs).forEach(([key, value]) => {
       const { type } = selectedTemplate.template.inputs.properties[key];
@@ -198,7 +203,7 @@ export const secretRotationServiceFactory = ({
       return docs;
     }
 
-    if (!botKey) throw new NotFoundError({ message: "Project bot not found" });
+    if (!botKey) throw new NotFoundError({ message: `Project bot not found for project with ID '${projectId}'` });
     const docs = await secretRotationDAL.find({ projectId });
     return docs.map((el) => ({
       ...el,
@@ -220,7 +225,7 @@ export const secretRotationServiceFactory = ({
 
   const restartById = async ({ actor, actorId, actorOrgId, actorAuthMethod, rotationId }: TRestartDTO) => {
     const doc = await secretRotationDAL.findById(rotationId);
-    if (!doc) throw new NotFoundError({ message: "Rotation not found" });
+    if (!doc) throw new NotFoundError({ message: `Rotation with ID '${rotationId}' not found` });
 
     const project = await projectDAL.findById(doc.projectId);
     const plan = await licenseService.getPlan(project.orgId);
@@ -244,7 +249,7 @@ export const secretRotationServiceFactory = ({
 
   const deleteById = async ({ actor, actorId, actorOrgId, actorAuthMethod, rotationId }: TDeleteDTO) => {
     const doc = await secretRotationDAL.findById(rotationId);
-    if (!doc) throw new NotFoundError({ message: "Rotation not found" });
+    if (!doc) throw new NotFoundError({ message: `Rotation with ID '${rotationId}' not found` });
 
     const { permission } = await permissionService.getProjectPermission(
       actor,