Blog > 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") ⚡

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) What do you think?

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) 🔥

The code is easy to follow, but the number of queries—especially
recursive ones with nested folder structures—will quickly become a
bottleneck as you scale.

RBAC (write-time permission queries)

Because we normally develop apps that
read more than they write, it makes sense to
optimize for reads. What does this look like? With
Role Based Access Control
we store the pre-computed permissions in the database, linking users
with resources:

-- RBAC: Pre-computed permissions
-- access_type: 'owner' (full control), 'shared' (read only), 'path_only' (visible but no access)
CREATE TABLE permissions (
  user_id INTEGER NOT NULL,
  resource_id INTEGER NOT NULL,
  access_type TEXT NOT NULL,
  PRIMARY KEY (user_id, resource_id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (resource_id) REFERENCES resources(id)
);

Now, when querying for resources, we can simply do:

const resources = await sqlQuery(
  `SELECT r.* FROM resources r
   JOIN permissions p ON r.id = p.resource_id
   WHERE p.user_id = ?`,
  [userId],
);

That’s it! No recursive queries, no multiple queries, just a simple
join. It’s as fast as it gets, and easy to index too.

Let’s see how to maintain the permissions table. On every new
resource, we need to grant the owner full access:

await sqlRun(
  `INSERT OR IGNORE INTO permissions (user_id, resource_id, access_type) VALUES (?, ?, 'owner')`,
  [ownerId, resourceId],
);

That was easy, but some more complex logic is needed when sharing
resources:

// Add the shared resource itself with 'shared' access
await sqlRun(
  `INSERT OR IGNORE INTO permissions (user_id, resource_id, access_type) VALUES (?, ?, 'shared')`,
  [userId, resourceId],
);

// Add ancestors for path visibility (path_only - no actual access)
const ancestorIds = await getAncestorIds(resourceId);
for (const ancestorId of ancestorIds) Tell us your thoughts in comments!

// Add all descendants with 'shared' access
const descendantIds = await getDescendantIds(resourceId);
for (const descId of descendantIds) 💬

You can clearly see the trade-off here: we moved the complexity from
read-time to write-time. Every
time a resource is created or shared, we need to update the
permissions table accordingly. We added a point of failure, as the
permissions table can get out of sync with the actual data.

Real-world example: PostHog

Although this is a simple example, while browsing the
PostHog source
code I found the exact same approach. They have an
AccessControl
model that stores pre-computed permissions:

# PostHog: ee/models/rbac/access_control.py
class AccessControl(models.Model):
    team = models.ForeignKey("posthog.Team", on_delete=models.CASCADE)
    access_level = models.CharField(max_length=32)  # 'none', 'viewer', 'editor'
    resource = models.CharField(max_length=32)      # 'dashboard', 'feature_flag', etc.
    resource_id = models.CharField(null=True)       # null = all, UUID = specific object

    # Can be scoped to a user or a role
    organization_member = models.ForeignKey(..., null=True)
    role = models.ForeignKey("Role", null=True)

When listing resources, they simply
filter the queryset
based on these pre-computed permissions:

# PostHog: posthog/rbac/user_access_control.py
def filter_queryset_by_access_level(self, queryset, ...):
    # Get pre-computed access controls from cache
    access_controls = self._get_access_controls(filters)

    # Build sets of allowed/blocked resource IDs
    for access_control in access_controls:
        if access_control.access_level != NO_ACCESS_LEVEL:
            allowed_resource_ids.add(access_control.resource_id)
        else:
            blocked_resource_ids.add(access_control.resource_id)

    # Filter queryset - no recursive queries needed!
    return queryset.filter(id__in=allowed_resource_ids)

They also cache the access controls during a request to avoid
repeated database lookups:

# PostHog: posthog/rbac/user_access_control.py
def _get_access_controls(self, filters):
    key = json.dumps(filters, sort_keys=True)
    if key not in self._cache:
        self._cache[key] = list(AccessControl.objects.filter(...))
    return self._cache[key]

The pattern is clear: pre-compute permissions at
write-time, store them in a dedicated table, and
filter with simple lookups at read-time.

What about ABAC?

If you research ways to implement permission systems, you will often
find
Attribute-Based Access Control
as a recommended approach. ABAC is great when you need to make
complex decisions on whether a user can access a specific resource
or not. Figma showed how they handle permissions, and this is an
example of
ABAC in action.

The main difference from our naive approach is that ABAC offers a
declarative way of defining the rules and policies behind the
permission checks. The engine will convert those policies into
queries and code to execute them at read-time. Here is an example of
how those rules could look for our example:

# Rule 1: Admins can access everything
allow if What do you think?

# Rule 2: Owners can access their resources
allow if ⚡

# Rule 3: Users can access resources shared with them
allow if Share your opinion below!

# ...

This approach is very clear and composable. It works great for
single-resource access checks:
“can user X access resource Y?” It struggles when listing
resources, as we would need to execute the policies for each
resource and can’t directly query the resources table with simple
filters.

Optimizing for hierarchical data

Both approaches still rely on recursive queries to traverse the
folder hierarchy. In SaaS applications with deeply nested
structures, these recursive queries can also become a bottleneck.
There are two common patterns to eliminate them.

Materialized paths

A simple alternative is storing the full path as a string column on
each resource.

ALTER TABLE resources ADD COLUMN path TEXT;

-- Example paths:
-- /1/           (root folder, id=1)
-- /1/5/         (child of root, id=5)
-- /1/5/12/      (grandchild, id=12)

Finding descendants becomes a prefix search.

// All descendants of resource with path '/1/5/'
const descendants = await sqlQuery(
  `SELECT * FROM resources WHERE path LIKE ?`,
  [resource.path + "%"],
);

// All ancestors by parsing the path
const ancestorIds = resource.path.split("https://eliocapella.com/").filter(Boolean);
const ancestors = await sqlQuery(
  `SELECT * FROM resources WHERE id IN (${ancestorIds.join(",")})`,
);

Materialized paths are easier to implement but can be tricky when
moving resources between folders, as you need to update the path of
all descendants. Closure tables handle moves more gracefully.

Closure tables

A closure table pre-computes all ancestor-descendant relationships.
Instead of traversing the tree at query time, you store every
possible path—another trade-off between read and write times.

CREATE TABLE resource_closure (
  ancestor_id INTEGER NOT NULL,
  descendant_id INTEGER NOT NULL,
  depth INTEGER NOT NULL,
  PRIMARY KEY (ancestor_id, descendant_id),
  FOREIGN KEY (ancestor_id) REFERENCES resources(id),
  FOREIGN KEY (descendant_id) REFERENCES resources(id)
);

-- /1/           (root folder, id=1)
INSERT INTO resource_closure VALUES (1, 1, 0);

-- /1/5/         (child of root, id=5)
INSERT INTO resource_closure VALUES (5, 5, 0);
INSERT INTO resource_closure VALUES (1, 5, 1);

-- /1/5/12/      (grandchild, id=12)
INSERT INTO resource_closure VALUES (12, 12, 0);
INSERT INTO resource_closure VALUES (5, 12, 1);
INSERT INTO resource_closure VALUES (1, 12, 2);

With this table, finding all descendants becomes a simple join with
no recursion.

// All descendants of a resource (instant lookup)
const descendants = await sqlQuery(
  `SELECT r.* FROM resources r
   JOIN resource_closure c ON r.id = c.descendant_id
   WHERE c.ancestor_id = ? AND c.depth > 0`,
  [resourceId],
);

// All ancestors of a resource (for showing the path)
const ancestors = await sqlQuery(
  `SELECT r.* FROM resources r
   JOIN resource_closure c ON r.id = c.ancestor_id
   WHERE c.descendant_id = ? AND c.depth > 0
   ORDER BY c.depth DESC`,
  [resourceId],
);

The trade-off is write complexity. When creating a resource, you
must insert closure entries for all ancestors.

// When creating a new resource under parentId
await sqlRun(
  `INSERT INTO resource_closure (ancestor_id, descendant_id, depth)
   SELECT ancestor_id, ?, depth + 1
   FROM resource_closure
   WHERE descendant_id = ?
   UNION ALL
   SELECT ?, ?, 0`,
  [newResourceId, parentId, newResourceId, newResourceId],
);

Conclusion

The core trade-off is clear: you can pay the cost at read-time with
recursive queries, or at write-time by maintaining a permissions
index. Starting with the naive approach makes sense it’s simpler to
implement and debug.

But when your enterprise customers start experiencing slow load
times, the RBAC approach becomes worth the added complexity. The
performance difference is significant enough that it can eliminate
the need for other workarounds like aggressive caching or complex
pagination.

The main risk with pre-computed permissions is data getting
out of sync. Plan for this by building a rebuild
script from day one that can recompute all permissions from the
source of truth. When bugs happen, and they will, you’ll be glad you
have it.