> ## Documentation Index
> Fetch the complete documentation index at: https://docs.tuturuuu.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Row-Level Security (RLS) Policies

> Security model and RLS policy patterns in the Tuturuuu platform

The Tuturuuu platform implements comprehensive Row-Level Security (RLS) policies to enforce workspace-based multi-tenancy and granular permission controls.

## Security Model Overview

### Core Principles

1. **Workspace Isolation**: All workspace-scoped resources are isolated via RLS policies
2. **Permission-Based Access**: Actions require specific workspace role permissions
3. **User Ownership**: Users can only access resources they own or have permission to view
4. **Fail-Secure Default**: No access unless explicitly granted by policy

### Architecture

```
┌─────────────────────────────────────────┐
│         PostgreSQL Database             │
│  ┌───────────────────────────────────┐  │
│  │   RLS Policies (113+ policies)     │  │
│  ├───────────────────────────────────┤  │
│  │  1. Workspace Membership Check     │  │
│  │  2. Permission Verification        │  │
│  │  3. Ownership Validation           │  │
│  └───────────────────────────────────┘  │
└─────────────────────────────────────────┘
```

## Policy Patterns

### Pattern 1: Workspace Membership Check

**Use Case:** User must be a member of the workspace to access resources.

**Example:** `workspace_tasks` SELECT policy

```sql theme={null}
CREATE POLICY "Users can view tasks in their workspaces"
ON workspace_tasks
FOR SELECT
USING (
  ws_id IN (
    SELECT ws_id
    FROM workspace_members
    WHERE user_id = auth.uid()
  )
);
```

**Key Components:**

* `auth.uid()`: Gets the authenticated user's ID
* Subquery checks `workspace_members` table
* Returns only rows matching user's workspaces

### Pattern 2: Permission-Based Access

**Use Case:** User needs specific workspace permission to perform action.

**Example:** Task UPDATE policy

```sql theme={null}
CREATE POLICY "Users can update tasks with manage_projects permission"
ON workspace_tasks
FOR UPDATE
USING (
  ws_id IN (
    SELECT wm.ws_id
    FROM workspace_members wm
    JOIN workspace_role_permissions wrp
      ON wrp.ws_id = wm.ws_id
      AND wrp.role_id = wm.role
    WHERE wm.user_id = auth.uid()
      AND wrp.permission = 'manage_projects'
  )
);
```

**Permission Enum Values:**

Permissions come from the `workspace_role_permission` enum. The generated list
is the single source of truth — see the `workspace_role_permission` array in
`packages/types/src/supabase.ts` (regenerated by `bun sb:typegen`) rather than
hard-coding values, since the set evolves with new features. A representative
subset at the time of writing:

* `manage_workspace_settings`
* `manage_workspace_roles`
* `manage_workspace_members`
* `manage_workspace_security`
* `manage_workspace_audit_logs`
* `manage_projects`
* `manage_calendar`
* `manage_documents`
* `manage_drive`
* `manage_users`
* `manage_finance`
* `manage_inventory`
* `ai_chat`
* `ai_lab`

<Note>
  Task and project access is governed by `manage_projects`; there is no
  `manage_tasks` permission. Several values that older docs referenced
  (`manage_infrastructure_settings`, `manage_user_groups`, `manage_user_roles`,
  `ai_lab_assistant`, `view_disabled_users`, `disable_user`) are no longer part
  of the enum. Always confirm a permission string against the generated enum
  before using it in a policy — a typo or removed value will fail the
  `permission::workspace_role_permission` cast at runtime.
</Note>

### Pattern 3: User Ownership

**Use Case:** User can only access resources they created.

**Example:** `notes` policies

```sql theme={null}
-- SELECT
CREATE POLICY "Users can view their own notes"
ON notes
FOR SELECT
USING (created_by = auth.uid());

-- INSERT
CREATE POLICY "Users can create their own notes"
ON notes
FOR INSERT
WITH CHECK (created_by = auth.uid());

-- UPDATE
CREATE POLICY "Users can update their own notes"
ON notes
FOR UPDATE
USING (created_by = auth.uid());

-- DELETE
CREATE POLICY "Users can delete their own notes"
ON notes
FOR DELETE
USING (created_by = auth.uid());
```

