|1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55|
Time: 2011/08/20 16:19:57 GMT+0
|Note: mention mysql invocation|
added: mysql savane # on internal subhost
Savannah relies on the Savane software to interface users and system administration. Savane manages a list of services for each project, and a list of project members who can access them (some of them are project admins and can add or remove members).
Savane also provide a flexible bug/task/support tracker, with mail notifications, field transition hooks, fine-grained permissions, query forms, squads, and more.
This page is the Savane Tasks List.
At Savannah, we rely on the 'savane-cleanup' branch: http://savannah.gnu.org/projects/savane-cleanup The reference repository is located at: http://git.sv.gnu.org/gitweb/?p=savane-cleanup.git;a=summary Read doc/devel/DEVEL and doc/devel/CLEANUP to see what's in progress or recently added.
The live checkout is /usr/src/savane on the frontend vhost.
Here are a few light / quick tasks for a shallow dive into the code :)
- https://savannah.gnu.org/support/?group=administration mentions several minor bugs that are mainly due to PHP E_STRICT warnings, waiting for fixes. This will make you understand how the code works. Assigned to:
- RMS also expressed interest in this small-sized improvement:
- Provide a description for all repositories of all SCMs? in use, so user can understand which ones are experimental, or mirrors (such as Emacs' Git mirror), or canonical/reference, etc. Assigned to:
- Savane attempts to reproduce the good i18n practice from desktop apps, including setting the date format using browser-settings autodetection (i.e. sent in HTTP requests headers). https://savannah.gnu.org/support/?106026 shows that, in the context of the Internet and of international communities, this can still be confusing. While Savane had historically a strong stand against reimplementing i18n features that are already provided by the browser, it appears it is necessary to do so nonetheless. In short: add an option for users to select their date format, including ISO 8601 or 200X-MM-DD, or any other non-confusing format. That option would override the browser settings for the Savane website. (Note: currently experimenting %c for long dates (rather than %a %x, %R) and %Y-%m-%d for short dates)
- Fix e-mail obfuscation in news Atom feeds - you cannot add contact info because of this!
Savannah is getting more spam.
Problem spam on Savannah comes not from email spam but instead in the form of web posts (aka spamdexing). Currently there's a simple "type 421" captcha-style box for anonymous posters. This seems to work well. But spammers now tend to create user accounts anyway. After creating an account there is no longer any automated blocks to posting and this includes posting spam. A voting mechanism to remove spam exists. Project owners can vote spam out in one vote. Others require multiple votes to hide posted spam.
Other mecanisms are implemented in Savane, and used at gna.org in particular, but are not used at Savannah. They include so-called "IP blacklists" (aka DNSBL), but they are blocking legitimate people from login in, because black lists tend to be more and more "white lists" (that is, wide IP ranges are blacklisted and people need to do some work and prove their good will to get unlisted, which is essentially whitelisting). DNSBL is often used for e-mail spam, and not so many people have their own mail server, so false positives are limited, but when it's used for spamdexing, more legitimate people are impacted.
It would be helpful to get tools to analyse incoming comments, and produce statistics about which tools are more efficient against spammers (post URL timeouts, URL blacklists, etc.). At the same time it would also be great to have efficient post-moderation tools, so remove all a user's comments at once, for instance. Efforts should be made so it doesn't turn into an efficient censorship tool...
https://savannah.gnu.org/siteadmin/spamlist.php is a starting point for improvements.
To better understand the tables structure, here are a few manually queries (Note: several actions are already implemented in the site admin interface, such as detect suspect users and IP addresses, and ban users or unban IP addresses):
mysql savane # on internal subhost -- Show all content posted by user #67738 mysql> SELECT "bugs_p", summary,details FROM bugs WHERE submitted_by=67738 UNION SELECT "support_p",summary,details FROM support WHERE submitted_by=67738 UNION SELECT "task_p", summary,details FROM task WHERE submitted_by=67738 UNION SELECT "patch_p", summary,details FROM patch WHERE submitted_by=67738 UNION SELECT "bugs_c", field_name,old_value FROM bugs_history WHERE mod_by=67738 AND field_name='details' UNION SELECT "support_c",field_name,old_value FROM support_history WHERE mod_by=67738 AND field_name='details' UNION SELECT "task_c", field_name,old_value FROM task_history WHERE mod_by=67738 AND field_name='details' UNION SELECT "patch_c", field_name,old_value FROM patch_history WHERE mod_by=67738 AND field_name='details' UNION SELECT "forum",subject,body FROM forum WHERE posted_by=67738; -- Mark all tasks posts from user '64322' as spam mysql> UPDATE task_history SET spamscore=spamscore+5 WHERE mod_by='64322' AND field_name='details'; -- Same for all trackers at once: UPDATE bugs_history SET bugs_history.spamscore=bugs_history.spamscore+5 WHERE field_name='details' AND mod_by='64322'; UPDATE task_history SET task_history.spamscore=task_history.spamscore+5 WHERE field_name='details' AND mod_by='64322'; UPDATE patch_history SET patch_history.spamscore=patch_history.spamscore+5 WHERE field_name='details' AND mod_by='64322'; UPDATE support_history SET support_history.spamscore=support_history.spamscore+5 WHERE field_name='details' AND mod_by='64322'; -- Disable user #64322 mysql> UPDATE user SET status='S' WHERE user_id='64322'; -- Mark all new bugs from user '66862' as spam mysql> UPDATE bugs SET spamscore=spamscore+5 WHERE submitted_by='66862'; -- Mark all posts in task #3491 posted after January 1st 2005 as spam mysql> UPDATE task_history SET spamscore=spamscore+5 WHERE task_history.field_name='details' AND bug_id='3491' AND date > UNIX_TIMESTAMP('2005-01-01'); -- Mark all posts from 22.214.171.124 over the last 24h as spam UPDATE support_history,spam_stats SET spamscore=spamscore+5 WHERE support_history.bug_id=spam_stats.bug_id AND support_history.field_name='details' AND spam_stats.date > ADDDATE(NOW(), -1) AND support_history.ip='126.96.36.199'; -- Remove forum posts from user #69022 DELETE FROM forum WHERE posted_by = 69022; -- Remove mail notifications from deleted users DELETE FROM bugs_cc WHERE email IN (SELECT user_id FROM user WHERE email="firstname.lastname@example.org"); DELETE FROM patch_cc WHERE email IN (SELECT user_id FROM user WHERE email="email@example.com"); DELETE FROM support_cc WHERE email IN (SELECT user_id FROM user WHERE email="firstname.lastname@example.org"); DELETE FROM task_cc WHERE email IN (SELECT user_id FROM user WHERE email="email@example.com");
An improved interface would show an overview all of a user's post (bug/task/etc. tracker items, forums/news comments), and offer to treat the account as a spammer's, similar to shown above.
Starting points from general purposes anti-spam tools:
- Nofollow revisited suggests not to believe in rel=nofollow
- Bad Behavior is GPL'd code that runs before the main application code and issues a series a generic tests before allowing the rest of the application to load. It's architecture is remotely similar to mod_security's. It provides logging, and shouldn't be very hard to plug in Savane.
- WordPress? has a 'Spam Karma 2' module which implements a number of general purpose tests; see the list of modules. With minimum effort, the tests could be applied on Savane posts.
Spam analysis on Savannah: I (Beuc) added a table called 'spam_stats' that logs all posted items and comments. See the patch and table structure at /vservers/frontend/usr/src/savane-spam_stats.*.
-- * Approximation for anonymous spam + anonymous users who mistyped the captcha * -- User was not logged and check_value is wrong SELECT COUNT(*) FROM spam_stats WHERE (user_id IS NULL AND (check_value != '421' OR check_value IS NULL)); -- Idem, and in addition the posted text contains an HTTP link SELECT COUNT(*) FROM spam_stats WHERE (user_id IS NULL AND (check_value != '421' OR check_value IS NULL)) AND details LIKE "%http://%"; -- Validated post (validated captcha or authentified) SELECT count(*) FROM spam_stats WHERE user_id IS NOT NULL OR (user_id IS NULL AND check_value=421); -- Posts per suspicious IP SELECT ip, count(*) AS sum FROM spam_stats WHERE (check_value IS NOT NULL OR check_value != '421') AND details LIKE "%http%" GROUP BY ip ORDER BY sum; -- Average posts by suspicious IP SELECT AVG(sum) FROM (SELECT ip, count(*) AS sum FROM spam_stats WHERE (check_value IS NOT NULL OR check_value != '421') AND details LIKE "%http%" GROUP BY ip ORDER BY sum) AS average; -- Posts per IP suspicious and per day SELECT DATE_FORMAT(date, '%Y-%m-%d') AS day,ip, count(*) AS sum FROM spam_stats WHERE (check_value IS NOT NULL OR check_value != '421') AND details LIKE "%http%" GROUP BY day,ip ORDER BY day,sum; -- Number of different IP adresses SELECT COUNT(DISTINCT ip) FROM spam_stats; -- Average number of different apparently-spamming IP adresses per day SELECT AVG(dist_ip) FROM (SELECT DATE_FORMAT(date, '%Y-%m-%d') AS day,count(distinct ip) AS dist_ip FROM spam_stats WHERE (check_value IS NOT NULL OR check_value != '421') AND details LIKE "%http%" GROUP BY day ORDER BY day,dist_ip) AS ips_per_day;
Check for an analysis after ~1 day: https://savannah.gnu.org/support/index.php?106304#comment9
Conclusion: distributed and dumb for the most part (95%), and %5 remaining distributed and clever.
Apparently we need to be a bit more challenging for the 5% one. Or, we need to accept there will be 5% clever spam anyway and filter them after-the-fact.
TODO: better analyse 5% remaining posts
Ideas (more challenging):
- text captcha or textcha - http://moinmo.in/HelpOnTextChas
- beware: i18n
- cons: our site configuration is public - make answer as regexp instead of strings, so they can't be copy/pasted
- http://blogspam.net/ is a daemon that you query through XML-RPC and implement a set of basic tests, namely URL block-lists and max-urls. (It also includes less interested test such as bayesian filter (which is meant to work on 50/50 ratios and requires checking false positives), size limits, etc.). It was published this month (1/2009) and currently lacks documentation.
- reduce number of links per posts + surge protection (limit msgs/min)
- URL block-lists?
- randomized text fields, this prevents reusing the form
- beware: pre-filling the fields on error
- limit: can't spammers just grab the form and fill-in the fields in order, like humans do, only with garbage? Does this really help? We already have form ids to prevent double-posting forms. Randomizing the form name and URL may be more effective in that regard, unless the spammer knows that he needs to use the 2nd form, whatever its name is.
- cons: needs cookie for anonymous users (not implemented)
- cons: breaks form pre-filling by the browser (since the field name changed). Not really an issue for the tracker though.
- Currently requires 5 spam points (user=1, tracker admin=2, project admin=5), but often normal users don't reach 5 points. Implement a moderation form for admins or site-admins, which would be able to quickly moderate the spam.
- Beware: lack of moderation team, lots of dead projects
- Resource usage: maybe block abusive dumb spammers (e.g. more than 20 failed attempts) for 48h
- Beware: if a harsher blocking behavior is detected, dumb spammers may improve their software; try to make it stealth
- Beware: be lax with blocking, false positives are easy (shared connections with a single IP, etc.); keep the threshold high. It's just for efficiency, those plain spammers are already blocked.
- accessibility issues
- clever spammers (the 5% we track) know how to read captcha
- I don't like decrypting numbers on screen 20 times per day
- recaptcha: graphic or sound captcha via a webservice: no server-side source code
- give less privileges to anonymous users:
- it discourages contribution (forces to create an account, remember password, etc.)
- some spammers already create accounts, so don't feel safe because the user is authenticated, it can very well be a spammer nevertheless
- akisnet (or something, no need to advertise): interesting idea based on centralization and cross-site analysis, via webservice (somewhat similar to Razor/Pyzor); but this is essentially a proprietary external solution; we would also need to paid a monthly fee since we're bigger than a classic blog. A free software implementation would need a huge traffic too to get consistent and reactive detection.
- automatically building a list of blocked URLs from content flagged as spam: spammers may mix some legitimate URLs in their message. When trying this approach, 'bugs.debian.org' appeared in my list of blocked URLs... This might be used with some moderation, but this means manually checking each and every URL.
Except for CVS and SVN, the VCSes? we provide can easily support several repositories for a single project. For Arch, there's no server-side and it's done directly via SFTP. For Git and Hg, we do it manually on request. So it would be good to have a web interface to manage those additional subrepositories, plus a backend script to create them on the filesystem.
There are several applications for this:
In all cases, the subrepositories need to be clearly parented to the Savannah project; it's not a way to create arbitrary repositories for project that weren't validated through the Savannah project registration procedure.
Discussion (TODO: sum it up in this wiki page): http://lists.gnu.org/archive/html/savannah-hackers-public/2008-06/msg00027.html
Apparently Tuxfamily/VHFFS has support for this: http://faq.tuxfamily.org/GIT/En
Initial post with discussions: https://savannah.gnu.org/support/?105668
- Defined manually: can we trust the user? Maybe check if e-mails match?
- Quick 'n dirty: everything as anonymous, with the original user specified as a comment
Existing implementations (how do they adress the points above?):
- https://sourceforge.net/export/xml_export.php?group_id=GROUPID (project admin only)
- https://sourceforge.net/export/xml_export2.php?group_id=GROUPID (requires project only)
- http://sourceforge.net/export/sf_tracker_export.php?atid=TRACKERID&group_id=GROUPID (public)
[Note: the Emacs people are shamelessly setting up a separate debbugs instance, so this may not be urgent]?
RMS also has interest in this one, because he's often offline. He cannot just set down for an hour and browse the Savannah website, but he could prepare a batch of trackers mail directives and send them all at once when online.
The main problem I can see is spam. We already get spamdexing on trackers, I can only image what we'll get through a mail interface... Studying the Debian BTS (Bug Tracker System - bugs.debian.org), which is entirely driven by mail and receives little spam, would probably prove interesting. This post http://lists.debian.org/debian-devel-announce/2007/10/msg00004.html has a couple interesting links, namely to their configuration files.
A note on permissions: the Savane trackers currently rely on heavy authentication and authorization (you may need to have a Savannah account, and then you may need to be "technician" or "admin" on the tracker to perform some actions). I do not see how to implement it for a mail interface. GPG might be used, but is not really suitable for that purpose. One suggestion is to simply disable tracker permissions if the tracker enables the mail interface for it - and proeminently notify the user about that fact. Again, the Debian BTS relies on a similar setup without apparent troubles, but this may need closer investigation.
- look for a way to easily invite people (mailing list command?)
We need to provide users with a web interface to register on-commit hooks in their repository. For CVS, we already offer several hooks: webpages on-commit synchronization (site-wide), commit_prep+log_accum mail notifications (per project, can be used multiple times), cia.vc notification via XMLRPC.
There's already some work done:
- frontend/php/cvs/admin/ in the Savane source code
The code has some limitations. In particular, when removing all hooks at once, hooks are still kept installed in the system (the replication model is flawed). Site-wide hooks are currently hard-coded. Last, the cron job does not deal with new projects (because they don't have registered hooks yet), while it should add the site-wide hooks nonetheless.
Write you own (reasonable) wishes here :)