delete_user.sh

#!/bin/bash

SERVER="matrix.mycompany.com"

echo -e "\e[97mEnter user you'd like to deactivate\e[0m";
read user

if  [ "$user" == "" ]; then
        echo "User required.";
        exit;
fi

account="@$user:$SERVER";

su postgres -c "psql synapse -c 'DELETE FROM users WHERE name='\''$account'\'';'"
su postgres -c "psql synapse -c 'DELETE FROM user_directory WHERE user_id='\''$account'\'';'"
su postgres -c "psql synapse -c 'DELETE FROM account_data WHERE user_id='\''$account'\'';'"
su postgres -c "psql synapse -c 'DELETE FROM profiles WHERE user_id='\''$user'\'';'"
su postgres -c "psql synapse -c 'DELETE FROM user_external_ids WHERE user_id='\''$account'\'';'"

I'm getting this on many of the commands, except the first one:
ERROR: column "name" does not exist

The table structure seems to have changed :-(
user_directory and account_data now seem to use user_id. "profiles" has a column user_id. But it's not filled with user_ids :-( I don't know whether the table is still in use. You might need to delete the user from that table manually. I've modified the script a bit. Couldnt yet test it though. Gee, this is a mess.

Hello, what's the actual status of this script? Did you manage to amend it to match the current db structure?

I uncommented the last line back in. I compared it with my current database. It should still work with a recent synapse version. I haven't used the script for a while because (fortunately) no people were laid off :-) If anyone discovers additional tables that need to be included, please let me know. This won't delete any messages of those users I guess. You might want to disable the user first and perhaps use the expiration feature to expire old messages... Don't know whether messages properly expire after you removed the user from the database with this script, though.

Matrix is now used with Government agencies here in Germany and our beloved neighbor France AFAIK... I really, really wonder why no one ever brought this topic up before. I imagine this to be a real mess if you have thousands of users...

There is an oidc mapping table also that will give headaches. If you miss user_external_ids you just get 'row not found' on login.

@disconn3ct : If I check that table on our database it is empty. But, alas, we don't have any external users anyway. Probably that's why. So what exactly do you mean by "will give headaches"? Are you proposing that I add the following to my script?

su postgres -c "psql synapse -c 'DELETE FROM user_external_ids WHERE user_id='\''$user'\'';'"

Yes. Otherwise it still references the deleted user and when they attempt to log in again it says row not found. (If future readers just want to prevent login from SSO, disable already does that.)

Thank you so much. I've changed it in the script. I cannot test it though. I don't know whether it should be the full account name ($account) or just the user name ($user). If you have any information on that, I'll be glad to know.

It is user_id, which afaik is always @USER:SYNAPSE

Thanks again, @disconn3ct . I've changed the script again accordingly.