### Pattern 4: Combined Workspace + Permission

**Use Case:** Workspace membership AND specific permission required.

**Example:** Calendar event management

```sql theme={null}
CREATE POLICY "Users can manage calendar events with permission"
ON workspace_calendar_events
FOR ALL
USING (
  ws_id IN (
    SELECT wm.ws_id
    FROM workspace_members wm
    JOIN workspace_role_permissions wrp
      ON wrp.ws_id = wm.ws_id
      AND wrp.role_id = wm.role
    WHERE wm.user_id = auth.uid()
      AND wrp.permission IN ('manage_calendar', 'manage_workspace_settings')
  )
);
```

### Pattern 5: Public Read, Restricted Write

**Use Case:** All workspace members can view, only privileged users can modify.

**Example:** Workspace settings

```sql theme={null}
-- Read: any workspace member
CREATE POLICY "Members can view workspace settings"
ON workspaces
FOR SELECT
USING (
  id IN (
    SELECT ws_id FROM workspace_members WHERE user_id = auth.uid()
  )
);

-- Write: requires permission
CREATE POLICY "Users can update workspace with permission"
ON workspaces
FOR UPDATE
USING (
  id IN (
    SELECT wm.ws_id
    FROM workspace_members wm
    JOIN workspace_role_permissions wrp
      ON wrp.ws_id = wm.ws_id AND wrp.role_id = wm.role
    WHERE wm.user_id = auth.uid()
      AND wrp.permission = 'manage_workspace_settings'
  )
);
```

### Pattern 6: Cross-Table Permission Inheritance

**Use Case:** Permission check depends on related table's policies.

**Example:** Task project members inherit from project permissions

```sql theme={null}
CREATE POLICY "Project members can view project tasks"
ON task_project_tasks
FOR SELECT
USING (
  project_id IN (
    SELECT id FROM task_projects
    -- task_projects has its own RLS policy checking workspace membership
  )
);
```

### Pattern 7: Platform User Profile Visibility

**Use Case:** Platform profile rows are useful for names and avatars, but the
Data API must not let any authenticated user enumerate every account.

`public.users` is not a globally readable directory. Authenticated callers can
read their own row and rows for users who share at least one workspace with
them. Private account fields such as email and full name belong in
`public.user_private_details`. Account state and preferences such as
`services`, `timezone`, `first_day_of_week`, `time_format`, and `deleted` also
belong there instead of on the public profile row.

`public.user_private_details` may be used for self-profile and shared-workspace
profile lookups, but the policy must correlate the target user to the current
requesting user. Do not use an uncorrelated `EXISTS` check that only proves the
target user belongs to some workspace.

Use a `SECURITY DEFINER` helper for the shared-workspace check so the profile
policy does not recurse through `workspace_members` RLS under the caller's
permissions.

Do not restore a policy equivalent to `USING (true)` on `public.users`. If a
feature needs a global user directory or public profile page, expose only the
intended columns through an application route or a purpose-built API contract
instead of widening the table policy.

```sql theme={null}
CREATE POLICY "Enable read access for current user and workspace members"
ON public.users
FOR SELECT
TO authenticated
USING (
  (select auth.uid()) is not null
  and (
    id = (select auth.uid())
    or public.current_user_shares_workspace_with(users.id)
  )
);
```

### Pattern 8: Client-Facing Public Views

**Use Case:** A view in the exposed `public` schema is granted to `anon` or
`authenticated`.

Postgres views are security definer by default when created by privileged
owners. In Supabase, that means a public view can bypass RLS on the underlying
tables even when every base table has RLS enabled. Any public-schema view that
is directly selectable by `anon` or `authenticated` must be created or altered
with `security_invoker = true`.

