security: harden database functions and policies

- Add SET search_path TO '' on 4 SECURITY DEFINER functions
  (update_feed_url, check_subscription_limit, check_folder_limit,
  check_muted_keyword_limit) to prevent search path injection
- Add DELETE policy to api_keys table for GDPR compliance
- Escape ILIKE/LIKE wildcards (%, _, \) in search_entries and
  get_custom_feed_timeline RPCs to prevent wildcard injection
- Fix get_custom_feed_timeline missing developer tier in retention
  check (was only checking 'pro', developer users got 14-day limit)
- Fully qualify table references in update_feed_url

1 files changed, 31 insertions, 15 deletions

diff --git a/supabase/schema.sql b/supabase/schema.sql
index 1db41f9..147dd8a 100644
--- a/supabase/schema.sql
+++ b/supabase/schema.sql
@@ -305,6 +305,7 @@ ALTER TABLE public.user_profiles ENABLE ROW LEVEL SECURITY;
 CREATE POLICY "Users can view own API keys" ON public.api_keys AS PERMISSIVE FOR SELECT USING ((auth.uid() = user_id));
 CREATE POLICY "Users can insert own API keys" ON public.api_keys AS PERMISSIVE FOR INSERT WITH CHECK ((auth.uid() = user_id));
 CREATE POLICY "Users can update own API keys" ON public.api_keys AS PERMISSIVE FOR UPDATE USING ((auth.uid() = user_id));
+CREATE POLICY "Users can delete own API keys" ON public.api_keys AS PERMISSIVE FOR DELETE USING ((auth.uid() = user_id));
 
 -- custom_feeds
 CREATE POLICY "users can view own custom feeds" ON public.custom_feeds AS PERMISSIVE FOR SELECT USING ((user_id = (SELECT auth.uid())));
