class UserProfile(db.Model, BaseModel):
"""Model for UserProfile"""
__tablename__ = 'user_profiles'
CRYPT_SYM_SECRET_KEY = Config.CRYPT_SYM_SECRET_KEY
# columns
user_id = db.Column('user_id',
db.Integer,
db.ForeignKey('users.id'),
nullable=False)
first_name = db.Column('first_name',
PGPString(CRYPT_SYM_SECRET_KEY, length=200),
nullable=False)
last_name = db.Column('last_name',
PGPString(CRYPT_SYM_SECRET_KEY, length=200),
nullable=False)
joined_at = db.Column('joined_at',
db.TIMESTAMP(timezone=True),
index=True,
server_default=db.func.current_timestamp(),
nullable=False)
# relationships
user = db.relationship('User', back_populates='profile')
class BaseModel:
"""Base model containing: id, status, and timestamps"""
STATUS_ENABLED = 1
STATUS_DISABLED = 2
STATUS_ARCHIVED = 3
STATUS_DELETED = 4
STATUS_PENDING = 5
# properties
id = db.Column(
'id',
db.Integer,
primary_key=True)
status = db.Column(
'status',
db.SmallInteger,
nullable=False)
# timestamps
status_changed_at = db.Column(
'status_changed_at',
db.TIMESTAMP(timezone=True),
nullable=False)
created_at = db.Column(
'created_at',
db.TIMESTAMP(timezone=True),
server_default=db.func.current_timestamp(),
nullable=False)
updated_at = db.Column(
'updated_at',
db.TIMESTAMP(timezone=True),
server_default=db.func.current_timestamp(),
onupdate=db.func.current_timestamp(),
nullable=False)
class Login(db.Model):
"""Model for Login"""
__tablename__ = 'logins'
API_ADMIN = 1
API_PUBLIC = 2
# columns
id = db.Column('id', db.BigInteger, primary_key=True)
user_id = db.Column('user_id', db.Integer, index=True, nullable=True)
username = db.Column('username', db.String(40), index=True, nullable=False)
ip_address = db.Column('ip_address', db.String(50), index=True)
api = db.Column('api', db.SmallInteger, nullable=False)
success = db.Column('success', db.Boolean, nullable=False)
attempt_date = db.Column('attempt_date',
db.TIMESTAMP(timezone=True),
server_default=db.func.current_timestamp(),
nullable=False)
# timestamps
created_at = db.Column('created_at',
db.TIMESTAMP(timezone=True),
server_default=db.func.current_timestamp(),
nullable=False)
updated_at = db.Column('updated_at',
db.TIMESTAMP(timezone=True),
server_default=db.func.current_timestamp(),
onupdate=db.func.current_timestamp(),
nullable=False)
class Country(db.Model, BaseModel):
"""Model for Country"""
__tablename__ = 'countries'
# columns
name = db.Column('name', db.String(60), unique=True, nullable=False)
code_2 = db.Column('code_2', db.String(2), unique=True, nullable=False)
code_3 = db.Column('code_3', db.String(3), unique=True, nullable=False)
# relationships
regions = db.relationship("Region", back_populates="country")
class TermsOfService(db.Model, BaseModel):
"""Model for TermsOfService"""
__tablename__ = 'terms_of_services'
# columns
text = db.Column('text', db.Text(), nullable=False)
version = db.Column('version', db.String(10), unique=True, nullable=False)
publish_date = db.Column('publish_date',
db.TIMESTAMP(timezone=True),
server_default=db.func.current_timestamp(),
nullable=False)
class Notification(db.Model, BaseModel):
"""Model for Notification"""
__tablename__ = 'notifications'
# columns
user_id = db.Column('user_id',
db.Integer,
db.ForeignKey('users.id'),
nullable=True)
channel = db.Column('channel', db.Integer, nullable=False)
template = db.Column('template', db.String(60), nullable=True)
service = db.Column('service', db.String(60), nullable=True)
notification_id = db.Column('notification_id',
db.String(60),
nullable=True)
accepted = db.Column('accepted', db.Integer, nullable=False)
rejected = db.Column('rejected', db.Integer, nullable=False)
sent_at = db.Column('sent_at',
db.TIMESTAMP(timezone=True),
server_default=db.func.current_timestamp(),
nullable=False)
# relationships
user = db.relationship('User', back_populates='notifications')
class Region(db.Model, BaseModel):
"""Model for Region"""
__tablename__ = 'regions'
# columns
name = db.Column('name', db.String(60), nullable=False)
code_2 = db.Column('code_2', db.String(2))
country_id = db.Column('country_id',
db.Integer,
db.ForeignKey('countries.id'),
nullable=False)
# relationships
country = db.relationship('Country', back_populates="regions")
class AppKey(db.Model, BaseModel):
"""Model for AppKey"""
__tablename__ = 'app_keys'
# columns
application = db.Column('application',
db.String(200),
unique=True,
nullable=False)
key = db.Column('key',
db.String(32),
index=True,
unique=True,
nullable=False)
class PasswordReset(db.Model, BaseModel):
"""Model for PasswordReset"""
__tablename__ = 'password_resets'
# columns
user_id = db.Column('user_id',
db.Integer,
db.ForeignKey('users.id'),
nullable=False)
code = db.Column('code', db.String(40), nullable=False)
is_used = db.Column('is_used', db.Boolean, nullable=False)
requested_at = db.Column('requested_at',
db.TIMESTAMP(timezone=True),
server_default=db.func.current_timestamp(),
nullable=False)
ip_address = db.Column('ip_address', db.String(50), index=True)
# relationships
user = db.relationship('User', back_populates='password_resets')
class AdministratorPasswordHistory(db.Model):
"""Model for AdministratorPasswordHistory"""
__tablename__ = 'administrator_password_history'
# columns
id = db.Column('id', db.BigInteger, primary_key=True)
administrator_id = db.Column('administrator_id',
db.Integer,
db.ForeignKey('administrators.id'),
nullable=True)
password = db.Column('password', db.String(60), nullable=False)
set_date = db.Column('set_date',
db.TIMESTAMP(timezone=True),
server_default=db.func.current_timestamp(),
nullable=False)
# timestamps
created_at = db.Column('created_at',
db.TIMESTAMP(timezone=True),
server_default=db.func.current_timestamp(),
nullable=False)
updated_at = db.Column('updated_at',
db.TIMESTAMP(timezone=True),
server_default=db.func.current_timestamp(),
onupdate=db.func.current_timestamp(),
nullable=False)
# relationships
administrator = db.relationship('Administrator',
back_populates='password_history')
class UserTermsOfService(db.Model):
"""Model for UserTermsOfService"""
__tablename__ = 'user_terms_of_services'
CRYPT_SYM_SECRET_KEY = Config.CRYPT_SYM_SECRET_KEY
# columns
user_id = db.Column('user_id',
db.Integer,
db.ForeignKey('users.id'),
primary_key=True)
terms_of_service_id = db.Column('terms_of_service_id',
db.Integer,
db.ForeignKey('terms_of_services.id'),
primary_key=True)
accept_date = db.Column('accept_date',
db.TIMESTAMP(timezone=True),
server_default=db.func.current_timestamp(),
nullable=False)
ip_address = db.Column('ip_address',
PGPString(CRYPT_SYM_SECRET_KEY, length=200),
nullable=False)
# relationships
user = db.relationship('User', uselist=False)
terms_of_service = db.relationship('TermsOfService', uselist=False)
# timestamps
created_at = db.Column('created_at',
db.TIMESTAMP(timezone=True),
server_default=db.func.current_timestamp(),
nullable=False)
updated_at = db.Column('updated_at',
db.TIMESTAMP(timezone=True),
server_default=db.func.current_timestamp(),
onupdate=db.func.current_timestamp(),
nullable=False)
class Role(db.Model):
"""Model for Role"""
__tablename__ = 'roles'
# columns
id = db.Column('id', db.Integer, primary_key=True)
name = db.Column('name', db.String(32), unique=True, nullable=False)
is_admin_role = db.Column('is_admin_role', db.Boolean, nullable=False)
priority = db.Column('priority', db.SmallInteger, nullable=False)
login_lockout_policy = db.Column('login_lockout_policy',
db.Boolean,
nullable=False)
login_max_attempts = db.Column('login_max_attempts',
db.SmallInteger,
nullable=False)
login_timeframe = db.Column('login_timeframe',
db.SmallInteger,
nullable=False)
login_ban_time = db.Column('login_ban_time',
db.SmallInteger,
nullable=False)
login_ban_by_ip = db.Column('login_ban_by_ip', db.Boolean, nullable=False)
password_policy = db.Column('password_policy', db.Boolean, nullable=False)
password_reuse_history = db.Column('password_reuse_history',
db.SmallInteger,
nullable=False)
password_reset_days = db.Column('password_reset_days',
db.SmallInteger,
nullable=False)
# timestamps
created_at = db.Column('created_at',
db.TIMESTAMP(timezone=True),
server_default=db.func.current_timestamp(),
nullable=False)
updated_at = db.Column('updated_at',
db.TIMESTAMP(timezone=True),
server_default=db.func.current_timestamp(),
onupdate=db.func.current_timestamp(),
nullable=False)
class Administrator(db.Model, BaseModel):
"""Model for Administrator"""
__tablename__ = 'administrators'
HASH_ROUNDS = Config.AUTH_HASH_ROUNDS
AUTH_SECRET_KEY = Config.AUTH_SECRET_KEY
CRYPT_SYM_SECRET_KEY = Config.CRYPT_SYM_SECRET_KEY
CRYPT_DIGEST_SALT = Config.CRYPT_DIGEST_SALT
# columns
username = db.Column('username',
db.String(40),
index=True,
unique=True,
nullable=False)
_email = db.Column('email',
PGPString(CRYPT_SYM_SECRET_KEY, length=500),
nullable=False)
email_digest = db.Column('email_digest',
db.String(64),
unique=True,
nullable=False)
first_name = db.Column('first_name',
PGPString(CRYPT_SYM_SECRET_KEY, length=200),
nullable=False)
last_name = db.Column('last_name',
PGPString(CRYPT_SYM_SECRET_KEY, length=200),
nullable=False)
_password = db.Column('password', db.String(60), nullable=False)
password_changed_at = db.Column('password_changed_at',
db.TIMESTAMP(timezone=True),
server_default=db.func.current_timestamp(),
nullable=False)
joined_at = db.Column('joined_at',
db.TIMESTAMP(timezone=True),
server_default=db.func.current_timestamp(),
nullable=False)
# relationships
roles = db.relationship('Role',
secondary=roles,
lazy='subquery',
order_by="Role.priority",
backref=db.backref('administrators', lazy=True))
password_history = db.relationship(
'AdministratorPasswordHistory',
cascade="all,delete-orphan",
back_populates='administrator',
order_by=AdministratorPasswordHistory.set_date.desc())
@hybrid_property
def password(self):
"""Gets `password` property (hashed).
:return: User's hashed password value
:rtype: string
"""
return self._password
@password.setter
def password(self, password):
"""Sets `password` property.
Applies Bcrypt hashing function to `password` before storing it. The
number of hashing rounds are configurable in the main application
config settings.
:param password: User's plaintext password
:type password: str
"""
self._password = str(
bcrypt.hashpw(bytes(password, 'utf-8'),
bcrypt.gensalt(self.HASH_ROUNDS)), 'utf8')
self.password_changed_at = datetime.now()
@hybrid_property
def email(self):
"""Gets `email` property.
:return: User's plaintext email address
:rtype: str
"""
return self._email
@email.setter
def email(self, email):
"""Sets `email` property.
Applies a lowercase transformation to `email` before storing it. Also
sets the `email_digest` property to its SHA-256 hash value - this is
useful if the email is stored encrypted, to allow lookups and
comparisons (e.g.: duplicates) if an exact match is supplied.
:param email: User's plaintext email address
:type email: str
"""
self._email = email.lower()
hash_object = hashlib.sha256(
(self.CRYPT_DIGEST_SALT + email).encode('utf-8'))
self.email_digest = hash_object.hexdigest()
def check_password(self, password):
"""Checks supplied password against saved value.
:param password: User's plaintext password
:type password: str
:return: True if password matches what's on file, False otherwise
:rtype: bool
"""
return bcrypt.checkpw(password.encode('utf-8'),
self._password.encode('utf-8'))
def generate_auth_token(self, expiration=1800):
"""Creates a new authentication token.
:param expiration: Length of time in seconds that token is valid
:type expiration: int
:return: Authentication token
:rtype: str
"""
ser = Serializer(self.AUTH_SECRET_KEY, expires_in=expiration)
return ser.dumps({'id': self.id, 'type': 'administrator'})
@staticmethod
def verify_auth_token(token):
"""Verifies authentication token is valid and current.
:param token: Authentication token
:type token: str
:return: The user associated with token if valid, None otherwise
:rtype: User | None
"""
ser = Serializer(Administrator.AUTH_SECRET_KEY)
try:
data = ser.loads(token)
except SignatureExpired:
return None # valid token, but expired
except BadSignature:
return None # invalid token
if 'type' in data and data['type'] == 'administrator':
admin = Administrator.query.get(data['id'])
return admin
return None
# from main import app
import bcrypt
from sqlalchemy.ext.hybrid import hybrid_property
from itsdangerous import (TimedJSONWebSignatureSerializer as Serializer,
BadSignature, SignatureExpired)
from lib.sqlalchemy.base_model import BaseModel
from lib.sqlalchemy.pgp_string import PGPString
from init_dep import db
from config import Config
# relation tables
roles = db.Table(
'admin_roles',
db.Column('admin_id',
db.Integer,
db.ForeignKey('administrators.id'),
primary_key=True),
db.Column('role_id',
db.Integer,
db.ForeignKey('roles.id'),
primary_key=True))
class AdministratorPasswordHistory(db.Model):
"""Model for AdministratorPasswordHistory"""
__tablename__ = 'administrator_password_history'
# columns
id = db.Column('id', db.BigInteger, primary_key=True)
administrator_id = db.Column('administrator_id',
from sqlalchemy.ext.hybrid import hybrid_property
from itsdangerous import (TimedJSONWebSignatureSerializer as Serializer,
BadSignature, SignatureExpired)
from lib.sqlalchemy.base_model import BaseModel
from lib.sqlalchemy.pgp_string import PGPString
from modules.password_resets.model import PasswordReset
from modules.notifications.model import Notification
from init_dep import db
from config import Config
# relation tables
roles = db.Table(
'user_roles',
db.Column('user_id',
db.Integer,
db.ForeignKey('users.id'),
primary_key=True),
db.Column('role_id',
db.Integer,
db.ForeignKey('roles.id'),
primary_key=True))
class UserPasswordHistory(db.Model):
"""Model for UserPasswordHistory"""
__tablename__ = 'user_password_history'
# columns
id = db.Column('id', db.BigInteger, primary_key=True)
user_id = db.Column('user_id',
class SomeModel(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100))
email = db.Column(db.String(100))
email_digest = db.Column(db.String(100))