class TokenLog(db.Model):
"""TokenLog class is used for logging changes to access tokens."""
__tablename__ = 'token_log'
token_value = db.Column(db.String,
db.ForeignKey('token.value'),
primary_key=True)
timestamp = db.Column(db.DateTime(timezone=True),
primary_key=True,
default=datetime.utcnow)
action = db.Column(
db.Enum(ACTION_DEACTIVATE,
ACTION_CREATE,
name='token_log_action_types'),
primary_key=True,
)
user_id = db.Column(
db.Integer,
db.ForeignKey('user.id', ondelete="SET NULL", onupdate="CASCADE"))
@classmethod
def create_record(cls, access_token, action):
user_id = current_user.id if current_user.is_authenticated() else None
new_record = cls(
token_value=access_token,
action=action,
user_id=user_id,
)
db.session.add(new_record)
db.session.commit()
return new_record
@classmethod
def list(cls, limit=None, offset=None):
query = cls.query.order_by(cls.timestamp.desc())
count = query.count() # Total count should be calculated before limits
if limit is not None:
query = query.limit(limit)
if offset is not None:
query = query.offset(offset)
return query.all(), count
class User(db.Model, UserMixin):
"""User model is used for users of MetaBrainz services like Live Data Feed.
Users are either commercial or non-commercial (see `is_commercial`). Their
access to the API is determined by their `state` (active, pending, waiting,
or rejected). All non-commercial users have active state by default, but
commercial users need to be approved by one of the admins first.
"""
__tablename__ = 'user'
# Common columns used by both commercial and non-commercial users:
id = db.Column(db.Integer, primary_key=True)
is_commercial = db.Column(db.Boolean, nullable=False)
musicbrainz_id = db.Column(
db.Unicode, unique=True) # MusicBrainz account that manages this user
created = db.Column(db.DateTime(timezone=True), default=datetime.utcnow)
state = db.Column(postgres.ENUM(STATE_ACTIVE,
STATE_PENDING,
STATE_WAITING,
STATE_REJECTED,
STATE_LIMITED,
name='state_types'),
nullable=False)
contact_name = db.Column(db.Unicode, nullable=False)
contact_email = db.Column(db.Unicode, nullable=False)
data_usage_desc = db.Column(db.UnicodeText)
# Columns specific to commercial users:
org_name = db.Column(db.Unicode)
org_logo_url = db.Column(db.Unicode)
website_url = db.Column(db.Unicode)
api_url = db.Column(db.Unicode)
org_desc = db.Column(db.UnicodeText)
address_street = db.Column(db.Unicode)
address_city = db.Column(db.Unicode)
address_state = db.Column(db.Unicode)
address_postcode = db.Column(db.Unicode)
address_country = db.Column(db.Unicode)
tier_id = db.Column(
db.Integer,
db.ForeignKey('tier.id', ondelete="SET NULL", onupdate="CASCADE"))
amount_pledged = db.Column(db.Numeric(11, 2))
# Administrative columns:
good_standing = db.Column(db.Boolean, nullable=False, default=True)
in_deadbeat_club = db.Column(db.Boolean, nullable=False, default=False)
featured = db.Column(db.Boolean, nullable=False, default=False)
tokens = db.relationship("Token", backref="owner", lazy="dynamic")
token_log_records = db.relationship("TokenLog",
backref="user",
lazy="dynamic")
def __unicode__(self):
if self.is_commercial:
return "%s (#%s)" % (self.org_name, self.id)
else:
if self.musicbrainz_id:
return "#%s (MBID: %s)" % (self.id, self.musicbrainz_id)
else:
return str(self.id)
@property
def token(self):
return Token.get(owner_id=self.id, is_active=True)
@classmethod
def add(cls, **kwargs):
new_user = cls(
is_commercial=kwargs.pop('is_commercial'),
musicbrainz_id=kwargs.pop('musicbrainz_id'),
contact_name=kwargs.pop('contact_name'),
contact_email=kwargs.pop('contact_email'),
data_usage_desc=kwargs.pop('data_usage_desc'),
org_desc=kwargs.pop('org_desc', None),
org_name=kwargs.pop('org_name', None),
org_logo_url=kwargs.pop('org_logo_url', None),
website_url=kwargs.pop('website_url', None),
api_url=kwargs.pop('api_url', None),
address_street=kwargs.pop('address_street', None),
address_city=kwargs.pop('address_city', None),
address_state=kwargs.pop('address_state', None),
address_postcode=kwargs.pop('address_postcode', None),
address_country=kwargs.pop('address_country', None),
tier_id=kwargs.pop('tier_id', None),
amount_pledged=kwargs.pop('amount_pledged', None),
)
new_user.state = STATE_ACTIVE if not new_user.is_commercial else STATE_PENDING
if kwargs:
raise TypeError('Unexpected **kwargs: %r' % kwargs)
db.session.add(new_user)
db.session.commit()
if new_user.is_commercial:
send_user_signup_notification(new_user)
return new_user
@classmethod
def get(cls, **kwargs):
return cls.query.filter_by(**kwargs).first()
@classmethod
def get_all(cls, **kwargs):
return cls.query.filter_by(**kwargs).all()
@classmethod
def get_all_commercial(cls, limit=None, offset=None):
query = cls.query.filter(cls.is_commercial == True).order_by(
cls.org_name)
count = query.count() # Total count should be calculated before limits
if limit is not None:
query = query.limit(limit)
if offset is not None:
query = query.offset(offset)
return query.all(), count
@classmethod
def get_featured(cls, limit=None, **kwargs):
"""Get list of featured users which is randomly sorted.
Args:
limit: Max number of users to return.
in_deadbeat_club: Returns only users from deadbeat club if set to True.
with_logos: True if need only users with logo URLs specified, False if
only users without logo URLs, None if it's irrelevant.
tier_id: Returns only users from tier with a specified ID.
Returns:
List of users according to filters described above.
"""
query = cls.query.filter(cls.featured == True)
query = query.filter(
cls.in_deadbeat_club == kwargs.pop('in_deadbeat_club', False))
with_logos = kwargs.pop('with_logos', None)
if with_logos:
query = query.filter(cls.org_logo_url != None)
tier_id = kwargs.pop('tier_id', None)
if tier_id:
query = query.filter(cls.tier_id == tier_id)
if kwargs:
raise TypeError('Unexpected **kwargs: %r' % kwargs)
return query.order_by(func.random()).limit(limit).all()
@classmethod
def search(cls, value):
"""Search users by their musicbrainz_id, org_name, contact_name,
or contact_email.
"""
query = cls.query.filter(
or_(
cls.musicbrainz_id.ilike('%' + value + '%'),
cls.org_name.ilike('%' + value + '%'),
cls.contact_name.ilike('%' + value + '%'),
cls.contact_email.ilike('%' + value + '%'),
))
return query.limit(20).all()
def generate_token(self):
"""Generates new access token for this user."""
if self.state == STATE_ACTIVE:
return Token.generate_token(self.id)
else:
raise InactiveUserException(
"Can't generate token for inactive user.")
def update(self, **kwargs):
contact_name = kwargs.pop('contact_name')
if contact_name is not None:
self.contact_name = contact_name
contact_email = kwargs.pop('contact_email')
if contact_email is not None:
self.contact_email = contact_email
if kwargs:
raise TypeError('Unexpected **kwargs: %r' % kwargs)
db.session.commit()
def set_state(self, state):
old_state = self.state
self.state = state
db.session.commit()
if old_state != self.state:
# TODO: Send additional info about new state.
state_name = "ACTIVE" if self.state == STATE_ACTIVE else \
"REJECTED" if self.state == STATE_REJECTED else \
"PENDING" if self.state == STATE_PENDING else \
"WAITING" if self.state == STATE_WAITING else \
"LIMITED" if self.state == STATE_LIMITED else \
self.state
send_mail(
subject="[MetaBrainz] Your account has been updated",
text=
'State of your MetaBrainz account has been changed to "%s".' %
state_name,
recipients=[self.contact_email],
)
class AccessLog(db.Model):
"""Access log is used for tracking requests to the API.
Each request needs to be logged. Logging is done to keep track of number of
requests in a fixed time frame. If there is an unusual number of requests
being made from different IP addresses in this time frame, action is taken.
See implementation of this model for more details.
"""
__tablename__ = 'access_log'
token = db.Column(db.String,
db.ForeignKey('token.value'),
primary_key=True)
timestamp = db.Column(db.DateTime(timezone=True),
primary_key=True,
default=datetime.utcnow)
ip_address = db.Column(postgres.INET)
@classmethod
def create_record(cls, access_token, ip_address):
"""Creates new access log record with a current timestamp.
It also checks if `DIFFERENT_IP_LIMIT` is exceeded within current time
and `CLEANUP_RANGE_MINUTES`, alerts admins if that's the case.
Args:
access_token: Access token used to access the API.
ip_address: IP access used to access the API.
Returns:
New access log record.
"""
new_record = cls(
token=access_token,
ip_address=ip_address,
)
db.session.add(new_record)
db.session.commit()
# Checking if HOURLY_ALERT_THRESHOLD is exceeded
count = cls.query \
.distinct(cls.ip_address) \
.filter(cls.timestamp > datetime.now(pytz.utc) - timedelta(minutes=CLEANUP_RANGE_MINUTES),
cls.token == access_token) \
.count()
if count > DIFFERENT_IP_LIMIT:
msg = ("Hourly access threshold exceeded for token %s\n\n"
"This token has been used from %s different IP "
"addresses during the last %s minutes.") % \
(access_token, count, CLEANUP_RANGE_MINUTES)
logging.info(msg)
# Checking if notification for admins about this token abuse has
# been sent in the last hour. This info is kept in cache.
key = "alert_sent_%s" % access_token
if not cache.get(key):
send_mail(
subject="[MetaBrainz] Hourly access threshold exceeded",
recipients=current_app.config['NOTIFICATION_RECIPIENTS'],
text=msg,
)
cache.set(key, True, 3600) # 1 hour
return new_record
@classmethod
def remove_old_ip_addr_records(cls):
cls.query. \
filter(cls.timestamp < datetime.now(pytz.utc) - timedelta(minutes=CLEANUP_RANGE_MINUTES)). \
update({cls.ip_address: None})
db.session.commit()
@classmethod
def get_hourly_usage(cls, user_id=None):
"""Get information about API usage.
Args:
user_id: User ID that can be specified to get stats only for that account.
Returns:
List of <datetime, request count> tuples for every hour.
"""
if not user_id:
rows = db.engine.execute(
'SELECT max("timestamp") as ts, count(*) '
'FROM access_log '
'GROUP BY extract(year from "timestamp"), extract(month from "timestamp"), '
' extract(day from "timestamp"), trunc(extract(hour from "timestamp")) '
'ORDER BY ts')
else:
rows = db.engine.execute(
'SELECT max(access_log."timestamp") as ts, count(access_log.*) '
'FROM access_log '
'JOIN token ON access_log.token = token.value '
'JOIN "user" ON token.owner_id = "user".id '
'WHERE "user".id = %s '
'GROUP BY extract(year from "timestamp"), extract(month from "timestamp"), '
' extract(day from "timestamp"), trunc(extract(hour from "timestamp")) '
'ORDER BY ts', (user_id, ))
return [(r[0].replace(
minute=0,
second=0,
microsecond=0,
tzinfo=None,
), r[1]) for r in rows]
@classmethod
def active_user_count(cls):
"""Returns number of different users whose access has been logged in
the last 24 hours.
"""
return cls.query.join(Token).join(User) \
.filter(cls.timestamp > datetime.now() - timedelta(days=1)) \
.distinct(User.id).count()
@classmethod
def top_downloaders(cls, limit=None):
"""Generates list of most active users in the last 24 hours.
Args:
limit: Max number of items to return.
Returns:
List of <User, request count> pairs
"""
query = db.session.query(User).join(Token).join(AccessLog) \
.filter(cls.timestamp > datetime.now() - timedelta(days=1)) \
.add_columns(func.count("AccessLog.*").label("count")).group_by(User.id) \
.order_by("count DESC")
if limit:
query = query.limit(limit)
return query.all()
class Token(db.Model):
__tablename__ = 'token'
value = db.Column(db.String, primary_key=True)
is_active = db.Column(db.Boolean, nullable=False, default=True)
owner_id = db.Column(
db.Integer,
db.ForeignKey('user.id', ondelete="SET NULL", onupdate="CASCADE"))
created = db.Column(db.DateTime(timezone=True), default=datetime.utcnow)
log_records = db.relationship(TokenLog, backref="token", lazy="dynamic")
@classmethod
def get(cls, **kwargs):
return cls.query.filter_by(**kwargs).first()
@classmethod
def get_all(cls, **kwargs):
return cls.query.filter_by(**kwargs).all()
@classmethod
def search_by_value(cls, value):
return cls.query.filter(cls.value.like('%' + value + '%')).all()
@classmethod
def generate_token(cls, owner_id):
"""Generates new token for a specified user and revokes all other
tokens owned by this user.
Returns:
Value of the new token.
"""
if owner_id is not None:
last_hour_q = cls.query.filter(
cls.owner_id == owner_id,
cls.created > datetime.utcnow() - timedelta(hours=1),
)
if last_hour_q.count() > 0:
raise TokenGenerationLimitException(
"Can't generate more than one token per hour.")
cls.revoke_tokens(owner_id)
new_token = cls(
value=generate_string(TOKEN_LENGTH),
owner_id=owner_id,
)
db.session.add(new_token)
db.session.commit()
TokenLog.create_record(new_token.value, token_log.ACTION_CREATE)
return new_token.value
@classmethod
def revoke_tokens(cls, owner_id):
"""Revokes all tokens owned by a specified user.
Args:
owner_id: ID of a user.
"""
tokens = db.session.query(cls).filter(cls.owner_id == owner_id,
cls.is_active == True)
for token in tokens:
token.revoke()
@classmethod
def is_valid(cls, token_value):
"""Checks if token exists and is active."""
token = cls.get(value=token_value)
return token and token.is_active
def revoke(self):
self.is_active = False
db.session.commit()
TokenLog.create_record(self.value, token_log.ACTION_DEACTIVATE)