pidrio / migrations /20250524213845_mellow_recipe.sql
Raiff1982's picture
Upload 20 files
03bff6f verified
raw
history blame contribute delete
992 Bytes
/*
# 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'
)
);