Posit Workbench: Remove User Information from SQL Lite Database


NOTE: We are working to add this function in to Workbench. 

When a user leaves your organisation, it's good security practice to remove their accounts. This is normally done at an IdP level or by removing their Linux account if using PAM authentication. Posit Workbench retains user information in the SQL Lite database after a user as been removed elsewhere.

These users can be locked by an administrator in the administrative dashboard, but it may be necessary in some organisations to remove references entirely. To remove user data from the database you can follow these steps.

It is important to remember that removing users from the database cannot be undone so caution should be used when manipulating the database directly.
1.  Backup the database
You can backup your sqlite database by copying the file /var/lib/rstudio-server/rstudio.sqlite to a new location before proceeding. e.g.

cp /var/lib/rstudio-server/rstudio.sqlite /tmp/rstudio.sqlite.bkp

2.  Connect to the database. e.g.

sudo sqlite3 /var/lib/rstudio-server/rstudio.sqlite

3.  Verify the user you want to remove.

sqlite> SELECT * FROM licensed_users WHERE user_name = '$USERNAME';

Where $USERNAME is the user's posix username. Verify that the row returned matches the user that you wish to delete.
4.  Remove the user from the database.

sqlite> DELETE FROM licensed_users WHERE user_name = '$USERNAME';

5.  Remove files owned by removed user.
There may also be leftover files in /var/lib/rstudio-server and /var/lib/rstudio-launcher that were owned by the user that can be removed. Since the user has already been removed from the system, a search for folders and files that have no owner can be completed with the following command:

find / -nouser -o -nogroup 2> /dev/null

You can remove these files and directories using the rm command for files and the rmdir command for directories

6. Restart Posit Workbench

sudo rstudio-server restart