Supabase Skills Assessment

This is the most common and critical Supabase security mistake. When RLS is disabled on a table, the PostgREST API layer grants read access to anyone with the anon key (which is public and embedded in frontend code). Enabling RLS immediately blocks all access until explicit policies are created. The correct flow is: enable RLS with ALTER TABLE documents ENABLE ROW LEVEL SECURITY, then create specific policies like CREATE POLICY 'Users can read own documents' ON documents FOR SELECT USING (auth.uid() = owner_id). Regenerating the API key or changing schemas does not enforce row-level access control.

The USING clause in an RLS policy filters which rows a user can read - auth.uid() = user_id ensures each user only sees their own row. The WITH CHECK clause validates write operations - it prevents a user from updating the user_id to someone else's ID, which would otherwise transfer ownership. Using FOR ALL covers SELECT, INSERT, UPDATE, and DELETE with a single policy. Relying on application-layer validation (option D) is insecure because it can be bypassed by direct API calls with the anon key. USING (true) grants access to all rows for any authenticated user, which is not the intended behavior.

The data model described (organizations > projects > tasks > users) is inherently relational. In PostgreSQL (Supabase), this maps naturally to four tables with foreign keys and JOIN queries. A single SQL query can return tasks assigned to a user across all projects in all organizations they belong to. In Firestore (Firebase), the same hierarchy requires a deeply nested document structure or extensive denormalization, and querying across hierarchies requires multiple round-trips. RLS in Supabase also makes multi-tenant security enforcement straightforward by filtering at the database level. Firebase has strong B2B auth support, and pricing depends heavily on workload patterns.

Supabase Realtime taps into PostgreSQL's logical replication (the WAL - Write-Ahead Log) to stream INSERT, UPDATE, and DELETE events to subscribed clients over WebSockets in near-real-time. The client-side subscription uses the Supabase SDK: supabase.channel('messages').on('postgres_changes', {event: 'INSERT', schema: 'public', table: 'messages'}, callback).subscribe(). This eliminates polling overhead and delivers changes as they happen. Importantly, Realtime respects RLS policies - users only receive events for rows their RLS policies permit them to read.

Multiple RLS policies on the same table are additive in Supabase/PostgreSQL - if any policy grants access to a row, the user can see it. The correct approach is two SELECT policies: one that allows premium users to read all rows (using a custom JWT claim like auth.jwt()->>'user_role' = 'premium' or a lookup in a user_roles table), and one that allows free users to read only their own rows (auth.uid() = owner_id). Filtering in the application layer (option C) after fetching all documents is insecure because the anon key would expose all data before the application filters it.

True. Supabase is built on top of standard PostgreSQL and exposes its full capabilities. Developers can create stored procedures and functions (callable via the Supabase SDK's .rpc() method), define foreign keys and constraints, create indexes, use PostgreSQL extensions (including pgvector for vector similarity search), write triggers, and use all PostgreSQL data types. This is a key architectural advantage over Firebase's Firestore: the underlying database is standard SQL, meaning existing PostgreSQL knowledge transfers directly, and teams are not locked into a proprietary query language or data model.

RLS is enforced at the PostgreSQL database level for all connections using the anon or authenticated roles - this includes the Supabase SDK, PostgREST API, and any direct PostgreSQL connection using those roles. RLS can only be bypassed by connecting as a role with BYPASSRLS privilege (typically the service_role key or a superuser). The service_role key should never be exposed in client-side code. This database-level enforcement is actually a security advantage: it guarantees that security policies apply regardless of the access path.

Supabase Auth (built on GoTrue) provides email/password authentication with optional email confirmation, social OAuth login with dozens of providers (Google, GitHub, Apple, Discord, Spotify, and more), magic link passwordless authentication (a sign-in link sent to the user's email), as well as phone OTP and SAML SSO on paid plans. Biometric authentication (option C) is a device-level capability handled by the operating system and WebAuthn - it is not provided automatically by Supabase Auth, though developers can integrate WebAuthn themselves. Supabase Auth issues JWT tokens that integrate directly with PostgreSQL RLS policies.

Row-level multi-tenancy using tenant_id columns with RLS policies (option A) is the standard, scalable approach for most SaaS applications. It keeps all tenants in the same table with database-level isolation enforced by RLS. Storing tenant context in a user-linked table and joining in RLS policies (option D) is a robust alternative when tenant assignment is complex or role-based. Creating separate Supabase projects per tenant (option B) is expensive and operationally untenable at scale (each project is a separate PostgreSQL instance). Schema-per-tenant (option C) is a valid isolation model in self-hosted PostgreSQL but is complex to manage in Supabase and is not a common recommended pattern.

Row Level Security (RLS) is a native PostgreSQL feature that Supabase exposes prominently. When enabled on a table, every query against that table automatically appends the applicable RLS policy conditions as WHERE clause predicates, filtering results to only the rows the current user is permitted to see. This is transparent to the application - the database enforces it without application code checking permissions. RLS policies are written in standard SQL and can reference the current authenticated user via auth.uid() (Supabase-injected), PostgreSQL session variables, or subqueries against other tables.

The .rpc() method in the Supabase JavaScript SDK calls a PostgreSQL function using the supabase.rpc('function_name', { param1: value1 }) syntax. This is how complex server-side logic is exposed through Supabase's PostgREST API. Common uses include business logic too complex for RLS policies, aggregation functions, and database operations that need to run in a single transaction. Functions called via .rpc() run in the database with the calling user's role context, so RLS policies still apply unless the function is defined with SECURITY DEFINER.

Supabase is a suite of open-source tools assembled around PostgreSQL. PostgREST automatically generates a RESTful API directly from the PostgreSQL schema, enabling CRUD operations without writing any API code. GoTrue is the authentication service (originally from Netlify) that handles user signup, login, and JWT token issuance. Realtime uses PostgreSQL's logical replication to broadcast row-level changes over WebSocket connections. Storage provides S3-compatible file storage with RLS-style access policies.

Security setup must follow this sequence: tables define the data structure including the columns used in RLS (user_id, tenant_id). RLS must be enabled on tables before policies have any effect. Policies define the access rules per operation. The service_role key (which bypasses RLS) must be stored server-side only - never in frontend code. Testing as an anonymous user verifies that RLS is blocking unauthorized access as intended. Testing as an authenticated user verifies that the policies correctly allow the expected rows and block others. Skipping any step leaves security gaps.

Supabase runs standard PostgreSQL, meaning all standard migration tools work without modification. A typical migration uses pg_dump to export the source database schema and data, then pg_restore or psql to load it into Supabase's PostgreSQL instance. The connection string (available in the Supabase dashboard under Settings > Database) is used just like any other PostgreSQL connection. After migration, developers add RLS policies and Supabase Auth integration. The existing schema, queries, indexes, stored procedures, and extensions transfer directly - no redesign is required.

auth.uid() is the Supabase-provided PostgreSQL function that extracts the user's UUID from the JWT token attached to the current database session. It is the foundation of almost every user-scoped RLS policy: USING (auth.uid() = user_id) restricts a table to the rows owned by the current user. When no user is authenticated (anon key), auth.uid() returns NULL. This function is injected by Supabase's connection pooler and is not a standard PostgreSQL function - it is specific to Supabase's authentication integration. auth.jwt() provides access to the full JWT payload for custom claims.