postgresql (Supabase) : security definer set search_path = '' (Security Problem)

Securing PostgreSQL Functions in Supabase: Addressing Search Path Vulnerabilities

In the world of database management and application development, security is paramount. Recently, while working on a Supabase project utilizing PostgreSQL functions, I encountered a security warning that led us down a path of discovery and improvement. This blog post will walk you through our journey from identifying the problem to implementing a robust solution.

 

The Problem: "Search Path Mutable" Warning

My story begins with a seemingly innocuous PostgreSQL function in our Supabase project. Upon deployment, we were greeted with a warning from the Supabase advisor:

 

This cryptic message was our first indication that something wasn't quite right with our function's security posture. The function in question was designed to delete and insert data related to toxic tactics for a given subject. Here's a simplified version of our initial function:

CREATE OR REPLACE FUNCTION public.delete_and_insert_toxic_tactics_subject(p_subject_id TEXT, p_new_data JSONB)
RETURNS VOID
AS $$
-- Function body
$$ LANGUAGE plpgsql;

 

Understanding the Vulnerability

The "search_path mutable" warning points to a potential security vulnerability related to PostgreSQL's search path mechanism. In PostgreSQL, the search path determines the order in which schemas are searched when an unqualified object name is used.

This vulnerability arises because:

1. The search path can be changed by users during their session.
2. If the function doesn't explicitly set its search path, it inherits the search path of the calling user.
3. This inherited search path could potentially be manipulated to make the function access unintended objects or schemas

In my case, this was particularly concerning because our function was dealing with sensitive data related to toxic tactics. Any unintended access or manipulation could have serious consequences.

 

The Solution: SECURITY DEFINER and Empty Search Path

After careful consideration and testing, I arrived at a solution that effectively addresses this vulnerability. Here's my updated function definition : 

CREATE OR REPLACE FUNCTION public.delete_and_insert_toxic_tactics_subject(p_subject_id TEXT, p_new_data JSONB)
RETURNS VOID
SECURITY DEFINER
SET search_path = ''
AS $$
DECLARE
  uuid_subject_id UUID;
  inserted_count INT := 0;
  expected_count INT;
  current_tactic TEXT;
  log_messages TEXT[] := ARRAY[]::TEXT[];
  total_average FLOAT;
  tactic_scores JSONB;
BEGIN
  -- Function body with fully qualified object names
  -- For example:
  -- INSERT INTO public."red toxic tactic" (...)
END;
$$ LANGUAGE plpgsql;

 

Let's break down the critical components of this solution :

 

1. SECURITY DEFINER

The SECURITY DEFINER clause ensures that the function executes with the privileges of its owner (creator) rather than those of the calling user. This provides a consistent security context for the function's execution.

 

2. SET search_path = ''

This is the crux of my solution. By setting the search path to an empty string, we're effectively clearing the search path for this function. This has several important implications : 

 

• It forces all object references within the function to be fully qualified with their schema names.
• It prevents any possibility of search path-based attacks, as there's no search path to manipulate.
• It ensures predictable behavior regardless of the calling user's search path settings.

3. Fully Qualified Object Names

With an empty search path, it becomes crucial to use fully qualified names for all database objects within the function body. For example:

INSERT INTO public."red toxic tactic" (column1, column2) VALUES (...);