app/codehosting/create-oidc-binding.sql

-- This trigger automatically ensures an openid connect identity is bound to an
-- equivalent LDAP account created by Gitea/Forgejo.

BEGIN;

CREATE OR REPLACE FUNCTION upsert_oidc_external_users ()
   RETURNS TRIGGER
AS $$
BEGIN
    -- 1 is OpenID Connect login source ID
    -- 2 is LDAP login source ID
    INSERT INTO external_login_user (external_id, user_id, login_source_id) SELECT name, id, 1 FROM "user" WHERE login_source = 2 ON CONFLICT DO NOTHING;
    RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

DROP TRIGGER IF EXISTS upsert_oidc_external_users ON "user";
CREATE TRIGGER upsert_oidc_external_users AFTER INSERT OR UPDATE ON "user" EXECUTE PROCEDURE upsert_oidc_external_users();

-- Force trigger run
UPDATE "user" SET name = name WHERE FALSE;

COMMIT;