pleskSaas/supabase/migrations/CL2_rls_policies.sql

-- CL2: Multi-tenant RLS hardening + onboarding-safe policies

create extension if not exists pgcrypto;

-- ---------------------------------------------------------------------------
-- Helper functions
-- ---------------------------------------------------------------------------
create or replace function public.is_agency_member(target_agency_id uuid)
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (
    select 1
    from public.agency_members am
    where am.agency_id = target_agency_id
      and am.user_id = auth.uid()
  );
$$;

create or replace function public.is_agency_admin(target_agency_id uuid)
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (
    select 1
    from public.agency_members am
    where am.agency_id = target_agency_id
      and am.user_id = auth.uid()
      and am.role in ('owner', 'admin')
  );
$$;

-- ---------------------------------------------------------------------------
-- RLS enablement
-- ---------------------------------------------------------------------------
alter table public.agencies enable row level security;
alter table public.agency_members enable row level security;
alter table public.clients enable row level security;
alter table public.plesk_instances enable row level security;
alter table public.plesk_subscriptions enable row level security;
alter table public.plesk_domains enable row level security;
alter table public.actions_log enable row level security;
alter table public.billing_accounts enable row level security;
alter table public.entitlements enable row level security;

-- ---------------------------------------------------------------------------
-- Drop legacy policies (if present)
-- ---------------------------------------------------------------------------
drop policy if exists agencies_select on public.agencies;
drop policy if exists agencies_update on public.agencies;
drop policy if exists agency_members_select on public.agency_members;
drop policy if exists agency_members_manage on public.agency_members;
drop policy if exists clients_member_access on public.clients;
drop policy if exists plesk_instances_member_access on public.plesk_instances;
drop policy if exists plesk_subscriptions_member_access on public.plesk_subscriptions;
drop policy if exists plesk_domains_member_access on public.plesk_domains;
drop policy if exists actions_log_member_access on public.actions_log;
drop policy if exists billing_accounts_member_access on public.billing_accounts;
drop policy if exists entitlements_member_access on public.entitlements;

-- ---------------------------------------------------------------------------
-- agencies
-- ---------------------------------------------------------------------------
create policy agencies_select_member
on public.agencies
for select
to authenticated
using (public.is_agency_member(id));

create policy agencies_insert_authenticated
on public.agencies
for insert
to authenticated
with check (true);

create policy agencies_update_admin
on public.agencies
for update
to authenticated
using (public.is_agency_admin(id))
with check (public.is_agency_admin(id));

create policy agencies_delete_admin
on public.agencies
for delete
to authenticated
using (public.is_agency_admin(id));

-- ---------------------------------------------------------------------------
-- agency_members
-- ---------------------------------------------------------------------------
create policy agency_members_select_self
on public.agency_members
for select
to authenticated
using (user_id = auth.uid());

-- Onboarding: user can create their own owner membership for their first agency.
create policy agency_members_insert_self_onboarding
on public.agency_members
for insert
to authenticated
with check (
  user_id = auth.uid()
  and role = 'owner'
  and not exists (
    select 1
    from public.agency_members am
    where am.user_id = auth.uid()
  )
);

-- Admins can add members to their agency.
create policy agency_members_insert_admin
on public.agency_members
for insert
to authenticated
with check (public.is_agency_admin(agency_id));

create policy agency_members_update_admin
on public.agency_members
for update
to authenticated
using (public.is_agency_admin(agency_id))
with check (public.is_agency_admin(agency_id));

create policy agency_members_delete_admin_or_self
on public.agency_members
for delete
to authenticated
using (public.is_agency_admin(agency_id) or user_id = auth.uid());

-- ---------------------------------------------------------------------------
-- clients (member writable by design)
-- ---------------------------------------------------------------------------
create policy clients_select_member
on public.clients
for select
to authenticated
using (public.is_agency_member(agency_id));

create policy clients_insert_member
on public.clients
for insert
to authenticated
with check (public.is_agency_member(agency_id));

create policy clients_update_member
on public.clients
for update
to authenticated
using (public.is_agency_member(agency_id))
with check (public.is_agency_member(agency_id));

create policy clients_delete_member
on public.clients
for delete
to authenticated
using (public.is_agency_member(agency_id));

-- ---------------------------------------------------------------------------
-- plesk_instances (admin-writable)
-- ---------------------------------------------------------------------------
create policy plesk_instances_select_member
on public.plesk_instances
for select
to authenticated
using (public.is_agency_member(agency_id));

create policy plesk_instances_insert_admin
on public.plesk_instances
for insert
to authenticated
with check (public.is_agency_admin(agency_id));

create policy plesk_instances_update_admin
on public.plesk_instances
for update
to authenticated
using (public.is_agency_admin(agency_id))
with check (public.is_agency_admin(agency_id));

create policy plesk_instances_delete_admin
on public.plesk_instances
for delete
to authenticated
using (public.is_agency_admin(agency_id));

-- ---------------------------------------------------------------------------
-- plesk_subscriptions (admin-writable synced data)
-- ---------------------------------------------------------------------------
create policy plesk_subscriptions_select_member
on public.plesk_subscriptions
for select
to authenticated
using (public.is_agency_member(agency_id));

