alpenwerk-hr/db/migrations/20260805110000_replace_auth_uid_in_functions.sql

-- auth.uid() aus den Geschäftsfunktionen entfernen.
--
-- Die Anwendung verbindet sich als eigene Rolle ohne BYPASSRLS. Diese Rolle
-- hat kein Recht auf das Schema `auth` — und jede Funktion, die dort etwas
-- aufruft, scheitert mit „permission denied for schema auth".
--
-- Das trifft **jede schreibende Aktion**: Einstellen, Versetzen, Befördern,
-- Austritt, Notizen, Planstellen. Aufgefallen ist es beim Nachstellen einer
-- Neueinstellung; zuvor lief alles über eine Rolle, die das Schema sehen
-- durfte.
--
-- app_current_user_id() liefert dasselbe und funktioniert auf jedem
-- PostgreSQL — es liest den transaktionslokalen Sitzungskontext, den lib/db
-- setzt.

create or replace function app_uid_ersetzen(p_funktion text)
returns void
language plpgsql
set search_path = public, pg_temp
as $$
declare
  v_def text;
begin
  select pg_get_functiondef(p.oid) into v_def
    from pg_proc p
    join pg_namespace n on n.oid = p.pronamespace
   where n.nspname = 'public' and p.proname = p_funktion
   limit 1;

  if v_def is null then
    raise exception 'Funktion %() nicht gefunden.', p_funktion;
  end if;
  if position('auth.uid()' in v_def) = 0 then
    raise notice '%(): bereits umgestellt.', p_funktion;
    return;
  end if;

  execute replace(v_def, 'auth.uid()', 'app_current_user_id()');
  raise notice '%(): umgestellt.', p_funktion;
end;
$$;

select app_uid_ersetzen('add_employee_dependent');
select app_uid_ersetzen('add_employee_note');
select app_uid_ersetzen('adjust_karenz_return');
select app_uid_ersetzen('complete_employee_note');
select app_uid_ersetzen('create_position');
select app_uid_ersetzen('delete_employee_dependent');
select app_uid_ersetzen('delete_position');
select app_uid_ersetzen('hire_employee');
select app_uid_ersetzen('promote_employee');
select app_uid_ersetzen('record_karenz_return');
select app_uid_ersetzen('rehire_employee');
select app_uid_ersetzen('start_karenz');
select app_uid_ersetzen('terminate_employee');
select app_uid_ersetzen('transfer_employee');

drop function app_uid_ersetzen(text);

-- ═══ Den Rückfall selbst absichern ═══════════════════════════════
-- app_current_user_id() behält seinen Übergangszweig auf auth.uid(), fängt
-- aber bisher nur „Funktion fehlt". Für eine Rolle ohne Recht auf das Schema
-- kommt stattdessen insufficient_privilege — und die Funktion warf, statt
-- null zu liefern. Das fiel nicht auf, weil der Zweig nur ohne
-- Sitzungskontext erreicht wird; genau dann soll aber „niemand angemeldet"
-- herauskommen und kein Fehler.
create or replace function app_current_user_id()
returns uuid
language plpgsql
stable
security definer
set search_path = public, pg_temp
as $$
declare
  v_id uuid;
begin
  v_id := nullif(current_setting('app.user_id', true), '')::uuid;
  if v_id is not null then
    return v_id;
  end if;

  begin
    execute 'select auth.uid()' into v_id;
  exception
    when undefined_function or invalid_schema_name or undefined_table or insufficient_privilege then
      v_id := null;
  end;
  return v_id;
end;
$$;

-- ═══ Gegenprobe ══════════════════════════════════════════════════
do $$
declare r record;
begin
  for r in
    select p.proname
      from pg_proc p
      join pg_namespace n on n.oid = p.pronamespace
     where n.nspname = 'public' and p.prokind = 'f'
       and p.proname <> 'app_current_user_id'
       and pg_get_functiondef(p.oid) like '%auth.uid()%'
  loop
    raise exception 'Funktion %() ruft weiterhin auth.uid() auf.', r.proname;
  end loop;

  -- Ohne Kontext muss die Kennung null sein und darf nicht werfen.
  perform set_config('app.user_id', '', true);
  if app_current_user_id() is not null then
    raise exception 'app_current_user_id() liefert ohne Kontext eine Kennung.';
  end if;
end;
$$;