collective-lists/supabase/tests/008_auth_user_role_default.sql

-- pgTAP: auth.users role/aud defaulting (migrations 013 + 014)
-- Run with: psql -U postgres -d postgres -f supabase/tests/008_auth_user_role_default.sql
--
-- Covers two bugs observed on ambrosio:
--  * 2026-04-15 — GoTrue v2.158.1 inserts OIDC-created users into auth.users
--    with empty-string `role` and `aud`, later making PostgREST's
--    `SET LOCAL role = ''` fail with `role "" does not exist`.
--    Migration 013 adds a BEFORE INSERT trigger + backfills.
--  * 2026-04-20 — GoTrue additionally emits an UPDATE right after the
--    insert (pop ORM resends the whole struct incl. role='') which clobbers
--    the role that 013's trigger just filled in. Migration 014 adds a
--    matching BEFORE UPDATE trigger.

CREATE EXTENSION IF NOT EXISTS pgtap;

BEGIN;
SELECT plan(11);

-- ── Existence assertions ────────────────────────────────────────────────
SELECT has_function(
    'auth', 'ensure_user_role_default', ARRAY[]::text[],
    'ensure_user_role_default trigger function exists in the auth schema'
);

SELECT has_trigger(
    'auth', 'users', 'ensure_role_default',
    'BEFORE INSERT trigger ensure_role_default exists on auth.users'
);

SELECT has_trigger(
    'auth', 'users', 'ensure_role_default_update',
    'BEFORE UPDATE trigger ensure_role_default_update exists on auth.users'
);

-- ── Backfill assertion ──────────────────────────────────────────────────
SELECT ok(
    (SELECT count(*) FROM auth.users WHERE role IS NULL OR role = '' OR aud IS NULL OR aud = '') = 0,
    'No pre-existing auth.users row has an empty role or aud'
);

-- ── Trigger behavior ────────────────────────────────────────────────────
-- Insert a synthetic user with empty role/aud; the trigger should fill both.
-- Drop the AFTER trigger locally so we don't exercise handle_new_user's
-- side-effects (tested elsewhere, not the focus here).
ALTER TABLE auth.users DISABLE TRIGGER on_auth_user_created;

-- NEW row with empty strings
INSERT INTO auth.users (instance_id, id, email, role, aud)
VALUES (
    '00000000-0000-0000-0000-000000000000',
    'f0000000-0000-0000-0000-000000000001',
    'empty-role@test.local',
    '',
    ''
);
SELECT is(
    (SELECT role FROM auth.users WHERE id = 'f0000000-0000-0000-0000-000000000001'),
    'authenticated',
    'Empty role on INSERT is defaulted to authenticated'
);
SELECT is(
    (SELECT aud FROM auth.users WHERE id = 'f0000000-0000-0000-0000-000000000001'),
    'authenticated',
    'Empty aud on INSERT is defaulted to authenticated'
);

-- NEW row with NULL values
INSERT INTO auth.users (instance_id, id, email, role, aud)
VALUES (
    '00000000-0000-0000-0000-000000000000',
    'f0000000-0000-0000-0000-000000000002',
    'null-role@test.local',
    NULL,
    NULL
);
SELECT is(
    (SELECT role FROM auth.users WHERE id = 'f0000000-0000-0000-0000-000000000002'),
    'authenticated',
    'NULL role on INSERT is defaulted to authenticated'
);

-- Explicit non-empty role is preserved (trigger should only fill when empty).
INSERT INTO auth.users (instance_id, id, email, role, aud)
VALUES (
    '00000000-0000-0000-0000-000000000000',
    'f0000000-0000-0000-0000-000000000003',
    'custom-role@test.local',
    'service_role',
    'authenticated'
);
SELECT is(
    (SELECT role FROM auth.users WHERE id = 'f0000000-0000-0000-0000-000000000003'),
    'service_role',
    'Explicit role is preserved by the trigger'
);

-- ── UPDATE trigger behavior (migration 014) ────────────────────────────
-- Simulate GoTrue's pop-ORM resending role='' on the follow-up UPDATE.
-- The BEFORE UPDATE trigger must rewrite it to 'authenticated'.
UPDATE auth.users
SET role = '', aud = ''
WHERE id = 'f0000000-0000-0000-0000-000000000001';
SELECT is(
    (SELECT role FROM auth.users WHERE id = 'f0000000-0000-0000-0000-000000000001'),
    'authenticated',
    'UPDATE clearing role to empty is reverted to authenticated'
);
SELECT is(
    (SELECT aud FROM auth.users WHERE id = 'f0000000-0000-0000-0000-000000000001'),
    'authenticated',
    'UPDATE clearing aud to empty is reverted to authenticated'
);

-- And a legitimate UPDATE to some other field (e.g. raw_user_meta_data)
-- does not change role/aud.
UPDATE auth.users
SET raw_user_meta_data = '{"locale":"es"}'::jsonb
WHERE id = 'f0000000-0000-0000-0000-000000000003';
SELECT is(
    (SELECT role FROM auth.users WHERE id = 'f0000000-0000-0000-0000-000000000003'),
    'service_role',
    'Unrelated UPDATE leaves an explicit role untouched'
);

SELECT * FROM finish();
ROLLBACK;