Do not use a public view as a shortcut around table policies. If a view needs
privileged data such as audit records or private profile details, revoke direct
client-role `SELECT` and expose the data through a permission-checked API route
or RPC instead.

```sql theme={null}
CREATE OR REPLACE VIEW public.workspace_safe_summary
WITH (security_invoker = true) AS
SELECT ...
FROM public.workspace_scoped_table;

REVOKE SELECT ON public.audit_logs FROM anon, authenticated;
```

New public views should be covered by `public-view-security.sql`, which fails
when a Data API-exposed view is missing `security_invoker`.

### Pattern 9: Workspace-Scoped Security Definer RPCs

**Use Case:** An RPC must run with elevated database privileges to read audit,
private, or RLS-protected tables.

Every workspace-scoped `SECURITY DEFINER` RPC that is reachable through the
Supabase Data API must enforce authorization inside the database function. Page,
route, or server action permission checks are not sufficient because clients can
call exposed RPCs directly.

Required controls:

* Read the caller with `auth.uid()` and deny missing callers unless the JWT role
  is `service_role`.
* Verify workspace membership and the workspace permission in the function
  before reading sensitive rows. Do not rely on permission helpers alone when a
  default permission could be enabled workspace-wide.
* For identity-linking flows, resolve candidate identity inside the function
  from the current verified `auth.users` row. Gate privileged lookups with the
  workspace feature config before returning candidate IDs, and do not accept
  caller-supplied emails or user-editable profile fields as proof of identity.
* Revoke direct execution from `public` and `anon`.
* Avoid exposing lower-level privileged helper functions to `authenticated` when
  they do not perform their own permission check.
* Cover the grant matrix and allowed/denied calls with pgTAP when practical.

```sql theme={null}
create or replace function public.authorize_workspace_sensitive_rpc(
  p_ws_id uuid
)
returns void
language plpgsql
security definer
set search_path = public
as $$
begin
  if auth.role() = 'service_role' then
    return;
  end if;

  if auth.uid() is null then
    raise exception 'auth_required' using errcode = '42501';
  end if;

  if not exists (
    select 1
    from public.workspace_members workspace_member
    where workspace_member.ws_id = p_ws_id
      and workspace_member.user_id = auth.uid()
  ) then
    raise exception 'permission_denied' using errcode = '42501';
  end if;

  if not public.has_workspace_permission(
    p_ws_id,
    auth.uid(),
    'manage_workspace_audit_logs'
  ) then
    raise exception 'permission_denied' using errcode = '42501';
  end if;
end;
$$;

revoke execute on function public.authorize_workspace_sensitive_rpc(uuid) from public;
revoke execute on function public.authorize_workspace_sensitive_rpc(uuid) from anon;
revoke execute on function public.authorize_workspace_sensitive_rpc(uuid) from authenticated;
```

### Pattern 10: API-Only Workspace Tables

**Use Case:** The `apps/web` API performs stricter permission checks than a
plain workspace membership policy can express safely, then reads or writes with
`createAdminClient()`.

For these tables, do not grant `anon` or `authenticated` direct Data API access
and do not leave member-only RLS policies in place. A browser or REST client can
call exposed Supabase tables directly with the publishable key, bypassing route
checks such as `manage_users` or `send_user_group_post_emails`.

Required controls:

* Revoke table privileges from `public`, `anon`, and `authenticated`.
* Keep table access behind service-role API routes that perform the workspace
  permission check first.
* Keep service-role grants explicit. The proxy route is the public contract; the
  table is not a browser, mobile, REST, or GraphQL contract.
* Drop permissive member-only RLS policies on the underlying tables; use
  service-role-only policies when an explicit policy is useful for review.
* Add the tables to `PROXY_ONLY_PUBLIC_TABLES` in
  `packages/supabase/src/next/protected-tables.ts` so deprecated direct clients
  fail loudly during app development.
* Revoke direct `EXECUTE` from exposed helper RPCs unless the function performs
  its own caller authorization.

