48 lines
1.5 KiB
PL/PgSQL
48 lines
1.5 KiB
PL/PgSQL
-- Allow admins to manage rate limit rules from the dashboard
|
|
ALTER TABLE rate_limit_rules ENABLE ROW LEVEL SECURITY;
|
|
|
|
DROP POLICY IF EXISTS "Admins can read rate limit rules" ON rate_limit_rules;
|
|
DROP POLICY IF EXISTS "Admins can update rate limit rules" ON rate_limit_rules;
|
|
|
|
CREATE POLICY "Public can read rate limit rules"
|
|
ON rate_limit_rules FOR SELECT
|
|
TO public USING (true);
|
|
|
|
CREATE POLICY "Admins can update rate limit rules"
|
|
ON rate_limit_rules FOR UPDATE
|
|
TO authenticated
|
|
USING (EXISTS (SELECT 1 FROM profiles WHERE id=auth.uid() AND role='admin'));
|
|
|
|
-- Allow admins to view rate limit logs (blocked requests)
|
|
ALTER TABLE rate_limit_logs ENABLE ROW LEVEL SECURITY;
|
|
|
|
DROP POLICY IF EXISTS "Admins can read rate limit logs" ON rate_limit_logs;
|
|
|
|
CREATE POLICY "Admins can read rate limit logs"
|
|
ON rate_limit_logs FOR SELECT
|
|
TO authenticated
|
|
USING (EXISTS (SELECT 1 FROM profiles WHERE id=auth.uid() AND role='admin'));
|
|
|
|
-- Function to clear rate limit logs for a specific user (unblock)
|
|
CREATE OR REPLACE FUNCTION admin_clear_rate_limit(p_user_id UUID)
|
|
RETURNS JSON
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
AS $$
|
|
DECLARE
|
|
v_deleted INTEGER;
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin'
|
|
) THEN
|
|
RAISE EXCEPTION 'Unauthorized';
|
|
END IF;
|
|
|
|
DELETE FROM rate_limit_logs WHERE user_id = p_user_id;
|
|
GET DIAGNOSTICS v_deleted = ROW_COUNT;
|
|
RETURN json_build_object('success', true, 'deleted', v_deleted);
|
|
END;
|
|
$$;
|
|
|
|
GRANT EXECUTE ON FUNCTION admin_clear_rate_limit(UUID) TO authenticated;
|