Hastebin

-- Fix search_path security warnings for functions by setting search_path = ''
ALTER FUNCTION set_modified_fields() SET search_path = '';
ALTER FUNCTION update_client_profile_on_password_set() SET search_path = '';
ALTER FUNCTION public.get_user_role() SET search_path = '';
ALTER FUNCTION get_clients_conditionally(uuid, timestamp with time zone, timestamp with time zone, integer) SET search_path = '';
ALTER FUNCTION public.notify_control_plane() SET search_path = '';

-- Tighten security to restrict default behavior and pg_net security

REVOKE ALL ON SCHEMA public FROM anon, authenticated;
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA public FROM anon, authenticated;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
REVOKE EXECUTE ON FUNCTIONS FROM anon, authenticated;

-- Create trigger on client_profiles INSERT
DROP TRIGGER IF EXISTS trigger_create_client_workbook ON public.client_profiles;

CREATE TRIGGER trigger_create_client_workbook
  AFTER INSERT ON public.client_profiles
  FOR EACH ROW
  EXECUTE FUNCTION create_client_workbook();

-- Grant execute permission on get_user_role function to authenticated users
GRANT EXECUTE ON FUNCTION public.get_user_role() TO authenticated;

-- Grant SELECT on staff_profiles and client_profiles so get_user_role() function can query them
-- The function runs as SECURITY DEFINER and needs these permissions
GRANT SELECT ON public.staff_profiles TO authenticated;
GRANT SELECT ON public.client_profiles TO authenticated;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO authenticated;

-- First, grant USAGE on the schema (this is what's missing!)
GRANT USAGE ON SCHEMA public TO anon, authenticated;

-- Grant EXECUTE on functions that should be accessible
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO authenticated;

-- Fix default privileges for future objects
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO authenticated;

ALTER DEFAULT PRIVILEGES IN SCHEMA public

ALTER FUNCTION set_modified_fields() SET search_path = 'public, auth';
ALTER FUNCTION update_client_profile_on_password_set() SET search_path = 'public, auth';
ALTER FUNCTION public.get_user_role() SET search_path = 'public, auth';
ALTER FUNCTION get_clients_conditionally(uuid, timestamp with time zone, timestamp with time zone, integer) SET search_path = 'public, auth';
ALTER FUNCTION public.notify_control_plane() SET search_path = 'public, auth';
GRANT EXECUTE ON FUNCTIONS TO authenticated;