67 lines
2.1 KiB
PL/PgSQL
67 lines
2.1 KiB
PL/PgSQL
-- SECURITY FIX: add_credits must only be callable by service_role (payment webhook)
|
|
-- or admin users. Regular authenticated users must not be able to grant themselves credits.
|
|
|
|
CREATE OR REPLACE FUNCTION add_credits(
|
|
p_provider_id UUID,
|
|
p_amount INTEGER,
|
|
p_description TEXT DEFAULT 'Credit purchase'
|
|
)
|
|
RETURNS JSON AS $$
|
|
DECLARE
|
|
v_current_balance INTEGER;
|
|
v_new_balance INTEGER;
|
|
v_transaction_id UUID;
|
|
BEGIN
|
|
-- SECURITY: Only service_role or admin may add credits
|
|
IF auth.role() != 'service_role' THEN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM profiles
|
|
WHERE id = auth.uid() AND role = 'admin'
|
|
) THEN
|
|
RAISE EXCEPTION 'Unauthorized: only service_role or admin can add credits';
|
|
END IF;
|
|
END IF;
|
|
|
|
-- Validate amount
|
|
IF p_amount <= 0 THEN
|
|
RAISE EXCEPTION 'Amount must be positive';
|
|
END IF;
|
|
|
|
-- Get or create wallet
|
|
INSERT INTO provider_wallets (provider_id, credit_balance)
|
|
VALUES (p_provider_id, 0)
|
|
ON CONFLICT (provider_id) DO NOTHING;
|
|
|
|
-- Get current balance with row lock
|
|
SELECT credit_balance INTO v_current_balance
|
|
FROM provider_wallets
|
|
WHERE provider_id = p_provider_id
|
|
FOR UPDATE;
|
|
|
|
-- Add credits
|
|
UPDATE provider_wallets
|
|
SET credit_balance = credit_balance + p_amount,
|
|
updated_at = NOW()
|
|
WHERE provider_id = p_provider_id
|
|
RETURNING credit_balance INTO v_new_balance;
|
|
|
|
-- Record transaction
|
|
INSERT INTO credit_transactions (
|
|
provider_id, amount, transaction_type, description, balance_after
|
|
) VALUES (
|
|
p_provider_id, p_amount, 'credit_purchase', p_description, v_new_balance
|
|
) RETURNING id INTO v_transaction_id;
|
|
|
|
RETURN json_build_object(
|
|
'success', true,
|
|
'transaction_id', v_transaction_id,
|
|
'new_balance', v_new_balance
|
|
);
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
-- Only service_role should call this directly; revoke from regular authenticated users
|
|
REVOKE EXECUTE ON FUNCTION add_credits(UUID, INTEGER, TEXT) FROM PUBLIC;
|
|
REVOKE EXECUTE ON FUNCTION add_credits(UUID, INTEGER, TEXT) FROM authenticated;
|
|
GRANT EXECUTE ON FUNCTION add_credits(UUID, INTEGER, TEXT) TO service_role;
|