77 lines
2.3 KiB
PL/PgSQL
77 lines
2.3 KiB
PL/PgSQL
-- 1. Create SECURITY DEFINER functions to bypass RLS recursion
|
|
-- We need these functions to check roles and membership without recursing into the same table's RLS policy.
|
|
|
|
-- Function to check if a user is an admin
|
|
CREATE OR REPLACE FUNCTION public.is_admin()
|
|
RETURNS boolean AS $$
|
|
BEGIN
|
|
RETURN EXISTS (
|
|
SELECT 1 FROM public.employees e
|
|
INNER JOIN public.roles r ON e.role_id = r.id
|
|
WHERE e.user_id = auth.uid()
|
|
AND r.name = 'admin'
|
|
);
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
-- Function to get the current user's company IDs
|
|
CREATE OR REPLACE FUNCTION public.get_my_companies()
|
|
RETURNS SETOF uuid AS $$
|
|
BEGIN
|
|
RETURN QUERY SELECT company_id FROM public.employees WHERE user_id = auth.uid();
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
-- 2. Clean up old recursive policies
|
|
DROP POLICY IF EXISTS "Users can view their own company" ON public.companies;
|
|
DROP POLICY IF EXISTS "Personal employee record view" ON public.employees;
|
|
DROP POLICY IF EXISTS "Employees can view colleagues in their company" ON public.employees;
|
|
DROP POLICY IF EXISTS "Managers can manage employees in their company" ON public.employees;
|
|
|
|
-- 3. Create new non-recursive policies
|
|
|
|
-- Companies: Users can see companies they belong to
|
|
CREATE POLICY "View belonging companies"
|
|
ON public.companies
|
|
FOR SELECT TO authenticated
|
|
USING (
|
|
id IN (SELECT public.get_my_companies())
|
|
OR public.is_admin()
|
|
);
|
|
|
|
-- Employees: Everyone can see their own record
|
|
CREATE POLICY "View own employee record"
|
|
ON public.employees
|
|
FOR SELECT TO authenticated
|
|
USING (user_id = auth.uid());
|
|
|
|
-- Employees: Admins can see everyone in the companies they belong to
|
|
CREATE POLICY "Admins can see coworkers"
|
|
ON public.employees
|
|
FOR SELECT TO authenticated
|
|
USING (
|
|
company_id IN (SELECT public.get_my_companies())
|
|
AND public.is_admin()
|
|
);
|
|
|
|
-- Employees: Admins can manage coworkers
|
|
CREATE POLICY "Admins can manage coworkers"
|
|
ON public.employees
|
|
FOR ALL TO authenticated
|
|
USING (
|
|
company_id IN (SELECT public.get_my_companies())
|
|
AND public.is_admin()
|
|
)
|
|
WITH CHECK (
|
|
company_id IN (SELECT public.get_my_companies())
|
|
AND public.is_admin()
|
|
);
|
|
|
|
-- Users: Ensure everyone can see their own profile at least
|
|
CREATE POLICY "Users can view own profile"
|
|
ON public.users
|
|
FOR SELECT TO authenticated
|
|
USING (auth.uid() = id);
|
|
|
|
-- If "Users can view all users" already exists, it's fine.
|