Security & Row Level Security (RLS)

Relevant source files

The following files were used as context for generating this wiki page:

• SECURITY.md
• supabase/migrations/20260531155757_local_first_initial_schema.sql
• supabase/migrations/20260531160852_editorial_starter_layer.sql
• supabase/config.toml
• supabase/tests/rls/README.md
• AGENTS.md
• CONTRIBUTING.md

Kocteau implements a multi-layered security architecture primarily driven by Supabase Auth and PostgreSQL Row Level Security (RLS). The system is designed to protect user-generated content, taste profiles, and administrative editorial features by enforcing strict access control policies at the database level. Security sensitivity is high regarding the schema, RLS policies, RPCs, and storage buckets.

The core security philosophy emphasizes private reporting of vulnerabilities, strict management of secrets (especially service role keys and production credentials), and the use of server-side logic (RPCs and Server Routes) for write-sensitive flows.

Sources: SECURITY.md:1-25, AGENTS.md:75-80, CONTRIBUTING.md:105-115

Authentication & Authorization

Kocteau utilizes an OTP-first (One-Time Password) authentication flow via Supabase Auth. Users sign in using 6-digit email codes, and new users must complete profile onboarding before they are fully integrated into the system.

Role-Based Access Control (RBAC)

Beyond standard authentication, the system differentiates between public users, official accounts, and administrative staff through specific table columns and a dedicated roles table.

• Official Accounts: Controlled by profiles.is_official and profiles.official_label.
• Privileged Roles: Managed in the public.profile_roles table, which supports 'curator' and 'admin' roles.

The diagram shows the transition from authentication to internal role-based access control.

Sources: AGENTS.md:252-262, supabase/migrations/20260531160852_editorial_starter_layer.sql:19-24

Row Level Security (RLS) Policies

RLS is enabled on all tables in the public schema. Policies generally fall into three categories: Public Read, Authenticated Ownership (Read/Write), and Administrative/Editorial Access.

Core Data Policies

The following table summarizes the primary RLS policies for user-facing features:

Table	SELECT Policy	INSERT/UPDATE/DELETE Policy
profiles	Publicly readable (anon, authenticated).	Users can only insert/update their own profile (id = auth.uid()).
reviews	Publicly readable.	Restricted to author (author_id = auth.uid()).
review_likes	Restricted to user or review author.	Users can manage their own likes.
profile_roles	Users can only read their own roles.	No public write access (Restricted).
starter_tracks	Publicly readable if is_active.	Restricted to users with 'curator' or 'admin' roles via RPC.

Sources: supabase/migrations/20260531155757_local_first_initial_schema.sql:185-265, supabase/migrations/20260531160852_editorial_starter_layer.sql:103-145

Editorial & Starter Content Security

Access to editorial features, such as creating starter tracks or preference tags, is protected by a SECURITY DEFINER function called is_starter_curator(). This function checks the profile_roles table to verify if the authenticated user has the necessary permissions.

CREATE OR REPLACE FUNCTION public.is_starter_curator()
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
AS $$
  SELECT EXISTS (
    SELECT 1
    FROM public.profile_roles role
    WHERE role.user_id = auth.uid()
      AND role.role IN ('curator', 'admin')
  );
$$;

Sources: supabase/migrations/20260531160852_editorial_starter_layer.sql:147-160

Security of Database Functions (RPCs)

Many write operations are wrapped in PostgreSQL functions (RPCs) using SECURITY DEFINER. This allows the function to perform operations that the user might not have direct table-level permissions for (like updating counts), while internal logic enforces ownership and validation.

Example: Toggle Review Like

The toggle_review_like function manages the review_likes table and increments the likes_count on the reviews table. It explicitly verifies the auth.uid() and creates notifications using internal logic.

The sequence diagram illustrates how SECURITY DEFINER functions encapsulate complex logic and authorization checks.

Sources: supabase/migrations/20260531155757_local_first_initial_schema.sql:430-475

Storage & Bucket Security

Kocteau manages an avatars bucket for user profile images. Security is enforced through storage policies that restrict uploads and deletions to the owner of the folder (partitioned by auth.uid()).

• Public Access: The bucket is set to public: true, allowing anyone to view images via SELECT.
• Write Access: INSERT, UPDATE, and DELETE operations require authenticated status and a check ensuring the file path begins with the user's ID.

Sources: supabase/migrations/20260531155757_local_first_initial_schema.sql:318-360

Rate Limiting

The project implements a PostgreSQL-based rate limiting system to prevent abuse of sensitive endpoints. This is handled via the rate_limit_windows table and the check_rate_limit RPC.

• Logic: Uses a sliding window based on p_window_seconds.
• Scope: Allows limiting by specific identifiers (e.g., IP or User ID) and scopes.
• Constraints: Enforces request counts and returns reset timestamps to the client.

Sources: supabase/migrations/20260531155757_local_first_initial_schema.sql:128-135, supabase/migrations/20260531155757_local_first_initial_schema.sql:566-611

Secret Management

A strict policy exists regarding the handling of secrets within the repository:

1. Never Post: Service role keys, database passwords, SMTP credentials, or .env.local values must never be included in issues, PRs, or documentation.
2. Environment Variables: The frontend uses NEXT_PUBLIC_* for browser-safe keys (like the Supabase anon key), while privileged keys are stored in secure environment managers.
3. Local Development: Contributors are encouraged to use the local Supabase stack (pnpm supabase:start), which uses safe, local-only credentials.

Sources: SECURITY.md:27-40, CONTRIBUTING.md:40-55, supabase/config.toml:104-106

Summary

Security in Kocteau is primarily handled at the database level using Supabase RLS and SECURITY DEFINER functions. This "database-first" security model ensures that even if the client application is compromised, the data remains protected by PostgreSQL's internal policy engine. Access control is granular, ranging from public read access for reviews to restricted administrative roles for editorial curation.