# -*- coding: utf-8 -*-
# authdb.py - Waqas Bhatti (wbhatti@astro.princeton.edu) - Aug 2018
# License: MIT - see the LICENSE file for the full text.
"""
This contains SQLAlchemy models for the authnzerver.
"""
import logging
from typing import Optional, Tuple, Union
# get a logger
LOGGER = logging.getLogger(__name__)
import os.path
import os
import stat
from datetime import datetime
import sqlite3
import secrets
import getpass
import uuid
from sqlalchemy import create_engine
from sqlalchemy.engine import Engine, Connection
from sqlalchemy import event
from sqlalchemy import (
Table,
Column,
Integer,
String,
Text,
Boolean,
DateTime,
ForeignKey,
MetaData,
JSON,
insert,
)
from argon2 import PasswordHasher
from .permissions import load_permissions_json
########################
## AUTHNZERVER TABLES ##
########################
AUTHDB_META = MetaData()
# This lists all possible roles in the system
Roles = Table(
"roles",
AUTHDB_META,
Column("name", String(length=100), primary_key=True, nullable=False),
Column("desc", String(length=280), nullable=False),
)
# This is the sessions table storing client sessions
Sessions = Table(
"sessions",
AUTHDB_META,
Column("session_token", String(), primary_key=True, nullable=False),
Column("ip_address", String(length=280), nullable=False),
# some annoying people send zero-length client-headers
# we won't allow them to initiate a session
Column("user_agent", String(length=280), nullable=False),
Column(
"user_id",
Integer,
ForeignKey("users.user_id", ondelete="CASCADE"),
nullable=False,
),
Column(
"created",
DateTime(),
default=datetime.utcnow,
nullable=False,
index=True,
),
Column("expires", DateTime(), nullable=False, index=True),
Column("extra_info_json", JSON(none_as_null=True)),
)
# This is the main users table
Users = Table(
"users",
AUTHDB_META,
# The primary key of this table. Should NOT be made public, not even to the
# frontend. Primary use for this is as a foreign key to other tables.
Column("user_id", Integer(), primary_key=True, nullable=False),
# This is the system ID to expose to JS frontends, etc and is a UUID4. We
# try to never expose the user_id pk to avoid user enumeration. The
# system_id is also used for autocompletes on the frontend to map to
# full_name if a user indicates their name can be public or is shared in a
# group.
Column(
"system_id", String(length=50), index=True, nullable=False, unique=True
),
# This is what the user calls themselves, freeform. Never exposed to other
# users unless we have explicit permission.
Column("full_name", String(length=280), index=True),
Column("password", Text(), nullable=False),
Column("email", String(length=280), nullable=False, unique=True),
Column(
"email_verified", Boolean(), default=False, nullable=False, index=True
),
Column("is_active", Boolean(), default=False, nullable=False, index=True),
# these track when we last sent emails to this user
Column("emailverify_sent_datetime", DateTime()),
Column("emailforgotpass_sent_datetime", DateTime()),
Column("emailchangepass_sent_datetime", DateTime()),
# these two are separated so we can enforce a rate-limit on login tries
Column("last_login_success", DateTime(), index=True),
Column("last_login_try", DateTime(), index=True),
# this is reset everytime a user logs in sucessfully. this is used to check
# the number of failed tries since the last successful try.
# FIXME: can we use this for throttling login attempts without leaking info?
Column("failed_login_tries", Integer(), default=0),
# this contains any extra information required for the user like their
# address, URL of their avatar PNG, etc, as JSON
Column("extra_info", JSON(none_as_null=True)),
Column(
"created_on",
DateTime(),
default=datetime.utcnow,
nullable=False,
index=True,
),
Column(
"last_updated",
DateTime(),
onupdate=datetime.utcnow,
nullable=False,
index=True,
),
Column(
"user_role",
String(length=100),
ForeignKey("roles.name"),
nullable=False,
index=True,
),
)
# this is the groups table
# groups can only be created by authenticated users and above
Groups = Table(
"groups",
AUTHDB_META,
Column("group_id", Integer, primary_key=True),
Column("system_id", String(length=50), index=True, nullable=False),
Column("group_name", String(length=280), nullable=False),
Column(
"visibility",
String(length=100),
nullable=False,
default="public",
index=True,
),
Column("created_by", Integer, ForeignKey("users.user_id"), nullable=False),
Column("is_active", Boolean(), default=False, nullable=False, index=True),
Column(
"created_on",
DateTime(),
default=datetime.utcnow,
nullable=False,
index=True,
),
Column(
"last_updated",
DateTime(),
onupdate=datetime.utcnow,
nullable=False,
index=True,
),
Column(
"group_role",
String(length=100),
ForeignKey("roles.name"),
nullable=False,
index=True,
),
Column("extra_info", JSON(none_as_null=True)),
)
# groups-to-users many-to-many mapping
UserGroups = Table(
"user_groups",
AUTHDB_META,
Column(
"user_id",
Integer,
ForeignKey("users.user_id", ondelete="CASCADE"),
primary_key=True,
),
Column(
"group_id",
Integer,
ForeignKey("groups.group_id", ondelete="CASCADE"),
primary_key=True,
),
)
# user preferences - fairly freeform to allow extension
Preferences = Table(
"preferences",
AUTHDB_META,
Column("pref_id", Integer, primary_key=True),
Column(
"user_id",
Integer,
ForeignKey("users.user_id", ondelete="CASCADE"),
nullable=False,
),
Column("pref_name", String(length=100), nullable=False),
Column("pref_value", String(length=280)),
)
# API keys that are in use
# FIXME: should API keys be deleted via CASCADE when the sessions are deleted?
APIKeys = Table(
"apikeys",
AUTHDB_META,
Column("apikey", Text(), primary_key=True, nullable=False),
Column("issued", DateTime(), nullable=False, default=datetime.utcnow),
Column("expires", DateTime(), index=True, nullable=False),
Column("not_valid_before", DateTime(), index=True, nullable=False),
Column(
"user_id",
Integer(),
ForeignKey("users.user_id", ondelete="CASCADE"),
nullable=False,
),
Column(
"user_role",
String(length=100),
ForeignKey("roles.name", ondelete="CASCADE"),
nullable=False,
),
Column(
"session_token",
Text(),
ForeignKey("sessions.session_token", ondelete="CASCADE"),
nullable=False,
),
)
# No-session API keys
APIKeysNoSession = Table(
"apikeys_nosession",
AUTHDB_META,
Column("apikey", Text(), primary_key=True, nullable=False),
Column("issued", DateTime(), nullable=False, default=datetime.utcnow),
Column("expires", DateTime(), index=True, nullable=False),
Column("not_valid_before", DateTime(), index=True, nullable=False),
Column("refresh_token", Text(), nullable=False),
Column(
"refresh_issued", DateTime(), nullable=False, default=datetime.utcnow
),
Column("refresh_expires", DateTime(), index=True, nullable=False),
Column("refresh_nbf", DateTime(), index=True, nullable=False),
Column(
"user_id",
Integer(),
ForeignKey("users.user_id", ondelete="CASCADE"),
nullable=False,
),
Column(
"user_role",
String(length=100),
ForeignKey("roles.name", ondelete="CASCADE"),
nullable=False,
),
)
#######################
## UTILITY FUNCTIONS ##
#######################
WAL_MODE_SCRIPT = """\
pragma journal_mode=WAL;
pragma journal_size_limit=5242880;
"""
[docs]def create_sqlite_authdb(
auth_db_path: str,
database_metadata: MetaData = AUTHDB_META,
echo: bool = False,
returnconn: bool = False,
) -> Optional[Tuple[Engine, MetaData]]:
"""
This creates the local SQLite auth DB.
"""
engine = create_engine(
"sqlite:///%s" % os.path.abspath(auth_db_path), echo=echo
)
database_metadata.create_all(engine, checkfirst=True)
if returnconn:
return engine, database_metadata
else:
engine.dispose()
del engine
# at the end, we'll switch the auth DB to WAL mode to make it handle
# concurrent operations a bit better
db = sqlite3.connect(auth_db_path)
cur = db.cursor()
cur.executescript(WAL_MODE_SCRIPT)
db.commit()
db.close()
# set the permissions on the file appropriately
os.chmod(auth_db_path, 0o100600)
[docs]def create_authdb(
authdb_url: str,
database_metadata: MetaData = AUTHDB_META,
echo: bool = False,
returnconn: bool = False,
) -> Optional[Tuple[Engine, MetaData]]:
"""
This creates an authentication database for an arbitrary SQLAlchemy DB URL.
"""
engine = create_engine(authdb_url, echo=echo)
# the create_all fn has checkfirst=True, meaning that it doesn't
# recreate existing tables.
database_metadata.create_all(engine, checkfirst=True)
if returnconn:
return engine, database_metadata
else:
engine.dispose()
del engine
[docs]def get_auth_db(
authdb_path: str,
database_metadata: MetaData = AUTHDB_META,
echo: bool = False,
returnconn: bool = True,
) -> Union[Tuple[Engine, Connection, MetaData], Tuple[Engine, MetaData]]:
"""
This just gets a connection to the auth DB.
"""
# if this is an SQLite DB, make sure to check the auth DB permissions before
# we load it so we can be sure no one else messes with it
potential_file_path = authdb_path.replace("sqlite:///", "")
if os.path.exists(potential_file_path):
fileperm = oct(os.stat(potential_file_path)[stat.ST_MODE])
if not (fileperm == "0100600" or fileperm == "0o100600"):
raise IOError("incorrect permissions on auth DB, will not load it")
@event.listens_for(Engine, "connect")
def set_sqlite_pragma(dbapi_connection, connection_record):
cursor = dbapi_connection.cursor()
cursor.execute("PRAGMA foreign_keys=ON")
cursor.close()
engine = create_engine(authdb_path, echo=echo)
database_metadata.bind = engine
conn = engine.connect()
if returnconn:
return engine, conn, database_metadata
else:
return engine, database_metadata
[docs]def initial_authdb_inserts(
auth_db_path: str,
permissions_json: str = None,
database_metadata: MetaData = AUTHDB_META,
superuser_email: str = None,
superuser_pass: str = None,
echo: bool = False,
):
"""
This does initial set up of the auth DB.
- adds an anonymous user
- adds a superuser with:
- userid = UNIX userid
- password = random 16 bytes)
- sets up the initial permissions table
Returns the superuser userid and password.
"""
ret = get_auth_db(
auth_db_path,
database_metadata=database_metadata,
echo=echo,
returnconn=False,
)
engine: Engine = ret[0]
meta: MetaData = ret[1]
# get the roles table and fill it in
roles = meta.tables["roles"]
if not permissions_json:
mod_dir = os.path.dirname(__file__)
permissions_json = os.path.abspath(
os.path.join(mod_dir, "default-permissions-model.json")
)
LOGGER.warning(
"Using default permissions policy JSON: %s to define roles."
% permissions_json
)
permissions_model = load_permissions_json(permissions_json)
roles_to_use = permissions_model["roles"]
for k in ("superuser", "anonymous", "locked"):
if k not in roles_to_use:
LOGGER.error(
"The '%s' role is required for the authnzerver "
"to work properly. It must be included in the "
"list of roles in the permissions policy JSON." % k
)
return None, None
insert_list = []
for role in roles_to_use:
insert_list.append(
{"name": role, "desc": "Role with %s privileges" % role}
)
with engine.begin() as conn:
res = conn.execute(insert(roles), insert_list)
res.close()
# get the users table
users = meta.tables["users"]
# make the superuser account
if not superuser_email:
try:
superuser_email = "%s@localhost" % getpass.getuser()
except Exception:
superuser_email = "admin@localhost"
if not superuser_pass:
superuser_pass = secrets.token_urlsafe(16)
superuser_pass_auto = True
else:
superuser_pass_auto = False
hasher = PasswordHasher()
hashed_password = hasher.hash(superuser_pass)
with engine.begin() as conn:
conn.execute(
insert(users),
[
# the superuser
{
"password": hashed_password,
"email": superuser_email,
"system_id": str(uuid.uuid4()),
"email_verified": True,
"is_active": True,
"user_role": "superuser",
"created_on": datetime.utcnow(),
"last_updated": datetime.utcnow(),
"full_name": "Superuser account",
"extra_info": {
"provenance": "auto-created",
"type": "system-user",
},
},
# the anonuser,
{
"password": hasher.hash(secrets.token_urlsafe(32)),
"email": "anonuser@localhost",
"system_id": str(uuid.uuid4()),
"email_verified": True,
"is_active": True,
"user_role": "anonymous",
"created_on": datetime.utcnow(),
"last_updated": datetime.utcnow(),
"full_name": "The systemwide anonymous user",
"extra_info": {
"provenance": "auto-created",
"type": "system-user",
},
},
# the dummyuser to fail passwords for nonexistent users
# against
{
"password": hasher.hash(secrets.token_urlsafe(32)),
"email": "dummyuser@localhost",
"system_id": str(uuid.uuid4()),
"email_verified": True,
"is_active": False,
"user_role": "locked",
"created_on": datetime.utcnow(),
"last_updated": datetime.utcnow(),
"full_name": "The systemwide locked user",
"extra_info": {
"provenance": "auto-created",
"type": "locked-user",
},
},
],
)
if superuser_pass_auto:
return superuser_email, superuser_pass
else:
return superuser_email, "password-was-user-generated"