55 lines
1.7 KiB
SQL
55 lines
1.7 KiB
SQL
-- Security hardening DDL for environments that already have the base tables.
|
|
-- Review duplicate rows before adding the unique constraint.
|
|
|
|
-- Find duplicate login identities before enforcing uniqueness.
|
|
SELECT
|
|
"provider",
|
|
"provider_user_id",
|
|
COUNT(*) AS duplicate_count
|
|
FROM "user_auth_identities"
|
|
WHERE "is_delete" IS NOT TRUE
|
|
GROUP BY "provider", "provider_user_id"
|
|
HAVING COUNT(*) > 1;
|
|
|
|
-- Enforce one active identity per provider account.
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "ux_user_auth_identities_provider_user_id_active"
|
|
ON "user_auth_identities" ("provider", "provider_user_id")
|
|
WHERE "is_delete" IS NOT TRUE;
|
|
|
|
-- Add missing recruit_posts constraints safely if the base DDL was applied before it became idempotent.
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_constraint WHERE conname = 'recruit_posts_user_id_fkey'
|
|
) THEN
|
|
ALTER TABLE "recruit_posts"
|
|
ADD CONSTRAINT "recruit_posts_user_id_fkey"
|
|
FOREIGN KEY ("user_id") REFERENCES "users" ("id");
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_constraint WHERE conname = 'recruit_posts_role_check'
|
|
) THEN
|
|
ALTER TABLE "recruit_posts"
|
|
ADD CONSTRAINT "recruit_posts_role_check"
|
|
CHECK ("role" IN ('기획', '아트', '프로그래머'));
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_constraint WHERE conname = 'recruit_posts_participation_type_check'
|
|
) THEN
|
|
ALTER TABLE "recruit_posts"
|
|
ADD CONSTRAINT "recruit_posts_participation_type_check"
|
|
CHECK ("participation_type" IN ('취미', '수익쉐어', '유급', '게임잼'));
|
|
END IF;
|
|
END
|
|
$$;
|