class Archive(Base): "Archive" __tablename__ = 'archive' id = Column(BigInteger, primary_key=True) messageid = Column(Unicode(255), index=True) actions = Column(Unicode(255)) clientip = Column(Unicode(128)) date = Column(Date, index=True) from_address = Column(Unicode(255), index=True) from_domain = Column(Unicode(255), index=True) headers = Column(UnicodeText) hostname = Column(UnicodeText) highspam = Column(SmallInteger, default=0, index=True) rblspam = Column(SmallInteger, default=0) saspam = Column(SmallInteger, default=0) spam = Column(SmallInteger, default=0, index=True) nameinfected = Column(SmallInteger, default=0) otherinfected = Column(SmallInteger, default=0) isquarantined = Column(SmallInteger, default=0, index=True) isarchived = Column(SmallInteger, default=0, index=True) sascore = Column(Float) scaned = Column(SmallInteger, default=0, index=True) size = Column(Integer) blacklisted = Column(SmallInteger, default=0, index=True) spamreport = Column(UnicodeText) whitelisted = Column(SmallInteger, default=0, index=True) subject = Column(UnicodeText) time = Column(Time(timezone=True)) timestamp = Column(TIMESTAMP(timezone=True), server_default=utcnow(), index=True) to_address = Column(Unicode(255), index=True) to_domain = Column(Unicode(255), index=True) virusinfected = Column(SmallInteger, default=0) ts = Column(TIMESTAMP(timezone=True), server_default=utcnow()) msgfiles = Column(UnicodeText) def __init__(self, messageid): "init" self.messageid = messageid __mapper_args__ = {'order_by': timestamp} @property def isdangerous(self): "Check if the message is dangerous" if self.virusinfected or self.otherinfected or self.nameinfected: return True return False
class AuditLog(Base): "Audit Log items" __tablename__ = 'auditlog' id = Column(BigInteger, primary_key=True) username = Column(Unicode(255)) category = Column(SmallInteger, default=1, index=True) info = Column(UnicodeText) hostname = Column(UnicodeText) remoteip = Column(UnicodeText) timestamp = Column(TIMESTAMP(timezone=True), server_default=utcnow()) __mapper_args__ = {'order_by': timestamp} def __init__(self, username, category, info, hostname, remoteip, timestamp=None): "init" self.username = username self.category = category self.info = info self.hostname = hostname self.remoteip = remoteip if timestamp: self.timestamp = timestamp # @property def tojson(self): "JSON friendly format" return dict(username=self.username, category=unicode(CATEGORY_MAP[self.category]), info=self.info, hostname=self.hostname, remoteip=self.remoteip, timestamp=str(self.timestamp.strftime('%Y-%m-%d %H:%M')))
class IndexerCounter(Base): "Indexing counters" __tablename__ = 'indexer_counters' tablename = Column(Unicode(255), primary_key=True) maxts = Column(TIMESTAMP(timezone=True), server_default=utcnow(), index=True, nullable=False)
class IndexerKillList(Base): "Keyword kill list" __tablename__ = 'indexer_killlist' id = Column(BigInteger, primary_key=True) ts = Column(TIMESTAMP(timezone=True), server_default=utcnow(), nullable=False, primary_key=True) tablename = Column(Unicode(255), nullable=False, primary_key=True)
class ResetToken(Base): "Password reset token" __tablename__ = 'passwdtokens' id = Column(Integer, primary_key=True) token = Column(UnicodeText, unique=True) timestamp = Column(TIMESTAMP(timezone=True), server_default=utcnow()) used = Column(Boolean(), default=False) user_id = Column(Integer, ForeignKey('users.id')) def __init__(self, token, user_id): "Accept token arg" self.token = token self.user_id = user_id
class MailQueueItem(Base): "Mail queue items" __tablename__ = 'mailq' id = Column(Integer, primary_key=True) messageid = Column(Unicode(255)) timestamp = Column(DateTime(timezone=True), server_default=utcnow()) from_address = Column(Unicode(255), index=True) to_address = Column(Unicode(255), index=True) from_domain = Column(Unicode(255), index=True) to_domain = Column(Unicode(255), index=True) subject = Column(UnicodeText) hostname = Column(UnicodeText) size = Column(Integer) attempts = Column(Integer) lastattempt = Column(DateTime(timezone=True), server_default=utcnow()) direction = Column(SmallInteger, default=1, index=True) reason = Column(UnicodeText) flag = Column(SmallInteger, default=0) __mapper_args__ = {'order_by': timestamp} def __init__(self, messageid): self.messageid = messageid
class MessageStatus(Base): "Message delivery status records" __tablename__ = 'messagestatus' id = Column(BigInteger, primary_key=True) messageid = Column(Unicode(255)) hostname = Column(UnicodeText) ipaddress = Column(Unicode(128)) port = Column(Integer) confirmation = Column(UnicodeText) errorno = Column(Integer, server_default='0') errorstr = Column(UnicodeText, server_default=u'') timestamp = Column(TIMESTAMP(timezone=True), server_default=utcnow()) __mapper_args__ = {'order_by': timestamp}
class Release(Base): "quarantine release record" __tablename__ = 'releases' id = Column(BigInteger, primary_key=True) messageid = Column(BigInteger) uuid = Column(String(128), unique=True) timestamp = Column(TIMESTAMP(timezone=True), server_default=utcnow()) released = Column(Boolean(), default=False) __mapper_args__ = {'order_by': id} def __init__(self, messageid, uuid): "init" self.messageid = messageid self.uuid = uuid
class User(Base): """User Model""" __tablename__ = "users" id = Column(BigInteger, primary_key=True) username = Column(Unicode(255), unique=True) firstname = Column(Unicode(255)) lastname = Column(Unicode(255)) __password = Column('password', Unicode(255)) email = Column(Unicode(255), unique=True) timezone = Column(Unicode(255), default=u'UTC') account_type = Column(SmallInteger(), default=3, index=True) created_on = Column(DateTime(timezone=True), server_default=utcnow()) last_login = Column(DateTime(timezone=True), server_default=utcnow()) active = Column(Boolean(), default=True) local = Column(Boolean(), default=False) send_report = Column(Boolean(), default=True) spam_checks = Column(Boolean(), default=True) low_score = Column(Float(), default=0.0) high_score = Column(Float(), default=0.0) lists = relationship('List', backref='user', cascade='delete, delete-orphan') addresses = relationship('Address', backref='user', cascade='delete, delete-orphan') domains = relationship('Domain', secondary=domain_users, backref='users') organizations = relationship('Group', secondary=organizations_admins, backref='admins') signatures = relationship('UserSignature', backref='user', cascade='delete, delete-orphan') __mapper_args__ = {'order_by': id} def __init__(self, username, email): "init" self.username = username self.email = email def set_password(self, password): "sets the password to a hash" self.__password = bcrypt.hashpw(password, bcrypt.gensalt()) def validate_password(self, password): "validate the password" if isinstance(password, unicode): password = password.encode('utf-8') return (self.active and self.local and password and (bcrypt.hashpw(password, self.__password) == self.__password)) def to_csv(self): "return only csv attributes" csvdict = {} for field in [ 'username', 'email', 'account_type', 'active', 'firstname', 'lastname', 'send_report', 'spam_checks', 'low_score', 'high_score', 'timezone' ]: try: csvdict[field] = str(getattr(self, field)) except UnicodeEncodeError: value = getattr(self, field) csvdict[field] = value.encode('utf-8') return csvdict def tojson(self): "Return JSON" if self.account_type == 1: user_icon = 'imgs/user_admin.png' elif self.account_type == 2: user_icon = 'imgs/user_dadmin.png' else: user_icon = 'imgs/user.png' return dict( id=self.id, username=self.username, fullname=self.firstname + ' ' + self.lastname, email=self.email, userimg=user_icon, statusimg='imgs/tick.png' if self.active else 'imgs/minus.png') def apijson(self): "Return JSON for the API" mdict = {} for attr in [ "id", "username", "firstname", "lastname", "email", "timezone", "account_type", "created_on", "last_login", "active", "local", "send_report", "spam_checks", "low_score", "high_score" ]: if isinstance(getattr(self, attr), datetime.datetime): mdict[attr] = getattr(self, attr)\ .strftime('%Y:%m:%d:%H:%M:%S') else: mdict[attr] = getattr(self, attr) mdict['addresses'] = [ dict(id=addr.id, address=addr.address) for addr in self.addresses if addr.enabled ] mdict['domains'] = [ dict(id=dom.id, name=dom.name) for dom in self.domains if dom.status ] mdict['organizations'] = [ dict(id=org.id, name=org.name) for org in self.organizations ] return mdict @property def is_superadmin(self): "Check if user is super admin" return self.account_type == 1 @property def is_admin(self): "Check if user is an admin" return self.account_type == 1 or self.account_type == 2 @property def is_domain_admin(self): "Check if user is a domain admin" return self.account_type == 2 @property def is_peleb(self): "Check if user is just an ordinary joe" return self.account_type == 3 @property def orgs(self): """Return the orgs the user belongs to""" if self.account_type == 2: return [org.id for org in self.organizations] else: return []
class Message(Base): "Message model" __tablename__ = 'messages' id = Column(BigInteger, primary_key=True) messageid = Column(Unicode(255), index=True, unique=True) actions = Column(Unicode(128)) clientip = Column(Unicode(128)) date = Column(Date(timezone=True), index=True) from_address = Column(Unicode(255), index=True) from_domain = Column(Unicode(255), index=True) headers = Column(UnicodeText) hostname = Column(UnicodeText) highspam = Column(SmallInteger, default=0, index=True) rblspam = Column(SmallInteger, default=0) saspam = Column(SmallInteger, default=0) spam = Column(SmallInteger, default=0, index=True) nameinfected = Column(SmallInteger, default=0) otherinfected = Column(SmallInteger, default=0) isquarantined = Column(SmallInteger, default=0, index=True) isarchived = Column(SmallInteger, default=0, index=True) sascore = Column(Float, index=True) scaned = Column(SmallInteger, default=0, index=True) size = Column(Integer) blacklisted = Column(SmallInteger, default=0, index=True) spamreport = Column(UnicodeText) whitelisted = Column(SmallInteger, default=0, index=True) subject = Column(UnicodeText) time = Column(Time(timezone=True)) timestamp = Column(TIMESTAMP(timezone=True), server_default=utcnow(), index=True) to_address = Column(Unicode(255), index=True) to_domain = Column(Unicode(255), index=True) virusinfected = Column(SmallInteger, default=0) ts = Column(TIMESTAMP(timezone=True), server_default=utcnow()) def __init__(self, messageid): "init" self.messageid = messageid __mapper_args__ = {'order_by':timestamp} @property def tojson(self): "Serialize to json" return dict( id=self.id, messageid=self.messageid, actions=self.actions, clientip=self.clientip, date=str(self.date), from_address=self.from_address, from_domain=self.from_domain, headers=self.headers, hostname=self.hostname, highspam=self.highspam, rblspam=self.rblspam, saspam=self.saspam, spam=self.spam, nameinfected=self.nameinfected, otherinfected=self.otherinfected, isquarantined=self.isquarantined, isarchived=self.isarchived, sascore=self.sascore, scaned=self.scaned, size=self.size, blacklisted=self.blacklisted, spamreport=self.spamreport, whitelisted=self.whitelisted, subject=self.subject, time=str(self.time), timestamp=str(self.timestamp), to_address=self.to_address, to_domain=self.to_domain, virusinfected=self.virusinfected, ) @property def json(self): "recent messages json" value = 'white' if (self.spam and not self.highspam and not self.blacklisted and not self.nameinfected and not self.otherinfected and not self.virusinfected): value = 'spam' if self.highspam and (not self.blacklisted): value = 'highspam' if self.whitelisted: value = 'whitelisted' if self.blacklisted: value = 'blacklisted' if self.nameinfected or self.virusinfected or self.otherinfected: value = 'infected' if not self.scaned: value = 'gray' if (self.spam and (not self.blacklisted) and (not self.virusinfected) and (not self.nameinfected) and (not self.otherinfected)): status = _('Spam') if self.blacklisted: status = _('BS') if (self.virusinfected or self.nameinfected or self.otherinfected): status = _('Infected') if ((not self.spam) and (not self.virusinfected) and (not self.nameinfected) and (not self.otherinfected) and (not self.whitelisted)): status = _('Clean') if self.whitelisted: status = _('AS') if not self.scaned: status = _('NS') return dict( id=self.id, timestamp=str(self.timestamp), sascore=self.sascore, size=format_byte_size(self.size), subject=escape(truncate((self.subject and self.subject.strip()) or '---', 50)), from_address=escape(wrap_paragraphs(self.from_address, 32)), to_address=escape(wrap_paragraphs(self.to_address or '---', 32)), style=value, status=status, )