File size: 3,812 Bytes
ad68e43 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 | -- Apply this migration after database/schema.sql
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1
FROM pg_policies
WHERE schemaname = 'public'
AND tablename = 'profiles'
AND policyname = 'Users can read own profile'
) THEN
CREATE POLICY "Users can read own profile" ON public.profiles
FOR SELECT
USING (auth.uid() = id);
END IF;
IF NOT EXISTS (
SELECT 1
FROM pg_policies
WHERE schemaname = 'public'
AND tablename = 'profiles'
AND policyname = 'Users can insert own profile'
) THEN
CREATE POLICY "Users can insert own profile" ON public.profiles
FOR INSERT TO authenticated
WITH CHECK (
auth.uid() = id
AND COALESCE(role, 'user') = 'user'
);
END IF;
IF NOT EXISTS (
SELECT 1
FROM pg_policies
WHERE schemaname = 'public'
AND tablename = 'profiles'
AND policyname = 'Users can update own profile'
) THEN
CREATE POLICY "Users can update own profile" ON public.profiles
FOR UPDATE TO authenticated
USING (auth.uid() = id)
WITH CHECK (
auth.uid() = id
AND role = (
SELECT p.role
FROM public.profiles p
WHERE p.id = auth.uid()
)
);
END IF;
IF NOT EXISTS (
SELECT 1
FROM pg_policies
WHERE schemaname = 'public'
AND tablename = 'profiles'
AND policyname = 'Admins can read all profiles'
) THEN
CREATE POLICY "Admins can read all profiles" ON public.profiles
FOR SELECT
USING (
EXISTS (
SELECT 1
FROM public.profiles admin_profile
WHERE admin_profile.id = auth.uid()
AND admin_profile.role = 'admin'
)
);
END IF;
IF NOT EXISTS (
SELECT 1
FROM pg_policies
WHERE schemaname = 'public'
AND tablename = 'profiles'
AND policyname = 'Admins can update all profiles'
) THEN
CREATE POLICY "Admins can update all profiles" ON public.profiles
FOR UPDATE TO authenticated
USING (
EXISTS (
SELECT 1
FROM public.profiles admin_profile
WHERE admin_profile.id = auth.uid()
AND admin_profile.role = 'admin'
)
)
WITH CHECK (
role IN ('user', 'admin')
AND EXISTS (
SELECT 1
FROM public.profiles admin_profile
WHERE admin_profile.id = auth.uid()
AND admin_profile.role = 'admin'
)
);
END IF;
END $$;
CREATE OR REPLACE FUNCTION public.handle_new_user_profile()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
INSERT INTO public.profiles (id, role, full_name, avatar_url)
VALUES (
NEW.id,
'user',
COALESCE(NEW.raw_user_meta_data ->> 'full_name', NEW.raw_user_meta_data ->> 'name'),
NEW.raw_user_meta_data ->> 'avatar_url'
)
ON CONFLICT (id) DO NOTHING;
RETURN NEW;
END;
$$;
DROP TRIGGER IF EXISTS on_auth_user_created_profile ON auth.users;
CREATE TRIGGER on_auth_user_created_profile
AFTER INSERT ON auth.users
FOR EACH ROW
EXECUTE FUNCTION public.handle_new_user_profile();
-- Promote your first administrator manually once:
-- UPDATE public.profiles SET role = 'admin' WHERE id = 'YOUR_USER_UUID';
|