Spaces:

tarkovski
/

venorly-api

Runtime error

venorly-api / supabase_migration.sql

tarikmenguc

Update supabase_migration.sql

3125e5c 5 months ago

3.6 kB

-- ============================================================
-- Venorly — Supabase Güvenlik Migration
-- Çalıştırma: Supabase Dashboard > SQL Editor > New Query
-- ============================================================


-- ── 1. credits negatife düşemez constraint ──────────────────
-- Mevcut negatif değerleri önce 0'a çek (varsa)
UPDATE users SET credits = 0 WHERE credits < 0;

-- Constraint ekle
ALTER TABLE users
  ADD CONSTRAINT credits_non_negative
  CHECK (credits >= 0);

-- ── 2. consume_credit atomic fonksiyonu ─────────────────────
-- Race condition'ı önler: check + decrement tek transaction'da
-- Returns: TRUE = kredi düşüldü, FALSE = yetersiz kredi
CREATE OR REPLACE FUNCTION consume_credit(
    p_clerk_id TEXT,
    p_cost     INTEGER
)
RETURNS BOOLEAN
LANGUAGE plpgsql
SECURITY DEFINER   -- Fonksiyon sahibinin yetkisiyle çalışır
AS $$
DECLARE
    rows_affected INTEGER;
BEGIN
    UPDATE users
    SET    credits = credits - p_cost
    WHERE  clerk_id = p_clerk_id
      AND  credits  >= p_cost;

    GET DIAGNOSTICS rows_affected = ROW_COUNT;
    RETURN rows_affected > 0;
END;
$$;

-- Sadece authenticated kullanıcılar çağırabilsin
REVOKE ALL ON FUNCTION consume_credit FROM PUBLIC;
GRANT EXECUTE ON FUNCTION consume_credit TO authenticated;
GRANT EXECUTE ON FUNCTION consume_credit TO service_role;


-- ── 3. scans tablosuna user_id kolonu ───────────────────────
-- IDOR fix için gerekli: hangi scan kime ait?
-- Kolon yoksa ekle (varsa hata vermez)
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE  table_name  = 'scans'
          AND  column_name = 'user_id'
    ) THEN
        ALTER TABLE scans ADD COLUMN user_id TEXT;
        COMMENT ON COLUMN scans.user_id IS 'Clerk sub / Supabase user id — ownership için';
    END IF;
END $$;

-- Index ekle (ownership sorguları için)
CREATE INDEX IF NOT EXISTS idx_scans_user_id ON scans(user_id);


-- ── 4. Doğrulama sorguları ───────────────────────────────────
-- Migration'ı çalıştırdıktan sonra bu 3 sorguyu AYRI AYRI çalıştırıp
-- sonuçları kontrol edin (hepsini birden çalıştırmayın):

-- a) Constraint var mı? (PostgreSQL 12+ uyumlu)
SELECT conname,
       pg_get_constraintdef(oid) AS constraint_def
FROM   pg_constraint
WHERE  conrelid = 'users'::regclass
  AND  conname  = 'credits_non_negative';
-- Beklenen: 1 satır, constraint_def = "CHECK ((credits >= 0))"

-- b) Fonksiyon var mı?
SELECT proname,
       prosecdef,
       pg_get_functiondef(oid) AS func_def
FROM   pg_proc
WHERE  proname = 'consume_credit';
-- Beklenen: 1 satır, prosecdef = true

-- c) user_id kolonu var mı?
SELECT column_name, data_type, is_nullable
FROM   information_schema.columns
WHERE  table_name  = 'scans'
  AND  column_name = 'user_id';
-- Beklenen: 1 satır, data_type = 'text'