The migration `20260522172925_harden_proxy_only_public_tables.sql` applies this
transition guardrail to the current `PROXY_ONLY_PUBLIC_TABLES` set and also
revokes automatic grants for future `public` tables, functions, and sequences.
New migrations that intentionally expose a public Data API surface must now add
the matching explicit `GRANT` statements in the same migration.

```sql theme={null}
revoke all privileges on table public.sensitive_workspace_table
from public, anon, authenticated;

drop policy if exists "Allow workspace members to manage sensitive workspace table"
  on public.sensitive_workspace_table;

create policy "Allow service role to manage sensitive workspace table"
  on public.sensitive_workspace_table
  for all
  to service_role
  using (true)
  with check (true);
```

### Pattern 11: Private Schema Server-Owned Tables

**Use Case:** A table, helper function, or protected query surface is only
needed by cron jobs, service-role routes, Tuturuuu API routes, or database
internals and should not be exposed through Supabase's generated REST API at
all.

Default new protected database access to the existing `private` schema unless
the data is intentionally public. Do not add `private` to the Supabase Data API
exposed schemas or extra search path. New migrations should prefer direct
server-owned access from `apps/web`: call private RPCs through the server-side
Supabase admin client with `schema('private').rpc(...)`, and use
`getPlatformSql()` only for private table access that is not modeled as an RPC.
This keeps browser and mobile consumers on centralized `apps/web` API routes
while avoiding new Supabase REST endpoints for private data.

Legacy `public` bridging RPCs may exist while older code is migrated, but do
not add new public RPCs for tables or protected helper queries that can be
reached through the server-side database connection.

If a public table has no app/package consumers and is not part of an active
database contract, prefer dropping it in a forward migration instead of moving
dead schema surface into `private`.

Notification delivery queue internals follow this pattern:
`private.notification_batches` and `private.notification_delivery_log` are
processed only by the server-owned batch cron, immediate-send route, and
service-role notification helper functions.

Required controls:

* Keep `anon` and `authenticated` without `USAGE` on `private`.
* Revoke direct table privileges from `public`, `anon`, and `authenticated`.
* Keep RLS enabled with service-role-only policies for reviewability.
* Keep private table reads and writes inside server-only modules used by
  `apps/web` routes, server actions, or server components.
* Cover private placement, table privileges, and schema usage with pgTAP tests.

```sql theme={null}
create schema if not exists private;

revoke all on schema private from public, anon, authenticated;
grant usage on schema private to service_role;

alter table if exists public.server_owned_table
  set schema private;

revoke all on table private.server_owned_table
from public, anon, authenticated;

alter table private.server_owned_table enable row level security;

create policy "Service role can manage server-owned table"
  on private.server_owned_table
  for all
  to service_role
  using (true)
  with check (true);
```

## Common RLS Helpers

### Get User Workspaces

```sql theme={null}
CREATE FUNCTION get_user_workspaces(user_uuid uuid)
RETURNS TABLE(ws_id text) AS $$
  SELECT ws_id
  FROM workspace_members
  WHERE user_id = user_uuid
    AND pending = false;
$$ LANGUAGE sql SECURITY DEFINER;
```

### Check Workspace Permission

The deployed helper is `public.has_workspace_permission(p_ws_id, p_user_id, p_permission)`.
The workspace id comes first, the user id second, and the permission is passed as
`text` (cast inside the function to `workspace_role_permission`). It checks both
role-based grants (`workspace_role_members` → `workspace_role_permissions`) and
workspace-wide default permissions (`workspace_default_permissions`), and only
counts rows where `enabled = true`. Match this signature when calling it from
policies or RPCs (see Pattern 9).

