Merge pull request #1557 from akhilmhdh/feat/temp-roles

feat(server): removed stream in project multi role migration

.github/values.yaml

@@ -27,7 +27,7 @@ infisical:
   deploymentAnnotations:
     secrets.infisical.com/auto-reload: "true"
 
-  kubeSecretRef: "infisical-gamma-secrets"
+  kubeSecretRef: "managed-secret"
 
 ingress:
   ## @param ingress.enabled Enable ingress

Makefile

@@ -7,6 +7,9 @@ push:
 up-dev:
 	docker compose -f docker-compose.dev.yml up --build
 
+up-dev-ldap:
+	docker compose -f docker-compose.dev.yml --profile ldap up --build
+
 up-prod:
 	docker-compose -f docker-compose.prod.yml up --build
 

backend/package-lock.json

@@ -45,6 +45,7 @@
         "knex": "^3.0.1",
         "libsodium-wrappers": "^0.7.13",
         "lodash.isequal": "^4.5.0",
+        "ms": "^2.1.3",
         "mysql2": "^3.9.1",
         "nanoid": "^5.0.4",
         "nodemailer": "^6.9.9",
@@ -52,7 +53,9 @@
         "passport-github": "^1.1.0",
         "passport-gitlab2": "^5.0.0",
         "passport-google-oauth20": "^2.0.0",
+        "passport-ldapauth": "^3.0.1",
         "pg": "^8.11.3",
+        "pg-query-stream": "^4.5.3",
         "picomatch": "^3.0.1",
         "pino": "^8.16.2",
         "posthog-node": "^3.6.2",
@@ -3973,6 +3976,14 @@
       "integrity": "sha512-2h3tFvkbHksiNcDiUdcJ08gXWG10fnahp30GJ2Tbt4vd4pfsbfkoKTaTbYykFoppaJ6DL3914nQ3PU1vVIlBRQ==",
       "dev": true
     },
+    "node_modules/@types/ldapjs": {
+      "version": "2.2.5",
+      "resolved": "https://registry.npmjs.org/@types/ldapjs/-/ldapjs-2.2.5.tgz",
+      "integrity": "sha512-Lv/nD6QDCmcT+V1vaTRnEKE8UgOilVv5pHcQuzkU1LcRe4mbHHuUo/KHi0LKrpdHhQY8FJzryF38fcVdeUIrzg==",
+      "dependencies": {
+        "@types/node": "*"
+      }
+    },
     "node_modules/@types/libsodium-wrappers": {
       "version": "0.7.13",
       "resolved": "https://registry.npmjs.org/@types/libsodium-wrappers/-/libsodium-wrappers-0.7.13.tgz",
@@ -5120,6 +5131,22 @@
         "url": "https://github.com/sponsors/ljharb"
       }
     },
+    "node_modules/asn1": {
+      "version": "0.2.6",
+      "resolved": "https://registry.npmjs.org/asn1/-/asn1-0.2.6.tgz",
+      "integrity": "sha512-ix/FxPn0MDjeyJ7i/yoHGFt/EX6LyNbxSEhPPXODPL+KB0VPk86UYfL0lMdy+KCnv+fmvIzySwaK5COwqVbWTQ==",
+      "dependencies": {
+        "safer-buffer": "~2.1.0"
+      }
+    },
+    "node_modules/assert-plus": {
+      "version": "1.0.0",
+      "resolved": "https://registry.npmjs.org/assert-plus/-/assert-plus-1.0.0.tgz",
+      "integrity": "sha512-NfJ4UzBCcQGLDlQq7nHxH+tv3kyZ0hHQqF5BO6J7tNJeP5do1llPr8dZ8zHonfhAu0PHAdMkSo+8o0wxg9lZWw==",
+      "engines": {
+        "node": ">=0.8"
+      }
+    },
     "node_modules/assertion-error": {
       "version": "1.1.0",
       "resolved": "https://registry.npmjs.org/assertion-error/-/assertion-error-1.1.0.tgz",
@@ -5286,6 +5313,17 @@
         "axios": "0.x || 1.x"
       }
     },
+    "node_modules/backoff": {
+      "version": "2.5.0",
+      "resolved": "https://registry.npmjs.org/backoff/-/backoff-2.5.0.tgz",
+      "integrity": "sha512-wC5ihrnUXmR2douXmXLCe5O3zg3GKIyvRi/hi58a/XyRxVI+3/yM0PYueQOZXPXQ9pxBislYkw+sF9b7C/RuMA==",
+      "dependencies": {
+        "precond": "0.2"
+      },
+      "engines": {
+        "node": ">= 0.6"
+      }
+    },
     "node_modules/balanced-match": {
       "version": "1.0.2",
       "resolved": "https://registry.npmjs.org/balanced-match/-/balanced-match-1.0.2.tgz",
@@ -5331,6 +5369,11 @@
         "node": ">= 10.0.0"
       }
     },
+    "node_modules/bcryptjs": {
+      "version": "2.4.3",
+      "resolved": "https://registry.npmjs.org/bcryptjs/-/bcryptjs-2.4.3.tgz",
+      "integrity": "sha512-V/Hy/X9Vt7f3BbPJEi8BdVFMByHi+jNXrYkW3huaybV/kQ0KJg0Y6PkEMbn+zeT+i+SiKZ/HMqJGIIt4LZDqNQ=="
+    },
     "node_modules/before-after-hook": {
       "version": "2.2.3",
       "resolved": "https://registry.npmjs.org/before-after-hook/-/before-after-hook-2.2.3.tgz",
@@ -5815,6 +5858,11 @@
         "node": ">=6.6.0"
       }
     },
+    "node_modules/core-util-is": {
+      "version": "1.0.2",
+      "resolved": "https://registry.npmjs.org/core-util-is/-/core-util-is-1.0.2.tgz",
+      "integrity": "sha512-3lqz5YjWTYnW6dlDa5TLaTCcShfar1e40rmcJVwCBJC6mWlFuj0eCHIElmG1g5kyuJ/GD+8Wn4FFCcz4gJPfaQ=="
+    },
     "node_modules/create-hash": {
       "version": "1.2.0",
       "resolved": "https://registry.npmjs.org/create-hash/-/create-hash-1.2.0.tgz",
@@ -6885,6 +6933,14 @@
       "resolved": "https://registry.npmjs.org/ms/-/ms-2.0.0.tgz",
       "integrity": "sha512-Tpp60P6IUJDTuOq/5Z8cdskzJujfwqfOTkrwIwj7IRISpnkJnT6SyJ4PCPnGMoFjC9ddhal5KVIYtAt97ix05A=="
     },
+    "node_modules/extsprintf": {
+      "version": "1.4.1",
+      "resolved": "https://registry.npmjs.org/extsprintf/-/extsprintf-1.4.1.tgz",
+      "integrity": "sha512-Wrk35e8ydCKDj/ArClo1VrPVmN8zph5V4AtHwIuHhvMXsKf73UT3BOD+azBIW+3wOJ4FhEH7zyaJCFvChjYvMA==",
+      "engines": [
+        "node >=0.6.0"
+      ]
+    },
     "node_modules/fast-content-type-parse": {
       "version": "1.1.0",
       "resolved": "https://registry.npmjs.org/fast-content-type-parse/-/fast-content-type-parse-1.1.0.tgz",
@@ -8623,6 +8679,57 @@
         "node": ">=8"
       }
     },
+    "node_modules/ldap-filter": {
+      "version": "0.3.3",
+      "resolved": "https://registry.npmjs.org/ldap-filter/-/ldap-filter-0.3.3.tgz",
+      "integrity": "sha512-/tFkx5WIn4HuO+6w9lsfxq4FN3O+fDZeO9Mek8dCD8rTUpqzRa766BOBO7BcGkn3X86m5+cBm1/2S/Shzz7gMg==",
+      "dependencies": {
+        "assert-plus": "^1.0.0"
+      },
+      "engines": {
+        "node": ">=0.8"
+      }
+    },
+    "node_modules/ldapauth-fork": {
+      "version": "5.0.5",
+      "resolved": "https://registry.npmjs.org/ldapauth-fork/-/ldapauth-fork-5.0.5.tgz",
+      "integrity": "sha512-LWUk76+V4AOZbny/3HIPQtGPWZyA3SW2tRhsWIBi9imP22WJktKLHV1ofd8Jo/wY7Ve6vAT7FCI5mEn3blZTjw==",
+      "dependencies": {
+        "@types/ldapjs": "^2.2.2",
+        "bcryptjs": "^2.4.0",
+        "ldapjs": "^2.2.1",
+        "lru-cache": "^7.10.1"
+      },
+      "engines": {
+        "node": ">=0.8.0"
+      }
+    },
+    "node_modules/ldapauth-fork/node_modules/lru-cache": {
+      "version": "7.18.3",
+      "resolved": "https://registry.npmjs.org/lru-cache/-/lru-cache-7.18.3.tgz",
+      "integrity": "sha512-jumlc0BIUrS3qJGgIkWZsyfAM7NCWiBcCDhnd+3NNM5KbBmLTgHVfWBcg6W+rLUsIpzpERPsvwUP7CckAQSOoA==",
+      "engines": {
+        "node": ">=12"
+      }
+    },
+    "node_modules/ldapjs": {
+      "version": "2.3.3",
+      "resolved": "https://registry.npmjs.org/ldapjs/-/ldapjs-2.3.3.tgz",
+      "integrity": "sha512-75QiiLJV/PQqtpH+HGls44dXweviFwQ6SiIK27EqzKQ5jU/7UFrl2E5nLdQ3IYRBzJ/AVFJI66u0MZ0uofKYwg==",
+      "dependencies": {
+        "abstract-logging": "^2.0.0",
+        "asn1": "^0.2.4",
+        "assert-plus": "^1.0.0",
+        "backoff": "^2.5.0",
+        "ldap-filter": "^0.3.3",
+        "once": "^1.4.0",
+        "vasync": "^2.2.0",
+        "verror": "^1.8.1"
+      },
+      "engines": {
+        "node": ">=10.13.0"
+      }
+    },
     "node_modules/leven": {
       "version": "2.1.0",
       "resolved": "https://registry.npmjs.org/leven/-/leven-2.1.0.tgz",
@@ -9755,6 +9862,18 @@
         "node": ">= 0.4.0"
       }
     },
+    "node_modules/passport-ldapauth": {
+      "version": "3.0.1",
+      "resolved": "https://registry.npmjs.org/passport-ldapauth/-/passport-ldapauth-3.0.1.tgz",
+      "integrity": "sha512-TRRx3BHi8GC8MfCT9wmghjde/EGeKjll7zqHRRfGRxXbLcaDce2OftbQrFG7/AWaeFhR6zpZHtBQ/IkINdLVjQ==",
+      "dependencies": {
+        "ldapauth-fork": "^5.0.1",
+        "passport-strategy": "^1.0.0"
+      },
+      "engines": {
+        "node": ">=0.8.0"
+      }
+    },
     "node_modules/passport-oauth2": {
       "version": "1.7.0",
       "resolved": "https://registry.npmjs.org/passport-oauth2/-/passport-oauth2-1.7.0.tgz",
@@ -9911,6 +10030,14 @@
       "resolved": "https://registry.npmjs.org/pg-connection-string/-/pg-connection-string-2.6.1.tgz",
       "integrity": "sha512-w6ZzNu6oMmIzEAYVw+RLK0+nqHPt8K3ZnknKi+g48Ak2pr3dtljJW3o+D/n2zzCG07Zoe9VOX3aiKpj+BN0pjg=="
     },
+    "node_modules/pg-cursor": {
+      "version": "2.10.3",
+      "resolved": "https://registry.npmjs.org/pg-cursor/-/pg-cursor-2.10.3.tgz",
+      "integrity": "sha512-rDyBVoqPVnx/PTmnwQAYgusSeAKlTL++gmpf5klVK+mYMFEqsOc6VHHZnPKc/4lOvr4r6fiMuoxSFuBF1dx4FQ==",
+      "peerDependencies": {
+        "pg": "^8"
+      }
+    },
     "node_modules/pg-int8": {
       "version": "1.0.1",
       "resolved": "https://registry.npmjs.org/pg-int8/-/pg-int8-1.0.1.tgz",
@@ -9941,6 +10068,17 @@
       "resolved": "https://registry.npmjs.org/pg-protocol/-/pg-protocol-1.6.0.tgz",
       "integrity": "sha512-M+PDm637OY5WM307051+bsDia5Xej6d9IR4GwJse1qA1DIhiKlksvrneZOYQq42OM+spubpcNYEo2FcKQrDk+Q=="
     },
+    "node_modules/pg-query-stream": {
+      "version": "4.5.3",
+      "resolved": "https://registry.npmjs.org/pg-query-stream/-/pg-query-stream-4.5.3.tgz",
+      "integrity": "sha512-ufa94r/lHJdjAm3+zPZEO0gXAmCb4tZPaOt7O76mjcxdL/HxwTuryy76km+u0odBBgtfdKFYq/9XGfiYeQF0yA==",
+      "dependencies": {
+        "pg-cursor": "^2.10.3"
+      },
+      "peerDependencies": {
+        "pg": "^8"
+      }
+    },
     "node_modules/pg-types": {
       "version": "2.2.0",
       "resolved": "https://registry.npmjs.org/pg-types/-/pg-types-2.2.0.tgz",
@@ -10309,6 +10447,14 @@
         "node": ">=15.0.0"
       }
     },
+    "node_modules/precond": {
+      "version": "0.2.3",
+      "resolved": "https://registry.npmjs.org/precond/-/precond-0.2.3.tgz",
+      "integrity": "sha512-QCYG84SgGyGzqJ/vlMsxeXd/pgL/I94ixdNFyh1PusWmTCyVfPJjZ1K1jvHtsbfnXQs2TSkEP2fR7QiMZAnKFQ==",
+      "engines": {
+        "node": ">= 0.6"
+      }
+    },
     "node_modules/prelude-ls": {
       "version": "1.2.1",
       "resolved": "https://registry.npmjs.org/prelude-ls/-/prelude-ls-1.2.1.tgz",
@@ -12759,6 +12905,43 @@
         "node": ">= 0.8"
       }
     },