-- ── 5. Mevcut scan'lere user_id backfill ────────────────────
-- Eğer Supabase Auth kullanıyorsanız ve usage_logs'ta eşleşme
-- varsa aşağıdaki query ile backfill yapabilirsiniz:
-- (Opsiyonel — yeni scan'ler zaten user_id ile kaydedilecek)

-- UPDATE scans s
-- SET    user_id = ul.ip_address  -- ip_address clerk_id olarak kullanılıyorsa
-- FROM   usage_logs ul
-- WHERE  ul.action_type = s.mode
--   AND  s.user_id IS NULL
--   AND  ul.created_at::date = s.created_at::date;

	-- ============================================================
	-- Venorly — Supabase Güvenlik Migration
	-- Çalıştırma: Supabase Dashboard > SQL Editor > New Query
	-- ============================================================


	-- ── 1. credits negatife düşemez constraint ──────────────────
	-- Mevcut negatif değerleri önce 0'a çek (varsa)
	UPDATE users SET credits = 0 WHERE credits < 0;

	-- Constraint ekle
	ALTER TABLE users
	ADD CONSTRAINT credits_non_negative
	CHECK (credits >= 0);

	-- ── 2. consume_credit atomic fonksiyonu ─────────────────────
	-- Race condition'ı önler: check + decrement tek transaction'da
	-- Returns: TRUE = kredi düşüldü, FALSE = yetersiz kredi
	CREATE OR REPLACE FUNCTION consume_credit(
	p_clerk_id TEXT,
	p_cost INTEGER
	)
	RETURNS BOOLEAN
	LANGUAGE plpgsql
	SECURITY DEFINER -- Fonksiyon sahibinin yetkisiyle çalışır
	AS $$
	DECLARE
	rows_affected INTEGER;
	BEGIN
	UPDATE users
	SET credits = credits - p_cost
	WHERE clerk_id = p_clerk_id
	AND credits >= p_cost;

	GET DIAGNOSTICS rows_affected = ROW_COUNT;
	RETURN rows_affected > 0;
	END;
	$$;

	-- Sadece authenticated kullanıcılar çağırabilsin
	REVOKE ALL ON FUNCTION consume_credit FROM PUBLIC;
	GRANT EXECUTE ON FUNCTION consume_credit TO authenticated;
	GRANT EXECUTE ON FUNCTION consume_credit TO service_role;


	-- ── 3. scans tablosuna user_id kolonu ───────────────────────
	-- IDOR fix için gerekli: hangi scan kime ait?
	-- Kolon yoksa ekle (varsa hata vermez)
	DO $$
	BEGIN
	IF NOT EXISTS (
	SELECT 1 FROM information_schema.columns
	WHERE table_name = 'scans'
	AND column_name = 'user_id'
	) THEN
	ALTER TABLE scans ADD COLUMN user_id TEXT;
	COMMENT ON COLUMN scans.user_id IS 'Clerk sub / Supabase user id — ownership için';
	END IF;
	END $$;

	-- Index ekle (ownership sorguları için)
	CREATE INDEX IF NOT EXISTS idx_scans_user_id ON scans(user_id);


	-- ── 4. Doğrulama sorguları ───────────────────────────────────
	-- Migration'ı çalıştırdıktan sonra bu 3 sorguyu AYRI AYRI çalıştırıp
	-- sonuçları kontrol edin (hepsini birden çalıştırmayın):

	-- a) Constraint var mı? (PostgreSQL 12+ uyumlu)
	SELECT conname,
	pg_get_constraintdef(oid) AS constraint_def
	FROM pg_constraint
	WHERE conrelid = 'users'::regclass
	AND conname = 'credits_non_negative';
	-- Beklenen: 1 satır, constraint_def = "CHECK ((credits >= 0))"

	-- b) Fonksiyon var mı?
	SELECT proname,
	prosecdef,
	pg_get_functiondef(oid) AS func_def
	FROM pg_proc
	WHERE proname = 'consume_credit';
	-- Beklenen: 1 satır, prosecdef = true

	-- c) user_id kolonu var mı?
	SELECT column_name, data_type, is_nullable
	FROM information_schema.columns
	WHERE table_name = 'scans'
	AND column_name = 'user_id';
	-- Beklenen: 1 satır, data_type = 'text'


	-- ── 5. Mevcut scan'lere user_id backfill ────────────────────
	-- Eğer Supabase Auth kullanıyorsanız ve usage_logs'ta eşleşme
	-- varsa aşağıdaki query ile backfill yapabilirsiniz:
	-- (Opsiyonel — yeni scan'ler zaten user_id ile kaydedilecek)

	-- UPDATE scans s
	-- SET user_id = ul.ip_address -- ip_address clerk_id olarak kullanılıyorsa
	-- FROM usage_logs ul
	-- WHERE ul.action_type = s.mode
	-- AND s.user_id IS NULL
	-- AND ul.created_at::date = s.created_at::date;