```sql theme={null}
-- Mirrors apps/database/.../add_has_workspace_permission.sql
CREATE OR REPLACE FUNCTION public.has_workspace_permission(
  p_ws_id uuid,
  p_user_id uuid,
  p_permission text
)
RETURNS boolean
LANGUAGE plpgsql
SECURITY DEFINER
STABLE
AS $$
BEGIN
  RETURN EXISTS (
    -- Role-based grant
    SELECT 1
    FROM workspace_role_members wrm
    JOIN workspace_role_permissions wrp
      ON wrp.role_id = wrm.role_id
      AND wrp.ws_id = p_ws_id
    WHERE wrm.user_id = p_user_id
      AND wrp.permission = p_permission::public.workspace_role_permission
      AND wrp.enabled = true

    UNION

    -- Workspace-wide default grant
    SELECT 1
    FROM workspace_default_permissions wdp
    WHERE wdp.ws_id = p_ws_id
      AND wdp.permission = p_permission::public.workspace_role_permission
      AND wdp.enabled = true
  );
END;
$$;
```

<Note>
  Call it as `public.has_workspace_permission(ws_id, auth.uid(), 'manage_projects')`.
  Argument order matters: `(p_ws_id, p_user_id, p_permission)`. Passing the user
  id and workspace id in the wrong order silently checks the wrong workspace.
</Note>

## Policy Testing

### Enable RLS (Always Required)

```sql theme={null}
ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;
```

### Test Policy as User

```sql theme={null}
-- Impersonate user for testing
SET request.jwt.claim.sub = 'user-uuid-here';

-- Run queries to verify policy
SELECT * FROM workspace_tasks WHERE ws_id = 'workspace-123';

-- Reset
RESET request.jwt.claim.sub;
```

### Verify Policy Coverage

```sql theme={null}
-- Check if all tables have RLS enabled
SELECT tablename
FROM pg_tables
WHERE schemaname = 'public'
  AND tablename NOT IN (
    SELECT tablename
    FROM pg_tables t
    JOIN pg_class c ON c.relname = t.tablename
    WHERE c.relrowsecurity = true
  );
```

## Security Best Practices

### 1. Always Enable RLS

```sql theme={null}
-- ✅ Good
CREATE TABLE my_table (...);
ALTER TABLE my_table ENABLE ROW LEVEL SECURITY;

-- ❌ Bad
CREATE TABLE my_table (...);
-- Forgot to enable RLS!
```

### 2. Use SECURITY DEFINER Carefully

Only use `SECURITY DEFINER` for helper functions that need elevated privileges:

```sql theme={null}
-- ✅ Good: Helper needs admin access
CREATE FUNCTION admin_helper() ... SECURITY DEFINER;

-- ❌ Bad: Bypasses RLS unnecessarily
CREATE FUNCTION simple_query() ... SECURITY DEFINER;
```

### 3. Test Policies Thoroughly

Always test:

* ✅ Users can access their own data
* ✅ Users cannot access other workspaces' data
* ✅ Permission checks work correctly
* ✅ Edge cases (pending invites, disabled users)

### 4. Avoid Policy Conflicts

```sql theme={null}
-- ❌ Bad: Multiple policies may conflict
CREATE POLICY "policy_1" ON table FOR SELECT USING (...);
CREATE POLICY "policy_2" ON table FOR SELECT USING (...);
-- Which one applies?

-- ✅ Good: One comprehensive policy per operation
CREATE POLICY "select_policy" ON table FOR SELECT USING (
  condition_1 OR condition_2
);
```

### 5. Performance Considerations

```sql theme={null}
-- ❌ Bad: Subquery runs for every row
CREATE POLICY "slow_policy" ON table FOR SELECT USING (
  column IN (SELECT id FROM expensive_query)
);

-- ✅ Good: Use indexed foreign keys
CREATE POLICY "fast_policy" ON table FOR SELECT USING (
  ws_id = (SELECT ws_id FROM workspace_members WHERE user_id = auth.uid() LIMIT 1)
);
```

## Common Policy Patterns by Table Type

### Workspace-Scoped Resources