+    "node_modules/vasync": {
+      "version": "2.2.1",
+      "resolved": "https://registry.npmjs.org/vasync/-/vasync-2.2.1.tgz",
+      "integrity": "sha512-Hq72JaTpcTFdWiNA4Y22Amej2GH3BFmBaKPPlDZ4/oC8HNn2ISHLkFrJU4Ds8R3jcUi7oo5Y9jcMHKjES+N9wQ==",
+      "engines": [
+        "node >=0.6.0"
+      ],
+      "dependencies": {
+        "verror": "1.10.0"
+      }
+    },
+    "node_modules/vasync/node_modules/verror": {
+      "version": "1.10.0",
+      "resolved": "https://registry.npmjs.org/verror/-/verror-1.10.0.tgz",
+      "integrity": "sha512-ZZKSmDAEFOijERBLkmYfJ+vmk3w+7hOLYDNkRCuRuMJGEmqYNCNLyBBFwWKVMhfwaEF3WOd0Zlw86U/WC/+nYw==",
+      "engines": [
+        "node >=0.6.0"
+      ],
+      "dependencies": {
+        "assert-plus": "^1.0.0",
+        "core-util-is": "1.0.2",
+        "extsprintf": "^1.2.0"
+      }
+    },
+    "node_modules/verror": {
+      "version": "1.10.1",
+      "resolved": "https://registry.npmjs.org/verror/-/verror-1.10.1.tgz",
+      "integrity": "sha512-veufcmxri4e3XSrT0xwfUR7kguIkaxBeosDg00yDWhk49wdwkSUrvvsm7nc75e1PUyvIeZj6nS8VQRYz2/S4Xg==",
+      "dependencies": {
+        "assert-plus": "^1.0.0",
+        "core-util-is": "1.0.2",
+        "extsprintf": "^1.2.0"
+      },
+      "engines": {
+        "node": ">=0.6.0"
+      }
+    },
     "node_modules/vite": {
       "version": "5.0.12",
       "resolved": "https://registry.npmjs.org/vite/-/vite-5.0.12.tgz",

backend/package.json

@@ -106,6 +106,7 @@
     "knex": "^3.0.1",
     "libsodium-wrappers": "^0.7.13",
     "lodash.isequal": "^4.5.0",
+    "ms": "^2.1.3",
     "mysql2": "^3.9.1",
     "nanoid": "^5.0.4",
     "nodemailer": "^6.9.9",
@@ -113,7 +114,9 @@
     "passport-github": "^1.1.0",
     "passport-gitlab2": "^5.0.0",
     "passport-google-oauth20": "^2.0.0",
+    "passport-ldapauth": "^3.0.1",
     "pg": "^8.11.3",
+    "pg-query-stream": "^4.5.3",
     "picomatch": "^3.0.1",
     "pino": "^8.16.2",
     "posthog-node": "^3.6.2",

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

@@ -3,6 +3,7 @@ import "fastify";
 import { TUsers } from "@app/db/schemas";
 import { TAuditLogServiceFactory } from "@app/ee/services/audit-log/audit-log-service";
 import { TCreateAuditLogDTO } from "@app/ee/services/audit-log/audit-log-types";
+import { TLdapConfigServiceFactory } from "@app/ee/services/ldap-config/ldap-config-service";
 import { TLicenseServiceFactory } from "@app/ee/services/license/license-service";
 import { TPermissionServiceFactory } from "@app/ee/services/permission/permission-service";
 import { TSamlConfigServiceFactory } from "@app/ee/services/saml-config/saml-config-service";
@@ -69,6 +70,7 @@ declare module "fastify" {
     };
     auditLogInfo: Pick<TCreateAuditLogDTO, "userAgent" | "userAgentType" | "ipAddress" | "actor">;
     ssoConfig: Awaited<ReturnType<TSamlConfigServiceFactory["getSaml"]>>;
+    ldapConfig: Awaited<ReturnType<TLdapConfigServiceFactory["getLdapCfg"]>>;
   }
 
   interface FastifyInstance {
@@ -107,6 +109,7 @@ declare module "fastify" {
       snapshot: TSecretSnapshotServiceFactory;
       saml: TSamlConfigServiceFactory;
       scim: TScimServiceFactory;
+      ldap: TLdapConfigServiceFactory;
       auditLog: TAuditLogServiceFactory;
       secretScanning: TSecretScanningServiceFactory;
       license: TLicenseServiceFactory;

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

@@ -32,6 +32,9 @@ import {
   TIdentityOrgMemberships,
   TIdentityOrgMembershipsInsert,
   TIdentityOrgMembershipsUpdate,
+  TIdentityProjectMembershipRole,
+  TIdentityProjectMembershipRoleInsert,
+  TIdentityProjectMembershipRoleUpdate,
   TIdentityProjectMemberships,
   TIdentityProjectMembershipsInsert,
   TIdentityProjectMembershipsUpdate,
@@ -50,6 +53,9 @@ import {
   TIntegrations,
   TIntegrationsInsert,
   TIntegrationsUpdate,
+  TLdapConfigs,
+  TLdapConfigsInsert,
+  TLdapConfigsUpdate,
   TOrganizations,
   TOrganizationsInsert,
   TOrganizationsUpdate,
@@ -80,6 +86,9 @@ import {
   TProjects,
   TProjectsInsert,
   TProjectsUpdate,
+  TProjectUserMembershipRoles,
+  TProjectUserMembershipRolesInsert,
+  TProjectUserMembershipRolesUpdate,
   TSamlConfigs,
   TSamlConfigsInsert,
   TSamlConfigsUpdate,
@@ -161,6 +170,9 @@ import {
   TUserActions,
   TUserActionsInsert,
   TUserActionsUpdate,
+  TUserAliases,
+  TUserAliasesInsert,
+  TUserAliasesUpdate,
   TUserEncryptionKeys,
   TUserEncryptionKeysInsert,
   TUserEncryptionKeysUpdate,
@@ -175,6 +187,7 @@ import {
 declare module "knex/types/tables" {
   interface Tables {
     [TableName.Users]: Knex.CompositeTableType<TUsers, TUsersInsert, TUsersUpdate>;
+    [TableName.UserAliases]: Knex.CompositeTableType<TUserAliases, TUserAliasesInsert, TUserAliasesUpdate>;
     [TableName.UserEncryptionKey]: Knex.CompositeTableType<
       TUserEncryptionKeys,
       TUserEncryptionKeysInsert,
@@ -214,6 +227,11 @@ declare module "knex/types/tables" {
       TProjectEnvironmentsUpdate
     >;
     [TableName.ProjectBot]: Knex.CompositeTableType<TProjectBots, TProjectBotsInsert, TProjectBotsUpdate>;
+    [TableName.ProjectUserMembershipRole]: Knex.CompositeTableType<
+      TProjectUserMembershipRoles,
+      TProjectUserMembershipRolesInsert,
+      TProjectUserMembershipRolesUpdate
+    >;
     [TableName.ProjectRoles]: Knex.CompositeTableType<TProjectRoles, TProjectRolesInsert, TProjectRolesUpdate>;
     [TableName.ProjectKeys]: Knex.CompositeTableType<TProjectKeys, TProjectKeysInsert, TProjectKeysUpdate>;
     [TableName.Secret]: Knex.CompositeTableType<TSecrets, TSecretsInsert, TSecretsUpdate>;
@@ -265,6 +283,11 @@ declare module "knex/types/tables" {
       TIdentityProjectMembershipsInsert,
       TIdentityProjectMembershipsUpdate
     >;
+    [TableName.IdentityProjectMembershipRole]: Knex.CompositeTableType<
+      TIdentityProjectMembershipRole,
+      TIdentityProjectMembershipRoleInsert,
+      TIdentityProjectMembershipRoleUpdate
+    >;
     [TableName.ScimToken]: Knex.CompositeTableType<TScimTokens, TScimTokensInsert, TScimTokensUpdate>;
     [TableName.SecretApprovalPolicy]: Knex.CompositeTableType<
       TSecretApprovalPolicies,
@@ -318,6 +341,7 @@ declare module "knex/types/tables" {
       TSecretSnapshotFoldersUpdate
     >;
     [TableName.SamlConfig]: Knex.CompositeTableType<TSamlConfigs, TSamlConfigsInsert, TSamlConfigsUpdate>;
+    [TableName.LdapConfig]: Knex.CompositeTableType<TLdapConfigs, TLdapConfigsInsert, TLdapConfigsUpdate>;
     [TableName.OrgBot]: Knex.CompositeTableType<TOrgBots, TOrgBotsInsert, TOrgBotsUpdate>;
     [TableName.AuditLog]: Knex.CompositeTableType<TAuditLogs, TAuditLogsInsert, TAuditLogsUpdate>;
     [TableName.GitAppInstallSession]: Knex.CompositeTableType<

backend/src/db/migrations/20240307232900_integration-last-used.ts

@@ -0,0 +1,15 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  await knex.schema.alterTable(TableName.Integration, (t) => {
+    t.datetime("lastUsed");
+  });
+}
+
+export async function down(knex: Knex): Promise<void> {
+  await knex.schema.alterTable(TableName.Integration, (t) => {
+    t.dropColumn("lastUsed");
+  });
+}

backend/src/db/migrations/20240311210135_ldap-config.ts

@@ -0,0 +1,68 @@
+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.LdapConfig))) {
+    await knex.schema.createTable(TableName.LdapConfig, (t) => {
+      t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+      t.uuid("orgId").notNullable().unique();
+      t.foreign("orgId").references("id").inTable(TableName.Organization).onDelete("CASCADE");
+      t.boolean("isActive").notNullable();
+      t.string("url").notNullable();
+      t.string("encryptedBindDN").notNullable();
+      t.string("bindDNIV").notNullable();
+      t.string("bindDNTag").notNullable();
+      t.string("encryptedBindPass").notNullable();
+      t.string("bindPassIV").notNullable();
+      t.string("bindPassTag").notNullable();
+      t.string("searchBase").notNullable();
+      t.text("encryptedCACert").notNullable();
+      t.string("caCertIV").notNullable();
+      t.string("caCertTag").notNullable();
+      t.timestamps(true, true, true);
+    });
+  }
+
+  await createOnUpdateTrigger(knex, TableName.LdapConfig);
+
+  if (!(await knex.schema.hasTable(TableName.UserAliases))) {
+    await knex.schema.createTable(TableName.UserAliases, (t) => {
+      t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+      t.uuid("userId").notNullable();
+      t.foreign("userId").references("id").inTable(TableName.Users).onDelete("CASCADE");
+      t.string("username").notNullable();
+      t.string("aliasType").notNullable();
+      t.string("externalId").notNullable();
+      t.specificType("emails", "text[]");
+      t.uuid("orgId").nullable();
+      t.foreign("orgId").references("id").inTable(TableName.Organization).onDelete("CASCADE");
+      t.timestamps(true, true, true);
+    });
+  }
+
+  await createOnUpdateTrigger(knex, TableName.UserAliases);
+
+  await knex.schema.alterTable(TableName.Users, (t) => {
+    t.string("username").unique();
+    t.string("email").nullable().alter();
+    t.dropUnique(["email"]);
+  });
+
+  await knex(TableName.Users).update("username", knex.ref("email"));
+
+  await knex.schema.alterTable(TableName.Users, (t) => {
+    t.string("username").notNullable().alter();
+  });
+}
+
+export async function down(knex: Knex): Promise<void> {
+  await knex.schema.dropTableIfExists(TableName.LdapConfig);
+  await knex.schema.dropTableIfExists(TableName.UserAliases);
+  await knex.schema.alterTable(TableName.Users, (t) => {
+    t.dropColumn("username");
+    // t.string("email").notNullable().alter();
+  });
+  await dropOnUpdateTrigger(knex, TableName.LdapConfig);
+}

backend/src/db/migrations/20240312162549_temp-roles.ts

@@ -0,0 +1,50 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+import { createOnUpdateTrigger, dropOnUpdateTrigger } from "../utils";
+
+export async function up(knex: Knex): Promise<void> {
+  const doesTableExist = await knex.schema.hasTable(TableName.ProjectUserMembershipRole);
+  if (!doesTableExist) {
+    await knex.schema.createTable(TableName.ProjectUserMembershipRole, (t) => {
+      t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+      t.string("role").notNullable();
+      t.uuid("projectMembershipId").notNullable();
+      t.foreign("projectMembershipId").references("id").inTable(TableName.ProjectMembership).onDelete("CASCADE");
+      // until role is changed/removed the role should not deleted
+      t.uuid("customRoleId");
+      t.foreign("customRoleId").references("id").inTable(TableName.ProjectRoles);
+      t.boolean("isTemporary").notNullable().defaultTo(false);
+      t.string("temporaryMode");
+      t.string("temporaryRange"); // could be cron or relative time like 1H or 1minute etc
+      t.datetime("temporaryAccessStartTime");
+      t.datetime("temporaryAccessEndTime");
+      t.timestamps(true, true, true);
+    });
+  }
+
+  await createOnUpdateTrigger(knex, TableName.ProjectUserMembershipRole);
+
+  const projectMemberships = await knex(TableName.ProjectMembership).select(
+    "id",
+    "role",
+    "createdAt",
+    "updatedAt",
+    knex.ref("roleId").withSchema(TableName.ProjectMembership).as("customRoleId")
+  );
+  if (projectMemberships.length)
+    await knex.batchInsert(
+      TableName.ProjectUserMembershipRole,
+      projectMemberships.map((data) => ({ ...data, projectMembershipId: data.id }))
+    );
+  // will be dropped later
+  // await knex.schema.alterTable(TableName.ProjectMembership, (t) => {
+  //   t.dropColumn("roleId");
+  //   t.dropColumn("role");
+  // });
+}
+
+export async function down(knex: Knex): Promise<void> {
+  await knex.schema.dropTableIfExists(TableName.ProjectUserMembershipRole);
+  await dropOnUpdateTrigger(knex, TableName.ProjectUserMembershipRole);
+}

backend/src/db/migrations/20240312162556_temp-role-identity.ts

@@ -0,0 +1,52 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+import { createOnUpdateTrigger, dropOnUpdateTrigger } from "../utils";
+
+export async function up(knex: Knex): Promise<void> {
+  const doesTableExist = await knex.schema.hasTable(TableName.IdentityProjectMembershipRole);
+  if (!doesTableExist) {
+    await knex.schema.createTable(TableName.IdentityProjectMembershipRole, (t) => {
+      t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+      t.string("role").notNullable();
+      t.uuid("projectMembershipId").notNullable();
+      t.foreign("projectMembershipId")
+        .references("id")
+        .inTable(TableName.IdentityProjectMembership)
+        .onDelete("CASCADE");
+      // until role is changed/removed the role should not deleted
+      t.uuid("customRoleId");
+      t.foreign("customRoleId").references("id").inTable(TableName.ProjectRoles);
+      t.boolean("isTemporary").notNullable().defaultTo(false);
+      t.string("temporaryMode");
+      t.string("temporaryRange"); // could be cron or relative time like 1H or 1minute etc
+      t.datetime("temporaryAccessStartTime");
+      t.datetime("temporaryAccessEndTime");
+      t.timestamps(true, true, true);
+    });
+  }
+
+  await createOnUpdateTrigger(knex, TableName.IdentityProjectMembershipRole);
+
+  const identityMemberships = await knex(TableName.IdentityProjectMembership).select(
+    "id",
+    "role",
+    "createdAt",
+    "updatedAt",
+    knex.ref("roleId").withSchema(TableName.IdentityProjectMembership).as("customRoleId")
+  );
+  if (identityMemberships.length)
+    await knex.batchInsert(
+      TableName.IdentityProjectMembershipRole,
+      identityMemberships.map((data) => ({ ...data, projectMembershipId: data.id }))
+    );
+  // await knex.schema.alterTable(TableName.IdentityProjectMembership, (t) => {
+  //   t.dropColumn("roleId");
+  //   t.dropColumn("role");
+  // });
+}
+
+export async function down(knex: Knex): Promise<void> {
+  await knex.schema.dropTableIfExists(TableName.IdentityProjectMembershipRole);
+  await dropOnUpdateTrigger(knex, TableName.IdentityProjectMembershipRole);
+}

backend/src/db/schemas/identity-project-membership-role.ts

@@ -0,0 +1,31 @@
+// 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 IdentityProjectMembershipRoleSchema = z.object({
+  id: z.string().uuid(),
+  role: z.string(),
+  projectMembershipId: z.string().uuid(),
+  customRoleId: z.string().uuid().nullable().optional(),
+  isTemporary: z.boolean().default(false),
+  temporaryMode: z.string().nullable().optional(),
+  temporaryRange: z.string().nullable().optional(),
+  temporaryAccessStartTime: z.date().nullable().optional(),
+  temporaryAccessEndTime: z.date().nullable().optional(),
+  createdAt: z.date(),
+  updatedAt: z.date()
+});
+
+export type TIdentityProjectMembershipRole = z.infer<typeof IdentityProjectMembershipRoleSchema>;
+export type TIdentityProjectMembershipRoleInsert = Omit<
+  z.input<typeof IdentityProjectMembershipRoleSchema>,
+  TImmutableDBKeys
+>;
+export type TIdentityProjectMembershipRoleUpdate = Partial<
+  Omit<z.input<typeof IdentityProjectMembershipRoleSchema>, TImmutableDBKeys>
+>;

backend/src/db/schemas/index.ts

@@ -8,12 +8,14 @@ export * from "./git-app-org";
 export * from "./identities";
 export * from "./identity-access-tokens";
 export * from "./identity-org-memberships";
+export * from "./identity-project-membership-role";
 export * from "./identity-project-memberships";
 export * from "./identity-ua-client-secrets";
 export * from "./identity-universal-auths";
 export * from "./incident-contacts";
 export * from "./integration-auths";
 export * from "./integrations";
+export * from "./ldap-configs";
 export * from "./models";
 export * from "./org-bots";
 export * from "./org-memberships";
@@ -24,6 +26,7 @@ export * from "./project-environments";
 export * from "./project-keys";
 export * from "./project-memberships";
 export * from "./project-roles";
+export * from "./project-user-membership-roles";
 export * from "./projects";
 export * from "./saml-configs";
 export * from "./scim-tokens";
@@ -52,6 +55,7 @@ export * from "./service-tokens";
 export * from "./super-admin";
 export * from "./trusted-ips";
 export * from "./user-actions";
+export * from "./user-aliases";
 export * from "./user-encryption-keys";
 export * from "./users";
 export * from "./webhooks";

backend/src/db/schemas/integrations.ts

@@ -27,7 +27,8 @@ export const IntegrationsSchema = z.object({
   envId: z.string().uuid(),
   secretPath: z.string().default("/"),
   createdAt: z.date(),
-  updatedAt: z.date()
+  updatedAt: z.date(),
+  lastUsed: z.date().nullable().optional()
 });
 
 export type TIntegrations = z.infer<typeof IntegrationsSchema>;

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

@@ -0,0 +1,31 @@
+// 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 LdapConfigsSchema = z.object({
+  id: z.string().uuid(),
+  orgId: z.string().uuid(),
+  isActive: z.boolean(),
+  url: z.string(),
+  encryptedBindDN: z.string(),
+  bindDNIV: z.string(),
+  bindDNTag: z.string(),
+  encryptedBindPass: z.string(),
+  bindPassIV: z.string(),
+  bindPassTag: z.string(),
+  searchBase: z.string(),
+  encryptedCACert: z.string(),
+  caCertIV: z.string(),
+  caCertTag: z.string(),
+  createdAt: z.date(),
+  updatedAt: z.date()
+});
+
+export type TLdapConfigs = z.infer<typeof LdapConfigsSchema>;
+export type TLdapConfigsInsert = Omit<z.input<typeof LdapConfigsSchema>, TImmutableDBKeys>;
+export type TLdapConfigsUpdate = Partial<Omit<z.input<typeof LdapConfigsSchema>, TImmutableDBKeys>>;

backend/src/db/schemas/models.ts

@@ -2,6 +2,7 @@ import { z } from "zod";
 
 export enum TableName {
   Users = "users",
+  UserAliases = "user_aliases",
   UserEncryptionKey = "user_encryption_keys",
   AuthTokens = "auth_tokens",
   AuthTokenSession = "auth_token_sessions",
@@ -19,6 +20,7 @@ export enum TableName {
   Environment = "project_environments",
   ProjectMembership = "project_memberships",
   ProjectRoles = "project_roles",
+  ProjectUserMembershipRole = "project_user_membership_roles",
   ProjectKeys = "project_keys",
   Secret = "secrets",
   SecretBlindIndex = "secret_blind_indexes",
@@ -40,6 +42,7 @@ export enum TableName {
   IdentityUaClientSecret = "identity_ua_client_secrets",
   IdentityOrgMembership = "identity_org_memberships",
   IdentityProjectMembership = "identity_project_memberships",
+  IdentityProjectMembershipRole = "identity_project_membership_role",
   ScimToken = "scim_tokens",
   SecretApprovalPolicy = "secret_approval_policies",
   SecretApprovalPolicyApprover = "secret_approval_policies_approvers",
@@ -50,6 +53,7 @@ export enum TableName {
   SecretRotation = "secret_rotations",
   SecretRotationOutput = "secret_rotation_outputs",
   SamlConfig = "saml_configs",
+  LdapConfig = "ldap_configs",
   AuditLog = "audit_logs",
   GitAppInstallSession = "git_app_install_sessions",
   GitAppOrg = "git_app_org",

backend/src/db/schemas/project-user-membership-roles.ts

@@ -0,0 +1,31 @@
+// 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 ProjectUserMembershipRolesSchema = z.object({
+  id: z.string().uuid(),
+  role: z.string(),
+  projectMembershipId: z.string().uuid(),
+  customRoleId: z.string().uuid().nullable().optional(),
+  isTemporary: z.boolean().default(false),
+  temporaryMode: z.string().nullable().optional(),
+  temporaryRange: z.string().nullable().optional(),
+  temporaryAccessStartTime: z.date().nullable().optional(),
+  temporaryAccessEndTime: z.date().nullable().optional(),
+  createdAt: z.date(),
+  updatedAt: z.date()
+});
+
+export type TProjectUserMembershipRoles = z.infer<typeof ProjectUserMembershipRolesSchema>;
+export type TProjectUserMembershipRolesInsert = Omit<
+  z.input<typeof ProjectUserMembershipRolesSchema>,
+  TImmutableDBKeys
+>;
+export type TProjectUserMembershipRolesUpdate = Partial<
+  Omit<z.input<typeof ProjectUserMembershipRolesSchema>, TImmutableDBKeys>
+>;

backend/src/db/schemas/user-aliases.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 { TImmutableDBKeys } from "./models";
+
+export const UserAliasesSchema = z.object({
+  id: z.string().uuid(),
+  userId: z.string().uuid(),
+  username: z.string(),
+  aliasType: z.string(),
+  externalId: z.string(),
+  emails: z.string().array().nullable().optional(),
+  orgId: z.string().uuid().nullable().optional(),
+  createdAt: z.date(),
+  updatedAt: z.date()
+});
+
+export type TUserAliases = z.infer<typeof UserAliasesSchema>;
+export type TUserAliasesInsert = Omit<z.input<typeof UserAliasesSchema>, TImmutableDBKeys>;
+export type TUserAliasesUpdate = Partial<Omit<z.input<typeof UserAliasesSchema>, TImmutableDBKeys>>;

backend/src/db/schemas/users.ts

@@ -9,7 +9,7 @@ import { TImmutableDBKeys } from "./models";
 
 export const UsersSchema = z.object({
   id: z.string().uuid(),
-  email: z.string(),
+  email: z.string().nullable().optional(),
   authMethods: z.string().array().nullable().optional(),
   superAdmin: z.boolean().default(false).nullable().optional(),
   firstName: z.string().nullable().optional(),
@@ -20,7 +20,8 @@ export const UsersSchema = z.object({
   devices: z.unknown().nullable().optional(),
   createdAt: z.date(),
   updatedAt: z.date(),
-  isGhost: z.boolean().default(false)
+  isGhost: z.boolean().default(false),
+  username: z.string()
 });
 
 export type TUsers = z.infer<typeof UsersSchema>;

backend/src/db/seed-data.ts

@@ -21,6 +21,7 @@ export let userPublicKey: string | undefined;
 
 export const seedData1 = {
   id: "3dafd81d-4388-432b-a4c5-f735616868c1",
+  username: process.env.TEST_USER_USERNAME || "test@localhost.local",
   email: process.env.TEST_USER_EMAIL || "test@localhost.local",
   password: process.env.TEST_USER_PASSWORD || "testInfisical@1",
   organization: {

backend/src/db/seeds/1-user.ts

@@ -22,6 +22,7 @@ export async function seed(knex: Knex): Promise<void> {
         // eslint-disable-next-line
         // @ts-ignore
         id: seedData1.id,
+        username: seedData1.username,
         email: seedData1.email,
         superAdmin: true,
         firstName: "test",

backend/src/db/seeds/3-project.ts

@@ -4,7 +4,7 @@ import { Knex } from "knex";
 
 import { encryptSymmetric128BitHexKeyUTF8 } from "@app/lib/crypto";
 
-import { OrgMembershipRole, SecretEncryptionAlgo, SecretKeyEncoding, TableName } from "../schemas";
+import { ProjectMembershipRole, SecretEncryptionAlgo, SecretKeyEncoding, TableName } from "../schemas";
 import { buildUserProjectKey, getUserPrivateKey, seedData1 } from "../seed-data";
 
 export const DEFAULT_PROJECT_ENVS = [
@@ -30,10 +30,16 @@ export async function seed(knex: Knex): Promise<void> {
     })
     .returning("*");
 
-  await knex(TableName.ProjectMembership).insert({
-    projectId: project.id,
-    role: OrgMembershipRole.Admin,
-    userId: seedData1.id
+  const projectMembership = await knex(TableName.ProjectMembership)
+    .insert({
+      projectId: project.id,
+      userId: seedData1.id,
+      role: ProjectMembershipRole.Admin
+    })
+    .returning("*");
+  await knex(TableName.ProjectUserMembershipRole).insert({
+    role: ProjectMembershipRole.Admin,
+    projectMembershipId: projectMembership[0].id
   });
 
   const user = await knex(TableName.UserEncryptionKey).where({ userId: seedData1.id }).first();

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

@@ -75,9 +75,16 @@ export async function seed(knex: Knex): Promise<void> {
     }
   ]);
 
-  await knex(TableName.IdentityProjectMembership).insert({
-    identityId: seedData1.machineIdentity.id,
+  const identityProjectMembership = await knex(TableName.IdentityProjectMembership)
+    .insert({
+      identityId: seedData1.machineIdentity.id,
+      projectId: seedData1.project.id,
+      role: ProjectMembershipRole.Admin
+    })
+    .returning("*");
+
+  await knex(TableName.IdentityProjectMembershipRole).insert({
     role: ProjectMembershipRole.Admin,
-    projectId: seedData1.project.id
+    projectMembershipId: identityProjectMembership[0].id
   });
 }

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

@@ -1,3 +1,4 @@
+import { registerLdapRouter } from "./ldap-router";
 import { registerLicenseRouter } from "./license-router";
 import { registerOrgRoleRouter } from "./org-role-router";
 import { registerProjectRoleRouter } from "./project-role-router";
@@ -35,6 +36,7 @@ export const registerV1EERoutes = async (server: FastifyZodProvider) => {
   });
   await server.register(registerSamlRouter, { prefix: "/sso" });
   await server.register(registerScimRouter, { prefix: "/scim" });
+  await server.register(registerLdapRouter, { prefix: "/ldap" });
   await server.register(registerSecretScanningRouter, { prefix: "/secret-scanning" });
   await server.register(registerSecretRotationRouter, { prefix: "/secret-rotations" });
   await server.register(registerSecretVersionRouter, { prefix: "/secret" });

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

@@ -0,0 +1,192 @@
+/* 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 { IncomingMessage } from "node:http";
+
+import { Authenticator } from "@fastify/passport";
+import fastifySession from "@fastify/session";
+import { FastifyRequest } from "fastify";
+import LdapStrategy from "passport-ldapauth";
+import { z } from "zod";
+
+import { LdapConfigsSchema } from "@app/db/schemas";
+import { getConfig } from "@app/lib/config/env";
+import { logger } from "@app/lib/logger";
+import { verifyAuth } from "@app/server/plugins/auth/verify-auth";
+import { AuthMode } from "@app/services/auth/auth-type";
+
+export const registerLdapRouter = async (server: FastifyZodProvider) => {
+  const appCfg = getConfig();
+  const passport = new Authenticator({ key: "ldap", userProperty: "passportUser" });
+  await server.register(fastifySession, { secret: appCfg.COOKIE_SECRET_SIGN_KEY });
+  await server.register(passport.initialize());
+  await server.register(passport.secureSession());
+
+  const getLdapPassportOpts = (req: FastifyRequest, done: any) => {
+    const { organizationSlug } = req.body as {
+      organizationSlug: string;
+    };
+
+    process.nextTick(async () => {
+      try {
+        const { opts, ldapConfig } = await server.services.ldap.bootLdap(organizationSlug);
+        req.ldapConfig = ldapConfig;
+        done(null, opts);
+      } catch (err) {
+        done(err);
+      }
+    });
+  };
+
+  passport.use(
+    new LdapStrategy(
+      getLdapPassportOpts as any,
+      // eslint-disable-next-line
+      async (req: IncomingMessage, user, cb) => {
+        try {
+          const { isUserCompleted, providerAuthToken } = await server.services.ldap.ldapLogin({
+            externalId: user.uidNumber,
+            username: user.uid,
+            firstName: user.givenName,
+            lastName: user.sn,
+            emails: user.mail ? [user.mail] : [],
+            relayState: ((req as unknown as FastifyRequest).body as { RelayState?: string }).RelayState,
+            orgId: (req as unknown as FastifyRequest).ldapConfig.organization
+          });
+
+          return cb(null, { isUserCompleted, providerAuthToken });
+        } catch (err) {
+          logger.error(err);
+          return cb(err, false);
+        }
+      }
+    )
+  );
+
+  server.route({
+    url: "/login",
+    method: "POST",
+    schema: {
+      body: z.object({
+        organizationSlug: z.string().trim()
+      })
+    },
+    preValidation: passport.authenticate("ldapauth", {
+      session: false
+      // failureFlash: true,
+      // failureRedirect: "/login/provider/error"
+      // this is due to zod type difference
+    }) as any,
+    handler: (req, res) => {
+      let nextUrl;
+      if (req.passportUser.isUserCompleted) {
+        nextUrl = `${appCfg.SITE_URL}/login/sso?token=${encodeURIComponent(req.passportUser.providerAuthToken)}`;
+      } else {
+        nextUrl = `${appCfg.SITE_URL}/signup/sso?token=${encodeURIComponent(req.passportUser.providerAuthToken)}`;
+      }
+
+      return res.status(200).send({
+        nextUrl
+      });
+    }
+  });
+
+  server.route({
+    url: "/config",
+    method: "GET",
+    onRequest: verifyAuth([AuthMode.JWT]),
+    schema: {
+      querystring: z.object({
+        organizationId: z.string().trim()
+      }),
+      response: {
+        200: z.object({
+          id: z.string(),
+          organization: z.string(),
+          isActive: z.boolean(),
+          url: z.string(),
+          bindDN: z.string(),
+          bindPass: z.string(),
+          searchBase: z.string(),
+          caCert: z.string()
+        })
+      }
+    },
+    handler: async (req) => {
+      const ldap = await server.services.ldap.getLdapCfgWithPermissionCheck({
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        orgId: req.query.organizationId,
+        actorOrgId: req.permission.orgId
+      });
+      return ldap;
+    }
+  });
+
+  server.route({
+    url: "/config",
+    method: "POST",
+    onRequest: verifyAuth([AuthMode.JWT]),
+    schema: {
+      body: z.object({
+        organizationId: z.string().trim(),
+        isActive: z.boolean(),
+        url: z.string().trim(),
+        bindDN: z.string().trim(),
+        bindPass: z.string().trim(),
+        searchBase: z.string().trim(),
+        caCert: z.string().trim().default("")
+      }),
+      response: {
+        200: LdapConfigsSchema
+      }
+    },
+    handler: async (req) => {
+      const ldap = await server.services.ldap.createLdapCfg({
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        orgId: req.body.organizationId,
+        actorOrgId: req.permission.orgId,
+        ...req.body
+      });
+
+      return ldap;
+    }
+  });
+
+  server.route({
+    url: "/config",
+    method: "PATCH",
+    onRequest: verifyAuth([AuthMode.JWT]),
+    schema: {
+      body: z.object({
+        organizationId: z.string().trim(),
+        isActive: z.boolean().optional(),
+        url: z.string().trim().optional(),
+        bindDN: z.string().trim().optional(),
+        bindPass: z.string().trim().optional(),
+        searchBase: z.string().trim().optional(),
+        caCert: z.string().trim().optional()
+      }),
+      response: {
+        200: LdapConfigsSchema
+      }
+    },
+    handler: async (req) => {
+      const ldap = await server.services.ldap.updateLdapCfg({
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        orgId: req.body.organizationId,
+        actorOrgId: req.permission.orgId,
+        ...req.body
+      });
+
+      return ldap;
+    }
+  });
+};

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

@@ -1,6 +1,7 @@
+import slugify from "@sindresorhus/slugify";
 import { z } from "zod";
 
-import { OrgMembershipsSchema, OrgRolesSchema } from "@app/db/schemas";
+import { OrgMembershipRole, OrgMembershipsSchema, OrgRolesSchema } from "@app/db/schemas";
 import { verifyAuth } from "@app/server/plugins/auth/verify-auth";
 import { AuthMode } from "@app/services/auth/auth-type";
 
@@ -13,7 +14,17 @@ export const registerOrgRoleRouter = async (server: FastifyZodProvider) => {
         organizationId: z.string().trim()
       }),
       body: z.object({
-        slug: z.string().trim(),
+        slug: z
+          .string()
+          .min(1)
+          .trim()
+          .refine(
+            (val) => Object.keys(OrgMembershipRole).includes(val),
+            "Please choose a different slug, the slug you have entered is reserved"
+          )
+          .refine((v) => slugify(v) === v, {
+            message: "Slug must be a valid"
+          }),
         name: z.string().trim(),
         description: z.string().trim().optional(),
         permissions: z.any().array()
@@ -45,7 +56,17 @@ export const registerOrgRoleRouter = async (server: FastifyZodProvider) => {
         roleId: z.string().trim()
       }),
       body: z.object({
-        slug: z.string().trim().optional(),
+        slug: z
+          .string()
+          .trim()
+          .optional()
+          .refine(
+            (val) => typeof val === "undefined" || Object.keys(OrgMembershipRole).includes(val),
+            "Please choose a different slug, the slug you have entered is reserved."
+          )
+          .refine((val) => typeof val === "undefined" || slugify(val) === val, {
+            message: "Slug must be a valid"
+          }),
         name: z.string().trim().optional(),
         description: z.string().trim().optional(),
         permissions: z.any().array()

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

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

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

@@ -122,7 +122,7 @@ export const registerScimRouter = async (server: FastifyZodProvider) => {
               emails: z.array(
                 z.object({
                   primary: z.boolean(),
-                  value: z.string().email(),
+                  value: z.string(),
                   type: z.string().trim()
                 })
               ),
@@ -168,7 +168,7 @@ export const registerScimRouter = async (server: FastifyZodProvider) => {
           emails: z.array(
             z.object({
               primary: z.boolean(),
-              value: z.string().email(),
+              value: z.string(),
               type: z.string().trim()
             })
           ),
@@ -198,13 +198,15 @@ export const registerScimRouter = async (server: FastifyZodProvider) => {
           familyName: z.string().trim(),
           givenName: z.string().trim()
         }),
-        // emails: z.array( // optional?
-        //   z.object({
-        //     primary: z.boolean(),
-        //     value: z.string().email(),
-        //     type: z.string().trim()
-        //   })
-        // ),
+        emails: z
+          .array(
+            z.object({
+              primary: z.boolean(),
+              value: z.string().email(),
+              type: z.string().trim()
+            })
+          )
+          .optional(),
         // displayName: z.string().trim(),
         active: z.boolean()
       }),
@@ -231,8 +233,11 @@ export const registerScimRouter = async (server: FastifyZodProvider) => {
     },
     onRequest: verifyAuth([AuthMode.SCIM_TOKEN]),
     handler: async (req) => {
+      const primaryEmail = req.body.emails?.find((email) => email.primary)?.value;
+
       const user = await req.server.services.scim.createScimUser({
-        email: req.body.userName,
+        username: req.body.userName,
+        email: primaryEmail,
         firstName: req.body.name.givenName,
         lastName: req.body.name.familyName,
         orgId: req.permission.orgId as string

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

@@ -92,7 +92,8 @@ export enum EventType {
 
 interface UserActorMetadata {
   userId: string;
-  email: string;
+  email?: string | null;
+  username: string;
 }
 
 interface ServiceActorMetadata {

backend/src/ee/services/ldap-config/ldap-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 TLdapConfigDALFactory = ReturnType<typeof ldapConfigDALFactory>;
+
+export const ldapConfigDALFactory = (db: TDbClient) => {
+  const ldapCfgOrm = ormify(db, TableName.LdapConfig);
+
+  return { ...ldapCfgOrm };
+};

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

@@ -0,0 +1,429 @@
+import { ForbiddenError } from "@casl/ability";
+import jwt from "jsonwebtoken";
+
+import { OrgMembershipRole, OrgMembershipStatus, SecretKeyEncoding, TLdapConfigsUpdate } from "@app/db/schemas";
+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 { TOrgPermission } from "@app/lib/types";
+import { AuthMethod, AuthTokenType } from "@app/services/auth/auth-type";
+import { TOrgBotDALFactory } from "@app/services/org/org-bot-dal";
+import { TOrgDALFactory } from "@app/services/org/org-dal";
+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 { TLicenseServiceFactory } from "../license/license-service";
+import { OrgPermissionActions, OrgPermissionSubjects } from "../permission/org-permission";
+import { TPermissionServiceFactory } from "../permission/permission-service";
+import { TLdapConfigDALFactory } from "./ldap-config-dal";
+import { TCreateLdapCfgDTO, TLdapLoginDTO, TUpdateLdapCfgDTO } from "./ldap-config-types";
+
+type TLdapConfigServiceFactoryDep = {
+  ldapConfigDAL: TLdapConfigDALFactory;
+  orgDAL: Pick<
+    TOrgDALFactory,
+    "createMembership" | "updateMembershipById" | "findMembership" | "findOrgById" | "findOne" | "updateById"
+  >;
+  orgBotDAL: Pick<TOrgBotDALFactory, "findOne" | "create" | "transaction">;
+  userDAL: Pick<TUserDALFactory, "create" | "findOne" | "transaction" | "updateById">;
+  userAliasDAL: Pick<TUserAliasDALFactory, "create" | "findOne">;
+  permissionService: Pick<TPermissionServiceFactory, "getOrgPermission">;
+  licenseService: Pick<TLicenseServiceFactory, "getPlan">;
+};
+
+export type TLdapConfigServiceFactory = ReturnType<typeof ldapConfigServiceFactory>;
+
+export const ldapConfigServiceFactory = ({
+  ldapConfigDAL,
+  orgDAL,
+  orgBotDAL,
+  userDAL,
+  userAliasDAL,
+  permissionService,
+  licenseService
+}: TLdapConfigServiceFactoryDep) => {
+  const createLdapCfg = async ({
+    actor,
+    actorId,
+    orgId,
+    actorOrgId,
+    isActive,
+    url,
+    bindDN,
+    bindPass,
+    searchBase,
+    caCert
+  }: TCreateLdapCfgDTO) => {
+    const { permission } = await permissionService.getOrgPermission(actor, actorId, orgId, actorOrgId);
+    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Create, OrgPermissionSubjects.Ldap);
+
+    const plan = await licenseService.getPlan(orgId);
+    if (!plan.ldap)
+      throw new BadRequestError({
+        message:
+          "Failed to create LDAP configuration due to plan restriction. Upgrade plan to create LDAP configuration."
+      });
+
+    const orgBot = await orgBotDAL.transaction(async (tx) => {
+      const doc = await orgBotDAL.findOne({ orgId }, 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,
+          privateKeyTag,
+          privateKeyAlgorithm,
+          privateKeyKeyEncoding,
+          symmetricKeyKeyEncoding
+        },
+        tx
+      );
+    });
+
+    const key = infisicalSymmetricDecrypt({
+      ciphertext: orgBot.encryptedSymmetricKey,
+      iv: orgBot.symmetricKeyIV,
+      tag: orgBot.symmetricKeyTag,
+      keyEncoding: orgBot.symmetricKeyKeyEncoding as SecretKeyEncoding
+    });
+
+    const { ciphertext: encryptedBindDN, iv: bindDNIV, tag: bindDNTag } = encryptSymmetric(bindDN, key);
+    const { ciphertext: encryptedBindPass, iv: bindPassIV, tag: bindPassTag } = encryptSymmetric(bindPass, key);
+    const { ciphertext: encryptedCACert, iv: caCertIV, tag: caCertTag } = encryptSymmetric(caCert, key);
+
+    const ldapConfig = await ldapConfigDAL.create({
+      orgId,
+      isActive,
+      url,
+      encryptedBindDN,
+      bindDNIV,
+      bindDNTag,
+      encryptedBindPass,
+      bindPassIV,
+      bindPassTag,
+      searchBase,
+      encryptedCACert,
+      caCertIV,
+      caCertTag
+    });
+
+    return ldapConfig;
+  };
+
+  const updateLdapCfg = async ({
+    actor,
+    actorId,
+    orgId,
+    actorOrgId,
+    isActive,
+    url,
+    bindDN,
+    bindPass,
+    searchBase,
+    caCert
+  }: TUpdateLdapCfgDTO) => {
+    const { permission } = await permissionService.getOrgPermission(actor, actorId, orgId, actorOrgId);
+    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Edit, OrgPermissionSubjects.Ldap);
+
+    const plan = await licenseService.getPlan(orgId);
+    if (!plan.ldap)
+      throw new BadRequestError({
+        message:
+          "Failed to update LDAP configuration due to plan restriction. Upgrade plan to update LDAP configuration."
+      });
+
+    const updateQuery: TLdapConfigsUpdate = {
+      isActive,
+      url,
+      searchBase
+    };
+
+    const orgBot = await orgBotDAL.findOne({ 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
+    });
+
+    if (bindDN) {
+      const { ciphertext: encryptedBindDN, iv: bindDNIV, tag: bindDNTag } = encryptSymmetric(bindDN, key);
+      updateQuery.encryptedBindDN = encryptedBindDN;
+      updateQuery.bindDNIV = bindDNIV;
+      updateQuery.bindDNTag = bindDNTag;
+    }
+
+    if (bindPass) {
+      const { ciphertext: encryptedBindPass, iv: bindPassIV, tag: bindPassTag } = encryptSymmetric(bindPass, key);
+      updateQuery.encryptedBindPass = encryptedBindPass;
+      updateQuery.bindPassIV = bindPassIV;
+      updateQuery.bindPassTag = bindPassTag;
+    }
+
+    if (caCert) {
+      const { ciphertext: encryptedCACert, iv: caCertIV, tag: caCertTag } = encryptSymmetric(caCert, key);
+      updateQuery.encryptedCACert = encryptedCACert;
+      updateQuery.caCertIV = caCertIV;
+      updateQuery.caCertTag = caCertTag;
+    }
+
+    const [ldapConfig] = await ldapConfigDAL.update({ orgId }, updateQuery);
+
+    return ldapConfig;
+  };
+
+  const getLdapCfg = async (filter: { orgId: string; isActive?: boolean }) => {
+    const ldapConfig = await ldapConfigDAL.findOne(filter);
+    if (!ldapConfig) throw new BadRequestError({ message: "Failed to find organization LDAP data" });
+
+    const orgBot = await orgBotDAL.findOne({ orgId: ldapConfig.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 {
+      encryptedBindDN,
+      bindDNIV,
+      bindDNTag,
+      encryptedBindPass,
+      bindPassIV,
+      bindPassTag,
+      encryptedCACert,
+      caCertIV,
+      caCertTag
+    } = ldapConfig;
+
+    let bindDN = "";
+    if (encryptedBindDN && bindDNIV && bindDNTag) {
+      bindDN = decryptSymmetric({
+        ciphertext: encryptedBindDN,
+        key,
+        tag: bindDNTag,
+        iv: bindDNIV
+      });
+    }
+
+    let bindPass = "";
+    if (encryptedBindPass && bindPassIV && bindPassTag) {
+      bindPass = decryptSymmetric({
+        ciphertext: encryptedBindPass,
+        key,
+        tag: bindPassTag,
+        iv: bindPassIV
+      });
+    }
+
+    let caCert = "";
+    if (encryptedCACert && caCertIV && caCertTag) {
+      caCert = decryptSymmetric({
+        ciphertext: encryptedCACert,
+        key,
+        tag: caCertTag,
+        iv: caCertIV
+      });
+    }
+
+    return {
+      id: ldapConfig.id,
+      organization: ldapConfig.orgId,
+      isActive: ldapConfig.isActive,
+      url: ldapConfig.url,
+      bindDN,
+      bindPass,
+      searchBase: ldapConfig.searchBase,
+      caCert
+    };
+  };
+
+  const getLdapCfgWithPermissionCheck = async ({ actor, actorId, orgId, actorOrgId }: TOrgPermission) => {
+    const { permission } = await permissionService.getOrgPermission(actor, actorId, orgId, actorOrgId);
+    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Read, OrgPermissionSubjects.Ldap);
+    return getLdapCfg({
+      orgId
+    });
+  };
+
+  const bootLdap = async (organizationSlug: string) => {
+    const organization = await orgDAL.findOne({ slug: organizationSlug });
+    if (!organization) throw new BadRequestError({ message: "Org not found" });
+
+    const ldapConfig = await getLdapCfg({
+      orgId: organization.id,
+      isActive: true
+    });
+
+    const opts = {
+      server: {
+        url: ldapConfig.url,
+        bindDN: ldapConfig.bindDN,
+        bindCredentials: ldapConfig.bindPass,
+        searchBase: ldapConfig.searchBase,
+        searchFilter: "(uid={{username}})",
+        searchAttributes: ["uid", "uidNumber", "givenName", "sn", "mail"],
+        ...(ldapConfig.caCert !== ""
+          ? {
+              tlsOptions: {
+                ca: [ldapConfig.caCert]
+              }
+            }
+          : {})
+      },
+      passReqToCallback: true
+    };
+
+    return { opts, ldapConfig };
+  };
+
+  const ldapLogin = async ({ externalId, username, firstName, lastName, emails, orgId, relayState }: TLdapLoginDTO) => {
+    const appCfg = getConfig();
+    let userAlias = await userAliasDAL.findOne({
+      externalId,
+      orgId,
+      aliasType: AuthMethod.LDAP
+    });
+
+    const organization = await orgDAL.findOrgById(orgId);
+    if (!organization) throw new BadRequestError({ message: "Org not found" });
+
+    if (userAlias) {
+      await userDAL.transaction(async (tx) => {
+        const [orgMembership] = await orgDAL.findMembership({ userId: userAlias.userId }, { tx });
+        if (!orgMembership) {
+          await orgDAL.createMembership(
+            {
+              userId: userAlias.userId,
+              orgId,
+              role: OrgMembershipRole.Member,
+              status: OrgMembershipStatus.Accepted
+            },
+            tx
+          );
+        } else if (orgMembership.status === OrgMembershipStatus.Invited) {
+          await orgDAL.updateMembershipById(
+            orgMembership.id,
+            {
+              status: OrgMembershipStatus.Accepted
+            },
+            tx
+          );
+        }
+      });
+    } else {
+      userAlias = await userDAL.transaction(async (tx) => {
+        const uniqueUsername = await normalizeUsername(username, userDAL);
+        const newUser = await userDAL.create(
+          {
+            username: uniqueUsername,
+            email: emails[0],
+            firstName,
+            lastName,
+            authMethods: [AuthMethod.LDAP],
+            isGhost: false
+          },
+          tx
+        );
+        const newUserAlias = await userAliasDAL.create(
+          {
+            userId: newUser.id,
+            username,
+            aliasType: AuthMethod.LDAP,
+            externalId,
+            emails,
+            orgId
+          },
+          tx
+        );
+
+        await orgDAL.createMembership(
+          {
+            userId: newUser.id,
+            orgId,
+            role: OrgMembershipRole.Member,
+            status: OrgMembershipStatus.Invited
+          },
+          tx
+        );
+
+        return newUserAlias;
+      });
+    }
+
+    const user = await userDAL.findOne({ id: userAlias.userId });
+
+    const isUserCompleted = Boolean(user.isAccepted);
+
+    const providerAuthToken = jwt.sign(
+      {
+        authTokenType: AuthTokenType.PROVIDER_TOKEN,
+        userId: user.id,
+        username: user.username,
+        firstName,
+        lastName,
+        organizationName: organization.name,
+        organizationId: organization.id,
+        authMethod: AuthMethod.LDAP,
+        isUserCompleted,
+        ...(relayState
+          ? {
+              callbackPort: (JSON.parse(relayState) as { callbackPort: string }).callbackPort
+            }
+          : {})
+      },
+      appCfg.AUTH_SECRET,
+      {
+        expiresIn: appCfg.JWT_PROVIDER_AUTH_LIFETIME
+      }
+    );
+
+    return { isUserCompleted, providerAuthToken };
+  };
+
+  return {
+    createLdapCfg,
+    updateLdapCfg,
+    getLdapCfgWithPermissionCheck,
+    getLdapCfg,
+    // getLdapPassportOpts,
+    ldapLogin,
+    bootLdap
+  };
+};

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

@@ -0,0 +1,30 @@
+import { TOrgPermission } from "@app/lib/types";
+
+export type TCreateLdapCfgDTO = {
+  isActive: boolean;
+  url: string;
+  bindDN: string;
+  bindPass: string;
+  searchBase: string;
+  caCert: string;
+} & TOrgPermission;
+
+export type TUpdateLdapCfgDTO = Partial<{
+  isActive: boolean;
+  url: string;
+  bindDN: string;
+  bindPass: string;
+  searchBase: string;
+  caCert: string;
+}> &
+  TOrgPermission;
+
+export type TLdapLoginDTO = {
+  externalId: string;
+  username: string;
+  firstName: string;
+  lastName: string;
+  emails: string[];
+  orgId: string;
+  relayState?: string;
+};

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

@@ -18,6 +18,8 @@ export const getDefaultOnPremFeatures = () => {
     auditLogs: false,
     auditLogsRetentionDays: 0,
     samlSSO: false,
+    scim: false,
+    ldap: false,
     status: null,
     trial_end: null,
     has_used_trial: true,

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

@@ -25,6 +25,7 @@ export const getDefaultOnPremFeatures = (): TFeatureSet => ({
   auditLogsRetentionDays: 0,
   samlSSO: false,
   scim: false,
+  ldap: false,
   status: null,
   trial_end: null,
   has_used_trial: true,

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

@@ -147,14 +147,14 @@ export const licenseServiceFactory = ({
     }
   };
 
-  const generateOrgCustomerId = async (orgName: string, email: string) => {
+  const generateOrgCustomerId = async (orgName: string, email?: string | null) => {
     if (instanceType === InstanceType.Cloud) {
       const {
         data: { customerId }
       } = await licenseServerCloudApi.request.post<{ customerId: string }>(
         "/api/license-server/v1/customers",
         {
-          email,
+          email: email ?? "",
           name: orgName
         },
         { timeout: 5000, signal: AbortSignal.timeout(5000) }

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

@@ -26,6 +26,7 @@ export type TFeatureSet = {
   auditLogsRetentionDays: 0;
   samlSSO: false;
   scim: false;
+  ldap: false;
   status: null;
   trial_end: null;
   has_used_trial: true;

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

@@ -17,6 +17,7 @@ export enum OrgPermissionSubjects {
   IncidentAccount = "incident-contact",
   Sso = "sso",
   Scim = "scim",
+  Ldap = "ldap",
   Billing = "billing",
   SecretScanning = "secret-scanning",
   Identity = "identity"
@@ -31,6 +32,7 @@ export type OrgPermissionSet =
   | [OrgPermissionActions, OrgPermissionSubjects.IncidentAccount]
   | [OrgPermissionActions, OrgPermissionSubjects.Sso]
   | [OrgPermissionActions, OrgPermissionSubjects.Scim]
+  | [OrgPermissionActions, OrgPermissionSubjects.Ldap]
   | [OrgPermissionActions, OrgPermissionSubjects.SecretScanning]
   | [OrgPermissionActions, OrgPermissionSubjects.Billing]
   | [OrgPermissionActions, OrgPermissionSubjects.Identity];
@@ -76,6 +78,11 @@ const buildAdminPermission = () => {
   can(OrgPermissionActions.Edit, OrgPermissionSubjects.Scim);
   can(OrgPermissionActions.Delete, OrgPermissionSubjects.Scim);
 
+  can(OrgPermissionActions.Read, OrgPermissionSubjects.Ldap);
+  can(OrgPermissionActions.Create, OrgPermissionSubjects.Ldap);
+  can(OrgPermissionActions.Edit, OrgPermissionSubjects.Ldap);
+  can(OrgPermissionActions.Delete, OrgPermissionSubjects.Ldap);
+
   can(OrgPermissionActions.Read, OrgPermissionSubjects.Billing);
   can(OrgPermissionActions.Create, OrgPermissionSubjects.Billing);
   can(OrgPermissionActions.Edit, OrgPermissionSubjects.Billing);

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

@@ -1,7 +1,9 @@
+import { z } from "zod";
+
 import { TDbClient } from "@app/db";
-import { TableName } from "@app/db/schemas";
+import { IdentityProjectMembershipRoleSchema, ProjectUserMembershipRolesSchema, TableName } from "@app/db/schemas";
 import { DatabaseError } from "@app/lib/errors";
-import { selectAllTableCols } from "@app/lib/knex";
+import { selectAllTableCols, sqlNestRelationships } from "@app/lib/knex";
 
 export type TPermissionDALFactory = ReturnType<typeof permissionDALFactory>;
 
@@ -43,21 +45,72 @@ export const permissionDALFactory = (db: TDbClient) => {
 
   const getProjectPermission = async (userId: string, projectId: string) => {
     try {
-      const membership = await db(TableName.ProjectMembership)
-        .leftJoin(TableName.ProjectRoles, `${TableName.ProjectMembership}.roleId`, `${TableName.ProjectRoles}.id`)
+      const docs = await db(TableName.ProjectMembership)
+        .join(
+          TableName.ProjectUserMembershipRole,
+          `${TableName.ProjectUserMembershipRole}.projectMembershipId`,
+          `${TableName.ProjectMembership}.id`
+        )
+        .leftJoin(
+          TableName.ProjectRoles,
+          `${TableName.ProjectUserMembershipRole}.customRoleId`,
+          `${TableName.ProjectRoles}.id`
+        )
         .join(TableName.Project, `${TableName.ProjectMembership}.projectId`, `${TableName.Project}.id`)
         .join(TableName.Organization, `${TableName.Project}.orgId`, `${TableName.Organization}.id`)
         .where("userId", userId)
         .where(`${TableName.ProjectMembership}.projectId`, projectId)
-        .select(selectAllTableCols(TableName.ProjectMembership))
+        .select(selectAllTableCols(TableName.ProjectUserMembershipRole))
         .select(
+          db.ref("id").withSchema(TableName.ProjectMembership).as("membershipId"),
+          // TODO(roll-forward-migration): remove this field when we drop this in next migration after a week
+          db.ref("role").withSchema(TableName.ProjectMembership).as("oldRoleField"),
+          db.ref("createdAt").withSchema(TableName.ProjectMembership).as("membershipCreatedAt"),
+          db.ref("updatedAt").withSchema(TableName.ProjectMembership).as("membershipUpdatedAt"),
           db.ref("authEnforced").withSchema(TableName.Organization).as("orgAuthEnforced"),
-          db.ref("orgId").withSchema(TableName.Project)
+          db.ref("orgId").withSchema(TableName.Project),
+          db.ref("slug").withSchema(TableName.ProjectRoles).as("customRoleSlug")
         )
-        .select("permissions")
-        .first();
+        .select("permissions");
 
-      return membership;
+      const permission = sqlNestRelationships({
+        data: docs,
+        key: "membershipId",
+        parentMapper: ({
+          orgId,
+          orgAuthEnforced,
+          membershipId,
+          membershipCreatedAt,
+          membershipUpdatedAt,
+          oldRoleField
+        }) => ({
+          orgId,
+          orgAuthEnforced,
+          userId,
+          role: oldRoleField,
+          id: membershipId,
+          projectId,
+          createdAt: membershipCreatedAt,
+          updatedAt: membershipUpdatedAt
+        }),
+        childrenMapper: [
+          {
+            key: "id",
+            label: "roles" as const,
+            mapper: (data) =>
+              ProjectUserMembershipRolesSchema.extend({
+                permissions: z.unknown(),
+                customRoleSlug: z.string().optional().nullable()
+              }).parse(data)
+          }
+        ]
+      });
+      // when introducting cron mode change it here
+      const activeRoles = permission?.[0]?.roles.filter(
+        ({ isTemporary, temporaryAccessEndTime }) =>
+          !isTemporary || (isTemporary && temporaryAccessEndTime && new Date() < temporaryAccessEndTime)
+      );
+      return permission?.[0] ? { ...permission[0], roles: activeRoles } : undefined;
     } catch (error) {
       throw new DatabaseError({ error, name: "GetProjectPermission" });
     }
@@ -65,18 +118,62 @@ export const permissionDALFactory = (db: TDbClient) => {
 
   const getProjectIdentityPermission = async (identityId: string, projectId: string) => {
     try {
-      const membership = await db(TableName.IdentityProjectMembership)
+      const docs = await db(TableName.IdentityProjectMembership)
+        .join(
+          TableName.IdentityProjectMembershipRole,
+          `${TableName.IdentityProjectMembershipRole}.projectMembershipId`,
+          `${TableName.IdentityProjectMembership}.id`
+        )
         .leftJoin(
           TableName.ProjectRoles,
-          `${TableName.IdentityProjectMembership}.roleId`,
+          `${TableName.IdentityProjectMembershipRole}.customRoleId`,
           `${TableName.ProjectRoles}.id`
         )
         .where("identityId", identityId)
         .where(`${TableName.IdentityProjectMembership}.projectId`, projectId)
-        .select(selectAllTableCols(TableName.IdentityProjectMembership))
-        .select("permissions")
-        .first();
-      return membership;
+        .select(selectAllTableCols(TableName.IdentityProjectMembershipRole))
+        .select(
+          db.ref("id").withSchema(TableName.IdentityProjectMembership).as("membershipId"),
+          db.ref("role").withSchema(TableName.IdentityProjectMembership).as("oldRoleField"),
+          db.ref("createdAt").withSchema(TableName.IdentityProjectMembership).as("membershipCreatedAt"),
+          db.ref("updatedAt").withSchema(TableName.IdentityProjectMembership).as("membershipUpdatedAt"),
+          db.ref("slug").withSchema(TableName.ProjectRoles).as("customRoleSlug")
+        )
+        .select("permissions");
+
+      const permission = sqlNestRelationships({
+        data: docs,
+        key: "membershipId",
+        parentMapper: ({ membershipId, membershipCreatedAt, membershipUpdatedAt, oldRoleField }) => ({
+          id: membershipId,
+          identityId,
+          projectId,
+          role: oldRoleField,
+          createdAt: membershipCreatedAt,
+          updatedAt: membershipUpdatedAt,
+          // just a prefilled value
+          orgAuthEnforced: false,
+          orgId: ""
+        }),
+        childrenMapper: [
+          {
+            key: "id",
+            label: "roles" as const,
+            mapper: (data) =>
+              IdentityProjectMembershipRoleSchema.extend({
+                permissions: z.unknown(),
+                customRoleSlug: z.string().optional().nullable()
+              }).parse(data)
+          }
+        ]
+      });
+
+      // when introducting cron mode change it here
+      const activeRoles = permission?.[0]?.roles.filter(
+        ({ isTemporary, temporaryAccessEndTime }) =>
+          !isTemporary || (isTemporary && temporaryAccessEndTime && new Date() < temporaryAccessEndTime)
+      );
+      return permission?.[0] ? { ...permission[0], roles: activeRoles } : undefined;
     } catch (error) {
       throw new DatabaseError({ error, name: "GetProjectIdentityPermission" });
     }

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

@@ -18,6 +18,7 @@ import { TServiceTokenDALFactory } from "@app/services/service-token/service-tok
 
 import { orgAdminPermissions, orgMemberPermissions, orgNoAccessPermissions, OrgPermissionSet } from "./org-permission";
 import { TPermissionDALFactory } from "./permission-dal";
+import { TBuildProjectPermissionDTO } from "./permission-types";
 import {
   buildServiceTokenProjectPermission,
   projectAdminPermissions,
@@ -64,31 +65,35 @@ export const permissionServiceFactory = ({
     }
   };
 
-  const buildProjectPermission = (role: string, permission?: unknown) => {
-    switch (role) {
-      case ProjectMembershipRole.Admin:
-        return projectAdminPermissions;
-      case ProjectMembershipRole.Member:
-        return projectMemberPermissions;
-      case ProjectMembershipRole.Viewer:
-        return projectViewerPermission;
-      case ProjectMembershipRole.NoAccess:
-        return projectNoAccessPermissions;
-      case ProjectMembershipRole.Custom:
-        return createMongoAbility<ProjectPermissionSet>(
-          unpackRules<RawRuleOf<MongoAbility<ProjectPermissionSet>>>(
-            permission as PackRule<RawRuleOf<MongoAbility<ProjectPermissionSet>>>[]
-          ),
-          {
-            conditionsMatcher
+  const buildProjectPermission = (projectUserRoles: TBuildProjectPermissionDTO) => {
+    const rules = projectUserRoles
+      .map(({ role, permissions }) => {
+        switch (role) {
+          case ProjectMembershipRole.Admin:
+            return projectAdminPermissions;
+          case ProjectMembershipRole.Member:
+            return projectMemberPermissions;
+          case ProjectMembershipRole.Viewer:
+            return projectViewerPermission;
+          case ProjectMembershipRole.NoAccess:
+            return projectNoAccessPermissions;
+          case ProjectMembershipRole.Custom: {
+            return unpackRules<RawRuleOf<MongoAbility<ProjectPermissionSet>>>(
+              permissions as PackRule<RawRuleOf<MongoAbility<ProjectPermissionSet>>>[]
+            );
           }
-        );
-      default:
-        throw new BadRequestError({
-          name: "ProjectRoleInvalid",
-          message: "Project role not found"
-        });
-    }
+          default:
+            throw new BadRequestError({
+              name: "ProjectRoleInvalid",
+              message: "Project role not found"
+            });
+        }
+      })
+      .reduce((curr, prev) => prev.concat(curr), []);
+
+    return createMongoAbility<ProjectPermissionSet>(rules, {
+      conditionsMatcher
+    });
   };
 
   /*
@@ -145,33 +150,56 @@ export const permissionServiceFactory = ({
   };
 
   // user permission for a project in an organization
-  const getUserProjectPermission = async (userId: string, projectId: string, userOrgId?: string) => {
-    const membership = await permissionDAL.getProjectPermission(userId, projectId);
-    if (!membership) throw new UnauthorizedError({ name: "User not in project" });
-    if (membership.role === ProjectMembershipRole.Custom && !membership.permissions) {
+  const getUserProjectPermission = async (
+    userId: string,
+    projectId: string,
+    userOrgId?: string
+  ): Promise<TProjectPermissionRT<ActorType.USER>> => {
+    const userProjectPermission = await permissionDAL.getProjectPermission(userId, projectId);
+    if (!userProjectPermission) throw new UnauthorizedError({ name: "User not in project" });
+
+    if (
+      userProjectPermission.roles.some(({ role, permissions }) => role === ProjectMembershipRole.Custom && !permissions)
+    ) {
       throw new BadRequestError({ name: "Custom permission not found" });
     }
 
-    if (membership.orgAuthEnforced && membership.orgId !== userOrgId) {
+    if (userProjectPermission.orgAuthEnforced && userProjectPermission.orgId !== userOrgId) {
       throw new BadRequestError({ name: "Cannot access org-scoped resource" });
     }
 
     return {
-      permission: buildProjectPermission(membership.role, membership.permissions),
-      membership
+      permission: buildProjectPermission(userProjectPermission.roles),
+      membership: userProjectPermission,
+      hasRole: (role: string) =>
+        userProjectPermission.roles.findIndex(
+          ({ role: slug, customRoleSlug }) => role === slug || slug === customRoleSlug
+        ) !== -1
     };
   };
 
-  const getIdentityProjectPermission = async (identityId: string, projectId: string) => {
-    const membership = await permissionDAL.getProjectIdentityPermission(identityId, projectId);
-    if (!membership) throw new UnauthorizedError({ name: "Identity not in project" });
-    if (membership.role === ProjectMembershipRole.Custom && !membership.permissions) {
+  const getIdentityProjectPermission = async (
+    identityId: string,
+    projectId: string
+  ): Promise<TProjectPermissionRT<ActorType.IDENTITY>> => {
+    const identityProjectPermission = await permissionDAL.getProjectIdentityPermission(identityId, projectId);
+    if (!identityProjectPermission) throw new UnauthorizedError({ name: "Identity not in project" });
+
+    if (
+      identityProjectPermission.roles.some(
+        ({ role, permissions }) => role === ProjectMembershipRole.Custom && !permissions
+      )
+    ) {
       throw new BadRequestError({ name: "Custom permission not found" });
     }
 
     return {
-      permission: buildProjectPermission(membership.role, membership.permissions),
-      membership
+      permission: buildProjectPermission(identityProjectPermission.roles),
+      membership: identityProjectPermission,
+      hasRole: (role: string) =>
+        identityProjectPermission.roles.findIndex(
+          ({ role: slug, customRoleSlug }) => role === slug || slug === customRoleSlug
+        ) !== -1
     };
   };
 
@@ -191,14 +219,19 @@ export const permissionServiceFactory = ({
   };
 
   type TProjectPermissionRT<T extends ActorType> = T extends ActorType.SERVICE
-    ? { permission: MongoAbility<ProjectPermissionSet, MongoQuery>; membership: undefined }
+    ? {
+        permission: MongoAbility<ProjectPermissionSet, MongoQuery>;
+        membership: undefined;
+        hasRole: (arg: string) => boolean;
+      } // service token doesn't have both membership and roles
     : {
         permission: MongoAbility<ProjectPermissionSet, MongoQuery>;
         membership: (T extends ActorType.USER ? TProjectMemberships : TIdentityProjectMemberships) & {
-          orgAuthEnforced: boolean;
+          orgAuthEnforced: boolean | null | undefined;
           orgId: string;
-          permissions?: unknown;
+          roles: Array<{ role: string }>;
         };
+        hasRole: (role: string) => boolean;
       };
 
   const getProjectPermission = async <T extends ActorType>(
@@ -228,11 +261,13 @@ export const permissionServiceFactory = ({
       const projectRole = await projectRoleDAL.findOne({ slug: role, projectId });
       if (!projectRole) throw new BadRequestError({ message: "Role not found" });
       return {
-        permission: buildProjectPermission(ProjectMembershipRole.Custom, projectRole.permissions),
+        permission: buildProjectPermission([
+          { role: ProjectMembershipRole.Custom, permissions: projectRole.permissions }
+        ]),
         role: projectRole
       };
     }
-    return { permission: buildProjectPermission(role, []) };
+    return { permission: buildProjectPermission([{ role, permissions: [] }]) };
   };
 
   return {

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

@@ -0,0 +1,4 @@
+export type TBuildProjectPermissionDTO = {
+  permissions?: unknown;
+  role: string;
+}[];

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

@@ -56,8 +56,8 @@ export type ProjectPermissionSet =
   | [ProjectPermissionActions.Read, ProjectPermissionSub.SecretRollback]
   | [ProjectPermissionActions.Create, ProjectPermissionSub.SecretRollback];
 
-const buildAdminPermission = () => {
-  const { can, build } = new AbilityBuilder<MongoAbility<ProjectPermissionSet>>(createMongoAbility);
+const buildAdminPermissionRules = () => {
+  const { can, rules } = new AbilityBuilder<MongoAbility<ProjectPermissionSet>>(createMongoAbility);
 
   can(ProjectPermissionActions.Read, ProjectPermissionSub.Secrets);
   can(ProjectPermissionActions.Create, ProjectPermissionSub.Secrets);
@@ -135,13 +135,13 @@ const buildAdminPermission = () => {
   can(ProjectPermissionActions.Edit, ProjectPermissionSub.Project);
   can(ProjectPermissionActions.Delete, ProjectPermissionSub.Project);
 
-  return build({ conditionsMatcher });
+  return rules;
 };
 
-export const projectAdminPermissions = buildAdminPermission();
+export const projectAdminPermissions = buildAdminPermissionRules();
 
-const buildMemberPermission = () => {
-  const { can, build } = new AbilityBuilder<MongoAbility<ProjectPermissionSet>>(createMongoAbility);
+const buildMemberPermissionRules = () => {
+  const { can, rules } = new AbilityBuilder<MongoAbility<ProjectPermissionSet>>(createMongoAbility);
 
   can(ProjectPermissionActions.Read, ProjectPermissionSub.Secrets);
   can(ProjectPermissionActions.Create, ProjectPermissionSub.Secrets);
@@ -196,13 +196,13 @@ const buildMemberPermission = () => {
   can(ProjectPermissionActions.Read, ProjectPermissionSub.AuditLogs);
   can(ProjectPermissionActions.Read, ProjectPermissionSub.IpAllowList);
 
-  return build({ conditionsMatcher });
+  return rules;
 };
 
-export const projectMemberPermissions = buildMemberPermission();
+export const projectMemberPermissions = buildMemberPermissionRules();
 
-const buildViewerPermission = () => {
-  const { can, build } = new AbilityBuilder<MongoAbility<ProjectPermissionSet>>(createMongoAbility);
+const buildViewerPermissionRules = () => {
+  const { can, rules } = new AbilityBuilder<MongoAbility<ProjectPermissionSet>>(createMongoAbility);
 
   can(ProjectPermissionActions.Read, ProjectPermissionSub.Secrets);
   can(ProjectPermissionActions.Read, ProjectPermissionSub.SecretApproval);
@@ -220,14 +220,14 @@ const buildViewerPermission = () => {
   can(ProjectPermissionActions.Read, ProjectPermissionSub.AuditLogs);
   can(ProjectPermissionActions.Read, ProjectPermissionSub.IpAllowList);
 
-  return build({ conditionsMatcher });
+  return rules;
 };
 
-export const projectViewerPermission = buildViewerPermission();
+export const projectViewerPermission = buildViewerPermissionRules();
 
 const buildNoAccessProjectPermission = () => {
-  const { build } = new AbilityBuilder<MongoAbility<ProjectPermissionSet>>(createMongoAbility);
-  return build({ conditionsMatcher });
+  const { rules } = new AbilityBuilder<MongoAbility<ProjectPermissionSet>>(createMongoAbility);
+  return rules;
 };
 
 export const buildServiceTokenProjectPermission = (

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

@@ -5,6 +5,7 @@ import {
   OrgMembershipRole,
   OrgMembershipStatus,
   SecretKeyEncoding,
+  TableName,
   TSamlConfigs,
   TSamlConfigsUpdate
 } from "@app/db/schemas";
@@ -31,7 +32,7 @@ import { TCreateSamlCfgDTO, TGetSamlCfgDTO, TSamlLoginDTO, TUpdateSamlCfgDTO } f
 
 type TSamlConfigServiceFactoryDep = {
   samlConfigDAL: TSamlConfigDALFactory;
-  userDAL: Pick<TUserDALFactory, "create" | "findUserByEmail" | "transaction" | "updateById">;
+  userDAL: Pick<TUserDALFactory, "create" | "findOne" | "transaction" | "updateById">;
   orgDAL: Pick<
     TOrgDALFactory,
     "createMembership" | "updateMembershipById" | "findMembership" | "findOrgById" | "findOne" | "updateById"
@@ -69,7 +70,7 @@ export const samlConfigServiceFactory = ({
     if (!plan.samlSSO)
       throw new BadRequestError({
         message:
-          "Failed to update SAML SSO configuration due to plan restriction. Upgrade plan to update SSO configuration."
+          "Failed to create SAML SSO configuration due to plan restriction. Upgrade plan to create SSO configuration."
       });
 
     const orgBot = await orgBotDAL.transaction(async (tx) => {
@@ -122,7 +123,6 @@ export const samlConfigServiceFactory = ({
 
     const { ciphertext: encryptedEntryPoint, iv: entryPointIV, tag: entryPointTag } = encryptSymmetric(entryPoint, key);
     const { ciphertext: encryptedIssuer, iv: issuerIV, tag: issuerTag } = encryptSymmetric(issuer, key);
-
     const { ciphertext: encryptedCert, iv: certIV, tag: certTag } = encryptSymmetric(cert, key);
     const samlConfig = await samlConfigDAL.create({
       orgId,
@@ -300,16 +300,30 @@ export const samlConfigServiceFactory = ({
     };
   };
 
-  const samlLogin = async ({ firstName, email, lastName, authProvider, orgId, relayState }: TSamlLoginDTO) => {
+  const samlLogin = async ({
+    username,
+    email,
+    firstName,
+    lastName,
+    authProvider,
+    orgId,
+    relayState
+  }: TSamlLoginDTO) => {
     const appCfg = getConfig();
-    let user = await userDAL.findUserByEmail(email);
+    let user = await userDAL.findOne({ username });
 
     const organization = await orgDAL.findOrgById(orgId);
     if (!organization) throw new BadRequestError({ message: "Org not found" });
 
     if (user) {
       await userDAL.transaction(async (tx) => {
-        const [orgMembership] = await orgDAL.findMembership({ userId: user.id, orgId }, { tx });
+        const [orgMembership] = await orgDAL.findMembership(
+          {
+            userId: user.id,
+            [`${TableName.OrgMembership}.orgId` as "id"]: orgId
+          },
+          { tx }
+        );
         if (!orgMembership) {
           await orgDAL.createMembership(
             {
@@ -335,6 +349,7 @@ export const samlConfigServiceFactory = ({
       user = await userDAL.transaction(async (tx) => {
         const newUser = await userDAL.create(
           {
+            username,
             email,
             firstName,
             lastName,
@@ -357,7 +372,7 @@ export const samlConfigServiceFactory = ({
       {
         authTokenType: AuthTokenType.PROVIDER_TOKEN,
         userId: user.id,
-        email: user.email,
+        username: user.username,
         firstName,
         lastName,
         organizationName: organization.name,

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

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

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

@@ -20,34 +20,38 @@ export const buildScimUserList = ({
 
 export const buildScimUser = ({
   userId,
+  username,
+  email,
   firstName,
   lastName,
-  email,
   active
 }: {
   userId: string;
+  username: string;
+  email?: string | null;
   firstName: string;
   lastName: string;
-  email: string;
   active: boolean;
 }): TScimUser => {
-  return {
+  const scimUser = {
     schemas: ["urn:ietf:params:scim:schemas:core:2.0:User"],
     id: userId,
-    userName: email,
+    userName: username,
     displayName: `${firstName} ${lastName}`,
     name: {
       givenName: firstName,
       middleName: null,
       familyName: lastName
     },
-    emails: [
-      {
-        primary: true,
-        value: email,
-        type: "work"
-      }
-    ],
+    emails: email
+      ? [
+          {
+            primary: true,
+            value: email,
+            type: "work"
+          }
+        ]
+      : [],
     active,
     groups: [],
     meta: {
@@ -55,4 +59,6 @@ export const buildScimUser = ({
       location: null
     }
   };
+
+  return scimUser;
 };

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

@@ -1,7 +1,7 @@
 import { ForbiddenError } from "@casl/ability";
 import jwt from "jsonwebtoken";
 
-import { OrgMembershipRole, OrgMembershipStatus } from "@app/db/schemas";
+import { OrgMembershipRole, OrgMembershipStatus, TableName } from "@app/db/schemas";
 import { TScimDALFactory } from "@app/ee/services/scim/scim-dal";
 import { getConfig } from "@app/lib/config/env";
 import { BadRequestError, ScimRequestError, UnauthorizedError } from "@app/lib/errors";
@@ -146,15 +146,16 @@ export const scimServiceFactory = ({
 
     const users = await orgDAL.findMembership(
       {
-        orgId,
+        [`${TableName.OrgMembership}.orgId` as "id"]: orgId,
         ...parseFilter(filter)
       },
       findOpts
     );
 
-    const scimUsers = users.map(({ userId, firstName, lastName, email }) =>
+    const scimUsers = users.map(({ userId, username, firstName, lastName, email }) =>
       buildScimUser({
         userId: userId ?? "",
+        username,
         firstName: firstName ?? "",
         lastName: lastName ?? "",
         email,
@@ -173,7 +174,7 @@ export const scimServiceFactory = ({
     const [membership] = await orgDAL
       .findMembership({
         userId,
-        orgId
+        [`${TableName.OrgMembership}.orgId` as "id"]: orgId
       })
       .catch(() => {
         throw new ScimRequestError({
@@ -196,14 +197,15 @@ export const scimServiceFactory = ({
 
     return buildScimUser({
       userId: membership.userId as string,
+      username: membership.username,
+      email: membership.email ?? "",
       firstName: membership.firstName as string,
       lastName: membership.lastName as string,
-      email: membership.email,
       active: true
     });
   };
 
-  const createScimUser = async ({ firstName, lastName, email, orgId }: TCreateScimUserDTO) => {
+  const createScimUser = async ({ username, email, firstName, lastName, orgId }: TCreateScimUserDTO) => {
     const org = await orgDAL.findById(orgId);
 
     if (!org)
@@ -219,12 +221,18 @@ export const scimServiceFactory = ({
       });
 
     let user = await userDAL.findOne({
-      email
+      username
     });
 
     if (user) {
       await userDAL.transaction(async (tx) => {
-        const [orgMembership] = await orgDAL.findMembership({ userId: user.id, orgId }, { tx });
+        const [orgMembership] = await orgDAL.findMembership(
+          {
+            userId: user.id,
+            [`${TableName.OrgMembership}.orgId` as "id"]: orgId
+          },
+          { tx }
+        );
         if (orgMembership)
           throw new ScimRequestError({
             detail: "User already exists in the database",
@@ -248,6 +256,7 @@ export const scimServiceFactory = ({
       user = await userDAL.transaction(async (tx) => {
         const newUser = await userDAL.create(
           {
+            username,
             email,
             firstName,
             lastName,
@@ -272,21 +281,25 @@ export const scimServiceFactory = ({
     }
 
     const appCfg = getConfig();
-    await smtpService.sendMail({
-      template: SmtpTemplates.ScimUserProvisioned,
-      subjectLine: "Infisical organization invitation",
-      recipients: [email],
-      substitutions: {
-        organizationName: org.name,
-        callback_url: `${appCfg.SITE_URL}/api/v1/sso/redirect/saml2/organizations/${org.slug}`
-      }
-    });
+
+    if (email) {
+      await smtpService.sendMail({
+        template: SmtpTemplates.ScimUserProvisioned,
+        subjectLine: "Infisical organization invitation",
+        recipients: [email],
+        substitutions: {
+          organizationName: org.name,
+          callback_url: `${appCfg.SITE_URL}/api/v1/sso/redirect/saml2/organizations/${org.slug}`
+        }
+      });
+    }
 
     return buildScimUser({
       userId: user.id,
+      username: user.username,
       firstName: user.firstName as string,
       lastName: user.lastName as string,
-      email: user.email,
+      email: user.email ?? "",
       active: true
     });
   };
@@ -295,7 +308,7 @@ export const scimServiceFactory = ({
     const [membership] = await orgDAL
       .findMembership({
         userId,
-        orgId
+        [`${TableName.OrgMembership}.orgId` as "id"]: orgId
       })
       .catch(() => {
         throw new ScimRequestError({
@@ -342,9 +355,10 @@ export const scimServiceFactory = ({
 
     return buildScimUser({
       userId: membership.userId as string,
+      username: membership.username,
+      email: membership.email,
       firstName: membership.firstName as string,
       lastName: membership.lastName as string,
-      email: membership.email,
       active
     });
   };
@@ -353,7 +367,7 @@ export const scimServiceFactory = ({
     const [membership] = await orgDAL
       .findMembership({
         userId,
-        orgId
+        [`${TableName.OrgMembership}.orgId` as "id"]: orgId
       })
       .catch(() => {
         throw new ScimRequestError({
@@ -387,9 +401,10 @@ export const scimServiceFactory = ({
 
     return buildScimUser({
       userId: membership.userId as string,
+      username: membership.username,
+      email: membership.email,
       firstName: membership.firstName as string,
       lastName: membership.lastName as string,
-      email: membership.email,
       active
     });
   };

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

@@ -32,7 +32,8 @@ export type TGetScimUserDTO = {
 };
 
 export type TCreateScimUserDTO = {
-  email: string;
+  username: string;
+  email?: string;
   firstName: string;
   lastName: string;
   orgId: string;

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

@@ -12,9 +12,11 @@ import { groupBy, pick, unique } from "@app/lib/fn";
 import { alphaNumericNanoId } from "@app/lib/nanoid";
 import { ActorType } from "@app/services/auth/auth-type";
 import { TProjectDALFactory } from "@app/services/project/project-dal";
+import { TSecretDALFactory } from "@app/services/secret/secret-dal";
 import { TSecretQueueFactory } from "@app/services/secret/secret-queue";
 import { TSecretServiceFactory } from "@app/services/secret/secret-service";
 import { TSecretVersionDALFactory } from "@app/services/secret/secret-version-dal";
+import { TSecretVersionTagDALFactory } from "@app/services/secret/secret-version-tag-dal";
 import { TSecretBlindIndexDALFactory } from "@app/services/secret-blind-index/secret-blind-index-dal";
 import { TSecretFolderDALFactory } from "@app/services/secret-folder/secret-folder-dal";
 import { TSecretTagDALFactory } from "@app/services/secret-tag/secret-tag-dal";
@@ -44,10 +46,12 @@ type TSecretApprovalRequestServiceFactoryDep = {
   secretApprovalRequestSecretDAL: TSecretApprovalRequestSecretDALFactory;
   secretApprovalRequestReviewerDAL: TSecretApprovalRequestReviewerDALFactory;
   folderDAL: Pick<TSecretFolderDALFactory, "findBySecretPath" | "findById" | "findSecretPathByFolderIds">;
-  secretTagDAL: Pick<TSecretTagDALFactory, "findManyTagsById">;
+  secretDAL: TSecretDALFactory;
+  secretTagDAL: Pick<TSecretTagDALFactory, "findManyTagsById" | "saveTagsToSecret" | "deleteTagsManySecret">;
   secretBlindIndexDAL: Pick<TSecretBlindIndexDALFactory, "findOne">;
   snapshotService: Pick<TSecretSnapshotServiceFactory, "performSnapshot">;
-  secretVersionDAL: Pick<TSecretVersionDALFactory, "findLatestVersionMany">;
+  secretVersionDAL: Pick<TSecretVersionDALFactory, "findLatestVersionMany" | "insertMany">;
+  secretVersionTagDAL: Pick<TSecretVersionTagDALFactory, "insertMany">;
   projectDAL: Pick<TProjectDALFactory, "checkProjectUpgradeStatus">;
   secretService: Pick<
     TSecretServiceFactory,
@@ -64,8 +68,10 @@ export type TSecretApprovalRequestServiceFactory = ReturnType<typeof secretAppro
 
 export const secretApprovalRequestServiceFactory = ({
   secretApprovalRequestDAL,
+  secretDAL,
   folderDAL,
   secretTagDAL,
+  secretVersionTagDAL,
   secretApprovalRequestReviewerDAL,
   secretApprovalRequestSecretDAL,
   secretBlindIndexDAL,
@@ -123,14 +129,14 @@ export const secretApprovalRequestServiceFactory = ({
     if (!secretApprovalRequest) throw new BadRequestError({ message: "Secret approval request not found" });
 
     const { policy } = secretApprovalRequest;
-    const { membership } = await permissionService.getProjectPermission(
+    const { membership, hasRole } = await permissionService.getProjectPermission(
       actor,
       actorId,
       secretApprovalRequest.projectId,
       actorOrgId
     );
     if (
-      membership.role !== ProjectMembershipRole.Admin &&
+      !hasRole(ProjectMembershipRole.Admin) &&
       secretApprovalRequest.committerId !== membership.id &&
       !policy.approvers.find((approverId) => approverId === membership.id)
     ) {
@@ -150,14 +156,14 @@ export const secretApprovalRequestServiceFactory = ({
     if (actor !== ActorType.USER) throw new BadRequestError({ message: "Must be a user" });
 
     const { policy } = secretApprovalRequest;
-    const { membership } = await permissionService.getProjectPermission(
+    const { membership, hasRole } = await permissionService.getProjectPermission(
       ActorType.USER,
       actorId,
       secretApprovalRequest.projectId,
       actorOrgId
     );
     if (
-      membership.role !== ProjectMembershipRole.Admin &&
+      !hasRole(ProjectMembershipRole.Admin) &&
       secretApprovalRequest.committerId !== membership.id &&
       !policy.approvers.find((approverId) => approverId === membership.id)
     ) {
@@ -192,14 +198,14 @@ export const secretApprovalRequestServiceFactory = ({
     if (actor !== ActorType.USER) throw new BadRequestError({ message: "Must be a user" });
 
     const { policy } = secretApprovalRequest;
-    const { membership } = await permissionService.getProjectPermission(
+    const { membership, hasRole } = await permissionService.getProjectPermission(
       ActorType.USER,
       actorId,
       secretApprovalRequest.projectId,
       actorOrgId
     );
     if (
-      membership.role !== ProjectMembershipRole.Admin &&
+      !hasRole(ProjectMembershipRole.Admin) &&
       secretApprovalRequest.committerId !== membership.id &&
       !policy.approvers.find((approverId) => approverId === membership.id)
     ) {
@@ -230,9 +236,14 @@ export const secretApprovalRequestServiceFactory = ({
     if (actor !== ActorType.USER) throw new BadRequestError({ message: "Must be a user" });
 
     const { policy, folderId, projectId } = secretApprovalRequest;
-    const { membership } = await permissionService.getProjectPermission(ActorType.USER, actorId, projectId, actorOrgId);
+    const { membership, hasRole } = await permissionService.getProjectPermission(
+      ActorType.USER,
+      actorId,
+      projectId,
+      actorOrgId
+    );
     if (
-      membership.role !== ProjectMembershipRole.Admin &&
+      !hasRole(ProjectMembershipRole.Admin) &&
       secretApprovalRequest.committerId !== membership.id &&
       !policy.approvers.find((approverId) => approverId === membership.id)
     ) {
@@ -335,7 +346,11 @@ export const secretApprovalRequestServiceFactory = ({
               tags: el?.tags.map(({ id }) => id),
               version: 1,
               type: SecretType.Shared
-            }))
+            })),
+            secretDAL,
+            secretVersionDAL,
+            secretTagDAL,
+            secretVersionTagDAL
           })
         : [];
       const updatedSecrets = secretUpdationCommits.length
@@ -367,7 +382,11 @@ export const secretApprovalRequestServiceFactory = ({
                   "secretBlindIndex"
                 ])
               }
-            }))
+            })),
+            secretDAL,
+            secretVersionDAL,
+            secretTagDAL,
+            secretVersionTagDAL
           })
         : [];
       const deletedSecret = secretDeletionCommits.length
@@ -455,7 +474,8 @@ export const secretApprovalRequestServiceFactory = ({
         inputSecrets: createdSecrets,
         folderId,
         isNew: true,
-        blindIndexCfg
+        blindIndexCfg,
+        secretDAL
       });
 
       commits.push(
@@ -482,7 +502,8 @@ export const secretApprovalRequestServiceFactory = ({
         inputSecrets: updatedSecrets,
         folderId,
         isNew: false,
-        blindIndexCfg
+        blindIndexCfg,
+        secretDAL
       });
 
       // now find any secret that needs to update its name
@@ -492,7 +513,8 @@ export const secretApprovalRequestServiceFactory = ({
         inputSecrets: nameUpdatedSecrets,
         folderId,
         isNew: true,
-        blindIndexCfg
+        blindIndexCfg,
+        secretDAL
       });
 
       const secsGroupedByBlindIndex = groupBy(secretsToBeUpdated, (el) => el.secretBlindIndex as string);
@@ -531,7 +553,8 @@ export const secretApprovalRequestServiceFactory = ({
         inputSecrets: deletedSecrets,
         folderId,
         isNew: false,
-        blindIndexCfg
+        blindIndexCfg,
+        secretDAL
       });
       const secretsGroupedByBlindIndex = groupBy(secrets, (i) => {
         if (!i.secretBlindIndex) throw new BadRequestError({ message: "Missing secret blind index" });

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

@@ -64,7 +64,7 @@ export const secretScanningQueueFactory = ({
       orgId: organizationId,
       role: OrgMembershipRole.Admin
     });
-    return adminsOfWork.map((userObject) => userObject.email);
+    return adminsOfWork.filter((userObject) => userObject.email).map((userObject) => userObject.email as string);
   };
 
   queueService.start(QueueName.SecretPushEventScan, async (job) => {
@@ -149,7 +149,7 @@ export const secretScanningQueueFactory = ({
       await smtpService.sendMail({
         template: SmtpTemplates.SecretLeakIncident,
         subjectLine: `Incident alert: leaked secrets found in Github repository ${repository.fullName}`,
-        recipients: adminEmails,
+        recipients: adminEmails.filter((email) => email).map((email) => email),
         substitutions: {
           numberOfSecrets: Object.keys(allFindingsByFingerprint).length,
           pusher_email: pusher.email,
@@ -221,7 +221,7 @@ export const secretScanningQueueFactory = ({
       await smtpService.sendMail({
         template: SmtpTemplates.SecretLeakIncident,
         subjectLine: `Incident alert: leaked secrets found in Github repository ${repository.fullName}`,
-        recipients: adminEmails,
+        recipients: adminEmails.filter((email) => email).map((email) => email),
         substitutions: {
           numberOfSecrets: findings.length
         }

backend/src/server/lib/telemetry.ts

@@ -5,7 +5,7 @@ import { ActorType } from "@app/services/auth/auth-type";
 // this is a unique id for sending posthog event
 export const getTelemetryDistinctId = (req: FastifyRequest) => {
   if (req.auth.actor === ActorType.USER) {
-    return req.auth.user.email;
+    return req.auth.user.username;
   }
   if (req.auth.actor === ActorType.IDENTITY) {
     return `identity-${req.auth.identityId}`;

backend/src/server/plugins/audit-log.ts

@@ -44,6 +44,7 @@ export const injectAuditLogInfo = fp(async (server: FastifyZodProvider) => {
         type: ActorType.USER,
         metadata: {
           email: req.auth.user.email,
+          username: req.auth.user.username,
           userId: req.permission.id
         }
       };

backend/src/server/routes/index.ts

@@ -5,6 +5,8 @@ import { registerV1EERoutes } from "@app/ee/routes/v1";
 import { auditLogDALFactory } from "@app/ee/services/audit-log/audit-log-dal";
 import { auditLogQueueServiceFactory } from "@app/ee/services/audit-log/audit-log-queue";
 import { auditLogServiceFactory } from "@app/ee/services/audit-log/audit-log-service";
+import { ldapConfigDALFactory } from "@app/ee/services/ldap-config/ldap-config-dal";
+import { ldapConfigServiceFactory } from "@app/ee/services/ldap-config/ldap-config-service";
 import { licenseDALFactory } from "@app/ee/services/license/license-dal";
 import { licenseServiceFactory } from "@app/ee/services/license/license-service";
 import { permissionDALFactory } from "@app/ee/services/permission/permission-dal";
@@ -51,6 +53,7 @@ import { identityServiceFactory } from "@app/services/identity/identity-service"
 import { identityAccessTokenDALFactory } from "@app/services/identity-access-token/identity-access-token-dal";
 import { identityAccessTokenServiceFactory } from "@app/services/identity-access-token/identity-access-token-service";
 import { identityProjectDALFactory } from "@app/services/identity-project/identity-project-dal";
+import { identityProjectMembershipRoleDALFactory } from "@app/services/identity-project/identity-project-membership-role-dal";
 import { identityProjectServiceFactory } from "@app/services/identity-project/identity-project-service";
 import { identityUaClientSecretDALFactory } from "@app/services/identity-ua/identity-ua-client-secret-dal";
 import { identityUaDALFactory } from "@app/services/identity-ua/identity-ua-dal";
@@ -76,6 +79,7 @@ import { projectKeyDALFactory } from "@app/services/project-key/project-key-dal"
 import { projectKeyServiceFactory } from "@app/services/project-key/project-key-service";
 import { projectMembershipDALFactory } from "@app/services/project-membership/project-membership-dal";
 import { projectMembershipServiceFactory } from "@app/services/project-membership/project-membership-service";
+import { projectUserMembershipRoleDALFactory } from "@app/services/project-membership/project-user-membership-role-dal";
 import { projectRoleDALFactory } from "@app/services/project-role/project-role-dal";
 import { projectRoleServiceFactory } from "@app/services/project-role/project-role-service";
 import { secretDALFactory } from "@app/services/secret/secret-dal";
@@ -102,6 +106,7 @@ import { telemetryQueueServiceFactory } from "@app/services/telemetry/telemetry-
 import { telemetryServiceFactory } from "@app/services/telemetry/telemetry-service";
 import { userDALFactory } from "@app/services/user/user-dal";
 import { userServiceFactory } from "@app/services/user/user-service";
+import { userAliasDALFactory } from "@app/services/user-alias/user-alias-dal";
 import { webhookDALFactory } from "@app/services/webhook/webhook-dal";
 import { webhookServiceFactory } from "@app/services/webhook/webhook-service";
 
@@ -126,6 +131,7 @@ export const registerRoutes = async (
 
   // db layers
   const userDAL = userDALFactory(db);
+  const userAliasDAL = userAliasDALFactory(db);
   const authDAL = authDALFactory(db);
   const authTokenDAL = tokenDALFactory(db);
   const orgDAL = orgDALFactory(db);
@@ -137,6 +143,7 @@ export const registerRoutes = async (
 
   const projectDAL = projectDALFactory(db);
   const projectMembershipDAL = projectMembershipDALFactory(db);
+  const projectUserMembershipRoleDAL = projectUserMembershipRoleDALFactory(db);
   const projectRoleDAL = projectRoleDALFactory(db);
   const projectEnvDAL = projectEnvDALFactory(db);
   const projectKeyDAL = projectKeyDALFactory(db);
@@ -160,18 +167,20 @@ export const registerRoutes = async (
   const identityAccessTokenDAL = identityAccessTokenDALFactory(db);
   const identityOrgMembershipDAL = identityOrgDALFactory(db);
   const identityProjectDAL = identityProjectDALFactory(db);
+  const identityProjectMembershipRoleDAL = identityProjectMembershipRoleDALFactory(db);
 
   const identityUaDAL = identityUaDALFactory(db);
   const identityUaClientSecretDAL = identityUaClientSecretDALFactory(db);
 
   const auditLogDAL = auditLogDALFactory(db);
   const trustedIpDAL = trustedIpDALFactory(db);
-  const scimDAL = scimDALFactory(db);
   const telemetryDAL = telemetryDALFactory(db);
 
   // ee db layer ops
   const permissionDAL = permissionDALFactory(db);
   const samlConfigDAL = samlConfigDALFactory(db);
+  const scimDAL = scimDALFactory(db);
+  const ldapConfigDAL = ldapConfigDALFactory(db);
   const sapApproverDAL = secretApprovalPolicyApproverDALFactory(db);
   const secretApprovalPolicyDAL = secretApprovalPolicyDALFactory(db);
   const secretApprovalRequestDAL = secretApprovalRequestDALFactory(db);
@@ -235,6 +244,16 @@ export const registerRoutes = async (
     smtpService
   });
 
+  const ldapService = ldapConfigServiceFactory({
+    ldapConfigDAL,
+    orgDAL,
+    orgBotDAL,
+    userDAL,
+    userAliasDAL,
+    permissionService,
+    licenseService
+  });
+
   const telemetryService = telemetryServiceFactory({
     keyStore,
     licenseService
@@ -306,6 +325,7 @@ export const registerRoutes = async (
 
   const projectMembershipService = projectMembershipServiceFactory({
     projectMembershipDAL,
+    projectUserMembershipRoleDAL,
     projectDAL,
     permissionService,
     projectBotDAL,
@@ -337,7 +357,8 @@ export const registerRoutes = async (
     projectBotDAL,
     projectMembershipDAL,
     secretApprovalRequestDAL,
-    secretApprovalSecretDAL: sarSecretDAL
+    secretApprovalSecretDAL: sarSecretDAL,
+    projectUserMembershipRoleDAL
   });
 
   const projectService = projectServiceFactory({
@@ -354,8 +375,11 @@ export const registerRoutes = async (
     orgService,
     projectMembershipDAL,
     folderDAL,
-    licenseService
+    licenseService,
+    projectUserMembershipRoleDAL,
+    identityProjectMembershipRoleDAL
   });
+
   const projectEnvService = projectEnvServiceFactory({
     permissionService,
     projectEnvDAL,
@@ -421,7 +445,12 @@ export const registerRoutes = async (
     orgDAL,
     projectMembershipDAL,
     smtpService,
-    projectDAL
+    projectDAL,
+    projectBotDAL,
+    secretVersionDAL,
+    secretBlindIndexDAL,
+    secretTagDAL,
+    secretVersionTagDAL
   });
   const secretBlindIndexService = secretBlindIndexServiceFactory({
     permissionService,
@@ -445,6 +474,7 @@ export const registerRoutes = async (
   const sarService = secretApprovalRequestServiceFactory({
     permissionService,
     folderDAL,
+    secretDAL,
     secretTagDAL,
     secretApprovalRequestSecretDAL: sarSecretDAL,
     secretApprovalRequestReviewerDAL: sarReviewerDAL,
@@ -454,6 +484,7 @@ export const registerRoutes = async (
     secretApprovalRequestDAL,
     secretService,
     snapshotService,
+    secretVersionTagDAL,
     secretQueueService
   });
   const secretRotationQueue = secretRotationQueueFactory({
@@ -499,7 +530,9 @@ export const registerRoutes = async (
     permissionService,
     projectDAL,
     identityProjectDAL,
-    identityOrgMembershipDAL
+    identityOrgMembershipDAL,
+    identityProjectMembershipRoleDAL,
+    projectRoleDAL
   });
   const identityUaService = identityUaServiceFactory({
     identityOrgMembershipDAL,
@@ -554,6 +587,7 @@ export const registerRoutes = async (
     secretRotation: secretRotationService,
     snapshot: snapshotService,
     saml: samlService,
+    ldap: ldapService,
     auditLog: auditLogService,
     secretScanning: secretScanningService,
     license: licenseService,

backend/src/server/routes/v1/admin-router.ts

@@ -92,9 +92,10 @@ export const registerAdminRouter = async (server: FastifyZodProvider) => {
 
       await server.services.telemetry.sendPostHogEvents({
         event: PostHogEventTypes.AdminInit,
-        distinctId: user.user.email,
+        distinctId: user.user.username ?? "",
         properties: {
-          email: user.user.email,
+          username: user.user.username,
+          email: user.user.email ?? "",
           lastName: user.user.lastName || "",
           firstName: user.user.firstName || ""
         }

backend/src/server/routes/v1/integration-auth-router.ts

@@ -513,6 +513,37 @@ export const registerIntegrationAuthRouter = async (server: FastifyZodProvider)
     }
   });
 
+  server.route({
+    url: "/:integrationAuthId/heroku/pipelines",
+    method: "GET",
+    onRequest: verifyAuth([AuthMode.JWT]),
+    schema: {
+      params: z.object({
+        integrationAuthId: z.string().trim()
+      }),
+      response: {
+        200: z.object({
+          pipelines: z
+            .object({
+              app: z.object({ appId: z.string() }),
+              stage: z.string(),
+              pipeline: z.object({ name: z.string(), pipelineId: z.string() })
+            })
+            .array()
+        })
+      }
+    },
+    handler: async (req) => {
+      const pipelines = await server.services.integrationAuth.getHerokuPipelines({
+        actorId: req.permission.id,
+        actor: req.permission.type,
+        actorOrgId: req.permission.orgId,
+        id: req.params.integrationAuthId
+      });
+      return { pipelines };
+    }
+  });
+
   server.route({
     url: "/:integrationAuthId/railway/environments",
     method: "GET",

backend/src/server/routes/v1/integration-router.ts

@@ -32,6 +32,7 @@ export const registerIntegrationRouter = async (server: FastifyZodProvider) => {
           .object({
             secretPrefix: z.string().optional(),
             secretSuffix: z.string().optional(),
+            initialSyncBehavior: z.string().optional(),
             secretGCPLabel: z
               .object({
                 labelName: z.string(),

backend/src/server/routes/v1/organization-router.ts

@@ -58,6 +58,7 @@ export const registerOrgRouter = async (server: FastifyZodProvider) => {
           users: OrgMembershipsSchema.merge(
             z.object({
               user: UsersSchema.pick({
+                username: true,
                 email: true,
                 firstName: true,
                 lastName: true,

backend/src/server/routes/v1/project-membership-router.ts

@@ -1,15 +1,17 @@
+import ms from "ms";
 import { z } from "zod";
 
 import {
   OrgMembershipsSchema,
-  ProjectMembershipRole,
   ProjectMembershipsSchema,
+  ProjectUserMembershipRolesSchema,
   UserEncryptionKeysSchema,
   UsersSchema
 } from "@app/db/schemas";
 import { EventType } from "@app/ee/services/audit-log/audit-log-types";
 import { verifyAuth } from "@app/server/plugins/auth/verify-auth";
 import { AuthMode } from "@app/services/auth/auth-type";
+import { ProjectUserMembershipTemporaryMode } from "@app/services/project-membership/project-membership-types";
 
 export const registerProjectMembershipRouter = async (server: FastifyZodProvider) => {
   server.route({
@@ -28,16 +30,31 @@ export const registerProjectMembershipRouter = async (server: FastifyZodProvider
       }),
       response: {
         200: z.object({
-          memberships: ProjectMembershipsSchema.merge(
-            z.object({
-              user: UsersSchema.pick({
-                email: true,
-                firstName: true,
-                lastName: true,
-                id: true
-              }).merge(UserEncryptionKeysSchema.pick({ publicKey: true }))
-            })
-          )
+          memberships: ProjectMembershipsSchema.omit({ role: true })
+            .merge(
+              z.object({
+                user: UsersSchema.pick({
+                  email: true,
+                  firstName: true,
+                  lastName: true,
+                  id: true
+                }).merge(UserEncryptionKeysSchema.pick({ publicKey: true })),
+                roles: z.array(
+                  z.object({
+                    id: z.string(),
+                    role: z.string(),
+                    customRoleId: z.string().optional().nullable(),
+                    customRoleName: z.string().optional().nullable(),
+                    customRoleSlug: z.string().optional().nullable(),
+                    isTemporary: z.boolean(),
+                    temporaryMode: z.string().optional().nullable(),
+                    temporaryRange: z.string().nullable().optional(),
+                    temporaryAccessStartTime: z.date().nullable().optional(),
+                    temporaryAccessEndTime: z.date().nullable().optional()
+                  })
+                )
+              })
+            )
             .omit({ createdAt: true, updatedAt: true })
             .array()
         })
@@ -86,10 +103,7 @@ export const registerProjectMembershipRouter = async (server: FastifyZodProvider
         actor: req.permission.type,
         actorOrgId: req.permission.orgId,
         projectId: req.params.workspaceId,
-        members: req.body.members.map((member) => ({
-          ...member,
-          projectRole: ProjectMembershipRole.Member
-        }))
+        members: req.body.members
       });
 
       await server.services.auditLog.createAuditLog({
@@ -124,39 +138,56 @@ export const registerProjectMembershipRouter = async (server: FastifyZodProvider
         membershipId: z.string().trim()
       }),
       body: z.object({
-        role: z.string().trim()
+        roles: z
+          .array(
+            z.union([
+              z.object({
+                role: z.string(),
+                isTemporary: z.literal(false).default(false)
+              }),
+              z.object({
+                role: z.string(),
+                isTemporary: z.literal(true),
+                temporaryMode: z.nativeEnum(ProjectUserMembershipTemporaryMode),
+                temporaryRange: z.string().refine((val) => ms(val) > 0, "Temporary range must be a positive number"),
+                temporaryAccessStartTime: z.string().datetime()
+              })
+            ])
+          )
+          .min(1)
+          .refine((data) => data.some(({ isTemporary }) => !isTemporary), "At least long lived role is required")
       }),
       response: {
         200: z.object({
-          membership: ProjectMembershipsSchema
+          roles: ProjectUserMembershipRolesSchema.array()
         })
       }
     },
     onRequest: verifyAuth([AuthMode.JWT, AuthMode.API_KEY, AuthMode.IDENTITY_ACCESS_TOKEN]),
     handler: async (req) => {
-      const membership = await server.services.projectMembership.updateProjectMembership({
+      const roles = await server.services.projectMembership.updateProjectMembership({
         actorId: req.permission.id,
         actor: req.permission.type,
         actorOrgId: req.permission.orgId,
         projectId: req.params.workspaceId,
         membershipId: req.params.membershipId,
-        role: req.body.role
+        roles: req.body.roles
       });
 
-      await server.services.auditLog.createAuditLog({
-        ...req.auditLogInfo,
-        projectId: req.params.workspaceId,
-        event: {
-          type: EventType.UPDATE_USER_WORKSPACE_ROLE,
-          metadata: {
-            userId: membership.userId,
-            newRole: req.body.role,
-            oldRole: membership.role,
-            email: ""
-          }
-        }
-      });
-      return { membership };
+      // await server.services.auditLog.createAuditLog({
+      //   ...req.auditLogInfo,
+      //   projectId: req.params.workspaceId,
+      //   event: {
+      //     type: EventType.UPDATE_USER_WORKSPACE_ROLE,
+      //     metadata: {
+      //       userId: membership.userId,
+      //       newRole: req.body.role,
+      //       oldRole: membership.role,
+      //       email: ""
+      //     }
+      //   }
+      // });
+      return { roles };
     }
   });
 

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

@@ -60,16 +60,32 @@ export const registerProjectRouter = async (server: FastifyZodProvider) => {
       }),
       response: {
         200: z.object({
-          users: ProjectMembershipsSchema.merge(
-            z.object({
-              user: UsersSchema.pick({
-                email: true,
-                firstName: true,
-                lastName: true,
-                id: true
-              }).merge(UserEncryptionKeysSchema.pick({ publicKey: true }))
-            })
-          )
+          users: ProjectMembershipsSchema.omit({ role: true })
+            .merge(
+              z.object({
+                user: UsersSchema.pick({
+                  username: true,
+                  email: true,
+                  firstName: true,
+                  lastName: true,
+                  id: true
+                }).merge(UserEncryptionKeysSchema.pick({ publicKey: true })),
+                roles: z.array(
+                  z.object({
+                    id: z.string(),
+                    role: z.string(),
+                    customRoleId: z.string().optional().nullable(),
+                    customRoleName: z.string().optional().nullable(),
+                    customRoleSlug: z.string().optional().nullable(),
+                    isTemporary: z.boolean(),
+                    temporaryMode: z.string().optional().nullable(),
+                    temporaryRange: z.string().nullable().optional(),
+                    temporaryAccessStartTime: z.date().nullable().optional(),
+                    temporaryAccessEndTime: z.date().nullable().optional()
+                  })
+                )
+              })
+            )
             .omit({ createdAt: true, updatedAt: true })
             .array()
         })

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

@@ -120,7 +120,7 @@ export const registerSecretFolderRouter = async (server: FastifyZodProvider) =>
   });
 
   server.route({
-    url: "/:folderId",
+    url: "/:folderIdOrName",
     method: "DELETE",
     schema: {
       description: "Delete a folder",
@@ -131,7 +131,7 @@ export const registerSecretFolderRouter = async (server: FastifyZodProvider) =>
         }
       ],
       params: z.object({
-        folderId: z.string()
+        folderIdOrName: z.string()
       }),
       body: z.object({
         workspaceId: z.string().trim(),
@@ -155,7 +155,7 @@ export const registerSecretFolderRouter = async (server: FastifyZodProvider) =>
         actorOrgId: req.permission.orgId,
         ...req.body,
         projectId: req.body.workspaceId,
-        id: req.params.folderId,
+        idOrName: req.params.folderIdOrName,
         path
       });
       await server.services.auditLog.createAuditLog({

backend/src/server/routes/v2/identity-project-router.ts

@@ -1,13 +1,15 @@
+import ms from "ms";
 import { z } from "zod";
 
 import {
   IdentitiesSchema,
   IdentityProjectMembershipsSchema,
   ProjectMembershipRole,
-  ProjectRolesSchema
+  ProjectUserMembershipRolesSchema
 } from "@app/db/schemas";
 import { verifyAuth } from "@app/server/plugins/auth/verify-auth";
 import { AuthMode } from "@app/services/auth/auth-type";
+import { ProjectUserMembershipTemporaryMode } from "@app/services/project-membership/project-membership-types";
 
 export const registerIdentityProjectRouter = async (server: FastifyZodProvider) => {
   server.route({
@@ -57,24 +59,40 @@ export const registerIdentityProjectRouter = async (server: FastifyZodProvider)
         identityId: z.string().trim()
       }),
       body: z.object({
-        role: z.string().trim().min(1).default(ProjectMembershipRole.NoAccess)
+        roles: z
+          .array(
+            z.union([
+              z.object({
+                role: z.string(),
+                isTemporary: z.literal(false).default(false)
+              }),
+              z.object({
+                role: z.string(),
+                isTemporary: z.literal(true),
+                temporaryMode: z.nativeEnum(ProjectUserMembershipTemporaryMode),
+                temporaryRange: z.string().refine((val) => ms(val) > 0, "Temporary range must be a positive number"),
+                temporaryAccessStartTime: z.string().datetime()
+              })
+            ])
+          )
+          .min(1)
       }),
       response: {
         200: z.object({
-          identityMembership: IdentityProjectMembershipsSchema
+          roles: ProjectUserMembershipRolesSchema.array()
         })
       }
     },
     handler: async (req) => {
-      const identityMembership = await server.services.identityProject.updateProjectIdentity({
+      const roles = await server.services.identityProject.updateProjectIdentity({
         actor: req.permission.type,
         actorId: req.permission.id,
         actorOrgId: req.permission.orgId,
         identityId: req.params.identityId,
         projectId: req.params.projectId,
-        role: req.body.role
+        roles: req.body.roles
       });
-      return { identityMembership };
+      return { roles };
     }
   });
 
@@ -127,18 +145,29 @@ export const registerIdentityProjectRouter = async (server: FastifyZodProvider)
       }),
       response: {
         200: z.object({
-          identityMemberships: IdentityProjectMembershipsSchema.merge(
-            z.object({
-              customRole: ProjectRolesSchema.pick({
-                id: true,
-                name: true,
-                slug: true,
-                permissions: true,
-                description: true
-              }).optional(),
+          identityMemberships: z
+            .object({
+              id: z.string(),
+              identityId: z.string(),
+              createdAt: z.date(),
+              updatedAt: z.date(),
+              roles: z.array(
+                z.object({
+                  id: z.string(),
+                  role: z.string(),
+                  customRoleId: z.string().optional().nullable(),
+                  customRoleName: z.string().optional().nullable(),
+                  customRoleSlug: z.string().optional().nullable(),
+                  isTemporary: z.boolean(),
+                  temporaryMode: z.string().optional().nullable(),
+                  temporaryRange: z.string().nullable().optional(),
+                  temporaryAccessStartTime: z.date().nullable().optional(),
+                  temporaryAccessEndTime: z.date().nullable().optional()
+                })
+              ),
               identity: IdentitiesSchema.pick({ name: true, id: true, authMethod: true })
             })
-          ).array()
+            .array()
         })
       }
     },

backend/src/server/routes/v2/organization-router.ts

@@ -24,6 +24,7 @@ export const registerOrgRouter = async (server: FastifyZodProvider) => {
           users: OrgMembershipsSchema.merge(
             z.object({
               user: UsersSchema.pick({
+                username: true,
                 email: true,
                 firstName: true,
                 lastName: true,
@@ -179,11 +180,12 @@ export const registerOrgRouter = async (server: FastifyZodProvider) => {
     handler: async (req) => {
       if (req.auth.actor !== ActorType.USER) return;
 
-      const organization = await server.services.org.createOrganization(
-        req.permission.id,
-        req.auth.user.email,
-        req.body.name
-      );
+      const organization = await server.services.org.createOrganization({
+        userId: req.permission.id,
+        userEmail: req.auth.user.email,
+        orgName: req.body.name
+      });
+
       return { organization };
     }
   });

backend/src/server/routes/v2/project-membership-router.ts

@@ -14,7 +14,8 @@ export const registerProjectMembershipRouter = async (server: FastifyZodProvider
         projectId: z.string().describe("The ID of the project.")
       }),
       body: z.object({
-        emails: z.string().email().array().describe("Emails of the users to add to the project.")
+        emails: z.string().email().array().default([]).describe("Emails of the users to add to the project."),
+        usernames: z.string().array().default([]).describe("Usernames of the users to add to the project.")
       }),
       response: {
         200: z.object({
@@ -28,7 +29,8 @@ export const registerProjectMembershipRouter = async (server: FastifyZodProvider
         projectId: req.params.projectId,
         actorId: req.permission.id,
         actor: req.permission.type,
-        emails: req.body.emails
+        emails: req.body.emails,
+        usernames: req.body.usernames
       });
 
       await server.services.auditLog.createAuditLog({
@@ -57,7 +59,8 @@ export const registerProjectMembershipRouter = async (server: FastifyZodProvider
       }),
 
       body: z.object({
-        emails: z.string().email().array().describe("Emails of the users to remove from the project.")
+        emails: z.string().email().array().default([]).describe("Emails of the users to remove from the project."),
+        usernames: z.string().array().default([]).describe("Usernames of the users to remove from the project.")
       }),
       response: {
         200: z.object({
@@ -72,7 +75,8 @@ export const registerProjectMembershipRouter = async (server: FastifyZodProvider
         actor: req.permission.type,
         actorOrgId: req.permission.orgId,
         projectId: req.params.projectId,
-        emails: req.body.emails
+        emails: req.body.emails,
+        usernames: req.body.usernames
       });
 
       for (const membership of memberships) {

backend/src/server/routes/v3/login-router.ts

@@ -12,7 +12,7 @@ export const registerLoginRouter = async (server: FastifyZodProvider) => {
     },
     schema: {
       body: z.object({
-        email: z.string().email().trim(),
+        email: z.string().trim(),
         providerAuthToken: z.string().trim().optional(),
         clientPublicKey: z.string().trim()
       }),
@@ -42,7 +42,7 @@ export const registerLoginRouter = async (server: FastifyZodProvider) => {
     },
     schema: {
       body: z.object({
-        email: z.string().email().trim(),
+        email: z.string().trim(),
         providerAuthToken: z.string().trim().optional(),
         clientProof: z.string().trim()
       }),

backend/src/server/routes/v3/signup-router.ts

@@ -88,7 +88,7 @@ export const registerSignupRouter = async (server: FastifyZodProvider) => {
     },
     schema: {
       body: z.object({
-        email: z.string().email().trim(),
+        email: z.string().trim(),
         firstName: z.string().trim(),
         lastName: z.string().trim().optional(),
         protectedKey: z.string().trim(),
@@ -131,13 +131,16 @@ export const registerSignupRouter = async (server: FastifyZodProvider) => {
         authorization: req.headers.authorization as string
       });
 
-      void server.services.telemetry.sendLoopsEvent(user.email, user.firstName || "", user.lastName || "");
+      if (user.email) {
+        void server.services.telemetry.sendLoopsEvent(user.email, user.firstName || "", user.lastName || "");
+      }
 
       void server.services.telemetry.sendPostHogEvents({
         event: PostHogEventTypes.UserSignedUp,
-        distinctId: user.email,
+        distinctId: user.username ?? "",
         properties: {
-          email: user.email,
+          username: user.username,
+          email: user.email ?? "",
           attributionSource: req.body.attributionSource
         }
       });
@@ -194,13 +197,16 @@ export const registerSignupRouter = async (server: FastifyZodProvider) => {
         authorization: req.headers.authorization as string
       });
 
-      void server.services.telemetry.sendLoopsEvent(user.email, user.firstName || "", user.lastName || "");
+      if (user.email) {
+        void server.services.telemetry.sendLoopsEvent(user.email, user.firstName || "", user.lastName || "");
+      }
 
       void server.services.telemetry.sendPostHogEvents({
         event: PostHogEventTypes.UserSignedUp,
-        distinctId: user.email,
+        distinctId: user.username ?? "",
         properties: {
-          email: user.email,
+          username: user.username,
+          email: user.email ?? "",
           attributionSource: "Team Invite"
         }
       });

backend/src/services/auth/auth-fns.ts

@@ -5,13 +5,14 @@ import { BadRequestError, UnauthorizedError } from "@app/lib/errors";
 
 import { AuthModeProviderJwtTokenPayload, AuthModeProviderSignUpTokenPayload, AuthTokenType } from "./auth-type";
 
-export const validateProviderAuthToken = (providerToken: string, email: string) => {
+export const validateProviderAuthToken = (providerToken: string, username?: string) => {
   if (!providerToken) throw new UnauthorizedError();
   const appCfg = getConfig();
   const decodedToken = jwt.verify(providerToken, appCfg.AUTH_SECRET) as AuthModeProviderJwtTokenPayload;
 
   if (decodedToken.authTokenType !== AuthTokenType.PROVIDER_TOKEN) throw new UnauthorizedError();
-  if (decodedToken.email !== email) throw new Error("Invalid auth credentials");
+
+  if (decodedToken.username !== username) throw new Error("Invalid auth credentials");
 
   if (decodedToken.organizationId) {
     return { orgId: decodedToken.organizationId };

backend/src/services/auth/auth-login-service.ts

@@ -39,17 +39,19 @@ export const authLoginServiceFactory = ({ userDAL, tokenService, smtpService }:
     if (!isDeviceSeen) {
       const newDeviceList = devices.concat([{ ip, userAgent }]);
       await userDAL.updateById(user.id, { devices: JSON.stringify(newDeviceList) });
-      await smtpService.sendMail({
-        template: SmtpTemplates.NewDeviceJoin,
-        subjectLine: "Successful login from new device",
-        recipients: [user.email],
-        substitutions: {
-          email: user.email,
-          timestamp: new Date().toString(),
-          ip,
-          userAgent
-        }
-      });
+      if (user.email) {
+        await smtpService.sendMail({
+          template: SmtpTemplates.NewDeviceJoin,
+          subjectLine: "Successful login from new device",
+          recipients: [user.email],
+          substitutions: {
+            email: user.email,
+            timestamp: new Date().toString(),
+            ip,
+            userAgent
+          }
+        });
+      }
     }
   };
 
@@ -131,7 +133,9 @@ export const authLoginServiceFactory = ({ userDAL, tokenService, smtpService }:
     providerAuthToken,
     clientPublicKey
   }: TLoginGenServerPublicKeyDTO) => {
-    const userEnc = await userDAL.findUserEncKeyByEmail(email);
+    const userEnc = await userDAL.findUserEncKeyByUsername({
+      username: email
+    });
     if (!userEnc || (userEnc && !userEnc.isAccepted)) {
       throw new Error("Failed to find  user");
     }
@@ -158,7 +162,9 @@ export const authLoginServiceFactory = ({ userDAL, tokenService, smtpService }:
     ip,
     userAgent
   }: TLoginClientProofDTO) => {
-    const userEnc = await userDAL.findUserEncKeyByEmail(email);
+    const userEnc = await userDAL.findUserEncKeyByUsername({
+      username: email
+    });
     if (!userEnc) throw new Error("Failed to find user");
     const cfg = getConfig();
 
@@ -187,7 +193,7 @@ export const authLoginServiceFactory = ({ userDAL, tokenService, smtpService }:
       clientPublicKey: null
     });
     // send multi factor auth token if they it enabled
-    if (userEnc.isMfaEnabled) {
+    if (userEnc.isMfaEnabled && userEnc.email) {
       const mfaToken = jwt.sign(
         {
           authTokenType: AuthTokenType.MFA_TOKEN,
@@ -227,7 +233,7 @@ export const authLoginServiceFactory = ({ userDAL, tokenService, smtpService }:
    */
   const resendMfaToken = async (userId: string) => {
     const user = await userDAL.findById(userId);
-    if (!user) return;
+    if (!user || !user.email) return;
     await sendUserMfaCode({
       userId: user.id,
       email: user.email
@@ -263,7 +269,7 @@ export const authLoginServiceFactory = ({ userDAL, tokenService, smtpService }:
    * OAuth2 login for google,github, and other oauth2 provider
    * */
   const oauth2Login = async ({ email, firstName, lastName, authMethod, callbackPort }: TOauthLoginDTO) => {
-    let user = await userDAL.findUserByEmail(email);
+    let user = await userDAL.findUserByUsername(email);
     const serverCfg = await getServerCfg();
 
     const appCfg = getConfig();
@@ -282,7 +288,14 @@ export const authLoginServiceFactory = ({ userDAL, tokenService, smtpService }:
           });
       }
 
-      user = await userDAL.create({ email, firstName, lastName, authMethods: [authMethod], isGhost: false });
+      user = await userDAL.create({
+        username: email,
+        email,
+        firstName,
+        lastName,
+        authMethods: [authMethod],
+        isGhost: false
+      });
     }
     const isLinkingRequired = !user?.authMethods?.includes(authMethod);
     const isUserCompleted = user.isAccepted;
@@ -290,7 +303,7 @@ export const authLoginServiceFactory = ({ userDAL, tokenService, smtpService }:
       {
         authTokenType: AuthTokenType.PROVIDER_TOKEN,
         userId: user.id,
-        email: user.email,
+        username: user.username,
         firstName: user.firstName,
         lastName: user.lastName,
         authMethod,

backend/src/services/auth/auth-password-service.ts

@@ -99,7 +99,7 @@ export const authPaswordServiceFactory = ({
    * Email password reset flow via email. Step 1 send email
    */
   const sendPasswordResetEmail = async (email: string) => {
-    const user = await userDAL.findUserByEmail(email);
+    const user = await userDAL.findUserByUsername(email);
     // ignore as user is not found to avoid an outside entity to identify infisical registered accounts
     if (!user || (user && !user.isAccepted)) return;
 
@@ -126,7 +126,7 @@ export const authPaswordServiceFactory = ({
    * */
   const verifyPasswordResetEmail = async (email: string, code: string) => {
     const cfg = getConfig();
-    const user = await userDAL.findUserByEmail(email);
+    const user = await userDAL.findUserByUsername(email);
     // ignore as user is not found to avoid an outside entity to identify infisical registered accounts
     if (!user || (user && !user.isAccepted)) {
       throw new Error("Failed email verification for pass reset");

backend/src/services/auth/auth-signup-service.ts

@@ -44,13 +44,13 @@ export const authSignupServiceFactory = ({
       throw new Error("Provided a disposable email");
     }
 
-    let user = await userDAL.findUserByEmail(email);
+    let user = await userDAL.findUserByUsername(email);
     if (user && user.isAccepted) {
       // TODO(akhilmhdh-pg): copy as old one. this needs to be changed due to security issues
       throw new Error("Failed to send verification code for complete account");
     }
     if (!user) {
-      user = await userDAL.create({ authMethods: [AuthMethod.EMAIL], email, isGhost: false });
+      user = await userDAL.create({ authMethods: [AuthMethod.EMAIL], username: email, email, isGhost: false });
     }
     if (!user) throw new Error("Failed to create user");
 
@@ -70,7 +70,7 @@ export const authSignupServiceFactory = ({
   };
 
   const verifyEmailSignup = async (email: string, code: string) => {
-    const user = await userDAL.findUserByEmail(email);
+    const user = await userDAL.findUserByUsername(email);
     if (!user || (user && user.isAccepted)) {
       // TODO(akhilmhdh): copy as old one. this needs to be changed due to security issues
       throw new Error("Failed to send verification code for complete account");
@@ -115,14 +115,14 @@ export const authSignupServiceFactory = ({
     userAgent,
     authorization
   }: TCompleteAccountSignupDTO) => {
-    const user = await userDAL.findUserByEmail(email);
+    const user = await userDAL.findOne({ username: email });
     if (!user || (user && user.isAccepted)) {
       throw new Error("Failed to complete account for complete user");
     }
 
     let organizationId;
     if (providerAuthToken) {
-      const { orgId } = validateProviderAuthToken(providerAuthToken, user.email);
+      const { orgId } = validateProviderAuthToken(providerAuthToken, user.username);
       organizationId = orgId;
     } else {
       validateSignUpAuthorization(authorization, user.id);
@@ -150,7 +150,11 @@ export const authSignupServiceFactory = ({
     });
 
     if (!organizationId) {
-      await orgService.createOrganization(user.id, user.email, organizationName);
+      await orgService.createOrganization({
+        userId: user.id,
+        userEmail: user.email ?? user.username,
+        orgName: organizationName
+      });
     }
 
     const updatedMembersips = await orgDAL.updateMembership(
@@ -215,7 +219,7 @@ export const authSignupServiceFactory = ({
     encryptedPrivateKeyTag,
     authorization
   }: TCompleteAccountInviteDTO) => {
-    const user = await userDAL.findUserByEmail(email);
+    const user = await userDAL.findUserByUsername(email);
     if (!user || (user && user.isAccepted)) {
       throw new Error("Failed to complete account for complete user");
     }

backend/src/services/auth/auth-type.ts

@@ -5,7 +5,8 @@ export enum AuthMethod {
   GITLAB = "gitlab",
   OKTA_SAML = "okta-saml",
   AZURE_SAML = "azure-saml",
-  JUMPCLOUD_SAML = "jumpcloud-saml"
+  JUMPCLOUD_SAML = "jumpcloud-saml",
+  LDAP = "ldap"
 }
 
 export enum AuthTokenType {
@@ -61,7 +62,7 @@ export type AuthModeRefreshJwtTokenPayload = {
 
 export type AuthModeProviderJwtTokenPayload = {
   authTokenType: AuthTokenType.PROVIDER_TOKEN;
-  email: string;
+  username: string;
   organizationId?: string;
 };
 

backend/src/services/identity-project/identity-project-dal.ts

@@ -3,7 +3,7 @@ import { Knex } from "knex";
 import { TDbClient } from "@app/db";
 import { TableName } from "@app/db/schemas";
 import { DatabaseError } from "@app/lib/errors";
-import { ormify, selectAllTableCols } from "@app/lib/knex";
+import { ormify, sqlNestRelationships } from "@app/lib/knex";
 
 export type TIdentityProjectDALFactory = ReturnType<typeof identityProjectDALFactory>;
 
@@ -15,52 +15,81 @@ export const identityProjectDALFactory = (db: TDbClient) => {
       const docs = await (tx || db)(TableName.IdentityProjectMembership)
         .where(`${TableName.IdentityProjectMembership}.projectId`, projectId)
         .join(TableName.Identity, `${TableName.IdentityProjectMembership}.identityId`, `${TableName.Identity}.id`)
+        .join(
+          TableName.IdentityProjectMembershipRole,
+          `${TableName.IdentityProjectMembershipRole}.projectMembershipId`,
+          `${TableName.IdentityProjectMembership}.id`
+        )
         .leftJoin(
           TableName.ProjectRoles,
-          `${TableName.IdentityProjectMembership}.roleId`,
+          `${TableName.IdentityProjectMembershipRole}.customRoleId`,
           `${TableName.ProjectRoles}.id`
         )
-        .select(selectAllTableCols(TableName.IdentityProjectMembership))
-        // cr stands for custom role
-        .select(db.ref("id").as("crId").withSchema(TableName.ProjectRoles))
-        .select(db.ref("name").as("crName").withSchema(TableName.ProjectRoles))
-        .select(db.ref("slug").as("crSlug").withSchema(TableName.ProjectRoles))
-        .select(db.ref("description").as("crDescription").withSchema(TableName.ProjectRoles))
-        .select(db.ref("permissions").as("crPermission").withSchema(TableName.ProjectRoles))
-        .select(db.ref("permissions").as("crPermission").withSchema(TableName.ProjectRoles))
-        .select(db.ref("id").as("identityId").withSchema(TableName.Identity))
-        .select(db.ref("name").as("identityName").withSchema(TableName.Identity))
-        .select(db.ref("authMethod").as("identityAuthMethod").withSchema(TableName.Identity));
-      return docs.map(
-        ({
-          crId,
-          crDescription,
-          crSlug,
-          crPermission,
-          crName,
-          identityId,
-          identityName,
-          identityAuthMethod,
-          ...el
-        }) => ({
-          ...el,
+        .select(
+          db.ref("id").withSchema(TableName.IdentityProjectMembership),
+          db.ref("createdAt").withSchema(TableName.IdentityProjectMembership),
+          db.ref("updatedAt").withSchema(TableName.IdentityProjectMembership),
+          db.ref("authMethod").as("identityAuthMethod").withSchema(TableName.Identity),
+          db.ref("id").as("identityId").withSchema(TableName.Identity),
+          db.ref("name").as("identityName").withSchema(TableName.Identity),
+          db.ref("id").withSchema(TableName.IdentityProjectMembership),
+          db.ref("role").withSchema(TableName.IdentityProjectMembershipRole),
+          db.ref("id").withSchema(TableName.IdentityProjectMembershipRole).as("membershipRoleId"),
+          db.ref("customRoleId").withSchema(TableName.IdentityProjectMembershipRole),
+          db.ref("name").withSchema(TableName.ProjectRoles).as("customRoleName"),
+          db.ref("slug").withSchema(TableName.ProjectRoles).as("customRoleSlug"),
+          db.ref("temporaryMode").withSchema(TableName.IdentityProjectMembershipRole),
+          db.ref("isTemporary").withSchema(TableName.IdentityProjectMembershipRole),
+          db.ref("temporaryRange").withSchema(TableName.IdentityProjectMembershipRole),
+          db.ref("temporaryAccessStartTime").withSchema(TableName.IdentityProjectMembershipRole),
+          db.ref("temporaryAccessEndTime").withSchema(TableName.IdentityProjectMembershipRole)
+        );
+
+      const members = sqlNestRelationships({
+        data: docs,
+        parentMapper: ({ identityId, identityName, identityAuthMethod, id, createdAt, updatedAt }) => ({
+          id,
           identityId,
+          createdAt,
+          updatedAt,
           identity: {
             id: identityId,
             name: identityName,
             authMethod: identityAuthMethod
-          },
-          customRole: el.roleId
-            ? {
-                id: crId,
-                name: crName,
-                slug: crSlug,
-                permissions: crPermission,
-                description: crDescription
-              }
-            : undefined
-        })
-      );
+          }
+        }),
+        key: "id",
+        childrenMapper: [
+          {
+            label: "roles" as const,
+            key: "membershipRoleId",
+            mapper: ({
+              role,
+              customRoleId,
+              customRoleName,
+              customRoleSlug,
+              membershipRoleId,
+              temporaryRange,
+              temporaryMode,
+              temporaryAccessEndTime,
+              temporaryAccessStartTime,
+              isTemporary
+            }) => ({
+              id: membershipRoleId,
+              role,
+              customRoleId,
+              customRoleName,
+              customRoleSlug,
+              temporaryRange,
+              temporaryMode,
+              temporaryAccessEndTime,
+              temporaryAccessStartTime,
+              isTemporary
+            })
+          }
+        ]
+      });
+      return members;
     } catch (error) {
       throw new DatabaseError({ error, name: "FindByProjectId" });
     }

backend/src/services/identity-project/identity-project-membership-role-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 TIdentityProjectMembershipRoleDALFactory = ReturnType<typeof identityProjectMembershipRoleDALFactory>;
+
+export const identityProjectMembershipRoleDALFactory = (db: TDbClient) => {
+  const orm = ormify(db, TableName.IdentityProjectMembershipRole);
+  return orm;
+};

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

@@ -1,15 +1,20 @@
 import { ForbiddenError } from "@casl/ability";
+import ms from "ms";
 
-import { ProjectMembershipRole, TProjectRoles } from "@app/db/schemas";
+import { ProjectMembershipRole } from "@app/db/schemas";
 import { TPermissionServiceFactory } from "@app/ee/services/permission/permission-service";
 import { ProjectPermissionActions, ProjectPermissionSub } from "@app/ee/services/permission/project-permission";
 import { isAtLeastAsPrivileged } from "@app/lib/casl";
 import { BadRequestError, ForbiddenRequestError } from "@app/lib/errors";
+import { groupBy } from "@app/lib/fn";
 
 import { ActorType } from "../auth/auth-type";
 import { TIdentityOrgDALFactory } from "../identity/identity-org-dal";
 import { TProjectDALFactory } from "../project/project-dal";
+import { ProjectUserMembershipTemporaryMode } from "../project-membership/project-membership-types";
+import { TProjectRoleDALFactory } from "../project-role/project-role-dal";
 import { TIdentityProjectDALFactory } from "./identity-project-dal";
+import { TIdentityProjectMembershipRoleDALFactory } from "./identity-project-membership-role-dal";
 import {
   TCreateProjectIdentityDTO,
   TDeleteProjectIdentityDTO,
@@ -19,7 +24,12 @@ import {
 
 type TIdentityProjectServiceFactoryDep = {
   identityProjectDAL: TIdentityProjectDALFactory;
+  identityProjectMembershipRoleDAL: Pick<
+    TIdentityProjectMembershipRoleDALFactory,
+    "create" | "transaction" | "insertMany" | "delete"
+  >;
   projectDAL: Pick<TProjectDALFactory, "findById">;
+  projectRoleDAL: Pick<TProjectRoleDALFactory, "find">;
   identityOrgMembershipDAL: Pick<TIdentityOrgDALFactory, "findOne">;
   permissionService: Pick<TPermissionServiceFactory, "getProjectPermission" | "getProjectPermissionByRole">;
 };
@@ -30,7 +40,9 @@ export const identityProjectServiceFactory = ({
   identityProjectDAL,
   permissionService,
   identityOrgMembershipDAL,
-  projectDAL
+  identityProjectMembershipRoleDAL,
+  projectDAL,
+  projectRoleDAL
 }: TIdentityProjectServiceFactoryDep) => {
   const createProjectIdentity = async ({
     identityId,
@@ -70,11 +82,26 @@ export const identityProjectServiceFactory = ({
       });
     const isCustomRole = Boolean(customRole);
 
-    const projectIdentity = await identityProjectDAL.create({
-      identityId,
-      projectId: project.id,
-      role: isCustomRole ? ProjectMembershipRole.Custom : role,
-      roleId: customRole?.id
+    const projectIdentity = await identityProjectDAL.transaction(async (tx) => {
+      const identityProjectMembership = await identityProjectDAL.create(
+        {
+          identityId,
+          projectId: project.id,
+          role: isCustomRole ? ProjectMembershipRole.Custom : role,
+          roleId: customRole?.id
+        },
+        tx
+      );
+
+      await identityProjectMembershipRoleDAL.create(
+        {
+          projectMembershipId: identityProjectMembership.id,
+          role: isCustomRole ? ProjectMembershipRole.Custom : role,
+          customRoleId: customRole?.id
+        },
+        tx
+      );
+      return identityProjectMembership;
     });
     return projectIdentity;
   };
@@ -82,7 +109,7 @@ export const identityProjectServiceFactory = ({
   const updateProjectIdentity = async ({
     projectId,
     identityId,
-    role,
+    roles,
     actor,
     actorId,
     actorOrgId
@@ -106,28 +133,51 @@ export const identityProjectServiceFactory = ({
     if (!hasRequiredPriviledges)
       throw new ForbiddenRequestError({ message: "Failed to delete more privileged identity" });
 
-    let customRole: TProjectRoles | undefined;
-    if (role) {
-      const { permission: rolePermission, role: customOrgRole } = await permissionService.getProjectPermissionByRole(
-        role,
-        projectIdentity.projectId
-      );
-
-      const isCustomRole = Boolean(customOrgRole);
-      const hasRequiredNewRolePermission = isAtLeastAsPrivileged(permission, rolePermission);
-      if (!hasRequiredNewRolePermission)
-        throw new BadRequestError({ message: "Failed to create a more privileged identity" });
-      if (isCustomRole) customRole = customOrgRole;
-    }
-
-    const [updatedProjectIdentity] = await identityProjectDAL.update(
-      { projectId, identityId: projectIdentity.identityId },
-      {
-        role: customRole ? ProjectMembershipRole.Custom : role,
-        roleId: customRole ? customRole.id : null
-      }
+    // validate custom roles input
+    const customInputRoles = roles.filter(
+      ({ role }) => !Object.values(ProjectMembershipRole).includes(role as ProjectMembershipRole)
     );
-    return updatedProjectIdentity;
+    const hasCustomRole = Boolean(customInputRoles.length);
+    const customRoles = hasCustomRole
+      ? await projectRoleDAL.find({
+          projectId,
+          $in: { slug: customInputRoles.map(({ role }) => role) }
+        })
+      : [];
+    if (customRoles.length !== customInputRoles.length) throw new BadRequestError({ message: "Custom role not found" });
+
+    const customRolesGroupBySlug = groupBy(customRoles, ({ slug }) => slug);
+
+    const santiziedProjectMembershipRoles = roles.map((inputRole) => {
+      const isCustomRole = Boolean(customRolesGroupBySlug?.[inputRole.role]?.[0]);
+      if (!inputRole.isTemporary) {
+        return {
+          projectMembershipId: projectIdentity.id,
+          role: isCustomRole ? ProjectMembershipRole.Custom : inputRole.role,
+          customRoleId: customRolesGroupBySlug[inputRole.role] ? customRolesGroupBySlug[inputRole.role][0].id : null
+        };
+      }
+
+      // check cron or relative here later for now its just relative
+      const relativeTimeInMs = ms(inputRole.temporaryRange);
+      return {
+        projectMembershipId: projectIdentity.id,
+        role: isCustomRole ? ProjectMembershipRole.Custom : inputRole.role,
+        customRoleId: customRolesGroupBySlug[inputRole.role] ? customRolesGroupBySlug[inputRole.role][0].id : null,
+        isTemporary: true,
+        temporaryMode: ProjectUserMembershipTemporaryMode.Relative,
+        temporaryRange: inputRole.temporaryRange,
+        temporaryAccessStartTime: new Date(inputRole.temporaryAccessStartTime),
+        temporaryAccessEndTime: new Date(new Date(inputRole.temporaryAccessStartTime).getTime() + relativeTimeInMs)
+      };
+    });
+
+    const updatedRoles = await identityProjectMembershipRoleDAL.transaction(async (tx) => {
+      await identityProjectMembershipRoleDAL.delete({ projectMembershipId: projectIdentity.id }, tx);
+      return identityProjectMembershipRoleDAL.insertMany(santiziedProjectMembershipRoles, tx);
+    });
+
+    return updatedRoles;
   };
 
   const deleteProjectIdentity = async ({

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

@@ -1,12 +1,26 @@
 import { TProjectPermission } from "@app/lib/types";
 
+import { ProjectUserMembershipTemporaryMode } from "../project-membership/project-membership-types";
+
 export type TCreateProjectIdentityDTO = {
   identityId: string;
   role: string;
 } & TProjectPermission;
 
 export type TUpdateProjectIdentityDTO = {
-  role: string;
+  roles: (
+    | {
+        role: string;
+        isTemporary?: false;
+      }
+    | {
+        role: string;
+        isTemporary: true;
+        temporaryMode: ProjectUserMembershipTemporaryMode.Relative;
+        temporaryRange: string;
+        temporaryAccessStartTime: string;
+      }
+  )[];
   identityId: string;
 } & TProjectPermission;
 

backend/src/services/integration-auth/integration-app-list.ts

@@ -109,7 +109,7 @@ const getAppsGCPSecretManager = async ({ accessToken }: { accessToken: string })
  */
 const getAppsHeroku = async ({ accessToken }: { accessToken: string }) => {
   const res = (
-    await request.get<{ name: string }[]>(`${IntegrationUrls.HEROKU_API_URL}/apps`, {
+    await request.get<{ name: string; id: string }[]>(`${IntegrationUrls.HEROKU_API_URL}/apps`, {
       headers: {
         Accept: "application/vnd.heroku+json; version=3",
         Authorization: `Bearer ${accessToken}`
@@ -118,7 +118,8 @@ const getAppsHeroku = async ({ accessToken }: { accessToken: string }) => {
   ).data;
 
   const apps = res.map((a) => ({
-    name: a.name
+    name: a.name,
+    appId: a.id
   }));
 
   return apps;

backend/src/services/integration-auth/integration-auth-service.ts

@@ -20,9 +20,11 @@ import {
   TDeleteIntegrationAuthsDTO,
   TGetIntegrationAuthDTO,
   TGetIntegrationAuthTeamCityBuildConfigDTO,
+  THerokuPipelineCoupling,
   TIntegrationAuthAppsDTO,
   TIntegrationAuthBitbucketWorkspaceDTO,
   TIntegrationAuthChecklyGroupsDTO,
+  TIntegrationAuthHerokuPipelinesDTO,
   TIntegrationAuthNorthflankSecretGroupDTO,
   TIntegrationAuthQoveryEnvironmentsDTO,
   TIntegrationAuthQoveryOrgsDTO,
@@ -576,6 +578,38 @@ export const integrationAuthServiceFactory = ({
     return [];
   };
 
+  const getHerokuPipelines = async ({ id, actor, actorId, actorOrgId }: TIntegrationAuthHerokuPipelinesDTO) => {
+    const integrationAuth = await integrationAuthDAL.findById(id);
+    if (!integrationAuth) throw new BadRequestError({ message: "Failed to find integration" });
+
+    const { permission } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      integrationAuth.projectId,
+      actorOrgId
+    );
+    ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Read, ProjectPermissionSub.Integrations);
+    const botKey = await projectBotService.getBotKey(integrationAuth.projectId);
+    const { accessToken } = await getIntegrationAccessToken(integrationAuth, botKey);
+
+    const { data } = await request.get<THerokuPipelineCoupling[]>(
+      `${IntegrationUrls.HEROKU_API_URL}/pipeline-couplings`,
+      {
+        headers: {
+          Accept: "application/vnd.heroku+json; version=3",
+          Authorization: `Bearer ${accessToken}`,
+          "Accept-Encoding": "application/json"
+        }
+      }
+    );
+
+    return data.map(({ app: { id: appId }, stage, pipeline: { id: pipelineId, name } }) => ({
+      app: { appId },
+      stage,
+      pipeline: { pipelineId, name }
+    }));
+  };
+
   const getRailwayEnvironments = async ({ id, actor, actorId, actorOrgId, appId }: TIntegrationAuthRailwayEnvDTO) => {
     const integrationAuth = await integrationAuthDAL.findById(id);
     if (!integrationAuth) throw new BadRequestError({ message: "Failed to find integration" });
@@ -649,33 +683,21 @@ export const integrationAuthServiceFactory = ({
     ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Read, ProjectPermissionSub.Integrations);
     const botKey = await projectBotService.getBotKey(integrationAuth.projectId);
     const { accessToken } = await getIntegrationAccessToken(integrationAuth, botKey);
-    if (appId) {
+
+    if (appId && appId !== "") {
       const query = `
-     query project($id: String!) {
-        project(id: $id) {
-          createdAt
-          deletedAt
-          id
-          description
-          expiredAt
-          isPublic
-          isTempProject
-          isUpdatable
-          name
-          prDeploys
-          teamId
-          updatedAt
-          upstreamUrl
-		  services {
-			edges {
-				node {
-					id
-					name
-				}
-			}
-		  }
+        query project($id: String!) {
+          project(id: $id) {
+            services {
+                edges {
+                    node {
+                        id
+                        name
+                    }
+                }
+            }
+          }
         }
-      }
       `;
 
       const variables = {
@@ -711,6 +733,7 @@ export const integrationAuthServiceFactory = ({
       );
       return edges.map(({ node: { name, id: serviceId } }) => ({ name, serviceId }));
     }
+
     return [];
   };
 
@@ -915,6 +938,7 @@ export const integrationAuthServiceFactory = ({
     getQoveryApps,
     getQoveryEnvs,
     getQoveryJobs,
+    getHerokuPipelines,
     getQoveryOrgs,
     getQoveryProjects,
     getQoveryContainers,

backend/src/services/integration-auth/integration-auth-types.ts

@@ -62,6 +62,10 @@ export type TIntegrationAuthQoveryScopesDTO = {
   environmentId: string;
 } & Omit<TProjectPermission, "projectId">;
 
+export type TIntegrationAuthHerokuPipelinesDTO = {
+  id: string;
+} & Omit<TProjectPermission, "projectId">;
+
 export type TIntegrationAuthRailwayEnvDTO = {
   id: string;
   appId: string;
@@ -129,6 +133,12 @@ export type TNorthflankSecretGroup = {
   projectId: string;
 };
 
+export type THerokuPipelineCoupling = {
+  app: { id: string };
+  stage: string;
+  pipeline: { id: string; name: string };
+};
+
 export type TTeamCityBuildConfig = {
   id: string;
   name: string;

backend/src/services/integration-auth/integration-list.ts

@@ -37,6 +37,12 @@ export enum IntegrationType {
   OAUTH2 = "oauth2"
 }
 
+export enum IntegrationInitialSyncBehavior {
+  OVERWRITE_TARGET = "overwrite-target",
+  PREFER_TARGET = "prefer-target",
+  PREFER_SOURCE = "prefer-source"
+}
+
 export enum IntegrationUrls {
   // integration oauth endpoints
   GCP_TOKEN_URL = "https://oauth2.googleapis.com/token",

backend/src/services/integration-auth/integration-sync-secret.ts

@@ -20,11 +20,13 @@ import sodium from "libsodium-wrappers";
 import isEqual from "lodash.isequal";
 import { z } from "zod";
 
-import { TIntegrationAuths, TIntegrations } from "@app/db/schemas";
+import { SecretType, TIntegrationAuths, TIntegrations, TSecrets } from "@app/db/schemas";
 import { request } from "@app/lib/config/request";
 import { BadRequestError } from "@app/lib/errors";
+import { TCreateManySecretsRawFn, TUpdateManySecretsRawFn } from "@app/services/secret/secret-types";
 
-import { Integrations, IntegrationUrls } from "./integration-list";
+import { TIntegrationDALFactory } from "../integration/integration-dal";
+import { IntegrationInitialSyncBehavior, Integrations, IntegrationUrls } from "./integration-list";
 
 const getSecretKeyValuePair = (secrets: Record<string, { value: string | null; comment?: string } | null>) =>
   Object.keys(secrets).reduce<Record<string, string | null | undefined>>((prev, key) => {
@@ -582,11 +584,25 @@ const syncSecretsAWSSecretManager = async ({
  * Sync/push [secrets] to Heroku app named [integration.app]
  */
 const syncSecretsHeroku = async ({
+  createManySecretsRawFn,
+  updateManySecretsRawFn,
+  integrationDAL,
   integration,
   secrets,
   accessToken
 }: {
-  integration: TIntegrations;
+  createManySecretsRawFn: (params: TCreateManySecretsRawFn) => Promise<Array<TSecrets & { _id: string }>>;
+  updateManySecretsRawFn: (params: TUpdateManySecretsRawFn) => Promise<Array<TSecrets & { _id: string }>>;
+  integrationDAL: Pick<TIntegrationDALFactory, "updateById">;
+  integration: TIntegrations & {
+    projectId: string;
+    environment: {
+      id: string;
+      name: string;
+      slug: string;
+    };
+    secretPath: string;
+  };
   secrets: Record<string, { value: string; comment?: string } | null>;
   accessToken: string;
 }) => {
@@ -600,12 +616,74 @@ const syncSecretsHeroku = async ({
     })
   ).data;
 
+  const secretsToAdd: { [key: string]: string } = {};
+  const secretsToUpdate: { [key: string]: string } = {};
+
+  const metadata = z.record(z.any()).parse(integration.metadata);
+
   Object.keys(herokuSecrets).forEach((key) => {
-    if (!(key in secrets)) {
-      secrets[key] = null;
-    }
+    if (!integration.lastUsed) {
+      // first time using integration
+      // -> apply initial sync behavior
+      switch (metadata.initialSyncBehavior) {
+        case IntegrationInitialSyncBehavior.OVERWRITE_TARGET: {
+          if (!(key in secrets)) secrets[key] = null;
+          break;
+        }
+        case IntegrationInitialSyncBehavior.PREFER_TARGET: {
+          if (!(key in secrets)) {
+            secretsToAdd[key] = herokuSecrets[key];
+          } else if (secrets[key]?.value !== herokuSecrets[key]) {
+            secretsToUpdate[key] = herokuSecrets[key];
+          }
+          secrets[key] = {
+            value: herokuSecrets[key]
+          };
+          break;
+        }
+        case IntegrationInitialSyncBehavior.PREFER_SOURCE: {
+          if (!(key in secrets)) {
+            secrets[key] = herokuSecrets[key];
+            secretsToAdd[key] = herokuSecrets[key];
+          }
+          break;
+        }
+        default: {
+          if (!(key in secrets)) secrets[key] = null;
+          break;
+        }
+      }
+    } else if (!(key in secrets)) secrets[key] = null;
   });
 
+  if (Object.keys(secretsToAdd).length) {
+    await createManySecretsRawFn({
+      projectId: integration.projectId,
+      environment: integration.environment.slug,
+      path: integration.secretPath,
+      secrets: Object.keys(secretsToAdd).map((key) => ({
+        secretName: key,
+        secretValue: secretsToAdd[key],
+        type: SecretType.Shared,
+        secretComment: ""
+      }))
+    });
+  }
+
+  if (Object.keys(secretsToUpdate).length) {
+    await updateManySecretsRawFn({
+      projectId: integration.projectId,
+      environment: integration.environment.slug,
+      path: integration.secretPath,
+      secrets: Object.keys(secretsToUpdate).map((key) => ({
+        secretName: key,
+        secretValue: secretsToUpdate[key],
+        type: SecretType.Shared,
+        secretComment: ""
+      }))
+    });
+  }
+
   await request.patch(
     `${IntegrationUrls.HEROKU_API_URL}/apps/${integration.app}/config-vars`,
     getSecretKeyValuePair(secrets),
@@ -617,6 +695,10 @@ const syncSecretsHeroku = async ({
       }
     }
   );
+
+  await integrationDAL.updateById(integration.id, {
+    lastUsed: new Date()
+  });
 };
 
 /**
@@ -1204,21 +1286,21 @@ const syncSecretsRailway = async ({
     }
   `;
 
-  const input = {
-    projectId: integration.appId,
-    environmentId: integration.targetEnvironmentId,
-    ...(integration.targetServiceId ? { serviceId: integration.targetServiceId } : {}),
-    replace: true,
-    variables: getSecretKeyValuePair(secrets)
+  const variables = {
+    input: {
+      projectId: integration.appId,
+      environmentId: integration.targetEnvironmentId,
+      ...(integration.targetServiceId ? { serviceId: integration.targetServiceId } : {}),
+      replace: true,
+      variables: getSecretKeyValuePair(secrets)
+    }
   };
 
   await request.post(
     IntegrationUrls.RAILWAY_API_URL,
     {
       query,
-      variables: {
-        input
-      }
+      variables
     },
     {
       headers: {
@@ -2930,8 +3012,14 @@ const syncSecretsHasuraCloud = async ({
 
 /**
  * Sync/push [secrets] to [app] in integration named [integration]
+ *
+ * Do this in terms of DAL
+ *
  */
 export const syncIntegrationSecrets = async ({
+  createManySecretsRawFn,
+  updateManySecretsRawFn,
+  integrationDAL,
   integration,
   integrationAuth,
   secrets,
@@ -2939,7 +3027,18 @@ export const syncIntegrationSecrets = async ({
   accessToken,
   appendices
 }: {
-  integration: TIntegrations;
+  createManySecretsRawFn: (params: TCreateManySecretsRawFn) => Promise<Array<TSecrets & { _id: string }>>;
+  updateManySecretsRawFn: (params: TUpdateManySecretsRawFn) => Promise<Array<TSecrets & { _id: string }>>;
+  integrationDAL: Pick<TIntegrationDALFactory, "updateById">;
+  integration: TIntegrations & {
+    projectId: string;
+    environment: {
+      id: string;
+      name: string;
+      slug: string;
+    };
+    secretPath: string;
+  };
   integrationAuth: TIntegrationAuths;
   secrets: Record<string, { value: string; comment?: string }>;
   accessId: string | null;
@@ -2979,6 +3078,9 @@ export const syncIntegrationSecrets = async ({
       break;
     case Integrations.HEROKU:
       await syncSecretsHeroku({
+        createManySecretsRawFn,
+        updateManySecretsRawFn,
+        integrationDAL,
         integration,
         secrets,
         accessToken

backend/src/services/org/org-dal.ts

@@ -57,7 +57,7 @@ export const orgDALFactory = (db: TDbClient) => {
   const findAllOrgMembers = async (orgId: string) => {
     try {
       const members = await db(TableName.OrgMembership)
-        .where({ orgId })
+        .where(`${TableName.OrgMembership}.orgId`, orgId)
         .join(TableName.Users, `${TableName.OrgMembership}.userId`, `${TableName.Users}.id`)
         .leftJoin<TUserEncryptionKeys>(
           TableName.UserEncryptionKey,
@@ -72,25 +72,27 @@ export const orgDALFactory = (db: TDbClient) => {
           db.ref("roleId").withSchema(TableName.OrgMembership),
           db.ref("status").withSchema(TableName.OrgMembership),
           db.ref("email").withSchema(TableName.Users),
+          db.ref("username").withSchema(TableName.Users),
           db.ref("firstName").withSchema(TableName.Users),
           db.ref("lastName").withSchema(TableName.Users),
           db.ref("id").withSchema(TableName.Users).as("userId"),
           db.ref("publicKey").withSchema(TableName.UserEncryptionKey)
         )
         .where({ isGhost: false }); // MAKE SURE USER IS NOT A GHOST USER
-      return members.map(({ email, firstName, lastName, userId, publicKey, ...data }) => ({
+
+      return members.map(({ email, username, firstName, lastName, userId, publicKey, ...data }) => ({
         ...data,
-        user: { email, firstName, lastName, id: userId, publicKey }
+        user: { email, username, firstName, lastName, id: userId, publicKey }
       }));
     } catch (error) {
       throw new DatabaseError({ error, name: "Find all org members" });
     }
   };
 
-  const findOrgMembersByEmail = async (orgId: string, emails: string[]) => {
+  const findOrgMembersByUsername = async (orgId: string, usernames: string[]) => {
     try {
       const members = await db(TableName.OrgMembership)
-        .where({ orgId })
+        .where(`${TableName.OrgMembership}.orgId`, orgId)
         .join(TableName.Users, `${TableName.OrgMembership}.userId`, `${TableName.Users}.id`)
         .leftJoin<TUserEncryptionKeys>(
           TableName.UserEncryptionKey,
@@ -104,6 +106,7 @@ export const orgDALFactory = (db: TDbClient) => {
           db.ref("role").withSchema(TableName.OrgMembership),
           db.ref("roleId").withSchema(TableName.OrgMembership),
           db.ref("status").withSchema(TableName.OrgMembership),
+          db.ref("username").withSchema(TableName.Users),
           db.ref("email").withSchema(TableName.Users),
           db.ref("firstName").withSchema(TableName.Users),
           db.ref("lastName").withSchema(TableName.Users),
@@ -111,7 +114,7 @@ export const orgDALFactory = (db: TDbClient) => {
           db.ref("publicKey").withSchema(TableName.UserEncryptionKey)
         )
         .where({ isGhost: false })
-        .whereIn("email", emails);
+        .whereIn("username", usernames);
       return members.map(({ email, firstName, lastName, userId, publicKey, ...data }) => ({
         ...data,
         user: { email, firstName, lastName, id: userId, publicKey }
@@ -243,10 +246,13 @@ export const orgDALFactory = (db: TDbClient) => {
         .select(
           selectAllTableCols(TableName.OrgMembership),
           db.ref("email").withSchema(TableName.Users),
+          db.ref("username").withSchema(TableName.Users),
           db.ref("firstName").withSchema(TableName.Users),
           db.ref("lastName").withSchema(TableName.Users),
           db.ref("scimEnabled").withSchema(TableName.Organization)
-        );
+        )
+        .where({ isGhost: false });
+
       if (limit) void query.limit(limit);
       if (offset) void query.offset(offset);
       if (sort) {
@@ -266,7 +272,7 @@ export const orgDALFactory = (db: TDbClient) => {
     findOrgById,
     findAllOrgsByUserId,
     ghostUserExists,
-    findOrgMembersByEmail,
+    findOrgMembersByUsername,
     findOrgGhostUser,
     create,
     updateById,

backend/src/services/org/org-role-service.ts

@@ -58,7 +58,7 @@ export const orgRoleServiceFactory = ({ orgRoleDAL, permissionService }: TOrgRol
       { id: roleId, orgId },
       { ...data, permissions: data.permissions ? JSON.stringify(data.permissions) : undefined }
     );
-    if (!updateRole) throw new BadRequestError({ message: "Role not found", name: "Update role" });
+    if (!updatedRole) throw new BadRequestError({ message: "Role not found", name: "Update role" });
     return updatedRole;
   };
 
@@ -66,7 +66,7 @@ export const orgRoleServiceFactory = ({ orgRoleDAL, permissionService }: TOrgRol
     const { permission } = await permissionService.getUserOrgPermission(userId, orgId, actorOrgId);
     ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Delete, OrgPermissionSubjects.Role);
     const [deletedRole] = await orgRoleDAL.delete({ id: roleId, orgId });
-    if (!deleteRole) throw new BadRequestError({ message: "Role not found", name: "Update role" });
+    if (!deletedRole) throw new BadRequestError({ message: "Role not found", name: "Update role" });
 
     return deletedRole;
   };

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

@@ -103,11 +103,11 @@ export const orgServiceFactory = ({
     return members;
   };
 
-  const findOrgMembersByEmail = async ({ actor, actorId, orgId, emails }: TFindOrgMembersByEmailDTO) => {
+  const findOrgMembersByUsername = async ({ actor, actorId, orgId, emails }: TFindOrgMembersByEmailDTO) => {
     const { permission } = await permissionService.getOrgPermission(actor, actorId, orgId);
     ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Read, OrgPermissionSubjects.Member);
 
-    const members = await orgDAL.findOrgMembersByEmail(orgId, emails);
+    const members = await orgDAL.findOrgMembersByUsername(orgId, emails);
 
     return members;
   };
@@ -145,6 +145,7 @@ export const orgServiceFactory = ({
       {
         isGhost: true,
         authMethods: [AuthMethod.EMAIL],
+        username: email,
         email,
         isAccepted: true
       },
@@ -239,7 +240,15 @@ export const orgServiceFactory = ({
   /*
    * Create organization
    * */
-  const createOrganization = async (userId: string, userEmail: string, orgName: string) => {
+  const createOrganization = async ({
+    userId,
+    userEmail,
+    orgName
+  }: {
+    userId: string;
+    orgName: string;
+    userEmail?: string | null;
+  }) => {
     const { privateKey, publicKey } = generateAsymmetricKeyPair();
     const key = generateSymmetricKey();
     const {
@@ -367,7 +376,7 @@ export const orgServiceFactory = ({
       });
     }
     const invitee = await orgDAL.transaction(async (tx) => {
-      const inviteeUser = await userDAL.findUserByEmail(inviteeEmail, tx);
+      const inviteeUser = await userDAL.findUserByUsername(inviteeEmail, tx);
       if (inviteeUser) {
         // if user already exist means its already part of infisical
         // Thus the signup flow is not needed anymore
@@ -403,6 +412,7 @@ export const orgServiceFactory = ({
       // not invited before
       const user = await userDAL.create(
         {
+          username: inviteeEmail,
           email: inviteeEmail,
           isAccepted: false,
           authMethods: [AuthMethod.EMAIL],
@@ -437,7 +447,7 @@ export const orgServiceFactory = ({
       recipients: [inviteeEmail],
       substitutions: {
         inviterFirstName: user.firstName,
-        inviterEmail: user.email,
+        inviterUsername: user.username,
         organizationName: org?.name,
         email: inviteeEmail,
         organizationId: org?.id.toString(),
@@ -457,7 +467,7 @@ export const orgServiceFactory = ({
    * magic link and issue a temporary signup token for user to complete setting up their account
    */
   const verifyUserToOrg = async ({ orgId, email, code }: TVerifyUserToOrgDTO) => {
-    const user = await userDAL.findUserByEmail(email);
+    const user = await userDAL.findUserByUsername(email);
     if (!user) {
       throw new BadRequestError({ message: "Invalid request", name: "Verify user to org" });
     }
@@ -595,7 +605,7 @@ export const orgServiceFactory = ({
     inviteUserToOrganization,
     verifyUserToOrg,
     updateOrg,
-    findOrgMembersByEmail,
+    findOrgMembersByUsername,
     createOrganization,
     deleteOrganizationById,
     deleteOrgMembership,

backend/src/services/project-bot/project-bot-fns.ts

@@ -0,0 +1,36 @@
+import { SecretKeyEncoding } from "@app/db/schemas";
+import { decryptAsymmetric, infisicalSymmetricDecrypt } from "@app/lib/crypto/encryption";
+import { BadRequestError } from "@app/lib/errors";
+import { TProjectBotDALFactory } from "@app/services/project-bot/project-bot-dal";
+
+import { TGetPrivateKeyDTO } from "./project-bot-types";
+
+export const getBotPrivateKey = ({ bot }: TGetPrivateKeyDTO) =>
+  infisicalSymmetricDecrypt({
+    keyEncoding: bot.keyEncoding as SecretKeyEncoding,
+    iv: bot.iv,
+    tag: bot.tag,
+    ciphertext: bot.encryptedPrivateKey
+  });
+
+export const getBotKeyFnFactory = (projectBotDAL: TProjectBotDALFactory) => {
+  const getBotKeyFn = async (projectId: string) => {
+    const bot = await projectBotDAL.findOne({ projectId });
+
+    if (!bot) throw new BadRequestError({ message: "failed to find bot key" });
+    if (!bot.isActive) throw new BadRequestError({ message: "Bot is not active" });
+    if (!bot.encryptedProjectKeyNonce || !bot.encryptedProjectKey)
+      throw new BadRequestError({ message: "Encryption key missing" });
+
+    const botPrivateKey = getBotPrivateKey({ bot });
+
+    return decryptAsymmetric({
+      ciphertext: bot.encryptedProjectKey,
+      privateKey: botPrivateKey,
+      nonce: bot.encryptedProjectKeyNonce,
+      publicKey: bot.sender.publicKey
+    });
+  };
+
+  return getBotKeyFn;
+};

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

@@ -1,15 +1,16 @@
 import { ForbiddenError } from "@casl/ability";
 
-import { ProjectVersion, SecretKeyEncoding } from "@app/db/schemas";
+import { ProjectVersion } from "@app/db/schemas";
 import { TPermissionServiceFactory } from "@app/ee/services/permission/permission-service";
 import { ProjectPermissionActions, ProjectPermissionSub } from "@app/ee/services/permission/project-permission";
-import { decryptAsymmetric, generateAsymmetricKeyPair } from "@app/lib/crypto";
-import { infisicalSymmetricDecrypt, infisicalSymmetricEncypt } from "@app/lib/crypto/encryption";
+import { generateAsymmetricKeyPair } from "@app/lib/crypto";
+import { infisicalSymmetricEncypt } from "@app/lib/crypto/encryption";
 import { BadRequestError } from "@app/lib/errors";
 
 import { TProjectDALFactory } from "../project/project-dal";
 import { TProjectBotDALFactory } from "./project-bot-dal";
-import { TFindBotByProjectIdDTO, TGetPrivateKeyDTO, TSetActiveStateDTO } from "./project-bot-types";
+import { getBotKeyFnFactory, getBotPrivateKey } from "./project-bot-fns";
+import { TFindBotByProjectIdDTO, TSetActiveStateDTO } from "./project-bot-types";
 
 type TProjectBotServiceFactoryDep = {
   permissionService: Pick<TPermissionServiceFactory, "getProjectPermission">;
@@ -24,29 +25,10 @@ export const projectBotServiceFactory = ({
   projectDAL,
   permissionService
 }: TProjectBotServiceFactoryDep) => {
-  const getBotPrivateKey = ({ bot }: TGetPrivateKeyDTO) =>
-    infisicalSymmetricDecrypt({
-      keyEncoding: bot.keyEncoding as SecretKeyEncoding,
-      iv: bot.iv,
-      tag: bot.tag,
-      ciphertext: bot.encryptedPrivateKey
-    });
+  const getBotKeyFn = getBotKeyFnFactory(projectBotDAL);
 
   const getBotKey = async (projectId: string) => {
-    const bot = await projectBotDAL.findOne({ projectId });
-    if (!bot) throw new BadRequestError({ message: "failed to find bot key" });
-    if (!bot.isActive) throw new BadRequestError({ message: "Bot is not active" });
-    if (!bot.encryptedProjectKeyNonce || !bot.encryptedProjectKey)
-      throw new BadRequestError({ message: "Encryption key missing" });
-
-    const botPrivateKey = getBotPrivateKey({ bot });
-
-    return decryptAsymmetric({
-      ciphertext: bot.encryptedProjectKey,
-      privateKey: botPrivateKey,
-      nonce: bot.encryptedProjectKeyNonce,
-      publicKey: bot.sender.publicKey
-    });
+    return getBotKeyFn(projectId);
   };
 
   const findBotByProjectId = async ({

backend/src/services/project-membership/project-membership-dal.ts

@@ -1,7 +1,7 @@
 import { TDbClient } from "@app/db";
 import { TableName, TUserEncryptionKeys } from "@app/db/schemas";
 import { DatabaseError } from "@app/lib/errors";
-import { ormify, selectAllTableCols } from "@app/lib/knex";
+import { ormify, selectAllTableCols, sqlNestRelationships } from "@app/lib/knex";
 
 export type TProjectMembershipDALFactory = ReturnType<typeof projectMembershipDALFactory>;
 
@@ -11,31 +11,86 @@ export const projectMembershipDALFactory = (db: TDbClient) => {
   // special query
   const findAllProjectMembers = async (projectId: string) => {
     try {
-      const members = await db(TableName.ProjectMembership)
-        .where({ projectId })
+      const docs = await db(TableName.ProjectMembership)
+        .where({ [`${TableName.ProjectMembership}.projectId` as "projectId"]: projectId })
         .join(TableName.Users, `${TableName.ProjectMembership}.userId`, `${TableName.Users}.id`)
         .join<TUserEncryptionKeys>(
           TableName.UserEncryptionKey,
           `${TableName.UserEncryptionKey}.userId`,
           `${TableName.Users}.id`
         )
+        .join(
+          TableName.ProjectUserMembershipRole,
+          `${TableName.ProjectUserMembershipRole}.projectMembershipId`,
+          `${TableName.ProjectMembership}.id`
+        )
+        .leftJoin(
+          TableName.ProjectRoles,
+          `${TableName.ProjectUserMembershipRole}.customRoleId`,
+          `${TableName.ProjectRoles}.id`
+        )
         .select(
           db.ref("id").withSchema(TableName.ProjectMembership),
-          db.ref("projectId").withSchema(TableName.ProjectMembership),
-          db.ref("role").withSchema(TableName.ProjectMembership),
-          db.ref("roleId").withSchema(TableName.ProjectMembership),
           db.ref("isGhost").withSchema(TableName.Users),
+          db.ref("username").withSchema(TableName.Users),
           db.ref("email").withSchema(TableName.Users),
           db.ref("publicKey").withSchema(TableName.UserEncryptionKey),
           db.ref("firstName").withSchema(TableName.Users),
           db.ref("lastName").withSchema(TableName.Users),
-          db.ref("id").withSchema(TableName.Users).as("userId")
+          db.ref("id").withSchema(TableName.Users).as("userId"),
+          db.ref("role").withSchema(TableName.ProjectUserMembershipRole),
+          db.ref("id").withSchema(TableName.ProjectUserMembershipRole).as("membershipRoleId"),
+          db.ref("customRoleId").withSchema(TableName.ProjectUserMembershipRole),
+          db.ref("name").withSchema(TableName.ProjectRoles).as("customRoleName"),
+          db.ref("slug").withSchema(TableName.ProjectRoles).as("customRoleSlug"),
+          db.ref("temporaryMode").withSchema(TableName.ProjectUserMembershipRole),
+          db.ref("isTemporary").withSchema(TableName.ProjectUserMembershipRole),
+          db.ref("temporaryRange").withSchema(TableName.ProjectUserMembershipRole),
+          db.ref("temporaryAccessStartTime").withSchema(TableName.ProjectUserMembershipRole),
+          db.ref("temporaryAccessEndTime").withSchema(TableName.ProjectUserMembershipRole)
         )
         .where({ isGhost: false });
-      return members.map(({ email, firstName, lastName, publicKey, isGhost, ...data }) => ({
-        ...data,
-        user: { email, firstName, lastName, id: data.userId, publicKey, isGhost }
-      }));
+
+      const members = sqlNestRelationships({
+        data: docs,
+        parentMapper: ({ email, firstName, username, lastName, publicKey, isGhost, id, userId }) => ({
+          id,
+          userId,
+          projectId,
+          user: { email, username, firstName, lastName, id: userId, publicKey, isGhost }
+        }),
+        key: "id",
+        childrenMapper: [
+          {
+            label: "roles" as const,
+            key: "membershipRoleId",
+            mapper: ({
+              role,
+              customRoleId,
+              customRoleName,
+              customRoleSlug,
+              membershipRoleId,
+              temporaryRange,
+              temporaryMode,
+              temporaryAccessEndTime,
+              temporaryAccessStartTime,
+              isTemporary
+            }) => ({
+              id: membershipRoleId,
+              role,
+              customRoleId,
+              customRoleName,
+              customRoleSlug,
+              temporaryRange,
+              temporaryMode,
+              temporaryAccessEndTime,
+              temporaryAccessStartTime,
+              isTemporary
+            })
+          }
+        ]
+      });
+      return members;
     } catch (error) {
       throw new DatabaseError({ error, name: "Find all project members" });
     }
@@ -56,7 +111,7 @@ export const projectMembershipDALFactory = (db: TDbClient) => {
     }
   };
 
-  const findMembershipsByEmail = async (projectId: string, emails: string[]) => {
+  const findMembershipsByUsername = async (projectId: string, usernames: string[]) => {
     try {
       const members = await db(TableName.ProjectMembership)
         .where({ projectId })
@@ -69,13 +124,13 @@ export const projectMembershipDALFactory = (db: TDbClient) => {
         .select(
           selectAllTableCols(TableName.ProjectMembership),
           db.ref("id").withSchema(TableName.Users).as("userId"),
-          db.ref("email").withSchema(TableName.Users)
+          db.ref("username").withSchema(TableName.Users)
         )
-        .whereIn("email", emails)
+        .whereIn("username", usernames)
         .where({ isGhost: false });
-      return members.map(({ userId, email, ...data }) => ({
+      return members.map(({ userId, username, ...data }) => ({
         ...data,
-        user: { id: userId, email }
+        user: { id: userId, username }
       }));
     } catch (error) {
       throw new DatabaseError({ error, name: "Find members by email" });
@@ -100,7 +155,7 @@ export const projectMembershipDALFactory = (db: TDbClient) => {
     ...projectMemberOrm,
     findAllProjectMembers,
     findProjectGhostUser,
-    findMembershipsByEmail,
+    findMembershipsByUsername,
     findProjectMembershipsByUserId
   };
 };

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

@@ -1,14 +1,13 @@
 /* eslint-disable no-await-in-loop */
 import { ForbiddenError } from "@casl/ability";
+import ms from "ms";
 
 import {
-  OrgMembershipStatus,
   ProjectMembershipRole,
   ProjectVersion,
   SecretKeyEncoding,
   TableName,
-  TProjectMemberships,
-  TUsers
+  TProjectMemberships
 } from "@app/db/schemas";
 import { TLicenseServiceFactory } from "@app/ee/services/license/license-service";
 import { TPermissionServiceFactory } from "@app/ee/services/permission/permission-service";
@@ -29,23 +28,25 @@ import { SmtpTemplates, TSmtpService } from "../smtp/smtp-service";
 import { TUserDALFactory } from "../user/user-dal";
 import { TProjectMembershipDALFactory } from "./project-membership-dal";
 import {
+  ProjectUserMembershipTemporaryMode,
   TAddUsersToWorkspaceDTO,
   TAddUsersToWorkspaceNonE2EEDTO,
   TDeleteProjectMembershipOldDTO,
   TDeleteProjectMembershipsDTO,
   TGetProjectMembershipDTO,
-  TInviteUserToProjectDTO,
   TUpdateProjectMembershipDTO
 } from "./project-membership-types";
+import { TProjectUserMembershipRoleDALFactory } from "./project-user-membership-role-dal";
 
 type TProjectMembershipServiceFactoryDep = {
   permissionService: Pick<TPermissionServiceFactory, "getProjectPermission">;
   smtpService: TSmtpService;
   projectBotDAL: TProjectBotDALFactory;
   projectMembershipDAL: TProjectMembershipDALFactory;
+  projectUserMembershipRoleDAL: Pick<TProjectUserMembershipRoleDALFactory, "insertMany" | "find" | "delete">;
   userDAL: Pick<TUserDALFactory, "findById" | "findOne" | "findUserByProjectMembershipId" | "find">;
-  projectRoleDAL: Pick<TProjectRoleDALFactory, "findOne">;
-  orgDAL: Pick<TOrgDALFactory, "findMembership" | "findOrgMembersByEmail">;
+  projectRoleDAL: Pick<TProjectRoleDALFactory, "find">;
+  orgDAL: Pick<TOrgDALFactory, "findMembership" | "findOrgMembersByUsername">;
   projectDAL: Pick<TProjectDALFactory, "findById" | "findProjectGhostUser" | "transaction">;
   projectKeyDAL: Pick<TProjectKeyDALFactory, "findLatestProjectKey" | "delete" | "insertMany">;
   licenseService: Pick<TLicenseServiceFactory, "getPlan">;
@@ -56,6 +57,7 @@ export type TProjectMembershipServiceFactory = ReturnType<typeof projectMembersh
 export const projectMembershipServiceFactory = ({
   permissionService,
   projectMembershipDAL,
+  projectUserMembershipRoleDAL,
   smtpService,
   projectRoleDAL,
   projectBotDAL,
@@ -72,82 +74,6 @@ export const projectMembershipServiceFactory = ({
     return projectMembershipDAL.findAllProjectMembers(projectId);
   };
 
-  const inviteUserToProject = async ({ actorId, actor, actorOrgId, projectId, emails }: TInviteUserToProjectDTO) => {
-    const { permission } = await permissionService.getProjectPermission(actor, actorId, projectId, actorOrgId);
-    ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Create, ProjectPermissionSub.Member);
-
-    const invitees: TUsers[] = [];
-
-    const project = await projectDAL.findById(projectId);
-    const users = await userDAL.find({
-      $in: { email: emails }
-    });
-
-    await projectDAL.transaction(async (tx) => {
-      for (const invitee of users) {
-        if (!invitee.isAccepted)
-          throw new BadRequestError({
-            message: "Failed to validate invitee",
-            name: "Invite user to project"
-          });
-
-        const inviteeMembership = await projectMembershipDAL.findOne(
-          {
-            userId: invitee.id,
-            projectId
-          },
-          tx
-        );
-
-        if (inviteeMembership) {
-          throw new BadRequestError({
-            message: "Existing member of project",
-            name: "Invite user to project"
-          });
-        }
-
-        const inviteeMembershipOrg = await orgDAL.findMembership({
-          userId: invitee.id,
-          orgId: project.orgId,
-          status: OrgMembershipStatus.Accepted
-        });
-
-        if (!inviteeMembershipOrg) {
-          throw new BadRequestError({
-            message: "Failed to validate invitee org membership",
-            name: "Invite user to project"
-          });
-        }
-
-        await projectMembershipDAL.create(
-          {
-            userId: invitee.id,
-            projectId,
-            role: ProjectMembershipRole.Member
-          },
-          tx
-        );
-
-        invitees.push(invitee);
-      }
-
-      const appCfg = getConfig();
-      await smtpService.sendMail({
-        template: SmtpTemplates.WorkspaceInvite,
-        subjectLine: "Infisical workspace invitation",
-        recipients: invitees.map((i) => i.email),
-        substitutions: {
-          workspaceName: project.name,
-          callback_url: `${appCfg.SITE_URL}/login`
-        }
-      });
-    });
-
-    const latestKey = await projectKeyDAL.findLatestProjectKey(actorId, projectId);
-
-    return { invitees, latestKey };
-  };
-
   const addUsersToProject = async ({
     projectId,
     actorId,
@@ -176,17 +102,16 @@ export const projectMembershipServiceFactory = ({
     if (existingMembers.length) throw new BadRequestError({ message: "Some users are already part of project" });
 
     await projectMembershipDAL.transaction(async (tx) => {
-      await projectMembershipDAL.insertMany(
-        orgMembers.map(({ userId, id: membershipId }) => {
-          const role =
-            members.find((i) => i.orgMembershipId === membershipId)?.projectRole || ProjectMembershipRole.Member;
-
-          return {
-            projectId,
-            userId: userId as string,
-            role
-          };
-        }),
+      const projectMemberships = await projectMembershipDAL.insertMany(
+        orgMembers.map(({ userId }) => ({
+          projectId,
+          userId: userId as string,
+          role: ProjectMembershipRole.Member
+        })),
+        tx
+      );
+      await projectUserMembershipRoleDAL.insertMany(
+        projectMemberships.map(({ id }) => ({ projectMembershipId: id, role: ProjectMembershipRole.Member })),
         tx
       );
       const encKeyGroupByOrgMembId = groupBy(members, (i) => i.orgMembershipId);
@@ -206,8 +131,8 @@ export const projectMembershipServiceFactory = ({
       const appCfg = getConfig();
       await smtpService.sendMail({
         template: SmtpTemplates.WorkspaceInvite,
-        subjectLine: "Infisical workspace invitation",
-        recipients: orgMembers.map(({ email }) => email).filter(Boolean),
+        subjectLine: "Infisical project invitation",
+        recipients: orgMembers.filter((i) => i.email).map((i) => i.email as string),
         substitutions: {
           workspaceName: project.name,
           callback_url: `${appCfg.SITE_URL}/login`
@@ -222,6 +147,7 @@ export const projectMembershipServiceFactory = ({
     actorId,
     actor,
     emails,
+    usernames,
     sendEmails = true
   }: TAddUsersToWorkspaceNonE2EEDTO) => {
     const project = await projectDAL.findById(projectId);
@@ -234,9 +160,14 @@ export const projectMembershipServiceFactory = ({
     const { permission } = await permissionService.getProjectPermission(actor, actorId, projectId);
     ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Create, ProjectPermissionSub.Member);
 
-    const orgMembers = await orgDAL.findOrgMembersByEmail(project.orgId, emails);
+    const usernamesAndEmails = [...emails, ...usernames];
 
-    if (orgMembers.length !== emails.length) throw new BadRequestError({ message: "Some users are not part of org" });
+    const orgMembers = await orgDAL.findOrgMembersByUsername(project.orgId, [
+      ...new Set(usernamesAndEmails.map((element) => element.toLowerCase()))
+    ]);
+
+    if (orgMembers.length !== usernamesAndEmails.length)
+      throw new BadRequestError({ message: "Some users are not part of org" });
 
     if (!orgMembers.length) return [];
 
@@ -290,7 +221,7 @@ export const projectMembershipServiceFactory = ({
     const members: TProjectMemberships[] = [];
 
     await projectMembershipDAL.transaction(async (tx) => {
-      const result = await projectMembershipDAL.insertMany(
+      const projectMemberships = await projectMembershipDAL.insertMany(
         orgMembers.map(({ user }) => ({
           projectId,
           userId: user.id,
@@ -298,8 +229,12 @@ export const projectMembershipServiceFactory = ({
         })),
         tx
       );
+      await projectUserMembershipRoleDAL.insertMany(
+        projectMemberships.map(({ id }) => ({ projectMembershipId: id, role: ProjectMembershipRole.Member })),
+        tx
+      );
 
-      members.push(...result);
+      members.push(...projectMemberships);
 
       const encKeyGroupByOrgMembId = groupBy(newWsMembers, (i) => i.orgMembershipId);
       await projectKeyDAL.insertMany(
@@ -315,16 +250,21 @@ export const projectMembershipServiceFactory = ({
     });
 
     if (sendEmails) {
+      const recipients = orgMembers.filter((i) => i.user.email).map((i) => i.user.email as string);
+
       const appCfg = getConfig();
-      await smtpService.sendMail({
-        template: SmtpTemplates.WorkspaceInvite,
-        subjectLine: "Infisical workspace invitation",
-        recipients: orgMembers.map(({ user }) => user.email).filter(Boolean),
-        substitutions: {
-          workspaceName: project.name,
-          callback_url: `${appCfg.SITE_URL}/login`
-        }
-      });
+
+      if (recipients.length) {
+        await smtpService.sendMail({
+          template: SmtpTemplates.WorkspaceInvite,
+          subjectLine: "Infisical project invitation",
+          recipients: orgMembers.filter((i) => i.user.email).map((i) => i.user.email as string),
+          substitutions: {
+            workspaceName: project.name,
+            callback_url: `${appCfg.SITE_URL}/login`
+          }
+        });
+      }
     }
     return members;
   };
@@ -335,43 +275,71 @@ export const projectMembershipServiceFactory = ({
     actorOrgId,
     projectId,
     membershipId,
-    role
+    roles
   }: TUpdateProjectMembershipDTO) => {
     const { permission } = await permissionService.getProjectPermission(actor, actorId, projectId, actorOrgId);
     ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Edit, ProjectPermissionSub.Member);
 
     const membershipUser = await userDAL.findUserByProjectMembershipId(membershipId);
-
-    if (membershipUser?.isGhost) {
+    if (membershipUser?.isGhost || membershipUser?.projectId !== projectId) {
       throw new BadRequestError({
         message: "Unauthorized member update",
         name: "Update project membership"
       });
     }
 
-    const isCustomRole = !Object.values(ProjectMembershipRole).includes(role as ProjectMembershipRole);
-    if (isCustomRole) {
-      const customRole = await projectRoleDAL.findOne({ slug: role, projectId });
-      if (!customRole) throw new BadRequestError({ name: "Update project membership", message: "Role not found" });
-      const project = await projectDAL.findById(customRole.projectId);
-      const plan = await licenseService.getPlan(project.orgId);
+    // validate custom roles input
+    const customInputRoles = roles.filter(
+      ({ role }) => !Object.values(ProjectMembershipRole).includes(role as ProjectMembershipRole)
+    );
+    const hasCustomRole = Boolean(customInputRoles.length);
+    if (hasCustomRole) {
+      const plan = await licenseService.getPlan(actorOrgId as string);
       if (!plan?.rbac)
         throw new BadRequestError({
           message: "Failed to assign custom role due to RBAC restriction. Upgrade plan to assign custom role to member."
         });
-
-      const [membership] = await projectMembershipDAL.update(
-        { id: membershipId, projectId },
-        {
-          role: ProjectMembershipRole.Custom,
-          roleId: customRole.id
-        }
-      );
-      return membership;
     }
 
-    const [membership] = await projectMembershipDAL.update({ id: membershipId, projectId }, { role, roleId: null });
-    return membership;
+    const customRoles = hasCustomRole
+      ? await projectRoleDAL.find({
+          projectId,
+          $in: { slug: customInputRoles.map(({ role }) => role) }
+        })
+      : [];
+    if (customRoles.length !== customInputRoles.length) throw new BadRequestError({ message: "Custom role not found" });
+    const customRolesGroupBySlug = groupBy(customRoles, ({ slug }) => slug);
+
+    const santiziedProjectMembershipRoles = roles.map((inputRole) => {
+      const isCustomRole = Boolean(customRolesGroupBySlug?.[inputRole.role]?.[0]);
+      if (!inputRole.isTemporary) {
+        return {
+          projectMembershipId: membershipId,
+          role: isCustomRole ? ProjectMembershipRole.Custom : inputRole.role,
+          customRoleId: customRolesGroupBySlug[inputRole.role] ? customRolesGroupBySlug[inputRole.role][0].id : null
+        };
+      }
+
+      // check cron or relative here later for now its just relative
+      const relativeTimeInMs = ms(inputRole.temporaryRange);
+      return {
+        projectMembershipId: membershipId,
+        role: isCustomRole ? ProjectMembershipRole.Custom : inputRole.role,
+        customRoleId: customRolesGroupBySlug[inputRole.role] ? customRolesGroupBySlug[inputRole.role][0].id : null,
+        isTemporary: true,
+        temporaryMode: ProjectUserMembershipTemporaryMode.Relative,
+        temporaryRange: inputRole.temporaryRange,
+        temporaryAccessStartTime: new Date(inputRole.temporaryAccessStartTime),
+        temporaryAccessEndTime: new Date(new Date(inputRole.temporaryAccessStartTime).getTime() + relativeTimeInMs)
+      };
+    });
+
+    const updatedRoles = await projectMembershipDAL.transaction(async (tx) => {
+      await projectUserMembershipRoleDAL.delete({ projectMembershipId: membershipId }, tx);
+      return projectUserMembershipRoleDAL.insertMany(santiziedProjectMembershipRoles, tx);
+    });
+
+    return updatedRoles;
   };
 
   // This is old and should be removed later. Its not used anywhere, but it is exposed in our API. So to avoid breaking changes, we are keeping it for now.
@@ -407,7 +375,8 @@ export const projectMembershipServiceFactory = ({
     actor,
     actorOrgId,
     projectId,
-    emails
+    emails,
+    usernames
   }: TDeleteProjectMembershipsDTO) => {
     const { permission } = await permissionService.getProjectPermission(actor, actorId, projectId, actorOrgId);
     ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Delete, ProjectPermissionSub.Member);
@@ -421,9 +390,13 @@ export const projectMembershipServiceFactory = ({
       });
     }
 
-    const projectMembers = await projectMembershipDAL.findMembershipsByEmail(projectId, emails);
+    const usernamesAndEmails = [...emails, ...usernames];
 
-    if (projectMembers.length !== emails.length) {
+    const projectMembers = await projectMembershipDAL.findMembershipsByUsername(projectId, [
+      ...new Set(usernamesAndEmails.map((element) => element.toLowerCase()))
+    ]);
+
+    if (projectMembers.length !== usernamesAndEmails.length) {
       throw new BadRequestError({
         message: "Some users are not part of project",
         name: "Delete project membership"
@@ -465,7 +438,6 @@ export const projectMembershipServiceFactory = ({
 
   return {
     getProjectMemberships,
-    inviteUserToProject,
     updateProjectMembership,
     addUsersToProjectNonE2EE,
     deleteProjectMemberships,

backend/src/services/project-membership/project-membership-types.ts

@@ -1,7 +1,9 @@
-import { ProjectMembershipRole } from "@app/db/schemas";
 import { TProjectPermission } from "@app/lib/types";
 
 export type TGetProjectMembershipDTO = TProjectPermission;
+export enum ProjectUserMembershipTemporaryMode {
+  Relative = "relative"
+}
 
 export type TInviteUserToProjectDTO = {
   emails: string[];
@@ -9,7 +11,19 @@ export type TInviteUserToProjectDTO = {
 
 export type TUpdateProjectMembershipDTO = {
   membershipId: string;
-  role: string;
+  roles: (
+    | {
+        role: string;
+        isTemporary?: false;
+      }
+    | {
+        role: string;
+        isTemporary: true;
+        temporaryMode: ProjectUserMembershipTemporaryMode.Relative;
+        temporaryRange: string;
+        temporaryAccessStartTime: string;
+      }
+  )[];
 } & TProjectPermission;
 
 export type TDeleteProjectMembershipOldDTO = {
@@ -18,6 +32,7 @@ export type TDeleteProjectMembershipOldDTO = {
 
 export type TDeleteProjectMembershipsDTO = {
   emails: string[];
+  usernames: string[];
 } & TProjectPermission;
 
 export type TAddUsersToWorkspaceDTO = {
@@ -26,11 +41,11 @@ export type TAddUsersToWorkspaceDTO = {
     orgMembershipId: string;
     workspaceEncryptedKey: string;
     workspaceEncryptedNonce: string;
-    projectRole: ProjectMembershipRole;
   }[];
 } & TProjectPermission;
 
 export type TAddUsersToWorkspaceNonE2EEDTO = {
   sendEmails?: boolean;
   emails: string[];
+  usernames: string[];
 } & TProjectPermission;

backend/src/services/project-membership/project-user-membership-role-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 TProjectUserMembershipRoleDALFactory = ReturnType<typeof projectUserMembershipRoleDALFactory>;
+
+export const projectUserMembershipRoleDALFactory = (db: TDbClient) => {
+  const orm = ormify(db, TableName.ProjectUserMembershipRole);
+  return orm;
+};

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

@@ -76,7 +76,7 @@ export const projectRoleServiceFactory = ({ projectRoleDAL, permissionService }:
     const { permission } = await permissionService.getProjectPermission(actor, actorId, projectId, actorOrgId);
     ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Delete, ProjectPermissionSub.Role);
     const [deletedRole] = await projectRoleDAL.delete({ id: roleId, projectId });
-    if (!deleteRole) throw new BadRequestError({ message: "Role not found", name: "Update role" });
+    if (!deletedRole) throw new BadRequestError({ message: "Role not found", name: "Update role" });
 
     return deletedRole;
   };
@@ -92,7 +92,7 @@ export const projectRoleServiceFactory = ({ projectRoleDAL, permissionService }:
         name: "Admin",
         slug: ProjectMembershipRole.Admin,
         description: "Complete administration access over the project",
-        permissions: packRules(projectAdminPermissions.rules),
+        permissions: packRules(projectAdminPermissions),
         createdAt: new Date(),
         updatedAt: new Date()
       },
@@ -102,7 +102,7 @@ export const projectRoleServiceFactory = ({ projectRoleDAL, permissionService }:
         name: "Developer",
         slug: ProjectMembershipRole.Member,
         description: "Non-administrative role in an project",
-        permissions: packRules(projectMemberPermissions.rules),
+        permissions: packRules(projectMemberPermissions),
         createdAt: new Date(),
         updatedAt: new Date()
       },
@@ -112,7 +112,7 @@ export const projectRoleServiceFactory = ({ projectRoleDAL, permissionService }:
         name: "Viewer",
         slug: ProjectMembershipRole.Viewer,
         description: "Non-administrative role in an project",
-        permissions: packRules(projectViewerPermission.rules),
+        permissions: packRules(projectViewerPermission),
         createdAt: new Date(),
         updatedAt: new Date()
       },
@@ -122,7 +122,7 @@ export const projectRoleServiceFactory = ({ projectRoleDAL, permissionService }:
         name: "No Access",
         slug: "no-access",
         description: "No access to any resources in the project",
-        permissions: packRules(projectNoAccessPermissions.rules),
+        permissions: packRules(projectNoAccessPermissions),
         createdAt: new Date(),
         updatedAt: new Date()
       },

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

@@ -38,6 +38,7 @@ import { TProjectBotDALFactory } from "../project-bot/project-bot-dal";
 import { TProjectEnvDALFactory } from "../project-env/project-env-dal";
 import { TProjectKeyDALFactory } from "../project-key/project-key-dal";
 import { TProjectMembershipDALFactory } from "../project-membership/project-membership-dal";
+import { TProjectUserMembershipRoleDALFactory } from "../project-membership/project-user-membership-role-dal";
 import { TSecretDALFactory } from "../secret/secret-dal";
 import { TSecretVersionDALFactory } from "../secret/secret-version-dal";
 import { TSecretFolderDALFactory } from "../secret-folder/secret-folder-dal";
@@ -58,9 +59,9 @@ type TProjectQueueFactoryDep = {
   projectBotDAL: Pick<TProjectBotDALFactory, "findOne" | "delete" | "create">;
   orgService: Pick<TOrgServiceFactory, "addGhostUser">;
   projectMembershipDAL: Pick<TProjectMembershipDALFactory, "create">;
+  projectUserMembershipRoleDAL: Pick<TProjectUserMembershipRoleDALFactory, "create">;
   integrationAuthDAL: TIntegrationAuthDALFactory;
   userDAL: Pick<TUserDALFactory, "findUserEncKeyByUserId">;
-
   projectEnvDAL: Pick<TProjectEnvDALFactory, "find">;
   projectDAL: Pick<TProjectDALFactory, "findOne" | "transaction" | "updateById" | "setProjectUpgradeStatus" | "find">;
   orgDAL: Pick<TOrgDALFactory, "findMembership">;
@@ -81,7 +82,8 @@ export const projectQueueFactory = ({
   orgDAL,
   projectDAL,
   orgService,
-  projectMembershipDAL
+  projectMembershipDAL,
+  projectUserMembershipRoleDAL
 }: TProjectQueueFactoryDep) => {
   const upgradeProject = async (dto: TQueueJobTypes["upgrade-project-to-ghost"]["payload"]) => {
     await queueService.queue(QueueName.UpgradeProjectToGhost, QueueJobs.UpgradeProjectToGhost, dto, {
@@ -227,7 +229,7 @@ export const projectQueueFactory = ({
         );
 
         // Create a membership for the ghost user
-        await projectMembershipDAL.create(
+        const projectMembership = await projectMembershipDAL.create(
           {
             projectId: project.id,
             userId: ghostUser.user.id,
@@ -235,6 +237,10 @@ export const projectQueueFactory = ({
           },
           tx
         );
+        await projectUserMembershipRoleDAL.create(
+          { projectMembershipId: projectMembership.id, role: ProjectMembershipRole.Admin },
+          tx
+        );
 
         // If a bot already exists, delete it
         if (existingBot) {

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

@@ -17,11 +17,13 @@ import { TProjectPermission } from "@app/lib/types";
 import { ActorType } from "../auth/auth-type";
 import { TIdentityOrgDALFactory } from "../identity/identity-org-dal";
 import { TIdentityProjectDALFactory } from "../identity-project/identity-project-dal";
+import { TIdentityProjectMembershipRoleDALFactory } from "../identity-project/identity-project-membership-role-dal";
 import { TOrgServiceFactory } from "../org/org-service";
 import { TProjectBotDALFactory } from "../project-bot/project-bot-dal";
 import { TProjectEnvDALFactory } from "../project-env/project-env-dal";
 import { TProjectKeyDALFactory } from "../project-key/project-key-dal";
 import { TProjectMembershipDALFactory } from "../project-membership/project-membership-dal";
+import { TProjectUserMembershipRoleDALFactory } from "../project-membership/project-user-membership-role-dal";
 import { TSecretBlindIndexDALFactory } from "../secret-blind-index/secret-blind-index-dal";
 import { ROOT_FOLDER_NAME, TSecretFolderDALFactory } from "../secret-folder/secret-folder-dal";
 import { TUserDALFactory } from "../user/user-dal";
@@ -50,9 +52,11 @@ type TProjectServiceFactoryDep = {
   projectEnvDAL: Pick<TProjectEnvDALFactory, "insertMany" | "find">;
   identityOrgMembershipDAL: TIdentityOrgDALFactory;
   identityProjectDAL: TIdentityProjectDALFactory;
+  identityProjectMembershipRoleDAL: Pick<TIdentityProjectMembershipRoleDALFactory, "create">;
   projectKeyDAL: Pick<TProjectKeyDALFactory, "create" | "findLatestProjectKey" | "delete" | "find" | "insertMany">;
   projectBotDAL: Pick<TProjectBotDALFactory, "create" | "findById" | "delete" | "findOne">;
   projectMembershipDAL: Pick<TProjectMembershipDALFactory, "create" | "findProjectGhostUser" | "findOne">;
+  projectUserMembershipRoleDAL: Pick<TProjectUserMembershipRoleDALFactory, "create">;
   secretBlindIndexDAL: Pick<TSecretBlindIndexDALFactory, "create">;
   permissionService: TPermissionServiceFactory;
   orgService: Pick<TOrgServiceFactory, "addGhostUser">;
@@ -75,7 +79,9 @@ export const projectServiceFactory = ({
   secretBlindIndexDAL,
   projectMembershipDAL,
   projectEnvDAL,
-  licenseService
+  licenseService,
+  projectUserMembershipRoleDAL,
+  identityProjectMembershipRoleDAL
 }: TProjectServiceFactoryDep) => {
   /*
    * Create workspace. Make user the admin
@@ -114,14 +120,18 @@ export const projectServiceFactory = ({
         tx
       );
       // set ghost user as admin of project
-      await projectMembershipDAL.create(
+      const projectMembership = await projectMembershipDAL.create(
         {
           userId: ghostUser.user.id,
-          role: ProjectMembershipRole.Admin,
-          projectId: project.id
+          projectId: project.id,
+          role: ProjectMembershipRole.Admin
         },
         tx
       );
+      await projectUserMembershipRoleDAL.create(
+        { projectMembershipId: projectMembership.id, role: ProjectMembershipRole.Admin },
+        tx
+      );
 
       // generate the blind index for project
       await secretBlindIndexDAL.create(
@@ -213,7 +223,7 @@ export const projectServiceFactory = ({
         });
 
         // Create a membership for the user
-        await projectMembershipDAL.create(
+        const userProjectMembership = await projectMembershipDAL.create(
           {
             projectId: project.id,
             userId: user.id,
@@ -221,6 +231,10 @@ export const projectServiceFactory = ({
           },
           tx
         );
+        await projectUserMembershipRoleDAL.create(
+          { projectMembershipId: userProjectMembership.id, role: projectAdmin.projectRole },
+          tx
+        );
 
         // Create a project key for the user
         await projectKeyDAL.create(
@@ -266,7 +280,7 @@ export const projectServiceFactory = ({
           });
         const isCustomRole = Boolean(customRole);
 
-        await identityProjectDAL.create(
+        const identityProjectMembership = await identityProjectDAL.create(
           {
             identityId: actorId,
             projectId: project.id,
@@ -275,6 +289,15 @@ export const projectServiceFactory = ({
           },
           tx
         );
+
+        await identityProjectMembershipRoleDAL.create(
+          {
+            projectMembershipId: identityProjectMembership.id,
+            role: isCustomRole ? ProjectMembershipRole.Custom : ProjectMembershipRole.Admin,
+            customRoleId: customRole?.id
+          },
+          tx
+        );
       }
 
       return {
@@ -350,11 +373,11 @@ export const projectServiceFactory = ({
   };
 
   const upgradeProject = async ({ projectId, actor, actorId, userPrivateKey }: TUpgradeProjectDTO) => {
-    const { permission, membership } = await permissionService.getProjectPermission(actor, actorId, projectId);
+    const { permission, hasRole } = await permissionService.getProjectPermission(actor, actorId, projectId);
 
     ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Delete, ProjectPermissionSub.Project);
 
-    if (membership?.role !== ProjectMembershipRole.Admin) {
+    if (!hasRole(ProjectMembershipRole.Admin)) {
       throw new ForbiddenRequestError({
         message: "User must be admin"
       });

backend/src/services/secret-blind-index/secret-blind-index-service.ts

@@ -37,8 +37,8 @@ export const secretBlindIndexServiceFactory = ({
   };
 
   const getProjectSecrets = async ({ projectId, actorId, actor }: TGetProjectSecretsDTO) => {
-    const { membership } = await permissionService.getProjectPermission(actor, actorId, projectId);
-    if (membership?.role !== ProjectMembershipRole.Admin) {
+    const { hasRole } = await permissionService.getProjectPermission(actor, actorId, projectId);
+    if (!hasRole(ProjectMembershipRole.Admin)) {
       throw new UnauthorizedError({ message: "User must be admin" });
     }
 
@@ -53,8 +53,8 @@ export const secretBlindIndexServiceFactory = ({
     actorOrgId,
     secretsToUpdate
   }: TUpdateProjectSecretNameDTO) => {
-    const { membership } = await permissionService.getProjectPermission(actor, actorId, projectId, actorOrgId);
-    if (membership?.role !== ProjectMembershipRole.Admin) {
+    const { hasRole } = await permissionService.getProjectPermission(actor, actorId, projectId, actorOrgId);
+    if (!hasRole(ProjectMembershipRole.Admin)) {
       throw new UnauthorizedError({ message: "User must be admin" });
     }
 

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

@@ -1,6 +1,6 @@
 import { ForbiddenError, subject } from "@casl/ability";
 import path from "path";
-import { v4 as uuidv4 } from "uuid";
+import { v4 as uuidv4, validate as uuidValidate } from "uuid";
 
 import { TSecretFoldersInsert } from "@app/db/schemas";
 import { TPermissionServiceFactory } from "@app/ee/services/permission/permission-service";
@@ -164,7 +164,7 @@ export const secretFolderServiceFactory = ({
     actorOrgId,
     environment,
     path: secretPath,
-    id
+    idOrName
   }: TDeleteFolderDTO) => {
     const { permission } = await permissionService.getProjectPermission(actor, actorId, projectId, actorOrgId);
     ForbiddenError.from(permission).throwUnlessCan(
@@ -179,7 +179,10 @@ export const secretFolderServiceFactory = ({
       const parentFolder = await folderDAL.findBySecretPath(projectId, environment, secretPath, tx);
       if (!parentFolder) throw new BadRequestError({ message: "Secret path not found" });
 
-      const [doc] = await folderDAL.delete({ envId: env.id, id, parentId: parentFolder.id }, tx);
+      const [doc] = await folderDAL.delete(
+        { envId: env.id, [uuidValidate(idOrName) ? "id" : "name"]: idOrName, parentId: parentFolder.id },
+        tx
+      );
       if (!doc) throw new BadRequestError({ message: "Folder not found", name: "Delete folder" });
       return doc;
     });

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

@@ -16,7 +16,7 @@ export type TUpdateFolderDTO = {
 export type TDeleteFolderDTO = {
   environment: string;
   path: string;
-  id: string;
+  idOrName: string;
 } & TProjectPermission;
 
 export type TGetFolderDTO = {

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

@@ -1,12 +1,35 @@
 /* eslint-disable no-await-in-loop */
 import path from "path";
 
-import { SecretKeyEncoding, TSecretBlindIndexes, TSecrets } from "@app/db/schemas";
+import {
+  SecretEncryptionAlgo,
+  SecretKeyEncoding,
+  SecretType,
+  TableName,
+  TSecretBlindIndexes,
+  TSecrets
+} from "@app/db/schemas";
 import { getConfig } from "@app/lib/config/env";
-import { buildSecretBlindIndexFromName, decryptSymmetric128BitHexKeyUTF8 } from "@app/lib/crypto";
+import {
+  buildSecretBlindIndexFromName,
+  decryptSymmetric128BitHexKeyUTF8,
+  encryptSymmetric128BitHexKeyUTF8
+} from "@app/lib/crypto";
+import { BadRequestError } from "@app/lib/errors";
+import { groupBy, unique } from "@app/lib/fn";
 
+import { getBotKeyFnFactory } from "../project-bot/project-bot-fns";
 import { TSecretFolderDALFactory } from "../secret-folder/secret-folder-dal";
 import { TSecretDALFactory } from "./secret-dal";
+import {
+  TCreateManySecretsRawFn,
+  TCreateManySecretsRawFnFactory,
+  TFnSecretBlindIndexCheck,
+  TFnSecretBulkInsert,
+  TFnSecretBulkUpdate,
+  TUpdateManySecretsRawFn,
+  TUpdateManySecretsRawFnFactory
+} from "./secret-types";
 
 export const generateSecretBlindIndexBySalt = async (secretName: string, secretBlindIndexDoc: TSecretBlindIndexes) => {
   const appCfg = getConfig();
@@ -228,3 +251,399 @@ export const decryptSecretRaw = (secret: TSecrets & { workspace: string; environ
     user: secret.userId
   };
 };
+
+/**
+ * Checks and handles secrets using a blind index method.
+ * The function generates mappings between secret names and their blind indexes, validates user IDs for personal secrets, and retrieves secrets from the database based on their blind indexes.
+ * For new secrets (isNew = true), it ensures they don't already exist in the database.
+ * For existing secrets, it verifies their presence in the database.
+ * If discrepancies are found, errors are thrown. The function returns mappings and the fetched secrets.
+ */
+export const fnSecretBlindIndexCheck = async ({
+  inputSecrets,
+  folderId,
+  isNew,
+  userId,
+  blindIndexCfg,
+  secretDAL
+}: TFnSecretBlindIndexCheck) => {
+  const blindIndex2KeyName: Record<string, string> = {}; // used at audit log point
+  const keyName2BlindIndex = await Promise.all(
+    inputSecrets.map(({ secretName }) => generateSecretBlindIndexBySalt(secretName, blindIndexCfg))
+  ).then((blindIndexes) =>
+    blindIndexes.reduce<Record<string, string>>((prev, curr, i) => {
+      // eslint-disable-next-line
+      prev[inputSecrets[i].secretName] = curr;
+      blindIndex2KeyName[curr] = inputSecrets[i].secretName;
+      return prev;
+    }, {})
+  );
+
+  if (inputSecrets.some(({ type }) => type === SecretType.Personal) && !userId) {
+    throw new BadRequestError({ message: "Missing user id for personal secret" });
+  }
+
+  const secrets = await secretDAL.findByBlindIndexes(
+    folderId,
+    inputSecrets.map(({ secretName, type }) => ({
+      blindIndex: keyName2BlindIndex[secretName],
+      type: type || SecretType.Shared
+    })),
+    userId
+  );
+
+  if (isNew) {
+    if (secrets.length) throw new BadRequestError({ message: "Secret already exist" });
+  } else {
+    const secretKeysInDB = unique(secrets, (el) => el.secretBlindIndex as string).map(
+      (el) => blindIndex2KeyName[el.secretBlindIndex as string]
+    );
+    const hasUnknownSecretsProvided = secretKeysInDB.length !== inputSecrets.length;
+    if (hasUnknownSecretsProvided) {
+      const keysMissingInDB = Object.keys(keyName2BlindIndex).filter((key) => !secretKeysInDB.includes(key));
+      throw new BadRequestError({
+        message: `Secret not found: blind index ${keysMissingInDB.join(",")}`
+      });
+    }
+  }
+
+  return { blindIndex2KeyName, keyName2BlindIndex, secrets };
+};
+
+// these functions are special functions shared by a couple of resources
+// used by secret approval, rotation or anywhere in which secret needs to modified
+export const fnSecretBulkInsert = async ({
+  // TODO: Pick types here
+  folderId,
+  inputSecrets,
+  secretDAL,
+  secretVersionDAL,
+  secretTagDAL,
+  secretVersionTagDAL,
+  tx
+}: TFnSecretBulkInsert) => {
+  const newSecrets = await secretDAL.insertMany(
+    inputSecrets.map(({ tags, ...el }) => ({ ...el, folderId })),
+    tx
+  );
+  const newSecretGroupByBlindIndex = groupBy(newSecrets, (item) => item.secretBlindIndex as string);
+  const newSecretTags = inputSecrets.flatMap(({ tags: secretTags = [], secretBlindIndex }) =>
+    secretTags.map((tag) => ({
+      [`${TableName.SecretTag}Id` as const]: tag,
+      [`${TableName.Secret}Id` as const]: newSecretGroupByBlindIndex[secretBlindIndex as string][0].id
+    }))
+  );
+  const secretVersions = await secretVersionDAL.insertMany(
+    inputSecrets.map(({ tags, ...el }) => ({
+      ...el,
+      folderId,
+      secretId: newSecretGroupByBlindIndex[el.secretBlindIndex as string][0].id
+    })),
+    tx
+  );
+  if (newSecretTags.length) {
+    const secTags = await secretTagDAL.saveTagsToSecret(newSecretTags, tx);
+    const secVersionsGroupBySecId = groupBy(secretVersions, (i) => i.secretId);
+    const newSecretVersionTags = secTags.flatMap(({ secretsId, secret_tagsId }) => ({
+      [`${TableName.SecretVersion}Id` as const]: secVersionsGroupBySecId[secretsId][0].id,
+      [`${TableName.SecretTag}Id` as const]: secret_tagsId
+    }));
+    await secretVersionTagDAL.insertMany(newSecretVersionTags, tx);
+  }
+
+  return newSecrets.map((secret) => ({ ...secret, _id: secret.id }));
+};
+
+export const fnSecretBulkUpdate = async ({
+  tx,
+  inputSecrets,
+  folderId,
+  projectId,
+  secretDAL,
+  secretVersionDAL,
+  secretTagDAL,
+  secretVersionTagDAL
+}: TFnSecretBulkUpdate) => {
+  const newSecrets = await secretDAL.bulkUpdate(
+    inputSecrets.map(({ filter, data: { tags, ...data } }) => ({
+      filter: { ...filter, folderId },
+      data
+    })),
+    tx
+  );
+  const secretVersions = await secretVersionDAL.insertMany(
+    newSecrets.map(({ id, createdAt, updatedAt, ...el }) => ({
+      ...el,
+      secretId: id
+    })),
+    tx
+  );
+  const secsUpdatedTag = inputSecrets.flatMap(({ data: { tags } }, i) =>
+    tags !== undefined ? { tags, secretId: newSecrets[i].id } : []
+  );
+  if (secsUpdatedTag.length) {
+    await secretTagDAL.deleteTagsManySecret(
+      projectId,
+      secsUpdatedTag.map(({ secretId }) => secretId),
+      tx
+    );
+    const newSecretTags = secsUpdatedTag.flatMap(({ tags: secretTags = [], secretId }) =>
+      secretTags.map((tag) => ({
+        [`${TableName.SecretTag}Id` as const]: tag,
+        [`${TableName.Secret}Id` as const]: secretId
+      }))
+    );
+    if (newSecretTags.length) {
+      const secTags = await secretTagDAL.saveTagsToSecret(newSecretTags, tx);
+      const secVersionsGroupBySecId = groupBy(secretVersions, (i) => i.secretId);
+      const newSecretVersionTags = secTags.flatMap(({ secretsId, secret_tagsId }) => ({
+        [`${TableName.SecretVersion}Id` as const]: secVersionsGroupBySecId[secretsId][0].id,
+        [`${TableName.SecretTag}Id` as const]: secret_tagsId
+      }));
+      await secretVersionTagDAL.insertMany(newSecretVersionTags, tx);
+    }
+  }
+
+  return newSecrets.map((secret) => ({ ...secret, _id: secret.id }));
+};
+
+export const createManySecretsRawFnFactory = ({
+  projectDAL,
+  projectBotDAL,
+  secretDAL,
+  secretVersionDAL,
+  secretBlindIndexDAL,
+  secretTagDAL,
+  secretVersionTagDAL,
+  folderDAL
+}: TCreateManySecretsRawFnFactory) => {
+  const getBotKeyFn = getBotKeyFnFactory(projectBotDAL);
+  const createManySecretsRawFn = async ({
+    projectId,
+    environment,
+    path: secretPath,
+    secrets,
+    userId
+  }: TCreateManySecretsRawFn) => {
+    const botKey = await getBotKeyFn(projectId);
+    if (!botKey) throw new BadRequestError({ message: "Project bot not found", name: "bot_not_found_error" });
+
+    await projectDAL.checkProjectUpgradeStatus(projectId);
+
+    const folder = await folderDAL.findBySecretPath(projectId, environment, secretPath);
+    if (!folder) throw new BadRequestError({ message: "Folder not  found", name: "Create secret" });
+    const folderId = folder.id;
+
+    const blindIndexCfg = await secretBlindIndexDAL.findOne({ projectId });
+    if (!blindIndexCfg) throw new BadRequestError({ message: "Blind index not found", name: "Create secret" });
+
+    // insert operation
+    const { keyName2BlindIndex } = await fnSecretBlindIndexCheck({
+      inputSecrets: secrets,
+      folderId,
+      isNew: true,
+      blindIndexCfg,
+      secretDAL
+    });
+
+    const inputSecrets = await Promise.all(
+      secrets.map(async (secret) => {
+        const secretKeyEncrypted = encryptSymmetric128BitHexKeyUTF8(secret.secretName, botKey);
+        const secretValueEncrypted = encryptSymmetric128BitHexKeyUTF8(secret.secretValue || "", botKey);
+        const secretCommentEncrypted = encryptSymmetric128BitHexKeyUTF8(secret.secretComment || "", botKey);
+
+        if (secret.type === SecretType.Personal) {
+          if (!userId) throw new BadRequestError({ message: "Missing user id for personal secret" });
+          const sharedExist = await secretDAL.findOne({
+            secretBlindIndex: keyName2BlindIndex[secret.secretName],
+            folderId,
+            type: SecretType.Shared
+          });
+
+          if (!sharedExist)
+            throw new BadRequestError({
+              message: "Failed to create personal secret override for no corresponding shared secret"
+            });
+        }
+
+        const tags = secret.tags ? await secretTagDAL.findManyTagsById(projectId, secret.tags) : [];
+        if ((secret.tags || []).length !== tags.length) throw new BadRequestError({ message: "Tag not found" });
+
+        return {
+          type: secret.type,
+          userId: secret.type === SecretType.Personal ? userId : null,
+          secretName: secret.secretName,
+          secretKeyCiphertext: secretKeyEncrypted.ciphertext,
+          secretKeyIV: secretKeyEncrypted.iv,
+          secretKeyTag: secretKeyEncrypted.tag,
+          secretValueCiphertext: secretValueEncrypted.ciphertext,
+          secretValueIV: secretValueEncrypted.iv,
+          secretValueTag: secretValueEncrypted.tag,
+          secretCommentCiphertext: secretCommentEncrypted.ciphertext,
+          secretCommentIV: secretCommentEncrypted.iv,
+          secretCommentTag: secretCommentEncrypted.tag,
+          skipMultilineEncoding: secret.skipMultilineEncoding,
+          tags: secret.tags
+        };
+      })
+    );
+
+    const newSecrets = await secretDAL.transaction(async (tx) =>
+      fnSecretBulkInsert({
+        inputSecrets: inputSecrets.map(({ secretName, ...el }) => ({
+          ...el,
+          version: 0,
+          secretBlindIndex: keyName2BlindIndex[secretName],
+          algorithm: SecretEncryptionAlgo.AES_256_GCM,
+          keyEncoding: SecretKeyEncoding.UTF8
+        })),
+        folderId,
+        secretDAL,
+        secretVersionDAL,
+        secretTagDAL,
+        secretVersionTagDAL,
+        tx
+      })
+    );
+
+    return newSecrets;
+  };
+
+  return createManySecretsRawFn;
+};
+
+export const updateManySecretsRawFnFactory = ({
+  projectDAL,
+  projectBotDAL,
+  secretDAL,
+  secretVersionDAL,
+  secretBlindIndexDAL,
+  secretTagDAL,
+  secretVersionTagDAL,
+  folderDAL
+}: TUpdateManySecretsRawFnFactory) => {
+  const getBotKeyFn = getBotKeyFnFactory(projectBotDAL);
+  const updateManySecretsRawFn = async ({
+    projectId,
+    environment,
+    path: secretPath,
+    secrets, // consider accepting instead ciphertext secrets
+    userId
+  }: TUpdateManySecretsRawFn): Promise<Array<TSecrets & { _id: string }>> => {
+    const botKey = await getBotKeyFn(projectId);
+    if (!botKey) throw new BadRequestError({ message: "Project bot not found", name: "bot_not_found_error" });
+
+    await projectDAL.checkProjectUpgradeStatus(projectId);
+
+    const folder = await folderDAL.findBySecretPath(projectId, environment, secretPath);
+    if (!folder) throw new BadRequestError({ message: "Folder not  found", name: "Update secret" });
+    const folderId = folder.id;
+
+    const blindIndexCfg = await secretBlindIndexDAL.findOne({ projectId });
+    if (!blindIndexCfg) throw new BadRequestError({ message: "Blind index not found", name: "Update secret" });
+
+    const { keyName2BlindIndex } = await fnSecretBlindIndexCheck({
+      inputSecrets: secrets,
+      folderId,
+      isNew: false,
+      blindIndexCfg,
+      secretDAL,
+      userId
+    });
+
+    const inputSecrets = await Promise.all(
+      secrets.map(async (secret) => {
+        if (secret.newSecretName === "") {
+          throw new BadRequestError({ message: "New secret name cannot be empty" });
+        }
+
+        const secretKeyEncrypted = encryptSymmetric128BitHexKeyUTF8(secret.secretName, botKey);
+        const secretValueEncrypted = encryptSymmetric128BitHexKeyUTF8(secret.secretValue || "", botKey);
+        const secretCommentEncrypted = encryptSymmetric128BitHexKeyUTF8(secret.secretComment || "", botKey);
+
+        if (secret.type === SecretType.Personal) {
+          if (!userId) throw new BadRequestError({ message: "Missing user id for personal secret" });
+
+          const sharedExist = await secretDAL.findOne({
+            secretBlindIndex: keyName2BlindIndex[secret.secretName],
+            folderId,
+            type: SecretType.Shared
+          });
+
+          if (!sharedExist)
+            throw new BadRequestError({
+              message: "Failed to update personal secret override for no corresponding shared secret"
+            });
+
+          if (secret.newSecretName)
+            throw new BadRequestError({ message: "Personal secret cannot change the key name" });
+        }
+
+        const tags = secret.tags ? await secretTagDAL.findManyTagsById(projectId, secret.tags) : [];
+        if ((secret.tags || []).length !== tags.length) throw new BadRequestError({ message: "Tag not found" });
+
+        return {
+          type: secret.type,
+          userId: secret.type === SecretType.Personal ? userId : null,
+          secretName: secret.secretName,
+          newSecretName: secret.newSecretName,
+          secretKeyCiphertext: secretKeyEncrypted.ciphertext,
+          secretKeyIV: secretKeyEncrypted.iv,
+          secretKeyTag: secretKeyEncrypted.tag,
+          secretValueCiphertext: secretValueEncrypted.ciphertext,
+          secretValueIV: secretValueEncrypted.iv,
+          secretValueTag: secretValueEncrypted.tag,
+          secretCommentCiphertext: secretCommentEncrypted.ciphertext,
+          secretCommentIV: secretCommentEncrypted.iv,
+          secretCommentTag: secretCommentEncrypted.tag,
+          skipMultilineEncoding: secret.skipMultilineEncoding,
+          tags: secret.tags
+        };
+      })
+    );
+
+    const tagIds = inputSecrets.flatMap(({ tags = [] }) => tags);
+    const tags = tagIds.length ? await secretTagDAL.findManyTagsById(projectId, tagIds) : [];
+    if (tagIds.length !== tags.length) throw new BadRequestError({ message: "Tag not found" });
+
+    // now find any secret that needs to update its name
+    // same process as above
+    const nameUpdatedSecrets = inputSecrets.filter(({ newSecretName }) => Boolean(newSecretName));
+    const { keyName2BlindIndex: newKeyName2BlindIndex } = await fnSecretBlindIndexCheck({
+      inputSecrets: nameUpdatedSecrets,
+      folderId,
+      isNew: true,
+      blindIndexCfg,
+      secretDAL
+    });
+
+    const updatedSecrets = await secretDAL.transaction(async (tx) =>
+      fnSecretBulkUpdate({
+        folderId,
+        projectId,
+        tx,
+        inputSecrets: inputSecrets.map(({ secretName, newSecretName, ...el }) => ({
+          filter: { secretBlindIndex: keyName2BlindIndex[secretName], type: SecretType.Shared },
+          data: {
+            ...el,
+            folderId,
+            secretBlindIndex:
+              newSecretName && newKeyName2BlindIndex[newSecretName]
+                ? newKeyName2BlindIndex[newSecretName]
+                : keyName2BlindIndex[secretName],
+            algorithm: SecretEncryptionAlgo.AES_256_GCM,
+            keyEncoding: SecretKeyEncoding.UTF8
+          }
+        })),
+        secretDAL,
+        secretVersionDAL,
+        secretTagDAL,
+        secretVersionTagDAL
+      })
+    );
+
+    return updatedSecrets;
+  };
+
+  return updateManySecretsRawFn;
+};

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

@@ -6,6 +6,12 @@ import { BadRequestError } from "@app/lib/errors";
 import { isSamePath } from "@app/lib/fn";
 import { logger } from "@app/lib/logger";
 import { QueueJobs, QueueName, TQueueServiceFactory } from "@app/queue";
+import { TProjectBotDALFactory } from "@app/services/project-bot/project-bot-dal";
+import { createManySecretsRawFnFactory, updateManySecretsRawFnFactory } from "@app/services/secret/secret-fns";
+import { TSecretVersionDALFactory } from "@app/services/secret/secret-version-dal";
+import { TSecretVersionTagDALFactory } from "@app/services/secret/secret-version-tag-dal";
+import { TSecretBlindIndexDALFactory } from "@app/services/secret-blind-index/secret-blind-index-dal";
+import { TSecretTagDALFactory } from "@app/services/secret-tag/secret-tag-dal";
 
 import { TIntegrationDALFactory } from "../integration/integration-dal";
 import { TIntegrationAuthServiceFactory } from "../integration-auth/integration-auth-service";
@@ -29,18 +35,23 @@ export type TSecretQueueFactory = ReturnType<typeof secretQueueFactory>;
 
 type TSecretQueueFactoryDep = {
   queueService: TQueueServiceFactory;
-  integrationDAL: Pick<TIntegrationDALFactory, "findByProjectIdV2">;
+  integrationDAL: Pick<TIntegrationDALFactory, "findByProjectIdV2" | "updateById">;
   projectBotService: Pick<TProjectBotServiceFactory, "getBotKey">;
   integrationAuthService: Pick<TIntegrationAuthServiceFactory, "getIntegrationAccessToken">;
-  folderDAL: Pick<TSecretFolderDALFactory, "findBySecretPath" | "findByManySecretPath">;
-  secretDAL: Pick<TSecretDALFactory, "findByFolderId" | "find">;
+  folderDAL: TSecretFolderDALFactory;
+  secretDAL: TSecretDALFactory;
   secretImportDAL: Pick<TSecretImportDALFactory, "find">;
   webhookDAL: Pick<TWebhookDALFactory, "findAllWebhooks" | "transaction" | "update" | "bulkUpdate">;
   projectEnvDAL: Pick<TProjectEnvDALFactory, "findOne">;
-  projectDAL: Pick<TProjectDALFactory, "findById">;
+  projectDAL: TProjectDALFactory;
+  projectBotDAL: TProjectBotDALFactory;
   projectMembershipDAL: Pick<TProjectMembershipDALFactory, "findAllProjectMembers">;
   smtpService: TSmtpService;
   orgDAL: Pick<TOrgDALFactory, "findOrgByProjectId">;
+  secretVersionDAL: TSecretVersionDALFactory;
+  secretBlindIndexDAL: TSecretBlindIndexDALFactory;
+  secretTagDAL: TSecretTagDALFactory;
+  secretVersionTagDAL: TSecretVersionTagDALFactory;
 };
 
 export type TGetSecrets = {
@@ -62,8 +73,35 @@ export const secretQueueFactory = ({
   orgDAL,
   smtpService,
   projectDAL,
-  projectMembershipDAL
+  projectBotDAL,
+  projectMembershipDAL,
+  secretVersionDAL,
+  secretBlindIndexDAL,
+  secretTagDAL,
+  secretVersionTagDAL
 }: TSecretQueueFactoryDep) => {
+  const createManySecretsRawFn = createManySecretsRawFnFactory({
+    projectDAL,
+    projectBotDAL,
+    secretDAL,
+    secretVersionDAL,
+    secretBlindIndexDAL,
+    secretTagDAL,
+    secretVersionTagDAL,
+    folderDAL
+  });
+
+  const updateManySecretsRawFn = updateManySecretsRawFnFactory({
+    projectDAL,
+    projectBotDAL,
+    secretDAL,
+    secretVersionDAL,
+    secretBlindIndexDAL,
+    secretTagDAL,
+    secretVersionTagDAL,
+    folderDAL
+  });
+
   const syncIntegrations = async (dto: TGetSecrets) => {
     await queueService.queue(QueueName.IntegrationSync, QueueJobs.IntegrationSync, dto, {
       attempts: 5,
@@ -307,6 +345,9 @@ export const secretQueueFactory = ({
       }
 
       await syncIntegrationSecrets({
+        createManySecretsRawFn,
+        updateManySecretsRawFn,
+        integrationDAL,
         integration,
         integrationAuth,
         secrets: Object.keys(suffixedSecrets).length !== 0 ? suffixedSecrets : secrets,
@@ -350,7 +391,7 @@ export const secretQueueFactory = ({
     await smtpService.sendMail({
       template: SmtpTemplates.SecretReminder,
       subjectLine: "Infisical secret reminder",
-      recipients: [...projectMembers.map((m) => m.user.email)],
+      recipients: [...projectMembers.map((m) => m.user.email)].filter((email) => email).map((email) => email as string),
       substitutions: {
         reminderNote: data.note, // May not be present.
         projectName: project.name,

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

@@ -1,13 +1,13 @@
 import { ForbiddenError, subject } from "@casl/ability";
 
-import { SecretEncryptionAlgo, SecretKeyEncoding, SecretsSchema, SecretType, TableName } from "@app/db/schemas";
+import { SecretEncryptionAlgo, SecretKeyEncoding, SecretsSchema, SecretType } from "@app/db/schemas";
 import { TPermissionServiceFactory } from "@app/ee/services/permission/permission-service";
 import { ProjectPermissionActions, ProjectPermissionSub } from "@app/ee/services/permission/project-permission";
 import { TSecretSnapshotServiceFactory } from "@app/ee/services/secret-snapshot/secret-snapshot-service";
 import { getConfig } from "@app/lib/config/env";
 import { buildSecretBlindIndexFromName, encryptSymmetric128BitHexKeyUTF8 } from "@app/lib/crypto";
 import { BadRequestError } from "@app/lib/errors";
-import { groupBy, pick, unique } from "@app/lib/fn";
+import { groupBy, pick } from "@app/lib/fn";
 import { logger } from "@app/lib/logger";
 
 import { ActorType } from "../auth/auth-type";
@@ -19,7 +19,7 @@ import { TSecretImportDALFactory } from "../secret-import/secret-import-dal";
 import { fnSecretsFromImports } from "../secret-import/secret-import-fns";
 import { TSecretTagDALFactory } from "../secret-tag/secret-tag-dal";
 import { TSecretDALFactory } from "./secret-dal";
-import { decryptSecretRaw, generateSecretBlindIndexBySalt } from "./secret-fns";
+import { decryptSecretRaw, fnSecretBlindIndexCheck, fnSecretBulkInsert, fnSecretBulkUpdate } from "./secret-fns";
 import { TSecretQueueFactory } from "./secret-queue";
 import {
   TCreateBulkSecretDTO,
@@ -28,11 +28,8 @@ import {
   TDeleteBulkSecretDTO,
   TDeleteSecretDTO,
   TDeleteSecretRawDTO,
-  TFnSecretBlindIndexCheck,
   TFnSecretBlindIndexCheckV2,
   TFnSecretBulkDelete,
-  TFnSecretBulkInsert,
-  TFnSecretBulkUpdate,
   TGetASecretDTO,
   TGetASecretRawDTO,
   TGetSecretsDTO,
@@ -95,85 +92,6 @@ export const secretServiceFactory = ({
     return secretBlindIndex;
   };
 
-  // these functions are special functions shared by a couple of resources
-  // used by secret approval, rotation or anywhere in which secret needs to modified
-  const fnSecretBulkInsert = async ({ folderId, inputSecrets, tx }: TFnSecretBulkInsert) => {
-    const newSecrets = await secretDAL.insertMany(
-      inputSecrets.map(({ tags, ...el }) => ({ ...el, folderId })),
-      tx
-    );
-    const newSecretGroupByBlindIndex = groupBy(newSecrets, (item) => item.secretBlindIndex as string);
-    const newSecretTags = inputSecrets.flatMap(({ tags: secretTags = [], secretBlindIndex }) =>
-      secretTags.map((tag) => ({
-        [`${TableName.SecretTag}Id` as const]: tag,
-        [`${TableName.Secret}Id` as const]: newSecretGroupByBlindIndex[secretBlindIndex as string][0].id
-      }))
-    );
-    const secretVersions = await secretVersionDAL.insertMany(
-      inputSecrets.map(({ tags, ...el }) => ({
-        ...el,
-        folderId,
-        secretId: newSecretGroupByBlindIndex[el.secretBlindIndex as string][0].id
-      })),
-      tx
-    );
-    if (newSecretTags.length) {
-      const secTags = await secretTagDAL.saveTagsToSecret(newSecretTags, tx);
-      const secVersionsGroupBySecId = groupBy(secretVersions, (i) => i.secretId);
-      const newSecretVersionTags = secTags.flatMap(({ secretsId, secret_tagsId }) => ({
-        [`${TableName.SecretVersion}Id` as const]: secVersionsGroupBySecId[secretsId][0].id,
-        [`${TableName.SecretTag}Id` as const]: secret_tagsId
-      }));
-      await secretVersionTagDAL.insertMany(newSecretVersionTags, tx);
-    }
-
-    return newSecrets.map((secret) => ({ ...secret, _id: secret.id }));
-  };
-
-  const fnSecretBulkUpdate = async ({ tx, inputSecrets, folderId, projectId }: TFnSecretBulkUpdate) => {
-    const newSecrets = await secretDAL.bulkUpdate(
-      inputSecrets.map(({ filter, data: { tags, ...data } }) => ({
-        filter: { ...filter, folderId },
-        data
-      })),
-      tx
-    );
-    const secretVersions = await secretVersionDAL.insertMany(
-      newSecrets.map(({ id, createdAt, updatedAt, ...el }) => ({
-        ...el,
-        secretId: id
-      })),
-      tx
-    );
-    const secsUpdatedTag = inputSecrets.flatMap(({ data: { tags } }, i) =>
-      tags !== undefined ? { tags, secretId: newSecrets[i].id } : []
-    );
-    if (secsUpdatedTag.length) {
-      await secretTagDAL.deleteTagsManySecret(
-        projectId,
-        secsUpdatedTag.map(({ secretId }) => secretId),
-        tx
-      );
-      const newSecretTags = secsUpdatedTag.flatMap(({ tags: secretTags = [], secretId }) =>
-        secretTags.map((tag) => ({
-          [`${TableName.SecretTag}Id` as const]: tag,
-          [`${TableName.Secret}Id` as const]: secretId
-        }))
-      );
-      if (newSecretTags.length) {
-        const secTags = await secretTagDAL.saveTagsToSecret(newSecretTags, tx);
-        const secVersionsGroupBySecId = groupBy(secretVersions, (i) => i.secretId);
-        const newSecretVersionTags = secTags.flatMap(({ secretsId, secret_tagsId }) => ({
-          [`${TableName.SecretVersion}Id` as const]: secVersionsGroupBySecId[secretsId][0].id,
-          [`${TableName.SecretTag}Id` as const]: secret_tagsId
-        }));
-        await secretVersionTagDAL.insertMany(newSecretVersionTags, tx);
-      }
-    }
-
-    return newSecrets.map((secret) => ({ ...secret, _id: secret.id }));
-  };
-
   const fnSecretBulkDelete = async ({ folderId, inputSecrets, tx, actorId }: TFnSecretBulkDelete) => {
     const deletedSecrets = await secretDAL.deleteMany(
       inputSecrets.map(({ type, secretBlindIndex }) => ({
@@ -202,63 +120,6 @@ export const secretServiceFactory = ({
     return deletedSecrets;
   };
 
-  /**
-   * Checks and handles secrets using a blind index method.
-   * The function generates mappings between secret names and their blind indexes, validates user IDs for personal secrets, and retrieves secrets from the database based on their blind indexes.
-   * For new secrets (isNew = true), it ensures they don't already exist in the database.
-   * For existing secrets, it verifies their presence in the database.
-   * If discrepancies are found, errors are thrown. The function returns mappings and the fetched secrets.
-   */
-  const fnSecretBlindIndexCheck = async ({
-    inputSecrets,
-    folderId,
-    isNew,
-    userId,
-    blindIndexCfg
-  }: TFnSecretBlindIndexCheck) => {
-    const blindIndex2KeyName: Record<string, string> = {}; // used at audit log point
-    const keyName2BlindIndex = await Promise.all(
-      inputSecrets.map(({ secretName }) => generateSecretBlindIndexBySalt(secretName, blindIndexCfg))
-    ).then((blindIndexes) =>
-      blindIndexes.reduce<Record<string, string>>((prev, curr, i) => {
-        // eslint-disable-next-line
-        prev[inputSecrets[i].secretName] = curr;
-        blindIndex2KeyName[curr] = inputSecrets[i].secretName;
-        return prev;
-      }, {})
-    );
-
-    if (inputSecrets.some(({ type }) => type === SecretType.Personal) && !userId) {
-      throw new BadRequestError({ message: "Missing user id for personal secret" });
-    }
-
-    const secrets = await secretDAL.findByBlindIndexes(
-      folderId,
-      inputSecrets.map(({ secretName, type }) => ({
-        blindIndex: keyName2BlindIndex[secretName],
-        type: type || SecretType.Shared
-      })),
-      userId
-    );
-
-    if (isNew) {
-      if (secrets.length) throw new BadRequestError({ message: "Secret already exist" });
-    } else {
-      const secretKeysInDB = unique(secrets, (el) => el.secretBlindIndex as string).map(
-        (el) => blindIndex2KeyName[el.secretBlindIndex as string]
-      );
-      const hasUnknownSecretsProvided = secretKeysInDB.length !== inputSecrets.length;
-      if (hasUnknownSecretsProvided) {
-        const keysMissingInDB = Object.keys(keyName2BlindIndex).filter((key) => !secretKeysInDB.includes(key));
-        throw new BadRequestError({
-          message: `Secret not found: blind index ${keysMissingInDB.join(",")}`
-        });
-      }
-    }
-
-    return { blindIndex2KeyName, keyName2BlindIndex, secrets };
-  };
-
   // this is used when secret blind index already exist
   // mainly for secret approval
   const fnSecretBlindIndexCheckV2 = async ({ inputSecrets, folderId, userId }: TFnSecretBlindIndexCheckV2) => {
@@ -311,7 +172,8 @@ export const secretServiceFactory = ({
       folderId,
       isNew: true,
       userId: actorId,
-      blindIndexCfg
+      blindIndexCfg,
+      secretDAL
     });
 
     // if user creating personal check its shared also exist
@@ -348,6 +210,10 @@ export const secretServiceFactory = ({
             tags: inputSecret.tags
           }
         ],
+        secretDAL,
+        secretVersionDAL,
+        secretTagDAL,
+        secretVersionTagDAL,
         tx
       })
     );
@@ -395,7 +261,8 @@ export const secretServiceFactory = ({
       folderId,
       isNew: false,
       blindIndexCfg,
-      userId: actorId
+      userId: actorId,
+      secretDAL
     });
     if (inputSecret.newSecretName && inputSecret.type === SecretType.Personal) {
       throw new BadRequestError({ message: "Personal secret cannot change the key name" });
@@ -407,7 +274,8 @@ export const secretServiceFactory = ({
         inputSecrets: [{ secretName: inputSecret.newSecretName }],
         folderId,
         isNew: true,
-        blindIndexCfg
+        blindIndexCfg,
+        secretDAL
       });
       newSecretNameBlindIndex = kN2NewBlindIndex[inputSecret.newSecretName];
     }
@@ -454,6 +322,10 @@ export const secretServiceFactory = ({
             }
           }
         ],
+        secretDAL,
+        secretVersionDAL,
+        secretTagDAL,
+        secretVersionTagDAL,
         tx
       })
     );
@@ -496,7 +368,8 @@ export const secretServiceFactory = ({
       inputSecrets: [{ secretName: inputSecret.secretName }],
       folderId,
       isNew: false,
-      blindIndexCfg
+      blindIndexCfg,
+      secretDAL
     });
 
     const deletedSecret = await secretDAL.transaction(async (tx) =>
@@ -679,13 +552,14 @@ export const secretServiceFactory = ({
     const folderId = folder.id;
 
     const blindIndexCfg = await secretBlindIndexDAL.findOne({ projectId });
-    if (!blindIndexCfg) throw new BadRequestError({ message: "Blind index not found", name: "Update secret" });
+    if (!blindIndexCfg) throw new BadRequestError({ message: "Blind index not found", name: "Create secret" });
 
     const { keyName2BlindIndex } = await fnSecretBlindIndexCheck({
       inputSecrets,
       folderId,
       isNew: true,
-      blindIndexCfg
+      blindIndexCfg,
+      secretDAL
     });
 
     // get all tags
@@ -704,6 +578,10 @@ export const secretServiceFactory = ({
           keyEncoding: SecretKeyEncoding.UTF8
         })),
         folderId,
+        secretDAL,
+        secretVersionDAL,
+        secretTagDAL,
+        secretVersionTagDAL,
         tx
       })
     );
@@ -732,7 +610,7 @@ export const secretServiceFactory = ({
     await projectDAL.checkProjectUpgradeStatus(projectId);
 
     const folder = await folderDAL.findBySecretPath(projectId, environment, path);
-    if (!folder) throw new BadRequestError({ message: "Folder not  found", name: "Create secret" });
+    if (!folder) throw new BadRequestError({ message: "Folder not  found", name: "Update secret" });
     const folderId = folder.id;
 
     const blindIndexCfg = await secretBlindIndexDAL.findOne({ projectId });
@@ -742,7 +620,8 @@ export const secretServiceFactory = ({
       inputSecrets,
       folderId,
       isNew: false,
-      blindIndexCfg
+      blindIndexCfg,
+      secretDAL
     });
 
     // now find any secret that needs to update its name
@@ -752,7 +631,8 @@ export const secretServiceFactory = ({
       inputSecrets: nameUpdatedSecrets,
       folderId,
       isNew: true,
-      blindIndexCfg
+      blindIndexCfg,
+      secretDAL
     });
 
     // get all tags
@@ -777,7 +657,11 @@ export const secretServiceFactory = ({
             algorithm: SecretEncryptionAlgo.AES_256_GCM,
             keyEncoding: SecretKeyEncoding.UTF8
           }
-        }))
+        })),
+        secretDAL,
+        secretVersionDAL,
+        secretTagDAL,
+        secretVersionTagDAL
       })
     );
 
@@ -815,7 +699,8 @@ export const secretServiceFactory = ({
       inputSecrets,
       folderId,
       isNew: false,
-      blindIndexCfg
+      blindIndexCfg,
+      secretDAL
     });
 
     const secretsDeleted = await secretDAL.transaction(async (tx) =>

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

@@ -2,6 +2,14 @@ import { Knex } from "knex";
 
 import { SecretType, TSecretBlindIndexes, TSecrets, TSecretsInsert, TSecretsUpdate } from "@app/db/schemas";
 import { TProjectPermission } from "@app/lib/types";
+import { TProjectDALFactory } from "@app/services/project/project-dal";
+import { TProjectBotDALFactory } from "@app/services/project-bot/project-bot-dal";
+import { TSecretDALFactory } from "@app/services/secret/secret-dal";
+import { TSecretVersionDALFactory } from "@app/services/secret/secret-version-dal";
+import { TSecretVersionTagDALFactory } from "@app/services/secret/secret-version-tag-dal";
+import { TSecretBlindIndexDALFactory } from "@app/services/secret-blind-index/secret-blind-index-dal";
+import { TSecretFolderDALFactory } from "@app/services/secret-folder/secret-folder-dal";
+import { TSecretTagDALFactory } from "@app/services/secret-tag/secret-tag-dal";
 
 type TPartialSecret = Pick<TSecrets, "id" | "secretReminderRepeatDays" | "secretReminderNote">;
 
@@ -181,12 +189,20 @@ export type TFnSecretBulkInsert = {
   folderId: string;
   tx?: Knex;
   inputSecrets: Array<Omit<TSecretsInsert, "folderId"> & { tags?: string[] }>;
+  secretDAL: Pick<TSecretDALFactory, "insertMany">;
+  secretVersionDAL: Pick<TSecretVersionDALFactory, "insertMany">;
+  secretTagDAL: Pick<TSecretTagDALFactory, "saveTagsToSecret">;
+  secretVersionTagDAL: Pick<TSecretVersionTagDALFactory, "insertMany">;
 };
 
 export type TFnSecretBulkUpdate = {
   folderId: string;
   projectId: string;
   inputSecrets: { filter: Partial<TSecrets>; data: TSecretsUpdate & { tags?: string[] } }[];
+  secretDAL: Pick<TSecretDALFactory, "bulkUpdate">;
+  secretVersionDAL: Pick<TSecretVersionDALFactory, "insertMany">;
+  secretTagDAL: Pick<TSecretTagDALFactory, "saveTagsToSecret" | "deleteTagsManySecret">;
+  secretVersionTagDAL: Pick<TSecretVersionTagDALFactory, "insertMany">;
   tx?: Knex;
 };
 
@@ -204,6 +220,7 @@ export type TFnSecretBlindIndexCheck = {
   blindIndexCfg: TSecretBlindIndexes;
   inputSecrets: Array<{ secretName: string; type?: SecretType }>;
   isNew: boolean;
+  secretDAL: Pick<TSecretDALFactory, "findByBlindIndexes">;
 };
 
 // when blind index is already present
@@ -229,3 +246,66 @@ export type TRemoveSecretReminderDTO = {
   secretId: string;
   repeatDays: number;
 };
+
+// ---
+
+export type TCreateManySecretsRawFnFactory = {
+  projectDAL: TProjectDALFactory;
+  projectBotDAL: TProjectBotDALFactory;
+  secretDAL: TSecretDALFactory;
+  secretVersionDAL: TSecretVersionDALFactory;
+  secretBlindIndexDAL: TSecretBlindIndexDALFactory;
+  secretTagDAL: TSecretTagDALFactory;
+  secretVersionTagDAL: TSecretVersionTagDALFactory;
+  folderDAL: TSecretFolderDALFactory;
+};
+
+export type TCreateManySecretsRawFn = {
+  projectId: string;
+  environment: string;
+  path: string;
+  secrets: {
+    secretName: string;
+    secretValue: string;
+    type: SecretType;
+    secretComment?: string;
+    skipMultilineEncoding?: boolean;
+    tags?: string[];
+    metadata?: {
+      source?: string;
+    };
+  }[];
+  userId?: string; // only relevant for personal secret(s)
+};
+
+export type TUpdateManySecretsRawFnFactory = {
+  projectDAL: TProjectDALFactory;
+  projectBotDAL: TProjectBotDALFactory;
+  secretDAL: TSecretDALFactory;
+  secretVersionDAL: TSecretVersionDALFactory;
+  secretBlindIndexDAL: TSecretBlindIndexDALFactory;
+  secretTagDAL: TSecretTagDALFactory;
+  secretVersionTagDAL: TSecretVersionTagDALFactory;
+  folderDAL: TSecretFolderDALFactory;
+};
+
+export type TUpdateManySecretsRawFn = {
+  projectId: string;
+  environment: string;
+  path: string;
+  secrets: {
+    secretName: string;
+    newSecretName?: string;
+    secretValue: string;
+    type: SecretType;
+    secretComment?: string;
+    skipMultilineEncoding?: boolean;
+    secretReminderRepeatDays?: number | null;
+    secretReminderNote?: string | null;
+    tags?: string[];
+    metadata?: {
+      source?: string;
+    };
+  }[];
+  userId?: string;
+};

backend/src/services/smtp/templates/organizationInvitation.handlebars

@@ -8,7 +8,7 @@
 </head>
 <body>
     <h2>Join your organization on Infisical</h2>
-    <p>{{inviterFirstName}} ({{inviterEmail}}) has invited you to their Infisical organization — {{organizationName}}</p>
+    <p>{{inviterFirstName}} ({{inviterUsername}}) has invited you to their Infisical organization — {{organizationName}}</p>
     <a href="{{callback_url}}?token={{token}}&to={{email}}&organization_id={{organizationId}}">Join now</a>
     <h3>What is Infisical?</h3>
     <p>Infisical is an easy-to-use end-to-end encrypted tool that enables developers to sync and manage their secrets and configs.</p>

backend/src/services/super-admin/super-admin-service.ts

@@ -97,6 +97,7 @@ export const superAdminServiceFactory = ({
         {
           firstName,
           lastName,
+          username: email,
           email,
           superAdmin: true,
           isGhost: false,
@@ -126,11 +127,11 @@ export const superAdminServiceFactory = ({
 
     const initialOrganizationName = appCfg.INITIAL_ORGANIZATION_NAME ?? "Admin Org";
 
-    const organization = await orgService.createOrganization(
-      userInfo.user.id,
-      userInfo.user.email,
-      initialOrganizationName
-    );
+    const organization = await orgService.createOrganization({
+      userId: userInfo.user.id,
+      userEmail: userInfo.user.email,
+      orgName: initialOrganizationName
+    });
 
     await updateServerCfg({ initialized: true });
     const token = await authService.generateUserTokens({

backend/src/services/telemetry/telemetry-types.ts

@@ -37,6 +37,7 @@ export type TSecretModifiedEvent = {
 export type TAdminInitEvent = {
   event: PostHogEventTypes.AdminInit;
   properties: {
+    username: string;
     email: string;
     firstName: string;
     lastName: string;
@@ -46,6 +47,7 @@ export type TAdminInitEvent = {
 export type TUserSignedUpEvent = {
   event: PostHogEventTypes.UserSignedUp;
   properties: {
+    username: string;
     email: string;
     attributionSource?: string;
   };

backend/src/services/user-alias/user-alias-dal.ts

@@ -0,0 +1,13 @@
+import { TDbClient } from "@app/db";
+import { TableName } from "@app/db/schemas";
+import { ormify } from "@app/lib/knex";
+
+export type TUserAliasDALFactory = ReturnType<typeof userAliasDALFactory>;
+
+export const userAliasDALFactory = (db: TDbClient) => {
+  const userAliasOrm = ormify(db, TableName.UserAliases);
+
+  return {
+    ...userAliasOrm
+  };
+};