Fix duplicate tags in linkwarden

linkwarden/linkwarden#520

I recently deployed Linkwarden in my homelab stack. After giving access to a few friends, we noticed that tags were getting duplicated whenever someone added an existing tag to a link created by someone else. This is a known, still-unfixed issue on GitHub (#520).

Simple and quick fix

The script below merges duplicate tags directly in the database it keeps the oldest tag, reassigns all links to it, and removes the duplicates:

DO $$
DECLARE
    dup RECORD;
    keep_id INTEGER;
    dup_id INTEGER;
BEGIN
    FOR dup IN
        SELECT name, MIN(id) as keep_id, array_agg(id) FILTER (WHERE id != (SELECT MIN(id) FROM "Tag" t2 WHERE t2.name = "Tag".name)) as dup_ids
        FROM "Tag"
        GROUP BY name
        HAVING COUNT(*) > 1
    LOOP
        keep_id := dup.keep_id;
        FOREACH dup_id IN ARRAY dup.dup_ids
        LOOP
            INSERT INTO "_LinkToTag" ("A", "B")
            SELECT "A", keep_id FROM "_LinkToTag" WHERE "B" = dup_id
            ON CONFLICT DO NOTHING;
            DELETE FROM "_LinkToTag" WHERE "B" = dup_id;
            DELETE FROM "Tag" WHERE id = dup_id;
            RAISE NOTICE 'Merged tag % into %', dup_id, keep_id;
        END LOOP;
    END LOOP;
END $$;

Automating it

Since I’m not the only one using the instance, I needed something that runs on its own rather than manual cleanup. A simple crontab does the job so I run it every hour:

0 * * * * docker exec link-postgres-1 psql -U postgres -d postgres -c "$(cat xxxx/doublon.sql)" >> xxxx/merge_tags.log 2>&1

CrowdSec banning itself off my own server

Separately, I ran into an issue with CrowdSec’s AppSec/WAF module being, it was too wild it ended up banning me from my own server TT. The fix was adding a custom exclusion rule at /data/crs-plugins/linkwarden/linkwarden-rules-exclusions-before.conf:

SecRule SERVER_NAME "@beginsWith links" \
    "id:9910004,\
    phase:1,\
    pass,\
    nolog,\
    setvar:'tx.allowed_methods=GET HEAD POST PUT DELETE PATCH OPTIONS'"