create policy plesk_subscriptions_insert_admin
on public.plesk_subscriptions
for insert
to authenticated
with check (public.is_agency_admin(agency_id));

create policy plesk_subscriptions_update_admin
on public.plesk_subscriptions
for update
to authenticated
using (public.is_agency_admin(agency_id))
with check (public.is_agency_admin(agency_id));

create policy plesk_subscriptions_delete_admin
on public.plesk_subscriptions
for delete
to authenticated
using (public.is_agency_admin(agency_id));

-- ---------------------------------------------------------------------------
-- plesk_domains (admin-writable synced data)
-- ---------------------------------------------------------------------------
create policy plesk_domains_select_member
on public.plesk_domains
for select
to authenticated
using (public.is_agency_member(agency_id));

create policy plesk_domains_insert_admin
on public.plesk_domains
for insert
to authenticated
with check (public.is_agency_admin(agency_id));

create policy plesk_domains_update_admin
on public.plesk_domains
for update
to authenticated
using (public.is_agency_admin(agency_id))
with check (public.is_agency_admin(agency_id));

create policy plesk_domains_delete_admin
on public.plesk_domains
for delete
to authenticated
using (public.is_agency_admin(agency_id));

-- ---------------------------------------------------------------------------
-- actions_log
-- ---------------------------------------------------------------------------
create policy actions_log_select_member
on public.actions_log
for select
to authenticated
using (public.is_agency_member(agency_id));

create policy actions_log_insert_admin
on public.actions_log
for insert
to authenticated
with check (public.is_agency_admin(agency_id));

create policy actions_log_update_admin
on public.actions_log
for update
to authenticated
using (public.is_agency_admin(agency_id))
with check (public.is_agency_admin(agency_id));

-- ---------------------------------------------------------------------------
-- billing_accounts (admin-writable)
-- ---------------------------------------------------------------------------
create policy billing_accounts_select_member
on public.billing_accounts
for select
to authenticated
using (public.is_agency_member(agency_id));

create policy billing_accounts_insert_admin
on public.billing_accounts
for insert
to authenticated
with check (public.is_agency_admin(agency_id));

create policy billing_accounts_update_admin
on public.billing_accounts
for update
to authenticated
using (public.is_agency_admin(agency_id))
with check (public.is_agency_admin(agency_id));

create policy billing_accounts_delete_admin
on public.billing_accounts
for delete
to authenticated
using (public.is_agency_admin(agency_id));

-- ---------------------------------------------------------------------------
-- entitlements (admin-writable)
-- ---------------------------------------------------------------------------
create policy entitlements_select_member
on public.entitlements
for select
to authenticated
using (public.is_agency_member(agency_id));

create policy entitlements_insert_admin
on public.entitlements
for insert
to authenticated
with check (public.is_agency_admin(agency_id));

create policy entitlements_update_admin
on public.entitlements
for update
to authenticated
using (public.is_agency_admin(agency_id))
with check (public.is_agency_admin(agency_id));

create policy entitlements_delete_admin
on public.entitlements
for delete
to authenticated
using (public.is_agency_admin(agency_id));

-- ---------------------------------------------------------------------------
-- Indexes (if missing)
-- ---------------------------------------------------------------------------
create index if not exists idx_agency_members_agency_id on public.agency_members (agency_id);
create index if not exists idx_agency_members_user_id on public.agency_members (user_id);

create index if not exists idx_clients_agency_id on public.clients (agency_id);

create index if not exists idx_plesk_instances_agency_id on public.plesk_instances (agency_id);

create index if not exists idx_plesk_subscriptions_agency_id on public.plesk_subscriptions (agency_id);
create index if not exists idx_plesk_subscriptions_instance_id on public.plesk_subscriptions (plesk_instance_id);
create index if not exists idx_plesk_subscriptions_client_id on public.plesk_subscriptions (client_id);

create index if not exists idx_plesk_domains_agency_id on public.plesk_domains (agency_id);
create index if not exists idx_plesk_domains_instance_id on public.plesk_domains (plesk_instance_id);
create index if not exists idx_plesk_domains_subscription_id on public.plesk_domains (subscription_id);

create index if not exists idx_actions_log_agency_id on public.actions_log (agency_id);
create index if not exists idx_actions_log_target_id on public.actions_log (target_id);

create index if not exists idx_billing_accounts_agency_id on public.billing_accounts (agency_id);

create index if not exists idx_entitlements_agency_id on public.entitlements (agency_id);

-- ---------------------------------------------------------------------------
-- updated_at trigger safety for expected tables
-- ---------------------------------------------------------------------------
create or replace function public.set_updated_at()
returns trigger
language plpgsql
as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

drop trigger if exists trg_plesk_subscriptions_updated_at on public.plesk_subscriptions;
create trigger trg_plesk_subscriptions_updated_at
before update on public.plesk_subscriptions
for each row execute function public.set_updated_at();

drop trigger if exists trg_billing_accounts_updated_at on public.billing_accounts;
create trigger trg_billing_accounts_updated_at
before update on public.billing_accounts
for each row execute function public.set_updated_at();