CKAN: Ticket #1341: Delete spam users from ckan

status changed; resolution set

kindly — Wed, 14 Sep 2011 10:32:48 GMT

status changed from new to closed
resolution set to fixed

This was run to delete the users and their mistaken revisions that where created.

BEGIN;
delete from revision r where r.id in (
    select r.id from "user" join revision r on r.author = "user".name
    left join resource_revision rr on rr.revision_id = r.id
    left join package_revision pr on pr.revision_id = r.id
    left join group_revision gr on gr.revision_id = r.id
    where "user".created between '2011-08-15' and '2011-09-06'
    and gr.id is null and rr.id is null and pr.id is null
    and ("user".name similar to '%[0-9]' or "user".fullname similar to '[A-Z][a-z]*[A-Z]%')
    and "user".name not like 'http%'
);
delete from "user" u where u.id in (
    select "user".id from "user"
    left join revision r on r.author = "user".name
    where r.id is null and "user".created between '2011-08-15' and '2011-09-06'
    and ("user".name similar to '%[0-9]' or "user".fullname similar to '[A-Z][a-z]*[A-Z]%')
    and "user".name not like 'http%'
);
COMMIT;

dread — Wed, 26 Oct 2011 17:19:33 GMT

The majority of our 6000 users are still spammers. Can we simply elimenate those who've not made any revisions? Even if we take a few genuine users with it, these are pretty dead accounts anyway.

status changed; resolution deleted

dread — Thu, 27 Oct 2011 10:06:37 GMT

status changed from closed to reopened
resolution fixed deleted

Reopening - this is still a serious problem. We can't link to the user page because it is so embarrassing: http://thedatahub.org/user

milestone changed

kindly — Tue, 01 Nov 2011 09:59:00 GMT

milestone changed from ckan-sprint-2011-09-12 to ckan-backlog