[solved] Session SOGS community server SQL triggers

[Fli]

Here You can find information about https://getsession.org/ messenger open community server (SOGS) SQL triggers to deal with SPAM and to reduce abuse.

A) use tool and triggers list https://codeberg.org/gravel/pysogs-sql-triggers (possibly better, more complete/complex solution)

# Setting up PySOGS SQL CLI:
cd /var/lib/session-open-group-server/ # possibly it is good to install into a folder you regularly backup
git clone https://codeberg.org/gravel/pysogs-sql-cli && cd pysogs-sql-cli; sudo usermod -aG _loki $USER && sudo chmod g+w /var/lib/session-open-group-server/sogs.db && ./test.sh && echo "export PATH="$PWD/bin:\$PATH"" >> ~/.bashrc && source ~/.bashrc

# Installing SQL triggers:
cd /var/lib/session-open-group-server/pysogs-sql-cli/ # go to a directory of the previously installed pysogs-sql-cli
git clone https://codeberg.org/gravel/pysogs-sql-triggers/ && mv pysogs-sql-triggers/sql/*.sqlite sql/

# customizing triggers
View all trigger files and adjust it to your needs if needed. For example look for XXXX and replace by a number and similar.
nano *.sqlite

# Running the tool:
sogsfilters # then use arrow keys, enter or q

# Debugging
sudo journalctl -f # this should follow system, SOGS messages
Try to do an action in Session, which triggers the triggers to see if it worked/no error emitted.

B) if you do not want to use that convenient tool, you may manually use following scripts/triggers (following triggers are only part of what the pysogs-sql-cli offers)

PM/DM disabling:
A)
Disallow only new regular users from sending private messages while allowing older users (helps reducing spam and illegal content spreading) - based on this Oddchat article:

cd /var/lib/session-open-group-server/ && sudo wget -qc  https://raw.githubusercontent.com/slrslr/misc/main/session-sogs-prevent-disable-pm-dm-for-new-regular-users.sh
chmod +x session-sogs-prevent-disable-pm-dm-for-new-regular-users.sh
echo "You can view the file now or run it by executing: ./session-sogs-prevent-disable-pm-dm-for-new-regular-users.sh"

B)
Disallow all regular users from sending private messages:

cd /var/lib/session-open-group-server/ && sudo wget -qc  https://raw.githubusercontent.com/slrslr/misc/main/session-sogs-prevent-disable-pm-dm-for-regular-users.sh
chmod +x session-sogs-prevent-disable-pm-dm-for-regular-users.sh
echo "You can view the file now or run it by executing: ./session-sogs-prevent-disable-pm-dm-for-regular-users.sh"

Deny long message:
Ban user who posts too long text message (by default it is 10 pages of text - 10x1800 characters) - based on this Oddchat article:

cd /var/lib/session-open-group-server/ && sudo wget -qc  https://raw.githubusercontent.com/slrslr/misc/main/session-sogs-ban-large-long-message-poster.sh
chmod +x session-sogs-ban-large-long-message-poster.sh
echo "You can view the file now or run it by executing: ./session-sogs-ban-large-long-message-poster.sh"

Cleanup banned user content:
Delete messages and reactions (from all rooms, except user defined room) of a user who gets banned (meaning someone bans a user and automatically their content is removed from everywhere) - based on this Oddchat article:

cd /var/lib/session-open-group-server/ && sudo wget -qc  https://raw.githubusercontent.com/slrslr/misc/main/session-sogs-delete-banned-user-messages-reactions.sh
chmod +x session-sogs-delete-banned-user-messages-reactions.sh
echo "You can view the file now (and adjust your room name to be  excluded from messages removal) or run it by executing:  ./session-sogs-delete-banned-user-messages-reactions.sh"