Permission Systems for Enterprise That Scale

Many startups eventually gravitate towards enterprise customers for bigger tickets and long-term contracts. As enterprise customers start using your product, they soon demand advanced permission systems to manage their different user roles and access levels. A naive implementation of permission checks works perfectly fine at first, but as they use your platform more and more, the amount of data, users, and relationships will put that implementation to the test. Soon your biggest paying customer will be threatening to churn because your app is just too slow . To illustrate this problem, I've set up a simple example. Imagine an app with folders and files where an admin can see all folders and files, but standard users can only see files and folders they've created or that have been shared with them. The Naive Approach (read-time permission queries) Your first intuition will be to query the database on every request to calculate the permissions and the data that the user can access. First, you check the role of the user: is the user an admin or a standard user? If the user is not an admin, then query for all the resources the user has created or that have been shared with them. The first part is straightforward: if you are an admin, return all resources. const user = await sqlQueryOne(`SELECT * FROM users WHERE id = ?`, [ userId, ]); // Admin: Fetch all resources if (user.type === "admin") { return await sqlQuery(`SELECT * FROM resources`); } For standard users, you need to query for resources created by them. If the resource is a folder, you will have access to all of its descendants. const accessibleResources = []; const ownedResources = await sqlQuery( `SELECT * FROM resources WHERE owner_id = ?`, [userId], ); // For each owned resource, fetch descendants recursively for (const resource of ownedResources) { accessibleResources.push(resource); const descendants = await sqlQuery( ` WITH RECURSIVE tree AS ( SELECT id FROM resources WHERE parent_id = ? UNION ALL SELECT r.id FROM resources r JOIN tree t ON r.parent_id = t.id ) SELECT r.* FROM resources r JOIN tree t ON r.id = t.id `, [resource.id], ); accessibleResources.push(...descendants); } We can already see recursive queries appearing. As we own more resources with deeper nesting, the queries will get slower. Finally, we include all shared resources, their descendants if a folder was shared, plus their ancestors to show the full path. const sharedResources = await sqlQuery( ` SELECT r.* FROM shares s JOIN resources r ON s.resource_id = r.id WHERE s.user_id = ? `, [userId], ); // For each shared resource, fetch ancestors and descendants for (const resource of sharedResources) { accessibleResources.push(resource); // Query ancestors to show full path const ancestors = await sqlQuery( ` WITH RECURSIVE ancestors AS ( SELECT parent_id FROM resources WHERE id = ? UNION ALL SELECT r.parent_id FROM resources r JOIN ancestors a ON r.id = a.parent_id WHERE r.parent_id IS NOT NULL ) SELECT r.* FROM resources r JOIN ancestors a ON r.id = a.parent_id `,...

Preview: ~500 words