@@ -534,17 +535,18 @@ CREATE OR REPLACE FUNCTION public.update_feed_url(target_feed_id uuid, new_url t
  RETURNS void
  LANGUAGE plpgsql
  SECURITY DEFINER
+ SET search_path TO ''
 AS $function$
 BEGIN
   IF NOT EXISTS (
-    SELECT 1 FROM subscriptions
+    SELECT 1 FROM public.subscriptions
     WHERE feed_id = target_feed_id
     AND user_id = auth.uid()
   ) THEN
     RAISE EXCEPTION 'not subscribed to this feed';
   END IF;
 
-  UPDATE feeds SET url = new_url WHERE id = target_feed_id;
+  UPDATE public.feeds SET url = new_url WHERE id = target_feed_id;
 END;
 $function$;
 
@@ -639,6 +641,7 @@ DECLARE
   v_source_folder_id UUID;
   v_keywords TEXT[];
   v_user_id UUID := auth.uid();
+  v_user_tier public.subscription_tier;
 BEGIN
   SELECT cf.query, cf.match_mode, cf.source_folder_id
   INTO v_query, v_match_mode, v_source_folder_id
@@ -649,6 +652,8 @@ BEGIN
     RETURN;
   END IF;
 
+  SELECT tier INTO v_user_tier FROM public.user_profiles WHERE id = v_user_id;
+
   v_keywords := string_to_array(lower(v_query), ' ');
 
   RETURN QUERY
@@ -680,11 +685,13 @@ BEGIN
     AND (
       CASE WHEN v_match_mode = 'and' THEN
         (SELECT bool_and(
-          lower(COALESCE(e.title, '') || ' ' || COALESCE(e.summary, '')) LIKE '%' || kw || '%'
+          lower(COALESCE(e.title, '') || ' ' || COALESCE(e.summary, ''))
+            LIKE '%' || replace(replace(replace(kw, '\', '\\'), '%', '\%'), '_', '\_') || '%' ESCAPE '\'
         ) FROM unnest(v_keywords) AS kw)
       ELSE
         (SELECT bool_or(
-          lower(COALESCE(e.title, '') || ' ' || COALESCE(e.summary, '')) LIKE '%' || kw || '%'
+          lower(COALESCE(e.title, '') || ' ' || COALESCE(e.summary, ''))
+            LIKE '%' || replace(replace(replace(kw, '\', '\\'), '%', '\%'), '_', '\_') || '%' ESCAPE '\'
         ) FROM unnest(v_keywords) AS kw)
       END
     )
@@ -692,12 +699,12 @@ BEGIN
       SELECT 1 FROM public.muted_keywords mk
       WHERE mk.user_id = v_user_id
       AND (
-        e.title ILIKE '%' || mk.keyword || '%'
-        OR e.summary ILIKE '%' || mk.keyword || '%'
+        e.title ILIKE '%' || replace(replace(replace(mk.keyword, '\', '\\'), '%', '\%'), '_', '\_') || '%' ESCAPE '\'
+        OR e.summary ILIKE '%' || replace(replace(replace(mk.keyword, '\', '\\'), '%', '\%'), '_', '\_') || '%' ESCAPE '\'
       )
     )
     AND (
-      (SELECT tier FROM public.user_profiles WHERE id = v_user_id) = 'pro'
+      v_user_tier IN ('pro', 'developer')
       OR e.published_at >= now() - interval '14 days'
     )
   ORDER BY e.published_at DESC NULLS LAST
@@ -766,7 +773,7 @@ CREATE OR REPLACE FUNCTION public.search_entries(p_query text, p_result_limit in
  RETURNS TABLE(entry_id uuid, feed_id uuid, feed_title text, custom_title text, entry_title text, entry_url text, author text, summary text, image_url text, published_at timestamp with time zone, is_read boolean, is_saved boolean)
  LANGUAGE sql
  STABLE SECURITY DEFINER
- SET search_path TO 'public'
+ SET search_path TO ''
 AS $function$
   SELECT
     e.id AS entry_id,
@@ -781,13 +788,19 @@ AS $function$
     e.published_at,
     COALESCE(ues.read, false) AS is_read,
     COALESCE(ues.saved, false) AS is_saved
-  FROM entries e
-  INNER JOIN subscriptions s ON s.feed_id = e.feed_id AND s.user_id = auth.uid()
-  INNER JOIN feeds f ON f.id = e.feed_id
-  LEFT JOIN user_entry_states ues ON ues.entry_id = e.id AND ues.user_id = auth.uid()
-  WHERE e.title ILIKE '%' || p_query || '%'
-     OR e.summary ILIKE '%' || p_query || '%'
-     OR e.author ILIKE '%' || p_query || '%'
+  FROM public.entries e
+  INNER JOIN public.subscriptions s ON s.feed_id = e.feed_id AND s.user_id = auth.uid()
+  INNER JOIN public.feeds f ON f.id = e.feed_id
+  LEFT JOIN public.user_entry_states ues ON ues.entry_id = e.id AND ues.user_id = auth.uid()
+  WHERE (
+    e.title ILIKE '%' || replace(replace(replace(p_query, '\', '\\'), '%', '\%'), '_', '\_') || '%' ESCAPE '\'
+    OR e.summary ILIKE '%' || replace(replace(replace(p_query, '\', '\\'), '%', '\%'), '_', '\_') || '%' ESCAPE '\'
+    OR e.author ILIKE '%' || replace(replace(replace(p_query, '\', '\\'), '%', '\%'), '_', '\_') || '%' ESCAPE '\'
+  )
+  AND (
+    (SELECT tier FROM public.user_profiles WHERE id = auth.uid()) IN ('pro', 'developer')
+    OR e.published_at >= now() - interval '14 days'
+  )
   ORDER BY e.published_at DESC
   LIMIT p_result_limit;
 $function$;
@@ -831,6 +844,7 @@ CREATE OR REPLACE FUNCTION public.check_subscription_limit()
  RETURNS trigger
  LANGUAGE plpgsql
  SECURITY DEFINER
+ SET search_path TO ''
 AS $function$
 declare
   current_tier public.subscription_tier;
@@ -867,6 +881,7 @@ CREATE OR REPLACE FUNCTION public.check_folder_limit()
  RETURNS trigger
  LANGUAGE plpgsql
  SECURITY DEFINER
+ SET search_path TO ''
 AS $function$
 declare
   current_tier public.subscription_tier;
@@ -892,6 +907,7 @@ CREATE OR REPLACE FUNCTION public.check_muted_keyword_limit()
  RETURNS trigger
  LANGUAGE plpgsql
  SECURITY DEFINER
+ SET search_path TO ''
 AS $function$
 declare
   current_tier public.subscription_tier;