import sqlite3
import click
import re
# Much from https://flask.palletsprojects.com/en/3.0.x/tutorial/database/
from flask import current_app, g
# Apparently g is a sort of a general per-request bucket, so that
# multiple parallel requests won't collide.
# Initializes the connection and stores it in g.
def get_db():
if 'db' not in g:
g.db = sqlite3.connect(
current_app.config['DATABASE'],
detect_types=sqlite3.PARSE_DECLTYPES
)
return g.db
# Close and remove from g.
def close_db(e=None):
db = g.pop('db', None)
if db is not None:
db.close()
# This iniitalizes the database. Will empty our table if called.
def init_db():
db = get_db()
with current_app.open_resource('schema.sql') as f:
db.executescript(f.read().decode('utf8'))
# The click doesn't really accompish anything here. Useful for getting
# command line args, and for fancy CLI interfaces, but this ain't that.
@click.command('init-db')
def init_db_command():
"""Clear the existing data and create new tables."""
init_db()
click.echo('Initialized the database.')
# Patterns to check for classes of characters. Got to be a better
# way to do this.
let_pat = re.compile("[A-Za-z]")
dig_pat = re.compile("[0-9]")
special_pat = re.compile("[~!@#$%^&*()_+=:;<>.,{}\[\]\"\'-]")
from werkzeug.security import generate_password_hash, check_password_hash
# Check a password against the security rules. If password2 is sent,
# also verify that they match. Returns an error message, or None if
# no error
def vet_new_passwd(userid, passwd, passwd2=None):
if passwd2 != None and passwd != passwd2:
return "Passwords do not match."
if len(passwd) < 6:
return "Password is too short (need 6)"
if bool(let_pat.search(passwd)) + bool(dig_pat.search(passwd)) + \
bool(special_pat.search(passwd)) < 2:
return "Password needs at least two of letters, digits, special"
return None
# Set the password of an existing user.
def set_passwd(userid, passwd, passwd2=None):
err = vet_new_passwd(userid, passwd, passwd2)
if err != None:
return err
db = get_db()
try:
t = db.execute("UPDATE users SET password = ? WHERE userid = ?",
(generate_password_hash(passwd), userid))
rc = t.rowcount
db.commit()
if rc < 1:
return f"No such user {userid}"
except db.DatabaseError as error:
return f"User {userid} password store failed: " + \
error.sqlite_errorname
return None
# Vet a password for a user. Return (success, msg), where
# success is a boolean success, and msg is an error message, or
# human name.
def vet_passwd(userid, passwd):
db = get_db()
p = db.execute("SELECT username, password FROM users WHERE userid = ?", \
(userid,)).fetchone()
if p == None:
return (False, f"No such user {userid}")
if p[1] == None:
return (False, f"User {userid} has no password set")
if not check_password_hash(p[1], passwd):
return (False, f"Incorrect password for {userid}")
return (True, p[0])
# RE for allowable username. At least six characters, starts with
# a letter, ends with a letter or digit, and can have a few other
# characters internally.
user_pat = re.compile('^[a-zA-Z][a-zA-Z0-9_:.-]*[a-zA-Z0-9]$')
# Insert a user into the database. If a password is sent, it is vetted
# and inserted. If two are sent, vetting includes making sure they match.
def add_user(userid, username, passwd = None, passwd2 = None):
# Vet the userid.
if user_pat.match(userid) is None:
return f"User {userid} too short (< 2), or "+\
"contains forbidden characters."
# Vet the passwd(s)
if passwd != None:
err = vet_new_passwd(userid, passwd, passwd2)
if err != None:
return err
# Create the record.
db = get_db()
try:
db.execute("INSERT INTO users (userid, username, password) "+
"VALUES (?, ?, ?)",
(userid, username,
None if passwd == None else generate_password_hash(passwd)))
db.commit()
except db.IntegrityError:
return f"User {userid} is already registered."
return None
@click.command('add-user')
@click.option('--userid', prompt='Account name')
@click.option('--name', prompt='Human name')
def add_user_cmd(userid, name):
"""Add indicated user"""
r = add_user(userid,name)
if r is None:
click.echo(f"User {userid} ({name}) added")
else:
click.echo(r)
@click.command('set-passwd')
@click.option('--userid', prompt='Account name')
@click.password_option()
def set_passwd_cmd(userid, password):
"""Set password for specified user"""
r = set_passwd(userid,password)
if r is None:
click.echo(f"User {userid} password set")
else:
click.echo(r)
# This is to be called by the factory. It adds the operations to the
# context to be called automatically.
def init_app(app):
app.teardown_appcontext(close_db)
app.cli.add_command(init_db_command)
app.cli.add_command(add_user_cmd)
app.cli.add_command(set_passwd_cmd)