Why Data Access Needs To Be Fine-Grained

Applications frequently deal with data that should only be visible to specific users or groups. Whether motivated by privacy, security, or regulatory compliance, controlling access to sensitive information is a core requirement. This is especially true in multi-tenant architectures, where a single database serves many users or organizations. Handling this properly requires more than just securing the network or the application layer; it demands careful management at the data level.

The first step in any access control strategy is identifying the user making the request. This process is often summarized as *Auth*, but it encompasses two distinct operations:

  • Authentication: Verifying that a user is who they claim to be.
  • Authorization: Deciding what an authenticated user is allowed to do or see.

Authentication can exist without authorization, but authorization always presupposes authentication. The technical mechanisms for implementing authorization directly on your database records are often grouped under the term Row-Level Security (RLS).

Defining Security At The Row Level

In a database table, a "row" is a single record. For instance, in a posts table, each entry might look like this:

{
    "posts": [
        {
            "id": "article_23495044",
            "title": "User Data Management",
            "content": "<huge blob of text>",
            "publishedAt": "2023-03-28",
            "author": "author_2929292"
        },
        // ...
    ]
}

Each object within that array is a single row. Enforcing row-level security means ensuring that a user can only operate on the rows they are permitted to access.

While it is possible to filter data in application code, a more robust approach is to define these access relationships directly in your data schema. This ensures that any service connected to the database has the necessary information to enforce its own access logic. For the example above, the schema for the posts table would establish a link to the authors table:

{
    "posts": {
        "columns": [
            {
                "name": "id",
                "type": "string"
            },
            // ... other primitive types
            // establish relationship with "authors"
            {
                "name": "author",
                "type": "link",
                "link": "authors"
            }
        ]
    }
}

This defines a one-to-many relationship: one author can have many posts. Each post is tied to an author's id.

More complex scenarios, such as team-based access, require different structures. To grant access to a resource based on team membership, you might create a junction table. This table connects teams to tasks and teams to users, allowing for very granular, scoped access directly within the data layer.

Regardless of the relationship pattern, the fundamental requirement is that your application must be able to determine which user is making the current request to apply the appropriate filters.

Establishing The User's Identity

To enforce row-level security, you need a reliable and efficient authentication system. Re-authenticating a user on every single request is both costly and increases the attack surface. Instead, a session is created after the initial login, storing the user's credentials for the duration of their interaction with the app.

This session typically carries a userId that corresponds to the user's record in your database. Once this identity is established and trusted, the application can move on to the next step.

Connecting Identity To Data Access

With authentication in place, the authorization flow follows a clear pattern:

  1. Authentication: The provider validates the user, creates a session, and the app stores this as a payload.
  2. Resource request: An authenticated user requests a resource by resourceId, and the app retrieves the userId from the session.
  3. Granting access: The app filters all resources in the table to only those owned by the userId, then returns the matching record.
Mental model for access handling
(Large preview)

This mental model can be translated into any implementation. For instance, considering the earlier posts and authors schema, your data-fetching logic can include a filter to restrict results to the current user:

async function getPostsByAuthor(authorId: string) {
    return sdk.db.posts
        .filter({
            author: authorId
        })
        .getPaginated()
}

This is a simplified example of an RLS implementation, intended to illustrate the core concept which can be expanded upon for production systems.

A Note On Data Minimization

Defining clear access management is one part of the security picture. Another important principle is to store only the data you truly need and to grant the minimum amount of access required for your application to function. The less sensitive data you hold and transmit, the lower the risk of that data being exposed in the event of a breach or a leak.