Spaces:
Running
Running
/* | |
# Add user roles table and admin role policy | |
1. New Tables | |
- `user_roles` | |
- `id` (uuid, primary key) | |
- `user_id` (uuid, references auth.users) | |
- `role` (text) | |
- `created_at` (timestamptz) | |
2. Security | |
- Enable RLS on `user_roles` table | |
- Add policies for admin role management | |
*/ | |
-- Create user_roles table | |
CREATE TABLE IF NOT EXISTS 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 user_roles ENABLE ROW LEVEL SECURITY; | |
-- Policies for user_roles table | |
CREATE POLICY "Users can read their own role" | |
ON user_roles | |
FOR SELECT | |
TO authenticated | |
USING (auth.uid() = user_id); | |
CREATE POLICY "Only admins can manage roles" | |
ON user_roles | |
FOR ALL | |
TO authenticated | |
USING ( | |
EXISTS ( | |
SELECT 1 FROM user_roles | |
WHERE user_id = auth.uid() | |
AND role = 'admin' | |
) | |
); |