Infisical is the open-source platform for securing secrets, certificates, access, and AI Agents.
TypeScript acmeagentsai-agentscertificate-managementdevsecopsenvironment-variableskubernetesopen-sourcepampkipostgresprivate-caprivileged-access-managementsecret-managementsecret-managersecretssecrets-managementsecuritysecurity-toolsvault
import { Knex } from "knex";
import { TDbClient } from "@app/db";
import {
AccessScope,
OrganizationsSchema,
OrgMembershipRole,
OrgMembershipStatus,
ProjectType,
ProjectVersion,
RESOURCE_SCOPE,
TableName,
TMemberships,
TMembershipsInsert,
TMembershipsUpdate,
TOrganizations,
TOrganizationsInsert,
TUserEncryptionKeys
} from "@app/db/schemas";
import { PgSqlLock } from "@app/keystore/keystore";
import { DatabaseError } from "@app/lib/errors";
import { groupBy, sanitizeSqlLikeString, unique } from "@app/lib/fn";
import {
buildFindFilter,
ormify,
selectAllTableCols,
sqlNestRelationships,
TFindFilter,
TFindOpt,
withTransaction
} from "@app/lib/knex";
import { applyScimComparison, generateKnexQueryFromScim, TScimAttributeResolver } from "@app/lib/knex/scim";
import { OrderByDirection } from "@app/lib/types";
import { ActorType } from "../auth/auth-type";
import { OrgAuthMethod, TOrgWithSubOrgs } from "./org-types";
export type TOrgDALFactory = ReturnType<typeof orgDALFactory>;
interface CountResult {
count: string;
}
const SCIM_USER_ALIAS = "scim_user_alias";
type TOrgAccessActors = {
userIds: Set<string>;
identityIds: Set<string>;
};
type TOrphanedProjectActor = {
actorUserId: string | null;
actorIdentityId: string | null;
scopeOrgId: string;
};
export const ORG_ACCESS_LOCK_ESCALATION_THRESHOLD = 500;
export const orgDALFactory = (db: TDbClient) => {
const orgOrm = ormify(db, TableName.Organization);
const findOrganizationsByFilter = async ({
limit,
offset,
searchTerm,
sortBy
}: {
limit: number;
offset: number;
searchTerm: string;
sortBy?: keyof TOrganizations;
}) => {
try {
// Build the subquery for limited organization IDs
const orgSubquery = db.replicaNode().select("id").from(TableName.Organization);
if (searchTerm) {
void orgSubquery.where((qb) => {
void qb.whereILike(`${TableName.Organization}.name`, `%${sanitizeSqlLikeString(searchTerm)}%`);
});
}
const countQuery = orgSubquery.clone();
if (sortBy) {
void orgSubquery.orderBy(sortBy);
}
void orgSubquery.limit(limit).offset(offset);
const buildBaseQuery = (orgIdSubquery: Knex.QueryBuilder) => {
return db
.replicaNode()(TableName.Organization)
.whereNull(`${TableName.Organization}.rootOrgId`)
.whereIn(`${TableName.Organization}.id`, orgIdSubquery)
.leftJoin(TableName.Project, `${TableName.Organization}.id`, `${TableName.Project}.orgId`)
.leftJoin(TableName.Membership, `${TableName.Organization}.id`, `${TableName.Membership}.scopeOrgId`)
.where(`${TableName.Membership}.scope`, AccessScope.Organization)
.whereNotNull(`${TableName.Membership}.actorUserId`)
.leftJoin(TableName.Users, `${TableName.Membership}.actorUserId`, `${TableName.Users}.id`)
.leftJoin(TableName.MembershipRole, `${TableName.Membership}.id`, `${TableName.MembershipRole}.membershipId`)
.leftJoin(TableName.Role, `${TableName.MembershipRole}.customRoleId`, `${TableName.Role}.id`)
.where((qb) => {
void qb.where(`${TableName.Users}.isGhost`, false).orWhereNull(`${TableName.Users}.id`);
});
};
const [docs, totalResult] = await Promise.all([
buildBaseQuery(orgSubquery)
.select(selectAllTableCols(TableName.Organization))
.select(db.ref("name").withSchema(TableName.Project).as("projectName"))
.select(db.ref("id").withSchema(TableName.Project).as("projectId"))
.select(db.ref("slug").withSchema(TableName.Project).as("projectSlug"))
.select(db.ref("createdAt").withSchema(TableName.Project).as("projectCreatedAt"))
.select(db.ref("email").withSchema(TableName.Users).as("userEmail"))
.select(db.ref("username").withSchema(TableName.Users).as("username"))
.select(db.ref("firstName").withSchema(TableName.Users).as("firstName"))
.select(db.ref("lastName").withSchema(TableName.Users).as("lastName"))
.select(db.ref("id").withSchema(TableName.Users).as("userId"))
.select(db.ref("id").withSchema(TableName.Membership).as("orgMembershipId"))
.select(db.ref("status").withSchema(TableName.Membership).as("orgMembershipStatus"))
.select(db.ref("role").withSchema(TableName.MembershipRole).as("orgMembershipRole"))
.select(db.ref("customRoleId").withSchema(TableName.MembershipRole).as("orgMembershipRoleId"))
.select(db.ref("name").withSchema(TableName.Role).as("orgMembershipRoleName")),
buildBaseQuery(countQuery).countDistinct(`${TableName.Organization}.id`, { as: "count" }).first()
]);
const formattedDocs = sqlNestRelationships({
data: docs,
key: "id",
parentMapper: (data) => OrganizationsSchema.parse(data),
childrenMapper: [
{
key: "projectId",
label: "projects" as const,
mapper: ({ projectId, projectName, projectSlug, projectCreatedAt }) => ({
id: projectId,
name: projectName,
slug: projectSlug,
createdAt: projectCreatedAt
})
},
{
key: "userId",
label: "members" as const,
mapper: ({
userId,
userEmail,
username,
firstName,
lastName,
orgMembershipId,
orgMembershipRole,
orgMembershipRoleName,
orgMembershipRoleId,
orgMembershipStatus
}) => ({
user: {
id: userId,
email: userEmail,
username,
firstName,
lastName
},
status: orgMembershipStatus,
membershipId: orgMembershipId,
role: orgMembershipRoleName || orgMembershipRole, // custom role name or pre-defined role name
roleId: orgMembershipRoleId
})
}
]
});
const total = Number(totalResult?.count || 0);
return {
organizations: formattedDocs,
total
};
} catch (error) {
throw new DatabaseError({ error, name: "Find organizations by filter" });
}
};
const listSubOrganizations = async (dto: {
actorId: string;
actorType: ActorType;
orgId: string;
isAccessible?: boolean;
search?: string;
orderBy?: string;
orderDirection?: OrderByDirection;
limit?: number;
offset?: number;
}) => {
try {
const conn = db.replicaNode();
const orderBy = dto.orderBy ?? "name";
const orderDirection = dto.orderDirection ?? "asc";
const buildBaseQuery = () => {
if (dto.isAccessible) {
const subOrgIdsSubquery = conn(TableName.Organization)
.where(`${TableName.Organization}.rootOrgId`, dto.orgId)
.select(db.ref("id").withSchema(TableName.Organization));
const userGroupIdsSubquery = conn(TableName.Groups)
.join(TableName.UserGroupMembership, `${TableName.UserGroupMembership}.groupId`, `${TableName.Groups}.id`)
.where(`${TableName.UserGroupMembership}.userId`, dto.actorId)
.select(db.ref("id").withSchema(TableName.Groups));
const identityGroupIdsSubquery = conn(TableName.Groups)
.join(
TableName.IdentityGroupMembership,
`${TableName.IdentityGroupMembership}.groupId`,
`${TableName.Groups}.id`
)
.where(`${TableName.IdentityGroupMembership}.identityId`, dto.actorId)
.select(db.ref("id").withSchema(TableName.Groups));
const accessibleSubOrgIdsSubquery = conn(TableName.Membership)
.where(`${TableName.Membership}.scope`, AccessScope.Organization)
.whereIn(`${TableName.Membership}.scopeOrgId`, subOrgIdsSubquery)
.andWhere((qb) => {
if (dto.actorType === ActorType.USER) {
void qb
.where(`${TableName.Membership}.actorUserId`, dto.actorId)
.orWhereIn(`${TableName.Membership}.actorGroupId`, userGroupIdsSubquery);
} else {
void qb
.where(`${TableName.Membership}.actorIdentityId`, dto.actorId)
.orWhereIn(`${TableName.Membership}.actorGroupId`, identityGroupIdsSubquery);
}
})
.select(db.ref("scopeOrgId").withSchema(TableName.Membership));
return conn(TableName.Organization)
.where(`${TableName.Organization}.rootOrgId`, dto.orgId)
.whereIn(`${TableName.Organization}.id`, accessibleSubOrgIdsSubquery);
}
return conn(TableName.Organization).where(`${TableName.Organization}.rootOrgId`, dto.orgId);
};
const baseQuery = buildBaseQuery();
if (dto.search)
void baseQuery.whereILike(`${TableName.Organization}.name`, `%${sanitizeSqlLikeString(dto.search)}%`);
const [totalResult, orgs] = await Promise.all([
baseQuery.clone().count({ count: "*" }).first(),
baseQuery
.clone()
.select(selectAllTableCols(TableName.Organization))
.orderBy(`${TableName.Organization}.${orderBy}`, orderDirection)
.limit(dto.limit ?? 25)
.offset(dto.offset ?? 0)
]);
return { orgs, totalCount: Number(totalResult?.count ?? 0) };
} catch (error) {
throw new DatabaseError({ error, name: "List sub organization" });
}
};
/**
* Returns all root orgs the actor can see (has membership, direct or via group),
* each with its sub-organizations. Basic org fields and suborg id/name/slug only.
*/
const listOrganizationsWithSubOrgs = async (dto: { actorId: string }): Promise<TOrgWithSubOrgs[]> => {
try {
const conn = db.replicaNode();
// Query 1: Get root orgs the user has direct membership in (full org info)
const rootOrgs = (await conn(TableName.Organization)
.join(TableName.Membership, (qb) => {
void qb
.on(`${TableName.Membership}.scopeOrgId`, "=", db.ref("id").withSchema(TableName.Organization))
.andOn(`${TableName.Membership}.scope`, db.raw("?", [AccessScope.Organization]))
.andOn(`${TableName.Membership}.actorUserId`, db.raw("?", [dto.actorId]));
})
.whereNull(`${TableName.Organization}.rootOrgId`)
.leftJoin(TableName.SamlConfig, (qb) => {
qb.on(`${TableName.SamlConfig}.orgId`, "=", `${TableName.Organization}.id`).andOn(
`${TableName.SamlConfig}.isActive`,
"=",
db.raw("true")
);
})
.leftJoin(TableName.OidcConfig, (qb) => {
qb.on(`${TableName.OidcConfig}.orgId`, "=", `${TableName.Organization}.id`).andOn(
`${TableName.OidcConfig}.isActive`,
"=",
db.raw("true")
);
})
.select(
selectAllTableCols(TableName.Organization),
db.ref("createdAt").withSchema(TableName.Membership).as("userJoinedAt"),
db.raw(`
CASE
WHEN ${TableName.SamlConfig}."orgId" IS NOT NULL THEN 'saml'
WHEN ${TableName.OidcConfig}."orgId" IS NOT NULL THEN 'oidc'
ELSE ''
END as "orgAuthMethod"
`)
)) as (TOrganizations & { orgAuthMethod: string; userJoinedAt: Date | null })[];
if (rootOrgs.length === 0) return [];
const rootOrgIds = rootOrgs.map((o) => o.id);
// Query 2: Get sub-orgs under those root orgs that the user can access (direct or via group)
const userGroupIdsSubquery = conn(TableName.Groups)
.join(TableName.UserGroupMembership, `${TableName.UserGroupMembership}.groupId`, `${TableName.Groups}.id`)
.where(`${TableName.UserGroupMembership}.userId`, dto.actorId)
.select(db.ref("id").withSchema(TableName.Groups));
const subOrgs = await conn(TableName.Organization)
.join(TableName.Membership, (qb) => {
void qb
.on(`${TableName.Membership}.scopeOrgId`, "=", db.ref("id").withSchema(TableName.Organization))
.andOn(`${TableName.Membership}.scope`, db.raw("?", [AccessScope.Organization]));
})
.whereIn(`${TableName.Organization}.rootOrgId`, rootOrgIds)
.andWhere((qb) => {
void qb
.where(`${TableName.Membership}.actorUserId`, dto.actorId)
.orWhereIn(`${TableName.Membership}.actorGroupId`, userGroupIdsSubquery);
})
.select(
db.ref("id").withSchema(TableName.Organization),
db.ref("name").withSchema(TableName.Organization),
db.ref("slug").withSchema(TableName.Organization),
db.ref("rootOrgId").withSchema(TableName.Organization),
db.ref("createdAt").withSchema(TableName.Membership).as("userJoinedAt")
);
const uniqueSubOrgs = unique(subOrgs, (s) => s.id);
const subOrgsByRootId = groupBy(uniqueSubOrgs, (s) => s.rootOrgId as string);
return rootOrgs.map((org) => ({
...org,
userJoinedAt: org.userJoinedAt ?? null,
subOrganizations: (subOrgsByRootId[org.id] ?? []).map((s) => ({
id: s.id,
name: s.name,
slug: s.slug,
userJoinedAt: s.userJoinedAt ?? null
}))
}));
} catch (error) {
throw new DatabaseError({ error, name: "List organizations with sub orgs" });
}
};
const findOrgById = async (orgId: string) => {
try {
const org = (await db
.replicaNode()(TableName.Organization)
.where({ [`${TableName.Organization}.id` as "id"]: orgId })
.whereNull(`${TableName.Organization}.rootOrgId`)
.leftJoin(TableName.SamlConfig, (qb) => {
qb.on(`${TableName.SamlConfig}.orgId`, "=", `${TableName.Organization}.id`).andOn(
`${TableName.SamlConfig}.isActive`,
"=",
db.raw("true")
);
})
.leftJoin(TableName.OidcConfig, (qb) => {
qb.on(`${TableName.OidcConfig}.orgId`, "=", `${TableName.Organization}.id`).andOn(
`${TableName.OidcConfig}.isActive`,
"=",
db.raw("true")
);
})
.select(selectAllTableCols(TableName.Organization))
.select(
db.raw(`
CASE
WHEN ${TableName.SamlConfig}."orgId" IS NOT NULL THEN '${OrgAuthMethod.SAML}'
WHEN ${TableName.OidcConfig}."orgId" IS NOT NULL THEN '${OrgAuthMethod.OIDC}'
ELSE ''
END as "orgAuthMethod"
`)
)
.first()) as TOrganizations & { orgAuthMethod?: string };
return org;
} catch (error) {
throw new DatabaseError({ error, name: "Find org by id" });
}
};
const findOrgBySlug = async (orgSlug: string) => {
try {
const org = (await db
.replicaNode()(TableName.Organization)
.whereNull(`${TableName.Organization}.rootOrgId`)
.where({ [`${TableName.Organization}.slug` as "slug"]: orgSlug })
.leftJoin(TableName.SamlConfig, (qb) => {
qb.on(`${TableName.SamlConfig}.orgId`, "=", `${TableName.Organization}.id`).andOn(
`${TableName.SamlConfig}.isActive`,
"=",
db.raw("true")
);
})
.leftJoin(TableName.OidcConfig, (qb) => {
qb.on(`${TableName.OidcConfig}.orgId`, "=", `${TableName.Organization}.id`).andOn(
`${TableName.OidcConfig}.isActive`,
"=",
db.raw("true")
);
})
.select(selectAllTableCols(TableName.Organization))
.select(
db.raw(`
CASE
WHEN ${TableName.SamlConfig}."orgId" IS NOT NULL THEN '${OrgAuthMethod.SAML}'
WHEN ${TableName.OidcConfig}."orgId" IS NOT NULL THEN '${OrgAuthMethod.OIDC}'
ELSE ''
END as "orgAuthMethod"
`)
)
.first()) as TOrganizations & { orgAuthMethod?: string };
return org;
} catch (error) {
throw new DatabaseError({ error, name: "Find org by slug" });
}
};
// special query
const findAllOrgsByUserId = async (
userId: string
): Promise<
(TOrganizations & { orgAuthMethod: string; userRole: string; userStatus: string; userJoinedAt: Date })[]
> => {
try {
const org = (await db
.replicaNode()(TableName.Membership)
.where(`${TableName.Membership}.actorUserId`, userId)
.where(`${TableName.Membership}.scope`, AccessScope.Organization)
.whereNotNull(`${TableName.Membership}.actorUserId`)
.join(TableName.MembershipRole, `${TableName.Membership}.id`, `${TableName.MembershipRole}.membershipId`)
.join(TableName.Organization, `${TableName.Membership}.scopeOrgId`, `${TableName.Organization}.id`)
.whereNull(`${TableName.Organization}.rootOrgId`)
.leftJoin(TableName.SamlConfig, (qb) => {
qb.on(`${TableName.SamlConfig}.orgId`, "=", `${TableName.Organization}.id`).andOn(
`${TableName.SamlConfig}.isActive`,
"=",
db.raw("true")
);
})
.leftJoin(TableName.OidcConfig, (qb) => {
qb.on(`${TableName.OidcConfig}.orgId`, "=", `${TableName.Organization}.id`).andOn(
`${TableName.OidcConfig}.isActive`,
"=",
db.raw("true")
);
})
.select(selectAllTableCols(TableName.Organization))
.select(db.ref("role").withSchema(TableName.MembershipRole).as("userRole"))
.select(db.ref("status").withSchema(TableName.Membership).as("userStatus"))
.select(db.ref("createdAt").withSchema(TableName.Membership).as("userJoinedAt"))
.select(
db.raw(`
CASE
WHEN ${TableName.SamlConfig}."orgId" IS NOT NULL THEN 'saml'
WHEN ${TableName.OidcConfig}."orgId" IS NOT NULL THEN 'oidc'
ELSE ''
END as "orgAuthMethod"
`)
)) as (TOrganizations & {
orgAuthMethod: string;
userRole: string;
userStatus: string;
userJoinedAt: Date;
})[];
return org;
} catch (error) {
throw new DatabaseError({ error, name: "Find all org by user id" });
}
};
// Membership is required so a removed or deactivated creator is not locked out everywhere. A second
// account with admin can therefore free the slot; accepted, since that costs an invite cycle per org.
// Takes the transaction, not an optional one, so the read cannot hit a replica and defeat the lock.
const countJoinedRootOrgsCreatedByUserId = async (userId: string, tx: Knex) => {
try {
const count = await tx(TableName.Organization)
.where(`${TableName.Organization}.createdByUserId`, userId)
.whereNull(`${TableName.Organization}.rootOrgId`)
.whereExists((qb) => {
void qb
.select(tx.raw("1"))
.from(TableName.Membership)
.where(`${TableName.Membership}.actorUserId`, userId)
.where(`${TableName.Membership}.scope`, AccessScope.Organization)
.where(`${TableName.Membership}.status`, OrgMembershipStatus.Accepted)
.where(`${TableName.Membership}.isActive`, true)
.whereRaw(`"${TableName.Membership}"."scopeOrgId" = "${TableName.Organization}"."id"`);
})
.count("*", { as: "count" })
.first();
return Number((count as unknown as CountResult)?.count ?? 0);
} catch (error) {
throw new DatabaseError({ error, name: "Count joined root orgs created by user id" });
}
};
const findOrgByProjectId = async (projectId: string): Promise<TOrganizations> => {
try {
const [org] = await db
.replicaNode()(TableName.Project)
.where({ [`${TableName.Project}.id` as "id"]: projectId })
.join(TableName.Organization, `${TableName.Project}.orgId`, `${TableName.Organization}.id`)
.select(selectAllTableCols(TableName.Organization));
return org;
} catch (error) {
throw new DatabaseError({ error, name: "Find org by project id" });
}
};
// special query
const findAllOrgMembers = async (orgId: string) => {
try {
const members = await db
.replicaNode()(TableName.Membership)
.where(`${TableName.Membership}.scopeOrgId`, orgId)
.where(`${TableName.Membership}.scope`, AccessScope.Organization)
.whereNotNull(`${TableName.Membership}.actorUserId`)
.join(TableName.Users, `${TableName.Membership}.actorUserId`, `${TableName.Users}.id`)
.join(TableName.MembershipRole, `${TableName.Membership}.id`, `${TableName.MembershipRole}.membershipId`)
.leftJoin(TableName.Role, `${TableName.MembershipRole}.customRoleId`, `${TableName.Role}.id`)
.leftJoin<TUserEncryptionKeys>(
TableName.UserEncryptionKey,
`${TableName.UserEncryptionKey}.userId`,
`${TableName.Users}.id`
)
.select(
db.ref("id").withSchema(TableName.Membership),
db.ref("inviteEmail").withSchema(TableName.Membership),
db.ref("scopeOrgId").withSchema(TableName.Membership).as("orgId"),
db.ref("role").withSchema(TableName.MembershipRole),
db.ref("customRoleId").withSchema(TableName.MembershipRole).as("roleId"),
db.ref("status").withSchema(TableName.Membership),
db.ref("isActive").withSchema(TableName.Membership),
db.ref("lastLoginAuthMethod").withSchema(TableName.Membership),
db.ref("lastLoginTime").withSchema(TableName.Membership),
db.ref("email").withSchema(TableName.Users),
db.ref("isEmailVerified").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("superAdmin").withSchema(TableName.Users),
db.ref("publicKey").withSchema(TableName.UserEncryptionKey)
)
.where({ isGhost: false }) // MAKE SURE USER IS NOT A GHOST USER
.orderBy("firstName")
.orderBy("lastName");
return members.map(
({ email, isEmailVerified, username, firstName, lastName, userId, publicKey, superAdmin, ...data }) => ({
...data,
user: { email, isEmailVerified, username, firstName, lastName, id: userId, publicKey, superAdmin }
})
);
} catch (error) {
throw new DatabaseError({ error, name: "Find all org members" });
}
};
// Users with no direct org row never show up in findAllOrgMembers, so this is the only place they come
// from. Expired temporary roles are skipped to match isActiveRole. Roles come back permanent first so
// the one a client shows when it has room for only one is never the one about to expire. The group id
// breaks name ties: a sub-org can hold a local group and a linked parent group with the same name, and
// sqlNestRelationships nests each role under the group it last appended, so a group's rows must stay
// contiguous.
const findAllOrgGroupUserAccess = async (orgId: string) => {
try {
const rows = await db
.replicaNode()(TableName.Membership)
.where(`${TableName.Membership}.scopeOrgId`, orgId)
.where(`${TableName.Membership}.scope`, AccessScope.Organization)
.whereNotNull(`${TableName.Membership}.actorGroupId`)
.join(TableName.Groups, `${TableName.Groups}.id`, `${TableName.Membership}.actorGroupId`)
.join(TableName.UserGroupMembership, `${TableName.UserGroupMembership}.groupId`, `${TableName.Groups}.id`)
.join(TableName.Users, `${TableName.Users}.id`, `${TableName.UserGroupMembership}.userId`)
.join(TableName.MembershipRole, `${TableName.MembershipRole}.membershipId`, `${TableName.Membership}.id`)
.where(`${TableName.Users}.isGhost`, false)
.where((qb) => {
void qb
.where(`${TableName.MembershipRole}.isTemporary`, false)
.orWhere(`${TableName.MembershipRole}.temporaryAccessEndTime`, ">", new Date());
})
.select(
db.ref("id").withSchema(TableName.Users).as("userId"),
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("superAdmin").withSchema(TableName.Users),
db.ref("id").withSchema(TableName.Groups).as("groupId"),
db.ref("name").withSchema(TableName.Groups).as("groupName"),
db.ref("slug").withSchema(TableName.Groups).as("groupSlug"),
db.ref("id").withSchema(TableName.MembershipRole).as("membershipRoleId"),
db.ref("role").withSchema(TableName.MembershipRole),
db.ref("customRoleId").withSchema(TableName.MembershipRole).as("roleId")
)
.orderBy([
{ column: `${TableName.Groups}.name` },
{ column: `${TableName.Groups}.id` },
{ column: `${TableName.MembershipRole}.isTemporary` },
{ column: `${TableName.MembershipRole}.temporaryAccessEndTime` },
{ column: `${TableName.MembershipRole}.role` }
]);
return sqlNestRelationships({
data: rows,
key: "userId",
parentMapper: ({ userId, email, username, firstName, lastName, superAdmin }) => ({
user: { id: userId, email, username, firstName, lastName, superAdmin }
}),
childrenMapper: [
{
key: "groupId",
label: "groups" as const,
mapper: ({ groupId, groupName, groupSlug }) => ({ id: groupId, name: groupName, slug: groupSlug }),
childrenMapper: [
{
key: "membershipRoleId",
label: "roles" as const,
mapper: ({ role, roleId }) => ({ role, roleId: roleId ?? null })
}
]
}
]
});
} catch (error) {
throw new DatabaseError({ error, name: "Find all org group user access" });
}
};
// TODO(sub-org): updated this logic later
const countAllOrgMembers = async (orgId: string) => {
try {
const count = await db
.replicaNode()(TableName.Membership)
.where(`${TableName.Membership}.scopeOrgId`, orgId)
.where(`${TableName.Membership}.scope`, AccessScope.Organization)
.where(`${TableName.Membership}.status`, OrgMembershipStatus.Accepted)
.whereNotNull(`${TableName.Membership}.actorUserId`)
.count("*")
.join(TableName.Users, `${TableName.Membership}.actorUserId`, `${TableName.Users}.id`)
.where({ isGhost: false, [`${TableName.Membership}.isActive` as "isActive"]: true })
.first();
return parseInt((count as unknown as CountResult).count || "0", 10);
} catch (error) {
throw new DatabaseError({ error, name: "Count all org members" });
}
};
const countSecretManagerProjectMembers = async (orgId: string) => {
try {
const projectUsers = db
.replicaNode()(TableName.Membership)
.where(`${TableName.Membership}.scope`, AccessScope.Project)
.where(`${TableName.Membership}.isActive`, true)
.join(TableName.Project, `${TableName.Membership}.scopeProjectId`, `${TableName.Project}.id`)
.where(`${TableName.Project}.orgId`, orgId)
.where(`${TableName.Project}.type`, ProjectType.SecretManager)
.where(`${TableName.Project}.version`, ProjectVersion.V3)
.whereNull(`${TableName.Project}.deleteAfter`)
.leftJoin(
TableName.UserGroupMembership,
`${TableName.UserGroupMembership}.groupId`,
`${TableName.Membership}.actorGroupId`
)
.select(
db.raw(`coalesce(??, ??) as "userId"`, [
`${TableName.Membership}.actorUserId`,
`${TableName.UserGroupMembership}.userId`
])
);
const count = await db
.replicaNode()(TableName.Users)
.where(`${TableName.Users}.isGhost`, false)
.whereIn(`${TableName.Users}.id`, projectUsers)
.countDistinct<{ count: string }[]>(`${TableName.Users}.id as count`)
.first();
return Number(count?.count ?? 0);
} catch (error) {
throw new DatabaseError({ error, name: "Count secret manager project members" });
}
};
const findOrgMembersByUsername = async (orgId: string, usernames: string[], tx?: Knex) => {
try {
const conn = tx || db.replicaNode();
const members = await conn(TableName.Membership)
.where(`${TableName.Membership}.scopeOrgId`, orgId)
.where(`${TableName.Membership}.scope`, AccessScope.Organization)
.whereNotNull(`${TableName.Membership}.actorUserId`)
.join(TableName.Users, `${TableName.Membership}.actorUserId`, `${TableName.Users}.id`)
.join(TableName.MembershipRole, `${TableName.Membership}.id`, `${TableName.MembershipRole}.membershipId`)
.leftJoin(TableName.Role, `${TableName.MembershipRole}.customRoleId`, `${TableName.Role}.id`)
.leftJoin<TUserEncryptionKeys>(
TableName.UserEncryptionKey,
`${TableName.UserEncryptionKey}.userId`,
`${TableName.Users}.id`
)
.select(
conn.ref("id").withSchema(TableName.Membership),
conn.ref("inviteEmail").withSchema(TableName.Membership),
conn.ref("scopeOrgId").withSchema(TableName.Membership).as("orgId"),
db.ref("role").withSchema(TableName.MembershipRole),
db.ref("customRoleId").withSchema(TableName.MembershipRole).as("roleId"),
conn.ref("status").withSchema(TableName.Membership),
conn.ref("username").withSchema(TableName.Users),
conn.ref("email").withSchema(TableName.Users),
conn.ref("firstName").withSchema(TableName.Users),
conn.ref("lastName").withSchema(TableName.Users),
conn.ref("id").withSchema(TableName.Users).as("userId"),
conn.ref("publicKey").withSchema(TableName.UserEncryptionKey)
)
.where({ isGhost: false })
.whereIn("username", usernames);
return members.map(({ email, firstName, lastName, userId, publicKey, ...data }) => ({
...data,
user: { email, firstName, lastName, id: userId, publicKey }
}));
} catch (error) {
throw new DatabaseError({ error, name: "Find all org members" });
}
};
const findOrgMembersByRole = async (orgId: string, role: OrgMembershipRole, tx?: Knex) => {
try {
const conn = tx || db.replicaNode();
const members = await conn(TableName.Membership)
.where(`${TableName.Membership}.scopeOrgId`, orgId)
.where(`${TableName.Membership}.scope`, AccessScope.Organization)
.whereNotNull(`${TableName.Membership}.actorUserId`)
.join(TableName.MembershipRole, `${TableName.Membership}.id`, `${TableName.MembershipRole}.membershipId`)
.where(`${TableName.MembershipRole}.role`, role)
.join(TableName.Users, `${TableName.Membership}.actorUserId`, `${TableName.Users}.id`)
.leftJoin<TUserEncryptionKeys>(
TableName.UserEncryptionKey,
`${TableName.UserEncryptionKey}.userId`,
`${TableName.Users}.id`
)
.select(
conn.ref("id").withSchema(TableName.Membership),
conn.ref("inviteEmail").withSchema(TableName.Membership),
conn.ref("scopeOrgId").withSchema(TableName.Membership).as("orgId"),
conn.ref("role").withSchema(TableName.MembershipRole),
conn.ref("customRoleId").withSchema(TableName.MembershipRole).as("roleId"),
conn.ref("status").withSchema(TableName.Membership),
conn.ref("username").withSchema(TableName.Users),
conn.ref("email").withSchema(TableName.Users),
conn.ref("firstName").withSchema(TableName.Users),
conn.ref("lastName").withSchema(TableName.Users),
conn.ref("id").withSchema(TableName.Users).as("userId"),
conn.ref("publicKey").withSchema(TableName.UserEncryptionKey),
conn.ref("isActive").withSchema(TableName.Membership)
)
.where({ isGhost: false });
return members.map(({ username, email, firstName, lastName, userId, publicKey, isActive, ...data }) => ({
...data,
user: { username, email, firstName, lastName, id: userId, publicKey, isActive }
}));
} catch (error) {
throw new DatabaseError({ error, name: "Find org members by role" });
}
};
const create = async (dto: TOrganizationsInsert, tx?: Knex) => {
try {
// An org created by this code has carried the org-scoped secret value digest on every secret
// it will ever write, so it is complete by construction and needs no backfill. The column
// defaults to false so that an org created by an older replica mid-deploy, whose writes carry
// no digest, is not wrongly marked complete.
const [organization] = await (tx || db)(TableName.Organization)
.insert({ orgWideSecretValueTrackingEnabled: true, ...dto })
.returning("*");
return organization;
} catch (error) {
throw new DatabaseError({ error, name: "Create organization" });
}
};
const deleteById = async (orgId: string, tx?: Knex) => {
try {
const [org] = await (tx || db)(TableName.Organization).where({ id: orgId }).delete().returning("*");
return org;
} catch (error) {
throw new DatabaseError({ error, name: "Update organization" });
}
};
const updateById = async (orgId: string, data: Partial<TOrganizations>, tx?: Knex) => {
try {
const [org] = await (tx || db)(TableName.Organization)
.where({ id: orgId })
.update({ ...data })
.returning("*");
return org;
} catch (error) {
throw new DatabaseError({ error, name: "Update organization" });
}
};
// MEMBERSHIP OPERATIONS
// --------------------
const createMembership = async (data: TMembershipsInsert, tx?: Knex) => {
try {
const [membership] = await (tx || db)(TableName.Membership).insert(data).returning("*");
return membership;
} catch (error) {
throw new DatabaseError({ error, name: "Create org membership" });
}
};
const bulkCreateMemberships = async (data: TMembershipsInsert[], tx?: Knex) => {
try {
const memberships = await (tx || db)(TableName.Membership).insert(data).returning("*");
return memberships;
} catch (error) {
throw new DatabaseError({ error, name: "Create org memberships" });
}
};
const updateMembershipById = async (id: string, data: TMembershipsUpdate, tx?: Knex) => {
try {
const [membership] = await (tx || db)(TableName.Membership).where({ id }).update(data).returning("*");
return membership;
} catch (error) {
throw new DatabaseError({ error, name: "Update org membership" });
}
};
const updateMembership = async (filter: Partial<TMemberships>, data: TMembershipsUpdate, tx?: Knex) => {
try {
const membership = await (tx || db)(TableName.Membership).where(filter).update(data).returning("*");
return membership;
} catch (error) {
throw new DatabaseError({ error, name: "Update org memberships" });
}
};
const deleteMembershipById = async (id: string, orgId: string, tx?: Knex) => {
try {
const [membership] = await (tx || db)(TableName.Membership)
.where({ id, scopeOrgId: orgId, scope: AccessScope.Organization })
.delete()
.returning("*");
return membership;
} catch (error) {
throw new DatabaseError({ error, name: "Delete org membership" });
}
};
const deleteMembershipsById = async (ids: string[], orgId: string, tx?: Knex) => {
try {
const memberships = await (tx || db)(TableName.Membership)
.where({
scopeOrgId: orgId,
scope: AccessScope.Organization
})
.whereIn("id", ids)
.delete()
.returning("*");
return memberships;
} catch (error) {
throw new DatabaseError({ error, name: "Delete org memberships" });
}
};
const findMembership = async (
filter: TFindFilter<TMemberships>,
{ offset, limit, sort, tx }: TFindOpt<TMemberships> = {}
) => {
try {
const query = (tx || db.replicaNode())(TableName.Membership)
// eslint-disable-next-line
.where(buildFindFilter(filter))
.where("scope", AccessScope.Organization)
.join(TableName.Users, `${TableName.Users}.id`, `${TableName.Membership}.actorUserId`)
.join(TableName.Organization, `${TableName.Organization}.id`, `${TableName.Membership}.scopeOrgId`)
.leftJoin(TableName.UserAliases, function joinUserAlias() {
this.on(`${TableName.UserAliases}.userId`, "=", `${TableName.Membership}.actorUserId`)
.andOn(`${TableName.UserAliases}.orgId`, "=", `${TableName.Membership}.scopeOrgId`)
.andOn(`${TableName.UserAliases}.aliasType`, "=", (tx || db).raw("?", ["saml"]));
})
.select(
selectAllTableCols(TableName.Membership),
db.ref("email").withSchema(TableName.Users),
db.ref("isEmailVerified").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),
db.ref("externalId").withSchema(TableName.UserAliases)
)
.where({ isGhost: false });
if (limit) void query.limit(limit);
if (offset) void query.offset(offset);
if (sort) {
void query.orderBy(sort.map(([column, order, nulls]) => ({ column: column as string, order, nulls })));
}
const res = await query;
return res;
} catch (error) {
throw new DatabaseError({ error, name: "Find one" });
}
};
/**
* Returns all effective org memberships for an actor (user or identity) in the org: direct
* membership and any memberships via groups the actor belongs to. Single query
*/
const findEffectiveOrgMemberships = async (dto: {
actorType: ActorType;
actorId: string;
orgId: string;
status?: OrgMembershipStatus;
/** When true, do not filter by status (e.g. to find Invited and update to Accepted) */
acceptAnyStatus?: boolean;
tx?: Knex;
}): Promise<TMemberships[]> => {
try {
const conn = dto.tx ?? db.replicaNode();
const status = dto.status ?? OrgMembershipStatus.Accepted;
const anyStatus = dto.acceptAnyStatus === true;
const userGroupIdsSubquery = conn(TableName.Groups)
.join(TableName.UserGroupMembership, `${TableName.UserGroupMembership}.groupId`, `${TableName.Groups}.id`)
.where(`${TableName.UserGroupMembership}.userId`, dto.actorId)
.select(db.ref("id").withSchema(TableName.Groups));
const identityGroupIdsSubquery = conn(TableName.Groups)
.join(
TableName.IdentityGroupMembership,
`${TableName.IdentityGroupMembership}.groupId`,
`${TableName.Groups}.id`
)
.where(`${TableName.IdentityGroupMembership}.identityId`, dto.actorId)
.select(db.ref("id").withSchema(TableName.Groups));
const query = conn(TableName.Membership)
.where(`${TableName.Membership}.scope`, AccessScope.Organization)
.where(`${TableName.Membership}.scopeOrgId`, dto.orgId)
.andWhere((qb) => {
if (dto.actorType === ActorType.USER) {
void qb
.where(`${TableName.Membership}.actorUserId`, dto.actorId)
.orWhereIn(`${TableName.Membership}.actorGroupId`, userGroupIdsSubquery);
} else {
void qb
.where(`${TableName.Membership}.actorIdentityId`, dto.actorId)
.orWhereIn(`${TableName.Membership}.actorGroupId`, identityGroupIdsSubquery);
}
});
if (!anyStatus) {
void query.where((qb) => {
void qb.where(`${TableName.Membership}.status`, status).orWhereNull(`${TableName.Membership}.status`);
});
}
const rows = await query.select(selectAllTableCols(TableName.Membership));
return rows as TMemberships[];
} catch (error) {
throw new DatabaseError({ error, name: "Find effective org memberships" });
}
};
const findActiveEffectiveOrgMembershipsByUserId = async (userId: string, tx?: Knex): Promise<TMemberships[]> => {
try {
const conn = tx ?? db.replicaNode();
const userGroupIdsSubquery = conn(TableName.UserGroupMembership)
.where(`${TableName.UserGroupMembership}.userId`, userId)
.select(db.ref("groupId").withSchema(TableName.UserGroupMembership));
const rows = await conn(TableName.Membership)
.where(`${TableName.Membership}.scope`, AccessScope.Organization)
.where(`${TableName.Membership}.isActive`, true)
.where((qb) => {
void qb
.where(`${TableName.Membership}.actorUserId`, userId)
.orWhereIn(`${TableName.Membership}.actorGroupId`, userGroupIdsSubquery);
})
.where((qb) => {
void qb
.where(`${TableName.Membership}.status`, OrgMembershipStatus.Accepted)
.orWhereNull(`${TableName.Membership}.status`);
})
.select(selectAllTableCols(TableName.Membership));
return rows as TMemberships[];
} catch (error) {
throw new DatabaseError({ error, name: "Find active effective org memberships by user id" });
}
};
/**
* Returns the first effective org membership for an actor (user or identity): direct or via group.
* Use for access checks and to get a single membership id/role. For all memberships use findEffectiveOrgMemberships.
*/
const findEffectiveOrgMembership = async (dto: {
actorType: ActorType;
actorId: string;
orgId: string;
status?: OrgMembershipStatus;
/** When true, do not filter by status (e.g. to find Invited and update to Accepted) */
acceptAnyStatus?: boolean;
tx?: Knex;
}): Promise<TMemberships | null> => {
try {
const conn = dto.tx ?? db.replicaNode();
const status = dto.status ?? OrgMembershipStatus.Accepted;
const anyStatus = dto.acceptAnyStatus === true;
const actorColumn =
dto.actorType === ActorType.USER
? (`${TableName.Membership}.actorUserId` as const)
: (`${TableName.Membership}.actorIdentityId` as const);
const directQuery = conn(TableName.Membership)
.where(`${TableName.Membership}.scope`, AccessScope.Organization)
.where(`${TableName.Membership}.scopeOrgId`, dto.orgId)
.where(actorColumn, dto.actorId)
.select(selectAllTableCols(TableName.Membership))
.first();
if (!anyStatus) {
void directQuery.where((qb) => {
void qb.where(`${TableName.Membership}.status`, status).orWhereNull(`${TableName.Membership}.status`);
});
}
const direct = (await directQuery) as TMemberships | undefined;
if (direct) {
return direct;
}
// Direct miss: fall back to group membership.
const list = await findEffectiveOrgMemberships(dto);
return list[0] ?? null;
} catch (error) {
throw new DatabaseError({ error, name: "Find effective org membership" });
}
};
const $buildScimMembershipQuery = (
orgId: string,
scimFilter: string | undefined,
orgAuthMethod: string | undefined,
{ tx, membershipId }: { tx?: Knex; membershipId?: string } = {}
) => {
const conn = tx || db.replicaNode();
// Determine alias type from org auth method (default to saml for backwards compatibility)
const aliasType = orgAuthMethod === OrgAuthMethod.OIDC ? OrgAuthMethod.OIDC : OrgAuthMethod.SAML;
const resolveColumn = (attrPath: string) => {
switch (attrPath) {
case "id":
return `${TableName.Membership}.id`;
case "active":
return `${TableName.Membership}.isActive`;
case "name.givenName":
return `${TableName.Users}.firstName`;
case "name.familyName":
return `${TableName.Users}.lastName`;
case "email.value":
return `${TableName.Users}.email`;
default:
return null;
}
};
const resolveFilterAttribute: TScimAttributeResolver = (attrPath) => {
if (attrPath !== "userName") return resolveColumn(attrPath);
return (qb, op, value) => {
const matchingAliases = conn(TableName.UserAliases)
.select(conn.raw("1"))
.whereRaw("?? = ??", [`${TableName.UserAliases}.userId`, `${TableName.Membership}.actorUserId`])
.where(`${TableName.UserAliases}.orgId`, orgId)
.where(`${TableName.UserAliases}.aliasType`, aliasType);
if (op === "ne") {
applyScimComparison(matchingAliases, `${TableName.UserAliases}.externalId`, "eq", value);
void qb.whereNotExists(matchingAliases);
} else {
applyScimComparison(matchingAliases, `${TableName.UserAliases}.externalId`, op, value);
void qb.whereExists(matchingAliases);
}
};
};
const resolveDisplayAttribute: TScimAttributeResolver = (attrPath) =>
attrPath === "userName" ? `${SCIM_USER_ALIAS}.externalId` : resolveColumn(attrPath);
const query = conn(TableName.Membership)
.where(`${TableName.Membership}.scopeOrgId`, orgId)
.where(`${TableName.Membership}.scope`, AccessScope.Organization)
.whereNotNull(`${TableName.Membership}.actorUserId`)
.where((qb) => {
if (membershipId) {
void qb.where(`${TableName.Membership}.id`, membershipId);
}
if (scimFilter) {
void generateKnexQueryFromScim(qb, scimFilter, resolveFilterAttribute);
}
})
.join(TableName.Users, `${TableName.Users}.id`, `${TableName.Membership}.actorUserId`)
.join(TableName.Organization, `${TableName.Organization}.id`, `${TableName.Membership}.scopeOrgId`)
.whereNull(`${TableName.Organization}.rootOrgId`)
.leftJoin(`${TableName.UserAliases} as ${SCIM_USER_ALIAS}`, function joinScimUserAlias() {
this.on(`${SCIM_USER_ALIAS}.userId`, "=", `${TableName.Membership}.actorUserId`)
.andOn(`${SCIM_USER_ALIAS}.orgId`, "=", conn.raw("?", [orgId]))
.andOn(`${SCIM_USER_ALIAS}.aliasType`, "=", conn.raw("?", [aliasType]));
})
.where({ isGhost: false });
const aliasRowMatchesFilter = scimFilter
? conn
.queryBuilder()
.select(conn.raw("1"))
.where((qb) => {
void generateKnexQueryFromScim(qb, scimFilter, resolveDisplayAttribute);
})
: undefined;
return { query, aliasRowMatchesFilter };
};
const findMembershipWithScimFilter = async (
orgId: string,
scimFilter: string | undefined,
orgAuthMethod: string | undefined,
{ offset, limit, sort, tx, membershipId }: TFindOpt<TMemberships> & { membershipId?: string } = {}
): Promise<
(TMemberships & {
email: string | null;
isEmailVerified: boolean | null;
username: string;
firstName: string | null;
lastName: string | null;
scimEnabled: boolean | null;
defaultMembershipRole: string;
externalId: string | null;
})[]
> => {
try {
const conn = tx || db.replicaNode();
const { query: memberships, aliasRowMatchesFilter } = $buildScimMembershipQuery(
orgId,
scimFilter,
orgAuthMethod,
{
tx,
membershipId
}
);
const dedupedMemberships = memberships
.distinctOn(`${TableName.Membership}.id`)
.select(
selectAllTableCols(TableName.Membership),
db.ref("email").withSchema(TableName.Users),
db.ref("isEmailVerified").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),
db.ref("defaultMembershipRole").withSchema(TableName.Organization),
db.ref("externalId").withSchema(SCIM_USER_ALIAS)
)
.orderBy(`${TableName.Membership}.id`);
if (aliasRowMatchesFilter) {
void dedupedMemberships.orderByRaw("CASE WHEN EXISTS (?) THEN 0 ELSE 1 END", [aliasRowMatchesFilter]);
}
void dedupedMemberships.orderBy(`${SCIM_USER_ALIAS}.createdAt`, "desc", "last").as("scim_membership");
const query = conn.select("*").from(dedupedMemberships);
if (sort) {
void query.orderBy(sort.map(([column, order, nulls]) => ({ column: column as string, order, nulls })));
} else {
void query.orderBy("id");
}
if (limit) void query.limit(limit);
if (offset) void query.offset(offset);
const res = await query;
// eslint-disable-next-line @typescript-eslint/no-unsafe-return
return res;
} catch (error) {
throw new DatabaseError({ error, name: "Find membership with scim filter" });
}
};
const countMembershipWithScimFilter = async (
orgId: string,
scimFilter: string | undefined,
orgAuthMethod: string | undefined,
tx?: Knex
): Promise<number> => {
try {
const doc = await $buildScimMembershipQuery(orgId, scimFilter, orgAuthMethod, { tx })
.query.countDistinct(`${TableName.Membership}.id as count`)
.first();
return Number(doc?.count ?? 0);
} catch (error) {
throw new DatabaseError({ error, name: "Count membership with scim filter" });
}
};
const findIdentityOrganization = async (
identityId: string
): Promise<{ id: string; name: string; slug: string; role: string }> => {
try {
const org = await db
.replicaNode()(TableName.Membership)
.where({ actorIdentityId: identityId })
.where(`${TableName.Membership}.scope`, AccessScope.Organization)
.whereNull(`${TableName.Organization}.rootOrgId`)
.whereNotNull(`${TableName.Membership}.actorIdentityId`)
.join(TableName.MembershipRole, `${TableName.Membership}.id`, `${TableName.MembershipRole}.membershipId`)
.join(TableName.Organization, `${TableName.Membership}.scopeOrgId`, `${TableName.Organization}.id`)
.select(db.ref("id").withSchema(TableName.Organization).as("id"))
.select(db.ref("name").withSchema(TableName.Organization).as("name"))
.select(db.ref("slug").withSchema(TableName.Organization).as("slug"))
.select(db.ref("role").withSchema(TableName.MembershipRole).as("role"));
return org?.[0];
} catch (error) {
throw new DatabaseError({ error, name: "Find identity organization" });
}
};
const findRootOrgDetails = async (orgId: string, tx?: Knex): Promise<TOrganizations | undefined> => {
try {
const org = await (tx ?? db.replicaNode())(TableName.Organization)
.select(selectAllTableCols(TableName.Organization))
.where(
"id",
db(TableName.Organization)
.select(db.raw(`CASE WHEN "rootOrgId" IS NULL THEN id ELSE "rootOrgId" END`))
.where("id", orgId)
)
.first();
return org;
} catch (error) {
throw new DatabaseError({ error, name: "FindRootOrgDetails" });
}
};
// Actors that still reach the org: a direct org row (any status) or a group holding one. Ignores isActive
// on purpose, it's for the reconcile consumer, and deactivating someone mustn't reap their projects.
const findActorsWithOrgAccess = async (
{ orgId, userIds, identityIds }: { orgId: string; userIds: string[]; identityIds: string[] },
tx?: Knex
): Promise<TOrgAccessActors> => {
try {
const conn = tx || db.replicaNode();
const orgRows = () =>
conn(TableName.Membership)
.where(`${TableName.Membership}.scope`, AccessScope.Organization)
.where(`${TableName.Membership}.scopeOrgId`, orgId);
const reaching = async (
ids: string[],
actorColumn: "actorUserId" | "actorIdentityId",
groupTable: TableName.UserGroupMembership | TableName.IdentityGroupMembership,
groupActorColumn: "userId" | "identityId"
) => {
if (!ids.length) return new Set<string>();
const direct = (await orgRows()
.whereIn(`${TableName.Membership}.${actorColumn}`, ids)
.select(db.ref(actorColumn).withSchema(TableName.Membership).as("actorId"))) as { actorId: string }[];
const viaGroup = (await orgRows()
.join(groupTable, `${groupTable}.groupId`, `${TableName.Membership}.actorGroupId`)
.whereIn(`${groupTable}.${groupActorColumn}`, ids)
.select(db.ref(groupActorColumn).withSchema(groupTable).as("actorId"))) as { actorId: string }[];
return new Set([...direct, ...viaGroup].map((row) => row.actorId));
};
return {
userIds: await reaching(userIds, "actorUserId", TableName.UserGroupMembership, "userId"),
identityIds: await reaching(identityIds, "actorIdentityId", TableName.IdentityGroupMembership, "identityId")
};
} catch (error) {
throw new DatabaseError({ error, name: "FindActorsWithOrgAccess" });
}
};
// Actors that can be granted access right now, used by every add path: an active direct org row, or no
// direct row and an active group holding one. A deactivated direct member stays out even with a group
// path, matching findEffectiveOrgMembership, which resolves the direct row first.
const findActorsWithActiveOrgAccess = async (
{ orgId, userIds, identityIds }: { orgId: string; userIds: string[]; identityIds: string[] },
tx?: Knex
): Promise<TOrgAccessActors> => {
try {
const conn = tx || db.replicaNode();
const orgRows = () =>
conn(TableName.Membership)
.where(`${TableName.Membership}.scope`, AccessScope.Organization)
.where(`${TableName.Membership}.scopeOrgId`, orgId);
const active = async (
ids: string[],
actorColumn: "actorUserId" | "actorIdentityId",
groupTable: TableName.UserGroupMembership | TableName.IdentityGroupMembership,
groupActorColumn: "userId" | "identityId"
) => {
if (!ids.length) return new Set<string>();
const direct = (await orgRows()
.whereIn(`${TableName.Membership}.${actorColumn}`, ids)
.select(
db.ref(actorColumn).withSchema(TableName.Membership).as("actorId"),
db.ref("isActive").withSchema(TableName.Membership)
)) as { actorId: string; isActive: boolean }[];
const withDirectRow = new Set(direct.map((row) => row.actorId));
const viaGroup = (await orgRows()
.join(groupTable, `${groupTable}.groupId`, `${TableName.Membership}.actorGroupId`)
.whereIn(`${groupTable}.${groupActorColumn}`, ids)
.where(`${TableName.Membership}.isActive`, true)
.select(db.ref(groupActorColumn).withSchema(groupTable).as("actorId"))) as { actorId: string }[];
return new Set([
...direct.filter((row) => row.isActive).map((row) => row.actorId),
...viaGroup.map((row) => row.actorId).filter((actorId) => !withDirectRow.has(actorId))
]);
};
return {
userIds: await active(userIds, "actorUserId", TableName.UserGroupMembership, "userId"),
identityIds: await active(identityIds, "actorIdentityId", TableName.IdentityGroupMembership, "identityId")
};
} catch (error) {
throw new DatabaseError({ error, name: "FindActorsWithActiveOrgAccess" });
}
};
// (actor, org) pairs with a project or resource row in an org they no longer reach. Resource rows count on
// their own (a group-derived PAM admin's folder row has no project row). Skips ghost users, they never
// have an org row. One streaming pass against a hash of org rows; collapsing to pairs first was measured
// and just moved the cost into an aggregate. Runs in its own replica tx so `statementTimeoutMs` can
// bound it: an overrunning backstop should fail, not keep the replica busy.
const findOrphanedProjectActors = async ({
limit,
statementTimeoutMs
}: {
limit: number;
statementTimeoutMs?: number;
}) => {
const m = TableName.Membership;
const query = async (conn: Knex): Promise<TOrphanedProjectActor[]> => {
const noDirectOrgRow = (actorColumn: "actorUserId" | "actorIdentityId") =>
conn
.select(conn.raw("1"))
.from(`${m} as direct`)
.where("direct.scope", AccessScope.Organization)
.whereRaw("?? = ??", [`direct.${actorColumn}`, `${m}.${actorColumn}`])
.whereRaw("?? = ??", ["direct.scopeOrgId", `${m}.scopeOrgId`]);
const noGroupOrgRow = (
groupTable: TableName.UserGroupMembership | TableName.IdentityGroupMembership,
groupActorColumn: "userId" | "identityId",
actorColumn: "actorUserId" | "actorIdentityId"
) =>
conn
.select(conn.raw("1"))
.from(groupTable)
.join(`${m} as viaGroup`, "viaGroup.actorGroupId", `${groupTable}.groupId`)
.where("viaGroup.scope", AccessScope.Organization)
.whereRaw("?? = ??", [`${groupTable}.${groupActorColumn}`, `${m}.${actorColumn}`])
.whereRaw("?? = ??", ["viaGroup.scopeOrgId", `${m}.scopeOrgId`]);
const userRows = (await conn(m)
.join(TableName.Users, `${TableName.Users}.id`, `${m}.actorUserId`)
.whereIn(`${m}.scope`, [AccessScope.Project, RESOURCE_SCOPE])
.where(`${TableName.Users}.isGhost`, false)
.whereNotExists(noDirectOrgRow("actorUserId"))
.whereNotExists(noGroupOrgRow(TableName.UserGroupMembership, "userId", "actorUserId"))
.distinct(db.ref("actorUserId").withSchema(m), db.ref("scopeOrgId").withSchema(m))
.limit(limit)) as { actorUserId: string; scopeOrgId: string }[];
const identityRows = (await conn(m)
.whereIn(`${m}.scope`, [AccessScope.Project, RESOURCE_SCOPE])
.whereNotNull(`${m}.actorIdentityId`)
.whereNotExists(noDirectOrgRow("actorIdentityId"))
.whereNotExists(noGroupOrgRow(TableName.IdentityGroupMembership, "identityId", "actorIdentityId"))
.distinct(db.ref("actorIdentityId").withSchema(m), db.ref("scopeOrgId").withSchema(m))
.limit(limit)) as { actorIdentityId: string; scopeOrgId: string }[];
return [
...userRows.map((row) => ({ actorUserId: row.actorUserId, actorIdentityId: null, scopeOrgId: row.scopeOrgId })),
...identityRows.map((row) => ({
actorUserId: null,
actorIdentityId: row.actorIdentityId,
scopeOrgId: row.scopeOrgId
}))
];
};
try {
return await db.replicaNode().transaction(async (trx) => {
if (statementTimeoutMs) await trx.raw(`SET LOCAL statement_timeout = ${Math.floor(statementTimeoutMs)}`);
return query(trx);
});
} catch (error) {
throw new DatabaseError({ error, name: "FindOrphanedProjectActors" });
}
};
// Serializes grants with the reconcile consumer. Two levels, like an engine escalating row locks to a table
// lock: a small set takes the tenant shared plus one exclusive key per actor, a set past the threshold takes
// the tenant exclusive and nothing per actor. Every key is a slot in Postgres's shared lock table (about
// 6,400 on default settings), so a SCIM sync adding thousands would otherwise fill it and fail unrelated
// transactions instance-wide; escalated, it only stalls grants in its own tenant. The tenant is the root org
// because access never crosses one: a root-org group reaches its sub-orgs, nothing reaches another tenant.
// Tenant first, then sorted actors in one statement, so two callers can't deadlock.
const lockActorsForOrgAccess = async ({ orgId, actorIds }: { orgId: string; actorIds: string[] }, tx: Knex) => {
const actorKeys = [...new Set(actorIds)].sort().map((actorId) => PgSqlLock.OrgAccessReconcile(actorId));
if (!actorKeys.length) return;
try {
const org = await tx(TableName.Organization).where({ id: orgId }).select("rootOrgId").first();
const scopeKey = PgSqlLock.OrgAccessReconcileScope(org?.rootOrgId ?? orgId);
if (actorKeys.length > ORG_ACCESS_LOCK_ESCALATION_THRESHOLD) {
await tx.raw("SELECT pg_advisory_xact_lock(?)", [scopeKey]);
return;
}
await tx.raw("SELECT pg_advisory_xact_lock_shared(?)", [scopeKey]);
await tx.raw("SELECT pg_advisory_xact_lock(k) FROM unnest(?::int[]) AS k", [actorKeys]);
} catch (error) {
throw new DatabaseError({ error, name: "LockActorsForOrgAccess" });
}
};
return withTransaction(db, {
...orgOrm,
findOrgByProjectId,
findAllOrgMembers,
findAllOrgGroupUserAccess,
countAllOrgMembers,
countSecretManagerProjectMembers,
listSubOrganizations,
listOrganizationsWithSubOrgs,
findOrgById,
findOrgBySlug,
findAllOrgsByUserId,
countJoinedRootOrgsCreatedByUserId,
findOrganizationsByFilter,
findOrgMembersByUsername,
findOrgMembersByRole,
create,
updateById,
deleteById,
findMembership,
findEffectiveOrgMembership,
findEffectiveOrgMemberships,
findActiveEffectiveOrgMembershipsByUserId,
findActorsWithOrgAccess,
findActorsWithActiveOrgAccess,
findOrphanedProjectActors,
lockActorsForOrgAccess,
findMembershipWithScimFilter,
countMembershipWithScimFilter,
createMembership,
bulkCreateMemberships,
updateMembershipById,
deleteMembershipById,
deleteMembershipsById,
updateMembership,
findIdentityOrganization,
findRootOrgDetails,
primaryNode: () => db.primaryNode()
});
};