add better label for identity id

fix(operator): azure auth

.env.example

@@ -67,3 +67,6 @@ CLIENT_SECRET_GITLAB_LOGIN=
 CAPTCHA_SECRET=
 
 NEXT_PUBLIC_CAPTCHA_SITE_KEY=
+
+PLAIN_API_KEY=
+PLAIN_WISH_LABEL_IDS=

.github/workflows/build-binaries.yml

@@ -0,0 +1,99 @@
+name: Build Binaries and Deploy
+
+on:
+    workflow_dispatch:
+        inputs:
+            version:
+                description: "Version number"
+                required: true
+                type: string
+
+defaults:
+    run:
+        working-directory: ./backend
+
+jobs:
+    build-and-deploy:
+        runs-on: ubuntu-20.04
+        strategy:
+            matrix:
+                arch: [x64, arm64]
+                os: [linux, win]
+                include:
+                    - os: linux
+                      target: node20-linux
+                    - os: win
+                      target: node20-win
+
+        steps:
+            - name: Checkout code
+              uses: actions/checkout@v3
+
+            - name: Set up Node.js
+              uses: actions/setup-node@v3
+              with:
+                  node-version: 20
+
+            - name: Install pkg
+              run: npm install -g @yao-pkg/pkg
+
+            - name: Install dependencies (backend)
+              run: npm install
+
+            - name: Install dependencies (frontend)
+              run: npm install --prefix ../frontend
+
+            - name: Prerequisites for pkg
+              run: npm run binary:build
+
+            - name: Package into node binary
+              run: |
+                  if [ "${{ matrix.os }}" != "linux" ]; then
+                    pkg --no-bytecode --public-packages "*" --public --compress Brotli --target ${{ matrix.target }}-${{ matrix.arch }} --output ./binary/infisical-core-${{ matrix.os }}-${{ matrix.arch }} .
+                  else
+                    pkg --no-bytecode --public-packages "*" --public --compress Brotli --target ${{ matrix.target }}-${{ matrix.arch }} --output ./binary/infisical-core .
+                  fi
+
+            # Set up .deb package structure (Debian/Ubuntu only)
+            - name: Set up .deb package structure
+              if: matrix.os == 'linux'
+              run: |
+                  mkdir -p infisical-core/DEBIAN
+                  mkdir -p infisical-core/usr/local/bin
+                  cp ./binary/infisical-core infisical-core/usr/local/bin/
+                  chmod +x infisical-core/usr/local/bin/infisical-core
+
+            - name: Create control file
+              if: matrix.os == 'linux'
+              run: |
+                  cat <<EOF > infisical-core/DEBIAN/control
+                  Package: infisical-core
+                  Version: ${{ github.event.inputs.version }}
+                  Section: base
+                  Priority: optional
+                  Architecture: ${{ matrix.arch == 'x64' && 'amd64' || matrix.arch }}
+                  Maintainer: Infisical <daniel@infisical.com>
+                  Description: Infisical Core standalone executable (app.infisical.com)
+                  EOF
+
+            # Build .deb file (Debian/Ubunutu only)
+            - name: Build .deb package
+              if: matrix.os == 'linux'
+              run: |
+                  dpkg-deb --build infisical-core
+                  mv infisical-core.deb ./binary/infisical-core-${{matrix.arch}}.deb
+
+            - uses: actions/setup-python@v4
+            - run: pip install --upgrade cloudsmith-cli
+
+            # Publish .deb file to Cloudsmith (Debian/Ubuntu only)
+            - name: Publish to Cloudsmith (Debian/Ubuntu)
+              if: matrix.os == 'linux'
+              working-directory: ./backend
+              run: cloudsmith push deb --republish --no-wait-for-sync --api-key=${{ secrets.CLOUDSMITH_API_KEY }} infisical/infisical-core/any-distro/any-version ./binary/infisical-core-${{ matrix.arch }}.deb
+
+            # Publish .exe file to Cloudsmith (Windows only)
+            - name: Publish to Cloudsmith (Windows)
+              if: matrix.os == 'win'
+              working-directory: ./backend
+              run: cloudsmith push raw infisical/infisical-core ./binary/infisical-core-${{ matrix.os }}-${{ matrix.arch }}.exe --republish --no-wait-for-sync --version ${{ github.event.inputs.version }} --api-key ${{ secrets.CLOUDSMITH_API_KEY }}

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

@@ -50,6 +50,13 @@ jobs:
     environment:
       name: Gamma
     steps:
+      - uses: twingate/github-action@v1
+        with:
+          # The Twingate Service Key used to connect Twingate to the proper service
+          # Learn more about [Twingate Services](https://docs.twingate.com/docs/services)
+          #
+          # Required
+          service-key: ${{ secrets.TWINGATE_SERVICE_KEY }}
       - name: Checkout code
         uses: actions/checkout@v2
       - name: Setup Node.js environment
@@ -74,21 +81,21 @@ jobs:
         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
+          aws ecs describe-task-definition --task-definition infisical-core-gamma-stage --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
+          container-name: infisical-core
           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
+          service: infisical-core-gamma-stage
+          cluster: infisical-gamma-stage
           wait-for-service-stability: true
 
   production-postgres-deployment:
@@ -98,6 +105,13 @@ jobs:
     environment:
       name: Production
     steps:
+      - uses: twingate/github-action@v1
+        with:
+        # The Twingate Service Key used to connect Twingate to the proper service
+        # Learn more about [Twingate Services](https://docs.twingate.com/docs/services)
+        #
+        # Required
+          service-key: ${{ secrets.TWINGATE_SERVICE_KEY }}
       - name: Checkout code
         uses: actions/checkout@v2
       - name: Setup Node.js environment

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

@@ -35,7 +35,7 @@ jobs:
             echo "SECRET_SCANNING_GIT_APP_ID=793712" >> .env
             echo "SECRET_SCANNING_PRIVATE_KEY=some-random" >> .env
             echo "SECRET_SCANNING_WEBHOOK_SECRET=some-random" >> .env
-            docker run --name infisical-api -d -p 4000:4000 -e DB_CONNECTION_URI=$DB_CONNECTION_URI -e REDIS_URL=$REDIS_URL -e JWT_AUTH_SECRET=$JWT_AUTH_SECRET --env-file .env --entrypoint '/bin/sh' infisical-api -c "npm run migration:latest && ls && node dist/main.mjs"
+            docker run --name infisical-api -d -p 4000:4000 -e DB_CONNECTION_URI=$DB_CONNECTION_URI -e REDIS_URL=$REDIS_URL -e JWT_AUTH_SECRET=$JWT_AUTH_SECRET -e ENCRYPTION_KEY=$ENCRYPTION_KEY --env-file .env --entrypoint '/bin/sh' infisical-api -c "npm run migration:latest && ls && node dist/main.mjs"
         env:
           REDIS_URL: redis://172.17.0.1:6379
           DB_CONNECTION_URI: postgres://infisical:infisical@172.17.0.1:5432/infisical?sslmode=disable

.github/workflows/check-migration-file-edited.yml

@@ -0,0 +1,25 @@
+name: Check migration file edited
+
+on:
+  pull_request:
+    types: [opened, synchronize]
+    paths:
+      - 'backend/src/db/migrations/**'
+
+jobs:
+  rename:
+    runs-on: ubuntu-latest
+    steps:
+      - name: Check out repository
+        uses: actions/checkout@v4
+        with:
+          fetch-depth: 0
+
+      - name: Check any migration files are modified, renamed or duplicated.
+        run: |
+          git diff --name-status HEAD^ HEAD backend/src/db/migrations | grep '^M\|^R\|^C' || true | cut -f2 | xargs -r -n1 basename > edited_files.txt
+          if [ -s edited_files.txt ]; then
+            echo "Exiting migration files cannot be modified."
+            cat edited_files.txt
+            exit 1
+          fi

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

@@ -1,59 +0,0 @@
-name: Rename Migrations
-
-on:
-  pull_request:
-    types: [closed]
-    paths:
-      - 'backend/src/db/migrations/**'
-
-jobs:
-  rename:
-    runs-on: ubuntu-latest
-    if: github.event.pull_request.merged == true
-
-    steps:
-      - name: Check out repository
-        uses: actions/checkout@v4
-        with:
-          fetch-depth: 0
-
-      - name: Get list of newly added files in migration folder
-        run: |
-          git diff --name-status HEAD^ HEAD backend/src/db/migrations | grep '^A' | cut -f2 | xargs -n1 basename > added_files.txt
-          if [ ! -s added_files.txt ]; then
-            echo "No new files added. Skipping"
-            echo "SKIP_RENAME=true" >> $GITHUB_ENV
-          fi
-
-      - name: Script to rename migrations
-        if: env.SKIP_RENAME != 'true'
-        run: python .github/resources/rename_migration_files.py
-
-      - name: Commit and push changes
-        if: env.SKIP_RENAME != 'true'
-        run: |
-          git config user.name github-actions
-          git config user.email github-actions@github.com
-          git add ./backend/src/db/migrations
-          rm added_files.txt
-          git commit -m "chore: renamed new migration files to latest timestamp (gh-action)"
-
-      - name: Get PR details
-        id: pr_details
-        run: |
-          PR_NUMBER=${{ github.event.pull_request.number }}
-          PR_MERGER=$(curl -s "https://api.github.com/repos/${{ github.repository }}/pulls/$PR_NUMBER" | jq -r '.merged_by.login')
-          
-          echo "PR Number: $PR_NUMBER"
-          echo "PR Merger: $PR_MERGER"
-          echo "pr_merger=$PR_MERGER" >> $GITHUB_OUTPUT
-
-      - name: Create Pull Request
-        if: env.SKIP_RENAME != 'true'
-        uses: peter-evans/create-pull-request@v6
-        with:
-          token: ${{ secrets.GITHUB_TOKEN }}
-          commit-message: 'chore: renamed new migration files to latest UTC (gh-action)'
-          title: 'GH Action: rename new migration file timestamp'
-          branch-suffix: timestamp
-          reviewers: ${{ steps.pr_details.outputs.pr_merger }}

.gitignore

@@ -69,3 +69,4 @@ frontend-build
 *.tgz
 cli/infisical-merge
 cli/test/infisical-merge
+/backend/binary

.infisicalignore

@@ -5,3 +5,4 @@ frontend/src/views/Project/MembersPage/components/MemberListTab/MemberRoleForm/M
 frontend/src/views/Project/MembersPage/components/MemberListTab/MemberRoleForm/SpecificPrivilegeSection.tsx:generic-api-key:292
 docs/self-hosting/configuration/envars.mdx:generic-api-key:106
 frontend/src/views/Project/MembersPage/components/MemberListTab/MemberRoleForm/SpecificPrivilegeSection.tsx:generic-api-key:451
+docs/mint.json:generic-api-key:651

README.md

@@ -48,25 +48,26 @@
 
 ## Introduction
 
