Lovable + Supabase RLS cheat sheet: copy-paste policies that actually work

Rule 1: RLS is opt-in. Always enable it.

By default, a Postgres table in Supabase has RLS disabled. The Data API will still apply role-level grants, but any policy you write does nothing until you enable RLS on the table.

ALTER TABLE public.projects ENABLE ROW LEVEL SECURITY;

Rule 2: Always GRANT alongside RLS

Supabase's PostgREST Data API does not grant default privileges on public-schema tables. RLS alone is not enough — without a GRANT, the API returns a permission error even when your policies would allow the query.

GRANT SELECT, INSERT, UPDATE, DELETE ON public.projects TO authenticated;
GRANT ALL ON public.projects TO service_role;

Pattern: ownership (one user owns one row)

The simplest pattern. Every row has a user_id column; users can only touch their own rows.

CREATE POLICY "Users read own projects"
ON public.projects FOR SELECT
TO authenticated
USING (user_id = auth.uid());

CREATE POLICY "Users insert own projects"
ON public.projects FOR INSERT
TO authenticated
WITH CHECK (user_id = auth.uid());

CREATE POLICY "Users update own projects"
ON public.projects FOR UPDATE
TO authenticated
USING (user_id = auth.uid())
WITH CHECK (user_id = auth.uid());

CREATE POLICY "Users delete own projects"
ON public.projects FOR DELETE
TO authenticated
USING (user_id = auth.uid());

Pattern: workspace membership (avoid recursion)

When access is scoped by workspace membership, the naive policy queries the workspace_members table — but if that table has its own RLS policy that also references workspace_members, you get an infinite recursion error.

The fix is a SECURITY DEFINER function that bypasses RLS for the lookup. Mark it STABLE and lock down search_path.

CREATE OR REPLACE FUNCTION public.is_workspace_member(ws_id uuid)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.workspace_members
    WHERE workspace_id = ws_id AND user_id = auth.uid()
  )
$$;

CREATE POLICY "Members read workspace projects"
ON public.projects FOR SELECT
TO authenticated
USING (public.is_workspace_member(workspace_id));

Pattern: roles (never on profiles)

Storing a role column on the profiles or users table is a privilege-escalation antipattern. Use a separate user_roles table and a SECURITY DEFINER has_role() function.

CREATE TYPE public.app_role AS ENUM ('admin', 'member');

CREATE TABLE public.user_roles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
  role app_role NOT NULL,
  UNIQUE (user_id, role)
);

GRANT SELECT ON public.user_roles TO authenticated;
GRANT ALL ON public.user_roles TO service_role;
ALTER TABLE public.user_roles ENABLE ROW LEVEL SECURITY;

CREATE OR REPLACE FUNCTION public.has_role(_user_id uuid, _role app_role)
RETURNS boolean
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.user_roles
    WHERE user_id = _user_id AND role = _role
  )
$$;

Common mistakes Lovable will make if you don't stop it

• Generating tables without RLS enabled.
• Writing policies without a matching GRANT, then debugging a permission error for an hour.
• Putting role columns on profiles instead of a user_roles table.
• Querying workspace_members from inside a workspace_members policy, causing recursion errors.
• Forgetting service_role grants, breaking edge functions and webhooks.