147 lines
4.7 KiB
PL/PgSQL
147 lines
4.7 KiB
PL/PgSQL
-- Lucozade Store Audit · Auth V6
|
|
-- Ejecutar una vez en Supabase SQL Editor.
|
|
-- Es idempotente y NO elimina usuarios de auth.users.
|
|
-- V6 usa los enlaces de recuperación nativos de Supabase Auth;
|
|
-- por eso elimina la tabla y los RPC personalizados de recuperación de V5.
|
|
|
|
begin;
|
|
|
|
-- Objetos obsoletos de V5 (ya no son necesarios).
|
|
drop function if exists public.lucozade_consume_password_reset(text, text);
|
|
drop function if exists public.lucozade_issue_password_reset(uuid, text);
|
|
drop function if exists public.lucozade_find_recoverable_user_by_email(text);
|
|
drop table if exists public.lucozade_password_reset_tokens;
|
|
|
|
-- Acceso específico a la aplicación.
|
|
create table if not exists public.lucozade_access (
|
|
user_id uuid primary key references auth.users(id) on delete cascade,
|
|
email text not null,
|
|
full_name text,
|
|
is_active boolean not null default true,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
-- Compatibilidad si la tabla venía de una versión anterior.
|
|
alter table public.lucozade_access
|
|
add column if not exists email text,
|
|
add column if not exists full_name text,
|
|
add column if not exists is_active boolean not null default true,
|
|
add column if not exists created_at timestamptz not null default now(),
|
|
add column if not exists updated_at timestamptz not null default now();
|
|
|
|
update public.lucozade_access
|
|
set email = lower(coalesce(email, ''))
|
|
where email is distinct from lower(coalesce(email, ''));
|
|
|
|
alter table public.lucozade_access
|
|
alter column email set not null;
|
|
|
|
create index if not exists lucozade_access_email_idx
|
|
on public.lucozade_access (lower(email));
|
|
|
|
create or replace function public.lucozade_set_updated_at()
|
|
returns trigger
|
|
language plpgsql
|
|
security invoker
|
|
set search_path = public
|
|
as $$
|
|
begin
|
|
new.email = lower(trim(new.email));
|
|
new.updated_at = now();
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
drop trigger if exists lucozade_access_set_updated_at on public.lucozade_access;
|
|
create trigger lucozade_access_set_updated_at
|
|
before update on public.lucozade_access
|
|
for each row execute function public.lucozade_set_updated_at();
|
|
|
|
-- Los usuarios creados por el workflow V6 llevan app_id=lucozade-audit.
|
|
-- Este trigger crea el acceso automáticamente; n8n también hace un upsert
|
|
-- como segunda garantía antes de responder que el registro terminó.
|
|
create or replace function public.handle_new_lucozade_user()
|
|
returns trigger
|
|
language plpgsql
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
begin
|
|
if coalesce(new.raw_user_meta_data ->> 'app_id', '') = 'lucozade-audit' then
|
|
insert into public.lucozade_access (
|
|
user_id,
|
|
email,
|
|
full_name,
|
|
is_active
|
|
)
|
|
values (
|
|
new.id,
|
|
lower(coalesce(new.email, '')),
|
|
nullif(trim(coalesce(new.raw_user_meta_data ->> 'full_name', '')), ''),
|
|
true
|
|
)
|
|
on conflict (user_id) do update
|
|
set email = excluded.email,
|
|
full_name = excluded.full_name,
|
|
is_active = true,
|
|
updated_at = now();
|
|
end if;
|
|
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
drop trigger if exists on_lucozade_auth_user_created on auth.users;
|
|
create trigger on_lucozade_auth_user_created
|
|
after insert on auth.users
|
|
for each row execute function public.handle_new_lucozade_user();
|
|
|
|
-- Recupera accesos de usuarios Lucozade existentes que pudieran haber sido
|
|
-- creados antes de instalar el trigger.
|
|
insert into public.lucozade_access (
|
|
user_id,
|
|
email,
|
|
full_name,
|
|
is_active
|
|
)
|
|
select
|
|
u.id,
|
|
lower(coalesce(u.email, '')),
|
|
nullif(trim(coalesce(u.raw_user_meta_data ->> 'full_name', '')), ''),
|
|
true
|
|
from auth.users as u
|
|
where coalesce(u.raw_user_meta_data ->> 'app_id', '') = 'lucozade-audit'
|
|
and coalesce(u.email, '') <> ''
|
|
on conflict (user_id) do update
|
|
set email = excluded.email,
|
|
full_name = coalesce(excluded.full_name, public.lucozade_access.full_name),
|
|
updated_at = now();
|
|
|
|
alter table public.lucozade_access enable row level security;
|
|
|
|
drop policy if exists "Lucozade users can read their active access"
|
|
on public.lucozade_access;
|
|
|
|
create policy "Lucozade users can read their active access"
|
|
on public.lucozade_access
|
|
for select
|
|
to authenticated
|
|
using (auth.uid() = user_id and is_active = true);
|
|
|
|
revoke all on public.lucozade_access from anon;
|
|
grant usage on schema public to authenticated, service_role;
|
|
grant select on public.lucozade_access to authenticated;
|
|
grant select, insert, update, delete on public.lucozade_access to service_role;
|
|
|
|
notify pgrst, 'reload schema';
|
|
|
|
commit;
|
|
|
|
-- IMPORTANTE:
|
|
-- Borrar una fila de public.lucozade_access revoca el acceso a Lucozade,
|
|
-- pero NO elimina la cuenta real de Supabase Auth.
|
|
-- Para eliminar totalmente una cuenta, use Authentication > Users
|
|
-- o, con extremo cuidado:
|
|
-- delete from auth.users where lower(email)=lower('correo@ejemplo.com');
|