Fix "Created By" Display Issue for Non-Admin Users

Date: 2026-01-15
Issue: Non-admin users cannot see "created by" names in Refill Management
Migration: 20260115100000_allow_read_user_names.sql

Problem Description

Symptom

• Admin users can see "Created By" names in Refill Management
• Non-admin users see "-" instead of names
• This affects all features that display created_by information

Root Cause

The user_profiles table has Row Level Security (RLS) policies that restrict access:

-- Current policies on user_profiles:
1. "Admins can view all profiles" - Admin only
2. "Users can view own profile" - Each user can only see their own profile

When a non-admin user queries user_profiles to get names of other users who created refills:

await supabase
  .from("user_profiles")
  .select("id, full_name")
  .in("id", uniqueUserIds);

The RLS policy filters the results, returning only the user's own profile (if their ID is in the list), not other users' profiles.

Solution

The migration 20260115100000_allow_read_user_names.sql provides two solutions:

Solution 1: SECURITY DEFINER Function (Recommended)

Creates a function that bypasses RLS for reading display names only:

CREATE FUNCTION get_user_display_names(user_ids UUID[])
RETURNS TABLE (id UUID, full_name TEXT)

Usage in code:

const { data: profiles } = await supabase
  .rpc('get_user_display_names', { user_ids: uniqueUserIds });

Solution 2: Permissive RLS Policy (Applied)

Adds a new RLS policy that allows all authenticated users to read user profiles:

CREATE POLICY "Users can view other users display names"
  ON user_profiles
  FOR SELECT
  TO authenticated
  USING (true);

This allows the existing code to work without modification.

How to Apply the Migration

Option 1: Using Supabase Dashboard (Easiest)

1. Open Supabase Dashboard
2. Go to SQL Editor
3. Create a new query
4. Copy and paste the content of supabase/migrations/20260115100000_allow_read_user_names.sql
5. Run the query

Option 2: Using Supabase CLI

If you have psql or database access tools:

# Connect to your database and run:
psql "$SUPABASE_DB_URL" -f supabase/migrations/20260115100000_allow_read_user_names.sql

Option 3: Manual Execution

Connect to your database using any SQL client and execute:

-- Create the function
CREATE OR REPLACE FUNCTION get_user_display_names(user_ids UUID[])
RETURNS TABLE (id UUID, full_name TEXT) AS $$
BEGIN
  RETURN QUERY
  SELECT up.id, up.full_name
  FROM user_profiles up
  WHERE up.id = ANY(user_ids)
  AND up.is_active = true;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER STABLE;

GRANT EXECUTE ON FUNCTION get_user_display_names(UUID[]) TO authenticated;

-- Create the RLS policy
CREATE POLICY "Users can view other users display names"
  ON user_profiles
  FOR SELECT
  TO authenticated
  USING (true);

Verification

After applying the migration:

1. Log in as a non-admin user
2. Go to Refill Management page
3. Check the "Created By" column
4. Should now show user names instead of "-"

Test Query (Optional)

You can verify in the SQL Editor:

-- As a non-admin user, this should now return results:
SELECT id, full_name 
FROM user_profiles 
WHERE id IN (SELECT DISTINCT created_by FROM ops_dustloc_refills);

Security Considerations

What Information Is Exposed?

Only non-sensitive information is exposed:

• ✅ User ID (UUID)
• ✅ Full Name
• ❌ NOT exposed: Email, role, permissions, passwords

Is This Safe?

Yes, because:

1. User names are not sensitive information
2. They're needed for attribution (who created/modified records)
3. Similar to how GitHub, Jira, etc. show user names
4. No sensitive data (email, role) is exposed

Alternative: Stricter Approach

If you prefer a stricter approach, use Solution 1 (the function) and modify the code:

File: src/app/(admin)/(pages)/refill-management/index.tsx

// Replace lines 83-86:
const { data: profiles } = await supabase
  .from("user_profiles")
  .select("id, full_name")
  .in("id", uniqueUserIds);

// With:
const { data: profiles } = await supabase
  .rpc('get_user_display_names', { user_ids: uniqueUserIds });

Then remove the RLS policy from the migration file before applying.

Related Files

• Migration: supabase/migrations/20260115100000_allow_read_user_names.sql
• Affected Page: src/app/(admin)/(pages)/refill-management/index.tsx
• Related: src/app/(admin)/(pages)/calibration-management/index.tsx (may have same issue)

Impact

Before Migration

User Type	Can See Created By
Admin	✅ Yes
Non-Admin	❌ No

After Migration

User Type	Can See Created By
Admin	✅ Yes
Non-Admin	✅ Yes

Rollback

If you need to rollback:

-- Remove the RLS policy
DROP POLICY IF EXISTS "Users can view other users display names" ON user_profiles;

-- Remove the function
DROP FUNCTION IF EXISTS get_user_display_names(UUID[]);

Summary

This migration fixes a permission issue where non-admin users couldn't see who created refills, calibrations, and other records. The fix is safe and follows best practices by only exposing non-sensitive information (user names) needed for proper attribution.

Action Required: Apply the migration using one of the methods above, then refresh the Refill Management page to see the fix in action.