40 lines
1.3 KiB
PL/PgSQL
40 lines
1.3 KiB
PL/PgSQL
-- Add clerk_user_id column to profiles table
|
|
ALTER TABLE profiles ADD COLUMN IF NOT EXISTS clerk_user_id TEXT UNIQUE;
|
|
ALTER TABLE profiles ADD COLUMN IF NOT EXISTS email TEXT;
|
|
|
|
-- Create index for clerk_user_id
|
|
CREATE INDEX IF NOT EXISTS idx_profiles_clerk_user_id ON profiles(clerk_user_id);
|
|
|
|
-- Helper function to get clerk_user_id from JWT
|
|
CREATE OR REPLACE FUNCTION get_clerk_user_id()
|
|
RETURNS TEXT AS $$
|
|
BEGIN
|
|
RETURN current_setting('request.jwt.claims', true)::json->>'sub';
|
|
EXCEPTION
|
|
WHEN OTHERS THEN
|
|
RETURN NULL;
|
|
END;
|
|
$$ LANGUAGE plpgsql STABLE;
|
|
|
|
-- Update RLS policies to use clerk_user_id
|
|
DROP POLICY IF EXISTS "Users can view own profile" ON profiles;
|
|
CREATE POLICY "Users can view own profile"
|
|
ON profiles FOR SELECT
|
|
TO authenticated
|
|
USING (clerk_user_id = get_clerk_user_id());
|
|
|
|
DROP POLICY IF EXISTS "Users can update own profile" ON profiles;
|
|
CREATE POLICY "Users can update own profile"
|
|
ON profiles FOR UPDATE
|
|
TO authenticated
|
|
USING (clerk_user_id = get_clerk_user_id());
|
|
|
|
-- Grant access to anonymous for certain things if needed, but profiles should be protected
|
|
-- Assuming profiles are public for basic info (viewing other people's profile)
|
|
CREATE POLICY "Profiles are viewable by everyone"
|
|
ON profiles FOR SELECT
|
|
USING (true);
|
|
|
|
-- Update muhammet to admin
|
|
UPDATE profiles SET role = 'admin' WHERE username = 'muhammet';
|