How to fix 38001: containing_sql_not_permitted in PostgreSQL

Examine the PostgreSQL error logs and stack trace to identify exactly which function, extension, or procedural handler is attempting the prohibited SQL. The error message typically includes the function name and context. You can also query pg_stat_activity to see recent statements and check pg_proc for function definitions that might be causing the issue.

Check the security attributes of the function causing the error. Use:

SELECT proname, prosecdef, prokind, prolang, prosrc 
FROM pg_proc 
WHERE proname = 'your_function_name';

Look for SECURITY DEFINER functions that might be attempting SQL in prohibited contexts. Also check function permissions with \df+ function_name in psql. Ensure functions have appropriate security contexts and aren't mixing security models in ways PostgreSQL prohibits.

Restructure the function to avoid executing SQL in prohibited contexts. Options include:
- Change from SECURITY DEFINER to SECURITY INVOKER if appropriate
- Move SQL operations to permitted contexts or wrapper functions
- Use different procedural languages that don't trigger the restriction
- Implement the logic differently to avoid the security violation

Example of changing security context:

CREATE OR REPLACE FUNCTION my_function() 
RETURNS void 
SECURITY INVOKER -- Changed from SECURITY DEFINER
LANGUAGE plpgsql 
AS $$
BEGIN
  -- SQL operations here
END;
$$;

After modifying the function, test it thoroughly to ensure it works without triggering error 38001. Verify that:
1. The function executes successfully in its intended context
2. No security violations occur
3. The function's behavior matches requirements
4. All SQL operations are now in permitted contexts

Use transaction blocks for safe testing:

BEGIN;
SELECT my_function();
-- Check for errors
ROLLBACK; -- Or COMMIT if successful

Check for similar patterns in other functions, extensions, or procedural code. Use:

SELECT proname, prosecdef 
FROM pg_proc 
WHERE prosecdef = true 
  AND prolang IN (SELECT oid FROM pg_language WHERE lanname IN ('plpgsql', 'plpythonu', etc.));

Update any functions with similar security context issues. Also review extension documentation for any known restrictions on SQL execution in certain contexts.