Row Level Security (RLS)

Introduction

Row Level Security (RLS) is a core security component of the ElmecV2-Demo project, providing granular access control at the database level. Within the Supabase/PostgreSQL backend, RLS ensures that users can only access or modify data they are explicitly authorized to handle based on their identity and role. This mechanism prevents unauthorized data leaks and ensures that sensitive information—such as private requests, chat messages, and personal profiles—remains isolated between different users and roles.

Sources: README.md:16-17, supabase/migrations/20250114_add_rls_policies_requests.sql:7-10

The implementation relies on PostgreSQL policies that evaluate expressions against the auth.uid() of the authenticated session. It specifically addresses role-based access for three primary personas: Admins, Agents, and Clients. By enforcing these rules at the database level rather than just the application layer, the system maintains a robust security posture even if client-side checks are bypassed.

Sources: supabase/migrations/20250114_add_rls_policies_requests.sql:19-50, VALIDACION-COMPLETA.md:126-130

RLS Architecture and Logic

The RLS architecture in this project is designed to be non-recursive to avoid performance bottlenecks and "infinite recursion" errors. The logic is divided between standard table-level policies and helper functions that verify user privileges.

Access Control Logic Flow

The following diagram illustrates how the database evaluates a request based on RLS policies:

This flow ensures that data is filtered before it ever reaches the application layer.
Sources: supabase/migrations/20250909120657_nameless_portal.sql:1-15

Security Helper Functions

To prevent recursion errors and simplify policy management, the system utilizes "Security Definer" functions. These functions run with the privileges of the function creator, allowing them to query protected tables (like users) to determine roles without triggering recursive policy loops.

Sources: supabase/migrations/20250909120657_nameless_portal.sql:14-25, supabase/migrations/20250909120559_floating_cake.sql:180-192

Module Specific Policies

Requests Module

The requests table uses RLS to distinguish between those who created a request and those assigned to handle it.

• Select: Users see their own requests; Agents see assigned or unassigned 'new' requests; Admins see everything.
• Insert: Any authenticated user can create a request, provided the usuario_id matches their own auth.uid().
• Update: Limited to the creator (if unassigned) or the assigned agent/admin.

Sources: supabase/migrations/20250114_add_rls_policies_requests.sql:20-100

Chat and Messages Module

Security for the chat system is governed by participation arrays and helper functions.

Storage Security

RLS extends to Supabase Storage buckets, specifically the request-files bucket, to manage file uploads and downloads.

• Public Read: Anyone can view files in the request-files bucket via a SELECT policy.
• Authenticated Upload: Only logged-in users can INSERT files.
• Owner Management: UPDATE and DELETE operations are restricted to the file owner using the owner_id (formerly owner) field.

Sources: CONFIGURACION_SUPABASE.md:15-65, VALIDACION-COMPLETA.md:130

Technical Implementation Example

Standard implementation follows the pattern of dropping existing problematic policies before defining streamlined versions.

-- Example: Simplified Request Policy to prevent recursion
DROP POLICY IF EXISTS "Users can view own requests" ON requests;

CREATE POLICY "Users can manage own requests"
  ON requests
  FOR ALL
  TO authenticated
  USING (usuario_id = auth.uid())
  WITH CHECK (usuario_id = auth.uid());

Sources: supabase/migrations/20250909120559_floating_cake.sql:43-51

Conclusion

Row Level Security is the primary defense mechanism in the ElmecV2-Demo project, ensuring multi-tenancy and data privacy. By implementing simplified, non-recursive policies and utilizing security helper functions, the system achieves a secure environment that supports complex interactions between Clients, Agents, and Admins while maintaining high performance. This setup is verified to be 100% operational for MVP launch.

Sources: VALIDACION-COMPLETA.md:7-15, supabase/migrations/20250909120559_floating_cake.sql:1-15