bknd-row-level-security

What this skill does

# Row-Level Security (RLS)

Implement data-level access control using filter policies to restrict which records users can access.

## Prerequisites

- Bknd project with code-first configuration
- Auth enabled (`auth: { enabled: true }`)
- Guard enabled (`guard: { enabled: true }`)
- At least one role defined (see **bknd-create-role**)
- Entity with ownership field (e.g., `user_id`)

## When to Use UI Mode

- Viewing current role policies
- Quick policy inspection

**UI steps:** Admin Panel > Auth > Roles > Select role

**Note:** RLS configuration requires code mode. UI is read-only.

## When to Use Code Mode

- Implementing row-level security
- Creating filter policies
- Entity-specific data isolation
- Multi-tenant patterns

## Code Approach

### Step 1: Add Ownership Field to Entity

Ensure entity has a field to track ownership:

```typescript
import { serve } from "bknd/adapter/bun";
import { em, entity, text, number } from "bknd";

const schema = em({
  posts: entity("posts", {
    title: text().required(),
    content: text(),
    user_id: number().required(),  // Ownership field
  }),
});
```

### Step 2: Basic RLS - Own Records Only

Users can only read their own records:

```typescript
serve({
  connection: { url: "file:data.db" },
  config: {
    data: schema.toJSON(),
    auth: {
      enabled: true,
      guard: { enabled: true },
      roles: {
        user: {
          implicit_allow: false,
          permissions: [
            {
              permission: "data.entity.read",
              effect: "allow",
              policies: [
                {
                  description: "Users read own records only",
                  effect: "filter",
                  filter: { user_id: "@user.id" },
                },
              ],
            },
          ],
        },
      },
    },
  },
});
```

### How Filter Policies Work

| Component | Purpose |
|-----------|---------|
| `effect: "filter"` | Apply row-level filtering (not allow/deny) |
| `filter` | Query conditions added to every request |
| `@user.id` | Variable replaced with current user's ID |

When user with ID 5 queries posts, the filter transforms:
```typescript
// User's query
api.data.readMany("posts", { where: { status: "published" } });

// Becomes (with RLS filter applied)
api.data.readMany("posts", { where: { status: "published", user_id: 5 } });
```

### Step 3: Full CRUD with RLS

Apply RLS to all operations:

```typescript
{
  roles: {
    user: {
      implicit_allow: false,
      permissions: [
        // Read: own records
        {
          permission: "data.entity.read",
          effect: "allow",
          policies: [{
            effect: "filter",
            filter: { user_id: "@user.id" },
          }],
        },
        // Create: allowed (user_id set via hook/plugin)
        { permission: "data.entity.create", effect: "allow" },
        // Update: own records
        {
          permission: "data.entity.update",
          effect: "allow",
          policies: [{
            effect: "filter",
            filter: { user_id: "@user.id" },
          }],
        },
        // Delete: own records
        {
          permission: "data.entity.delete",
          effect: "allow",
          policies: [{
            effect: "filter",
            filter: { user_id: "@user.id" },
          }],
        },
      ],
    },
  },
}
```

### Step 4: Entity-Specific RLS

Different RLS rules per entity:

```typescript
{
  roles: {
    user: {
      implicit_allow: false,
      permissions: [
        {
          permission: "data.entity.read",
          effect: "allow",
          policies: [
            // Posts: filter by author
            {
              condition: { entity: "posts" },
              effect: "filter",
              filter: { author_id: "@user.id" },
            },
            // Comments: filter by user
            {
              condition: { entity: "comments" },
              effect: "filter",
              filter: { user_id: "@user.id" },
            },
            // Categories: no filter (public)
            {
              condition: { entity: "categories" },
              effect: "allow",
            },
          ],
        },
      ],
    },
  },
}
```

### Step 5: Public + Private Records

Users see public records AND their own private records:

```typescript
{
  permissions: [
    {
      permission: "data.entity.read",
      effect: "allow",
      policies: [
        {
          condition: { entity: "posts" },
          effect: "filter",
          filter: {
            $or: [
              { is_public: true },      // Public posts
              { user_id: "@user.id" },  // Own posts
            ],
          },
        },
      ],
    },
  ],
}
```

### Step 6: Draft/Published Pattern

Authors see their drafts, everyone sees published:

```typescript
{
  roles: {
    author: {
      permissions: [
        {
          permission: "data.entity.read",
          effect: "allow",
          policies: [
            {
              condition: { entity: "posts" },
              effect: "filter",
              filter: {
                $or: [
                  { status: "published" },  // Anyone can read published
                  { author_id: "@user.id" }, // Author reads own drafts
                ],
              },
            },
          ],
        },
      ],
    },
    viewer: {
      is_default: true,
      permissions: [
        {
          permission: "data.entity.read",
          effect: "allow",
          policies: [
            {
              condition: { entity: "posts" },
              effect: "filter",
              filter: { status: "published" },  // Only published
            },
          ],
        },
      ],
    },
  },
}
```

## Common RLS Patterns

### Multi-Tenant Isolation

Isolate data by organization/tenant:

```typescript
const schema = em({
  organizations: entity("organizations", {
    name: text().required(),
  }),
  projects: entity("projects", {
    name: text().required(),
    org_id: number().required(),
  }),
  tasks: entity("tasks", {
    title: text().required(),
    org_id: number().required(),
  }),
});

// Assuming user has org_id field
{
  roles: {
    member: {
      permissions: [
        {
          permission: "data.entity.read",
          effect: "allow",
          policies: [
            {
              condition: { entity: { $in: ["projects", "tasks"] } },
              effect: "filter",
              filter: { org_id: "@user.org_id" },
            },
          ],
        },
        {
          permission: "data.entity.create",
          effect: "allow",
          policies: [
            {
              condition: { entity: { $in: ["projects", "tasks"] } },
              effect: "allow",
            },
          ],
        },
      ],
    },
  },
}
```

### Team-Based Access

Users access records belonging to their team:

```typescript
// Assuming user has team_id field
{
  roles: {
    team_member: {
      permissions: [
        {
          permission: "data.entity.read",
          effect: "allow",
          policies: [{
            effect: "filter",
            filter: { team_id: "@user.team_id" },
          }],
        },
        {
          permission: "data.entity.update",
          effect: "allow",
          policies: [{
            effect: "filter",
            filter: { team_id: "@user.team_id" },
          }],
        },
      ],
    },
  },
}
```

### Hierarchical Access (Manager Pattern)

Manager sees their reports' data:

```typescript
// Manager sees records where:
// - They own the record, OR
// - Record belongs to someone they manage
// Note: This pattern may require custom logic via hooks
{
  roles: {
    manager: {
      permissions: [
        {
          permission: "data.entity.read",
          effect: "allow",
          policies: [{
            effect: "filter",
            filter: {
              $or: [
                { user_id: "@user.id" },
                { manager_id: "@user.id"