```sql theme={null}
ALTER TABLE resource ENABLE ROW LEVEL SECURITY;

CREATE POLICY "workspace_members_can_view"
ON resource FOR SELECT
USING (ws_id IN (SELECT ws_id FROM workspace_members WHERE user_id = auth.uid()));

CREATE POLICY "permission_required_to_modify"
ON resource FOR ALL
USING (
  ws_id IN (
    SELECT wm.ws_id FROM workspace_members wm
    JOIN workspace_role_permissions wrp
      ON wrp.ws_id = wm.ws_id AND wrp.role_id = wm.role
    WHERE wm.user_id = auth.uid()
      AND wrp.permission = 'specific_permission'
  )
);
```

### User-Owned Resources

```sql theme={null}
ALTER TABLE user_resource ENABLE ROW LEVEL SECURITY;

CREATE POLICY "users_can_manage_own"
ON user_resource FOR ALL
USING (user_id = auth.uid());
```

### Public Read Resources

```sql theme={null}
ALTER TABLE public_resource ENABLE ROW LEVEL SECURITY;

CREATE POLICY "anyone_can_read"
ON public_resource FOR SELECT
USING (true);

CREATE POLICY "admin_can_write"
ON public_resource FOR ALL
USING (
  EXISTS (
    SELECT 1 FROM workspace_members wm
    JOIN workspace_role_permissions wrp
      ON wrp.ws_id = wm.ws_id AND wrp.role_id = wm.role
    WHERE wm.user_id = auth.uid()
      AND wrp.permission = 'manage_workspace_settings'
  )
);
```

## Debugging RLS Issues

### Check Active Policies

```sql theme={null}
SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual
FROM pg_policies
WHERE tablename = 'your_table_name';
```

### Test as Specific User

```sql theme={null}
-- In Supabase SQL Editor
SELECT set_config('request.jwt.claim.sub', 'user-uuid', false);
SELECT * FROM workspace_tasks; -- Runs with RLS as that user
```

### Bypass RLS for Admin Operations

Use `createAdminClient()` from `@tuturuuu/supabase` in server-side code:

```typescript theme={null}
import { createAdminClient } from '@tuturuuu/supabase/next/server';

const sbAdmin = createAdminClient(); // synchronous - do not await
// This client bypasses RLS - use carefully!
```

Always name `createAdminClient()` locals `sbAdmin`, not `supabase`, so service-role access is visually distinct from request-scoped clients during review.

## Migration Example

When adding a new table, always include RLS:

```sql theme={null}
-- Create table
CREATE TABLE new_feature (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ws_id text REFERENCES workspaces(id) ON DELETE CASCADE,
  name text NOT NULL,
  created_by uuid REFERENCES workspace_users(id),
  created_at timestamptz DEFAULT now()
);

-- Enable RLS
ALTER TABLE new_feature ENABLE ROW LEVEL SECURITY;

-- Add policies
CREATE POLICY "workspace_members_can_view"
ON new_feature FOR SELECT
USING (ws_id IN (
  SELECT ws_id FROM workspace_members WHERE user_id = auth.uid()
));

CREATE POLICY "permission_required_to_modify"
ON new_feature FOR ALL
USING (ws_id IN (
  SELECT wm.ws_id FROM workspace_members wm
  JOIN workspace_role_permissions wrp
    ON wrp.ws_id = wm.ws_id AND wrp.role_id = wm.role
  WHERE wm.user_id = auth.uid()
    AND wrp.permission = 'manage_specific_feature'
));

-- Add indexes for policy performance
CREATE INDEX idx_new_feature_ws_id ON new_feature(ws_id);
CREATE INDEX idx_new_feature_created_by ON new_feature(created_by);
```

## Privilege Grants vs RLS Scope

RLS policies only apply after SQL privileges have already allowed the role to
touch a table. For sensitive workspace configuration tables, avoid granting
`insert`, `update`, or `delete` to the broad `authenticated` role when the
matching RLS policies only check workspace membership.

Keep `authenticated` grants read-only unless the table policy itself enforces
the same granular permission that the application route requires. Route
privileged writes through permission-gated API handlers using elevated
service-role access.

## Related Documentation

* [Database Schema Overview](/reference/database/schema-overview)
* [Permission System](/platform/architecture/authorization)
* [Supabase Client Usage](/reference/packages/supabase)