120 lines
4.6 KiB
PL/PgSQL
120 lines
4.6 KiB
PL/PgSQL
-- Subscription RLS and permissions
|
|
-- Purpose: Ensure admins can read/write ml_user_subscriptions and ml_subscription_plans;
|
|
-- consumers can only access their own subscriptions; everyone can read active plans.
|
|
-- Notes:
|
|
-- - Designed for Supabase (auth.uid(), auth.jwt()).
|
|
-- - Adjust table/column names if they differ in your DB.
|
|
|
|
-- 1) Helper: identify admin users
|
|
-- Prefer JWT app_metadata.role = 'admin' if you set it; fallback to ak_users.user_type = 5
|
|
-- (5 corresponds to ADMIN per MALL_USER_TYPE).
|
|
create or replace function public.is_admin()
|
|
returns boolean
|
|
language sql
|
|
stable
|
|
as $$
|
|
select coalesce(
|
|
-- Check custom claim from JWT: { app_metadata: { role: 'admin' } }
|
|
((auth.jwt() -> 'app_metadata' ->> 'role') = 'admin')
|
|
-- Fallback: ak_users.user_type = 5 (cast to text for compatibility), match user by id as text
|
|
or exists (
|
|
select 1 from public.ak_users u
|
|
where u.id::text = auth.uid()::text
|
|
and u.user_type::text = '5'
|
|
)
|
|
, false);
|
|
$$;
|
|
|
|
comment on function public.is_admin is 'Returns true if current JWT/app user is admin by claim or ak_users.user_type=5.';
|
|
|
|
-- 2) Enable RLS on subscription tables
|
|
alter table if exists public.ml_subscription_plans enable row level security;
|
|
alter table if exists public.ml_user_subscriptions enable row level security;
|
|
|
|
grant select on table public.ml_subscription_plans to anon, authenticated;
|
|
grant select, insert, update, delete on table public.ml_subscription_plans to authenticated; -- limited by RLS
|
|
grant select, insert, update, delete on table public.ml_user_subscriptions to authenticated; -- limited by RLS
|
|
|
|
-- 4) Policies for ml_subscription_plans
|
|
-- 4.1 Everyone can read active plans
|
|
drop policy if exists ml_plans_select_active on public.ml_subscription_plans;
|
|
create policy ml_plans_select_active
|
|
on public.ml_subscription_plans
|
|
for select
|
|
to anon, authenticated
|
|
using (is_active = true);
|
|
|
|
-- 4.2 Admin can do anything
|
|
drop policy if exists ml_plans_admin_all on public.ml_subscription_plans;
|
|
create policy ml_plans_admin_all
|
|
on public.ml_subscription_plans
|
|
for all
|
|
to authenticated
|
|
using (public.is_admin())
|
|
with check (public.is_admin());
|
|
|
|
-- 5) Policies for ml_user_subscriptions
|
|
-- 5.1 Users can see their own subscriptions
|
|
drop policy if exists ml_user_subs_select_own on public.ml_user_subscriptions;
|
|
create policy ml_user_subs_select_own
|
|
on public.ml_user_subscriptions
|
|
for select
|
|
to authenticated
|
|
using (user_id = auth.uid());
|
|
|
|
-- 5.2 Users can create their own subscriptions (checkout)
|
|
drop policy if exists ml_user_subs_insert_own on public.ml_user_subscriptions;
|
|
create policy ml_user_subs_insert_own
|
|
on public.ml_user_subscriptions
|
|
for insert
|
|
to authenticated
|
|
with check (user_id = auth.uid());
|
|
|
|
-- 5.3 Users may update their own records (e.g., auto_renew, cancel_at_period_end)
|
|
-- NOTE: This allows updating any columns; for stricter control, add a BEFORE UPDATE trigger
|
|
-- that restricts column changes for non-admins.
|
|
drop policy if exists ml_user_subs_update_own on public.ml_user_subscriptions;
|
|
create policy ml_user_subs_update_own
|
|
on public.ml_user_subscriptions
|
|
for update
|
|
to authenticated
|
|
using (user_id = auth.uid())
|
|
with check (user_id = auth.uid());
|
|
|
|
-- 5.4 Admin can do anything on user subscriptions
|
|
drop policy if exists ml_user_subs_admin_all on public.ml_user_subscriptions;
|
|
create policy ml_user_subs_admin_all
|
|
on public.ml_user_subscriptions
|
|
for all
|
|
to authenticated
|
|
using (public.is_admin())
|
|
with check (public.is_admin());
|
|
|
|
-- 6) Optional: Trigger to limit non-admin updates to specific fields
|
|
-- Uncomment if you want to enforce column-level restrictions
|
|
-- create or replace function public.enforce_user_sub_update()
|
|
-- returns trigger language plpgsql as $$
|
|
-- begin
|
|
-- if public.is_admin() then
|
|
-- return new; -- admins can change anything
|
|
-- end if;
|
|
-- -- Only allow toggling auto_renew and cancel_at_period_end for owners
|
|
-- if new.user_id = auth.uid() then
|
|
-- new.status := old.status;
|
|
-- new.plan_id := old.plan_id;
|
|
-- new.start_date := old.start_date;
|
|
-- new.end_date := old.end_date;
|
|
-- new.next_billing_date := old.next_billing_date;
|
|
-- -- allow: auto_renew, cancel_at_period_end
|
|
-- return new;
|
|
-- end if;
|
|
-- raise exception 'Forbidden';
|
|
-- end $$;
|
|
-- drop trigger if exists trg_enforce_user_sub_update on public.ml_user_subscriptions;
|
|
-- create trigger trg_enforce_user_sub_update
|
|
-- before update on public.ml_user_subscriptions
|
|
-- for each row execute function public.enforce_user_sub_update();
|
|
|
|
-- 7) Safety: ensure no rows are exposed to non-auth users except active plans via select policy above.
|
|
-- Admins authenticate as normal users with admin claim or ak_users.user_type=5.
|