73 lines
1.8 KiB
PL/PgSQL
73 lines
1.8 KiB
PL/PgSQL
-- SECURITY FIX: Use auth.uid() instead of caller-supplied p_admin_id
|
|
CREATE OR REPLACE FUNCTION admin_override_lead_price(
|
|
p_lead_id UUID,
|
|
p_override_price INTEGER
|
|
)
|
|
RETURNS JSON
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
AS $$
|
|
DECLARE
|
|
v_old_price INTEGER;
|
|
v_new_price INTEGER;
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin'
|
|
) THEN
|
|
RAISE EXCEPTION 'Unauthorized: admin access required';
|
|
END IF;
|
|
|
|
IF p_override_price IS NOT NULL AND p_override_price < 0 THEN
|
|
RAISE EXCEPTION 'Price cannot be negative';
|
|
END IF;
|
|
|
|
SELECT final_price INTO v_old_price FROM leads WHERE id = p_lead_id;
|
|
|
|
UPDATE leads
|
|
SET override_price = p_override_price,
|
|
final_price = COALESCE(p_override_price, calculated_price),
|
|
updated_at = NOW()
|
|
WHERE id = p_lead_id
|
|
RETURNING final_price INTO v_new_price;
|
|
|
|
RETURN json_build_object(
|
|
'success', true,
|
|
'old_price', v_old_price,
|
|
'new_price', v_new_price
|
|
);
|
|
END;
|
|
$$;
|
|
|
|
DROP FUNCTION IF EXISTS admin_override_lead_price(UUID, INTEGER, UUID);
|
|
|
|
GRANT EXECUTE ON FUNCTION admin_override_lead_price(UUID, INTEGER) TO authenticated;
|
|
|
|
-- Add admin function to change lead status manually
|
|
CREATE OR REPLACE FUNCTION admin_set_lead_status(
|
|
p_lead_id UUID,
|
|
p_status TEXT
|
|
)
|
|
RETURNS JSON
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
AS $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin'
|
|
) THEN
|
|
RAISE EXCEPTION 'Unauthorized';
|
|
END IF;
|
|
|
|
IF p_status NOT IN ('new', 'sold', 'expired', 'cancelled') THEN
|
|
RAISE EXCEPTION 'Invalid status';
|
|
END IF;
|
|
|
|
UPDATE leads SET status = p_status, updated_at = NOW()
|
|
WHERE id = p_lead_id;
|
|
|
|
RETURN json_build_object('success', true, 'status', p_status);
|
|
END;
|
|
$$;
|
|
|
|
GRANT EXECUTE ON FUNCTION admin_set_lead_status(UUID, TEXT) TO authenticated;
|