How to fix 2F004: reading_sql_data_not_permitted in PostgreSQL

-- Check function definition and owner
SELECT
  proname,
  proowner::regrole as owner,
  prosecdef as security_definer
FROM pg_proc
WHERE proname = 'your_function_name';

-- Check table permissions for function owner
SELECT
  grantee,
  table_schema,
  table_name,
  privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'function_owner_role'
  AND table_name IN ('table1', 'table2');

-- Grant SELECT permissions if missing
GRANT SELECT ON table_name TO function_owner;
GRANT SELECT ON table_name TO executing_user;

-- Check if RLS is enabled
SELECT tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public'
  AND rowsecurity = true;

-- View existing RLS policies
SELECT
  schemaname,
  tablename,
  policyname,
  permissive,
  roles,
  cmd,
  qual
FROM pg_policies
WHERE tablename = 'your_table';

-- If needed, create policy to allow function access
-- Option 1: Grant access to function owner
CREATE POLICY function_read_policy ON your_table
  FOR SELECT
  TO function_owner_role
  USING (true);

-- Option 2: Bypass RLS for specific role
ALTER TABLE your_table ENABLE ROW LEVEL SECURITY;
ALTER TABLE your_table FORCE ROW LEVEL SECURITY;
GRANT BYPASSRLS ON your_table TO function_owner_role;

-- Option 3: Disable RLS if not needed
ALTER TABLE your_table DISABLE ROW LEVEL SECURITY;

-- Check if function uses SECURITY DEFINER
SELECT
  proname,
  prosecdef,
  proowner::regrole
FROM pg_proc
WHERE proname = 'your_function_name';

-- If SECURITY DEFINER is causing issues, switch to SECURITY INVOKER
CREATE OR REPLACE FUNCTION your_function(param text)
RETURNS text
LANGUAGE plpgsql
SECURITY INVOKER  -- Run with caller's privileges
AS $$
BEGIN
  RETURN (SELECT column FROM table WHERE condition = param);
END;
$$;

-- Or keep SECURITY DEFINER but grant owner proper permissions
-- First, ensure owner has SELECT on all tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO function_owner;

-- Make owner a member of roles with necessary privileges
GRANT data_reader_role TO function_owner;

-- Check function's search_path
SELECT
  proname,
  proconfig
FROM pg_proc
WHERE proname = 'your_function_name';

-- Set explicit search_path for function
ALTER FUNCTION your_function(param text)
SET search_path = public, pg_temp;

-- Or set search_path at function runtime
CREATE OR REPLACE FUNCTION your_function(param text)
RETURNS text
LANGUAGE plpgsql
AS $$
BEGIN
  -- Set search path explicitly
  SET LOCAL search_path = public, pg_temp;

  RETURN (SELECT column FROM table WHERE condition = param);
END;
$$;

-- Verify table exists in expected schema
SELECT
  table_schema,
  table_name
FROM information_schema.tables
WHERE table_name = 'your_table';

-- If function is used in a trigger, check trigger definition
SELECT
  trigger_name,
  event_manipulation,
  event_object_table,
  action_statement
FROM information_schema.triggers
WHERE action_statement LIKE '%your_function%';

-- For functions used in CHECK constraints (restricted context)
SELECT
  constraint_name,
  table_name,
  check_clause
FROM information_schema.check_constraints
WHERE check_clause LIKE '%your_function%';

-- Avoid calling functions that read data in CHECK constraints
-- Instead, use triggers for validation that requires data reads:
CREATE OR REPLACE FUNCTION validate_data()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM reference_table WHERE id = NEW.ref_id) THEN
    RAISE EXCEPTION 'Invalid reference';
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER validate_before_insert
  BEFORE INSERT OR UPDATE ON target_table
  FOR EACH ROW
  EXECUTE FUNCTION validate_data();

-- Check current role memberships
SELECT
  r.rolname as role_name,
  m.rolname as member_of
FROM pg_roles r
LEFT JOIN pg_auth_members am ON r.oid = am.member
LEFT JOIN pg_roles m ON am.roleid = m.oid
WHERE r.rolname = 'function_owner';

-- Grant necessary role membership
GRANT data_reader_role TO function_owner;

-- Or grant direct table access
GRANT SELECT ON ALL TABLES IN SCHEMA public TO function_owner;
GRANT USAGE ON SCHEMA public TO function_owner;

-- For dynamic schemas, grant default privileges
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO function_owner;

-- Verify permissions are effective
SET ROLE function_owner;
SELECT * FROM your_table LIMIT 1;  -- Test if access works
RESET ROLE;