-**[Infisical](https://infisical.com)** is the open source secret management platform that teams use to centralize their secrets like API keys, database credentials, and configurations.
+**[Infisical](https://infisical.com)** is the open source secret management platform that teams use to centralize their application configuration and secrets like API keys and database credentials as well as manage their internal PKI.
 
-We're on a mission to make secret management more accessible to everyone, not just security teams, and that means redesigning the entire developer experience from ground up.
+We're on a mission to make security tooling more accessible to everyone, not just security teams, and that means redesigning the entire developer experience from ground up.
 
 ## Features
 
-- **[User-friendly dashboard](https://infisical.com/docs/documentation/platform/project)** to manage secrets across projects and environments (e.g. development, production, etc.). 
-- **[Client SDKs](https://infisical.com/docs/sdks/overview)** to fetch secrets for your apps and infrastructure on demand. 
-- **[Infisical CLI](https://infisical.com/docs/cli/overview)** to fetch and inject secrets into any framework in local development and CI/CD. 
-- **[Infisical API](https://infisical.com/docs/api-reference/overview/introduction)** to perform CRUD operation on secrets, users, projects, and any other resource in Infisical. 
-- **[Native integrations](https://infisical.com/docs/integrations/overview)** with platforms like [GitHub](https://infisical.com/docs/integrations/cicd/githubactions), [Vercel](https://infisical.com/docs/integrations/cloud/vercel), [AWS](https://infisical.com/docs/integrations/cloud/aws-secret-manager), and tools like [Terraform](https://infisical.com/docs/integrations/frameworks/terraform), [Ansible](https://infisical.com/docs/integrations/platforms/ansible), and more. 
+- **[User-friendly dashboard](https://infisical.com/docs/documentation/platform/project)** to manage secrets across projects and environments (e.g. development, production, etc.).
+- **[Client SDKs](https://infisical.com/docs/sdks/overview)** to fetch secrets for your apps and infrastructure on demand.
+- **[Infisical CLI](https://infisical.com/docs/cli/overview)** to fetch and inject secrets into any framework in local development and CI/CD.
+- **[Infisical API](https://infisical.com/docs/api-reference/overview/introduction)** to perform CRUD operation on secrets, users, projects, and any other resource in Infisical.
+- **[Native integrations](https://infisical.com/docs/integrations/overview)** with platforms like [GitHub](https://infisical.com/docs/integrations/cicd/githubactions), [Vercel](https://infisical.com/docs/integrations/cloud/vercel), [AWS](https://infisical.com/docs/integrations/cloud/aws-secret-manager), and tools like [Terraform](https://infisical.com/docs/integrations/frameworks/terraform), [Ansible](https://infisical.com/docs/integrations/platforms/ansible), and more.
 - **[Infisical Kubernetes operator](https://infisical.com/docs/documentation/getting-started/kubernetes)** to managed secrets in k8s, automatically reload deployments, and more.
-- **[Infisical Agent](https://infisical.com/docs/infisical-agent/overview)** to inject secrets into your applications without modifying any code logic. 
+- **[Infisical Agent](https://infisical.com/docs/infisical-agent/overview)** to inject secrets into your applications without modifying any code logic.
 - **[Self-hosting and on-prem](https://infisical.com/docs/self-hosting/overview)** to get complete control over your data.
-- **[Secret versioning](https://infisical.com/docs/documentation/platform/secret-versioning)** and **[Point-in-Time Recovery](https://infisical.com/docs/documentation/platform/pit-recovery)** to version every secret and project state. 
-- **[Audit logs](https://infisical.com/docs/documentation/platform/audit-logs)** to record every action taken in a project. 
-- **[Role-based Access Controls](https://infisical.com/docs/documentation/platform/role-based-access-controls)** to create permission sets on any resource in Infisica and assign those to user or machine identities. 
+- **[Secret versioning](https://infisical.com/docs/documentation/platform/secret-versioning)** and **[Point-in-Time Recovery](https://infisical.com/docs/documentation/platform/pit-recovery)** to version every secret and project state.
+- **[Audit logs](https://infisical.com/docs/documentation/platform/audit-logs)** to record every action taken in a project.
+- **[Role-based Access Controls](https://infisical.com/docs/documentation/platform/role-based-access-controls)** to create permission sets on any resource in Infisica and assign those to user or machine identities.
 - **[Simple on-premise deployments](https://infisical.com/docs/self-hosting/overview)** to AWS, Digital Ocean, and more.
-- **[Secret Scanning and Leak Prevention](https://infisical.com/docs/cli/scanning-overview)** to prevent secrets from leaking to git. 
+- **[Internal PKI](https://infisical.com/docs/documentation/platform/pki/private-ca)** to create Private CA hierarchies and start issuing and managing X.509 digital certificates.
+- **[Secret Scanning and Leak Prevention](https://infisical.com/docs/cli/scanning-overview)** to prevent secrets from leaking to git.
 
 And much more.
 
@@ -74,9 +75,9 @@ And much more.
 
 Check out the [Quickstart Guides](https://infisical.com/docs/getting-started/introduction)
 
-| Use Infisical Cloud                                                                                                                                     | Deploy Infisical on premise                                                                                                                                                                                                                                                                                                                                                                                                                                           |
-| ------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
-| The fastest and most reliable way to <br> get started with Infisical is signing up <br> for free to [Infisical Cloud](https://app.infisical.com/login). |  <br> View all [deployment options](https://infisical.com/docs/self-hosting/overview) |
+| Use Infisical Cloud                                                                                                                                     | Deploy Infisical on premise                                                          |
+| ------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------ |
+| The fastest and most reliable way to <br> get started with Infisical is signing up <br> for free to [Infisical Cloud](https://app.infisical.com/login). | <br> View all [deployment options](https://infisical.com/docs/self-hosting/overview) |
 
 ### Run Infisical locally
 

backend/babel.config.json

@@ -0,0 +1,4 @@
+{
+  "presets": ["@babel/preset-env", "@babel/preset-react"],
+  "plugins": ["@babel/plugin-syntax-import-attributes", "babel-plugin-transform-import-meta"]
+}

backend/e2e-test/vitest-environment-knex.ts

@@ -3,7 +3,6 @@ import "ts-node/register";
 
 import dotenv from "dotenv";
 import jwt from "jsonwebtoken";
-import knex from "knex";
 import path from "path";
 
 import { seedData1 } from "@app/db/seed-data";
@@ -15,6 +14,7 @@ import { AuthMethod, AuthTokenType } from "@app/services/auth/auth-type";
 import { mockQueue } from "./mocks/queue";
 import { mockSmtpServer } from "./mocks/smtp";
 import { mockKeyStore } from "./mocks/keystore";
+import { initDbConnection } from "@app/db";
 
 dotenv.config({ path: path.join(__dirname, "../../.env.test"), debug: true });
 export default {
@@ -23,23 +23,21 @@ export default {
   async setup() {
     const logger = await initLogger();
     const cfg = initEnvConfig(logger);
-    const db = knex({
-      client: "pg",
-      connection: cfg.DB_CONNECTION_URI,
-      migrations: {
-        directory: path.join(__dirname, "../src/db/migrations"),
-        extension: "ts",
-        tableName: "infisical_migrations"
-      },
-      seeds: {
-        directory: path.join(__dirname, "../src/db/seeds"),
-        extension: "ts"
-      }
+    const db = initDbConnection({
+      dbConnectionUri: cfg.DB_CONNECTION_URI,
+      dbRootCert: cfg.DB_ROOT_CERT
     });
 
     try {
-      await db.migrate.latest();
-      await db.seed.run();
+      await db.migrate.latest({
+        directory: path.join(__dirname, "../src/db/migrations"),
+        extension: "ts",
+        tableName: "infisical_migrations"
+      });
+      await db.seed.run({
+        directory: path.join(__dirname, "../src/db/seeds"),
+        extension: "ts"
+      });
       const smtp = mockSmtpServer();
       const queue = mockQueue();
       const keyStore = mockKeyStore();
@@ -74,7 +72,14 @@ export default {
         // @ts-expect-error type
         delete globalThis.jwtToken;
         // called after all tests with this env have been run
-        await db.migrate.rollback({}, true);
+        await db.migrate.rollback(
+          {
+            directory: path.join(__dirname, "../src/db/migrations"),
+            extension: "ts",
+            tableName: "infisical_migrations"
+          },
+          true
+        );
         await db.destroy();
       }
     };

backend/package.json

@@ -3,11 +3,39 @@
   "version": "1.0.0",
   "description": "",
   "main": "./dist/main.mjs",
+  "bin": "dist/main.js",
+  "pkg": {
+    "scripts": [
+      "dist/**/*.js",
+      "../frontend/node_modules/next/**/*.js",
+      "../frontend/.next/*/**/*.js",
+      "../frontend/node_modules/next/dist/server/**/*.js",
+      "../frontend/node_modules/@fortawesome/fontawesome-svg-core/**/*.js"
+    ],
+    "assets": [
+      "dist/**",
+      "!dist/**/*.js",
+      "node_modules/**",
+      "../frontend/node_modules/**",
+      "../frontend/.next/**",
+      "!../frontend/node_modules/next/dist/server/**/*.js",
+      "../frontend/node_modules/@fortawesome/fontawesome-svg-core/**/*",
+      "../frontend/public/**"
+    ],
+    "outputPath": "binary"
+  },
   "scripts": {
+    "binary:build": "npm run binary:clean && npm run build:frontend && npm run build && npm run binary:babel-frontend && npm run binary:babel-backend && npm run binary:rename-imports",
+    "binary:package": "pkg --no-bytecode --public-packages \"*\" --public --target host .",
+    "binary:babel-backend": " babel ./dist -d ./dist",
+    "binary:babel-frontend": "babel --copy-files ../frontend/.next/server -d ../frontend/.next/server",
+    "binary:clean": "rm -rf ./dist && rm -rf ./binary",
+    "binary:rename-imports": "ts-node ./scripts/rename-mjs.ts",
     "test": "echo \"Error: no test specified\" && exit 1",
     "dev": "tsx watch --clear-screen=false ./src/main.ts  | pino-pretty --colorize --colorizeObjects --singleLine",
     "dev:docker": "nodemon",
     "build": "tsup",
+    "build:frontend": "npm run build --prefix ../frontend",
     "start": "node dist/main.mjs",
     "type:check": "tsc --noEmit",
     "lint:fix": "eslint --fix --ext js,ts ./src",
@@ -31,6 +59,11 @@
   "author": "",
   "license": "ISC",
   "devDependencies": {
+    "@babel/cli": "^7.18.10",
+    "@babel/core": "^7.18.10",
+    "@babel/plugin-syntax-import-attributes": "^7.24.7",
+    "@babel/preset-env": "^7.18.10",
+    "@babel/preset-react": "^7.24.7",
     "@types/bcrypt": "^5.0.2",
     "@types/jmespath": "^0.15.2",
     "@types/jsonwebtoken": "^9.0.5",
@@ -48,6 +81,8 @@
     "@types/uuid": "^9.0.7",
     "@typescript-eslint/eslint-plugin": "^6.20.0",
     "@typescript-eslint/parser": "^6.20.0",
+    "@yao-pkg/pkg": "^5.12.0",
+    "babel-plugin-transform-import-meta": "^2.2.1",
     "eslint": "^8.56.0",
     "eslint-config-airbnb-base": "^15.0.0",
     "eslint-config-airbnb-typescript": "^17.1.0",
@@ -60,7 +95,7 @@
     "pino-pretty": "^10.2.3",
     "prompt-sync": "^4.2.0",
     "rimraf": "^5.0.5",
-    "ts-node": "^10.9.1",
+    "ts-node": "^10.9.2",
     "tsc-alias": "^1.8.8",
     "tsconfig-paths": "^4.2.0",
     "tsup": "^8.0.1",
@@ -72,6 +107,7 @@
   "dependencies": {
     "@aws-sdk/client-iam": "^3.525.0",
     "@aws-sdk/client-secrets-manager": "^3.504.0",
+    "@aws-sdk/client-sts": "^3.600.0",
     "@casl/ability": "^6.5.0",
     "@fastify/cookie": "^9.3.1",
     "@fastify/cors": "^8.5.0",
@@ -86,8 +122,11 @@
     "@node-saml/passport-saml": "^4.0.4",
     "@octokit/rest": "^20.0.2",
     "@octokit/webhooks-types": "^7.3.1",
+    "@peculiar/asn1-schema": "^2.3.8",
+    "@peculiar/x509": "^1.10.0",
     "@serdnam/pino-cloudwatch-transport": "^1.0.4",
-    "@sindresorhus/slugify": "^2.2.1",
+    "@team-plain/typescript-sdk": "^4.6.1",
+    "@sindresorhus/slugify": "1.1.0",
     "@ucast/mongo2js": "^1.3.4",
     "ajv": "^8.12.0",
     "argon2": "^0.31.2",
@@ -97,6 +136,7 @@
     "bcrypt": "^5.1.1",
     "bullmq": "^5.4.2",
     "cassandra-driver": "^4.7.2",
+    "connect-redis": "^7.1.1",
     "cron": "^3.1.7",
     "dotenv": "^16.4.1",
     "fastify": "^4.26.0",
@@ -114,8 +154,9 @@
     "lodash.isequal": "^4.5.0",
     "ms": "^2.1.3",
     "mysql2": "^3.9.8",
-    "nanoid": "^5.0.4",
+    "nanoid": "^3.3.4",
     "nodemailer": "^6.9.9",
+    "openid-client": "^5.6.5",
     "ora": "^7.0.1",
     "oracledb": "^6.4.0",
     "passport-github": "^1.1.0",
@@ -129,6 +170,7 @@
     "posthog-node": "^3.6.2",
     "probot": "^13.0.0",
     "smee-client": "^2.0.0",
+    "tedious": "^18.2.1",
     "tweetnacl": "^1.0.3",
     "tweetnacl-util": "^0.15.1",
     "uuid": "^9.0.1",

backend/scripts/create-migration.ts

@@ -2,13 +2,14 @@
 import { execSync } from "child_process";
 import path from "path";
 import promptSync from "prompt-sync";
+import slugify from "@sindresorhus/slugify"
 
 const prompt = promptSync({ sigint: true });
 
 const migrationName = prompt("Enter name for migration: ");
 
 // Remove spaces from migration name and replace with hyphens
-const formattedMigrationName = migrationName.replace(/\s+/g, "-");
+const formattedMigrationName = slugify(migrationName);
 
 execSync(
   `npx knex migrate:make --knexfile ${path.join(__dirname, "../src/db/knexfile.ts")} -x ts ${formattedMigrationName}`,

backend/scripts/rename-mjs.ts

@@ -0,0 +1,27 @@
+/* eslint-disable @typescript-eslint/no-shadow */
+import fs from "node:fs";
+import path from "node:path";
+
+function replaceMjsOccurrences(directory: string) {
+  fs.readdir(directory, (err, files) => {
+    if (err) throw err;
+    files.forEach((file) => {
+      const filePath = path.join(directory, file);
+      if (fs.statSync(filePath).isDirectory()) {
+        replaceMjsOccurrences(filePath);
+      } else {
+        fs.readFile(filePath, "utf8", (err, data) => {
+          if (err) throw err;
+          const result = data.replace(/\.mjs/g, ".js");
+          fs.writeFile(filePath, result, "utf8", (err) => {
+            if (err) throw err;
+            // eslint-disable-next-line no-console
+            console.log(`Updated: ${filePath}`);
+          });
+        });
+      }
+    });
+  });
+}
+
+replaceMjsOccurrences("dist");

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

@@ -6,14 +6,17 @@ import { TAccessApprovalRequestServiceFactory } from "@app/ee/services/access-ap
 import { TAuditLogServiceFactory } from "@app/ee/services/audit-log/audit-log-service";
 import { TCreateAuditLogDTO } from "@app/ee/services/audit-log/audit-log-types";
 import { TAuditLogStreamServiceFactory } from "@app/ee/services/audit-log-stream/audit-log-stream-service";
+import { TCertificateAuthorityCrlServiceFactory } from "@app/ee/services/certificate-authority-crl/certificate-authority-crl-service";
 import { TDynamicSecretServiceFactory } from "@app/ee/services/dynamic-secret/dynamic-secret-service";
 import { TDynamicSecretLeaseServiceFactory } from "@app/ee/services/dynamic-secret-lease/dynamic-secret-lease-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 { 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";
 import { TPermissionServiceFactory } from "@app/ee/services/permission/permission-service";
 import { TProjectUserAdditionalPrivilegeServiceFactory } from "@app/ee/services/project-user-additional-privilege/project-user-additional-privilege-service";
+import { TRateLimitServiceFactory } from "@app/ee/services/rate-limit/rate-limit-service";
 import { TSamlConfigServiceFactory } from "@app/ee/services/saml-config/saml-config-service";
 import { TScimServiceFactory } from "@app/ee/services/scim/scim-service";
 import { TSecretApprovalPolicyServiceFactory } from "@app/ee/services/secret-approval-policy/secret-approval-policy-service";
@@ -29,6 +32,8 @@ import { TAuthPasswordFactory } from "@app/services/auth/auth-password-service";
 import { TAuthSignupFactory } from "@app/services/auth/auth-signup-service";
 import { ActorAuthMethod, ActorType } from "@app/services/auth/auth-type";
 import { TAuthTokenServiceFactory } from "@app/services/auth-token/auth-token-service";
+import { TCertificateServiceFactory } from "@app/services/certificate/certificate-service";
+import { TCertificateAuthorityServiceFactory } from "@app/services/certificate-authority/certificate-authority-service";
 import { TGroupProjectServiceFactory } from "@app/services/group-project/group-project-service";
 import { TIdentityServiceFactory } from "@app/services/identity/identity-service";
 import { TIdentityAccessTokenServiceFactory } from "@app/services/identity-access-token/identity-access-token-service";
@@ -37,6 +42,7 @@ import { TIdentityAzureAuthServiceFactory } from "@app/services/identity-azure-a
 import { TIdentityGcpAuthServiceFactory } from "@app/services/identity-gcp-auth/identity-gcp-auth-service";
 import { TIdentityKubernetesAuthServiceFactory } from "@app/services/identity-kubernetes-auth/identity-kubernetes-auth-service";
 import { TIdentityProjectServiceFactory } from "@app/services/identity-project/identity-project-service";
+import { TIdentityTokenAuthServiceFactory } from "@app/services/identity-token-auth/identity-token-auth-service";
 import { TIdentityUaServiceFactory } from "@app/services/identity-ua/identity-ua-service";
 import { TIntegrationServiceFactory } from "@app/services/integration/integration-service";
 import { TIntegrationAuthServiceFactory } from "@app/services/integration-auth/integration-auth-service";
@@ -48,7 +54,6 @@ import { TProjectEnvServiceFactory } from "@app/services/project-env/project-env
 import { TProjectKeyServiceFactory } from "@app/services/project-key/project-key-service";
 import { TProjectMembershipServiceFactory } from "@app/services/project-membership/project-membership-service";
 import { TProjectRoleServiceFactory } from "@app/services/project-role/project-role-service";
-import { TRateLimitServiceFactory } from "@app/services/rate-limit/rate-limit-service";
 import { TSecretServiceFactory } from "@app/services/secret/secret-service";
 import { TSecretBlindIndexServiceFactory } from "@app/services/secret-blind-index/secret-blind-index-service";
 import { TSecretFolderServiceFactory } from "@app/services/secret-folder/secret-folder-service";
@@ -61,6 +66,7 @@ import { TSuperAdminServiceFactory } from "@app/services/super-admin/super-admin
 import { TTelemetryServiceFactory } from "@app/services/telemetry/telemetry-service";
 import { TUserDALFactory } from "@app/services/user/user-dal";
 import { TUserServiceFactory } from "@app/services/user/user-service";
+import { TUserEngagementServiceFactory } from "@app/services/user-engagement/user-engagement-service";
 import { TWebhookServiceFactory } from "@app/services/webhook/webhook-service";
 
 declare module "fastify" {
@@ -99,6 +105,7 @@ declare module "fastify" {
       permission: TPermissionServiceFactory;
       org: TOrgServiceFactory;
       orgRole: TOrgRoleServiceFactory;
+      oidc: TOidcConfigServiceFactory;
       superAdmin: TSuperAdminServiceFactory;
       user: TUserServiceFactory;
       group: TGroupServiceFactory;
@@ -122,6 +129,7 @@ declare module "fastify" {
       identity: TIdentityServiceFactory;
       identityAccessToken: TIdentityAccessTokenServiceFactory;
       identityProject: TIdentityProjectServiceFactory;
+      identityTokenAuth: TIdentityTokenAuthServiceFactory;
       identityUa: TIdentityUaServiceFactory;
       identityKubernetesAuth: TIdentityKubernetesAuthServiceFactory;
       identityGcpAuth: TIdentityGcpAuthServiceFactory;
@@ -138,6 +146,9 @@ declare module "fastify" {
       ldap: TLdapConfigServiceFactory;
       auditLog: TAuditLogServiceFactory;
       auditLogStream: TAuditLogStreamServiceFactory;
+      certificate: TCertificateServiceFactory;
+      certificateAuthority: TCertificateAuthorityServiceFactory;
+      certificateAuthorityCrl: TCertificateAuthorityCrlServiceFactory;
       secretScanning: TSecretScanningServiceFactory;
       license: TLicenseServiceFactory;
       trustedIp: TTrustedIpServiceFactory;
@@ -149,6 +160,7 @@ declare module "fastify" {
       identityProjectAdditionalPrivilege: TIdentityProjectAdditionalPrivilegeServiceFactory;
       secretSharing: TSecretSharingServiceFactory;
       rateLimit: TRateLimitServiceFactory;
+      userEngagement: TUserEngagementServiceFactory;
     };
     // this is exclusive use for middlewares in which we need to inject data
     // everywhere else access using service layer

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

@@ -1,4 +1,4 @@
-import { Knex } from "knex";
+import { Knex as KnexOriginal } from "knex";
 
 import {
   TableName,
@@ -32,6 +32,27 @@ import {
   TBackupPrivateKey,
   TBackupPrivateKeyInsert,
   TBackupPrivateKeyUpdate,
+  TCertificateAuthorities,
+  TCertificateAuthoritiesInsert,
+  TCertificateAuthoritiesUpdate,
+  TCertificateAuthorityCerts,
+  TCertificateAuthorityCertsInsert,
+  TCertificateAuthorityCertsUpdate,
+  TCertificateAuthorityCrl,
+  TCertificateAuthorityCrlInsert,
+  TCertificateAuthorityCrlUpdate,
+  TCertificateAuthoritySecret,
+  TCertificateAuthoritySecretInsert,
+  TCertificateAuthoritySecretUpdate,
+  TCertificateBodies,
+  TCertificateBodiesInsert,
+  TCertificateBodiesUpdate,
+  TCertificates,
+  TCertificateSecrets,
+  TCertificateSecretsInsert,
+  TCertificateSecretsUpdate,
+  TCertificatesInsert,
+  TCertificatesUpdate,
   TDynamicSecretLeases,
   TDynamicSecretLeasesInsert,
   TDynamicSecretLeasesUpdate,
@@ -83,6 +104,9 @@ import {
   TIdentityProjectMemberships,
   TIdentityProjectMembershipsInsert,
   TIdentityProjectMembershipsUpdate,
+  TIdentityTokenAuths,
+  TIdentityTokenAuthsInsert,
+  TIdentityTokenAuthsUpdate,
   TIdentityUaClientSecrets,
   TIdentityUaClientSecretsInsert,
   TIdentityUaClientSecretsUpdate,
@@ -113,6 +137,9 @@ import {
   TLdapGroupMaps,
   TLdapGroupMapsInsert,
   TLdapGroupMapsUpdate,
+  TOidcConfigs,
+  TOidcConfigsInsert,
+  TOidcConfigsUpdate,
   TOrganizations,
   TOrganizationsInsert,
   TOrganizationsUpdate,
@@ -256,286 +283,376 @@ import {
   TWebhooksUpdate
 } from "@app/db/schemas";
 
+declare module "knex" {
+  namespace Knex {
+    interface QueryInterface {
+      primaryNode(): KnexOriginal;
+      replicaNode(): KnexOriginal;
+    }
+  }
+}
+
 declare module "knex/types/tables" {
   interface Tables {
-    [TableName.Users]: Knex.CompositeTableType<TUsers, TUsersInsert, TUsersUpdate>;
-    [TableName.Groups]: Knex.CompositeTableType<TGroups, TGroupsInsert, TGroupsUpdate>;
-    [TableName.UserGroupMembership]: Knex.CompositeTableType<
+    [TableName.Users]: KnexOriginal.CompositeTableType<TUsers, TUsersInsert, TUsersUpdate>;
+    [TableName.Groups]: KnexOriginal.CompositeTableType<TGroups, TGroupsInsert, TGroupsUpdate>;
+    [TableName.CertificateAuthority]: KnexOriginal.CompositeTableType<
+      TCertificateAuthorities,
+      TCertificateAuthoritiesInsert,
+      TCertificateAuthoritiesUpdate
+    >;
+    [TableName.CertificateAuthorityCert]: KnexOriginal.CompositeTableType<
+      TCertificateAuthorityCerts,
+      TCertificateAuthorityCertsInsert,
+      TCertificateAuthorityCertsUpdate
+    >;
+    [TableName.CertificateAuthoritySecret]: KnexOriginal.CompositeTableType<
+      TCertificateAuthoritySecret,
+      TCertificateAuthoritySecretInsert,
+      TCertificateAuthoritySecretUpdate
+    >;
+    [TableName.CertificateAuthorityCrl]: KnexOriginal.CompositeTableType<
+      TCertificateAuthorityCrl,
+      TCertificateAuthorityCrlInsert,
+      TCertificateAuthorityCrlUpdate
+    >;
+    [TableName.Certificate]: KnexOriginal.CompositeTableType<TCertificates, TCertificatesInsert, TCertificatesUpdate>;
+    [TableName.CertificateBody]: KnexOriginal.CompositeTableType<
+      TCertificateBodies,
+      TCertificateBodiesInsert,
+      TCertificateBodiesUpdate
+    >;
+    [TableName.CertificateSecret]: KnexOriginal.CompositeTableType<
+      TCertificateSecrets,
+      TCertificateSecretsInsert,
+      TCertificateSecretsUpdate
+    >;
+    [TableName.UserGroupMembership]: KnexOriginal.CompositeTableType<
       TUserGroupMembership,
       TUserGroupMembershipInsert,
       TUserGroupMembershipUpdate
     >;
-    [TableName.GroupProjectMembership]: Knex.CompositeTableType<
+    [TableName.GroupProjectMembership]: KnexOriginal.CompositeTableType<
       TGroupProjectMemberships,
       TGroupProjectMembershipsInsert,
       TGroupProjectMembershipsUpdate
     >;
-    [TableName.GroupProjectMembershipRole]: Knex.CompositeTableType<
+    [TableName.GroupProjectMembershipRole]: KnexOriginal.CompositeTableType<
       TGroupProjectMembershipRoles,
       TGroupProjectMembershipRolesInsert,
       TGroupProjectMembershipRolesUpdate
     >;
-    [TableName.UserAliases]: Knex.CompositeTableType<TUserAliases, TUserAliasesInsert, TUserAliasesUpdate>;
-    [TableName.UserEncryptionKey]: Knex.CompositeTableType<
+    [TableName.UserAliases]: KnexOriginal.CompositeTableType<TUserAliases, TUserAliasesInsert, TUserAliasesUpdate>;
+    [TableName.UserEncryptionKey]: KnexOriginal.CompositeTableType<
       TUserEncryptionKeys,
       TUserEncryptionKeysInsert,
       TUserEncryptionKeysUpdate
     >;
-    [TableName.AuthTokens]: Knex.CompositeTableType<TAuthTokens, TAuthTokensInsert, TAuthTokensUpdate>;
-    [TableName.AuthTokenSession]: Knex.CompositeTableType<
+    [TableName.AuthTokens]: KnexOriginal.CompositeTableType<TAuthTokens, TAuthTokensInsert, TAuthTokensUpdate>;
+    [TableName.AuthTokenSession]: KnexOriginal.CompositeTableType<
       TAuthTokenSessions,
       TAuthTokenSessionsInsert,
       TAuthTokenSessionsUpdate
     >;
-    [TableName.BackupPrivateKey]: Knex.CompositeTableType<
+    [TableName.BackupPrivateKey]: KnexOriginal.CompositeTableType<
       TBackupPrivateKey,
       TBackupPrivateKeyInsert,
       TBackupPrivateKeyUpdate
     >;
-    [TableName.Organization]: Knex.CompositeTableType<TOrganizations, TOrganizationsInsert, TOrganizationsUpdate>;
-    [TableName.OrgMembership]: Knex.CompositeTableType<TOrgMemberships, TOrgMembershipsInsert, TOrgMembershipsUpdate>;
-    [TableName.OrgRoles]: Knex.CompositeTableType<TOrgRoles, TOrgRolesInsert, TOrgRolesUpdate>;
-    [TableName.IncidentContact]: Knex.CompositeTableType<
+    [TableName.Organization]: KnexOriginal.CompositeTableType<
+      TOrganizations,
+      TOrganizationsInsert,
+      TOrganizationsUpdate
+    >;
+    [TableName.OrgMembership]: KnexOriginal.CompositeTableType<
+      TOrgMemberships,
+      TOrgMembershipsInsert,
+      TOrgMembershipsUpdate
+    >;
+    [TableName.OrgRoles]: KnexOriginal.CompositeTableType<TOrgRoles, TOrgRolesInsert, TOrgRolesUpdate>;
+    [TableName.IncidentContact]: KnexOriginal.CompositeTableType<
       TIncidentContacts,
       TIncidentContactsInsert,
       TIncidentContactsUpdate
     >;
-    [TableName.UserAction]: Knex.CompositeTableType<TUserActions, TUserActionsInsert, TUserActionsUpdate>;
-    [TableName.SuperAdmin]: Knex.CompositeTableType<TSuperAdmin, TSuperAdminInsert, TSuperAdminUpdate>;
-    [TableName.ApiKey]: Knex.CompositeTableType<TApiKeys, TApiKeysInsert, TApiKeysUpdate>;
-    [TableName.Project]: Knex.CompositeTableType<TProjects, TProjectsInsert, TProjectsUpdate>;
-    [TableName.ProjectMembership]: Knex.CompositeTableType<
+    [TableName.UserAction]: KnexOriginal.CompositeTableType<TUserActions, TUserActionsInsert, TUserActionsUpdate>;
+    [TableName.SuperAdmin]: KnexOriginal.CompositeTableType<TSuperAdmin, TSuperAdminInsert, TSuperAdminUpdate>;
+    [TableName.ApiKey]: KnexOriginal.CompositeTableType<TApiKeys, TApiKeysInsert, TApiKeysUpdate>;
+    [TableName.Project]: KnexOriginal.CompositeTableType<TProjects, TProjectsInsert, TProjectsUpdate>;
+    [TableName.ProjectMembership]: KnexOriginal.CompositeTableType<
       TProjectMemberships,
       TProjectMembershipsInsert,
       TProjectMembershipsUpdate
     >;
-    [TableName.Environment]: Knex.CompositeTableType<
+    [TableName.Environment]: KnexOriginal.CompositeTableType<
       TProjectEnvironments,
       TProjectEnvironmentsInsert,
       TProjectEnvironmentsUpdate
     >;
-    [TableName.ProjectBot]: Knex.CompositeTableType<TProjectBots, TProjectBotsInsert, TProjectBotsUpdate>;
-    [TableName.ProjectUserMembershipRole]: Knex.CompositeTableType<
+    [TableName.ProjectBot]: KnexOriginal.CompositeTableType<TProjectBots, TProjectBotsInsert, TProjectBotsUpdate>;
+    [TableName.ProjectUserMembershipRole]: KnexOriginal.CompositeTableType<
       TProjectUserMembershipRoles,
       TProjectUserMembershipRolesInsert,
       TProjectUserMembershipRolesUpdate
     >;
-    [TableName.ProjectRoles]: Knex.CompositeTableType<TProjectRoles, TProjectRolesInsert, TProjectRolesUpdate>;
-    [TableName.ProjectUserAdditionalPrivilege]: Knex.CompositeTableType<
+    [TableName.ProjectRoles]: KnexOriginal.CompositeTableType<TProjectRoles, TProjectRolesInsert, TProjectRolesUpdate>;
+    [TableName.ProjectUserAdditionalPrivilege]: KnexOriginal.CompositeTableType<
       TProjectUserAdditionalPrivilege,
       TProjectUserAdditionalPrivilegeInsert,
       TProjectUserAdditionalPrivilegeUpdate
     >;
-    [TableName.ProjectKeys]: Knex.CompositeTableType<TProjectKeys, TProjectKeysInsert, TProjectKeysUpdate>;
-    [TableName.Secret]: Knex.CompositeTableType<TSecrets, TSecretsInsert, TSecretsUpdate>;
-    [TableName.SecretReference]: Knex.CompositeTableType<
+    [TableName.ProjectKeys]: KnexOriginal.CompositeTableType<TProjectKeys, TProjectKeysInsert, TProjectKeysUpdate>;
+    [TableName.Secret]: KnexOriginal.CompositeTableType<TSecrets, TSecretsInsert, TSecretsUpdate>;
+    [TableName.SecretReference]: KnexOriginal.CompositeTableType<
       TSecretReferences,
       TSecretReferencesInsert,
       TSecretReferencesUpdate
     >;
-    [TableName.SecretBlindIndex]: Knex.CompositeTableType<
+    [TableName.SecretBlindIndex]: KnexOriginal.CompositeTableType<
       TSecretBlindIndexes,
       TSecretBlindIndexesInsert,
       TSecretBlindIndexesUpdate
     >;
-    [TableName.SecretVersion]: Knex.CompositeTableType<TSecretVersions, TSecretVersionsInsert, TSecretVersionsUpdate>;
-    [TableName.SecretFolder]: Knex.CompositeTableType<TSecretFolders, TSecretFoldersInsert, TSecretFoldersUpdate>;
-    [TableName.SecretFolderVersion]: Knex.CompositeTableType<
+    [TableName.SecretVersion]: KnexOriginal.CompositeTableType<
+      TSecretVersions,
+      TSecretVersionsInsert,
+      TSecretVersionsUpdate
+    >;
+    [TableName.SecretFolder]: KnexOriginal.CompositeTableType<
+      TSecretFolders,
+      TSecretFoldersInsert,
+      TSecretFoldersUpdate
+    >;
+    [TableName.SecretFolderVersion]: KnexOriginal.CompositeTableType<
       TSecretFolderVersions,
       TSecretFolderVersionsInsert,
       TSecretFolderVersionsUpdate
     >;
-    [TableName.SecretSharing]: Knex.CompositeTableType<TSecretSharing, TSecretSharingInsert, TSecretSharingUpdate>;
-    [TableName.RateLimit]: Knex.CompositeTableType<TRateLimit, TRateLimitInsert, TRateLimitUpdate>;
-    [TableName.SecretTag]: Knex.CompositeTableType<TSecretTags, TSecretTagsInsert, TSecretTagsUpdate>;
-    [TableName.SecretImport]: Knex.CompositeTableType<TSecretImports, TSecretImportsInsert, TSecretImportsUpdate>;
-    [TableName.Integration]: Knex.CompositeTableType<TIntegrations, TIntegrationsInsert, TIntegrationsUpdate>;
-    [TableName.Webhook]: Knex.CompositeTableType<TWebhooks, TWebhooksInsert, TWebhooksUpdate>;
-    [TableName.ServiceToken]: Knex.CompositeTableType<TServiceTokens, TServiceTokensInsert, TServiceTokensUpdate>;
-    [TableName.IntegrationAuth]: Knex.CompositeTableType<
+    [TableName.SecretSharing]: KnexOriginal.CompositeTableType<
+      TSecretSharing,
+      TSecretSharingInsert,
+      TSecretSharingUpdate
+    >;
+    [TableName.RateLimit]: KnexOriginal.CompositeTableType<TRateLimit, TRateLimitInsert, TRateLimitUpdate>;
+    [TableName.SecretTag]: KnexOriginal.CompositeTableType<TSecretTags, TSecretTagsInsert, TSecretTagsUpdate>;
+    [TableName.SecretImport]: KnexOriginal.CompositeTableType<
+      TSecretImports,
+      TSecretImportsInsert,
+      TSecretImportsUpdate
+    >;
+    [TableName.Integration]: KnexOriginal.CompositeTableType<TIntegrations, TIntegrationsInsert, TIntegrationsUpdate>;
+    [TableName.Webhook]: KnexOriginal.CompositeTableType<TWebhooks, TWebhooksInsert, TWebhooksUpdate>;
+    [TableName.ServiceToken]: KnexOriginal.CompositeTableType<
+      TServiceTokens,
+      TServiceTokensInsert,
+      TServiceTokensUpdate
+    >;
+    [TableName.IntegrationAuth]: KnexOriginal.CompositeTableType<
       TIntegrationAuths,
       TIntegrationAuthsInsert,
       TIntegrationAuthsUpdate
     >;
-    [TableName.Identity]: Knex.CompositeTableType<TIdentities, TIdentitiesInsert, TIdentitiesUpdate>;
-    [TableName.IdentityUniversalAuth]: Knex.CompositeTableType<
+    [TableName.Identity]: KnexOriginal.CompositeTableType<TIdentities, TIdentitiesInsert, TIdentitiesUpdate>;
+    [TableName.IdentityTokenAuth]: KnexOriginal.CompositeTableType<
+      TIdentityTokenAuths,
+      TIdentityTokenAuthsInsert,
+      TIdentityTokenAuthsUpdate
+    >;
+    [TableName.IdentityUniversalAuth]: KnexOriginal.CompositeTableType<
       TIdentityUniversalAuths,
       TIdentityUniversalAuthsInsert,
       TIdentityUniversalAuthsUpdate
     >;
-    [TableName.IdentityKubernetesAuth]: Knex.CompositeTableType<
+    [TableName.IdentityKubernetesAuth]: KnexOriginal.CompositeTableType<
       TIdentityKubernetesAuths,
       TIdentityKubernetesAuthsInsert,
       TIdentityKubernetesAuthsUpdate
     >;
-    [TableName.IdentityGcpAuth]: Knex.CompositeTableType<
+    [TableName.IdentityGcpAuth]: KnexOriginal.CompositeTableType<
       TIdentityGcpAuths,
       TIdentityGcpAuthsInsert,
       TIdentityGcpAuthsUpdate
     >;
-    [TableName.IdentityAwsAuth]: Knex.CompositeTableType<
+    [TableName.IdentityAwsAuth]: KnexOriginal.CompositeTableType<
       TIdentityAwsAuths,
       TIdentityAwsAuthsInsert,
       TIdentityAwsAuthsUpdate
     >;
-    [TableName.IdentityAzureAuth]: Knex.CompositeTableType<
+    [TableName.IdentityAzureAuth]: KnexOriginal.CompositeTableType<
       TIdentityAzureAuths,
       TIdentityAzureAuthsInsert,
       TIdentityAzureAuthsUpdate
     >;
-    [TableName.IdentityUaClientSecret]: Knex.CompositeTableType<
+    [TableName.IdentityUaClientSecret]: KnexOriginal.CompositeTableType<
       TIdentityUaClientSecrets,
       TIdentityUaClientSecretsInsert,
       TIdentityUaClientSecretsUpdate
     >;
-    [TableName.IdentityAccessToken]: Knex.CompositeTableType<
+    [TableName.IdentityAccessToken]: KnexOriginal.CompositeTableType<
       TIdentityAccessTokens,
       TIdentityAccessTokensInsert,
       TIdentityAccessTokensUpdate
     >;
-    [TableName.IdentityOrgMembership]: Knex.CompositeTableType<
+    [TableName.IdentityOrgMembership]: KnexOriginal.CompositeTableType<
       TIdentityOrgMemberships,
       TIdentityOrgMembershipsInsert,
       TIdentityOrgMembershipsUpdate
     >;
-    [TableName.IdentityProjectMembership]: Knex.CompositeTableType<
+    [TableName.IdentityProjectMembership]: KnexOriginal.CompositeTableType<
       TIdentityProjectMemberships,
       TIdentityProjectMembershipsInsert,
       TIdentityProjectMembershipsUpdate
     >;
-    [TableName.IdentityProjectMembershipRole]: Knex.CompositeTableType<
+    [TableName.IdentityProjectMembershipRole]: KnexOriginal.CompositeTableType<
       TIdentityProjectMembershipRole,
       TIdentityProjectMembershipRoleInsert,
       TIdentityProjectMembershipRoleUpdate
     >;
-    [TableName.IdentityProjectAdditionalPrivilege]: Knex.CompositeTableType<
+    [TableName.IdentityProjectAdditionalPrivilege]: KnexOriginal.CompositeTableType<
       TIdentityProjectAdditionalPrivilege,
       TIdentityProjectAdditionalPrivilegeInsert,
       TIdentityProjectAdditionalPrivilegeUpdate
     >;
 
-    [TableName.AccessApprovalPolicy]: Knex.CompositeTableType<
+    [TableName.AccessApprovalPolicy]: KnexOriginal.CompositeTableType<
       TAccessApprovalPolicies,
       TAccessApprovalPoliciesInsert,
       TAccessApprovalPoliciesUpdate
     >;
 
-    [TableName.AccessApprovalPolicyApprover]: Knex.CompositeTableType<
+    [TableName.AccessApprovalPolicyApprover]: KnexOriginal.CompositeTableType<
       TAccessApprovalPoliciesApprovers,
       TAccessApprovalPoliciesApproversInsert,
       TAccessApprovalPoliciesApproversUpdate
     >;
 
-    [TableName.AccessApprovalRequest]: Knex.CompositeTableType<
+    [TableName.AccessApprovalRequest]: KnexOriginal.CompositeTableType<
       TAccessApprovalRequests,
       TAccessApprovalRequestsInsert,
       TAccessApprovalRequestsUpdate
     >;
 
-    [TableName.AccessApprovalRequestReviewer]: Knex.CompositeTableType<
+    [TableName.AccessApprovalRequestReviewer]: KnexOriginal.CompositeTableType<
       TAccessApprovalRequestsReviewers,
       TAccessApprovalRequestsReviewersInsert,
       TAccessApprovalRequestsReviewersUpdate
     >;
 
-    [TableName.ScimToken]: Knex.CompositeTableType<TScimTokens, TScimTokensInsert, TScimTokensUpdate>;
-    [TableName.SecretApprovalPolicy]: Knex.CompositeTableType<
+    [TableName.ScimToken]: KnexOriginal.CompositeTableType<TScimTokens, TScimTokensInsert, TScimTokensUpdate>;
+    [TableName.SecretApprovalPolicy]: KnexOriginal.CompositeTableType<
       TSecretApprovalPolicies,
       TSecretApprovalPoliciesInsert,
       TSecretApprovalPoliciesUpdate
     >;
-    [TableName.SecretApprovalPolicyApprover]: Knex.CompositeTableType<
+    [TableName.SecretApprovalPolicyApprover]: KnexOriginal.CompositeTableType<
       TSecretApprovalPoliciesApprovers,
       TSecretApprovalPoliciesApproversInsert,
       TSecretApprovalPoliciesApproversUpdate
     >;
-    [TableName.SecretApprovalRequest]: Knex.CompositeTableType<
+    [TableName.SecretApprovalRequest]: KnexOriginal.CompositeTableType<
       TSecretApprovalRequests,
       TSecretApprovalRequestsInsert,
       TSecretApprovalRequestsUpdate
     >;
-    [TableName.SecretApprovalRequestReviewer]: Knex.CompositeTableType<
+    [TableName.SecretApprovalRequestReviewer]: KnexOriginal.CompositeTableType<
       TSecretApprovalRequestsReviewers,
       TSecretApprovalRequestsReviewersInsert,
       TSecretApprovalRequestsReviewersUpdate
     >;
-    [TableName.SecretApprovalRequestSecret]: Knex.CompositeTableType<
+    [TableName.SecretApprovalRequestSecret]: KnexOriginal.CompositeTableType<
       TSecretApprovalRequestsSecrets,
       TSecretApprovalRequestsSecretsInsert,
       TSecretApprovalRequestsSecretsUpdate
     >;
-    [TableName.SecretApprovalRequestSecretTag]: Knex.CompositeTableType<
+    [TableName.SecretApprovalRequestSecretTag]: KnexOriginal.CompositeTableType<
       TSecretApprovalRequestSecretTags,
       TSecretApprovalRequestSecretTagsInsert,
       TSecretApprovalRequestSecretTagsUpdate
     >;
-    [TableName.SecretRotation]: Knex.CompositeTableType<
+    [TableName.SecretRotation]: KnexOriginal.CompositeTableType<
       TSecretRotations,
       TSecretRotationsInsert,
       TSecretRotationsUpdate
     >;
-    [TableName.SecretRotationOutput]: Knex.CompositeTableType<
+    [TableName.SecretRotationOutput]: KnexOriginal.CompositeTableType<
       TSecretRotationOutputs,
       TSecretRotationOutputsInsert,
       TSecretRotationOutputsUpdate
     >;
-    [TableName.Snapshot]: Knex.CompositeTableType<TSecretSnapshots, TSecretSnapshotsInsert, TSecretSnapshotsUpdate>;
-    [TableName.SnapshotSecret]: Knex.CompositeTableType<
+    [TableName.Snapshot]: KnexOriginal.CompositeTableType<
+      TSecretSnapshots,
+      TSecretSnapshotsInsert,
+      TSecretSnapshotsUpdate
+    >;
+    [TableName.SnapshotSecret]: KnexOriginal.CompositeTableType<
       TSecretSnapshotSecrets,
       TSecretSnapshotSecretsInsert,
       TSecretSnapshotSecretsUpdate
     >;
-    [TableName.SnapshotFolder]: Knex.CompositeTableType<
+    [TableName.SnapshotFolder]: KnexOriginal.CompositeTableType<
       TSecretSnapshotFolders,
       TSecretSnapshotFoldersInsert,
       TSecretSnapshotFoldersUpdate
     >;
-    [TableName.DynamicSecret]: Knex.CompositeTableType<TDynamicSecrets, TDynamicSecretsInsert, TDynamicSecretsUpdate>;
-    [TableName.DynamicSecretLease]: Knex.CompositeTableType<
+    [TableName.DynamicSecret]: KnexOriginal.CompositeTableType<
+      TDynamicSecrets,
+      TDynamicSecretsInsert,
+      TDynamicSecretsUpdate
+    >;
+    [TableName.DynamicSecretLease]: KnexOriginal.CompositeTableType<
       TDynamicSecretLeases,
       TDynamicSecretLeasesInsert,
       TDynamicSecretLeasesUpdate
     >;
-    [TableName.SamlConfig]: Knex.CompositeTableType<TSamlConfigs, TSamlConfigsInsert, TSamlConfigsUpdate>;
-    [TableName.LdapConfig]: Knex.CompositeTableType<TLdapConfigs, TLdapConfigsInsert, TLdapConfigsUpdate>;
-    [TableName.LdapGroupMap]: Knex.CompositeTableType<TLdapGroupMaps, TLdapGroupMapsInsert, TLdapGroupMapsUpdate>;
-    [TableName.OrgBot]: Knex.CompositeTableType<TOrgBots, TOrgBotsInsert, TOrgBotsUpdate>;
-    [TableName.AuditLog]: Knex.CompositeTableType<TAuditLogs, TAuditLogsInsert, TAuditLogsUpdate>;
-    [TableName.AuditLogStream]: Knex.CompositeTableType<
+    [TableName.SamlConfig]: KnexOriginal.CompositeTableType<TSamlConfigs, TSamlConfigsInsert, TSamlConfigsUpdate>;
+    [TableName.OidcConfig]: KnexOriginal.CompositeTableType<TOidcConfigs, TOidcConfigsInsert, TOidcConfigsUpdate>;
+    [TableName.LdapConfig]: KnexOriginal.CompositeTableType<TLdapConfigs, TLdapConfigsInsert, TLdapConfigsUpdate>;
+    [TableName.LdapGroupMap]: KnexOriginal.CompositeTableType<
+      TLdapGroupMaps,
+      TLdapGroupMapsInsert,
+      TLdapGroupMapsUpdate
+    >;
+    [TableName.OrgBot]: KnexOriginal.CompositeTableType<TOrgBots, TOrgBotsInsert, TOrgBotsUpdate>;
+    [TableName.AuditLog]: KnexOriginal.CompositeTableType<TAuditLogs, TAuditLogsInsert, TAuditLogsUpdate>;
+    [TableName.AuditLogStream]: KnexOriginal.CompositeTableType<
       TAuditLogStreams,
       TAuditLogStreamsInsert,
       TAuditLogStreamsUpdate
     >;
-    [TableName.GitAppInstallSession]: Knex.CompositeTableType<
+    [TableName.GitAppInstallSession]: KnexOriginal.CompositeTableType<
       TGitAppInstallSessions,
       TGitAppInstallSessionsInsert,
       TGitAppInstallSessionsUpdate
     >;
-    [TableName.GitAppOrg]: Knex.CompositeTableType<TGitAppOrg, TGitAppOrgInsert, TGitAppOrgUpdate>;
-    [TableName.SecretScanningGitRisk]: Knex.CompositeTableType<
+    [TableName.GitAppOrg]: KnexOriginal.CompositeTableType<TGitAppOrg, TGitAppOrgInsert, TGitAppOrgUpdate>;
+    [TableName.SecretScanningGitRisk]: KnexOriginal.CompositeTableType<
       TSecretScanningGitRisks,
       TSecretScanningGitRisksInsert,
       TSecretScanningGitRisksUpdate
     >;
-    [TableName.TrustedIps]: Knex.CompositeTableType<TTrustedIps, TTrustedIpsInsert, TTrustedIpsUpdate>;
+    [TableName.TrustedIps]: KnexOriginal.CompositeTableType<TTrustedIps, TTrustedIpsInsert, TTrustedIpsUpdate>;
     // Junction tables
-    [TableName.JnSecretTag]: Knex.CompositeTableType<
+    [TableName.JnSecretTag]: KnexOriginal.CompositeTableType<
       TSecretTagJunction,
       TSecretTagJunctionInsert,
       TSecretTagJunctionUpdate
     >;
-    [TableName.SecretVersionTag]: Knex.CompositeTableType<
+    [TableName.SecretVersionTag]: KnexOriginal.CompositeTableType<
       TSecretVersionTagJunction,
       TSecretVersionTagJunctionInsert,
       TSecretVersionTagJunctionUpdate
     >;
     // KMS service
-    [TableName.KmsServerRootConfig]: Knex.CompositeTableType<
+    [TableName.KmsServerRootConfig]: KnexOriginal.CompositeTableType<
       TKmsRootConfig,
       TKmsRootConfigInsert,
       TKmsRootConfigUpdate
     >;
-    [TableName.KmsKey]: Knex.CompositeTableType<TKmsKeys, TKmsKeysInsert, TKmsKeysUpdate>;
-    [TableName.KmsKeyVersion]: Knex.CompositeTableType<TKmsKeyVersions, TKmsKeyVersionsInsert, TKmsKeyVersionsUpdate>;
+    [TableName.KmsKey]: KnexOriginal.CompositeTableType<TKmsKeys, TKmsKeysInsert, TKmsKeysUpdate>;
+    [TableName.KmsKeyVersion]: KnexOriginal.CompositeTableType<
+      TKmsKeyVersions,
+      TKmsKeyVersionsInsert,
+      TKmsKeyVersionsUpdate
+    >;
   }
 }

backend/src/db/instance.ts

@@ -1,8 +1,38 @@
-import knex from "knex";
+import knex, { Knex } from "knex";
 
 export type TDbClient = ReturnType<typeof initDbConnection>;
-export const initDbConnection = ({ dbConnectionUri, dbRootCert }: { dbConnectionUri: string; dbRootCert?: string }) => {
-  const db = knex({
+export const initDbConnection = ({
+  dbConnectionUri,
+  dbRootCert,
+  readReplicas = []
+}: {
+  dbConnectionUri: string;
+  dbRootCert?: string;
+  readReplicas?: {
+    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
+  let db: Knex<any, unknown[]>;
+  // eslint-disable-next-line
+  let readReplicaDbs: Knex<any, unknown[]>[];
+  // @ts-expect-error the querybuilder type is expected but our intension is to return  a knex instance
+  knex.QueryBuilder.extend("primaryNode", () => {
+    return db;
+  });
+
+  // @ts-expect-error the querybuilder type is expected but our intension is to return  a knex instance
+  knex.QueryBuilder.extend("replicaNode", () => {
+    if (!readReplicaDbs.length) return db;
+
+    const selectedReplica = readReplicaDbs[Math.floor(Math.random() * readReplicaDbs.length)];
+    return selectedReplica;
+  });
+
+  db = knex({
     client: "pg",
     connection: {
       connectionString: dbConnectionUri,
@@ -22,5 +52,21 @@ export const initDbConnection = ({ dbConnectionUri, dbRootCert }: { dbConnection
     }
   });
 
+  readReplicaDbs = readReplicas.map((el) => {
+    const replicaDbCertificate = el.dbRootCert || dbRootCert;
+    return knex({
+      client: "pg",
+      connection: {
+        connectionString: el.dbConnectionUri,
+        ssl: replicaDbCertificate
+          ? {
+              rejectUnauthorized: true,
+              ca: Buffer.from(replicaDbCertificate, "base64").toString("ascii")
+            }
+          : false
+      }
+    });
+  });
+
   return db;
 };

backend/src/db/migrations/20240609133400_private-key-handoff.ts

@@ -0,0 +1,61 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  const doesPasswordFieldExist = await knex.schema.hasColumn(TableName.UserEncryptionKey, "hashedPassword");
+  const doesPrivateKeyFieldExist = await knex.schema.hasColumn(
+    TableName.UserEncryptionKey,
+    "serverEncryptedPrivateKey"
+  );
+  const doesPrivateKeyIVFieldExist = await knex.schema.hasColumn(
+    TableName.UserEncryptionKey,
+    "serverEncryptedPrivateKeyIV"
+  );
+  const doesPrivateKeyTagFieldExist = await knex.schema.hasColumn(
+    TableName.UserEncryptionKey,
+    "serverEncryptedPrivateKeyTag"
+  );
+  const doesPrivateKeyEncodingFieldExist = await knex.schema.hasColumn(
+    TableName.UserEncryptionKey,
+    "serverEncryptedPrivateKeyEncoding"
+  );
+  if (await knex.schema.hasTable(TableName.UserEncryptionKey)) {
+    await knex.schema.alterTable(TableName.UserEncryptionKey, (t) => {
+      if (!doesPasswordFieldExist) t.string("hashedPassword");
+      if (!doesPrivateKeyFieldExist) t.text("serverEncryptedPrivateKey");
+      if (!doesPrivateKeyIVFieldExist) t.text("serverEncryptedPrivateKeyIV");
+      if (!doesPrivateKeyTagFieldExist) t.text("serverEncryptedPrivateKeyTag");
+      if (!doesPrivateKeyEncodingFieldExist) t.text("serverEncryptedPrivateKeyEncoding");
+    });
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  const doesPasswordFieldExist = await knex.schema.hasColumn(TableName.UserEncryptionKey, "hashedPassword");
+  const doesPrivateKeyFieldExist = await knex.schema.hasColumn(
+    TableName.UserEncryptionKey,
+    "serverEncryptedPrivateKey"
+  );
+  const doesPrivateKeyIVFieldExist = await knex.schema.hasColumn(
+    TableName.UserEncryptionKey,
+    "serverEncryptedPrivateKeyIV"
+  );
+  const doesPrivateKeyTagFieldExist = await knex.schema.hasColumn(
+    TableName.UserEncryptionKey,
+    "serverEncryptedPrivateKeyTag"
+  );
+  const doesPrivateKeyEncodingFieldExist = await knex.schema.hasColumn(
+    TableName.UserEncryptionKey,
+    "serverEncryptedPrivateKeyEncoding"
+  );
+  if (await knex.schema.hasTable(TableName.UserEncryptionKey)) {
+    await knex.schema.alterTable(TableName.UserEncryptionKey, (t) => {
+      if (doesPasswordFieldExist) t.dropColumn("hashedPassword");
+      if (doesPrivateKeyFieldExist) t.dropColumn("serverEncryptedPrivateKey");
+      if (doesPrivateKeyIVFieldExist) t.dropColumn("serverEncryptedPrivateKeyIV");
+      if (doesPrivateKeyTagFieldExist) t.dropColumn("serverEncryptedPrivateKeyTag");
+      if (doesPrivateKeyEncodingFieldExist) t.dropColumn("serverEncryptedPrivateKeyEncoding");
+    });
+  }
+}

backend/src/db/migrations/20240614115952_tag-machine-identity.ts

@@ -0,0 +1,25 @@
+import { Knex } from "knex";
+
+import { ActorType } from "@app/services/auth/auth-type";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  const hasCreatedByActorType = await knex.schema.hasColumn(TableName.SecretTag, "createdByActorType");
+  await knex.schema.alterTable(TableName.SecretTag, (tb) => {
+    if (!hasCreatedByActorType) {
+      tb.string("createdByActorType").notNullable().defaultTo(ActorType.USER);
+      tb.dropForeign("createdBy");
+    }
+  });
+}
+
+export async function down(knex: Knex): Promise<void> {
+  const hasCreatedByActorType = await knex.schema.hasColumn(TableName.SecretTag, "createdByActorType");
+  await knex.schema.alterTable(TableName.SecretTag, (tb) => {
+    if (hasCreatedByActorType) {
+      tb.dropColumn("createdByActorType");
+      tb.foreign("createdBy").references("id").inTable(TableName.Users).onDelete("SET NULL");
+    }
+  });
+}

backend/src/db/migrations/20240614154212_certificate-mgmt.ts

@@ -0,0 +1,137 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+import { createOnUpdateTrigger, dropOnUpdateTrigger } from "../utils";
+
+export async function up(knex: Knex): Promise<void> {
+  if (await knex.schema.hasTable(TableName.Project)) {
+    const doesProjectCertificateKeyIdExist = await knex.schema.hasColumn(TableName.Project, "kmsCertificateKeyId");
+    await knex.schema.alterTable(TableName.Project, (t) => {
+      if (!doesProjectCertificateKeyIdExist) {
+        t.uuid("kmsCertificateKeyId").nullable();
+        t.foreign("kmsCertificateKeyId").references("id").inTable(TableName.KmsKey);
+      }
+    });
+  }
+
+  if (!(await knex.schema.hasTable(TableName.CertificateAuthority))) {
+    await knex.schema.createTable(TableName.CertificateAuthority, (t) => {
+      t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+      t.timestamps(true, true, true);
+      t.uuid("parentCaId").nullable();
+      t.foreign("parentCaId").references("id").inTable(TableName.CertificateAuthority).onDelete("CASCADE");
+      t.string("projectId").notNullable();
+      t.foreign("projectId").references("id").inTable(TableName.Project).onDelete("CASCADE");
+      t.string("type").notNullable(); // root / intermediate
+      t.string("status").notNullable(); // active / pending-certificate
+      t.string("friendlyName").notNullable();
+      t.string("organization").notNullable();
+      t.string("ou").notNullable();
+      t.string("country").notNullable();
+      t.string("province").notNullable();
+      t.string("locality").notNullable();
+      t.string("commonName").notNullable();
+      t.string("dn").notNullable();
+      t.string("serialNumber").nullable().unique();
+      t.integer("maxPathLength").nullable();
+      t.string("keyAlgorithm").notNullable();
+      t.datetime("notBefore").nullable();
+      t.datetime("notAfter").nullable();
+    });
+  }
+
+  if (!(await knex.schema.hasTable(TableName.CertificateAuthorityCert))) {
+    // table to keep track of certificates belonging to CA
+    await knex.schema.createTable(TableName.CertificateAuthorityCert, (t) => {
+      t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+      t.timestamps(true, true, true);
+      t.uuid("caId").notNullable().unique();
+      t.foreign("caId").references("id").inTable(TableName.CertificateAuthority).onDelete("CASCADE");
+      t.binary("encryptedCertificate").notNullable();
+      t.binary("encryptedCertificateChain").notNullable();
+    });
+  }
+
+  if (!(await knex.schema.hasTable(TableName.CertificateAuthoritySecret))) {
+    await knex.schema.createTable(TableName.CertificateAuthoritySecret, (t) => {
+      t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+      t.timestamps(true, true, true);
+      t.uuid("caId").notNullable().unique();
+      t.foreign("caId").references("id").inTable(TableName.CertificateAuthority).onDelete("CASCADE");
+      t.binary("encryptedPrivateKey").notNullable();
+    });
+  }
+
+  if (!(await knex.schema.hasTable(TableName.CertificateAuthorityCrl))) {
+    await knex.schema.createTable(TableName.CertificateAuthorityCrl, (t) => {
+      t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+      t.timestamps(true, true, true);
+      t.uuid("caId").notNullable().unique();
+      t.foreign("caId").references("id").inTable(TableName.CertificateAuthority).onDelete("CASCADE");
+      t.binary("encryptedCrl").notNullable();
+    });
+  }
+
+  if (!(await knex.schema.hasTable(TableName.Certificate))) {
+    await knex.schema.createTable(TableName.Certificate, (t) => {
+      t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+      t.timestamps(true, true, true);
+      t.uuid("caId").notNullable();
+      t.foreign("caId").references("id").inTable(TableName.CertificateAuthority).onDelete("CASCADE");
+      t.string("status").notNullable(); // active / pending-certificate
+      t.string("serialNumber").notNullable().unique();
+      t.string("friendlyName").notNullable();
+      t.string("commonName").notNullable();
+      t.datetime("notBefore").notNullable();
+      t.datetime("notAfter").notNullable();
+      t.datetime("revokedAt").nullable();
+      t.integer("revocationReason").nullable(); // integer based on crl reason in RFC 5280
+    });
+  }
+
+  if (!(await knex.schema.hasTable(TableName.CertificateBody))) {
+    await knex.schema.createTable(TableName.CertificateBody, (t) => {
+      t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+      t.timestamps(true, true, true);
+      t.uuid("certId").notNullable().unique();
+      t.foreign("certId").references("id").inTable(TableName.Certificate).onDelete("CASCADE");
+      t.binary("encryptedCertificate").notNullable();
+    });
+  }
+
+  await createOnUpdateTrigger(knex, TableName.CertificateAuthority);
+  await createOnUpdateTrigger(knex, TableName.CertificateAuthorityCert);
+  await createOnUpdateTrigger(knex, TableName.CertificateAuthoritySecret);
+  await createOnUpdateTrigger(knex, TableName.Certificate);
+  await createOnUpdateTrigger(knex, TableName.CertificateBody);
+}
+
+export async function down(knex: Knex): Promise<void> {
+  // project
+  if (await knex.schema.hasTable(TableName.Project)) {
+    const doesProjectCertificateKeyIdExist = await knex.schema.hasColumn(TableName.Project, "kmsCertificateKeyId");
+    await knex.schema.alterTable(TableName.Project, (t) => {
+      if (doesProjectCertificateKeyIdExist) t.dropColumn("kmsCertificateKeyId");
+    });
+  }
+
+  // certificates
+  await knex.schema.dropTableIfExists(TableName.CertificateBody);
+  await dropOnUpdateTrigger(knex, TableName.CertificateBody);
+
+  await knex.schema.dropTableIfExists(TableName.Certificate);
+  await dropOnUpdateTrigger(knex, TableName.Certificate);
+
+  // certificate authorities
+  await knex.schema.dropTableIfExists(TableName.CertificateAuthoritySecret);
+  await dropOnUpdateTrigger(knex, TableName.CertificateAuthoritySecret);
+
+  await knex.schema.dropTableIfExists(TableName.CertificateAuthorityCrl);
+  await dropOnUpdateTrigger(knex, TableName.CertificateAuthorityCrl);
+
+  await knex.schema.dropTableIfExists(TableName.CertificateAuthorityCert);
+  await dropOnUpdateTrigger(knex, TableName.CertificateAuthorityCert);
+
+  await knex.schema.dropTableIfExists(TableName.CertificateAuthority);
+  await dropOnUpdateTrigger(knex, TableName.CertificateAuthority);
+}

backend/src/db/migrations/20240614184133_make-secret-sharing-public.ts

@@ -0,0 +1,27 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  const hasOrgIdColumn = await knex.schema.hasColumn(TableName.SecretSharing, "orgId");
+  const hasUserIdColumn = await knex.schema.hasColumn(TableName.SecretSharing, "userId");
+
+  if (await knex.schema.hasTable(TableName.SecretSharing)) {
+    await knex.schema.alterTable(TableName.SecretSharing, (t) => {
+      if (hasOrgIdColumn) t.uuid("orgId").nullable().alter();
+      if (hasUserIdColumn) t.uuid("userId").nullable().alter();
+    });
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  const hasOrgIdColumn = await knex.schema.hasColumn(TableName.SecretSharing, "orgId");
+  const hasUserIdColumn = await knex.schema.hasColumn(TableName.SecretSharing, "userId");
+
+  if (await knex.schema.hasTable(TableName.SecretSharing)) {
+    await knex.schema.alterTable(TableName.SecretSharing, (t) => {
+      if (hasOrgIdColumn) t.uuid("orgId").notNullable().alter();
+      if (hasUserIdColumn) t.uuid("userId").notNullable().alter();
+    });
+  }
+}

backend/src/db/migrations/20240624161942_add-oidc-auth.ts

@@ -0,0 +1,49 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  if (!(await knex.schema.hasTable(TableName.OidcConfig))) {
+    await knex.schema.createTable(TableName.OidcConfig, (tb) => {
+      tb.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+      tb.string("discoveryURL");
+      tb.string("issuer");
+      tb.string("authorizationEndpoint");
+      tb.string("jwksUri");
+      tb.string("tokenEndpoint");
+      tb.string("userinfoEndpoint");
+      tb.text("encryptedClientId").notNullable();
+      tb.string("configurationType").notNullable();
+      tb.string("clientIdIV").notNullable();
+      tb.string("clientIdTag").notNullable();
+      tb.text("encryptedClientSecret").notNullable();
+      tb.string("clientSecretIV").notNullable();
+      tb.string("clientSecretTag").notNullable();
+      tb.string("allowedEmailDomains").nullable();
+      tb.boolean("isActive").notNullable();
+      tb.timestamps(true, true, true);
+      tb.uuid("orgId").notNullable().unique();
+      tb.foreign("orgId").references("id").inTable(TableName.Organization);
+    });
+  }
+
+  if (await knex.schema.hasTable(TableName.SuperAdmin)) {
+    if (!(await knex.schema.hasColumn(TableName.SuperAdmin, "trustOidcEmails"))) {
+      await knex.schema.alterTable(TableName.SuperAdmin, (tb) => {
+        tb.boolean("trustOidcEmails").defaultTo(false);
+      });
+    }
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  await knex.schema.dropTableIfExists(TableName.OidcConfig);
+
+  if (await knex.schema.hasTable(TableName.SuperAdmin)) {
+    if (await knex.schema.hasColumn(TableName.SuperAdmin, "trustOidcEmails")) {
+      await knex.schema.alterTable(TableName.SuperAdmin, (t) => {
+        t.dropColumn("trustOidcEmails");
+      });
+    }
+  }
+}

backend/src/db/migrations/20240624172027_default-saml-ldap-org.ts

@@ -0,0 +1,27 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+const DEFAULT_AUTH_ORG_ID_FIELD = "defaultAuthOrgId";
+
+export async function up(knex: Knex): Promise<void> {
+  const hasDefaultOrgColumn = await knex.schema.hasColumn(TableName.SuperAdmin, DEFAULT_AUTH_ORG_ID_FIELD);
+
+  await knex.schema.alterTable(TableName.SuperAdmin, (t) => {
+    if (!hasDefaultOrgColumn) {
+      t.uuid(DEFAULT_AUTH_ORG_ID_FIELD).nullable();
+      t.foreign(DEFAULT_AUTH_ORG_ID_FIELD).references("id").inTable(TableName.Organization).onDelete("SET NULL");
+    }
+  });
+}
+
+export async function down(knex: Knex): Promise<void> {
+  const hasDefaultOrgColumn = await knex.schema.hasColumn(TableName.SuperAdmin, DEFAULT_AUTH_ORG_ID_FIELD);
+
+  await knex.schema.alterTable(TableName.SuperAdmin, (t) => {
+    if (hasDefaultOrgColumn) {
+      t.dropForeign([DEFAULT_AUTH_ORG_ID_FIELD]);
+      t.dropColumn(DEFAULT_AUTH_ORG_ID_FIELD);
+    }
+  });
+}

backend/src/db/migrations/20240624221840_certificate-alt-names.ts

@@ -0,0 +1,24 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  if (await knex.schema.hasTable(TableName.Certificate)) {
+    const hasAltNamesColumn = await knex.schema.hasColumn(TableName.Certificate, "altNames");
+    if (!hasAltNamesColumn) {
+      await knex.schema.alterTable(TableName.Certificate, (t) => {
+        t.string("altNames").defaultTo("");
+      });
+    }
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  if (await knex.schema.hasTable(TableName.Certificate)) {
+    if (await knex.schema.hasColumn(TableName.Certificate, "altNames")) {
+      await knex.schema.alterTable(TableName.Certificate, (t) => {
+        t.dropColumn("altNames");
+      });
+    }
+  }
+}

backend/src/db/migrations/20240626111536_integration-auth-aws-assume-role.ts

@@ -0,0 +1,35 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  const hasAwsAssumeRoleCipherText = await knex.schema.hasColumn(
+    TableName.IntegrationAuth,
+    "awsAssumeIamRoleArnCipherText"
+  );
+  const hasAwsAssumeRoleIV = await knex.schema.hasColumn(TableName.IntegrationAuth, "awsAssumeIamRoleArnIV");
+  const hasAwsAssumeRoleTag = await knex.schema.hasColumn(TableName.IntegrationAuth, "awsAssumeIamRoleArnTag");
+  if (await knex.schema.hasTable(TableName.IntegrationAuth)) {
+    await knex.schema.alterTable(TableName.IntegrationAuth, (t) => {
+      if (!hasAwsAssumeRoleCipherText) t.text("awsAssumeIamRoleArnCipherText");
+      if (!hasAwsAssumeRoleIV) t.text("awsAssumeIamRoleArnIV");
+      if (!hasAwsAssumeRoleTag) t.text("awsAssumeIamRoleArnTag");
+    });
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  const hasAwsAssumeRoleCipherText = await knex.schema.hasColumn(
+    TableName.IntegrationAuth,
+    "awsAssumeIamRoleArnCipherText"
+  );
+  const hasAwsAssumeRoleIV = await knex.schema.hasColumn(TableName.IntegrationAuth, "awsAssumeIamRoleArnIV");
+  const hasAwsAssumeRoleTag = await knex.schema.hasColumn(TableName.IntegrationAuth, "awsAssumeIamRoleArnTag");
+  if (await knex.schema.hasTable(TableName.IntegrationAuth)) {
+    await knex.schema.alterTable(TableName.IntegrationAuth, (t) => {
+      if (hasAwsAssumeRoleCipherText) t.dropColumn("awsAssumeIamRoleArnCipherText");
+      if (hasAwsAssumeRoleIV) t.dropColumn("awsAssumeIamRoleArnIV");
+      if (hasAwsAssumeRoleTag) t.dropColumn("awsAssumeIamRoleArnTag");
+    });
+  }
+}

backend/src/db/migrations/20240626115035_admin-login-method-config.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.SuperAdmin, "enabledLoginMethods"))) {
+    await knex.schema.alterTable(TableName.SuperAdmin, (tb) => {
+      tb.specificType("enabledLoginMethods", "text[]");
+    });
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  if (await knex.schema.hasColumn(TableName.SuperAdmin, "enabledLoginMethods")) {
+    await knex.schema.alterTable(TableName.SuperAdmin, (t) => {
+      t.dropColumn("enabledLoginMethods");
+    });
+  }
+}

backend/src/db/migrations/20240626171758_add-ldap-unique-user-attribute.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.LdapConfig, "uniqueUserAttribute"))) {
+    await knex.schema.alterTable(TableName.LdapConfig, (tb) => {
+      tb.string("uniqueUserAttribute").notNullable().defaultTo("");
+    });
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  if (await knex.schema.hasColumn(TableName.LdapConfig, "uniqueUserAttribute")) {
+    await knex.schema.alterTable(TableName.LdapConfig, (t) => {
+      t.dropColumn("uniqueUserAttribute");
+    });
+  }
+}

backend/src/db/migrations/20240626171943_configurable-audit-log-retention.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.Project, "auditLogsRetentionDays"))) {
+    await knex.schema.alterTable(TableName.Project, (tb) => {
+      tb.integer("auditLogsRetentionDays").nullable();
+    });
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  if (await knex.schema.hasColumn(TableName.Project, "auditLogsRetentionDays")) {
+    await knex.schema.alterTable(TableName.Project, (t) => {
+      t.dropColumn("auditLogsRetentionDays");
+    });
+  }
+}

backend/src/db/migrations/20240627173239_add-oidc-updated-at-trigger.ts

@@ -0,0 +1,12 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+import { createOnUpdateTrigger, dropOnUpdateTrigger } from "../utils";
+
+export async function up(knex: Knex): Promise<void> {
+  await createOnUpdateTrigger(knex, TableName.OidcConfig);
+}
+
+export async function down(knex: Knex): Promise<void> {
+  await dropOnUpdateTrigger(knex, TableName.OidcConfig);
+}

backend/src/db/migrations/20240701143900_member-project-favorite.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.OrgMembership, "projectFavorites"))) {
+    await knex.schema.alterTable(TableName.OrgMembership, (tb) => {
+      tb.specificType("projectFavorites", "text[]");
+    });
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  if (await knex.schema.hasColumn(TableName.OrgMembership, "projectFavorites")) {
+    await knex.schema.alterTable(TableName.OrgMembership, (t) => {
+      t.dropColumn("projectFavorites");
+    });
+  }
+}

backend/src/db/migrations/20240702055253_add-encrypted-webhook-url.ts

@@ -0,0 +1,53 @@
+import { Knex } from "knex";
+
+import { WebhookType } from "@app/services/webhook/webhook-types";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  const hasUrlCipherText = await knex.schema.hasColumn(TableName.Webhook, "urlCipherText");
+  const hasUrlIV = await knex.schema.hasColumn(TableName.Webhook, "urlIV");
+  const hasUrlTag = await knex.schema.hasColumn(TableName.Webhook, "urlTag");
+  const hasType = await knex.schema.hasColumn(TableName.Webhook, "type");
+
+  if (await knex.schema.hasTable(TableName.Webhook)) {
+    await knex.schema.alterTable(TableName.Webhook, (tb) => {
+      if (!hasUrlCipherText) {
+        tb.text("urlCipherText");
+      }
+      if (!hasUrlIV) {
+        tb.string("urlIV");
+      }
+      if (!hasUrlTag) {
+        tb.string("urlTag");
+      }
+      if (!hasType) {
+        tb.string("type").defaultTo(WebhookType.GENERAL);
+      }
+    });
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  const hasUrlCipherText = await knex.schema.hasColumn(TableName.Webhook, "urlCipherText");
+  const hasUrlIV = await knex.schema.hasColumn(TableName.Webhook, "urlIV");
+  const hasUrlTag = await knex.schema.hasColumn(TableName.Webhook, "urlTag");
+  const hasType = await knex.schema.hasColumn(TableName.Webhook, "type");
+
+  if (await knex.schema.hasTable(TableName.Webhook)) {
+    await knex.schema.alterTable(TableName.Webhook, (t) => {
+      if (hasUrlCipherText) {
+        t.dropColumn("urlCipherText");
+      }
+      if (hasUrlIV) {
+        t.dropColumn("urlIV");
+      }
+      if (hasUrlTag) {
+        t.dropColumn("urlTag");
+      }
+      if (hasType) {
+        t.dropColumn("type");
+      }
+    });
+  }
+}

backend/src/db/migrations/20240702131735_secret-approval-groups.ts

@@ -0,0 +1,188 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  // migrate secret approval policy approvers to user id
+  const hasApproverUserId = await knex.schema.hasColumn(TableName.SecretApprovalPolicyApprover, "approverUserId");
+  const hasApproverId = await knex.schema.hasColumn(TableName.SecretApprovalPolicyApprover, "approverId");
+  if (!hasApproverUserId) {
+    // add the new fields
+    await knex.schema.alterTable(TableName.SecretApprovalPolicyApprover, (tb) => {
+      // if (hasApproverId) tb.setNullable("approverId");
+      tb.uuid("approverUserId");
+      tb.foreign("approverUserId").references("id").inTable(TableName.Users).onDelete("CASCADE");
+    });
+
+    // convert project membership id => user id
+    await knex(TableName.SecretApprovalPolicyApprover).update({
+      // eslint-disable-next-line
+      // @ts-ignore because generate schema happens after this
+      approverUserId: knex(TableName.ProjectMembership)
+        .select("userId")
+        .where("id", knex.raw("??", [`${TableName.SecretApprovalPolicyApprover}.approverId`]))
+    });
+    // drop the old field
+    await knex.schema.alterTable(TableName.SecretApprovalPolicyApprover, (tb) => {
+      if (hasApproverId) tb.dropColumn("approverId");
+      tb.uuid("approverUserId").notNullable().alter();
+    });
+  }
+
+  // migrate secret approval request committer and statusChangeBy to user id
+  const hasSecretApprovalRequestTable = await knex.schema.hasTable(TableName.SecretApprovalRequest);
+  const hasCommitterUserId = await knex.schema.hasColumn(TableName.SecretApprovalRequest, "committerUserId");
+  const hasCommitterId = await knex.schema.hasColumn(TableName.SecretApprovalRequest, "committerId");
+  const hasStatusChangeBy = await knex.schema.hasColumn(TableName.SecretApprovalRequest, "statusChangeBy");
+  const hasStatusChangedByUserId = await knex.schema.hasColumn(
+    TableName.SecretApprovalRequest,
+    "statusChangedByUserId"
+  );
+  if (hasSecretApprovalRequestTable) {
+    // new fields
+    await knex.schema.alterTable(TableName.SecretApprovalRequest, (tb) => {
+      // if (hasCommitterId) tb.setNullable("committerId");
+      if (!hasCommitterUserId) {
+        tb.uuid("committerUserId");
+        tb.foreign("committerUserId").references("id").inTable(TableName.Users).onDelete("SET NULL");
+      }
+      if (!hasStatusChangedByUserId) {
+        tb.uuid("statusChangedByUserId");
+        tb.foreign("statusChangedByUserId").references("id").inTable(TableName.Users).onDelete("SET NULL");
+      }
+    });
+
+    // copy the assigned project membership => user id to new fields
+    await knex(TableName.SecretApprovalRequest).update({
+      // eslint-disable-next-line
+      // @ts-ignore because generate schema happens after this
+      committerUserId: knex(TableName.ProjectMembership)
+        .select("userId")
+        .where("id", knex.raw("??", [`${TableName.SecretApprovalRequest}.committerId`])),
+      // eslint-disable-next-line
+      // @ts-ignore because generate schema happens after this
+      statusChangedByUserId: knex(TableName.ProjectMembership)
+        .select("userId")
+        .where("id", knex.raw("??", [`${TableName.SecretApprovalRequest}.statusChangeBy`]))
+    });
+    // drop old fields
+    await knex.schema.alterTable(TableName.SecretApprovalRequest, (tb) => {
+      if (hasStatusChangeBy) tb.dropColumn("statusChangeBy");
+      if (hasCommitterId) tb.dropColumn("committerId");
+      tb.uuid("committerUserId").notNullable().alter();
+    });
+  }
+
+  // migrate secret approval request reviewer to user id
+  const hasMemberId = await knex.schema.hasColumn(TableName.SecretApprovalRequestReviewer, "member");
+  const hasReviewerUserId = await knex.schema.hasColumn(TableName.SecretApprovalRequestReviewer, "reviewerUserId");
+  if (!hasReviewerUserId) {
+    // new fields
+    await knex.schema.alterTable(TableName.SecretApprovalRequestReviewer, (tb) => {
+      // if (hasMemberId) tb.setNullable("member");
+      tb.uuid("reviewerUserId");
+      tb.foreign("reviewerUserId").references("id").inTable(TableName.Users).onDelete("SET NULL");
+    });
+    // copy project membership => user id to new fields
+    await knex(TableName.SecretApprovalRequestReviewer).update({
+      // eslint-disable-next-line
+      // @ts-ignore because generate schema happens after this
+      reviewerUserId: knex(TableName.ProjectMembership)
+        .select("userId")
+        .where("id", knex.raw("??", [`${TableName.SecretApprovalRequestReviewer}.member`]))
+    });
+    // drop table
+    await knex.schema.alterTable(TableName.SecretApprovalRequestReviewer, (tb) => {
+      if (hasMemberId) tb.dropColumn("member");
+      tb.uuid("reviewerUserId").notNullable().alter();
+    });
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  const hasApproverUserId = await knex.schema.hasColumn(TableName.SecretApprovalPolicyApprover, "approverUserId");
+  const hasApproverId = await knex.schema.hasColumn(TableName.SecretApprovalPolicyApprover, "approverId");
+  if (hasApproverUserId) {
+    await knex.schema.alterTable(TableName.SecretApprovalPolicyApprover, (tb) => {
+      if (!hasApproverId) {
+        tb.uuid("approverId");
+        tb.foreign("approverId").references("id").inTable(TableName.ProjectMembership).onDelete("CASCADE");
+      }
+    });
+
+    if (!hasApproverId) {
+      await knex(TableName.SecretApprovalPolicyApprover).update({
+        // eslint-disable-next-line
+        // @ts-ignore because generate schema happens after this
+        approverId: knex(TableName.ProjectMembership)
+          .select("id")
+          .where("userId", knex.raw("??", [`${TableName.SecretApprovalPolicyApprover}.approverUserId`]))
+      });
+      await knex.schema.alterTable(TableName.SecretApprovalPolicyApprover, (tb) => {
+        tb.dropColumn("approverUserId");
+        tb.uuid("approverId").notNullable().alter();
+      });
+    }
+  }
+
+  const hasSecretApprovalRequestTable = await knex.schema.hasTable(TableName.SecretApprovalRequest);
+  const hasCommitterUserId = await knex.schema.hasColumn(TableName.SecretApprovalRequest, "committerUserId");
+  const hasCommitterId = await knex.schema.hasColumn(TableName.SecretApprovalRequest, "committerId");
+  const hasStatusChangeBy = await knex.schema.hasColumn(TableName.SecretApprovalRequest, "statusChangeBy");
+  const hasStatusChangedByUser = await knex.schema.hasColumn(TableName.SecretApprovalRequest, "statusChangedByUserId");
+  if (hasSecretApprovalRequestTable) {
+    await knex.schema.alterTable(TableName.SecretApprovalRequest, (tb) => {
+      // if (hasCommitterId) tb.uuid("committerId").notNullable().alter();
+      if (!hasCommitterId) {
+        tb.uuid("committerId");
+        tb.foreign("committerId").references("id").inTable(TableName.ProjectMembership).onDelete("CASCADE");
+      }
+      if (!hasStatusChangeBy) {
+        tb.uuid("statusChangeBy");
+        tb.foreign("statusChangeBy").references("id").inTable(TableName.ProjectMembership).onDelete("SET NULL");
+      }
+    });
+
+    await knex(TableName.SecretApprovalRequest).update({
+      // eslint-disable-next-line
+      // @ts-ignore because generate schema happens after this
+      committerId: knex(TableName.ProjectMembership)
+        .select("id")
+        .where("userId", knex.raw("??", [`${TableName.SecretApprovalRequest}.committerUserId`])),
+      // eslint-disable-next-line
+      // @ts-ignore because generate schema happens after this
+      statusChangeBy: knex(TableName.ProjectMembership)
+        .select("id")
+        .where("userId", knex.raw("??", [`${TableName.SecretApprovalRequest}.statusChangedByUserId`]))
+    });
+
+    await knex.schema.alterTable(TableName.SecretApprovalRequest, (tb) => {
+      if (hasCommitterUserId) tb.dropColumn("committerUserId");
+      if (hasStatusChangedByUser) tb.dropColumn("statusChangedByUserId");
+      if (hasCommitterId) tb.uuid("committerId").notNullable().alter();
+    });
+  }
+
+  const hasMemberId = await knex.schema.hasColumn(TableName.SecretApprovalRequestReviewer, "member");
+  const hasReviewerUserId = await knex.schema.hasColumn(TableName.SecretApprovalRequestReviewer, "reviewerUserId");
+  if (hasReviewerUserId) {
+    if (!hasMemberId) {
+      await knex.schema.alterTable(TableName.SecretApprovalRequestReviewer, (tb) => {
+        // if (hasMemberId) tb.uuid("member").notNullable().alter();
+        tb.uuid("member");
+        tb.foreign("member").references("id").inTable(TableName.ProjectMembership).onDelete("CASCADE");
+      });
+    }
+    await knex(TableName.SecretApprovalRequestReviewer).update({
+      // eslint-disable-next-line
+      // @ts-ignore because generate schema happens after this
+      member: knex(TableName.ProjectMembership)
+        .select("id")
+        .where("userId", knex.raw("??", [`${TableName.SecretApprovalRequestReviewer}.reviewerUserId`]))
+    });
+    await knex.schema.alterTable(TableName.SecretApprovalRequestReviewer, (tb) => {
+      tb.uuid("member").notNullable().alter();
+      tb.dropColumn("reviewerUserId");
+    });
+  }
+}

backend/src/db/migrations/20240702175124_identity-token-auth.ts

@@ -0,0 +1,24 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+import { createOnUpdateTrigger, dropOnUpdateTrigger } from "../utils";
+
+export async function up(knex: Knex): Promise<void> {
+  await knex.schema.createTable(TableName.IdentityTokenAuth, (t) => {
+    t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+    t.bigInteger("accessTokenTTL").defaultTo(7200).notNullable();
+    t.bigInteger("accessTokenMaxTTL").defaultTo(7200).notNullable();
+    t.bigInteger("accessTokenNumUsesLimit").defaultTo(0).notNullable();
+    t.jsonb("accessTokenTrustedIps").notNullable();
+    t.timestamps(true, true, true);
+    t.uuid("identityId").notNullable().unique();
+    t.foreign("identityId").references("id").inTable(TableName.Identity).onDelete("CASCADE");
+  });
+
+  await createOnUpdateTrigger(knex, TableName.IdentityTokenAuth);
+}
+
+export async function down(knex: Knex): Promise<void> {
+  await knex.schema.dropTableIfExists(TableName.IdentityTokenAuth);
+  await dropOnUpdateTrigger(knex, TableName.IdentityTokenAuth);
+}

backend/src/db/migrations/20240704161322_identity-access-token-name.ts

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

backend/src/db/schemas/certificate-authorities.ts

@@ -0,0 +1,37 @@
+// 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 CertificateAuthoritiesSchema = z.object({
+  id: z.string().uuid(),
+  createdAt: z.date(),
+  updatedAt: z.date(),
+  parentCaId: z.string().uuid().nullable().optional(),
+  projectId: z.string(),
+  type: z.string(),
+  status: z.string(),
+  friendlyName: z.string(),
+  organization: z.string(),
+  ou: z.string(),
+  country: z.string(),
+  province: z.string(),
+  locality: z.string(),
+  commonName: z.string(),
+  dn: z.string(),
+  serialNumber: z.string().nullable().optional(),
+  maxPathLength: z.number().nullable().optional(),
+  keyAlgorithm: z.string(),
+  notBefore: z.date().nullable().optional(),
+  notAfter: z.date().nullable().optional()
+});
+
+export type TCertificateAuthorities = z.infer<typeof CertificateAuthoritiesSchema>;
+export type TCertificateAuthoritiesInsert = Omit<z.input<typeof CertificateAuthoritiesSchema>, TImmutableDBKeys>;
+export type TCertificateAuthoritiesUpdate = Partial<
+  Omit<z.input<typeof CertificateAuthoritiesSchema>, TImmutableDBKeys>
+>;

backend/src/db/schemas/certificate-authority-certs.ts

@@ -0,0 +1,25 @@
+// Code generated by automation script, DO NOT EDIT.
+// Automated by pulling database and generating zod schema
+// To update. Just run npm run generate:schema
+// Written by akhilmhdh.
+
+import { z } from "zod";
+
+import { zodBuffer } from "@app/lib/zod";
+
+import { TImmutableDBKeys } from "./models";
+
+export const CertificateAuthorityCertsSchema = z.object({
+  id: z.string().uuid(),
+  createdAt: z.date(),
+  updatedAt: z.date(),
+  caId: z.string().uuid(),
+  encryptedCertificate: zodBuffer,
+  encryptedCertificateChain: zodBuffer
+});
+
+export type TCertificateAuthorityCerts = z.infer<typeof CertificateAuthorityCertsSchema>;
+export type TCertificateAuthorityCertsInsert = Omit<z.input<typeof CertificateAuthorityCertsSchema>, TImmutableDBKeys>;
+export type TCertificateAuthorityCertsUpdate = Partial<
+  Omit<z.input<typeof CertificateAuthorityCertsSchema>, TImmutableDBKeys>
+>;

backend/src/db/schemas/certificate-authority-crl.ts

@@ -0,0 +1,24 @@
+// Code generated by automation script, DO NOT EDIT.
+// Automated by pulling database and generating zod schema
+// To update. Just run npm run generate:schema
+// Written by akhilmhdh.
+
+import { z } from "zod";
+
+import { zodBuffer } from "@app/lib/zod";
+
+import { TImmutableDBKeys } from "./models";
+
+export const CertificateAuthorityCrlSchema = z.object({
+  id: z.string().uuid(),
+  createdAt: z.date(),
+  updatedAt: z.date(),
+  caId: z.string().uuid(),
+  encryptedCrl: zodBuffer
+});
+
+export type TCertificateAuthorityCrl = z.infer<typeof CertificateAuthorityCrlSchema>;
+export type TCertificateAuthorityCrlInsert = Omit<z.input<typeof CertificateAuthorityCrlSchema>, TImmutableDBKeys>;
+export type TCertificateAuthorityCrlUpdate = Partial<
+  Omit<z.input<typeof CertificateAuthorityCrlSchema>, TImmutableDBKeys>
+>;

backend/src/db/schemas/certificate-authority-secret.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 { zodBuffer } from "@app/lib/zod";
+
+import { TImmutableDBKeys } from "./models";
+
+export const CertificateAuthoritySecretSchema = z.object({
+  id: z.string().uuid(),
+  createdAt: z.date(),
+  updatedAt: z.date(),
+  caId: z.string().uuid(),
+  encryptedPrivateKey: zodBuffer
+});
+
+export type TCertificateAuthoritySecret = z.infer<typeof CertificateAuthoritySecretSchema>;
+export type TCertificateAuthoritySecretInsert = Omit<
+  z.input<typeof CertificateAuthoritySecretSchema>,
+  TImmutableDBKeys
+>;
+export type TCertificateAuthoritySecretUpdate = Partial<
+  Omit<z.input<typeof CertificateAuthoritySecretSchema>, TImmutableDBKeys>
+>;

backend/src/db/schemas/certificate-bodies.ts

@@ -0,0 +1,22 @@
+// Code generated by automation script, DO NOT EDIT.
+// Automated by pulling database and generating zod schema
+// To update. Just run npm run generate:schema
+// Written by akhilmhdh.
+
+import { z } from "zod";
+
+import { zodBuffer } from "@app/lib/zod";
+
+import { TImmutableDBKeys } from "./models";
+
+export const CertificateBodiesSchema = z.object({
+  id: z.string().uuid(),
+  createdAt: z.date(),
+  updatedAt: z.date(),
+  certId: z.string().uuid(),
+  encryptedCertificate: zodBuffer
+});
+
+export type TCertificateBodies = z.infer<typeof CertificateBodiesSchema>;
+export type TCertificateBodiesInsert = Omit<z.input<typeof CertificateBodiesSchema>, TImmutableDBKeys>;
+export type TCertificateBodiesUpdate = Partial<Omit<z.input<typeof CertificateBodiesSchema>, TImmutableDBKeys>>;

backend/src/db/schemas/certificate-secrets.ts

@@ -0,0 +1,21 @@
+// Code generated by automation script, DO NOT EDIT.
+// Automated by pulling database and generating zod schema
+// To update. Just run npm run generate:schema
+// Written by akhilmhdh.
+
+import { z } from "zod";
+
+import { TImmutableDBKeys } from "./models";
+
+export const CertificateSecretsSchema = z.object({
+  id: z.string().uuid(),
+  createdAt: z.date(),
+  updatedAt: z.date(),
+  certId: z.string().uuid(),
+  pk: z.string(),
+  sk: z.string()
+});
+
+export type TCertificateSecrets = z.infer<typeof CertificateSecretsSchema>;
+export type TCertificateSecretsInsert = Omit<z.input<typeof CertificateSecretsSchema>, TImmutableDBKeys>;
+export type TCertificateSecretsUpdate = Partial<Omit<z.input<typeof CertificateSecretsSchema>, TImmutableDBKeys>>;

backend/src/db/schemas/certificates.ts

@@ -0,0 +1,28 @@
+// 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 CertificatesSchema = z.object({
+  id: z.string().uuid(),
+  createdAt: z.date(),
+  updatedAt: z.date(),
+  caId: z.string().uuid(),
+  status: z.string(),
+  serialNumber: z.string(),
+  friendlyName: z.string(),
+  commonName: z.string(),
+  notBefore: z.date(),
+  notAfter: z.date(),
+  revokedAt: z.date().nullable().optional(),
+  revocationReason: z.number().nullable().optional(),
+  altNames: z.string().default("").nullable().optional()
+});
+
+export type TCertificates = z.infer<typeof CertificatesSchema>;
+export type TCertificatesInsert = Omit<z.input<typeof CertificatesSchema>, TImmutableDBKeys>;
+export type TCertificatesUpdate = Partial<Omit<z.input<typeof CertificatesSchema>, TImmutableDBKeys>>;

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

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

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

@@ -0,0 +1,23 @@
+// 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 IdentityTokenAuthsSchema = z.object({
+  id: z.string().uuid(),
+  accessTokenTTL: z.coerce.number().default(7200),
+  accessTokenMaxTTL: z.coerce.number().default(7200),
+  accessTokenNumUsesLimit: z.coerce.number().default(0),
+  accessTokenTrustedIps: z.unknown(),
+  createdAt: z.date(),
+  updatedAt: z.date(),
+  identityId: z.string().uuid()
+});
+
+export type TIdentityTokenAuths = z.infer<typeof IdentityTokenAuthsSchema>;
+export type TIdentityTokenAuthsInsert = Omit<z.input<typeof IdentityTokenAuthsSchema>, TImmutableDBKeys>;
+export type TIdentityTokenAuthsUpdate = Partial<Omit<z.input<typeof IdentityTokenAuthsSchema>, TImmutableDBKeys>>;

backend/src/db/schemas/index.ts

@@ -8,6 +8,13 @@ export * from "./audit-logs";
 export * from "./auth-token-sessions";
 export * from "./auth-tokens";
 export * from "./backup-private-key";
+export * from "./certificate-authorities";
+export * from "./certificate-authority-certs";
+export * from "./certificate-authority-crl";
+export * from "./certificate-authority-secret";
+export * from "./certificate-bodies";
+export * from "./certificate-secrets";
+export * from "./certificates";
 export * from "./dynamic-secret-leases";
 export * from "./dynamic-secrets";
 export * from "./git-app-install-sessions";
@@ -25,6 +32,7 @@ export * from "./identity-org-memberships";
 export * from "./identity-project-additional-privilege";
 export * from "./identity-project-membership-role";
 export * from "./identity-project-memberships";
+export * from "./identity-token-auths";
 export * from "./identity-ua-client-secrets";
 export * from "./identity-universal-auths";
 export * from "./incident-contacts";
@@ -36,6 +44,7 @@ export * from "./kms-root-config";
 export * from "./ldap-configs";
 export * from "./ldap-group-maps";
 export * from "./models";
+export * from "./oidc-configs";
 export * from "./org-bots";
 export * from "./org-memberships";
 export * from "./org-roles";

backend/src/db/schemas/integration-auths.ts

@@ -29,7 +29,10 @@ export const IntegrationAuthsSchema = z.object({
   keyEncoding: z.string(),
   projectId: z.string(),
   createdAt: z.date(),
-  updatedAt: z.date()
+  updatedAt: z.date(),
+  awsAssumeIamRoleArnCipherText: z.string().nullable().optional(),
+  awsAssumeIamRoleArnIV: z.string().nullable().optional(),
+  awsAssumeIamRoleArnTag: z.string().nullable().optional()
 });
 
 export type TIntegrationAuths = z.infer<typeof IntegrationAuthsSchema>;

backend/src/db/schemas/ldap-configs.ts

@@ -26,7 +26,8 @@ export const LdapConfigsSchema = z.object({
   updatedAt: z.date(),
   groupSearchBase: z.string().default(""),
   groupSearchFilter: z.string().default(""),
-  searchFilter: z.string().default("")
+  searchFilter: z.string().default(""),
+  uniqueUserAttribute: z.string().default("")
 });
 
 export type TLdapConfigs = z.infer<typeof LdapConfigsSchema>;

backend/src/db/schemas/models.ts

@@ -2,6 +2,13 @@ import { z } from "zod";
 
 export enum TableName {
   Users = "users",
+  CertificateAuthority = "certificate_authorities",
+  CertificateAuthorityCert = "certificate_authority_certs",
+  CertificateAuthoritySecret = "certificate_authority_secret",
+  CertificateAuthorityCrl = "certificate_authority_crl",
+  Certificate = "certificates",
+  CertificateBody = "certificate_bodies",
+  CertificateSecret = "certificate_secrets",
   Groups = "groups",
   GroupProjectMembership = "group_project_memberships",
   GroupProjectMembershipRole = "group_project_membership_roles",
@@ -46,6 +53,7 @@ export enum TableName {
   Webhook = "webhooks",
   Identity = "identities",
   IdentityAccessToken = "identity_access_tokens",
+  IdentityTokenAuth = "identity_token_auths",
   IdentityUniversalAuth = "identity_universal_auths",
   IdentityKubernetesAuth = "identity_kubernetes_auths",
   IdentityGcpAuth = "identity_gcp_auths",
@@ -71,6 +79,7 @@ export enum TableName {
   SecretRotationOutput = "secret_rotation_outputs",
   SamlConfig = "saml_configs",
   LdapConfig = "ldap_configs",
+  OidcConfig = "oidc_configs",
   LdapGroupMap = "ldap_group_maps",
   AuditLog = "audit_logs",
   AuditLogStream = "audit_log_streams",
@@ -153,6 +162,7 @@ export enum ProjectUpgradeStatus {
 }
 
 export enum IdentityAuthMethod {
+  TOKEN_AUTH = "token-auth",
   Univeral = "universal-auth",
   KUBERNETES_AUTH = "kubernetes-auth",
   GCP_AUTH = "gcp-auth",

backend/src/db/schemas/oidc-configs.ts

@@ -0,0 +1,34 @@
+// 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 OidcConfigsSchema = z.object({
+  id: z.string().uuid(),
+  discoveryURL: z.string().nullable().optional(),
+  issuer: z.string().nullable().optional(),
+  authorizationEndpoint: z.string().nullable().optional(),
+  jwksUri: z.string().nullable().optional(),
+  tokenEndpoint: z.string().nullable().optional(),
+  userinfoEndpoint: z.string().nullable().optional(),
+  encryptedClientId: z.string(),
+  configurationType: z.string(),
+  clientIdIV: z.string(),
+  clientIdTag: z.string(),
+  encryptedClientSecret: z.string(),
+  clientSecretIV: z.string(),
+  clientSecretTag: z.string(),
+  allowedEmailDomains: z.string().nullable().optional(),
+  isActive: z.boolean(),
+  createdAt: z.date(),
+  updatedAt: z.date(),
+  orgId: z.string().uuid()
+});
+
+export type TOidcConfigs = z.infer<typeof OidcConfigsSchema>;
+export type TOidcConfigsInsert = Omit<z.input<typeof OidcConfigsSchema>, TImmutableDBKeys>;
+export type TOidcConfigsUpdate = Partial<Omit<z.input<typeof OidcConfigsSchema>, TImmutableDBKeys>>;

backend/src/db/schemas/org-memberships.ts

@@ -16,7 +16,8 @@ export const OrgMembershipsSchema = z.object({
   updatedAt: z.date(),
   userId: z.string().uuid().nullable().optional(),
   orgId: z.string().uuid(),
-  roleId: z.string().uuid().nullable().optional()
+  roleId: z.string().uuid().nullable().optional(),
+  projectFavorites: z.string().array().nullable().optional()
 });
 
 export type TOrgMemberships = z.infer<typeof OrgMembershipsSchema>;

backend/src/db/schemas/projects.ts

@@ -17,7 +17,9 @@ export const ProjectsSchema = z.object({
   updatedAt: z.date(),
   version: z.number().default(1),
   upgradeStatus: z.string().nullable().optional(),
-  pitVersionLimit: z.number().default(10)
+  pitVersionLimit: z.number().default(10),
+  kmsCertificateKeyId: z.string().uuid().nullable().optional(),
+  auditLogsRetentionDays: z.number().nullable().optional()
 });
 
 export type TProjects = z.infer<typeof ProjectsSchema>;

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

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

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

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

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

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

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

@@ -14,8 +14,8 @@ export const SecretSharingSchema = z.object({
   tag: z.string(),
   hashedHex: z.string(),
   expiresAt: z.date(),
-  userId: z.string().uuid(),
-  orgId: z.string().uuid(),
+  userId: z.string().uuid().nullable().optional(),
+  orgId: z.string().uuid().nullable().optional(),
   createdAt: z.date(),
   updatedAt: z.date(),
   expiresAfterViews: z.number().nullable().optional()

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

@@ -15,7 +15,8 @@ export const SecretTagsSchema = z.object({
   createdAt: z.date(),
   updatedAt: z.date(),
   createdBy: z.string().uuid().nullable().optional(),
-  projectId: z.string()
+  projectId: z.string(),
+  createdByActorType: z.string().default("user")
 });
 
 export type TSecretTags = z.infer<typeof SecretTagsSchema>;

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

@@ -16,7 +16,10 @@ export const SuperAdminSchema = z.object({
   allowedSignUpDomain: z.string().nullable().optional(),
   instanceId: z.string().uuid().default("00000000-0000-0000-0000-000000000000"),
   trustSamlEmails: z.boolean().default(false).nullable().optional(),
-  trustLdapEmails: z.boolean().default(false).nullable().optional()
+  trustLdapEmails: z.boolean().default(false).nullable().optional(),
+  trustOidcEmails: z.boolean().default(false).nullable().optional(),
+  defaultAuthOrgId: z.string().uuid().nullable().optional(),
+  enabledLoginMethods: z.string().array().nullable().optional()
 });
 
 export type TSuperAdmin = z.infer<typeof SuperAdminSchema>;

backend/src/db/schemas/user-encryption-keys.ts

@@ -21,7 +21,12 @@ export const UserEncryptionKeysSchema = z.object({
   tag: z.string(),
   salt: z.string(),
   verifier: z.string(),
-  userId: z.string().uuid()
+  userId: z.string().uuid(),
+  hashedPassword: z.string().nullable().optional(),
+  serverEncryptedPrivateKey: z.string().nullable().optional(),
+  serverEncryptedPrivateKeyIV: z.string().nullable().optional(),
+  serverEncryptedPrivateKeyTag: z.string().nullable().optional(),
+  serverEncryptedPrivateKeyEncoding: z.string().nullable().optional()
 });
 
 export type TUserEncryptionKeys = z.infer<typeof UserEncryptionKeysSchema>;

backend/src/db/schemas/webhooks.ts

@@ -21,7 +21,11 @@ export const WebhooksSchema = z.object({
   keyEncoding: z.string().nullable().optional(),
   createdAt: z.date(),
   updatedAt: z.date(),
-  envId: z.string().uuid()
+  envId: z.string().uuid(),
+  urlCipherText: z.string().nullable().optional(),
+  urlIV: z.string().nullable().optional(),
+  urlTag: z.string().nullable().optional(),
+  type: z.string().default("general").nullable().optional()
 });
 
 export type TWebhooks = z.infer<typeof WebhooksSchema>;

backend/src/ee/routes/v1/certificate-authority-crl-router.ts

@@ -0,0 +1,86 @@
+import { z } from "zod";
+
+import { EventType } from "@app/ee/services/audit-log/audit-log-types";
+import { CERTIFICATE_AUTHORITIES } from "@app/lib/api-docs";
+import { readLimit } from "@app/server/config/rateLimiter";
+import { verifyAuth } from "@app/server/plugins/auth/verify-auth";
+import { AuthMode } from "@app/services/auth/auth-type";
+
+export const registerCaCrlRouter = async (server: FastifyZodProvider) => {
+  server.route({
+    method: "GET",
+    url: "/:caId/crl",
+    config: {
+      rateLimit: readLimit
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    schema: {
+      description: "Get CRL of the CA",
+      params: z.object({
+        caId: z.string().trim().describe(CERTIFICATE_AUTHORITIES.GET_CRL.caId)
+      }),
+      response: {
+        200: z.object({
+          crl: z.string().describe(CERTIFICATE_AUTHORITIES.GET_CRL.crl)
+        })
+      }
+    },
+    handler: async (req) => {
+      const { crl, ca } = await server.services.certificateAuthorityCrl.getCaCrl({
+        caId: req.params.caId,
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        actorAuthMethod: req.permission.authMethod,
+        actorOrgId: req.permission.orgId
+      });
+
+      await server.services.auditLog.createAuditLog({
+        ...req.auditLogInfo,
+        projectId: ca.projectId,
+        event: {
+          type: EventType.GET_CA_CRL,
+          metadata: {
+            caId: ca.id,
+            dn: ca.dn
+          }
+        }
+      });
+
+      return {
+        crl
+      };
+    }
+  });
+
+  // server.route({
+  //   method: "GET",
+  //   url: "/:caId/crl/rotate",
+  //   config: {
+  //     rateLimit: writeLimit
+  //   },
+  //   onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+  //   schema: {
+  //     description: "Rotate CRL of the CA",
+  //     params: z.object({
+  //       caId: z.string().trim()
+  //     }),
+  //     response: {
+  //       200: z.object({
+  //         message: z.string()
+  //       })
+  //     }
+  //   },
+  //   handler: async (req) => {
+  //     await server.services.certificateAuthority.rotateCaCrl({
+  //       caId: req.params.caId,
+  //       actor: req.permission.type,
+  //       actorId: req.permission.id,
+  //       actorAuthMethod: req.permission.authMethod,
+  //       actorOrgId: req.permission.orgId
+  //     });
+  //     return {
+  //       message: "Successfully rotated CA CRL"
+  //     };
+  //   }
+  // });
+};

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

@@ -1,15 +1,18 @@
 import { registerAccessApprovalPolicyRouter } from "./access-approval-policy-router";
 import { registerAccessApprovalRequestRouter } from "./access-approval-request-router";
 import { registerAuditLogStreamRouter } from "./audit-log-stream-router";
+import { registerCaCrlRouter } from "./certificate-authority-crl-router";
 import { registerDynamicSecretLeaseRouter } from "./dynamic-secret-lease-router";
 import { registerDynamicSecretRouter } from "./dynamic-secret-router";
 import { registerGroupRouter } from "./group-router";
 import { registerIdentityProjectAdditionalPrivilegeRouter } from "./identity-project-additional-privilege-router";
 import { registerLdapRouter } from "./ldap-router";
 import { registerLicenseRouter } from "./license-router";
+import { registerOidcRouter } from "./oidc-router";
 import { registerOrgRoleRouter } from "./org-role-router";
 import { registerProjectRoleRouter } from "./project-role-router";
 import { registerProjectRouter } from "./project-router";
+import { registerRateLimitRouter } from "./rate-limit-router";
 import { registerSamlRouter } from "./saml-router";
 import { registerScimRouter } from "./scim-router";
 import { registerSecretApprovalPolicyRouter } from "./secret-approval-policy-router";
@@ -45,6 +48,7 @@ export const registerV1EERoutes = async (server: FastifyZodProvider) => {
 
   await server.register(registerAccessApprovalPolicyRouter, { prefix: "/access-approvals/policies" });
   await server.register(registerAccessApprovalRequestRouter, { prefix: "/access-approvals/requests" });
+  await server.register(registerRateLimitRouter, { prefix: "/rate-limit" });
 
   await server.register(
     async (dynamicSecretRouter) => {
@@ -54,7 +58,21 @@ export const registerV1EERoutes = async (server: FastifyZodProvider) => {
     { prefix: "/dynamic-secrets" }
   );
 
-  await server.register(registerSamlRouter, { prefix: "/sso" });
+  await server.register(
+    async (pkiRouter) => {
+      await pkiRouter.register(registerCaCrlRouter, { prefix: "/ca" });
+    },
+    { prefix: "/pki" }
+  );
+
+  await server.register(
+    async (ssoRouter) => {
+      await ssoRouter.register(registerSamlRouter);
+      await ssoRouter.register(registerOidcRouter, { prefix: "/oidc" });
+    },
+    { prefix: "/sso" }
+  );
+
   await server.register(registerScimRouter, { prefix: "/scim" });
   await server.register(registerLdapRouter, { prefix: "/ldap" });
   await server.register(registerSecretScanningRouter, { prefix: "/secret-scanning" });

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

@@ -53,7 +53,7 @@ export const registerLdapRouter = async (server: FastifyZodProvider) => {
       // eslint-disable-next-line
       async (req: IncomingMessage, user, cb) => {
         try {
-          if (!user.email) throw new BadRequestError({ message: "Invalid request. Missing email." });
+          if (!user.mail) throw new BadRequestError({ message: "Invalid request. Missing mail attribute on user." });
           const ldapConfig = (req as unknown as FastifyRequest).ldapConfig as TLDAPConfig;
 
           let groups: { dn: string; cn: string }[] | undefined;
@@ -70,10 +70,13 @@ export const registerLdapRouter = async (server: FastifyZodProvider) => {
             groups = await searchGroups(ldapConfig, groupSearchFilter, ldapConfig.groupSearchBase);
           }
 
+          const externalId = ldapConfig.uniqueUserAttribute ? user[ldapConfig.uniqueUserAttribute] : user.uidNumber;
+          const username = ldapConfig.uniqueUserAttribute ? externalId : user.uid;
+
           const { isUserCompleted, providerAuthToken } = await server.services.ldap.ldapLogin({
+            externalId,
+            username,
             ldapConfigId: ldapConfig.id,
-            externalId: user.uidNumber,
-            username: user.uid,
             firstName: user.givenName ?? user.cn ?? "",
             lastName: user.sn ?? "",
             email: user.mail,
@@ -138,6 +141,7 @@ export const registerLdapRouter = async (server: FastifyZodProvider) => {
           url: z.string(),
           bindDN: z.string(),
           bindPass: z.string(),
+          uniqueUserAttribute: z.string(),
           searchBase: z.string(),
           searchFilter: z.string(),
           groupSearchBase: z.string(),
@@ -172,6 +176,7 @@ export const registerLdapRouter = async (server: FastifyZodProvider) => {
         url: z.string().trim(),
         bindDN: z.string().trim(),
         bindPass: z.string().trim(),
+        uniqueUserAttribute: z.string().trim().default("uidNumber"),
         searchBase: z.string().trim(),
         searchFilter: z.string().trim().default("(uid={{username}})"),
         groupSearchBase: z.string().trim(),
@@ -213,6 +218,7 @@ export const registerLdapRouter = async (server: FastifyZodProvider) => {
           url: z.string().trim(),
           bindDN: z.string().trim(),
           bindPass: z.string().trim(),
+          uniqueUserAttribute: z.string().trim(),
           searchBase: z.string().trim(),
           searchFilter: z.string().trim(),
           groupSearchBase: z.string().trim(),

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

@@ -0,0 +1,355 @@
+/* eslint-disable @typescript-eslint/no-explicit-any */
+/* eslint-disable @typescript-eslint/no-unsafe-return */
+/* eslint-disable @typescript-eslint/no-unsafe-member-access */
+/* eslint-disable @typescript-eslint/no-unsafe-assignment */
+/* eslint-disable @typescript-eslint/no-unsafe-call */
+/* eslint-disable @typescript-eslint/no-unsafe-argument */
+// All the any rules are disabled because passport typesense with fastify is really poor
+
+import { Authenticator, Strategy } from "@fastify/passport";
+import fastifySession from "@fastify/session";
+import RedisStore from "connect-redis";
+import { Redis } from "ioredis";
+import { z } from "zod";
+
+import { OidcConfigsSchema } from "@app/db/schemas/oidc-configs";
+import { OIDCConfigurationType } from "@app/ee/services/oidc/oidc-config-types";
+import { getConfig } from "@app/lib/config/env";
+import { authRateLimit, readLimit, writeLimit } from "@app/server/config/rateLimiter";
+import { verifyAuth } from "@app/server/plugins/auth/verify-auth";
+import { AuthMode } from "@app/services/auth/auth-type";
+
+export const registerOidcRouter = async (server: FastifyZodProvider) => {
+  const appCfg = getConfig();
+  const redis = new Redis(appCfg.REDIS_URL);
+  const passport = new Authenticator({ key: "oidc", userProperty: "passportUser" });
+
+  /*
+  - OIDC protocol cannot work without sessions: https://github.com/panva/node-openid-client/issues/190
+  - Current redis usage is not ideal and will eventually have to be refactored to use a better structure
+  - Fastify session <> Redis structure is based on the ff: https://github.com/fastify/session/blob/master/examples/redis.js
+  */
+  const redisStore = new RedisStore({
+    client: redis,
+    prefix: "oidc-session:",
+    ttl: 600 // 10 minutes
+  });
+
+  await server.register(fastifySession, {
+    secret: appCfg.COOKIE_SECRET_SIGN_KEY,
+    store: redisStore,
+    cookie: {
+      secure: appCfg.HTTPS_ENABLED,
+      sameSite: "lax" // we want cookies to be sent to Infisical in redirects originating from IDP server
+    }
+  });
+
+  await server.register(passport.initialize());
+  await server.register(passport.secureSession());
+
+  // redirect to IDP for login
+  server.route({
+    url: "/login",
+    method: "GET",
+    config: {
+      rateLimit: authRateLimit
+    },
+    schema: {
+      querystring: z.object({
+        orgSlug: z.string().trim(),
+        callbackPort: z.string().trim().optional()
+      })
+    },
+    preValidation: [
+      async (req, res) => {
+        const { orgSlug, callbackPort } = req.query;
+
+        // ensure fresh session state per login attempt
+        await req.session.regenerate();
+
+        req.session.set<any>("oidcOrgSlug", orgSlug);
+
+        if (callbackPort) {
+          req.session.set<any>("callbackPort", callbackPort);
+        }
+
+        const oidcStrategy = await server.services.oidc.getOrgAuthStrategy(orgSlug, callbackPort);
+        return (
+          passport.authenticate(oidcStrategy as Strategy, {
+            scope: "profile email openid"
+          }) as any
+        )(req, res);
+      }
+    ],
+    handler: () => {}
+  });
+
+  // callback route after login from IDP
+  server.route({
+    url: "/callback",
+    method: "GET",
+    preValidation: [
+      async (req, res) => {
+        const oidcOrgSlug = req.session.get<any>("oidcOrgSlug");
+        const callbackPort = req.session.get<any>("callbackPort");
+        const oidcStrategy = await server.services.oidc.getOrgAuthStrategy(oidcOrgSlug, callbackPort);
+
+        return (
+          passport.authenticate(oidcStrategy as Strategy, {
+            failureRedirect: "/api/v1/sso/oidc/login/error",
+            session: false,
+            failureMessage: true
+          }) as any
+        )(req, res);
+      }
+    ],
+    handler: async (req, res) => {
+      await req.session.destroy();
+
+      if (req.passportUser.isUserCompleted) {
+        return res.redirect(
+          `${appCfg.SITE_URL}/login/sso?token=${encodeURIComponent(req.passportUser.providerAuthToken)}`
+        );
+      }
+
+      // signup
+      return res.redirect(
+        `${appCfg.SITE_URL}/signup/sso?token=${encodeURIComponent(req.passportUser.providerAuthToken)}`
+      );
+    }
+  });
+
+  server.route({
+    url: "/login/error",
+    method: "GET",
+    handler: async (req, res) => {
+      await req.session.destroy();
+
+      return res.status(500).send({
+        error: "Authentication error",
+        details: req.query
+      });
+    }
+  });
+
+  server.route({
+    url: "/config",
+    method: "GET",
+    config: {
+      rateLimit: readLimit
+    },
+    onRequest: verifyAuth([AuthMode.JWT]),
+    schema: {
+      querystring: z.object({
+        orgSlug: z.string().trim()
+      }),
+      response: {
+        200: OidcConfigsSchema.pick({
+          id: true,
+          issuer: true,
+          authorizationEndpoint: true,
+          jwksUri: true,
+          tokenEndpoint: true,
+          userinfoEndpoint: true,
+          configurationType: true,
+          discoveryURL: true,
+          isActive: true,
+          orgId: true,
+          allowedEmailDomains: true
+        }).extend({
+          clientId: z.string(),
+          clientSecret: z.string()
+        })
+      }
+    },
+    handler: async (req) => {
+      const { orgSlug } = req.query;
+      const oidc = await server.services.oidc.getOidc({
+        orgSlug,
+        type: "external",
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        actorOrgId: req.permission.orgId,
+        actorAuthMethod: req.permission.authMethod
+      });
+
+      return oidc;
+    }
+  });
+
+  server.route({
+    method: "PATCH",
+    url: "/config",
+    config: {
+      rateLimit: writeLimit
+    },
+    onRequest: verifyAuth([AuthMode.JWT]),
+    schema: {
+      body: z
+        .object({
+          allowedEmailDomains: z
+            .string()
+            .trim()
+            .optional()
+            .default("")
+            .transform((data) => {
+              if (data === "") return "";
+              // Trim each ID and join with ', ' to ensure formatting
+              return data
+                .split(",")
+                .map((id) => id.trim())
+                .join(", ");
+            }),
+          discoveryURL: z.string().trim(),
+          configurationType: z.nativeEnum(OIDCConfigurationType),
+          issuer: z.string().trim(),
+          authorizationEndpoint: z.string().trim(),
+          jwksUri: z.string().trim(),
+          tokenEndpoint: z.string().trim(),
+          userinfoEndpoint: z.string().trim(),
+          clientId: z.string().trim(),
+          clientSecret: z.string().trim(),
+          isActive: z.boolean()
+        })
+        .partial()
+        .merge(z.object({ orgSlug: z.string() })),
+      response: {
+        200: OidcConfigsSchema.pick({
+          id: true,
+          issuer: true,
+          authorizationEndpoint: true,
+          configurationType: true,
+          discoveryURL: true,
+          jwksUri: true,
+          tokenEndpoint: true,
+          userinfoEndpoint: true,
+          orgId: true,
+          allowedEmailDomains: true,
+          isActive: true
+        })
+      }
+    },
+    handler: async (req) => {
+      const oidc = await server.services.oidc.updateOidcCfg({
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        actorAuthMethod: req.permission.authMethod,
+        actorOrgId: req.permission.orgId,
+        ...req.body
+      });
+      return oidc;
+    }
+  });
+
+  server.route({
+    method: "POST",
+    url: "/config",
+    config: {
+      rateLimit: writeLimit
+    },
+    onRequest: verifyAuth([AuthMode.JWT]),
+    schema: {
+      body: z
+        .object({
+          allowedEmailDomains: z
+            .string()
+            .trim()
+            .optional()
+            .default("")
+            .transform((data) => {
+              if (data === "") return "";
+              // Trim each ID and join with ', ' to ensure formatting
+              return data
+                .split(",")
+                .map((id) => id.trim())
+                .join(", ");
+            }),
+          configurationType: z.nativeEnum(OIDCConfigurationType),
+          issuer: z.string().trim().optional().default(""),
+          discoveryURL: z.string().trim().optional().default(""),
+          authorizationEndpoint: z.string().trim().optional().default(""),
+          jwksUri: z.string().trim().optional().default(""),
+          tokenEndpoint: z.string().trim().optional().default(""),
+          userinfoEndpoint: z.string().trim().optional().default(""),
+          clientId: z.string().trim(),
+          clientSecret: z.string().trim(),
+          isActive: z.boolean(),
+          orgSlug: z.string().trim()
+        })
+        .superRefine((data, ctx) => {
+          if (data.configurationType === OIDCConfigurationType.CUSTOM) {
+            if (!data.issuer) {
+              ctx.addIssue({
+                path: ["issuer"],
+                message: "Issuer is required",
+                code: z.ZodIssueCode.custom
+              });
+            }
+            if (!data.authorizationEndpoint) {
+              ctx.addIssue({
+                path: ["authorizationEndpoint"],
+                message: "Authorization endpoint is required",
+                code: z.ZodIssueCode.custom
+              });
+            }
+            if (!data.jwksUri) {
+              ctx.addIssue({
+                path: ["jwksUri"],
+                message: "JWKS URI is required",
+                code: z.ZodIssueCode.custom
+              });
+            }
+            if (!data.tokenEndpoint) {
+              ctx.addIssue({
+                path: ["tokenEndpoint"],
+                message: "Token endpoint is required",
+                code: z.ZodIssueCode.custom
+              });
+            }
+            if (!data.userinfoEndpoint) {
+              ctx.addIssue({
+                path: ["userinfoEndpoint"],
+                message: "Userinfo endpoint is required",
+                code: z.ZodIssueCode.custom
+              });
+            }
+          } else {
+            // eslint-disable-next-line no-lonely-if
+            if (!data.discoveryURL) {
+              ctx.addIssue({
+                path: ["discoveryURL"],
+                message: "Discovery URL is required",
+                code: z.ZodIssueCode.custom
+              });
+            }
+          }
+        }),
+      response: {
+        200: OidcConfigsSchema.pick({
+          id: true,
+          issuer: true,
+          authorizationEndpoint: true,
+          configurationType: true,
+          discoveryURL: true,
+          jwksUri: true,
+          tokenEndpoint: true,
+          userinfoEndpoint: true,
+          orgId: true,
+          isActive: true,
+          allowedEmailDomains: true
+        })
+      }
+    },
+
+    handler: async (req) => {
+      const oidc = await server.services.oidc.createOidcCfg({
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        actorAuthMethod: req.permission.authMethod,
+        actorOrgId: req.permission.orgId,
+        ...req.body
+      });
+      return oidc;
+    }
+  });
+};

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

@@ -143,7 +143,8 @@ export const registerProjectRouter = async (server: FastifyZodProvider) => {
         actorAuthMethod: req.permission.authMethod,
         projectId: req.params.workspaceId,
         ...req.query,
-        startDate: req.query.endDate || getLastMidnightDateISO(),
+        endDate: req.query.endDate,
+        startDate: req.query.startDate || getLastMidnightDateISO(),
         auditLogActor: req.query.actor,
         actor: req.permission.type
       });

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

@@ -25,10 +25,10 @@ export const registerSecretApprovalPolicyRouter = async (server: FastifyZodProvi
             .optional()
             .nullable()
             .transform((val) => (val ? removeTrailingSlash(val) : val)),
-          approvers: z.string().array().min(1),
+          approverUserIds: z.string().array().min(1),
           approvals: z.number().min(1).default(1)
         })
-        .refine((data) => data.approvals <= data.approvers.length, {
+        .refine((data) => data.approvals <= data.approverUserIds.length, {
           path: ["approvals"],
           message: "The number of approvals should be lower than the number of approvers."
         }),
@@ -66,7 +66,7 @@ export const registerSecretApprovalPolicyRouter = async (server: FastifyZodProvi
       body: z
         .object({
           name: z.string().optional(),
-          approvers: z.string().array().min(1),
+          approverUserIds: z.string().array().min(1),
           approvals: z.number().min(1).default(1),
           secretPath: z
             .string()
@@ -74,7 +74,7 @@ export const registerSecretApprovalPolicyRouter = async (server: FastifyZodProvi
             .nullable()
             .transform((val) => (val ? removeTrailingSlash(val) : val))
         })
-        .refine((data) => data.approvals <= data.approvers.length, {
+        .refine((data) => data.approvals <= data.approverUserIds.length, {
           path: ["approvals"],
           message: "The number of approvals should be lower than the number of approvers."
         }),
@@ -139,7 +139,15 @@ export const registerSecretApprovalPolicyRouter = async (server: FastifyZodProvi
       }),
       response: {
         200: z.object({
-          approvals: sapPubSchema.merge(z.object({ approvers: z.string().array() })).array()
+          approvals: sapPubSchema
+            .extend({
+              userApprovers: z
+                .object({
+                  userId: z.string()
+                })
+                .array()
+            })
+            .array()
         })
       }
     },
@@ -170,7 +178,11 @@ export const registerSecretApprovalPolicyRouter = async (server: FastifyZodProvi
       }),
       response: {
         200: z.object({
-          policy: sapPubSchema.merge(z.object({ approvers: z.string().array() })).optional()
+          policy: sapPubSchema
+            .extend({
+              userApprovers: z.object({ userId: z.string() }).array()
+            })
+            .optional()
         })
       }
     },

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

@@ -6,7 +6,8 @@ import {
   SecretApprovalRequestsSecretsSchema,
   SecretsSchema,
   SecretTagsSchema,
-  SecretVersionsSchema
+  SecretVersionsSchema,
+  UsersSchema
 } from "@app/db/schemas";
 import { EventType } from "@app/ee/services/audit-log/audit-log-types";
 import { ApprovalStatus, RequestState } from "@app/ee/services/secret-approval-request/secret-approval-request-types";
@@ -14,6 +15,15 @@ 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";
 
+const approvalRequestUser = z.object({ userId: z.string() }).merge(
+  UsersSchema.pick({
+    email: true,
+    firstName: true,
+    lastName: true,
+    username: true
+  })
+);
+
 export const registerSecretApprovalRequestRouter = async (server: FastifyZodProvider) => {
   server.route({
     method: "GET",
@@ -41,9 +51,10 @@ export const registerSecretApprovalRequestRouter = async (server: FastifyZodProv
               approvers: z.string().array(),
               secretPath: z.string().optional().nullable()
             }),
+            committerUser: approvalRequestUser,
             commits: z.object({ op: z.string(), secretId: z.string().nullable().optional() }).array(),
             environment: z.string(),
-            reviewers: z.object({ member: z.string(), status: z.string() }).array(),
+            reviewers: z.object({ userId: z.string(), status: z.string() }).array(),
             approvers: z.string().array()
           }).array()
         })
@@ -195,7 +206,7 @@ export const registerSecretApprovalRequestRouter = async (server: FastifyZodProv
           type: isClosing ? EventType.SECRET_APPROVAL_CLOSED : EventType.SECRET_APPROVAL_REOPENED,
           // eslint-disable-next-line
           metadata: {
-            [isClosing ? ("closedBy" as const) : ("reopenedBy" as const)]: approval.statusChangeBy as string,
+            [isClosing ? ("closedBy" as const) : ("reopenedBy" as const)]: approval.statusChangedByUserId as string,
             secretApprovalRequestId: approval.id,
             secretApprovalRequestSlug: approval.slug
             // eslint-disable-next-line
@@ -216,6 +227,7 @@ export const registerSecretApprovalRequestRouter = async (server: FastifyZodProv
   })
     .array()
     .optional();
+
   server.route({
     method: "GET",
     url: "/:id",
@@ -235,12 +247,13 @@ export const registerSecretApprovalRequestRouter = async (server: FastifyZodProv
                 id: z.string(),
                 name: z.string(),
                 approvals: z.number(),
-                approvers: z.string().array(),
+                approvers: approvalRequestUser.array(),
                 secretPath: z.string().optional().nullable()
               }),
               environment: z.string(),
-              reviewers: z.object({ member: z.string(), status: z.string() }).array(),
-              approvers: z.string().array(),
+              statusChangedByUser: approvalRequestUser.optional(),
+              committerUser: approvalRequestUser,
+              reviewers: approvalRequestUser.extend({ status: z.string() }).array(),
               secretPath: z.string(),
               commits: SecretApprovalRequestsSecretsSchema.omit({ secretBlindIndex: true })
                 .merge(

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

@@ -32,7 +32,7 @@ export const accessApprovalPolicyDALFactory = (db: TDbClient) => {
 
   const findById = async (id: string, tx?: Knex) => {
     try {
-      const doc = await accessApprovalPolicyFindQuery(tx || db, {
+      const doc = await accessApprovalPolicyFindQuery(tx || db.replicaNode(), {
         [`${TableName.AccessApprovalPolicy}.id` as "id"]: id
       });
       const formatedDoc = mergeOneToManyRelation(
@@ -54,7 +54,7 @@ export const accessApprovalPolicyDALFactory = (db: TDbClient) => {
 
   const find = async (filter: TFindFilter<TAccessApprovalPolicies & { projectId: string }>, tx?: Knex) => {
     try {
-      const docs = await accessApprovalPolicyFindQuery(tx || db, filter);
+      const docs = await accessApprovalPolicyFindQuery(tx || db.replicaNode(), filter);
       const formatedDoc = mergeOneToManyRelation(
         docs,
         "id",

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

@@ -14,7 +14,8 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
 
   const findRequestsWithPrivilegeByPolicyIds = async (policyIds: string[]) => {
     try {
-      const docs = await db(TableName.AccessApprovalRequest)
+      const docs = await db
+        .replicaNode()(TableName.AccessApprovalRequest)
         .whereIn(`${TableName.AccessApprovalRequest}.policyId`, policyIds)
 
         .leftJoin(
@@ -170,7 +171,7 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
 
   const findById = async (id: string, tx?: Knex) => {
     try {
-      const sql = findQuery({ [`${TableName.AccessApprovalRequest}.id` as "id"]: id }, tx || db);
+      const sql = findQuery({ [`${TableName.AccessApprovalRequest}.id` as "id"]: id }, tx || db.replicaNode());
       const docs = await sql;
       const formatedDoc = sqlNestRelationships({
         data: docs,
@@ -207,7 +208,8 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
 
   const getCount = async ({ projectId }: { projectId: string }) => {
     try {
-      const accessRequests = await db(TableName.AccessApprovalRequest)
+      const accessRequests = await db
+        .replicaNode()(TableName.AccessApprovalRequest)
         .leftJoin(
           TableName.AccessApprovalPolicy,
           `${TableName.AccessApprovalRequest}.policyId`,

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

@@ -4,6 +4,7 @@ import { TDbClient } from "@app/db";
 import { TableName } from "@app/db/schemas";
 import { DatabaseError } from "@app/lib/errors";
 import { ormify, stripUndefinedInWhere } from "@app/lib/knex";
+import { logger } from "@app/lib/logger";
 
 export type TAuditLogDALFactory = ReturnType<typeof auditLogDALFactory>;
 
@@ -27,7 +28,7 @@ export const auditLogDALFactory = (db: TDbClient) => {
     tx?: Knex
   ) => {
     try {
-      const sqlQuery = (tx || db)(TableName.AuditLog)
+      const sqlQuery = (tx || db.replicaNode())(TableName.AuditLog)
         .where(
           stripUndefinedInWhere({
             projectId,
@@ -55,13 +56,34 @@ export const auditLogDALFactory = (db: TDbClient) => {
 
   // delete all audit log that have expired
   const pruneAuditLog = async (tx?: Knex) => {
-    try {
-      const today = new Date();
-      const docs = await (tx || db)(TableName.AuditLog).where("expiresAt", "<", today).del();
-      return docs;
-    } catch (error) {
-      throw new DatabaseError({ error, name: "PruneAuditLog" });
-    }
+    const AUDIT_LOG_PRUNE_BATCH_SIZE = 10000;
+    const MAX_RETRY_ON_FAILURE = 3;
+
+    const today = new Date();
+    let deletedAuditLogIds: { id: string }[] = [];
+    let numberOfRetryOnFailure = 0;
+
+    do {
+      try {
+        const findExpiredLogSubQuery = (tx || db)(TableName.AuditLog)
+          .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)
+          .del()
+          .returning("id");
+        numberOfRetryOnFailure = 0; // reset
+        // eslint-disable-next-line no-await-in-loop
+        await new Promise((resolve) => {
+          setTimeout(resolve, 100); // time to breathe for db
+        });
+      } catch (error) {
+        numberOfRetryOnFailure += 1;
+        logger.error(error, "Failed to delete audit log on pruning");
+      }
+    } while (deletedAuditLogIds.length > 0 && numberOfRetryOnFailure < MAX_RETRY_ON_FAILURE);
   };
 
   return { ...auditLogOrm, pruneAuditLog, find };

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

@@ -45,18 +45,29 @@ export const auditLogQueueServiceFactory = ({
     const { actor, event, ipAddress, projectId, userAgent, userAgentType } = job.data;
     let { orgId } = job.data;
     const MS_IN_DAY = 24 * 60 * 60 * 1000;
+    let project;
 
     if (!orgId) {
       // it will never be undefined for both org and project id
       // TODO(akhilmhdh): use caching here in dal to avoid db calls
-      const project = await projectDAL.findById(projectId as string);
+      project = await projectDAL.findById(projectId as string);
       orgId = project.orgId;
     }
 
     const plan = await licenseService.getPlan(orgId);
-    const ttl = plan.auditLogsRetentionDays * MS_IN_DAY;
-    // skip inserting if audit log retention is 0 meaning its not supported
-    if (ttl === 0) return;
+    if (plan.auditLogsRetentionDays === 0) {
+      // skip inserting if audit log retention is 0 meaning its not supported
+      return;
+    }
+
+    // For project actions, set TTL to project-level audit log retention config
+    // This condition ensures that the plan's audit log retention days cannot be bypassed
+    const ttlInDays =
+      project?.auditLogsRetentionDays && project.auditLogsRetentionDays < plan.auditLogsRetentionDays
+        ? project.auditLogsRetentionDays
+        : plan.auditLogsRetentionDays;
+
+    const ttl = ttlInDays * MS_IN_DAY;
 
     const auditLog = await auditLogDAL.create({
       actor: actor.type,

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

@@ -1,5 +1,6 @@
 import { TProjectPermission } from "@app/lib/types";
 import { ActorType } from "@app/services/auth/auth-type";
+import { CaStatus } from "@app/services/certificate-authority/certificate-authority-types";
 import { TIdentityTrustedIp } from "@app/services/identity/identity-types";
 
 export type TListProjectAuditLogDTO = {
@@ -64,25 +65,38 @@ export enum EventType {
   ADD_IDENTITY_UNIVERSAL_AUTH = "add-identity-universal-auth",
   UPDATE_IDENTITY_UNIVERSAL_AUTH = "update-identity-universal-auth",
   GET_IDENTITY_UNIVERSAL_AUTH = "get-identity-universal-auth",
+  REVOKE_IDENTITY_UNIVERSAL_AUTH = "revoke-identity-universal-auth",
+  CREATE_TOKEN_IDENTITY_TOKEN_AUTH = "create-token-identity-token-auth",
+  UPDATE_TOKEN_IDENTITY_TOKEN_AUTH = "update-token-identity-token-auth",
+  GET_TOKENS_IDENTITY_TOKEN_AUTH = "get-tokens-identity-token-auth",
+  ADD_IDENTITY_TOKEN_AUTH = "add-identity-token-auth",
+  UPDATE_IDENTITY_TOKEN_AUTH = "update-identity-token-auth",
+  GET_IDENTITY_TOKEN_AUTH = "get-identity-token-auth",
+  REVOKE_IDENTITY_TOKEN_AUTH = "revoke-identity-token-auth",
   LOGIN_IDENTITY_KUBERNETES_AUTH = "login-identity-kubernetes-auth",
   ADD_IDENTITY_KUBERNETES_AUTH = "add-identity-kubernetes-auth",
   UPDATE_IDENTITY_KUBENETES_AUTH = "update-identity-kubernetes-auth",
   GET_IDENTITY_KUBERNETES_AUTH = "get-identity-kubernetes-auth",
+  REVOKE_IDENTITY_KUBERNETES_AUTH = "revoke-identity-kubernetes-auth",
   CREATE_IDENTITY_UNIVERSAL_AUTH_CLIENT_SECRET = "create-identity-universal-auth-client-secret",
   REVOKE_IDENTITY_UNIVERSAL_AUTH_CLIENT_SECRET = "revoke-identity-universal-auth-client-secret",
   GET_IDENTITY_UNIVERSAL_AUTH_CLIENT_SECRETS = "get-identity-universal-auth-client-secret",
+  GET_IDENTITY_UNIVERSAL_AUTH_CLIENT_SECRET_BY_ID = "get-identity-universal-auth-client-secret-by-id",
   LOGIN_IDENTITY_GCP_AUTH = "login-identity-gcp-auth",
   ADD_IDENTITY_GCP_AUTH = "add-identity-gcp-auth",
   UPDATE_IDENTITY_GCP_AUTH = "update-identity-gcp-auth",
+  REVOKE_IDENTITY_GCP_AUTH = "revoke-identity-gcp-auth",
   GET_IDENTITY_GCP_AUTH = "get-identity-gcp-auth",
   LOGIN_IDENTITY_AWS_AUTH = "login-identity-aws-auth",
   ADD_IDENTITY_AWS_AUTH = "add-identity-aws-auth",
   UPDATE_IDENTITY_AWS_AUTH = "update-identity-aws-auth",
+  REVOKE_IDENTITY_AWS_AUTH = "revoke-identity-aws-auth",
   GET_IDENTITY_AWS_AUTH = "get-identity-aws-auth",
   LOGIN_IDENTITY_AZURE_AUTH = "login-identity-azure-auth",
   ADD_IDENTITY_AZURE_AUTH = "add-identity-azure-auth",
   UPDATE_IDENTITY_AZURE_AUTH = "update-identity-azure-auth",
   GET_IDENTITY_AZURE_AUTH = "get-identity-azure-auth",
+  REVOKE_IDENTITY_AZURE_AUTH = "revoke-identity-azure-auth",
   CREATE_ENVIRONMENT = "create-environment",
   UPDATE_ENVIRONMENT = "update-environment",
   DELETE_ENVIRONMENT = "delete-environment",
@@ -104,7 +118,21 @@ export enum EventType {
   SECRET_APPROVAL_MERGED = "secret-approval-merged",
   SECRET_APPROVAL_REQUEST = "secret-approval-request",
   SECRET_APPROVAL_CLOSED = "secret-approval-closed",
-  SECRET_APPROVAL_REOPENED = "secret-approval-reopened"
+  SECRET_APPROVAL_REOPENED = "secret-approval-reopened",
+  CREATE_CA = "create-certificate-authority",
+  GET_CA = "get-certificate-authority",
+  UPDATE_CA = "update-certificate-authority",
+  DELETE_CA = "delete-certificate-authority",
+  GET_CA_CSR = "get-certificate-authority-csr",
+  GET_CA_CERT = "get-certificate-authority-cert",
+  SIGN_INTERMEDIATE = "sign-intermediate",
+  IMPORT_CA_CERT = "import-certificate-authority-cert",
+  GET_CA_CRL = "get-certificate-authority-crl",
+  ISSUE_CERT = "issue-cert",
+  GET_CERT = "get-cert",
+  DELETE_CERT = "delete-cert",
+  REVOKE_CERT = "revoke-cert",
+  GET_CERT_BODY = "get-cert-body"
 }
 
 interface UserActorMetadata {
@@ -419,6 +447,73 @@ interface GetIdentityUniversalAuthEvent {
   };
 }
 
+interface DeleteIdentityUniversalAuthEvent {
+  type: EventType.REVOKE_IDENTITY_UNIVERSAL_AUTH;
+  metadata: {
+    identityId: string;
+  };
+}
+
+interface CreateTokenIdentityTokenAuthEvent {
+  type: EventType.CREATE_TOKEN_IDENTITY_TOKEN_AUTH;
+  metadata: {
+    identityId: string;
+    identityAccessTokenId: string;
+  };
+}
+
+interface UpdateTokenIdentityTokenAuthEvent {
+  type: EventType.UPDATE_TOKEN_IDENTITY_TOKEN_AUTH;
+  metadata: {
+    identityId: string;
+    tokenId: string;
+    name?: string;
+  };
+}
+
+interface GetTokensIdentityTokenAuthEvent {
+  type: EventType.GET_TOKENS_IDENTITY_TOKEN_AUTH;
+  metadata: {
+    identityId: string;
+  };
+}
+
+interface AddIdentityTokenAuthEvent {
+  type: EventType.ADD_IDENTITY_TOKEN_AUTH;
+  metadata: {
+    identityId: string;
+    accessTokenTTL: number;
+    accessTokenMaxTTL: number;
+    accessTokenNumUsesLimit: number;
+    accessTokenTrustedIps: Array<TIdentityTrustedIp>;
+  };
+}
+
+interface UpdateIdentityTokenAuthEvent {
+  type: EventType.UPDATE_IDENTITY_TOKEN_AUTH;
+  metadata: {
+    identityId: string;
+    accessTokenTTL?: number;
+    accessTokenMaxTTL?: number;
+    accessTokenNumUsesLimit?: number;
+    accessTokenTrustedIps?: Array<TIdentityTrustedIp>;
+  };
+}
+
+interface GetIdentityTokenAuthEvent {
+  type: EventType.GET_IDENTITY_TOKEN_AUTH;
+  metadata: {
+    identityId: string;
+  };
+}
+
+interface DeleteIdentityTokenAuthEvent {
+  type: EventType.REVOKE_IDENTITY_TOKEN_AUTH;
+  metadata: {
+    identityId: string;
+  };
+}
+
 interface LoginIdentityKubernetesAuthEvent {
   type: EventType.LOGIN_IDENTITY_KUBERNETES_AUTH;
   metadata: {
@@ -442,6 +537,13 @@ interface AddIdentityKubernetesAuthEvent {
   };
 }
 
+interface DeleteIdentityKubernetesAuthEvent {
+  type: EventType.REVOKE_IDENTITY_KUBERNETES_AUTH;
+  metadata: {
+    identityId: string;
+  };
+}
+
 interface UpdateIdentityKubernetesAuthEvent {
   type: EventType.UPDATE_IDENTITY_KUBENETES_AUTH;
   metadata: {
@@ -478,6 +580,14 @@ interface GetIdentityUniversalAuthClientSecretsEvent {
   };
 }
 
+interface GetIdentityUniversalAuthClientSecretByIdEvent {
+  type: EventType.GET_IDENTITY_UNIVERSAL_AUTH_CLIENT_SECRET_BY_ID;
+  metadata: {
+    identityId: string;
+    clientSecretId: string;
+  };
+}
+
 interface RevokeIdentityUniversalAuthClientSecretEvent {
   type: EventType.REVOKE_IDENTITY_UNIVERSAL_AUTH_CLIENT_SECRET;
   metadata: {
@@ -510,6 +620,13 @@ interface AddIdentityGcpAuthEvent {
   };
 }
 
+interface DeleteIdentityGcpAuthEvent {
+  type: EventType.REVOKE_IDENTITY_GCP_AUTH;
+  metadata: {
+    identityId: string;
+  };
+}
+
 interface UpdateIdentityGcpAuthEvent {
   type: EventType.UPDATE_IDENTITY_GCP_AUTH;
   metadata: {
@@ -555,6 +672,13 @@ interface AddIdentityAwsAuthEvent {
   };
 }
 
+interface DeleteIdentityAwsAuthEvent {
+  type: EventType.REVOKE_IDENTITY_AWS_AUTH;
+  metadata: {
+    identityId: string;
+  };
+}
+
 interface UpdateIdentityAwsAuthEvent {
   type: EventType.UPDATE_IDENTITY_AWS_AUTH;
   metadata: {
@@ -598,6 +722,13 @@ interface AddIdentityAzureAuthEvent {
   };
 }
 
+interface DeleteIdentityAzureAuthEvent {
+  type: EventType.REVOKE_IDENTITY_AZURE_AUTH;
+  metadata: {
+    identityId: string;
+  };
+}
+
 interface UpdateIdentityAzureAuthEvent {
   type: EventType.UPDATE_IDENTITY_AZURE_AUTH;
   metadata: {
@@ -707,7 +838,6 @@ interface CreateWebhookEvent {
     webhookId: string;
     environment: string;
     secretPath: string;
-    webhookUrl: string;
     isDisabled: boolean;
   };
 }
@@ -718,7 +848,6 @@ interface UpdateWebhookStatusEvent {
     webhookId: string;
     environment: string;
     secretPath: string;
-    webhookUrl: string;
     isDisabled: boolean;
   };
 }
@@ -729,7 +858,6 @@ interface DeleteWebhookEvent {
     webhookId: string;
     environment: string;
     secretPath: string;
-    webhookUrl: string;
     isDisabled: boolean;
   };
 }
@@ -843,6 +971,125 @@ interface SecretApprovalRequest {
   };
 }
 
+interface CreateCa {
+  type: EventType.CREATE_CA;
+  metadata: {
+    caId: string;
+    dn: string;
+  };
+}
+
+interface GetCa {
+  type: EventType.GET_CA;
+  metadata: {
+    caId: string;
+    dn: string;
+  };
+}
+
+interface UpdateCa {
+  type: EventType.UPDATE_CA;
+  metadata: {
+    caId: string;
+    dn: string;
+    status: CaStatus;
+  };
+}
+
+interface DeleteCa {
+  type: EventType.DELETE_CA;
+  metadata: {
+    caId: string;
+    dn: string;
+  };
+}
+
+interface GetCaCsr {
+  type: EventType.GET_CA_CSR;
+  metadata: {
+    caId: string;
+    dn: string;
+  };
+}
+
+interface GetCaCert {
+  type: EventType.GET_CA_CERT;
+  metadata: {
+    caId: string;
+    dn: string;
+  };
+}
+
+interface SignIntermediate {
+  type: EventType.SIGN_INTERMEDIATE;
+  metadata: {
+    caId: string;
+    dn: string;
+    serialNumber: string;
+  };
+}
+
+interface ImportCaCert {
+  type: EventType.IMPORT_CA_CERT;
+  metadata: {
+    caId: string;
+    dn: string;
+  };
+}
+
+interface GetCaCrl {
+  type: EventType.GET_CA_CRL;
+  metadata: {
+    caId: string;
+    dn: string;
+  };
+}
+
+interface IssueCert {
+  type: EventType.ISSUE_CERT;
+  metadata: {
+    caId: string;
+    dn: string;
+    serialNumber: string;
+  };
+}
+
+interface GetCert {
+  type: EventType.GET_CERT;
+  metadata: {
+    certId: string;
+    cn: string;
+    serialNumber: string;
+  };
+}
+
+interface DeleteCert {
+  type: EventType.DELETE_CERT;
+  metadata: {
+    certId: string;
+    cn: string;
+    serialNumber: string;
+  };
+}
+
+interface RevokeCert {
+  type: EventType.REVOKE_CERT;
+  metadata: {
+    certId: string;
+    cn: string;
+    serialNumber: string;
+  };
+}
+
+interface GetCertBody {
+  type: EventType.GET_CERT_BODY;
+  metadata: {
+    certId: string;
+    cn: string;
+    serialNumber: string;
+  };
+}
+
 export type Event =
   | GetSecretsEvent
   | GetSecretEvent
@@ -869,24 +1116,37 @@ export type Event =
   | LoginIdentityUniversalAuthEvent
   | AddIdentityUniversalAuthEvent
   | UpdateIdentityUniversalAuthEvent
+  | DeleteIdentityUniversalAuthEvent
   | GetIdentityUniversalAuthEvent
+  | CreateTokenIdentityTokenAuthEvent
+  | UpdateTokenIdentityTokenAuthEvent
+  | GetTokensIdentityTokenAuthEvent
+  | AddIdentityTokenAuthEvent
+  | UpdateIdentityTokenAuthEvent
+  | GetIdentityTokenAuthEvent
+  | DeleteIdentityTokenAuthEvent
   | LoginIdentityKubernetesAuthEvent
+  | DeleteIdentityKubernetesAuthEvent
   | AddIdentityKubernetesAuthEvent
   | UpdateIdentityKubernetesAuthEvent
   | GetIdentityKubernetesAuthEvent
   | CreateIdentityUniversalAuthClientSecretEvent
   | GetIdentityUniversalAuthClientSecretsEvent
+  | GetIdentityUniversalAuthClientSecretByIdEvent
   | RevokeIdentityUniversalAuthClientSecretEvent
   | LoginIdentityGcpAuthEvent
   | AddIdentityGcpAuthEvent
+  | DeleteIdentityGcpAuthEvent
   | UpdateIdentityGcpAuthEvent
   | GetIdentityGcpAuthEvent
   | LoginIdentityAwsAuthEvent
   | AddIdentityAwsAuthEvent
   | UpdateIdentityAwsAuthEvent
   | GetIdentityAwsAuthEvent
+  | DeleteIdentityAwsAuthEvent
   | LoginIdentityAzureAuthEvent
   | AddIdentityAzureAuthEvent
+  | DeleteIdentityAzureAuthEvent
   | UpdateIdentityAzureAuthEvent
   | GetIdentityAzureAuthEvent
   | CreateEnvironmentEvent
@@ -910,4 +1170,18 @@ export type Event =
   | SecretApprovalMerge
   | SecretApprovalClosed
   | SecretApprovalRequest
-  | SecretApprovalReopened;
+  | SecretApprovalReopened
+  | CreateCa
+  | GetCa
+  | UpdateCa
+  | DeleteCa
+  | GetCaCsr
+  | GetCaCert
+  | SignIntermediate
+  | ImportCaCert
+  | GetCaCrl
+  | IssueCert
+  | GetCert
+  | DeleteCert
+  | RevokeCert
+  | GetCertBody;

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

@@ -0,0 +1,10 @@
+import { TDbClient } from "@app/db";
+import { TableName } from "@app/db/schemas";
+import { ormify } from "@app/lib/knex";
+
+export type TCertificateAuthorityCrlDALFactory = ReturnType<typeof certificateAuthorityCrlDALFactory>;
+
+export const certificateAuthorityCrlDALFactory = (db: TDbClient) => {
+  const caCrlOrm = ormify(db, TableName.CertificateAuthorityCrl);
+  return caCrlOrm;
+};

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

@@ -0,0 +1,172 @@
+import { ForbiddenError } from "@casl/ability";
+import * as x509 from "@peculiar/x509";
+
+import { TCertificateAuthorityCrlDALFactory } from "@app/ee/services/certificate-authority-crl/certificate-authority-crl-dal";
+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 { BadRequestError } from "@app/lib/errors";
+import { TCertificateAuthorityDALFactory } from "@app/services/certificate-authority/certificate-authority-dal";
+import { TKmsServiceFactory } from "@app/services/kms/kms-service";
+import { TProjectDALFactory } from "@app/services/project/project-dal";
+import { getProjectKmsCertificateKeyId } from "@app/services/project/project-fns";
+
+import { TGetCrl } from "./certificate-authority-crl-types";
+
+type TCertificateAuthorityCrlServiceFactoryDep = {
+  certificateAuthorityDAL: Pick<TCertificateAuthorityDALFactory, "findById">;
+  certificateAuthorityCrlDAL: Pick<TCertificateAuthorityCrlDALFactory, "findOne">;
+  projectDAL: Pick<TProjectDALFactory, "findOne" | "updateById" | "transaction">;
+  kmsService: Pick<TKmsServiceFactory, "decrypt" | "generateKmsKey">;
+  permissionService: Pick<TPermissionServiceFactory, "getProjectPermission">;
+  licenseService: Pick<TLicenseServiceFactory, "getPlan">;
+};
+
+export type TCertificateAuthorityCrlServiceFactory = ReturnType<typeof certificateAuthorityCrlServiceFactory>;
+
+export const certificateAuthorityCrlServiceFactory = ({
+  certificateAuthorityDAL,
+  certificateAuthorityCrlDAL,
+  projectDAL,
+  kmsService,
+  permissionService,
+  licenseService
+}: TCertificateAuthorityCrlServiceFactoryDep) => {
+  /**
+   * Return the Certificate Revocation List (CRL) for CA with id [caId]
+   */
+  const getCaCrl = async ({ caId, actorId, actorAuthMethod, actor, actorOrgId }: TGetCrl) => {
+    const ca = await certificateAuthorityDAL.findById(caId);
+    if (!ca) throw new BadRequestError({ message: "CA not found" });
+
+    const { permission } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      ca.projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+
+    ForbiddenError.from(permission).throwUnlessCan(
+      ProjectPermissionActions.Read,
+      ProjectPermissionSub.CertificateAuthorities
+    );
+
+    const plan = await licenseService.getPlan(actorOrgId);
+    if (!plan.caCrl)
+      throw new BadRequestError({
+        message:
+          "Failed to get CA certificate revocation list (CRL) due to plan restriction. Upgrade plan to get the CA CRL."
+      });
+
+    const caCrl = await certificateAuthorityCrlDAL.findOne({ caId: ca.id });
+    if (!caCrl) throw new BadRequestError({ message: "CRL not found" });
+
+    const keyId = await getProjectKmsCertificateKeyId({
+      projectId: ca.projectId,
+      projectDAL,
+      kmsService
+    });
+
+    const decryptedCrl = await kmsService.decrypt({
+      kmsId: keyId,
+      cipherTextBlob: caCrl.encryptedCrl
+    });
+
+    const crl = new x509.X509Crl(decryptedCrl);
+
+    const base64crl = crl.toString("base64");
+    const crlPem = `-----BEGIN X509 CRL-----\n${base64crl.match(/.{1,64}/g)?.join("\n")}\n-----END X509 CRL-----`;
+
+    return {
+      crl: crlPem,
+      ca
+    };
+  };
+
+  // const rotateCaCrl = async ({ caId, actorId, actorAuthMethod, actor, actorOrgId }: TRotateCrlDTO) => {
+  //   const ca = await certificateAuthorityDAL.findById(caId);
+  //   if (!ca) throw new BadRequestError({ message: "CA not found" });
+
+  //   const { permission } = await permissionService.getProjectPermission(
+  //     actor,
+  //     actorId,
+  //     ca.projectId,
+  //     actorAuthMethod,
+  //     actorOrgId
+  //   );
+
+  //   ForbiddenError.from(permission).throwUnlessCan(
+  //     ProjectPermissionActions.Read,
+  //     ProjectPermissionSub.CertificateAuthorities
+  //   );
+
+  //   const caSecret = await certificateAuthoritySecretDAL.findOne({ caId: ca.id });
+
+  //   const alg = keyAlgorithmToAlgCfg(ca.keyAlgorithm as CertKeyAlgorithm);
+
+  //   const keyId = await getProjectKmsCertificateKeyId({
+  //     projectId: ca.projectId,
+  //     projectDAL,
+  //     kmsService
+  //   });
+
+  //   const privateKey = await kmsService.decrypt({
+  //     kmsId: keyId,
+  //     cipherTextBlob: caSecret.encryptedPrivateKey
+  //   });
+
+  //   const skObj = crypto.createPrivateKey({ key: privateKey, format: "der", type: "pkcs8" });
+  //   const sk = await crypto.subtle.importKey("pkcs8", skObj.export({ format: "der", type: "pkcs8" }), alg, true, [
+  //     "sign"
+  //   ]);
+
+  //   const revokedCerts = await certificateDAL.find({
+  //     caId: ca.id,
+  //     status: CertStatus.REVOKED
+  //   });
+
+  //   const crl = await x509.X509CrlGenerator.create({
+  //     issuer: ca.dn,
+  //     thisUpdate: new Date(),
+  //     nextUpdate: new Date("2025/12/12"),
+  //     entries: revokedCerts.map((revokedCert) => {
+  //       return {
+  //         serialNumber: revokedCert.serialNumber,
+  //         revocationDate: new Date(revokedCert.revokedAt as Date),
+  //         reason: revokedCert.revocationReason as number,
+  //         invalidity: new Date("2022/01/01"),
+  //         issuer: ca.dn
+  //       };
+  //     }),
+  //     signingAlgorithm: alg,
+  //     signingKey: sk
+  //   });
+
+  //   const { cipherTextBlob: encryptedCrl } = await kmsService.encrypt({
+  //     kmsId: keyId,
+  //     plainText: Buffer.from(new Uint8Array(crl.rawData))
+  //   });
+
+  //   await certificateAuthorityCrlDAL.update(
+  //     {
+  //       caId: ca.id
+  //     },
+  //     {
+  //       encryptedCrl
+  //     }
+  //   );
+
+  //   const base64crl = crl.toString("base64");
+  //   const crlPem = `-----BEGIN X509 CRL-----\n${base64crl.match(/.{1,64}/g)?.join("\n")}\n-----END X509 CRL-----`;
+
+  //   return {
+  //     crl: crlPem
+  //   };
+  // };
+
+  return {
+    getCaCrl
+    // rotateCaCrl
+  };
+};

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

@@ -0,0 +1,5 @@
+import { TProjectPermission } from "@app/lib/types";
+
+export type TGetCrl = {
+  caId: string;
+} & Omit<TProjectPermission, "projectId">;

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

@@ -12,7 +12,10 @@ export const dynamicSecretLeaseDALFactory = (db: TDbClient) => {
 
   const countLeasesForDynamicSecret = async (dynamicSecretId: string, tx?: Knex) => {
     try {
-      const doc = await (tx || db)(TableName.DynamicSecretLease).count("*").where({ dynamicSecretId }).first();
+      const doc = await (tx || db.replicaNode())(TableName.DynamicSecretLease)
+        .count("*")
+        .where({ dynamicSecretId })
+        .first();
       return parseInt(doc || "0", 10);
     } catch (error) {
       throw new DatabaseError({ error, name: "DynamicSecretCountLeases" });
@@ -21,7 +24,7 @@ export const dynamicSecretLeaseDALFactory = (db: TDbClient) => {
 
   const findById = async (id: string, tx?: Knex) => {
     try {
-      const doc = await (tx || db)(TableName.DynamicSecretLease)
+      const doc = await (tx || db.replicaNode())(TableName.DynamicSecretLease)
         .where({ [`${TableName.DynamicSecretLease}.id` as "id"]: id })
         .first()
         .join(

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

@@ -3,7 +3,8 @@ import { z } from "zod";
 export enum SqlProviders {
   Postgres = "postgres",
   MySQL = "mysql2",
-  Oracle = "oracledb"
+  Oracle = "oracledb",
+  MsSQL = "mssql"
 }
 
 export const DynamicSecretSqlDBSchema = z.object({

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

@@ -12,7 +12,7 @@ export const groupDALFactory = (db: TDbClient) => {
 
   const findGroups = async (filter: TFindFilter<TGroups>, { offset, limit, sort, tx }: TFindOpt<TGroups> = {}) => {
     try {
-      const query = (tx || db)(TableName.Groups)
+      const query = (tx || db.replicaNode())(TableName.Groups)
         // eslint-disable-next-line
         .where(buildFindFilter(filter))
         .select(selectAllTableCols(TableName.Groups));
@@ -32,7 +32,7 @@ export const groupDALFactory = (db: TDbClient) => {
 
   const findByOrgId = async (orgId: string, tx?: Knex) => {
     try {
-      const docs = await (tx || db)(TableName.Groups)
+      const docs = await (tx || db.replicaNode())(TableName.Groups)
         .where(`${TableName.Groups}.orgId`, orgId)
         .leftJoin(TableName.OrgRoles, `${TableName.Groups}.roleId`, `${TableName.OrgRoles}.id`)
         .select(selectAllTableCols(TableName.Groups))
@@ -74,11 +74,12 @@ export const groupDALFactory = (db: TDbClient) => {
     username?: string;
   }) => {
     try {
-      let query = db(TableName.OrgMembership)
+      let query = db
+        .replicaNode()(TableName.OrgMembership)
         .where(`${TableName.OrgMembership}.orgId`, orgId)
         .join(TableName.Users, `${TableName.OrgMembership}.userId`, `${TableName.Users}.id`)
-        .leftJoin(TableName.UserGroupMembership, function () {
-          this.on(`${TableName.UserGroupMembership}.userId`, "=", `${TableName.Users}.id`).andOn(
+        .leftJoin(TableName.UserGroupMembership, (bd) => {
+          bd.on(`${TableName.UserGroupMembership}.userId`, "=", `${TableName.Users}.id`).andOn(
             `${TableName.UserGroupMembership}.groupId`,
             "=",
             db.raw("?", [groupId])

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

@@ -18,7 +18,7 @@ export const userGroupMembershipDALFactory = (db: TDbClient) => {
    */
   const filterProjectsByUserMembership = async (userId: string, groupId: string, projectIds: string[], tx?: Knex) => {
     try {
-      const userProjectMemberships: string[] = await (tx || db)(TableName.ProjectMembership)
+      const userProjectMemberships: string[] = await (tx || db.replicaNode())(TableName.ProjectMembership)
         .where(`${TableName.ProjectMembership}.userId`, userId)
         .whereIn(`${TableName.ProjectMembership}.projectId`, projectIds)
         .pluck(`${TableName.ProjectMembership}.projectId`);
@@ -43,7 +43,8 @@ export const userGroupMembershipDALFactory = (db: TDbClient) => {
   // special query
   const findUserGroupMembershipsInProject = async (usernames: string[], projectId: string) => {
     try {
-      const usernameDocs: string[] = await db(TableName.UserGroupMembership)
+      const usernameDocs: string[] = await db
+        .replicaNode()(TableName.UserGroupMembership)
         .join(
           TableName.GroupProjectMembership,
           `${TableName.UserGroupMembership}.groupId`,
@@ -73,7 +74,7 @@ export const userGroupMembershipDALFactory = (db: TDbClient) => {
     try {
       // get list of groups in the project with id [projectId]
       // that that are not the group with id [groupId]
-      const groups: string[] = await (tx || db)(TableName.GroupProjectMembership)
+      const groups: string[] = await (tx || db.replicaNode())(TableName.GroupProjectMembership)
         .where(`${TableName.GroupProjectMembership}.projectId`, projectId)
         .whereNot(`${TableName.GroupProjectMembership}.groupId`, groupId)
         .pluck(`${TableName.GroupProjectMembership}.groupId`);
@@ -83,8 +84,8 @@ export const userGroupMembershipDALFactory = (db: TDbClient) => {
         .where(`${TableName.UserGroupMembership}.groupId`, groupId)
         .where(`${TableName.UserGroupMembership}.isPending`, false)
         .join(TableName.Users, `${TableName.UserGroupMembership}.userId`, `${TableName.Users}.id`)
-        .leftJoin(TableName.ProjectMembership, function () {
-          this.on(`${TableName.Users}.id`, "=", `${TableName.ProjectMembership}.userId`).andOn(
+        .leftJoin(TableName.ProjectMembership, (bd) => {
+          bd.on(`${TableName.Users}.id`, "=", `${TableName.ProjectMembership}.userId`).andOn(
             `${TableName.ProjectMembership}.projectId`,
             "=",
             db.raw("?", [projectId])
@@ -107,9 +108,9 @@ export const userGroupMembershipDALFactory = (db: TDbClient) => {
           db.ref("publicKey").withSchema(TableName.UserEncryptionKey)
         )
         .where({ isGhost: false }) // MAKE SURE USER IS NOT A GHOST USER
-        .whereNotIn(`${TableName.UserGroupMembership}.userId`, function () {
+        .whereNotIn(`${TableName.UserGroupMembership}.userId`, (bd) => {
           // eslint-disable-next-line @typescript-eslint/no-floating-promises
-          this.select(`${TableName.UserGroupMembership}.userId`)
+          bd.select(`${TableName.UserGroupMembership}.userId`)
             .from(TableName.UserGroupMembership)
             .whereIn(`${TableName.UserGroupMembership}.groupId`, groups);
         });

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

@@ -23,6 +23,8 @@ import {
 } from "@app/lib/crypto/encryption";
 import { BadRequestError } from "@app/lib/errors";
 import { AuthMethod, AuthTokenType } from "@app/services/auth/auth-type";
+import { TAuthTokenServiceFactory } from "@app/services/auth-token/auth-token-service";
+import { TokenType } from "@app/services/auth-token/auth-token-types";
 import { 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";
@@ -30,7 +32,9 @@ import { TOrgMembershipDALFactory } from "@app/services/org-membership/org-membe
 import { TProjectDALFactory } from "@app/services/project/project-dal";
 import { TProjectBotDALFactory } from "@app/services/project-bot/project-bot-dal";
 import { TProjectKeyDALFactory } from "@app/services/project-key/project-key-dal";
+import { SmtpTemplates, TSmtpService } from "@app/services/smtp/smtp-service";
 import { getServerCfg } from "@app/services/super-admin/super-admin-service";
+import { LoginMethod } from "@app/services/super-admin/super-admin-types";
 import { TUserDALFactory } from "@app/services/user/user-dal";
 import { normalizeUsername } from "@app/services/user/user-fns";
 import { TUserAliasDALFactory } from "@app/services/user-alias/user-alias-dal";
@@ -50,7 +54,7 @@ import {
   TTestLdapConnectionDTO,
   TUpdateLdapCfgDTO
 } from "./ldap-config-types";
-import { testLDAPConfig } from "./ldap-fns";
+import { searchGroups, testLDAPConfig } from "./ldap-fns";
 import { TLdapGroupMapDALFactory } from "./ldap-group-map-dal";
 
 type TLdapConfigServiceFactoryDep = {
@@ -73,11 +77,19 @@ type TLdapConfigServiceFactoryDep = {
   >;
   userDAL: Pick<
     TUserDALFactory,
-    "create" | "findOne" | "transaction" | "updateById" | "findUserEncKeyByUserIdsBatch" | "find"
+    | "create"
+    | "findOne"
+    | "transaction"
+    | "updateById"
+    | "findUserEncKeyByUserIdsBatch"
+    | "find"
+    | "findUserEncKeyByUserId"
   >;
   userAliasDAL: Pick<TUserAliasDALFactory, "create" | "findOne">;
   permissionService: Pick<TPermissionServiceFactory, "getOrgPermission">;
   licenseService: Pick<TLicenseServiceFactory, "getPlan" | "updateSubscriptionOrgMemberCount">;
+  tokenService: Pick<TAuthTokenServiceFactory, "createTokenForUser">;
+  smtpService: Pick<TSmtpService, "sendMail">;
 };
 
 export type TLdapConfigServiceFactory = ReturnType<typeof ldapConfigServiceFactory>;
@@ -97,7 +109,9 @@ export const ldapConfigServiceFactory = ({
   userDAL,
   userAliasDAL,
   permissionService,
-  licenseService
+  licenseService,
+  tokenService,
+  smtpService
 }: TLdapConfigServiceFactoryDep) => {
   const createLdapCfg = async ({
     actor,
@@ -109,6 +123,7 @@ export const ldapConfigServiceFactory = ({
     url,
     bindDN,
     bindPass,
+    uniqueUserAttribute,
     searchBase,
     searchFilter,
     groupSearchBase,
@@ -187,6 +202,7 @@ export const ldapConfigServiceFactory = ({
       encryptedBindPass,
       bindPassIV,
       bindPassTag,
+      uniqueUserAttribute,
       searchBase,
       searchFilter,
       groupSearchBase,
@@ -209,6 +225,7 @@ export const ldapConfigServiceFactory = ({
     url,
     bindDN,
     bindPass,
+    uniqueUserAttribute,
     searchBase,
     searchFilter,
     groupSearchBase,
@@ -231,7 +248,8 @@ export const ldapConfigServiceFactory = ({
       searchBase,
       searchFilter,
       groupSearchBase,
-      groupSearchFilter
+      groupSearchFilter,
+      uniqueUserAttribute
     };
 
     const orgBot = await orgBotDAL.findOne({ orgId });
@@ -269,7 +287,7 @@ export const ldapConfigServiceFactory = ({
     return ldapConfig;
   };
 
-  const getLdapCfg = async (filter: { orgId: string; isActive?: boolean }) => {
+  const getLdapCfg = async (filter: { orgId: string; isActive?: boolean; id?: string }) => {
     const ldapConfig = await ldapConfigDAL.findOne(filter);
     if (!ldapConfig) throw new BadRequestError({ message: "Failed to find organization LDAP data" });
 
@@ -332,6 +350,7 @@ export const ldapConfigServiceFactory = ({
       url: ldapConfig.url,
       bindDN,
       bindPass,
+      uniqueUserAttribute: ldapConfig.uniqueUserAttribute,
       searchBase: ldapConfig.searchBase,
       searchFilter: ldapConfig.searchFilter,
       groupSearchBase: ldapConfig.groupSearchBase,
@@ -368,6 +387,7 @@ export const ldapConfigServiceFactory = ({
         url: ldapConfig.url,
         bindDN: ldapConfig.bindDN,
         bindCredentials: ldapConfig.bindPass,
+        uniqueUserAttribute: ldapConfig.uniqueUserAttribute,
         searchBase: ldapConfig.searchBase,
         searchFilter: ldapConfig.searchFilter || "(uid={{username}})",
         // searchAttributes: ["uid", "uidNumber", "givenName", "sn", "mail"],
@@ -398,6 +418,13 @@ export const ldapConfigServiceFactory = ({
   }: TLdapLoginDTO) => {
     const appCfg = getConfig();
     const serverCfg = await getServerCfg();
+
+    if (serverCfg.enabledLoginMethods && !serverCfg.enabledLoginMethods.includes(LoginMethod.LDAP)) {
+      throw new BadRequestError({
+        message: "Login with LDAP is disabled by administrator."
+      });
+    }
+
     let userAlias = await userAliasDAL.findOne({
       externalId,
       orgId,
@@ -437,9 +464,24 @@ export const ldapConfigServiceFactory = ({
         }
       });
     } else {
+      const plan = await licenseService.getPlan(orgId);
+      if (plan?.memberLimit && plan.membersUsed >= plan.memberLimit) {
+        // limit imposed on number of members allowed / number of members used exceeds the number of members allowed
+        throw new BadRequestError({
+          message: "Failed to create new member via LDAP due to member limit reached. Upgrade plan to add more members."
+        });
+      }
+
+      if (plan?.identityLimit && plan.identitiesUsed >= plan.identityLimit) {
+        // limit imposed on number of identities allowed / number of identities used exceeds the number of identities allowed
+        throw new BadRequestError({
+          message: "Failed to create new member via LDAP due to member limit reached. Upgrade plan to add more members."
+        });
+      }
+
       userAlias = await userDAL.transaction(async (tx) => {
         let newUser: TUsers | undefined;
-        if (serverCfg.trustSamlEmails) {
+        if (serverCfg.trustLdapEmails) {
           newUser = await userDAL.findOne(
             {
               email,
@@ -488,7 +530,7 @@ export const ldapConfigServiceFactory = ({
         if (!orgMembership) {
           await orgMembershipDAL.create(
             {
-              userId: userAlias.userId,
+              userId: newUser.id,
               inviteEmail: email,
               orgId,
               role: OrgMembershipRole.Member,
@@ -592,12 +634,14 @@ export const ldapConfigServiceFactory = ({
     });
 
     const isUserCompleted = Boolean(user.isAccepted);
+    const userEnc = await userDAL.findUserEncKeyByUserId(user.id);
 
     const providerAuthToken = jwt.sign(
       {
         authTokenType: AuthTokenType.PROVIDER_TOKEN,
         userId: user.id,
         username: user.username,
+        hasExchangedPrivateKey: Boolean(userEnc?.serverEncryptedPrivateKey),
         ...(user.email && { email: user.email, isEmailVerified: user.isEmailVerified }),
         firstName,
         lastName,
@@ -619,6 +663,22 @@ export const ldapConfigServiceFactory = ({
       }
     );
 
+    if (user.email && !user.isEmailVerified) {
+      const token = await tokenService.createTokenForUser({
+        type: TokenType.TOKEN_EMAIL_VERIFICATION,
+        userId: user.id
+      });
+
+      await smtpService.sendMail({
+        template: SmtpTemplates.EmailVerification,
+        subjectLine: "Infisical confirmation code",
+        recipients: [user.email],
+        substitutions: {
+          code: token
+        }
+      });
+    }
+
     return { isUserCompleted, providerAuthToken };
   };
 
@@ -664,11 +724,25 @@ export const ldapConfigServiceFactory = ({
         message: "Failed to create LDAP group map due to plan restriction. Upgrade plan to create LDAP group map."
       });
 
-    const ldapConfig = await ldapConfigDAL.findOne({
-      id: ldapConfigId,
-      orgId
+    const ldapConfig = await getLdapCfg({
+      orgId,
+      id: ldapConfigId
     });
-    if (!ldapConfig) throw new BadRequestError({ message: "Failed to find organization LDAP data" });
+
+    if (!ldapConfig.groupSearchBase) {
+      throw new BadRequestError({
+        message: "Configure a group search base in your LDAP configuration in order to proceed."
+      });
+    }
+
+    const groupSearchFilter = `(cn=${ldapGroupCN})`;
+    const groups = await searchGroups(ldapConfig, groupSearchFilter, ldapConfig.groupSearchBase);
+
+    if (!groups.some((g) => g.cn === ldapGroupCN)) {
+      throw new BadRequestError({
+        message: "Failed to find LDAP Group CN"
+      });
+    }
 
     const group = await groupDAL.findOne({ slug: groupSlug, orgId });
     if (!group) throw new BadRequestError({ message: "Failed to find group" });

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

@@ -7,6 +7,7 @@ export type TLDAPConfig = {
   url: string;
   bindDN: string;
   bindPass: string;
+  uniqueUserAttribute: string;
   searchBase: string;
   groupSearchBase: string;
   groupSearchFilter: string;
@@ -19,6 +20,7 @@ export type TCreateLdapCfgDTO = {
   url: string;
   bindDN: string;
   bindPass: string;
+  uniqueUserAttribute: string;
   searchBase: string;
   searchFilter: string;
   groupSearchBase: string;
@@ -33,6 +35,7 @@ export type TUpdateLdapCfgDTO = {
   url: string;
   bindDN: string;
   bindPass: string;
+  uniqueUserAttribute: string;
   searchBase: string;
   searchFilter: string;
   groupSearchBase: string;

backend/src/ee/services/ldap-config/ldap-group-map-dal.ts

@@ -10,7 +10,8 @@ export const ldapGroupMapDALFactory = (db: TDbClient) => {
 
   const findLdapGroupMapsByLdapConfigId = async (ldapConfigId: string) => {
     try {
-      const docs = await db(TableName.LdapGroupMap)
+      const docs = await db
+        .replicaNode()(TableName.LdapGroupMap)
         .where(`${TableName.LdapGroupMap}.ldapConfigId`, ldapConfigId)
         .join(TableName.Groups, `${TableName.LdapGroupMap}.groupId`, `${TableName.Groups}.id`)
         .select(selectAllTableCols(TableName.LdapGroupMap))

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

@@ -7,6 +7,8 @@ export const getDefaultOnPremFeatures = () => {
     workspacesUsed: 0,
     memberLimit: null,
     membersUsed: 0,
+    identityLimit: null,
+    identitiesUsed: 0,
     environmentLimit: null,
     environmentsUsed: 0,
     secretVersioning: true,
@@ -25,6 +27,7 @@ export const getDefaultOnPremFeatures = () => {
     trial_end: null,
     has_used_trial: true,
     secretApproval: false,
-    secretRotation: true
+    secretRotation: true,
+    caCrl: false
   };
 };

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

@@ -15,6 +15,8 @@ export const getDefaultOnPremFeatures = (): TFeatureSet => ({
   membersUsed: 0,
   environmentLimit: null,
   environmentsUsed: 0,
+  identityLimit: null,
+  identitiesUsed: 0,
   dynamicSecret: false,
   secretVersioning: true,
   pitRecovery: false,
@@ -27,6 +29,7 @@ export const getDefaultOnPremFeatures = (): TFeatureSet => ({
   auditLogStreams: false,
   auditLogStreamLimit: 3,
   samlSSO: false,
+  oidcSSO: false,
   scim: false,
   ldap: false,
   groups: false,
@@ -34,7 +37,8 @@ export const getDefaultOnPremFeatures = (): TFeatureSet => ({
   trial_end: null,
   has_used_trial: true,
   secretApproval: false,
-  secretRotation: true
+  secretRotation: true,
+  caCrl: false
 });
 
 export const setupLicenceRequestWithStore = (baseURL: string, refreshUrl: string, licenseKey: string) => {

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

@@ -9,7 +9,7 @@ export type TLicenseDALFactory = ReturnType<typeof licenseDALFactory>;
 export const licenseDALFactory = (db: TDbClient) => {
   const countOfOrgMembers = async (orgId: string | null, tx?: Knex) => {
     try {
-      const doc = await (tx || db)(TableName.OrgMembership)
+      const doc = await (tx || db.replicaNode())(TableName.OrgMembership)
         .where({ status: OrgMembershipStatus.Accepted })
         .andWhere((bd) => {
           if (orgId) {
@@ -19,11 +19,44 @@ export const licenseDALFactory = (db: TDbClient) => {
         .join(TableName.Users, `${TableName.OrgMembership}.userId`, `${TableName.Users}.id`)
         .where(`${TableName.Users}.isGhost`, false)
         .count();
-      return doc?.[0].count;
+      return Number(doc?.[0].count);
     } catch (error) {
       throw new DatabaseError({ error, name: "Count of Org Members" });
     }
   };
 
-  return { countOfOrgMembers };
+  const countOrgUsersAndIdentities = async (orgId: string | null, tx?: Knex) => {
+    try {
+      // count org users
+      const userDoc = await (tx || db)(TableName.OrgMembership)
+        .where({ status: OrgMembershipStatus.Accepted })
+        .andWhere((bd) => {
+          if (orgId) {
+            void bd.where({ orgId });
+          }
+        })
+        .join(TableName.Users, `${TableName.OrgMembership}.userId`, `${TableName.Users}.id`)
+        .where(`${TableName.Users}.isGhost`, false)
+        .count();
+
+      const userCount = Number(userDoc?.[0].count);
+
+      // count org identities
+      const identityDoc = await (tx || db)(TableName.IdentityOrgMembership)
+        .where((bd) => {
+          if (orgId) {
+            void bd.where({ orgId });
+          }
+        })
+        .count();
+
+      const identityCount = Number(identityDoc?.[0].count);
+
+      return userCount + identityCount;
+    } catch (error) {
+      throw new DatabaseError({ error, name: "Count of Org Users + Identities" });
+    }
+  };
+
+  return { countOfOrgMembers, countOrgUsersAndIdentities };
 };

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

@@ -5,6 +5,7 @@
 // TODO(akhilmhdh): With tony find out the api structure and fill it here
 
 import { ForbiddenError } from "@casl/ability";
+import { Knex } from "knex";
 
 import { TKeyStoreFactory } from "@app/keystore/keystore";
 import { getConfig } from "@app/lib/config/env";
@@ -155,6 +156,7 @@ export const licenseServiceFactory = ({
           LICENSE_SERVER_CLOUD_PLAN_TTL,
           JSON.stringify(currentPlan)
         );
+
         return currentPlan;
       }
     } catch (error) {
@@ -199,21 +201,27 @@ export const licenseServiceFactory = ({
     await licenseServerCloudApi.request.delete(`/api/license-server/v1/customers/${customerId}`);
   };
 
-  const updateSubscriptionOrgMemberCount = async (orgId: string) => {
+  const updateSubscriptionOrgMemberCount = async (orgId: string, tx?: Knex) => {
     if (instanceType === InstanceType.Cloud) {
       const org = await orgDAL.findOrgById(orgId);
       if (!org) throw new BadRequestError({ message: "Org not found" });
 
-      const count = await licenseDAL.countOfOrgMembers(orgId);
+      const quantity = await licenseDAL.countOfOrgMembers(orgId, tx);
+      const quantityIdentities = await licenseDAL.countOrgUsersAndIdentities(orgId, tx);
       if (org?.customerId) {
         await licenseServerCloudApi.request.patch(`/api/license-server/v1/customers/${org.customerId}/cloud-plan`, {
-          quantity: count
+          quantity,
+          quantityIdentities
         });
       }
       await keyStore.deleteItem(FEATURE_CACHE_KEY(orgId));
     } else if (instanceType === InstanceType.EnterpriseOnPrem) {
-      const usedSeats = await licenseDAL.countOfOrgMembers(null);
-      await licenseServerOnPremApi.request.patch(`/api/license/v1/license`, { usedSeats });
+      const usedSeats = await licenseDAL.countOfOrgMembers(null, tx);
+      const usedIdentitySeats = await licenseDAL.countOrgUsersAndIdentities(null, tx);
+      await licenseServerOnPremApi.request.patch(`/api/license/v1/license`, {
+        usedSeats,
+        usedIdentitySeats
+      });
     }
     await refreshPlan(orgId);
   };
@@ -575,6 +583,9 @@ export const licenseServiceFactory = ({
     getInstanceType() {
       return instanceType;
     },
+    get onPremFeatures() {
+      return onPremFeatures;
+    },
     getPlan,
     updateSubscriptionOrgMemberCount,
     refreshPlan,

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

@@ -31,6 +31,8 @@ export type TFeatureSet = {
   dynamicSecret: false;
   memberLimit: null;
   membersUsed: 0;
+  identityLimit: null;
+  identitiesUsed: 0;
   environmentLimit: null;
   environmentsUsed: 0;
   secretVersioning: true;
@@ -44,6 +46,7 @@ export type TFeatureSet = {
   auditLogStreams: false;
   auditLogStreamLimit: 3;
   samlSSO: false;
+  oidcSSO: false;
   scim: false;
   ldap: false;
   groups: false;
@@ -52,6 +55,7 @@ export type TFeatureSet = {
   has_used_trial: true;
   secretApproval: false;
   secretRotation: true;
+  caCrl: false;
 };
 
 export type TOrgPlansTableDTO = {

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

@@ -0,0 +1,11 @@
+import { TDbClient } from "@app/db";
+import { TableName } from "@app/db/schemas";
+import { ormify } from "@app/lib/knex";
+
+export type TOidcConfigDALFactory = ReturnType<typeof oidcConfigDALFactory>;
+
+export const oidcConfigDALFactory = (db: TDbClient) => {
+  const oidcCfgOrm = ormify(db, TableName.OidcConfig);
+
+  return { ...oidcCfgOrm };
+};

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

@@ -0,0 +1,645 @@
+/* eslint-disable @typescript-eslint/no-unsafe-call */
+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 { 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";
+import { TPermissionServiceFactory } from "@app/ee/services/permission/permission-service";
+import { getConfig } from "@app/lib/config/env";
+import {
+  decryptSymmetric,
+  encryptSymmetric,
+  generateAsymmetricKeyPair,
+  generateSymmetricKey,
+  infisicalSymmetricDecrypt,
+  infisicalSymmetricEncypt
+} from "@app/lib/crypto/encryption";
+import { BadRequestError } from "@app/lib/errors";
+import { AuthMethod, AuthTokenType } from "@app/services/auth/auth-type";
+import { TAuthTokenServiceFactory } from "@app/services/auth-token/auth-token-service";
+import { TokenType } from "@app/services/auth-token/auth-token-types";
+import { TOrgBotDALFactory } from "@app/services/org/org-bot-dal";
+import { TOrgDALFactory } from "@app/services/org/org-dal";
+import { TOrgMembershipDALFactory } from "@app/services/org-membership/org-membership-dal";
+import { SmtpTemplates, TSmtpService } from "@app/services/smtp/smtp-service";
+import { getServerCfg } from "@app/services/super-admin/super-admin-service";
+import { LoginMethod } from "@app/services/super-admin/super-admin-types";
+import { TUserDALFactory } from "@app/services/user/user-dal";
+import { normalizeUsername } from "@app/services/user/user-fns";
+import { TUserAliasDALFactory } from "@app/services/user-alias/user-alias-dal";
+import { UserAliasType } from "@app/services/user-alias/user-alias-types";
+
+import { TOidcConfigDALFactory } from "./oidc-config-dal";
+import {
+  OIDCConfigurationType,
+  TCreateOidcCfgDTO,
+  TGetOidcCfgDTO,
+  TOidcLoginDTO,
+  TUpdateOidcCfgDTO
+} from "./oidc-config-types";
+
+type TOidcConfigServiceFactoryDep = {
+  userDAL: Pick<
+    TUserDALFactory,
+    "create" | "findOne" | "transaction" | "updateById" | "findById" | "findUserEncKeyByUserId"
+  >;
+  userAliasDAL: Pick<TUserAliasDALFactory, "create" | "findOne">;
+  orgDAL: Pick<
+    TOrgDALFactory,
+    "createMembership" | "updateMembershipById" | "findMembership" | "findOrgById" | "findOne" | "updateById"
+  >;
+  orgMembershipDAL: Pick<TOrgMembershipDALFactory, "create">;
+  orgBotDAL: Pick<TOrgBotDALFactory, "findOne" | "create" | "transaction">;
+  licenseService: Pick<TLicenseServiceFactory, "getPlan" | "updateSubscriptionOrgMemberCount">;
+  tokenService: Pick<TAuthTokenServiceFactory, "createTokenForUser">;
+  smtpService: Pick<TSmtpService, "sendMail">;
+  permissionService: Pick<TPermissionServiceFactory, "getOrgPermission">;
+  oidcConfigDAL: Pick<TOidcConfigDALFactory, "findOne" | "update" | "create">;
+};
+
+export type TOidcConfigServiceFactory = ReturnType<typeof oidcConfigServiceFactory>;
+
+export const oidcConfigServiceFactory = ({
+  orgDAL,
+  orgMembershipDAL,
+  userDAL,
+  userAliasDAL,
+  licenseService,
+  permissionService,
+  tokenService,
+  orgBotDAL,
+  smtpService,
+  oidcConfigDAL
+}: TOidcConfigServiceFactoryDep) => {
+  const getOidc = async (dto: TGetOidcCfgDTO) => {
+    const org = await orgDAL.findOne({ slug: dto.orgSlug });
+    if (!org) {
+      throw new BadRequestError({
+        message: "Organization not found",
+        name: "OrgNotFound"
+      });
+    }
+    if (dto.type === "external") {
+      const { permission } = await permissionService.getOrgPermission(
+        dto.actor,
+        dto.actorId,
+        org.id,
+        dto.actorAuthMethod,
+        dto.actorOrgId
+      );
+      ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Read, OrgPermissionSubjects.Sso);
+    }
+
+    const oidcCfg = await oidcConfigDAL.findOne({
+      orgId: org.id
+    });
+
+    if (!oidcCfg) {
+      throw new BadRequestError({
+        message: "Failed to find organization OIDC configuration"
+      });
+    }
+
+    // decrypt and return cfg
+    const orgBot = await orgBotDAL.findOne({ orgId: oidcCfg.orgId });
+    if (!orgBot) {
+      throw new BadRequestError({ message: "Org bot not found", name: "OrgBotNotFound" });
+    }
+
+    const key = infisicalSymmetricDecrypt({
+      ciphertext: orgBot.encryptedSymmetricKey,
+      iv: orgBot.symmetricKeyIV,
+      tag: orgBot.symmetricKeyTag,
+      keyEncoding: orgBot.symmetricKeyKeyEncoding as SecretKeyEncoding
+    });
+
+    const { encryptedClientId, clientIdIV, clientIdTag, encryptedClientSecret, clientSecretIV, clientSecretTag } =
+      oidcCfg;
+
+    let clientId = "";
+    if (encryptedClientId && clientIdIV && clientIdTag) {
+      clientId = decryptSymmetric({
+        ciphertext: encryptedClientId,
+        key,
+        tag: clientIdTag,
+        iv: clientIdIV
+      });
+    }
+
+    let clientSecret = "";
+    if (encryptedClientSecret && clientSecretIV && clientSecretTag) {
+      clientSecret = decryptSymmetric({
+        key,
+        tag: clientSecretTag,
+        iv: clientSecretIV,
+        ciphertext: encryptedClientSecret
+      });
+    }
+
+    return {
+      id: oidcCfg.id,
+      issuer: oidcCfg.issuer,
+      authorizationEndpoint: oidcCfg.authorizationEndpoint,
+      configurationType: oidcCfg.configurationType,
+      discoveryURL: oidcCfg.discoveryURL,
+      jwksUri: oidcCfg.jwksUri,
+      tokenEndpoint: oidcCfg.tokenEndpoint,
+      userinfoEndpoint: oidcCfg.userinfoEndpoint,
+      orgId: oidcCfg.orgId,
+      isActive: oidcCfg.isActive,
+      allowedEmailDomains: oidcCfg.allowedEmailDomains,
+      clientId,
+      clientSecret
+    };
+  };
+
+  const oidcLogin = async ({ externalId, email, firstName, lastName, orgId, callbackPort }: TOidcLoginDTO) => {
+    const serverCfg = await getServerCfg();
+
+    if (serverCfg.enabledLoginMethods && !serverCfg.enabledLoginMethods.includes(LoginMethod.OIDC)) {
+      throw new BadRequestError({
+        message: "Login with OIDC is disabled by administrator."
+      });
+    }
+
+    const appCfg = getConfig();
+    const userAlias = await userAliasDAL.findOne({
+      externalId,
+      orgId,
+      aliasType: UserAliasType.OIDC
+    });
+
+    const organization = await orgDAL.findOrgById(orgId);
+    if (!organization) throw new BadRequestError({ message: "Org not found" });
+
+    let user: TUsers;
+    if (userAlias) {
+      user = await userDAL.transaction(async (tx) => {
+        const foundUser = await userDAL.findById(userAlias.userId, tx);
+        const [orgMembership] = await orgDAL.findMembership(
+          {
+            [`${TableName.OrgMembership}.userId` as "userId"]: foundUser.id,
+            [`${TableName.OrgMembership}.orgId` as "id"]: orgId
+          },
+          { tx }
+        );
+        if (!orgMembership) {
+          await orgMembershipDAL.create(
+            {
+              userId: userAlias.userId,
+              inviteEmail: email,
+              orgId,
+              role: OrgMembershipRole.Member,
+              status: foundUser.isAccepted ? OrgMembershipStatus.Accepted : OrgMembershipStatus.Invited // if user is fully completed, then set status to accepted, otherwise set it to invited so we can update it later
+            },
+            tx
+          );
+          // Only update the membership to Accepted if the user account is already completed.
+        } else if (orgMembership.status === OrgMembershipStatus.Invited && foundUser.isAccepted) {
+          await orgDAL.updateMembershipById(
+            orgMembership.id,
+            {
+              status: OrgMembershipStatus.Accepted
+            },
+            tx
+          );
+        }
+
+        return foundUser;
+      });
+    } else {
+      user = await userDAL.transaction(async (tx) => {
+        let newUser: TUsers | undefined;
+
+        if (serverCfg.trustOidcEmails) {
+          newUser = await userDAL.findOne(
+            {
+              email,
+              isEmailVerified: true
+            },
+            tx
+          );
+        }
+
+        if (!newUser) {
+          const uniqueUsername = await normalizeUsername(externalId, userDAL);
+          newUser = await userDAL.create(
+            {
+              email,
+              firstName,
+              isEmailVerified: serverCfg.trustOidcEmails,
+              username: serverCfg.trustOidcEmails ? email : uniqueUsername,
+              lastName,
+              authMethods: [],
+              isGhost: false
+            },
+            tx
+          );
+        }
+
+        await userAliasDAL.create(
+          {
+            userId: newUser.id,
+            aliasType: UserAliasType.OIDC,
+            externalId,
+            emails: email ? [email] : [],
+            orgId
+          },
+          tx
+        );
+
+        const [orgMembership] = await orgDAL.findMembership(
+          {
+            [`${TableName.OrgMembership}.userId` as "userId"]: newUser.id,
+            [`${TableName.OrgMembership}.orgId` as "id"]: orgId
+          },
+          { tx }
+        );
+
+        if (!orgMembership) {
+          await orgMembershipDAL.create(
+            {
+              userId: newUser.id,
+              inviteEmail: email,
+              orgId,
+              role: OrgMembershipRole.Member,
+              status: newUser.isAccepted ? OrgMembershipStatus.Accepted : OrgMembershipStatus.Invited // if user is fully completed, then set status to accepted, otherwise set it to invited so we can update it later
+            },
+            tx
+          );
+          // Only update the membership to Accepted if the user account is already completed.
+        } else if (orgMembership.status === OrgMembershipStatus.Invited && newUser.isAccepted) {
+          await orgDAL.updateMembershipById(
+            orgMembership.id,
+            {
+              status: OrgMembershipStatus.Accepted
+            },
+            tx
+          );
+        }
+
+        return newUser;
+      });
+    }
+
+    await licenseService.updateSubscriptionOrgMemberCount(organization.id);
+
+    const userEnc = await userDAL.findUserEncKeyByUserId(user.id);
+    const isUserCompleted = Boolean(user.isAccepted);
+    const providerAuthToken = jwt.sign(
+      {
+        authTokenType: AuthTokenType.PROVIDER_TOKEN,
+        userId: user.id,
+        username: user.username,
+        ...(user.email && { email: user.email, isEmailVerified: user.isEmailVerified }),
+        firstName,
+        lastName,
+        organizationName: organization.name,
+        organizationId: organization.id,
+        organizationSlug: organization.slug,
+        hasExchangedPrivateKey: Boolean(userEnc?.serverEncryptedPrivateKey),
+        authMethod: AuthMethod.OIDC,
+        authType: UserAliasType.OIDC,
+        isUserCompleted,
+        ...(callbackPort && { callbackPort })
+      },
+      appCfg.AUTH_SECRET,
+      {
+        expiresIn: appCfg.JWT_PROVIDER_AUTH_LIFETIME
+      }
+    );
+
+    if (user.email && !user.isEmailVerified) {
+      const token = await tokenService.createTokenForUser({
+        type: TokenType.TOKEN_EMAIL_VERIFICATION,
+        userId: user.id
+      });
+
+      await smtpService.sendMail({
+        template: SmtpTemplates.EmailVerification,
+        subjectLine: "Infisical confirmation code",
+        recipients: [user.email],
+        substitutions: {
+          code: token
+        }
+      });
+    }
+
+    return { isUserCompleted, providerAuthToken };
+  };
+
+  const updateOidcCfg = async ({
+    orgSlug,
+    allowedEmailDomains,
+    configurationType,
+    discoveryURL,
+    actor,
+    actorOrgId,
+    actorAuthMethod,
+    actorId,
+    issuer,
+    isActive,
+    authorizationEndpoint,
+    jwksUri,
+    tokenEndpoint,
+    userinfoEndpoint,
+    clientId,
+    clientSecret
+  }: TUpdateOidcCfgDTO) => {
+    const org = await orgDAL.findOne({
+      slug: orgSlug
+    });
+
+    if (!org) {
+      throw new BadRequestError({
+        message: "Organization not found"
+      });
+    }
+
+    const plan = await licenseService.getPlan(org.id);
+    if (!plan.oidcSSO)
+      throw new BadRequestError({
+        message:
+          "Failed to update OIDC SSO configuration due to plan restriction. Upgrade plan to update SSO configuration."
+      });
+
+    const { permission } = await permissionService.getOrgPermission(
+      actor,
+      actorId,
+      org.id,
+      actorAuthMethod,
+      actorOrgId
+    );
+    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Edit, OrgPermissionSubjects.Sso);
+
+    const orgBot = await orgBotDAL.findOne({ orgId: org.id });
+    if (!orgBot) throw new BadRequestError({ message: "Org bot not found", name: "OrgBotNotFound" });
+    const key = infisicalSymmetricDecrypt({
+      ciphertext: orgBot.encryptedSymmetricKey,
+      iv: orgBot.symmetricKeyIV,
+      tag: orgBot.symmetricKeyTag,
+      keyEncoding: orgBot.symmetricKeyKeyEncoding as SecretKeyEncoding
+    });
+
+    const updateQuery: TOidcConfigsUpdate = {
+      allowedEmailDomains,
+      configurationType,
+      discoveryURL,
+      issuer,
+      authorizationEndpoint,
+      tokenEndpoint,
+      userinfoEndpoint,
+      jwksUri,
+      isActive
+    };
+
+    if (clientId !== undefined) {
+      const { ciphertext: encryptedClientId, iv: clientIdIV, tag: clientIdTag } = encryptSymmetric(clientId, key);
+      updateQuery.encryptedClientId = encryptedClientId;
+      updateQuery.clientIdIV = clientIdIV;
+      updateQuery.clientIdTag = clientIdTag;
+    }
+
+    if (clientSecret !== undefined) {
+      const {
+        ciphertext: encryptedClientSecret,
+        iv: clientSecretIV,
+        tag: clientSecretTag
+      } = encryptSymmetric(clientSecret, key);
+
+      updateQuery.encryptedClientSecret = encryptedClientSecret;
+      updateQuery.clientSecretIV = clientSecretIV;
+      updateQuery.clientSecretTag = clientSecretTag;
+    }
+
+    const [ssoConfig] = await oidcConfigDAL.update({ orgId: org.id }, updateQuery);
+    return ssoConfig;
+  };
+
+  const createOidcCfg = async ({
+    orgSlug,
+    allowedEmailDomains,
+    configurationType,
+    discoveryURL,
+    actor,
+    actorOrgId,
+    actorAuthMethod,
+    actorId,
+    issuer,
+    isActive,
+    authorizationEndpoint,
+    jwksUri,
+    tokenEndpoint,
+    userinfoEndpoint,
+    clientId,
+    clientSecret
+  }: TCreateOidcCfgDTO) => {
+    const org = await orgDAL.findOne({
+      slug: orgSlug
+    });
+    if (!org) {
+      throw new BadRequestError({
+        message: "Organization not found"
+      });
+    }
+
+    const plan = await licenseService.getPlan(org.id);
+    if (!plan.oidcSSO)
+      throw new BadRequestError({
+        message:
+          "Failed to create OIDC SSO configuration due to plan restriction. Upgrade plan to update SSO configuration."
+      });
+
+    const { permission } = await permissionService.getOrgPermission(
+      actor,
+      actorId,
+      org.id,
+      actorAuthMethod,
+      actorOrgId
+    );
+    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Create, OrgPermissionSubjects.Sso);
+
+    const orgBot = await orgBotDAL.transaction(async (tx) => {
+      const doc = await orgBotDAL.findOne({ orgId: org.id }, tx);
+      if (doc) return doc;
+
+      const { privateKey, publicKey } = generateAsymmetricKeyPair();
+      const key = generateSymmetricKey();
+      const {
+        ciphertext: encryptedPrivateKey,
+        iv: privateKeyIV,
+        tag: privateKeyTag,
+        encoding: privateKeyKeyEncoding,
+        algorithm: privateKeyAlgorithm
+      } = infisicalSymmetricEncypt(privateKey);
+      const {
+        ciphertext: encryptedSymmetricKey,
+        iv: symmetricKeyIV,
+        tag: symmetricKeyTag,
+        encoding: symmetricKeyKeyEncoding,
+        algorithm: symmetricKeyAlgorithm
+      } = infisicalSymmetricEncypt(key);
+
+      return orgBotDAL.create(
+        {
+          name: "Infisical org bot",
+          publicKey,
+          privateKeyIV,
+          encryptedPrivateKey,
+          symmetricKeyIV,
+          symmetricKeyTag,
+          encryptedSymmetricKey,
+          symmetricKeyAlgorithm,
+          orgId: org.id,
+          privateKeyTag,
+          privateKeyAlgorithm,
+          privateKeyKeyEncoding,
+          symmetricKeyKeyEncoding
+        },
+        tx
+      );
+    });
+
+    const key = infisicalSymmetricDecrypt({
+      ciphertext: orgBot.encryptedSymmetricKey,
+      iv: orgBot.symmetricKeyIV,
+      tag: orgBot.symmetricKeyTag,
+      keyEncoding: orgBot.symmetricKeyKeyEncoding as SecretKeyEncoding
+    });
+
+    const { ciphertext: encryptedClientId, iv: clientIdIV, tag: clientIdTag } = encryptSymmetric(clientId, key);
+    const {
+      ciphertext: encryptedClientSecret,
+      iv: clientSecretIV,
+      tag: clientSecretTag
+    } = encryptSymmetric(clientSecret, key);
+
+    const oidcCfg = await oidcConfigDAL.create({
+      issuer,
+      isActive,
+      configurationType,
+      discoveryURL,
+      authorizationEndpoint,
+      allowedEmailDomains,
+      jwksUri,
+      tokenEndpoint,
+      userinfoEndpoint,
+      orgId: org.id,
+      encryptedClientId,
+      clientIdIV,
+      clientIdTag,
+      encryptedClientSecret,
+      clientSecretIV,
+      clientSecretTag
+    });
+
+    return oidcCfg;
+  };
+
+  const getOrgAuthStrategy = async (orgSlug: string, callbackPort?: string) => {
+    const appCfg = getConfig();
+
+    const org = await orgDAL.findOne({
+      slug: orgSlug
+    });
+
+    if (!org) {
+      throw new BadRequestError({
+        message: "Organization not found."
+      });
+    }
+
+    const oidcCfg = await getOidc({
+      type: "internal",
+      orgSlug
+    });
+
+    if (!oidcCfg || !oidcCfg.isActive) {
+      throw new BadRequestError({
+        message: "Failed to authenticate with OIDC SSO"
+      });
+    }
+
+    let issuer: Issuer;
+    if (oidcCfg.configurationType === OIDCConfigurationType.DISCOVERY_URL) {
+      if (!oidcCfg.discoveryURL) {
+        throw new BadRequestError({
+          message: "OIDC not configured correctly"
+        });
+      }
+      issuer = await Issuer.discover(oidcCfg.discoveryURL);
+    } else {
+      if (
+        !oidcCfg.issuer ||
+        !oidcCfg.authorizationEndpoint ||
+        !oidcCfg.jwksUri ||
+        !oidcCfg.tokenEndpoint ||
+        !oidcCfg.userinfoEndpoint
+      ) {
+        throw new BadRequestError({
+          message: "OIDC not configured correctly"
+        });
+      }
+      issuer = new OpenIdIssuer({
+        issuer: oidcCfg.issuer,
+        authorization_endpoint: oidcCfg.authorizationEndpoint,
+        jwks_uri: oidcCfg.jwksUri,
+        token_endpoint: oidcCfg.tokenEndpoint,
+        userinfo_endpoint: oidcCfg.userinfoEndpoint
+      });
+    }
+
+    const client = new issuer.Client({
+      client_id: oidcCfg.clientId,
+      client_secret: oidcCfg.clientSecret,
+      redirect_uris: [`${appCfg.SITE_URL}/api/v1/sso/oidc/callback`]
+    });
+
+    const strategy = new OpenIdStrategy(
+      {
+        client,
+        passReqToCallback: true
+      },
+      // eslint-disable-next-line @typescript-eslint/no-explicit-any
+      (_req: any, tokenSet: TokenSet, cb: any) => {
+        const claims = tokenSet.claims();
+        if (!claims.email || !claims.given_name) {
+          throw new BadRequestError({
+            message: "Invalid request. Missing email or first name"
+          });
+        }
+
+        if (oidcCfg.allowedEmailDomains) {
+          const allowedDomains = oidcCfg.allowedEmailDomains.split(", ");
+          if (!allowedDomains.includes(claims.email.split("@")[1])) {
+            throw new BadRequestError({
+              message: "Email not allowed."
+            });
+          }
+        }
+
+        oidcLogin({
+          email: claims.email,
+          externalId: claims.sub,
+          firstName: claims.given_name ?? "",
+          lastName: claims.family_name ?? "",
+          orgId: org.id,
+          callbackPort
+        })
+          .then(({ isUserCompleted, providerAuthToken }) => {
+            cb(null, { isUserCompleted, providerAuthToken });
+          })
+          .catch((error) => {
+            cb(error);
+          });
+      }
+    );
+
+    return strategy;
+  };
+
+  return { oidcLogin, getOrgAuthStrategy, getOidc, updateOidcCfg, createOidcCfg };
+};

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

@@ -0,0 +1,56 @@
+import { TGenericPermission } from "@app/lib/types";
+
+export enum OIDCConfigurationType {
+  CUSTOM = "custom",
+  DISCOVERY_URL = "discoveryURL"
+}
+
+export type TOidcLoginDTO = {
+  externalId: string;
+  email: string;
+  firstName: string;
+  lastName?: string;
+  orgId: string;
+  callbackPort?: string;
+};
+
+export type TGetOidcCfgDTO =
+  | ({
+      type: "external";
+      orgSlug: string;
+    } & TGenericPermission)
+  | {
+      type: "internal";
+      orgSlug: string;
+    };
+
+export type TCreateOidcCfgDTO = {
+  issuer?: string;
+  authorizationEndpoint?: string;
+  discoveryURL?: string;
+  configurationType: OIDCConfigurationType;
+  allowedEmailDomains?: string;
+  jwksUri?: string;
+  tokenEndpoint?: string;
+  userinfoEndpoint?: string;
+  clientId: string;
+  clientSecret: string;
+  isActive: boolean;
+  orgSlug: string;
+} & TGenericPermission;
+
+export type TUpdateOidcCfgDTO = Partial<{
+  issuer: string;
+  authorizationEndpoint: string;
+  allowedEmailDomains: string;
+  discoveryURL: string;
+  jwksUri: string;
+  configurationType: OIDCConfigurationType;
+  tokenEndpoint: string;
+  userinfoEndpoint: string;
+  clientId: string;
+  clientSecret: string;
+  isActive: boolean;
+  orgSlug: string;
+}> &
+  TGenericPermission;

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

@@ -116,7 +116,6 @@ const buildMemberPermission = () => {
   can(OrgPermissionActions.Read, OrgPermissionSubjects.Role);
   can(OrgPermissionActions.Read, OrgPermissionSubjects.Settings);
   can(OrgPermissionActions.Read, OrgPermissionSubjects.Billing);
-  can(OrgPermissionActions.Read, OrgPermissionSubjects.Sso);
   can(OrgPermissionActions.Read, OrgPermissionSubjects.IncidentAccount);
 
   can(OrgPermissionActions.Read, OrgPermissionSubjects.SecretScanning);

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

@@ -10,7 +10,8 @@ export type TPermissionDALFactory = ReturnType<typeof permissionDALFactory>;
 export const permissionDALFactory = (db: TDbClient) => {
   const getOrgPermission = async (userId: string, orgId: string) => {
     try {
-      const membership = await db(TableName.OrgMembership)
+      const membership = await db
+        .replicaNode()(TableName.OrgMembership)
         .leftJoin(TableName.OrgRoles, `${TableName.OrgMembership}.roleId`, `${TableName.OrgRoles}.id`)
         .join(TableName.Organization, `${TableName.OrgMembership}.orgId`, `${TableName.Organization}.id`)
         .where("userId", userId)
@@ -28,7 +29,8 @@ export const permissionDALFactory = (db: TDbClient) => {
 
   const getOrgIdentityPermission = async (identityId: string, orgId: string) => {
     try {
-      const membership = await db(TableName.IdentityOrgMembership)
+      const membership = await db
+        .replicaNode()(TableName.IdentityOrgMembership)
         .leftJoin(TableName.OrgRoles, `${TableName.IdentityOrgMembership}.roleId`, `${TableName.OrgRoles}.id`)
         .join(TableName.Organization, `${TableName.IdentityOrgMembership}.orgId`, `${TableName.Organization}.id`)
         .where("identityId", identityId)
@@ -45,11 +47,13 @@ export const permissionDALFactory = (db: TDbClient) => {
 
   const getProjectPermission = async (userId: string, projectId: string) => {
     try {
-      const groups: string[] = await db(TableName.GroupProjectMembership)
+      const groups: string[] = await db
+        .replicaNode()(TableName.GroupProjectMembership)
         .where(`${TableName.GroupProjectMembership}.projectId`, projectId)
         .pluck(`${TableName.GroupProjectMembership}.groupId`);
 
-      const groupDocs = await db(TableName.UserGroupMembership)
+      const groupDocs = await db
+        .replicaNode()(TableName.UserGroupMembership)
         .where(`${TableName.UserGroupMembership}.userId`, userId)
         .whereIn(`${TableName.UserGroupMembership}.groupId`, groups)
         .join(
@@ -231,7 +235,8 @@ export const permissionDALFactory = (db: TDbClient) => {
 
   const getProjectIdentityPermission = async (identityId: string, projectId: string) => {
     try {
-      const docs = await db(TableName.IdentityProjectMembership)
+      const docs = await db
+        .replicaNode()(TableName.IdentityProjectMembership)
         .join(
           TableName.IdentityProjectMembershipRole,
           `${TableName.IdentityProjectMembershipRole}.projectMembershipId`,

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

@@ -26,7 +26,9 @@ export enum ProjectPermissionSub {
   SecretRollback = "secret-rollback",
   SecretApproval = "secret-approval",
   SecretRotation = "secret-rotation",
-  Identity = "identity"
+  Identity = "identity",
+  CertificateAuthorities = "certificate-authorities",
+  Certificates = "certificates"
 }
 
 type SubjectFields = {
@@ -53,6 +55,8 @@ export type ProjectPermissionSet =
   | [ProjectPermissionActions, ProjectPermissionSub.SecretApproval]
   | [ProjectPermissionActions, ProjectPermissionSub.SecretRotation]
   | [ProjectPermissionActions, ProjectPermissionSub.Identity]
+  | [ProjectPermissionActions, ProjectPermissionSub.CertificateAuthorities]
+  | [ProjectPermissionActions, ProjectPermissionSub.Certificates]
   | [ProjectPermissionActions.Delete, ProjectPermissionSub.Project]
   | [ProjectPermissionActions.Edit, ProjectPermissionSub.Project]
   | [ProjectPermissionActions.Read, ProjectPermissionSub.SecretRollback]
@@ -139,6 +143,17 @@ const buildAdminPermissionRules = () => {
   can(ProjectPermissionActions.Edit, ProjectPermissionSub.IpAllowList);
   can(ProjectPermissionActions.Delete, ProjectPermissionSub.IpAllowList);
 
+  // double check if all CRUD are needed for CA and Certificates
+  can(ProjectPermissionActions.Read, ProjectPermissionSub.CertificateAuthorities);
+  can(ProjectPermissionActions.Create, ProjectPermissionSub.CertificateAuthorities);
+  can(ProjectPermissionActions.Edit, ProjectPermissionSub.CertificateAuthorities);
+  can(ProjectPermissionActions.Delete, ProjectPermissionSub.CertificateAuthorities);
+
+  can(ProjectPermissionActions.Read, ProjectPermissionSub.Certificates);
+  can(ProjectPermissionActions.Create, ProjectPermissionSub.Certificates);
+  can(ProjectPermissionActions.Edit, ProjectPermissionSub.Certificates);
+  can(ProjectPermissionActions.Delete, ProjectPermissionSub.Certificates);
+
   can(ProjectPermissionActions.Edit, ProjectPermissionSub.Project);
   can(ProjectPermissionActions.Delete, ProjectPermissionSub.Project);
 
@@ -205,6 +220,14 @@ const buildMemberPermissionRules = () => {
   can(ProjectPermissionActions.Read, ProjectPermissionSub.AuditLogs);
   can(ProjectPermissionActions.Read, ProjectPermissionSub.IpAllowList);
 
+  // double check if all CRUD are needed for CA and Certificates
+  can(ProjectPermissionActions.Read, ProjectPermissionSub.CertificateAuthorities);
+
+  can(ProjectPermissionActions.Read, ProjectPermissionSub.Certificates);
+  can(ProjectPermissionActions.Create, ProjectPermissionSub.Certificates);
+  can(ProjectPermissionActions.Edit, ProjectPermissionSub.Certificates);
+  can(ProjectPermissionActions.Delete, ProjectPermissionSub.Certificates);
+
   return rules;
 };
 
@@ -229,6 +252,8 @@ const buildViewerPermissionRules = () => {
   can(ProjectPermissionActions.Read, ProjectPermissionSub.Tags);
   can(ProjectPermissionActions.Read, ProjectPermissionSub.AuditLogs);
   can(ProjectPermissionActions.Read, ProjectPermissionSub.IpAllowList);
+  can(ProjectPermissionActions.Read, ProjectPermissionSub.CertificateAuthorities);
+  can(ProjectPermissionActions.Read, ProjectPermissionSub.Certificates);
 
   return rules;
 };

backend/src/ee/services/rate-limit/rate-limit-service.ts

@@ -2,6 +2,7 @@ import { CronJob } from "cron";
 
 import { logger } from "@app/lib/logger";
 
+import { TLicenseServiceFactory } from "../license/license-service";
 import { TRateLimitDALFactory } from "./rate-limit-dal";
 import { TRateLimit, TRateLimitUpdateDTO } from "./rate-limit-types";
 
@@ -24,11 +25,12 @@ export const getRateLimiterConfig = () => {
 
 type TRateLimitServiceFactoryDep = {
   rateLimitDAL: TRateLimitDALFactory;
+  licenseService: Pick<TLicenseServiceFactory, "onPremFeatures">;
 };
 
 export type TRateLimitServiceFactory = ReturnType<typeof rateLimitServiceFactory>;
 
-export const rateLimitServiceFactory = ({ rateLimitDAL }: TRateLimitServiceFactoryDep) => {
+export const rateLimitServiceFactory = ({ rateLimitDAL, licenseService }: TRateLimitServiceFactoryDep) => {
   const DEFAULT_RATE_LIMIT_CONFIG_ID = "00000000-0000-0000-0000-000000000000";
 
   const getRateLimits = async (): Promise<TRateLimit | undefined> => {
@@ -78,7 +80,16 @@ export const rateLimitServiceFactory = ({ rateLimitDAL }: TRateLimitServiceFacto
     }
   };
 
-  const initializeBackgroundSync = () => {
+  const initializeBackgroundSync = async () => {
+    if (!licenseService.onPremFeatures.customRateLimits) {
+      logger.info("Current license does not support custom rate limit configuration");
+      return;
+    }
+
+    logger.info("Setting up background sync process for rate limits");
+    // initial sync upon startup
+    await syncRateLimitConfiguration();
+
     // sync rate limits configuration every 10 minutes
     const job = new CronJob("*/10 * * * *", syncRateLimitConfiguration);
     job.start();

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

@@ -10,7 +10,8 @@ export const samlConfigDALFactory = (db: TDbClient) => {
 
   const findEnforceableSamlCfg = async (orgId: string) => {
     try {
-      const samlCfg = await db(TableName.SamlConfig)
+      const samlCfg = await db
+        .replicaNode()(TableName.SamlConfig)
         .where({
           orgId,
           isActive: true

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

@@ -28,6 +28,7 @@ import { TOrgDALFactory } from "@app/services/org/org-dal";
 import { TOrgMembershipDALFactory } from "@app/services/org-membership/org-membership-dal";
 import { SmtpTemplates, TSmtpService } from "@app/services/smtp/smtp-service";
 import { getServerCfg } from "@app/services/super-admin/super-admin-service";
+import { LoginMethod } from "@app/services/super-admin/super-admin-types";
 import { TUserDALFactory } from "@app/services/user/user-dal";
 import { normalizeUsername } from "@app/services/user/user-fns";
 import { TUserAliasDALFactory } from "@app/services/user-alias/user-alias-dal";
@@ -41,7 +42,10 @@ import { TCreateSamlCfgDTO, TGetSamlCfgDTO, TSamlLoginDTO, TUpdateSamlCfgDTO } f
 
 type TSamlConfigServiceFactoryDep = {
   samlConfigDAL: Pick<TSamlConfigDALFactory, "create" | "findOne" | "update" | "findById">;
-  userDAL: Pick<TUserDALFactory, "create" | "findOne" | "transaction" | "updateById" | "findById">;
+  userDAL: Pick<
+    TUserDALFactory,
+    "create" | "findOne" | "transaction" | "updateById" | "findById" | "findUserEncKeyByUserId"
+  >;
   userAliasDAL: Pick<TUserAliasDALFactory, "create" | "findOne">;
   orgDAL: Pick<
     TOrgDALFactory,
@@ -332,6 +336,13 @@ export const samlConfigServiceFactory = ({
   }: TSamlLoginDTO) => {
     const appCfg = getConfig();
     const serverCfg = await getServerCfg();
+
+    if (serverCfg.enabledLoginMethods && !serverCfg.enabledLoginMethods.includes(LoginMethod.SAML)) {
+      throw new BadRequestError({
+        message: "Login with SAML is disabled by administrator."
+      });
+    }
+
     const userAlias = await userAliasDAL.findOne({
       externalId,
       orgId,
@@ -377,6 +388,21 @@ export const samlConfigServiceFactory = ({
         return foundUser;
       });
     } else {
+      const plan = await licenseService.getPlan(orgId);
+      if (plan?.memberLimit && plan.membersUsed >= plan.memberLimit) {
+        // limit imposed on number of members allowed / number of members used exceeds the number of members allowed
+        throw new BadRequestError({
+          message: "Failed to create new member via SAML due to member limit reached. Upgrade plan to add more members."
+        });
+      }
+
+      if (plan?.identityLimit && plan.identitiesUsed >= plan.identityLimit) {
+        // limit imposed on number of identities allowed / number of identities used exceeds the number of identities allowed
+        throw new BadRequestError({
+          message: "Failed to create new member via SAML due to member limit reached. Upgrade plan to add more members."
+        });
+      }
+
       user = await userDAL.transaction(async (tx) => {
         let newUser: TUsers | undefined;
         if (serverCfg.trustSamlEmails) {
@@ -452,6 +478,7 @@ export const samlConfigServiceFactory = ({
     await licenseService.updateSubscriptionOrgMemberCount(organization.id);
 
     const isUserCompleted = Boolean(user.isAccepted);
+    const userEnc = await userDAL.findUserEncKeyByUserId(user.id);
     const providerAuthToken = jwt.sign(
       {
         authTokenType: AuthTokenType.PROVIDER_TOKEN,
@@ -464,6 +491,7 @@ export const samlConfigServiceFactory = ({
         organizationId: organization.id,
         organizationSlug: organization.slug,
         authMethod: authProvider,
+        hasExchangedPrivateKey: Boolean(userEnc?.serverEncryptedPrivateKey),
         authType: UserAliasType.SAML,
         isUserCompleted,
         ...(relayState

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

@@ -1,49 +1,59 @@
 import { Knex } from "knex";
 
 import { TDbClient } from "@app/db";
-import { TableName, TSecretApprovalPolicies } from "@app/db/schemas";
+import { SecretApprovalPoliciesSchema, TableName, TSecretApprovalPolicies } from "@app/db/schemas";
 import { DatabaseError } from "@app/lib/errors";
-import { buildFindFilter, mergeOneToManyRelation, ormify, selectAllTableCols, TFindFilter } from "@app/lib/knex";
+import { buildFindFilter, ormify, selectAllTableCols, sqlNestRelationships, TFindFilter } from "@app/lib/knex";
 
 export type TSecretApprovalPolicyDALFactory = ReturnType<typeof secretApprovalPolicyDALFactory>;
 
 export const secretApprovalPolicyDALFactory = (db: TDbClient) => {
   const secretApprovalPolicyOrm = ormify(db, TableName.SecretApprovalPolicy);
 
-  const sapFindQuery = (tx: Knex, filter: TFindFilter<TSecretApprovalPolicies>) =>
+  const secretApprovalPolicyFindQuery = (tx: Knex, filter: TFindFilter<TSecretApprovalPolicies>) =>
     tx(TableName.SecretApprovalPolicy)
       // eslint-disable-next-line
       .where(buildFindFilter(filter))
       .join(TableName.Environment, `${TableName.SecretApprovalPolicy}.envId`, `${TableName.Environment}.id`)
-      .join(
+      .leftJoin(
         TableName.SecretApprovalPolicyApprover,
         `${TableName.SecretApprovalPolicy}.id`,
         `${TableName.SecretApprovalPolicyApprover}.policyId`
       )
-      .select(tx.ref("approverId").withSchema(TableName.SecretApprovalPolicyApprover))
-      .select(tx.ref("name").withSchema(TableName.Environment).as("envName"))
-      .select(tx.ref("slug").withSchema(TableName.Environment).as("envSlug"))
-      .select(tx.ref("id").withSchema(TableName.Environment).as("envId"))
-      .select(tx.ref("projectId").withSchema(TableName.Environment))
+      .select(tx.ref("approverUserId").withSchema(TableName.SecretApprovalPolicyApprover))
+      .select(
+        tx.ref("name").withSchema(TableName.Environment).as("envName"),
+        tx.ref("slug").withSchema(TableName.Environment).as("envSlug"),
+        tx.ref("id").withSchema(TableName.Environment).as("envId"),
+        tx.ref("projectId").withSchema(TableName.Environment)
+      )
       .select(selectAllTableCols(TableName.SecretApprovalPolicy))
       .orderBy("createdAt", "asc");
 
   const findById = async (id: string, tx?: Knex) => {
     try {
-      const doc = await sapFindQuery(tx || db, {
+      const doc = await secretApprovalPolicyFindQuery(tx || db.replicaNode(), {
         [`${TableName.SecretApprovalPolicy}.id` as "id"]: id
       });
-      const formatedDoc = mergeOneToManyRelation(
-        doc,
-        "id",
-        ({ approverId, envId, envName: name, envSlug: slug, ...el }) => ({
-          ...el,
-          envId,
-          environment: { id: envId, name, slug }
+      const formatedDoc = sqlNestRelationships({
+        data: doc,
+        key: "id",
+        parentMapper: (data) => ({
+          environment: { id: data.envId, name: data.envName, slug: data.envSlug },
+          projectId: data.projectId,
+          ...SecretApprovalPoliciesSchema.parse(data)
         }),
-        ({ approverId }) => approverId,
-        "approvers"
-      );
+        childrenMapper: [
+          {
+            key: "approverUserId",
+            label: "userApprovers" as const,
+            mapper: ({ approverUserId }) => ({
+              userId: approverUserId
+            })
+          }
+        ]
+      });
+
       return formatedDoc?.[0];
     } catch (error) {
       throw new DatabaseError({ error, name: "FindById" });
@@ -52,18 +62,25 @@ export const secretApprovalPolicyDALFactory = (db: TDbClient) => {
 
   const find = async (filter: TFindFilter<TSecretApprovalPolicies & { projectId: string }>, tx?: Knex) => {
     try {
-      const docs = await sapFindQuery(tx || db, filter);
-      const formatedDoc = mergeOneToManyRelation(
-        docs,
-        "id",
-        ({ approverId, envId, envName: name, envSlug: slug, ...el }) => ({
-          ...el,
-          envId,
-          environment: { id: envId, name, slug }
+      const docs = await secretApprovalPolicyFindQuery(tx || db.replicaNode(), filter);
+      const formatedDoc = sqlNestRelationships({
+        data: docs,
+        key: "id",
+        parentMapper: (data) => ({
+          environment: { id: data.envId, name: data.envName, slug: data.envSlug },
+          projectId: data.projectId,
+          ...SecretApprovalPoliciesSchema.parse(data)
         }),
-        ({ approverId }) => approverId,
-        "approvers"
-      );
+        childrenMapper: [
+          {
+            key: "approverUserId",
+            label: "userApprovers" as const,
+            mapper: ({ approverUserId }) => ({
+              userId: approverUserId
+            })
+          }
+        ]
+      });
       return formatedDoc;
     } catch (error) {
       throw new DatabaseError({ error, name: "Find" });

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

@@ -7,7 +7,6 @@ import { BadRequestError } from "@app/lib/errors";
 import { removeTrailingSlash } from "@app/lib/fn";
 import { containsGlobPatterns } from "@app/lib/picomatch";
 import { TProjectEnvDALFactory } from "@app/services/project-env/project-env-dal";
-import { TProjectMembershipDALFactory } from "@app/services/project-membership/project-membership-dal";
 
 import { TSecretApprovalPolicyApproverDALFactory } from "./secret-approval-policy-approver-dal";
 import { TSecretApprovalPolicyDALFactory } from "./secret-approval-policy-dal";
@@ -29,7 +28,6 @@ type TSecretApprovalPolicyServiceFactoryDep = {
   secretApprovalPolicyDAL: TSecretApprovalPolicyDALFactory;
   projectEnvDAL: Pick<TProjectEnvDALFactory, "findOne">;
   secretApprovalPolicyApproverDAL: TSecretApprovalPolicyApproverDALFactory;
-  projectMembershipDAL: Pick<TProjectMembershipDALFactory, "find">;
 };
 
 export type TSecretApprovalPolicyServiceFactory = ReturnType<typeof secretApprovalPolicyServiceFactory>;
@@ -38,8 +36,7 @@ export const secretApprovalPolicyServiceFactory = ({
   secretApprovalPolicyDAL,
   permissionService,
   secretApprovalPolicyApproverDAL,
-  projectEnvDAL,
-  projectMembershipDAL
+  projectEnvDAL
 }: TSecretApprovalPolicyServiceFactoryDep) => {
   const createSecretApprovalPolicy = async ({
     name,
@@ -48,12 +45,12 @@ export const secretApprovalPolicyServiceFactory = ({
     actorOrgId,
     actorAuthMethod,
     approvals,
-    approvers,
+    approverUserIds,
     projectId,
     secretPath,
     environment
   }: TCreateSapDTO) => {
-    if (approvals > approvers.length)
+    if (approvals > approverUserIds.length)
       throw new BadRequestError({ message: "Approvals cannot be greater than approvers" });
 
     const { permission } = await permissionService.getProjectPermission(
@@ -70,13 +67,6 @@ export const secretApprovalPolicyServiceFactory = ({
     const env = await projectEnvDAL.findOne({ slug: environment, projectId });
     if (!env) throw new BadRequestError({ message: "Environment not found" });
 
-    const secretApprovers = await projectMembershipDAL.find({
-      projectId,
-      $in: { id: approvers }
-    });
-    if (secretApprovers.length !== approvers.length)
-      throw new BadRequestError({ message: "Approver not found in project" });
-
     const secretApproval = await secretApprovalPolicyDAL.transaction(async (tx) => {
       const doc = await secretApprovalPolicyDAL.create(
         {
@@ -88,8 +78,8 @@ export const secretApprovalPolicyServiceFactory = ({
         tx
       );
       await secretApprovalPolicyApproverDAL.insertMany(
-        secretApprovers.map(({ id }) => ({
-          approverId: id,
+        approverUserIds.map((approverUserId) => ({
+          approverUserId,
           policyId: doc.id
         })),
         tx
@@ -100,7 +90,7 @@ export const secretApprovalPolicyServiceFactory = ({
   };
 
   const updateSecretApprovalPolicy = async ({
-    approvers,
+    approverUserIds,
     secretPath,
     name,
     actorId,
@@ -132,22 +122,11 @@ export const secretApprovalPolicyServiceFactory = ({
         },
         tx
       );
-      if (approvers) {
-        const secretApprovers = await projectMembershipDAL.find(
-          {
-            projectId: secretApprovalPolicy.projectId,
-            $in: { id: approvers }
-          },
-          { tx }
-        );
-        if (secretApprovers.length !== approvers.length)
-          throw new BadRequestError({ message: "Approver not found in project" });
-        if (doc.approvals > secretApprovers.length)
-          throw new BadRequestError({ message: "Approvals cannot be greater than approvers" });
+      if (approverUserIds) {
         await secretApprovalPolicyApproverDAL.delete({ policyId: doc.id }, tx);
         await secretApprovalPolicyApproverDAL.insertMany(
-          secretApprovers.map(({ id }) => ({
-            approverId: id,
+          approverUserIds.map((approverUserId) => ({
+            approverUserId,
             policyId: doc.id
           })),
           tx