Updating an IdP using PATCH or PUT with a 6k applications or greater may fail

[spwitt]

Updating an IdP using PATCH or PUT with a 6k applications or greater may fail

Description

PostgreSQL uses a 2 byte int (short) for parameterized type. When creating or updating (PUT or PATCH) an Identity Provider with 8.5k+ applications, the number of application related configurations will exceed this limitation of 32,767 parameterized types. The application configuration has 4 fields, 5 if apple - so 6k * 5 > 32,767.

We may be able to hit this error for managed domains, application config and tenant configuration per IdP.

• Managed domains has 2 parameters, so we can bulk insert up to ~ 16k at a time. Unlikely we'll hit this limitation, but we should protect against it.
• Application configuration has 4, and if Apple, 5, so we can bulk insert up to ~6k at a time. This is easy to hit once you have more than 6k applications.
• Tenant configuration has 3, so we can bulk insert ~ 10k at a time. This one is less likely to hit - because we don't require a configuration per tenant. The user would have to configure this per tenant. But we should still account for this possibility.

The likely one to hit is the application configuration.

Example exception you may see:

### Error updating database.  Cause: org.postgresql.util.PSQLException: PreparedStatement can have at most 65,535 parameters. Please consider using arrays, or splitting the query in several ones, or using COPY. Given query has 66,000 parameters

We need to audit any other path using a <foreach to ensure we always account for this same limitation.

Note that

Note that for the PostgreSQL JDBC driver version 42.4.0 and beyond, they are now using an unsigned int, so for this to occur, we have to exceed 65,536 parameters. This will occur after you have 16k+ applications, or a bit earlier if you're editing an Apple IdP which has 5 parameters instead of 4.

• https://jdbc.postgresql.org/changelogs/2022-06-09-42.4.0-release/
• fix: support queries with up to 65535 (inclusive) parameters pgjdbc/pgjdbc#2525
• How to resolve the "Tried to send an out-of-range integer as a 2-byte value" error pgjdbc/pgjdbc#1311

Notes on MySQL limitation, or lack thereof.

• https://stackoverflow.com/a/6582902/3892636

Affects versions

TBD, likely all versions that have the IdP API when the number of applications exceed N?

• Only affects you if you are running PostgreSQL, MySQL does not have this limitation. MySQL is likely using a 4 byte int, and if that is the case, there is a limitation, but it is much larger - such as 2,147,483,648.

Steps to reproduce

Here is just one example where this can be an issue:

1. Create around 8.5k+ applications
2. Update an IdP
3. The query to save application IdP configurations will fail because it inserts all of the records in a single query

Expected behavior

No failures. FusionAuth needs to internally handle this limitation and adjust the UPDATE statement.

Platform

• PostgresSQL

Related

• Bulk upsert for login aggregator may fail for larger interval counts #1854

Community guidelines

All issues filed in this repository must abide by the FusionAuth community guidelines.

[robotdan]

Internal:

• https://github.com/FusionAuth/fusionauth-app/pull/250