64 lines
2.1 KiB
PL/PgSQL
64 lines
2.1 KiB
PL/PgSQL
-- 0007_baneos.sql
|
|
-- Lista negra de cuentas: tabla prestamos.baneos con historial
|
|
-- (soporta baneos permanentes y temporales via expires_at, aunque la UI
|
|
-- de esta ronda solo expone permanentes). Helper is_banned() para middleware.
|
|
|
|
begin;
|
|
|
|
create table if not exists prestamos.baneos (
|
|
id bigserial primary key,
|
|
profile_id uuid not null references prestamos.profiles(id) on delete cascade,
|
|
razon text not null,
|
|
banned_at timestamptz not null default now(),
|
|
banned_by uuid references prestamos.profiles(id),
|
|
expires_at timestamptz,
|
|
unbanned_at timestamptz,
|
|
unbanned_by uuid references prestamos.profiles(id)
|
|
);
|
|
|
|
-- Un solo baneo activo por profile (unbanned_at IS NULL) — partial unique index
|
|
create unique index if not exists baneos_profile_activo_uniq
|
|
on prestamos.baneos (profile_id)
|
|
where unbanned_at is null;
|
|
|
|
create index if not exists baneos_banned_at_idx
|
|
on prestamos.baneos (banned_at desc);
|
|
|
|
alter table prestamos.baneos enable row level security;
|
|
|
|
-- Admin lee/escribe todo
|
|
drop policy if exists baneos_admin_all on prestamos.baneos;
|
|
create policy baneos_admin_all on prestamos.baneos
|
|
for all to authenticated
|
|
using (prestamos.is_admin())
|
|
with check (prestamos.is_admin());
|
|
|
|
-- User autenticado lee los suyos (para /banned mostrar la razón)
|
|
drop policy if exists baneos_read_self on prestamos.baneos;
|
|
create policy baneos_read_self on prestamos.baneos
|
|
for select to authenticated
|
|
using (profile_id = auth.uid());
|
|
|
|
grant select, insert, update, delete on prestamos.baneos to authenticated, service_role;
|
|
grant usage, select on prestamos.baneos_id_seq to authenticated, service_role;
|
|
|
|
-- Helper: baneo activo (no desbaneado y no expirado)
|
|
create or replace function prestamos.is_banned(p_uid uuid)
|
|
returns boolean
|
|
language sql
|
|
stable
|
|
security definer
|
|
set search_path = prestamos, pg_temp
|
|
as $$
|
|
select exists (
|
|
select 1 from prestamos.baneos
|
|
where profile_id = p_uid
|
|
and unbanned_at is null
|
|
and (expires_at is null or expires_at > now())
|
|
);
|
|
$$;
|
|
|
|
grant execute on function prestamos.is_banned(uuid) to anon, authenticated;
|
|
|
|
commit;
|