Row-Level Security (RLS) Policies

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

Workspace Isolation: All workspace-scoped resources are isolated via RLS policies
Permission-Based Access: Actions require specific workspace role permissions
User Ownership: Users can only access resources they own or have permission to view
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

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

CREATE POLICY "Users can update tasks with manage_tasks 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_tasks'
  )
);

Permission Enum Values:

manage_infrastructure_settings
manage_workspace_settings
manage_workspace_security
manage_users
manage_user_groups
manage_user_roles
manage_finance
manage_calendar
manage_documents
manage_inventory
ai_lab_assistant
view_disabled_users
disable_user

Pattern 3: User Ownership

Use Case: User can only access resources they created. Example: notes policies

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

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

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

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.

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.

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.

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.

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.

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

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

CREATE FUNCTION has_workspace_permission(
  user_uuid uuid,
  workspace_id text,
  required_permission workspace_role_permission
)
RETURNS boolean AS $$
  SELECT 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 = user_uuid
      AND wm.ws_id = workspace_id
      AND wrp.permission = required_permission
  );
$$ LANGUAGE sql SECURITY DEFINER;

Policy Testing

Enable RLS (Always Required)

ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

Test Policy as User

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

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

-- ✅ 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:

-- ✅ 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

-- ❌ 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

-- ❌ 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

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

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

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_infrastructure_settings'
  )
);

Debugging RLS Issues

Check Active Policies

SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual
FROM pg_policies
WHERE tablename = 'your_table_name';

Test as Specific User

-- 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:

import { createAdminClient } from '@tuturuuu/supabase/next/server';

const sbAdmin = await createAdminClient();
// 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:

-- 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.

​Security Model Overview

​Core Principles

​Architecture

​Policy Patterns

​Pattern 1: Workspace Membership Check

​Pattern 2: Permission-Based Access

​Pattern 3: User Ownership

​Pattern 4: Combined Workspace + Permission

​Pattern 5: Public Read, Restricted Write

​Pattern 6: Cross-Table Permission Inheritance

​Pattern 7: Platform User Profile Visibility

​Pattern 8: Client-Facing Public Views

​Pattern 9: Workspace-Scoped Security Definer RPCs

​Pattern 10: API-Only Workspace Tables

​Pattern 11: Private Schema Server-Owned Tables

​Common RLS Helpers

​Get User Workspaces

​Check Workspace Permission

​Policy Testing

​Enable RLS (Always Required)

​Test Policy as User

​Verify Policy Coverage

​Security Best Practices

​1. Always Enable RLS

​2. Use SECURITY DEFINER Carefully

​3. Test Policies Thoroughly

​4. Avoid Policy Conflicts

​5. Performance Considerations

​Common Policy Patterns by Table Type

​Workspace-Scoped Resources

​User-Owned Resources

​Public Read Resources

​Debugging RLS Issues

​Check Active Policies

​Test as Specific User

​Bypass RLS for Admin Operations

​Migration Example

​Privilege Grants vs RLS Scope

​Related Documentation