[BUG] Connect migration fails with NULL constraint

Problem

In versions of Posit Connect prior to 2024.09.0, deleting a user may not properly remove associated API keys. When upgrading to Connect 2024.09.0 or later, these orphaned API keys can cause unexpected behavior or database inconsistencies.
 

Steps to reproduce

1. Ensure that you are on a version of Connect prior to 2024.09.0.
2. Create a user and an API key for that user.
3. Delete the user.
4. Upgrade to Connect 2024.09.0 or later.

Root Cause

Prior to version 2024.09.0, Posit Connect did not consistently remove API keys when a user was deleted, leading to orphaned entries in the database.

Workaround

This issue occurs in Posit Connect versions prior to 2024.09.0. To prevent this from happening in the future, we will need to manually delete users with a null id value.

1. Stop Posit Connect:

   sudo systemctl stop rstudio-connect

2. Back up the Connect Database

   Ensure you create a full backup before making any modifications.

3. Identify Orphaned API Keys:

   Run the following SQL query to find API keys associated with deleted users:

   SELECT api_keys.id, api_keys.user_id 
   FROM api_keys 
   LEFT JOIN users ON api_keys.user_id = users.id 
   WHERE users.id IS NULL;

4. Delete Orphaned API Keys:

   Using the list of keys identified in the previous step, run the following command (replace 3,9,22 with the actual key IDs returned in step 3.  <comma separated>):

   DELETE FROM api_keys WHERE api_keys.id IN (3,9,22);

5. Upgrade Connect:

   Follow the official Posit Connect Upgrade Guide to complete the upgrade process.

Status

A bug report has been opened for this issue, but it has not yet been resolved.