inviteUserByEmail failing on certain instances on the Supabase platform

[lwjameson]

Bug report

• [x] I confirm this is a bug with Supabase, not with my own application.
• [x] I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

We have a number of instances of our app hosted on Supabase. We have a feature that allows admins to invite users to the app via email. In the last few weeks this function has been failing. Our client uses Astro JS and is hosted on Netlify. We have an Astro endpoint which is responsible for making a call to auth.admin.inviteUserByEmail;

  const supa = createClient(
    supabaseServerUrl,
    import.meta.env.SUPABASE_SERVICE_KEY
  );

  const body = await request.json();

  const inviteResp = await supa.auth.admin.inviteUserByEmail(body.email);

This worked fine on our Staging server (hosted on Supabase) and a few or our other instances, but this fails on newer instances on Supabase. This manifests as a timeout of the edge function on Netlify and the following error in the Auth logs on Supabase:

with PII removed:

{"auth_event":{"action":"user_invited","actor_id":"00000000-0000-0000-0000-000000000000","actor_username":"service_role","actor_via_sso":false,"log_type":"team","traits":{"user_email":"XXXXXXXXXX","user_id":"505ba316-1842-43ca-88ae-5e68744774c4"}},"component":"api","error":"database error on committing or rolling back transaction: sql: transaction has already been committed or rolled back","level":"error","method":"POST","msg":"Unhandled server error: database error on committing or rolling back transaction: sql: transaction has already been committed or rolled back","path":"/invite","referer":"XXXXXX","remote_addr":"XXXXXX","request_id":"8a22c45ba3f9011d-ORD","time":"2024-07-12T17:31:38Z"}

I have run through a number of the instances and the major difference between the ones that work and those that don't appears to be the version of Postgres and PostgREST, with Postgres 15.1.0.x and PostgREST 11.2.x working and instances running Postgres 15.1.1.x and PostgREST 12.x.x failing. I realize that PostgREST is probably not the culprit here but include the info in the spirit of completeness:

Instance	Auth	PostgREST	Postgres	Works?
Staging	2.154.2	11.2.0	15.1.0.122	Yes
B---	2.154.2	12.2.1	15.1.1.76	No
C---	2.154.2	12.0.2	15.1.1.33	No
T---	2.154.2	12.2.1	15.1.1.76	No
E---	2.154.2	12.0.2	15.1.1.25	No
E---	2.154.2	11.2.2	15.1.0.133	Yes
E---	2.154.2	11.2.0	15.1.0.116	Yes
V---	2.154.2	11.2.2	15.1.0.137	Yes
P---	2.155.1	12.0.2	15.1.1.25	No
P---	2.154.2	12.0.2	15.1.1.25	No
E---	2.154.2	12.0.2	15.1.1.25	No
P---	2.155.1	12.0.2	15.1.1.28	No
M---	2.154.2	12.0.2	15.1.1.33	No

To Reproduce

No really fast repro here beyond what I cited above.

Expected behavior

I expect all valid calls that we make to the Supabase platform to work consistently across all instances.

Screenshots

NA

System information

• "@supabase/supabase-js": "2.43.6"

[encima]

Thanks for this, I will transfer this to the Auth repo for easier tracking.

The error message you are seeing is typically due to issues of duplicate calls. Are you able to see if the same call is being made twice or potentially the first one is failing during execution?

[lwjameson]

Hi Auth,

So I have tracked down the issue to using port 25 for our custom SMTP server. The ones that were working were using port 587.

Very recently this message started appearing in the SMTP setup panel:

This would have been in the last week or so. Changing the port to 2525 (CRAM-MD5) fixed the issue. As port 25 (TLS) was working before, I have to assume that this is a recent change.

If port 25 is actually no longer supported by the custom SMTP service, the note seems a little soft. I am pretty sure that our SMTP provider is not blocking port 25. If Supabase's infrastructure blocks outgoing port 25 requests, it would probably be better to have a more dire message here.

I will not close the ticket in order to give my feedback about the documentation but the issue is resolved on our end.

lwjameson

[saka01]

Facing exact issue here, it worked after I disabled smtp on supabase.

I am using spacemail for smtp and their mails has to sent through https but I was expecting it to work through localhost which is http. So it didn’t work.

When I pushed my changes to prod with smtp, it worked fine

[J0]

@saka01

Thanks for circling back. It makes sense that it works after disabling SMTP as it should default to the Supabase

Quick check - does port 465 work for you? one of the underlying libraries we use only enforces ssl on port 465 so if there is a hard requirement you may have to use port 465