Spaces:
Running
Running
File size: 2,023 Bytes
43172ae |
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 |
/*
# Authentication and User Roles Setup
1. New Tables
- `user_roles`
- `id` (uuid, primary key)
- `user_id` (uuid, references auth.users)
- `role` (text)
- `created_at` (timestamp with time zone)
2. Security
- Enable RLS on `user_roles` table
- Add policies for authenticated users to read their own role
- Add policy for admin users to manage roles
*/
-- Create user_roles table
CREATE TABLE IF NOT EXISTS public.user_roles (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
user_id uuid REFERENCES auth.users NOT NULL,
role text NOT NULL,
created_at timestamptz DEFAULT now()
);
-- Enable RLS
ALTER TABLE public.user_roles ENABLE ROW LEVEL SECURITY;
-- Policies
CREATE POLICY "Users can read own role"
ON public.user_roles
FOR SELECT
TO authenticated
USING (auth.uid() = user_id);
CREATE POLICY "Admin users can manage roles"
ON public.user_roles
FOR ALL
TO authenticated
USING ((SELECT role FROM public.user_roles WHERE user_id = auth.uid()) = 'admin')
WITH CHECK ((SELECT role FROM public.user_roles WHERE user_id = auth.uid()) = 'admin');
-- Create admin user if not exists
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM auth.users WHERE email = 'admin@codette.ai'
) THEN
INSERT INTO auth.users (
instance_id,
id,
aud,
role,
email,
encrypted_password,
email_confirmed_at,
created_at,
updated_at,
confirmation_token,
recovery_token
)
VALUES (
'00000000-0000-0000-0000-000000000000',
gen_random_uuid(),
'authenticated',
'authenticated',
'admin@codette.ai',
crypt('admin123', gen_salt('bf')), -- Default password: admin123
now(),
now(),
now(),
encode(gen_random_bytes(32), 'hex'),
encode(gen_random_bytes(32), 'hex')
);
-- Add admin role
INSERT INTO public.user_roles (user_id, role)
SELECT id, 'admin'
FROM auth.users
WHERE email = 'admin@codette.ai';
END IF;
END $$; |