malicious-package-deletion-audit.py

from collections import defaultdict
import psycopg2
from psycopg2.extras import DictCursor

project_owners = defaultdict(set)
project_maintainers = defaultdict(set)

conn = psycopg2.connect("dbname=journals")
cur = conn.cursor(cursor_factory=DictCursor)
cur.execute("SELECT * FROM journals ORDER BY submitted_date asc")

def normalize_user(user):
    return user.lower().replace(' ', '.')

for journal in cur:
    project_name = journal['name'].lower()
    action = journal['action']
    if action.startswith('add Owner '):
        user = normalize_user(' '.join(action.split()[2:]))
        project_owners[project_name].add(user)
    if action.startswith('add Maintainer '):
        user = normalize_user(' '.join(action.split()[2:]))
        project_maintainers[project_name].add(user)
    if action.startswith('remove Owner '):
        user = normalize_user(' '.join(action.split()[2:]))
        try:
            project_owners[project_name].remove(user)
        except KeyError:
            pass
    if action.startswith('remove Maintainer '):
        user = normalize_user(' '.join(action.split()[2:]))
        try:
            project_maintainers[project_name].remove(user)
        except KeyError:
            pass
    if action.startswith('remove file'):
        action_user = normalize_user(journal['submitted_by'])
        if action_user not in project_owners[project_name] and action_user not in project_maintainers[project_name]:
            if action_user not in ['dstufft', 'ewdurbin', 'deleted-user', 'deleted.user']:
                print('Package maliciously removed by %s'%(action_user))
                print(journal)