Security: Row Level Security (RLS)

Relevant source files

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

• supabase/migrations/20250909120657_nameless_portal.sql
• supabase/migrations/20250909120559_floating_cake.sql
• CONFIGURACION_SUPABASE.md
• RESUMEN_FINAL.md
• FIXES-APLICADOS.md
• README.md
• VALIDACION-COMPLETA.md

Row Level Security (RLS) is a fundamental security mechanism within the ElmecV2 application, leveraging PostgreSQL's capabilities through the Supabase backend. It ensures that users can only access and manipulate data that they are explicitly permitted to see, providing a granular, row-by-row control layer directly within the database. This prevents unauthorized data access and ensures data segregation between different users and roles (clients, agents, admins).

The implementation has evolved to solve critical issues like infinite recursion, resulting in a robust and secure architecture. RLS is applied to both standard database tables (like users, requests, chat_rooms) and Supabase Storage objects, forming a comprehensive security perimeter for all application data.

Sources: README.md:31, VALIDACION-COMPLETA.md:86, supabase/migrations/20250909120657_nameless_portal.sql

RLS Architecture and Strategy

The core of the RLS strategy is to define policies on database tables that filter data based on the authenticated user's ID (auth.uid()) and their role. A significant challenge encountered was an "infinite recursion" error, which occurred when policies on the users table tried to query the same table to determine a user's role. This has been resolved by introducing a SECURITY DEFINER helper function.

Sources: supabase/migrations/20250909120559_floating_cake.sql:4-15, supabase/migrations/20250909120657_nameless_portal.sql:4-10

The Infinite Recursion Problem and Solution

The initial RLS implementation caused an infinite loop because policies on the users table required checking a user's role, which in turn required reading from the users table.

The diagram below illustrates the problematic recursive loop.

The solution was to create a SECURITY DEFINER helper function, is_admin_or_agent(), which runs with the permissions of the function owner, bypassing the RLS policy on the users table for its specific query. This breaks the recursion.

The corrected, non-recursive data access flow is shown below.

is_admin_or_agent() Helper Function

This PostgreSQL function is central to the corrected RLS architecture. It safely checks if the currently authenticated user has the role of 'admin' or 'agent'.

CREATE OR REPLACE FUNCTION public.is_admin_or_agent()
RETURNS boolean
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
  DECLARE
    user_role text;
  BEGIN
    SELECT rol INTO user_role FROM public.users WHERE id = auth.uid();
    RETURN user_role IN ('admin', 'agent');
  END;
$$;

Sources: supabase/migrations/20250909120657_nameless_portal.sql:13-24

Core Table Policies

After resolving the recursion issue, policies on core tables were simplified and updated to use the helper function where necessary.

Sources: supabase/migrations/20250909120559_floating_cake.sql, supabase/migrations/20250909120657_nameless_portal.sql

users Table Policies

To prevent recursion, the policies on the users table were simplified. Authenticated users can now read all user profiles, with granular access control enforced on other tables that contain more sensitive, user-specific data.

• Enable read access for own profile: Allows users to select their own user record.
  • USING (auth.uid() = id)
• Enable update for own profile: Allows users to update their own record.
  • USING (auth.uid() = id) WITH CHECK (auth.uid() = id)

Sources: supabase/migrations/20250909120559_floating_cake.sql:31-42, supabase/migrations/20250909120657_nameless_portal.sql:31-36

requests Table Policies

The requests table has role-based policies to control access to support and service requests.

Policy Name	Operation	Target Role	Condition
Users can manage own requests	ALL	authenticated	usuario_id = auth.uid()
Agents can manage assigned requests	ALL	authenticated	agente_id = auth.uid()
Admins can manage all requests	ALL	authenticated	public.is_admin_or_agent()

Sources: supabase/migrations/20250909120559_floating_cake.sql:56-69, supabase/migrations/20250909120657_nameless_portal.sql:43-60, FIXES-APLICADOS.md:154-159

chat_rooms and messages Policies

Access to chat is restricted to participants of a given chat room.

• chat_rooms: Users can access chat rooms where their auth.uid() is present in the participants array.
  • USING (auth.uid() = ANY(participants))
• messages: A helper function is_chat_participant() is used to check if a user can access messages within a specific room.
  • USING (is_chat_participant(chat_room_id, auth.uid()))

Sources: supabase/migrations/20250909120559_floating_cake.sql:75-81, 87-99

Supabase Storage Policies (request-files bucket)

RLS is also applied to file storage to control access to objects in the request-files bucket. This ensures that users can only upload files and manage the files they own.

The following diagram shows the sequence of a user uploading a file and the RLS checks involved.

The policies for the request-files bucket are summarized below.

Policy Name	Operation	Target Role	Condition
Public read access	SELECT	public	bucket_id = 'request-files'
Authenticated users can upload	INSERT	authenticated	bucket_id = 'request-files' AND auth.uid()::text = owner_id
Users can update own files	UPDATE	authenticated	bucket_id = 'request-files' AND auth.uid()::text = owner_id
Users can delete own files	DELETE	authenticated	bucket_id = 'request-files' AND auth.uid()::text = owner_id

Sources: CONFIGURACION_SUPABASE.md:32-121, RESUMEN_FINAL.md:83-98

Conclusion

The Row Level Security implementation in ElmecV2 is a critical and well-architected feature. It provides robust, multi-layered data protection by combining table-level policies, a SECURITY DEFINER function to avoid common pitfalls like recursion, and specific rules for file storage objects. This ensures that data is properly segregated and accessible only to authorized users, which is essential for the application's integrity and security. The system has been validated and is considered a strong point of the application's architecture.

Sources: VALIDACION-COMPLETA.md:86, FIXES-APLICADOS.md:152-159