def testconnection(self, param):
"""
This function lets you test the to be saved SQL connection.
:param param: A dictionary with all necessary parameter
to test the connection.
:type param: dict
:return: Tuple of success and a description
:rtype: (bool, string)
Parameters are: Server, Driver, Database, User, Password, Port,
Limit, Table, Map
Where, Encoding, conParams
"""
num = -1
desc = None
connect_string = self._create_connect_string(param)
log.info("using the connect string %s" % connect_string)
engine = create_engine(connect_string)
# create a configured "Session" class
session = sessionmaker(bind=engine)()
db = SQLSoup(engine)
try:
TABLE = db.entity(param.get("Table"))
result = session.query(TABLE).count()
num = result
desc = "Found %i users." % num
except Exception as exx:
desc = "failed to retrieve users: %s" % exx
return num, desc
def loadConfig(self, config):
"""
Load the config from conf.
:param config: The configuration from the Config Table
:type config: dict
"""
self.server = config.get('Server', "")
self.driver = config.get('Driver', "")
self.database = config.get('Database', "")
self.resolverId = self.database
self.port = config.get('Port', "")
self.limit = config.get('Limit', 100)
self.user = config.get('User', "")
self.password = config.get('Password', "")
self.table = config.get('Table', "")
self._editable = config.get("Editable", False)
usermap = config.get('Map', {})
self.map = yaml.load(usermap)
self.reverse_map = dict([[v, k] for k, v in self.map.items()])
self.where = config.get('Where', "")
self.encoding = str(config.get('Encoding') or "latin1")
self.conParams = config.get('conParams', "")
self.pool_size = int(config.get('poolSize') or 5)
self.pool_timeout = int(config.get('poolTimeout') or 10)
# create the connectstring like
params = {'Port': self.port,
'Password': self.password,
'conParams': self.conParams,
'Driver': self.driver,
'User': self.user,
'Server': self.server,
'Database': self.database}
self.connect_string = self._create_connect_string(params)
log.info("using the connect string {0!s}".format(self.connect_string))
try:
log.debug("using pool_size={0!s} and pool_timeout={1!s}".format(
self.pool_size, self.pool_timeout))
self.engine = create_engine(self.connect_string,
encoding=self.encoding,
convert_unicode=False,
pool_size=self.pool_size,
pool_timeout=self.pool_timeout)
except TypeError:
# The DB Engine/Poolclass might not support the pool_size.
log.debug("connecting without pool_size.")
self.engine = create_engine(self.connect_string,
encoding=self.encoding,
convert_unicode=False)
# create a configured "Session" class
Session = sessionmaker(bind=self.engine)
# create a Session
self.session = Session()
self.session._model_changes = {}
self.db = SQLSoup(self.engine)
self.TABLE = self.db.entity(self.table)
return self
def loadConfig(self, config):
"""
Load the config from conf.
:param config: The configuration from the Config Table
:type config: dict
"""
self.server = config.get('Server', "")
self.driver = config.get('Driver', "")
self.database = config.get('Database', "")
self.resolverId = self.database
self.port = config.get('Port', "")
self.limit = config.get('Limit', 100)
self.user = config.get('User', "")
self.password = config.get('Password', "")
self.table = config.get('Table', "")
self._editable = config.get("Editable", False)
self.password_hash_type = config.get("Password_Hash_Type", "SSHA256")
usermap = config.get('Map', {})
self.map = yaml.safe_load(usermap)
self.reverse_map = dict([[v, k] for k, v in self.map.items()])
self.where = config.get('Where', "")
self.encoding = str(config.get('Encoding') or "latin1")
self.conParams = config.get('conParams', "")
self.pool_size = int(config.get('poolSize') or 5)
self.pool_timeout = int(config.get('poolTimeout') or 10)
# recycle SQL connections after 2 hours by default
# (necessary for MySQL servers, which terminate idle connections after some hours)
self.pool_recycle = int(config.get('poolRecycle') or 7200)
# create the connectstring like
params = {
'Port': self.port,
'Password': self.password,
'conParams': self.conParams,
'Driver': self.driver,
'User': self.user,
'Server': self.server,
'Database': self.database
}
self.connect_string = self._create_connect_string(params)
# get an engine from the engine registry, using self.getResolverId() as the key,
# which involves the connect string and the pool settings.
self.engine = get_engine(self.getResolverId(), self._create_engine)
# We use ``scoped_session`` to be sure that the SQLSoup object
# also uses ``self.session``.
Session = scoped_session(sessionmaker(bind=self.engine))
# Session should be closed on teardown
self.session = Session()
register_finalizer(self.session.close)
self.session._model_changes = {}
self.db = SQLSoup(self.engine, session=Session)
self.db.session._model_changes = {}
self.TABLE = self.db.entity(self.table)
return self
def get_table(self, tablename):
db = SQLSoup(
"mysql+pymysql://cic_admin:159357a@{}:{}/cic_finance".format(
'192.168.10.11', 3306))
table = db.entity(tablename)
records = table.all()
res = [{
key: val
for key, val in record.__dict__.items() if not key.startswith('_')
} for record in records]
return res
def testconnection(self, param):
'''
This function lets you test the to be saved SQL connection.
:param param: A dictionary with all necessary parameter to test the connection.
:type param: dict
:return: Tuple of success and a description
:rtype: (bool, string)
Parameters are: Server, Driver, Database, User, Password, Port, Limit, Table, Map
Where, Encoding, conParams
'''
num=-1
desc=None
port = ""
password = ""
conParams = ""
if param.get("Port"):
port = ":%s" % param.get("Port")
if param.get("Password"):
password = "******" % param.get("Password")
if param.get("conParams"):
conParams = "?%s" % param.get("conParams")
connect_string = "%s://%s%s%s%s%s/%s%s" % (param.get("Driver"),
param.get("User"),
password,
"@" if (param.get("User") or password) else "",
param.get("Server"),
port,
param.get("Database"),
conParams)
log.info("using the connect string %s" % connect_string)
engine = create_engine(connect_string)
# create a configured "Session" class
session = sessionmaker(bind=engine)()
db = SQLSoup(engine)
TABLE = db.entity(param.get("Table"))
try:
result = session.query(TABLE).count()
num = result
desc = "Found %i users." % num
except Exception as exx:
desc = "failed to retrieve users: %s" % exx
return (num, desc)
def check_email(self, email):
db = SQLSoup(engine)
try:
result = db.accounts.filter(db.accounts.email == email).one()
return "found"
except:
return "not found"
def check_username(self, username):
db = SQLSoup(engine)
try:
result = db.accounts.filter(db.accounts.username == username).one()
return "found"
except:
return "not found"
def get_account(self, username):
db = SQLSoup(engine)
try:
result = db.accounts.filter(db.accounts.username == username).one()
return result
except:
return -1
def testconnection(cls, param):
"""
This function lets you test the to be saved SQL connection.
:param param: A dictionary with all necessary parameter
to test the connection.
:type param: dict
:return: Tuple of success and a description
:rtype: (bool, string)
Parameters are: Server, Driver, Database, User, Password, Port,
Limit, Table, Map
Where, Encoding, conParams
"""
num = -1
desc = None
connect_string = cls._create_connect_string(param)
log.info(u"using the connect string {0!s}".format(
censor_connect_string(connect_string)))
engine = create_engine(connect_string)
# create a configured "Session" class
Session = scoped_session(sessionmaker(bind=engine))
session = Session()
db = SQLSoup(engine, session=Session)
try:
TABLE = db.entity(param.get("Table"))
conditions = cls._append_where_filter([], TABLE,
param.get("Where"))
filter_condition = and_(*conditions)
result = session.query(TABLE).filter(filter_condition).count()
num = result
desc = "Found {0:d} users.".format(num)
except Exception as exx:
desc = "failed to retrieve users: {0!s}".format(exx)
finally:
# We do not want any leftover DB connection, so we first need to close
# the session such that the DB connection gets returned to the pool (it
# is still open at that point!) and then dispose the engine such that the
# checked-in connection gets closed.
session.close()
engine.dispose()
return num, desc
def loadConfig(self, config):
"""
Load the config from conf.
:param config: The configuration from the Config Table
:type config: dict
"""
self.server = config.get('Server', "")
self.driver = config.get('Driver', "")
self.database = config.get('Database', "")
self.resolverId = self.database
self.port = config.get('Port', "")
self.limit = config.get('Limit', 100)
self.user = config.get('User', "")
self.password = config.get('Password', "")
self.table = config.get('Table', "")
usermap = config.get('Map', {})
self.map = yaml.load(usermap)
self.reverse_map = dict([[v, k] for k, v in self.map.items()])
self.where = config.get('Where', "")
self.encoding = str(config.get('Encoding') or "latin1")
self.conParams = config.get('conParams', "")
self.pool_size = int(config.get('poolSize') or 5)
self.pool_timeout = int(config.get('poolTimeout') or 10)
# create the connectstring like
params = {'Port': self.port,
'Password': self.password,
'conParams': self.conParams,
'Driver': self.driver,
'User': self.user,
'Server': self.server,
'Database': self.database}
self.connect_string = self._create_connect_string(params)
log.info("using the connect string {0!s}".format(self.connect_string))
try:
log.debug("using pool_size={0!s} and pool_timeout={1!s}".format(
self.pool_size, self.pool_timeout))
self.engine = create_engine(self.connect_string,
encoding=self.encoding,
convert_unicode=False,
pool_size=self.pool_size,
pool_timeout=self.pool_timeout)
except TypeError:
# The DB Engine/Poolclass might not support the pool_size.
log.debug("connecting without pool_size.")
self.engine = create_engine(self.connect_string,
encoding=self.encoding,
convert_unicode=False)
# create a configured "Session" class
Session = sessionmaker(bind=self.engine)
# create a Session
self.session = Session()
self.session._model_changes = {}
self.db = SQLSoup(self.engine)
self.TABLE = self.db.entity(self.table)
return self
def testconnection(cls, param):
"""
This function lets you test the to be saved SQL connection.
:param param: A dictionary with all necessary parameter
to test the connection.
:type param: dict
:return: Tuple of success and a description
:rtype: (bool, string)
Parameters are: Server, Driver, Database, User, Password, Port,
Limit, Table, Map
Where, Encoding, conParams
"""
num = -1
desc = None
connect_string = cls._create_connect_string(param)
log.info(u"using the connect string {0!s}".format(censor_connect_string(connect_string)))
engine = create_engine(connect_string)
# create a configured "Session" class
Session = scoped_session(sessionmaker(bind=engine))
session = Session()
db = SQLSoup(engine, session=Session)
try:
TABLE = db.entity(param.get("Table"))
conditions = cls._append_where_filter([], TABLE,
param.get("Where"))
filter_condition = and_(*conditions)
result = session.query(TABLE).filter(filter_condition).count()
num = result
desc = "Found {0:d} users.".format(num)
except Exception as exx:
desc = "failed to retrieve users: {0!s}".format(exx)
finally:
# We do not want any leftover DB connection, so we first need to close
# the session such that the DB connection gets returned to the pool (it
# is still open at that point!) and then dispose the engine such that the
# checked-in connection gets closed.
session.close()
engine.dispose()
return num, desc
def create_account(self, params):
db = SQLSoup(engine)
# Generate a random 32 character number and get it's md5 hash
random_num = randrange(100000000000000000000000000000000,
999999999999999999999999999999999, 2)
salt = hashlib.md5(str(random_num).encode())
# Concatenate the users password and salt, then hash it with sha256
concat = str(params['password']) + str(salt.hexdigest())
pass_hash = hashlib.sha256(concat.encode())
db.accounts.insert(project_name=params['project_name'],
email=params['email'],
username=params['username'],
password=pass_hash.hexdigest(),
salt=salt.hexdigest())
db.commit()
def __init__ (self, band, ip, db, update_interval=30, ttl=60*3):
self.interval = update_interval
self.ttl = ttl
self.band = band
self.name = "log"
#self.db = SqlSoup('mysql://*****:*****@192.168.3.99/ukac20130226')
self.db = SqlSoup('mysql://*****:*****@%s/%s' % (ip, db))
#self.db = SqlSoup('mysql://*****:*****@192.168.0.104/%s' %db)
SpotSource.__init__(self)
def loadConfig(self, config, conf):
'''
Load the config from conf.
:param config: The configuration from the Config Table
:type config: dict
:param conf: the instance of the configuration
:type conf: string
The information which config entries we need to load is taken from
manage.js: function save_sql_config
'''
self.resolverId = conf
self.server = self.getConfigEntry(config, 'privacyidea.sqlresolver.Server', conf)
self.driver = self.getConfigEntry(config, 'privacyidea.sqlresolver.Driver', conf)
self.database = self.getConfigEntry(config, 'privacyidea.sqlresolver.Database', conf)
self.port = self.getConfigEntry(config, 'privacyidea.sqlresolver.Port', conf, required=False)
self.limit = self.getConfigEntry(config, 'privacyidea.sqlresolver.Limit', conf, required=False, default=100)
self.user = self.getConfigEntry(config, 'privacyidea.sqlresolver.User', conf)
self.password = self.getConfigEntry(config, 'privacyidea.sqlresolver.Password', conf, required=False)
self.table = self.getConfigEntry(config, 'privacyidea.sqlresolver.Table', conf)
usermap = self.getConfigEntry(config, 'privacyidea.sqlresolver.Map', conf)
self.map = yaml.load(usermap)
self.reverse_map = dict([[v,k] for k,v in self.map.items()])
self.where = self.getConfigEntry(config, 'privacyidea.sqlresolver.Where', conf, required=False)
self.encoding = self.getConfigEntry(config, 'privacyidea.sqlresolver.Encoding', conf, required=False, default="latin1")
self.conParams = self.getConfigEntry(config, 'privacyidea.sqlresolver.conParams', conf, required=False)
# create the connectstring like
# driver://user:passwd@seerver/database?conParams
port = ""
password = ""
if self.port:
port = ":%s" % self.port
if self.password:
password = "******" % self.password
self.connect_string = "%s://%s%s@%s%s/%s?%s" % (self.driver,
self.user,
password,
self.server,
port,
self.database,
self.conParams)
log.info("using the connect string %s" % self.connect_string)
self.engine = create_engine(self.connect_string, encoding=str(self.encoding))
# create a configured "Session" class
Session = sessionmaker(bind=self.engine)
# create a Session
self.session = Session()
self.db = SQLSoup(self.engine)
self.TABLE = self.db.entity(self.table)
return self
def __init__(self):
if not self.singleton:
self.__class__.singleton = True
else:
raise SystemError, 'SQLite3Vocab is singleton.'
path = os.path.dirname(os.path.realpath(__file__))
self.engine = SQLSoup('sqlite:////%s/sqlite3.db' % path)
self.comune_id = self.engine.comuni.codice_istat
self.provincia_id = self.engine.province.sigla
self.regione_id = self.engine.regioni.codice_istat
def loadConfig(self, config):
"""
Load the config from conf.
:param config: The configuration from the Config Table
:type config: dict
"""
self.server = config.get('Server', "")
self.driver = config.get('Driver', "")
self.database = config.get('Database', "")
self.resolverId = self.database
self.port = config.get('Port', "")
self.limit = config.get('Limit', 100)
self.user = config.get('User', "")
self.password = config.get('Password', "")
self.table = config.get('Table', "")
self._editable = config.get("Editable", False)
self.password_hash_type = config.get("Password_Hash_Type", "SSHA256")
usermap = config.get('Map', {})
self.map = yaml.safe_load(usermap)
self.reverse_map = dict([[v, k] for k, v in self.map.items()])
self.where = config.get('Where', "")
self.encoding = str(config.get('Encoding') or "latin1")
self.conParams = config.get('conParams', "")
self.pool_size = int(config.get('poolSize') or 5)
self.pool_timeout = int(config.get('poolTimeout') or 10)
# recycle SQL connections after 2 hours by default
# (necessary for MySQL servers, which terminate idle connections after some hours)
self.pool_recycle = int(config.get('poolRecycle') or 7200)
# create the connectstring like
params = {'Port': self.port,
'Password': self.password,
'conParams': self.conParams,
'Driver': self.driver,
'User': self.user,
'Server': self.server,
'Database': self.database}
self.connect_string = self._create_connect_string(params)
# get an engine from the engine registry, using self.getResolverId() as the key,
# which involves the connect string and the pool settings.
self.engine = get_engine(self.getResolverId(), self._create_engine)
# We use ``scoped_session`` to be sure that the SQLSoup object
# also uses ``self.session``.
Session = scoped_session(sessionmaker(bind=self.engine))
# Session should be closed on teardown
self.session = Session()
register_finalizer(self.session.close)
self.session._model_changes = {}
self.db = SQLSoup(self.engine, session=Session)
self.db.session._model_changes = {}
self.TABLE = self.db.entity(self.table)
return self
def testconnection(cls, param):
"""
This function lets you test the to be saved SQL connection.
:param param: A dictionary with all necessary parameter
to test the connection.
:type param: dict
:return: Tuple of success and a description
:rtype: (bool, string)
Parameters are: Server, Driver, Database, User, Password, Port,
Limit, Table, Map
Where, Encoding, conParams
"""
num = -1
desc = None
connect_string = cls._create_connect_string(param)
log.info("using the connect string {0!s}".format(connect_string))
engine = create_engine(connect_string)
# create a configured "Session" class
session = sessionmaker(bind=engine)()
db = SQLSoup(engine)
try:
TABLE = db.entity(param.get("Table"))
conditions = cls._append_where_filter([], TABLE,
param.get("Where"))
filter_condition = and_(*conditions)
result = session.query(TABLE).filter(filter_condition).count()
num = result
desc = "Found {0:d} users.".format(num)
except Exception as exx:
desc = "failed to retrieve users: {0!s}".format(exx)
return num, desc
def testconnection(cls, param):
"""
This function lets you test the to be saved SQL connection.
:param param: A dictionary with all necessary parameter
to test the connection.
:type param: dict
:return: Tuple of success and a description
:rtype: (bool, string)
Parameters are: Server, Driver, Database, User, Password, Port,
Limit, Table, Map
Where, Encoding, conParams
"""
num = -1
desc = None
connect_string = cls._create_connect_string(param)
log.info("using the connect string {0!s}".format(connect_string))
engine = create_engine(connect_string)
# create a configured "Session" class
session = sessionmaker(bind=engine)()
db = SQLSoup(engine)
try:
TABLE = db.entity(param.get("Table"))
conditions = cls._append_where_filter([], TABLE,
param.get("Where"))
filter_condition = and_(*conditions)
result = session.query(TABLE).filter(filter_condition).count()
num = result
desc = "Found {0:d} users.".format(num)
except Exception as exx:
desc = "failed to retrieve users: {0!s}".format(exx)
return num, desc
def main(config_path):
cfg = ConfigParser()
cfg.read(config_path)
# Start Twisted logging to console.
log.startLogging(stderr)
# Read database configuration options.
db_url = cfg.get('database', 'url')
# Read website configuration options.
http_debug = cfg.getboolean('http', 'debug', fallback=False)
http_host = cfg.get('http', 'host', fallback='localhost')
http_port = cfg.getint('http', 'port', fallback=5000)
http_pool = cfg.getint('http', 'pool_size', fallback=4)
# Default to much saner database query defaults and always
# commit and/or flush statements explicitly.
# factory = sessionmaker(autocommit=False, autoflush=False)
# Prepare database connection with table reflection.
engine = create_engine(db_url)
session = scoped_session(sessionmaker(autocommit=False, autoflush=False))
db = SQLSoup(engine, session=session)
# Extract manager options, sans the pool_size we handle here.
# pool_size = int(manager_opts.pop('pool_size', 2))
pool_size = 2
# Set the correct thread pool size for the manager.
reactor.suggestThreadPoolSize(pool_size)
# Prepare the website that will get exposed to the users.
site = make_site(db, debug=http_debug)
# Prepare WSGI site with a separate thread pool.
pool = ThreadPool(http_pool, http_pool, 'http')
site = Site(WSGIResource(reactor, pool, site))
pool.start()
# Bind the website to it's address.
reactor.listenTCP(http_port, site, interface=http_host)
# Run the Twisted reactor until the user terminates us.
reactor.run()
# Kill the HTTP ThreadPool.
pool.stop()
def loadConfig(self, config):
"""
Load the config from conf.
:param config: The configuration from the Config Table
:type config: dict
"""
self.server = config.get('Server', "")
self.driver = config.get('Driver', "")
self.database = config.get('Database', "")
self.resolverId = self.database
self.port = config.get('Port', "")
self.limit = config.get('Limit', 100)
self.user = config.get('User', "")
self.password = config.get('Password', "")
self.table = config.get('Table', "")
usermap = config.get('Map', {})
self.map = yaml.load(usermap)
self.reverse_map = dict([[v, k] for k, v in self.map.items()])
self.where = config.get('Where', "")
self.encoding = str(config.get('Encoding') or "latin1")
self.conParams = config.get('conParams', "")
# create the connectstring like
params = {'Port': self.port,
'Password': self.password,
'conParams': self.conParams,
'Driver': self.driver,
'User': self.user,
'Server': self.server,
'Database': self.database}
self.connect_string = self._create_connect_string(params)
log.info("using the connect string %s" % self.connect_string)
self.engine = create_engine(self.connect_string,
encoding=self.encoding,
convert_unicode=False)
# create a configured "Session" class
Session = sessionmaker(bind=self.engine)
# create a Session
self.session = Session()
self.session._model_changes = {}
self.db = SQLSoup(self.engine)
self.TABLE = self.db.entity(self.table)
return self
def connect_db(config):
"""
:param ConfigParser config
"""
factory = sessionmaker(autocommit=False, autoflush=False)
db_engine = config.get('database', 'engine')
username = config.get('database', 'username')
password = config.get('database', 'password')
host = config.get('database', 'hostname')
port = config.get('database', 'port')
db_name = config.get('database', 'db_name')
db_url = str(db_engine) + '://' + str(username) + ':' + str(
password) + '@' + str(host) + ':' + str(port) + '/' + str(db_name)
engine = create_engine(db_url)
session = scoped_session(factory)
db_meta = reflect(engine)
db = SQLSoup(db_meta, session=session)
return db
class Log (SpotSource):
def __init__ (self, band, ip, db, update_interval=30, ttl=60*3):
self.interval = update_interval
self.ttl = ttl
self.band = band
self.name = "log"
#self.db = SqlSoup('mysql://*****:*****@192.168.3.99/ukac20130226')
self.db = SqlSoup('mysql://*****:*****@%s/%s' % (ip, db))
#self.db = SqlSoup('mysql://*****:*****@192.168.0.104/%s' %db)
SpotSource.__init__(self)
def update (self):
logger.debug('%s: updating.', self.name)
if datetime.now() < self.last + timedelta(seconds=self.interval):
logger.debug('%s: aborting update - before update interval', self.name)
return None
rp = self.db.execute('select callsign from log')
for callsign, in rp.fetchall():
self.add_spot(frequency=None,
time=datetime.now(),
call=callsign)
def main(url, crawl_id):
G = nx.DiGraph()
soup = SQLSoup(url)
peers_with_ip = 0
reachable_peers = 0
peers = dict(
(p.id, p) for p in soup.peers.filter_by(crawl_id=crawl_id).all())
edges = soup.edges.filter_by(crawl_id=crawl_id).all()
for p in peers.values():
if p.reachable: reachable_peers += 1
if p.ip: peers_with_ip += 1
G.add_node(
p.id,
color=('blue' if p.ip else 'red' if not p.reachable else 'orange'))
for e in edges:
G.add_edge(getattr(e, 'from'),
e.to,
directed=e.directed,
color=('black' if e.directed else 'red'))
degrees = find_degrees(G)
export_to_dot(G, peers, degrees, 'crawl-%d.dot' % crawl_id)
UG = nx.Graph(G)
print('vertices with ip: ', peers_with_ip)
print('vertices with reachable ip: ', reachable_peers)
print('vertices: ', len(G))
print('edges: ', G.size())
print('average degree: ', degree_avg(G.degree()))
print('average in degree: ', degrees['in_degree_avg'])
print('average out degree: ', degrees['out_degree_avg'])
print('average unknown degree: ', degrees['unknown_degree_avg'])
print('graph edges (as undirected graph): ', UG.size())
print('graph diameter (as undirected graph): ', nx.diameter(UG))
debug=1
"""
exec(_var_defaults)
from pyfusion.utils import process_cmd_line_args
exec(process_cmd_line_args())
if db_url != 'None':
from sqlsoup import SQLSoup
def write_to_summary(shot, valdic):
if not isinstance(shot, (tuple, list, np.ndarray)):
shot = [shot]
for s in shot:
SUMM.filter(h1db.summary_h1.shot == s).update(valdic)
h1db = SQLSoup(db_url)
cols = h1db.summary_h1.c.keys()
SUMM = h1db.summary_h1
else:
h1db = None
bads, simple, best, IPs = [], [], [], []
for i, shot in enumerate(shots):
dev = pyfusion.getDevice(dev_name)
try:
dat = dev.acq.getdata(shot, diag_name)
except:
bads.append(shot)
continue
def __init__(self, dsn):
self.soup = SQLSoup(dsn)
class IdResolver (UserIdResolver):
searchFields = {"username": "******",
"userid": "numeric",
"phone": "text",
"mobile": "text",
"surname": "text",
"givenname": "text",
"email": "text",
"description": "text",
}
updateable = True
@classmethod
def setup(cls, config=None, cache_dir=None):
"""
this setup hook is triggered, when the server
starts to serve the first request
:param config: the privacyidea config
:type config: the privacyidea config dict
"""
log.info("Setting up the SQLResolver")
def __init__(self):
self.resolverId = ""
self.server = ""
self.driver = ""
self.database = ""
self.port = 0
self.limit = 100
self.user = ""
self.password = ""
self.table = ""
self.map = {}
self.reverse_map = {}
self.where = ""
self.encoding = ""
self.conParams = ""
self.connect_string = ""
self.session = None
return
def getSearchFields(self):
return self.searchFields
@classmethod
def _append_where_filter(cls, conditions, table, where):
"""
Append contents of WHERE statement to the list of filter conditions
:param conditions: filter conditions
:type conditions: list
:return: list of filter conditions
"""
if where:
# this might result in errors if the
# administrator enters nonsense
(w_column, w_cond, w_value) = where.split()
if w_cond.lower() == "like":
conditions.append(getattr(table, w_column).like(w_value))
elif w_cond == "==":
conditions.append(getattr(table, w_column) == w_value)
elif w_cond == ">":
conditions.append(getattr(table, w_column) > w_value)
elif w_cond == "<":
conditions.append(getattr(table, w_column) < w_value)
return conditions
def checkPass(self, uid, password):
"""
This function checks the password for a given uid.
- returns true in case of success
- false if password does not match
"""
def _check_ssha(pw_hash, password, hashfunc, length):
pw_hash_bin = b64decode(pw_hash.split("}")[1])
digest = pw_hash_bin[:length]
salt = pw_hash_bin[length:]
hr = hashfunc(password)
hr.update(salt)
return digest == hr.digest()
def _check_sha(pw_hash, password):
b64_db_password = pw_hash[5:]
hr = hashlib.sha1(password).digest()
b64_password = b64encode(hr)
return b64_password == b64_db_password
def _otrs_sha256(pw_hash, password):
hr = hashlib.sha256(password)
digest = binascii.hexlify(hr.digest())
return pw_hash == digest
res = False
userinfo = self.getUserInfo(uid)
database_pw = userinfo.get("password", "XXXXXXX")
if database_pw[:2] == "$P":
# We have a phpass (wordpress) password
PH = PasswordHash()
res = PH.check_password(password, userinfo.get("password"))
# check salted hashed passwords
elif database_pw[:6].upper() == "{SSHA}":
res = _check_ssha(database_pw, password, hashlib.sha1, 20)
elif database_pw[:9].upper() == "{SSHA256}":
res = _check_ssha(database_pw, password, hashlib.sha256, 32)
elif database_pw[:9].upper() == "{SSHA512}":
res = _check_ssha(database_pw, password, hashlib.sha512, 64)
# check for hashed password.
elif userinfo.get("password", "XXXXX")[:5].upper() == "{SHA}":
res = _check_sha(database_pw, password)
elif len(userinfo.get("password")) == 64:
# OTRS sha256 password
res = _otrs_sha256(database_pw, password)
return res
def getUserInfo(self, userId):
"""
This function returns all user info for a given userid/object.
:param userId: The userid of the object
:type userId: string
:return: A dictionary with the keys defined in self.map
:rtype: dict
"""
userinfo = {}
try:
conditions = []
column = self.map.get("userid")
conditions.append(getattr(self.TABLE, column).like(userId))
conditions = self._append_where_filter(conditions, self.TABLE,
self.where)
filter_condition = and_(*conditions)
result = self.session.query(self.TABLE).filter(filter_condition)
for r in result:
if len(userinfo.keys()) > 0: # pragma: no cover
raise Exception("More than one user with userid %s found!"
% userId)
userinfo = self._get_user_from_mapped_object(r)
except Exception as exx: # pragma: no cover
log.error("Could not get the userinformation: %r" % exx)
return userinfo
def getUsername(self, userId):
"""
Returns the username/loginname for a given userid
:param userid: The userid in this resolver
:type userid: string
:return: username
:rtype: string
"""
info = self.getUserInfo(userId)
return info.get('username', "")
def getUserId(self, LoginName):
"""
resolve the loginname to the userid.
:param LoginName: The login name from the credentials
:type LoginName: string
:return: UserId as found for the LoginName
"""
userid = ""
try:
conditions = []
column = self.map.get("username")
conditions.append(getattr(self.TABLE, column).like(LoginName))
conditions = self._append_where_filter(conditions, self.TABLE,
self.where)
filter_condition = and_(*conditions)
result = self.session.query(self.TABLE).filter(filter_condition)
for r in result:
if userid != "": # pragma: no cover
raise Exception("More than one user with loginname"
" %s found!" % LoginName)
user = self._get_user_from_mapped_object(r)
userid = user["id"]
except Exception as exx: # pragma: no cover
log.error("Could not get the userinformation: %r" % exx)
return userid
def _get_user_from_mapped_object(self, ro):
"""
:param ro: row
:type ro: Mapped Object
:return: User
:rtype: dict
"""
r = ro.__dict__
user = {}
try:
if self.map.get("userid") in r:
user["id"] = r[self.map.get("userid")]
except UnicodeEncodeError: # pragma: no cover
log.error("Failed to convert user: %r" % r)
log.error(traceback.format_exc())
for key in ["username",
"surname",
"givenname",
"email",
"mobile",
"description",
"phone",
"password"]:
try:
raw_value = r.get(self.map.get(key))
if raw_value:
if type(raw_value) == str:
val = raw_value.decode(self.encoding)
else:
val = raw_value
user[key] = val
except UnicodeDecodeError: # pragma: no cover
user[key] = "decoding_error"
log.error("Failed to convert user: %r" % r)
log.error(traceback.format_exc())
return user
def getUserList(self, searchDict=None):
"""
:param searchDict: A dictionary with search parameters
:type searchDict: dict
:return: list of users, where each user is a dictionary
"""
users = []
conditions = []
if searchDict is None:
searchDict = {}
for key in searchDict.keys():
column = self.map.get(key)
value = searchDict.get(key)
value = value.replace("*", "%")
conditions.append(getattr(self.TABLE, column).like(value))
conditions = self._append_where_filter(conditions, self.TABLE,
self.where)
filter_condition = and_(*conditions)
result = self.session.query(self.TABLE).\
filter(filter_condition).\
limit(self.limit)
for r in result:
user = self._get_user_from_mapped_object(r)
if "id" in user:
users.append(user)
return users
def getResolverId(self):
"""
Returns the resolver Id
This should be an Identifier of the resolver, preferable the type
and the name of the resolver.
"""
return "sql." + self.resolverId
@classmethod
def getResolverClassType(cls):
return 'sqlresolver'
@classmethod
def getResolverType(cls):
return IdResolver.getResolverClassType()
def loadConfig(self, config):
"""
Load the config from conf.
:param config: The configuration from the Config Table
:type config: dict
"""
self.server = config.get('Server', "")
self.driver = config.get('Driver', "")
self.database = config.get('Database', "")
self.resolverId = self.database
self.port = config.get('Port', "")
self.limit = config.get('Limit', 100)
self.user = config.get('User', "")
self.password = config.get('Password', "")
self.table = config.get('Table', "")
usermap = config.get('Map', {})
self.map = yaml.load(usermap)
self.reverse_map = dict([[v, k] for k, v in self.map.items()])
self.where = config.get('Where', "")
self.encoding = str(config.get('Encoding') or "latin1")
self.conParams = config.get('conParams', "")
# create the connectstring like
params = {'Port': self.port,
'Password': self.password,
'conParams': self.conParams,
'Driver': self.driver,
'User': self.user,
'Server': self.server,
'Database': self.database}
self.connect_string = self._create_connect_string(params)
log.info("using the connect string %s" % self.connect_string)
self.engine = create_engine(self.connect_string,
encoding=self.encoding,
convert_unicode=False)
# create a configured "Session" class
Session = sessionmaker(bind=self.engine)
# create a Session
self.session = Session()
self.session._model_changes = {}
self.db = SQLSoup(self.engine)
self.TABLE = self.db.entity(self.table)
return self
@classmethod
def getResolverClassDescriptor(cls):
descriptor = {}
typ = cls.getResolverType()
descriptor['clazz'] = "useridresolver.SQLIdResolver.IdResolver"
descriptor['config'] = {'Server': 'string',
'Driver': 'string',
'Database': 'string',
'User': '******',
'Password': '******',
'Port': 'int',
'Limit': 'int',
'Table': 'string',
'Map': 'string',
'Where': 'string',
'Editable': 'int',
'Encoding': 'string',
'conParams': 'string'}
return {typ: descriptor}
def getResolverDescriptor(self):
return IdResolver.getResolverClassDescriptor()
@classmethod
def _create_connect_string(self, param):
"""
create the connectstring
Port, Password, conParams, Driver, User,
Server, Database
"""
port = ""
password = ""
conParams = ""
if param.get("Port"):
port = ":%s" % param.get("Port")
if param.get("Password"):
password = "******" % param.get("Password")
if param.get("conParams"):
conParams = "?%s" % param.get("conParams")
connect_string = "%s://%s%s%s%s%s/%s%s" % (param.get("Driver", ""),
param.get("User", ""),
password,
"@" if (param.get("User")
or
password) else "",
param.get("Server", ""),
port,
param.get("Database", ""),
conParams)
# SQLAlchemy does not like a unicode connect string!
if param.get("Driver").lower() == "sqlite":
connect_string = str(connect_string)
log.debug("SQL connectstring: %r" % connect_string)
return connect_string
@classmethod
def testconnection(cls, param):
"""
This function lets you test the to be saved SQL connection.
:param param: A dictionary with all necessary parameter
to test the connection.
:type param: dict
:return: Tuple of success and a description
:rtype: (bool, string)
Parameters are: Server, Driver, Database, User, Password, Port,
Limit, Table, Map
Where, Encoding, conParams
"""
num = -1
desc = None
connect_string = cls._create_connect_string(param)
log.info("using the connect string %s" % connect_string)
engine = create_engine(connect_string)
# create a configured "Session" class
session = sessionmaker(bind=engine)()
db = SQLSoup(engine)
try:
TABLE = db.entity(param.get("Table"))
conditions = cls._append_where_filter([], TABLE,
param.get("Where"))
filter_condition = and_(*conditions)
result = session.query(TABLE).filter(filter_condition).count()
num = result
desc = "Found %i users." % num
except Exception as exx:
desc = "failed to retrieve users: %s" % exx
return num, desc
def add_user(self, attributes=None):
"""
Add a new user to the SQL database.
attributes are these
"username", "surname", "givenname", "email",
"mobile", "phone", "password"
:param attributes: Attributes according to the attribute mapping
:return: The new UID of the user. The UserIdResolver needs to
determine the way how to create the UID.
"""
attributes = attributes or {}
kwargs = self._attributes_to_db_columns(attributes)
log.debug("Insert new user with attributes %s" % kwargs)
r = self.TABLE.insert(**kwargs)
self.db.commit()
# Return the UID of the new object
return getattr(r, self.map.get("userid"))
def _attributes_to_db_columns(self, attributes):
"""
takes the attributes and maps them to the DB columns
:param attributes:
:return: dict with column name as keys and values
"""
columns = {}
for fieldname in attributes.keys():
if self.map.get(fieldname):
if fieldname == "password":
password = attributes.get(fieldname)
# Create a {SSHA256} password
salt = geturandom(16)
hr = hashlib.sha256(password)
hr.update(salt)
hash_bin = hr.digest()
hash_b64 = b64encode(hash_bin + salt)
columns[self.map.get(fieldname)] = "{SSHA256}" + hash_b64
else:
columns[self.map.get(fieldname)] = attributes.get(fieldname)
return columns
def delete_user(self, uid):
"""
Delete a user from the SQL database.
The user is referenced by the user id.
:param uid: The uid of the user object, that should be deleted.
:type uid: basestring
:return: Returns True in case of success
:rtype: bool
"""
res = True
try:
conditions = []
column = self.map.get("userid")
conditions.append(getattr(self.TABLE, column).like(uid))
conditions = self._append_where_filter(conditions, self.TABLE,
self.where)
filter_condition = and_(*conditions)
user_obj = self.session.query(self.TABLE).filter(
filter_condition).first()
self.session.delete(user_obj)
self.session.commit()
except Exception as exx:
log.error("Error deleting user: %s" % exx)
res = False
return res
def update_user(self, uid, attributes=None):
"""
Update an existing user.
This function is also used to update the password. Since the
attribute mapping know, which field contains the password,
this function can also take care for password changing.
Attributes that are not contained in the dict attributes are not
modified.
:param uid: The uid of the user object in the resolver.
:type uid: basestring
:param attributes: Attributes to be updated.
:type attributes: dict
:return: True in case of success
"""
attributes = attributes or {}
params = self._attributes_to_db_columns(attributes)
kwargs = {self.map.get("userid"): uid}
r = self.TABLE.filter_by(**kwargs).update(params)
self.db.commit()
return r
class IdResolver(UserIdResolver):
searchFields = {
"username": "******",
"userid": "numeric",
"phone": "text",
"mobile": "text",
"surname": "text",
"givenname": "text",
"email": "text",
"description": "text",
}
# If the resolver could be configured editable
updateable = True
@staticmethod
def setup(config=None, cache_dir=None):
"""
this setup hook is triggered, when the server
starts to serve the first request
:param config: the privacyidea config
:type config: the privacyidea config dict
"""
log.info("Setting up the SQLResolver")
def __init__(self):
self.resolverId = ""
self.server = ""
self.driver = ""
self.database = ""
self.port = 0
self.limit = 100
self.user = ""
self.password = ""
self.table = ""
self.map = {}
self.reverse_map = {}
self.where = ""
self.encoding = ""
self.conParams = ""
self.connect_string = ""
self.session = None
self.pool_size = 10
self.pool_timeout = 120
self.engine = None
self._editable = False
self.password_hash_type = None
return
def getSearchFields(self):
return self.searchFields
@staticmethod
def _append_where_filter(conditions, table, where):
"""
Append contents of WHERE statement to the list of filter conditions
:param conditions: filter conditions
:type conditions: list
:return: list of filter conditions
"""
if where:
parts = re.split(' and ', where, flags=re.IGNORECASE)
for part in parts:
# this might result in errors if the
# administrator enters nonsense
(w_column, w_cond, w_value) = part.split()
if w_cond.lower() == "like":
conditions.append(getattr(table, w_column).like(w_value))
elif w_cond == "==":
conditions.append(getattr(table, w_column) == w_value)
elif w_cond == ">":
conditions.append(getattr(table, w_column) > w_value)
elif w_cond == "<":
conditions.append(getattr(table, w_column) < w_value)
return conditions
def checkPass(self, uid, password):
"""
This function checks the password for a given uid.
:param uid: uid of the user for which the password should be checked
:type uid: str
:param password: the password to check
:type password: str
:return: True if password matches the saved password hash, False otherwise
:rtype: bool
"""
res = False
userinfo = self.getUserInfo(uid)
database_pw = userinfo.get("password", "XXXXXXX")
# remove owncloud hash format identifier (currently only version 1)
database_pw = re.sub(r'^1\|', '', database_pw)
# translate lower case hash identifier to uppercase
database_pw = re.sub(
r'^{([a-z0-9]+)}',
lambda match: '{{{}}}'.format(match.group(1).upper()), database_pw)
try:
res = pw_ctx.verify(password, database_pw)
except ValueError as _e:
# if the hash could not be identified / verified, just return False
pass
return res
def getUserInfo(self, userId):
"""
This function returns all user info for a given userid/object.
:param userId: The userid of the object
:type userId: string
:return: A dictionary with the keys defined in self.map
:rtype: dict
"""
userinfo = {}
try:
conditions = []
conditions.append(self._get_userid_filter(userId))
conditions = self._append_where_filter(conditions, self.TABLE,
self.where)
filter_condition = and_(*conditions)
result = self.session.query(self.TABLE).filter(filter_condition)
for r in result:
if userinfo: # pragma: no cover
raise Exception(
"More than one user with userid {0!s} found!".format(
userId))
userinfo = self._get_user_from_mapped_object(r)
except Exception as exx: # pragma: no cover
log.error("Could not get the userinformation: {0!r}".format(exx))
return userinfo
def _get_userid_filter(self, userId):
column = getattr(self.TABLE, self.map.get("userid"))
if isinstance(column.type, Integer):
# since our user ID is usually a string we need to cast
return column == int(userId)
else:
# otherwise we cast the column to string (in case of postgres UUIDs)
return cast(column, String).like(userId)
def getUsername(self, userId):
"""
Returns the username/loginname for a given userid
:param userid: The userid in this resolver
:type userid: string
:return: username
:rtype: string
"""
info = self.getUserInfo(userId)
return info.get('username', "")
def getUserId(self, LoginName):
"""
resolve the loginname to the userid.
:param LoginName: The login name from the credentials
:type LoginName: string
:return: UserId as found for the LoginName
:rtype: str
"""
userid = ""
try:
conditions = []
column = self.map.get("username")
conditions.append(getattr(self.TABLE, column).like(LoginName))
conditions = self._append_where_filter(conditions, self.TABLE,
self.where)
filter_condition = and_(*conditions)
result = self.session.query(self.TABLE).filter(filter_condition)
for r in result:
if userid != "": # pragma: no cover
raise Exception("More than one user with loginname"
" %s found!" % LoginName)
user = self._get_user_from_mapped_object(r)
userid = convert_column_to_unicode(user["id"])
except Exception as exx: # pragma: no cover
log.error("Could not get the userinformation: {0!r}".format(exx))
return userid
def _get_user_from_mapped_object(self, ro):
"""
:param ro: row
:type ro: Mapped Object
:return: User
:rtype: dict
"""
r = ro.__dict__
user = {}
try:
if self.map.get("userid") in r:
user["id"] = r[self.map.get("userid")]
except UnicodeEncodeError: # pragma: no cover
log.error("Failed to convert user: {0!r}".format(r))
log.debug("{0!s}".format(traceback.format_exc()))
for key in self.map.keys():
try:
raw_value = r.get(self.map.get(key))
if raw_value:
if isinstance(raw_value, bytes):
val = raw_value.decode(self.encoding)
else:
val = raw_value
user[key] = val
except UnicodeDecodeError: # pragma: no cover
user[key] = "decoding_error"
log.error("Failed to convert user: {0!r}".format(r))
log.debug("{0!s}".format(traceback.format_exc()))
return user
def getUserList(self, searchDict=None):
"""
:param searchDict: A dictionary with search parameters
:type searchDict: dict
:return: list of users, where each user is a dictionary
"""
users = []
conditions = []
if searchDict is None:
searchDict = {}
for key in searchDict.keys():
column = self.map.get(key)
value = searchDict.get(key)
value = value.replace("*", "%")
conditions.append(getattr(self.TABLE, column).like(value))
conditions = self._append_where_filter(conditions, self.TABLE,
self.where)
filter_condition = and_(*conditions)
result = self.session.query(self.TABLE).\
filter(filter_condition).\
limit(self.limit)
for r in result:
user = self._get_user_from_mapped_object(r)
if "id" in user:
users.append(user)
return users
def getResolverId(self):
"""
Returns the resolver Id
This should be an Identifier of the resolver, preferable the type
and the name of the resolver.
:return: identifier of the resolver
:rtype: str
"""
# Take the following parts, join them with the NULL byte and return
# the hexlified SHA-1 digest
id_parts = (self.connect_string, str(self.pool_size),
str(self.pool_recycle), str(self.pool_timeout))
id_str = "\x00".join(id_parts)
resolver_id = binascii.hexlify(
hashlib.sha1(id_str.encode('utf8')).digest())
return "sql." + resolver_id.decode('utf8')
@staticmethod
def getResolverClassType():
return 'sqlresolver'
@staticmethod
def getResolverType():
return IdResolver.getResolverClassType()
def loadConfig(self, config):
"""
Load the config from conf.
:param config: The configuration from the Config Table
:type config: dict
"""
self.server = config.get('Server', "")
self.driver = config.get('Driver', "")
self.database = config.get('Database', "")
self.resolverId = self.database
self.port = config.get('Port', "")
self.limit = config.get('Limit', 100)
self.user = config.get('User', "")
self.password = config.get('Password', "")
self.table = config.get('Table', "")
self._editable = config.get("Editable", False)
self.password_hash_type = config.get("Password_Hash_Type", "SSHA256")
usermap = config.get('Map', {})
self.map = yaml.safe_load(usermap)
self.reverse_map = dict([[v, k] for k, v in self.map.items()])
self.where = config.get('Where', "")
self.encoding = str(config.get('Encoding') or "latin1")
self.conParams = config.get('conParams', "")
self.pool_size = int(config.get('poolSize') or 5)
self.pool_timeout = int(config.get('poolTimeout') or 10)
# recycle SQL connections after 2 hours by default
# (necessary for MySQL servers, which terminate idle connections after some hours)
self.pool_recycle = int(config.get('poolRecycle') or 7200)
# create the connectstring like
params = {
'Port': self.port,
'Password': self.password,
'conParams': self.conParams,
'Driver': self.driver,
'User': self.user,
'Server': self.server,
'Database': self.database
}
self.connect_string = self._create_connect_string(params)
# get an engine from the engine registry, using self.getResolverId() as the key,
# which involves the connect string and the pool settings.
self.engine = get_engine(self.getResolverId(), self._create_engine)
# We use ``scoped_session`` to be sure that the SQLSoup object
# also uses ``self.session``.
Session = scoped_session(sessionmaker(bind=self.engine))
# Session should be closed on teardown
self.session = Session()
register_finalizer(self.session.close)
self.session._model_changes = {}
self.db = SQLSoup(self.engine, session=Session)
self.db.session._model_changes = {}
self.TABLE = self.db.entity(self.table)
return self
def _create_engine(self):
log.info(u"using the connect string "
u"{0!s}".format(censor_connect_string(self.connect_string)))
try:
log.debug(
"using pool_size={0!s}, pool_timeout={1!s}, pool_recycle={2!s}"
.format(self.pool_size, self.pool_timeout, self.pool_recycle))
engine = create_engine(self.connect_string,
encoding=self.encoding,
convert_unicode=False,
pool_size=self.pool_size,
pool_recycle=self.pool_recycle,
pool_timeout=self.pool_timeout)
except TypeError:
# The DB Engine/Poolclass might not support the pool_size.
log.debug("connecting without pool_size.")
engine = create_engine(self.connect_string,
encoding=self.encoding,
convert_unicode=False)
return engine
@classmethod
def getResolverClassDescriptor(cls):
descriptor = {}
typ = cls.getResolverType()
descriptor['clazz'] = "useridresolver.SQLIdResolver.IdResolver"
descriptor['config'] = {
'Server': 'string',
'Driver': 'string',
'Database': 'string',
'User': '******',
'Password': '******',
'Password_Hash_Type': 'string',
'Port': 'int',
'Limit': 'int',
'Table': 'string',
'Map': 'string',
'Where': 'string',
'Editable': 'int',
'poolTimeout': 'int',
'poolSize': 'int',
'poolRecycle': 'int',
'Encoding': 'string',
'conParams': 'string'
}
return {typ: descriptor}
@staticmethod
def getResolverDescriptor():
return IdResolver.getResolverClassDescriptor()
@staticmethod
def _create_connect_string(param):
"""
create the connectstring
Port, Password, conParams, Driver, User,
Server, Database
"""
port = ""
password = ""
conParams = ""
if param.get("Port"):
port = u":{0!s}".format(param.get("Port"))
if param.get("Password"):
password = u":{0!s}".format(param.get("Password"))
if param.get("conParams"):
conParams = u"?{0!s}".format(param.get("conParams"))
connect_string = u"{0!s}://{1!s}{2!s}{3!s}{4!s}{5!s}/{6!s}{7!s}".format(
param.get("Driver", ""), param.get("User", ""), password,
"@" if (param.get("User") or password) else "",
param.get("Server", ""), port, param.get("Database",
""), conParams)
# SQLAlchemy does not like a unicode connect string!
# if param.get("Driver").lower() == "sqlite":
# connect_string = str(connect_string)
return connect_string
@classmethod
def testconnection(cls, param):
"""
This function lets you test the to be saved SQL connection.
:param param: A dictionary with all necessary parameter
to test the connection.
:type param: dict
:return: Tuple of success and a description
:rtype: (bool, string)
Parameters are: Server, Driver, Database, User, Password, Port,
Limit, Table, Map
Where, Encoding, conParams
"""
num = -1
desc = None
connect_string = cls._create_connect_string(param)
log.info(u"using the connect string {0!s}".format(
censor_connect_string(connect_string)))
engine = create_engine(connect_string)
# create a configured "Session" class
Session = scoped_session(sessionmaker(bind=engine))
session = Session()
db = SQLSoup(engine, session=Session)
try:
TABLE = db.entity(param.get("Table"))
conditions = cls._append_where_filter([], TABLE,
param.get("Where"))
filter_condition = and_(*conditions)
result = session.query(TABLE).filter(filter_condition).count()
num = result
desc = "Found {0:d} users.".format(num)
except Exception as exx:
desc = "failed to retrieve users: {0!s}".format(exx)
finally:
# We do not want any leftover DB connection, so we first need to close
# the session such that the DB connection gets returned to the pool (it
# is still open at that point!) and then dispose the engine such that the
# checked-in connection gets closed.
session.close()
engine.dispose()
return num, desc
def add_user(self, attributes=None):
"""
Add a new user to the SQL database.
attributes are these
"username", "surname", "givenname", "email",
"mobile", "phone", "password"
:param attributes: Attributes according to the attribute mapping
:return: The new UID of the user. The UserIdResolver needs to
determine the way how to create the UID.
"""
attributes = attributes or {}
kwargs = self.prepare_attributes_for_db(attributes)
log.debug("Insert new user with attributes {0!s}".format(kwargs))
r = self.TABLE.insert(**kwargs)
self.db.commit()
# Return the UID of the new object
return getattr(r, self.map.get("userid"))
def prepare_attributes_for_db(self, attributes):
"""
Given a dictionary of attributes, return a dictionary
mapping columns to values.
If the attributes contain a password, hash the password according to the
configured password hash type.
:param attributes: attributes dictionary
:return: dictionary with column name as keys
"""
attributes = attributes.copy()
if "password" in attributes:
attributes["password"] = hash_password(attributes["password"],
self.password_hash_type)
columns = {}
for fieldname in attributes:
if fieldname in self.map:
columns[self.map[fieldname]] = attributes[fieldname]
return columns
def delete_user(self, uid):
"""
Delete a user from the SQL database.
The user is referenced by the user id.
:param uid: The uid of the user object, that should be deleted.
:type uid: basestring
:return: Returns True in case of success
:rtype: bool
"""
res = True
try:
conditions = []
conditions.append(self._get_userid_filter(uid))
conditions = self._append_where_filter(conditions, self.TABLE,
self.where)
filter_condition = and_(*conditions)
user_obj = self.session.query(
self.TABLE).filter(filter_condition).first()
self.session.delete(user_obj)
self.session.commit()
except Exception as exx:
log.error("Error deleting user: {0!s}".format(exx))
res = False
return res
def update_user(self, uid, attributes=None):
"""
Update an existing user.
This function is also used to update the password. Since the
attribute mapping know, which field contains the password,
this function can also take care for password changing.
Attributes that are not contained in the dict attributes are not
modified.
:param uid: The uid of the user object in the resolver.
:type uid: basestring
:param attributes: Attributes to be updated.
:type attributes: dict
:return: True in case of success
"""
attributes = attributes or {}
params = self.prepare_attributes_for_db(attributes)
kwargs = {self.map.get("userid"): uid}
r = self.TABLE.filter_by(**kwargs).update(params)
self.db.commit()
return r
@property
def editable(self):
"""
Return true, if the instance of the resolver is configured editable
:return:
"""
# Depending on the database this might look different
# Usually this is "1"
return is_true(self._editable)
class IdResolver(UserIdResolver):
searchFields = {
"username": "******",
"userid": "numeric",
"phone": "text",
"mobile": "text",
"surname": "text",
"givenname": "text",
"email": "text",
"description": "text",
}
# If the resolver could be configured editable
updateable = True
@staticmethod
def setup(config=None, cache_dir=None):
"""
this setup hook is triggered, when the server
starts to serve the first request
:param config: the privacyidea config
:type config: the privacyidea config dict
"""
log.info("Setting up the SQLResolver")
def __init__(self):
self.resolverId = ""
self.server = ""
self.driver = ""
self.database = ""
self.port = 0
self.limit = 100
self.user = ""
self.password = ""
self.table = ""
self.map = {}
self.reverse_map = {}
self.where = ""
self.encoding = ""
self.conParams = ""
self.connect_string = ""
self.session = None
self.pool_size = 10
self.pool_timeout = 120
self.engine = None
self._editable = False
self.password_hash_type = None
return
def getSearchFields(self):
return self.searchFields
@staticmethod
def _append_where_filter(conditions, table, where):
"""
Append contents of WHERE statement to the list of filter conditions
:param conditions: filter conditions
:type conditions: list
:return: list of filter conditions
"""
if where:
parts = re.split(' and ', where, flags=re.IGNORECASE)
for part in parts:
# this might result in errors if the
# administrator enters nonsense
(w_column, w_cond, w_value) = part.split()
if w_cond.lower() == "like":
conditions.append(getattr(table, w_column).like(w_value))
elif w_cond == "==":
conditions.append(getattr(table, w_column) == w_value)
elif w_cond == ">":
conditions.append(getattr(table, w_column) > w_value)
elif w_cond == "<":
conditions.append(getattr(table, w_column) < w_value)
return conditions
def checkPass(self, uid, password):
"""
This function checks the password for a given uid.
If ``password`` is a unicode object, it is converted to the database encoding first.
- returns true in case of success
- false if password does not match
"""
res = False
userinfo = self.getUserInfo(uid)
if isinstance(password, unicode):
password = password.encode(self.encoding)
database_pw = userinfo.get("password", "XXXXXXX")
if database_pw[:2] in ["$P", "$S"]:
# We have a phpass (wordpress) password
PH = PasswordHash()
res = PH.check_password(password, userinfo.get("password"))
# check salted hashed passwords
# elif database_pw[:2] == "$6":
# res = sha512_crypt.verify(password, userinfo.get("password"))
elif database_pw[:6].upper() == "{SSHA}":
res = check_ssha(database_pw, password, hashlib.sha1, 20)
elif database_pw[:9].upper() == "{SSHA256}":
res = check_ssha(database_pw, password, hashlib.sha256, 32)
elif database_pw[:9].upper() == "{SSHA512}":
res = check_ssha(database_pw, password, hashlib.sha512, 64)
# check for hashed password.
elif userinfo.get("password", "XXXXX")[:5].upper() == "{SHA}":
res = check_sha(database_pw, password)
elif len(userinfo.get("password")) == 64:
# OTRS sha256 password
res = otrs_sha256(database_pw, password)
elif database_pw[:3] in ["$1$", "$6$"]:
res = check_crypt(database_pw, password)
return res
def getUserInfo(self, userId):
"""
This function returns all user info for a given userid/object.
:param userId: The userid of the object
:type userId: string
:return: A dictionary with the keys defined in self.map
:rtype: dict
"""
userinfo = {}
try:
conditions = []
column = self.map.get("userid")
conditions.append(getattr(self.TABLE, column).like(userId))
conditions = self._append_where_filter(conditions, self.TABLE,
self.where)
filter_condition = and_(*conditions)
result = self.session.query(self.TABLE).filter(filter_condition)
for r in result:
if userinfo.keys(): # pragma: no cover
raise Exception(
"More than one user with userid {0!s} found!".format(
userId))
userinfo = self._get_user_from_mapped_object(r)
except Exception as exx: # pragma: no cover
log.error("Could not get the userinformation: {0!r}".format(exx))
return userinfo
def getUsername(self, userId):
"""
Returns the username/loginname for a given userid
:param userid: The userid in this resolver
:type userid: string
:return: username
:rtype: string
"""
info = self.getUserInfo(userId)
return info.get('username', "")
def getUserId(self, LoginName):
"""
resolve the loginname to the userid.
:param LoginName: The login name from the credentials
:type LoginName: string
:return: UserId as found for the LoginName
"""
userid = ""
try:
conditions = []
column = self.map.get("username")
conditions.append(getattr(self.TABLE, column).like(LoginName))
conditions = self._append_where_filter(conditions, self.TABLE,
self.where)
filter_condition = and_(*conditions)
result = self.session.query(self.TABLE).filter(filter_condition)
for r in result:
if userid != "": # pragma: no cover
raise Exception("More than one user with loginname"
" %s found!" % LoginName)
user = self._get_user_from_mapped_object(r)
userid = user["id"]
except Exception as exx: # pragma: no cover
log.error("Could not get the userinformation: {0!r}".format(exx))
return userid
def _get_user_from_mapped_object(self, ro):
"""
:param ro: row
:type ro: Mapped Object
:return: User
:rtype: dict
"""
r = ro.__dict__
user = {}
try:
if self.map.get("userid") in r:
user["id"] = r[self.map.get("userid")]
except UnicodeEncodeError: # pragma: no cover
log.error("Failed to convert user: {0!r}".format(r))
log.debug("{0!s}".format(traceback.format_exc()))
for key in self.map.keys():
try:
raw_value = r.get(self.map.get(key))
if raw_value:
if type(raw_value) == str:
val = raw_value.decode(self.encoding)
else:
val = raw_value
user[key] = val
except UnicodeDecodeError: # pragma: no cover
user[key] = "decoding_error"
log.error("Failed to convert user: {0!r}".format(r))
log.debug("{0!s}".format(traceback.format_exc()))
return user
def getUserList(self, searchDict=None):
"""
:param searchDict: A dictionary with search parameters
:type searchDict: dict
:return: list of users, where each user is a dictionary
"""
users = []
conditions = []
if searchDict is None:
searchDict = {}
for key in searchDict.keys():
column = self.map.get(key)
value = searchDict.get(key)
value = value.replace("*", "%")
conditions.append(getattr(self.TABLE, column).like(value))
conditions = self._append_where_filter(conditions, self.TABLE,
self.where)
filter_condition = and_(*conditions)
result = self.session.query(self.TABLE).\
filter(filter_condition).\
limit(self.limit)
for r in result:
user = self._get_user_from_mapped_object(r)
if "id" in user:
users.append(user)
return users
def getResolverId(self):
"""
Returns the resolver Id
This should be an Identifier of the resolver, preferable the type
and the name of the resolver.
"""
return "sql." + self.resolverId
@staticmethod
def getResolverClassType():
return 'sqlresolver'
@staticmethod
def getResolverType():
return IdResolver.getResolverClassType()
def loadConfig(self, config):
"""
Load the config from conf.
:param config: The configuration from the Config Table
:type config: dict
"""
self.server = config.get('Server', "")
self.driver = config.get('Driver', "")
self.database = config.get('Database', "")
self.resolverId = self.database
self.port = config.get('Port', "")
self.limit = config.get('Limit', 100)
self.user = config.get('User', "")
self.password = config.get('Password', "")
self.table = config.get('Table', "")
self._editable = config.get("Editable", False)
self.password_hash_type = config.get("Password_Hash_Type")
usermap = config.get('Map', {})
self.map = yaml.safe_load(usermap)
self.reverse_map = dict([[v, k] for k, v in self.map.items()])
self.where = config.get('Where', "")
self.encoding = str(config.get('Encoding') or "latin1")
self.conParams = config.get('conParams', "")
self.pool_size = int(config.get('poolSize') or 5)
self.pool_timeout = int(config.get('poolTimeout') or 10)
# create the connectstring like
params = {
'Port': self.port,
'Password': self.password,
'conParams': self.conParams,
'Driver': self.driver,
'User': self.user,
'Server': self.server,
'Database': self.database
}
self.connect_string = self._create_connect_string(params)
log.info("using the connect string {0!s}".format(self.connect_string))
try:
log.debug("using pool_size={0!s} and pool_timeout={1!s}".format(
self.pool_size, self.pool_timeout))
self.engine = create_engine(self.connect_string,
encoding=self.encoding,
convert_unicode=False,
pool_size=self.pool_size,
pool_timeout=self.pool_timeout)
except TypeError:
# The DB Engine/Poolclass might not support the pool_size.
log.debug("connecting without pool_size.")
self.engine = create_engine(self.connect_string,
encoding=self.encoding,
convert_unicode=False)
# create a configured "Session" class
Session = sessionmaker(bind=self.engine)
# create a Session
self.session = Session()
self.session._model_changes = {}
self.db = SQLSoup(self.engine)
self.db.session._model_changes = {}
self.TABLE = self.db.entity(self.table)
return self
@classmethod
def getResolverClassDescriptor(cls):
descriptor = {}
typ = cls.getResolverType()
descriptor['clazz'] = "useridresolver.SQLIdResolver.IdResolver"
descriptor['config'] = {
'Server': 'string',
'Driver': 'string',
'Database': 'string',
'User': '******',
'Password': '******',
'Password_Hash_Type': 'string',
'Port': 'int',
'Limit': 'int',
'Table': 'string',
'Map': 'string',
'Where': 'string',
'Editable': 'int',
'Encoding': 'string',
'conParams': 'string'
}
return {typ: descriptor}
@staticmethod
def getResolverDescriptor():
return IdResolver.getResolverClassDescriptor()
@staticmethod
def _create_connect_string(param):
"""
create the connectstring
Port, Password, conParams, Driver, User,
Server, Database
"""
port = ""
password = ""
conParams = ""
if param.get("Port"):
port = ":{0!s}".format(param.get("Port"))
if param.get("Password"):
password = "******".format(param.get("Password"))
if param.get("conParams"):
conParams = "?{0!s}".format(param.get("conParams"))
connect_string = "{0!s}://{1!s}{2!s}{3!s}{4!s}{5!s}/{6!s}{7!s}".format(
param.get("Driver", ""), param.get("User", ""), password,
"@" if (param.get("User") or password) else "",
param.get("Server", ""), port, param.get("Database",
""), conParams)
# SQLAlchemy does not like a unicode connect string!
if param.get("Driver").lower() == "sqlite":
connect_string = str(connect_string)
log.debug("SQL connectstring: {0!r}".format(connect_string))
return connect_string
@classmethod
def testconnection(cls, param):
"""
This function lets you test the to be saved SQL connection.
:param param: A dictionary with all necessary parameter
to test the connection.
:type param: dict
:return: Tuple of success and a description
:rtype: (bool, string)
Parameters are: Server, Driver, Database, User, Password, Port,
Limit, Table, Map
Where, Encoding, conParams
"""
num = -1
desc = None
connect_string = cls._create_connect_string(param)
log.info("using the connect string {0!s}".format(connect_string))
engine = create_engine(connect_string)
# create a configured "Session" class
session = sessionmaker(bind=engine)()
db = SQLSoup(engine)
try:
TABLE = db.entity(param.get("Table"))
conditions = cls._append_where_filter([], TABLE,
param.get("Where"))
filter_condition = and_(*conditions)
result = session.query(TABLE).filter(filter_condition).count()
num = result
desc = "Found {0:d} users.".format(num)
except Exception as exx:
desc = "failed to retrieve users: {0!s}".format(exx)
return num, desc
def add_user(self, attributes=None):
"""
Add a new user to the SQL database.
attributes are these
"username", "surname", "givenname", "email",
"mobile", "phone", "password"
:param attributes: Attributes according to the attribute mapping
:return: The new UID of the user. The UserIdResolver needs to
determine the way how to create the UID.
"""
attributes = attributes or {}
if "password" in attributes and self.password_hash_type:
attributes["password"] = hash_password(attributes["password"],
self.password_hash_type)
kwargs = self._attributes_to_db_columns(attributes)
log.debug("Insert new user with attributes {0!s}".format(kwargs))
r = self.TABLE.insert(**kwargs)
self.db.commit()
# Return the UID of the new object
return getattr(r, self.map.get("userid"))
def _attributes_to_db_columns(self, attributes):
"""
takes the attributes and maps them to the DB columns
:param attributes:
:return: dict with column name as keys and values
"""
columns = {}
for fieldname in attributes.keys():
if self.map.get(fieldname):
if fieldname == "password":
password = attributes.get(fieldname)
# Create a {SSHA256} password
salt = geturandom(16)
hr = hashlib.sha256(password)
hr.update(salt)
hash_bin = hr.digest()
hash_b64 = b64encode(hash_bin + salt)
columns[self.map.get(fieldname)] = "{SSHA256}" + hash_b64
else:
columns[self.map.get(fieldname)] = attributes.get(
fieldname)
return columns
def delete_user(self, uid):
"""
Delete a user from the SQL database.
The user is referenced by the user id.
:param uid: The uid of the user object, that should be deleted.
:type uid: basestring
:return: Returns True in case of success
:rtype: bool
"""
res = True
try:
conditions = []
column = self.map.get("userid")
conditions.append(getattr(self.TABLE, column).like(uid))
conditions = self._append_where_filter(conditions, self.TABLE,
self.where)
filter_condition = and_(*conditions)
user_obj = self.session.query(
self.TABLE).filter(filter_condition).first()
self.session.delete(user_obj)
self.session.commit()
except Exception as exx:
log.error("Error deleting user: {0!s}".format(exx))
res = False
return res
def update_user(self, uid, attributes=None):
"""
Update an existing user.
This function is also used to update the password. Since the
attribute mapping know, which field contains the password,
this function can also take care for password changing.
Attributes that are not contained in the dict attributes are not
modified.
:param uid: The uid of the user object in the resolver.
:type uid: basestring
:param attributes: Attributes to be updated.
:type attributes: dict
:return: True in case of success
"""
attributes = attributes or {}
params = self._attributes_to_db_columns(attributes)
kwargs = {self.map.get("userid"): uid}
r = self.TABLE.filter_by(**kwargs).update(params)
self.db.commit()
return r
@property
def editable(self):
"""
Return true, if the instance of the resolver is configured editable
:return:
"""
# Depending on the database this might look different
# Usually this is "1"
return is_true(self._editable)
class IdResolver (UserIdResolver):
searchFields = {"username": "******",
"userid": "numeric",
"phone": "text",
"mobile": "text",
"surname": "text",
"givenname": "text",
"email": "text",
"description": "text",
}
# If the resolver could be configured editable
updateable = True
@staticmethod
def setup(config=None, cache_dir=None):
"""
this setup hook is triggered, when the server
starts to serve the first request
:param config: the privacyidea config
:type config: the privacyidea config dict
"""
log.info("Setting up the SQLResolver")
def __init__(self):
self.resolverId = ""
self.server = ""
self.driver = ""
self.database = ""
self.port = 0
self.limit = 100
self.user = ""
self.password = ""
self.table = ""
self.map = {}
self.reverse_map = {}
self.where = ""
self.encoding = ""
self.conParams = ""
self.connect_string = ""
self.session = None
self.pool_size = 10
self.pool_timeout = 120
self.engine = None
self._editable = False
self.password_hash_type = None
return
def getSearchFields(self):
return self.searchFields
@staticmethod
def _append_where_filter(conditions, table, where):
"""
Append contents of WHERE statement to the list of filter conditions
:param conditions: filter conditions
:type conditions: list
:return: list of filter conditions
"""
if where:
parts = re.split(' and ', where, flags=re.IGNORECASE)
for part in parts:
# this might result in errors if the
# administrator enters nonsense
(w_column, w_cond, w_value) = part.split()
if w_cond.lower() == "like":
conditions.append(getattr(table, w_column).like(w_value))
elif w_cond == "==":
conditions.append(getattr(table, w_column) == w_value)
elif w_cond == ">":
conditions.append(getattr(table, w_column) > w_value)
elif w_cond == "<":
conditions.append(getattr(table, w_column) < w_value)
return conditions
def checkPass(self, uid, password):
"""
This function checks the password for a given uid.
If ``password`` is a unicode object, it is converted to the database encoding first.
- returns true in case of success
- false if password does not match
"""
res = False
userinfo = self.getUserInfo(uid)
if isinstance(password, unicode):
password = password.encode(self.encoding)
database_pw = userinfo.get("password", "XXXXXXX")
if database_pw[:2] in ["$P", "$S"]:
# We have a phpass (wordpress) password
PH = PasswordHash()
res = PH.check_password(password, userinfo.get("password"))
# check salted hashed passwords
# elif database_pw[:2] == "$6":
# res = sha512_crypt.verify(password, userinfo.get("password"))
elif database_pw[:6].upper() == "{SSHA}":
res = check_ssha(database_pw, password, hashlib.sha1, 20)
elif database_pw[:9].upper() == "{SSHA256}":
res = check_ssha(database_pw, password, hashlib.sha256, 32)
elif database_pw[:9].upper() == "{SSHA512}":
res = check_ssha(database_pw, password, hashlib.sha512, 64)
# check for hashed password.
elif userinfo.get("password", "XXXXX")[:5].upper() == "{SHA}":
res = check_sha(database_pw, password)
elif len(userinfo.get("password")) == 64:
# OTRS sha256 password
res = otrs_sha256(database_pw, password)
elif database_pw[:3] in ["$1$", "$6$"]:
res = check_crypt(database_pw, password)
elif database_pw[:4] in ["$2a$", "$2b$", "$2y$"]:
# Do bcrypt hashing
res = bcrypt.verify(password, database_pw)
elif database_pw[:6] in ["1|$2a$", "1|$2b$", "1|$2y$"]:
# Do bcrypt hashing with some owncloud format
res = bcrypt.verify(password, database_pw[2:])
return res
def getUserInfo(self, userId):
"""
This function returns all user info for a given userid/object.
:param userId: The userid of the object
:type userId: string
:return: A dictionary with the keys defined in self.map
:rtype: dict
"""
userinfo = {}
try:
conditions = []
column = self.map.get("userid")
conditions.append(getattr(self.TABLE, column).like(userId))
conditions = self._append_where_filter(conditions, self.TABLE,
self.where)
filter_condition = and_(*conditions)
result = self.session.query(self.TABLE).filter(filter_condition)
for r in result:
if userinfo: # pragma: no cover
raise Exception("More than one user with userid {0!s} found!".format(userId))
userinfo = self._get_user_from_mapped_object(r)
except Exception as exx: # pragma: no cover
log.error("Could not get the userinformation: {0!r}".format(exx))
return userinfo
def getUsername(self, userId):
"""
Returns the username/loginname for a given userid
:param userid: The userid in this resolver
:type userid: string
:return: username
:rtype: string
"""
info = self.getUserInfo(userId)
return info.get('username', "")
def getUserId(self, LoginName):
"""
resolve the loginname to the userid.
:param LoginName: The login name from the credentials
:type LoginName: string
:return: UserId as found for the LoginName
"""
userid = ""
try:
conditions = []
column = self.map.get("username")
conditions.append(getattr(self.TABLE, column).like(LoginName))
conditions = self._append_where_filter(conditions, self.TABLE,
self.where)
filter_condition = and_(*conditions)
result = self.session.query(self.TABLE).filter(filter_condition)
for r in result:
if userid != "": # pragma: no cover
raise Exception("More than one user with loginname"
" %s found!" % LoginName)
user = self._get_user_from_mapped_object(r)
userid = user["id"]
except Exception as exx: # pragma: no cover
log.error("Could not get the userinformation: {0!r}".format(exx))
return userid
def _get_user_from_mapped_object(self, ro):
"""
:param ro: row
:type ro: Mapped Object
:return: User
:rtype: dict
"""
r = ro.__dict__
user = {}
try:
if self.map.get("userid") in r:
user["id"] = r[self.map.get("userid")]
except UnicodeEncodeError: # pragma: no cover
log.error("Failed to convert user: {0!r}".format(r))
log.debug("{0!s}".format(traceback.format_exc()))
for key in self.map.keys():
try:
raw_value = r.get(self.map.get(key))
if raw_value:
if type(raw_value) == str:
val = raw_value.decode(self.encoding)
else:
val = raw_value
user[key] = val
except UnicodeDecodeError: # pragma: no cover
user[key] = "decoding_error"
log.error("Failed to convert user: {0!r}".format(r))
log.debug("{0!s}".format(traceback.format_exc()))
return user
def getUserList(self, searchDict=None):
"""
:param searchDict: A dictionary with search parameters
:type searchDict: dict
:return: list of users, where each user is a dictionary
"""
users = []
conditions = []
if searchDict is None:
searchDict = {}
for key in searchDict.keys():
column = self.map.get(key)
value = searchDict.get(key)
value = value.replace("*", "%")
conditions.append(getattr(self.TABLE, column).like(value))
conditions = self._append_where_filter(conditions, self.TABLE,
self.where)
filter_condition = and_(*conditions)
result = self.session.query(self.TABLE).\
filter(filter_condition).\
limit(self.limit)
for r in result:
user = self._get_user_from_mapped_object(r)
if "id" in user:
users.append(user)
return users
def getResolverId(self):
"""
Returns the resolver Id
This should be an Identifier of the resolver, preferable the type
and the name of the resolver.
"""
# Take the following parts, join them with the NULL byte and return
# the hexlified SHA-1 digest
id_parts = (to_utf8(self.connect_string),
str(self.pool_size),
str(self.pool_recycle),
str(self.pool_timeout))
resolver_id = binascii.hexlify(hashlib.sha1("\x00".join(id_parts)).digest())
return "sql." + resolver_id
@staticmethod
def getResolverClassType():
return 'sqlresolver'
@staticmethod
def getResolverType():
return IdResolver.getResolverClassType()
def loadConfig(self, config):
"""
Load the config from conf.
:param config: The configuration from the Config Table
:type config: dict
"""
self.server = config.get('Server', "")
self.driver = config.get('Driver', "")
self.database = config.get('Database', "")
self.resolverId = self.database
self.port = config.get('Port', "")
self.limit = config.get('Limit', 100)
self.user = config.get('User', "")
self.password = config.get('Password', "")
self.table = config.get('Table', "")
self._editable = config.get("Editable", False)
self.password_hash_type = config.get("Password_Hash_Type", "SSHA256")
usermap = config.get('Map', {})
self.map = yaml.safe_load(usermap)
self.reverse_map = dict([[v, k] for k, v in self.map.items()])
self.where = config.get('Where', "")
self.encoding = str(config.get('Encoding') or "latin1")
self.conParams = config.get('conParams', "")
self.pool_size = int(config.get('poolSize') or 5)
self.pool_timeout = int(config.get('poolTimeout') or 10)
# recycle SQL connections after 2 hours by default
# (necessary for MySQL servers, which terminate idle connections after some hours)
self.pool_recycle = int(config.get('poolRecycle') or 7200)
# create the connectstring like
params = {'Port': self.port,
'Password': self.password,
'conParams': self.conParams,
'Driver': self.driver,
'User': self.user,
'Server': self.server,
'Database': self.database}
self.connect_string = self._create_connect_string(params)
# get an engine from the engine registry, using self.getResolverId() as the key,
# which involves the connect string and the pool settings.
self.engine = get_engine(self.getResolverId(), self._create_engine)
# We use ``scoped_session`` to be sure that the SQLSoup object
# also uses ``self.session``.
Session = scoped_session(sessionmaker(bind=self.engine))
# Session should be closed on teardown
self.session = Session()
register_finalizer(self.session.close)
self.session._model_changes = {}
self.db = SQLSoup(self.engine, session=Session)
self.db.session._model_changes = {}
self.TABLE = self.db.entity(self.table)
return self
def _create_engine(self):
log.info(u"using the connect string {0!s}".format(censor_connect_string(self.connect_string)))
try:
log.debug("using pool_size={0!s}, pool_timeout={1!s}, pool_recycle={2!s}".format(
self.pool_size, self.pool_timeout, self.pool_recycle))
engine = create_engine(self.connect_string,
encoding=self.encoding,
convert_unicode=False,
pool_size=self.pool_size,
pool_recycle=self.pool_recycle,
pool_timeout=self.pool_timeout)
except TypeError:
# The DB Engine/Poolclass might not support the pool_size.
log.debug("connecting without pool_size.")
engine = create_engine(self.connect_string,
encoding=self.encoding,
convert_unicode=False)
return engine
@classmethod
def getResolverClassDescriptor(cls):
descriptor = {}
typ = cls.getResolverType()
descriptor['clazz'] = "useridresolver.SQLIdResolver.IdResolver"
descriptor['config'] = {'Server': 'string',
'Driver': 'string',
'Database': 'string',
'User': '******',
'Password': '******',
'Password_Hash_Type': 'string',
'Port': 'int',
'Limit': 'int',
'Table': 'string',
'Map': 'string',
'Where': 'string',
'Editable': 'int',
'poolTimeout': 'int',
'poolSize': 'int',
'poolRecycle': 'int',
'Encoding': 'string',
'conParams': 'string'}
return {typ: descriptor}
@staticmethod
def getResolverDescriptor():
return IdResolver.getResolverClassDescriptor()
@staticmethod
def _create_connect_string(param):
"""
create the connectstring
Port, Password, conParams, Driver, User,
Server, Database
"""
port = ""
password = ""
conParams = ""
if param.get("Port"):
port = u":{0!s}".format(param.get("Port"))
if param.get("Password"):
password = u":{0!s}".format(param.get("Password"))
if param.get("conParams"):
conParams = u"?{0!s}".format(param.get("conParams"))
connect_string = u"{0!s}://{1!s}{2!s}{3!s}{4!s}{5!s}/{6!s}{7!s}".format(param.get("Driver", ""),
param.get("User", ""),
password,
"@" if (param.get("User")
or
password) else "",
param.get("Server", ""),
port,
param.get("Database", ""),
conParams)
# SQLAlchemy does not like a unicode connect string!
if param.get("Driver").lower() == "sqlite":
connect_string = str(connect_string)
return connect_string
@classmethod
def testconnection(cls, param):
"""
This function lets you test the to be saved SQL connection.
:param param: A dictionary with all necessary parameter
to test the connection.
:type param: dict
:return: Tuple of success and a description
:rtype: (bool, string)
Parameters are: Server, Driver, Database, User, Password, Port,
Limit, Table, Map
Where, Encoding, conParams
"""
num = -1
desc = None
connect_string = cls._create_connect_string(param)
log.info(u"using the connect string {0!s}".format(censor_connect_string(connect_string)))
engine = create_engine(connect_string)
# create a configured "Session" class
Session = scoped_session(sessionmaker(bind=engine))
session = Session()
db = SQLSoup(engine, session=Session)
try:
TABLE = db.entity(param.get("Table"))
conditions = cls._append_where_filter([], TABLE,
param.get("Where"))
filter_condition = and_(*conditions)
result = session.query(TABLE).filter(filter_condition).count()
num = result
desc = "Found {0:d} users.".format(num)
except Exception as exx:
desc = "failed to retrieve users: {0!s}".format(exx)
finally:
# We do not want any leftover DB connection, so we first need to close
# the session such that the DB connection gets returned to the pool (it
# is still open at that point!) and then dispose the engine such that the
# checked-in connection gets closed.
session.close()
engine.dispose()
return num, desc
def add_user(self, attributes=None):
"""
Add a new user to the SQL database.
attributes are these
"username", "surname", "givenname", "email",
"mobile", "phone", "password"
:param attributes: Attributes according to the attribute mapping
:return: The new UID of the user. The UserIdResolver needs to
determine the way how to create the UID.
"""
attributes = attributes or {}
kwargs = self.prepare_attributes_for_db(attributes)
log.debug("Insert new user with attributes {0!s}".format(kwargs))
r = self.TABLE.insert(**kwargs)
self.db.commit()
# Return the UID of the new object
return getattr(r, self.map.get("userid"))
def prepare_attributes_for_db(self, attributes):
"""
Given a dictionary of attributes, return a dictionary
mapping columns to values.
If the attributes contain a password, hash the password according to the
configured password hash type.
:param attributes: attributes dictionary
:return: dictionary with column name as keys
"""
attributes = attributes.copy()
if "password" in attributes:
attributes["password"] = hash_password(attributes["password"],
self.password_hash_type)
columns = {}
for fieldname in attributes:
if fieldname in self.map:
columns[self.map[fieldname]] = attributes[fieldname]
return columns
def delete_user(self, uid):
"""
Delete a user from the SQL database.
The user is referenced by the user id.
:param uid: The uid of the user object, that should be deleted.
:type uid: basestring
:return: Returns True in case of success
:rtype: bool
"""
res = True
try:
conditions = []
column = self.map.get("userid")
conditions.append(getattr(self.TABLE, column).like(uid))
conditions = self._append_where_filter(conditions, self.TABLE,
self.where)
filter_condition = and_(*conditions)
user_obj = self.session.query(self.TABLE).filter(
filter_condition).first()
self.session.delete(user_obj)
self.session.commit()
except Exception as exx:
log.error("Error deleting user: {0!s}".format(exx))
res = False
return res
def update_user(self, uid, attributes=None):
"""
Update an existing user.
This function is also used to update the password. Since the
attribute mapping know, which field contains the password,
this function can also take care for password changing.
Attributes that are not contained in the dict attributes are not
modified.
:param uid: The uid of the user object in the resolver.
:type uid: basestring
:param attributes: Attributes to be updated.
:type attributes: dict
:return: True in case of success
"""
attributes = attributes or {}
params = self.prepare_attributes_for_db(attributes)
kwargs = {self.map.get("userid"): uid}
r = self.TABLE.filter_by(**kwargs).update(params)
self.db.commit()
return r
@property
def editable(self):
"""
Return true, if the instance of the resolver is configured editable
:return:
"""
# Depending on the database this might look different
# Usually this is "1"
return is_true(self._editable)
@site.after_request
def insert_lang_cookie(response):
"""
Make user locale selection persistent using a cookie.
"""
if 'lang' in request.args:
response.set_cookie('lang', request.args['lang'])
return response
# Use SQLSoup for database access.
db = SQLSoup(site.config['SQLSOUP_DATABASE_URI'])
# Specify primary keys for SQLSoup to allow us to work with views.
map_view(db, 'my_subscriptions', ['id'])
map_view(db, 'my_channels', ['id'])
map_view(db, 'my_campaigns', ['id'])
map_view(db, 'recipients', ['user', 'channel'])
# Specify automatic row relations.
db.campaign.relate('Channel', db.channel)
db.my_campaigns.relate('Channel', db.channel,
primaryjoin=(db.channel.c.id == db.my_campaigns.c.channel),
foreign_keys=[db.my_campaigns.c.channel])
db.channel.relate('Template', db.template)
class IdResolver (UserIdResolver):
searchFields = {
"username": "******",
"userid": "numeric",
"phone": "text",
"mobile": "text",
"surname": "text",
"givenname": "text",
"email": "text"
}
@classmethod
def setup(cls, config=None, cache_dir=None):
'''
this setup hook is triggered, when the server
starts to serve the first request
:param config: the privacyidea config
:type config: the privacyidea config dict
'''
log.info("Setting up the SQLResolver")
return
def __init__(self):
self.resolverId = ""
self.server = ""
self.driver = ""
self.database = ""
self.port = 0
self.limit = 100
self.user = ""
self.password = ""
self.table = ""
self.map = {}
self.reverse_map = {}
self.where = ""
self.encoding = ""
self.conParams = ""
self.connect_string = ""
self.session = None
return
def getSearchFields(self):
return self.searchFields
def checkPass(self, uid, password):
"""
This function checks the password for a given uid.
- returns true in case of success
- false if password does not match
"""
def _check_ssha(pw_hash, password, hashfunc, length):
pw_hash_bin = b64decode(pw_hash.split("}")[1])
digest = pw_hash_bin[:length]
salt = pw_hash_bin[length:]
hr = hashfunc(password)
hr.update(salt)
return digest == hr.digest()
def _check_sha(pw_hash, password):
pw_hash_bin = b64decode(pw_hash[6:])
digest = pw_hash_bin[:20]
hr = hashlib.sha1(password)
return digest == hr.digest()
def _otrs_sha256(pw_hash, password):
hr = hashlib.sha256(password)
digest = binascii.hexlify(hr.digest())
return pw_hash == digest
res = False
userinfo = self.getUserInfo(uid)
database_pw = userinfo.get("password", "XXXXXXX")
if database_pw[:2] == "$P":
# We have a phpass (wordpress) password
PH = PasswordHash()
res = PH.check_password(password, userinfo.get("password"))
# check salted hashed passwords
elif database_pw[:6].upper() == "{SSHA}":
res = _check_ssha(database_pw, password, hashlib.sha1, 20)
elif database_pw[:6].upper() == "{SSHA256}":
res = _check_ssha(database_pw, password, hashlib.sha256, 32)
elif database_pw[:6].upper() == "{SSHA512}":
res = _check_ssha(database_pw, password, hashlib.sha512, 64)
# check for hashed password.
elif userinfo.get("password", "XXXXX")[:5].upper() == "{SHA}":
res = _check_sha(database_pw, password)
elif len(userinfo.get("password"))==64:
# OTRS sha256 password
res = _otrs_sha256(database_pw, password)
return res
def getUserInfo(self,userId):
'''
This function returns all user info for a given userid/object.
:param userId: The userid of the object
:type userId: string
:return: A dictionary with the keys defined in self.map
:rtype: dict
'''
userinfo = {}
try:
conditions=[]
column = self.map.get("userid")
conditions.append( getattr(self.TABLE, column).like(userId) )
filter_condition = and_(*conditions)
result = self.session.query(self.TABLE).filter(filter_condition)
for r in result:
if len(userinfo.keys())>0:
raise Exception("More than one user with userid %s found!" % userId)
userinfo = self._get_user_from_mapped_object(r)
except Exception as exx:
log.error("Could not get the userinformation: %r" % exx)
return userinfo
def getUsername(self,userId):
'''
returns true, if a user id exists
'''
info = self.getUserInfo(userId)
return info.has_key('username')
def getUserId(self, LoginName):
'''
resolve the loginname to the userid.
:param LoginName: The login name from the credentials
:type LoginName: string
:return: UserId as found for the LoginName
'''
userid = ""
try:
conditions=[]
column = self.map.get("username")
conditions.append( getattr(self.TABLE, column).like(LoginName) )
filter_condition = and_(*conditions)
result = self.session.query(self.TABLE).filter(filter_condition)
for r in result:
if userid != "":
raise Exception("More than one user with loginname %s found!" % LoginName)
user = self._get_user_from_mapped_object(r)
userid = user["id"]
except Exception as exx:
log.error("Could not get the userinformation: %r" % exx)
return userid
def _get_user_from_mapped_object(self, ro):
'''
:param r: row
:type r: Mapped Object
:return: User
:rtype: dict
'''
r = ro.__dict__
user = {}
try:
if r.has_key(self.map.get("userid")):
user["id"] = r[self.map.get("userid")]
if r.has_key(self.map.get("username")):
user["username"] = r[self.map.get("username")].decode(self.encoding)
if r.has_key(self.map.get("surname")):
user["surname"] = r[self.map.get("surname")].decode(self.encoding)
if r.has_key(self.map.get("givenname")):
user["givenname"] = r[self.map.get("givenname")].decode(self.encoding)
if r.has_key(self.map.get("email")):
user["email"] = r[self.map.get("email")].decode(self.encoding)
if r.has_key(self.map.get("mobile")):
user["mobile"] = r[self.map.get("mobile")].decode(self.encoding)
if r.has_key(self.map.get("phone")):
user["phone"] = r[self.map.get("phone")].decode(self.encoding)
if r.has_key(self.map.get("password")):
user["password"] = r[self.map.get("password")]
except:
log.error("Failed to convert user: %r" % r)
log.error(traceback.format_exc())
return user
def getUserList(self, searchDict=None):
'''
:param searchDict: A dictionary with search parameters
:type searchDict: dict
:return: list of users, where each user is a dictionary
'''
users = []
conditions = []
if searchDict == None:
searchDict = {}
for key in searchDict.keys():
column = self.map.get(key)
value = searchDict.get(key)
value = value.replace("*", "%")
conditions.append( getattr(self.TABLE, column).like(value) )
if self.where:
# this might result in erros if the
# administrator enters nonsense
(w_column, w_cond, w_value) = self.where.split()
if w_cond.lower() == "like":
conditions.append( getattr(self.TABLE, w_column).like(w_value))
elif w_cond == "==":
conditions.append(getattr(self.TABLE, w_column) == w_value)
elif w_cond == ">":
conditions.append(getattr(self.TABLE, w_column) > w_value)
elif w_cond == "<":
conditions.append(getattr(self.TABLE, w_column) < w_value)
filter_condition = and_(*conditions)
result = self.session.query(self.TABLE).filter(filter_condition).limit(self.limit)
for r in result:
user = self._get_user_from_mapped_object(r)
if user.has_key("id"):
users.append(user)
return users
def getResolverId(self):
'''
Returns the resolver Id
This should be an Identifier of the resolver, preferable the type and the name of the resolver.
'''
return "sqlresolver." + self.resolverId
@classmethod
def getResolverClassType(cls):
return 'sqlresolver'
def getResolverType(self):
return IdResolver.getResolverClassType()
def loadConfig(self, config, conf):
'''
Load the config from conf.
:param config: The configuration from the Config Table
:type config: dict
:param conf: the instance of the configuration
:type conf: string
The information which config entries we need to load is taken from
manage.js: function save_sql_config
'''
self.resolverId = conf
self.server = self.getConfigEntry(config, 'privacyidea.sqlresolver.Server', conf)
self.driver = self.getConfigEntry(config, 'privacyidea.sqlresolver.Driver', conf)
self.database = self.getConfigEntry(config, 'privacyidea.sqlresolver.Database', conf)
self.port = self.getConfigEntry(config, 'privacyidea.sqlresolver.Port', conf, required=False)
self.limit = self.getConfigEntry(config, 'privacyidea.sqlresolver.Limit', conf, required=False, default=100)
self.user = self.getConfigEntry(config, 'privacyidea.sqlresolver.User', conf)
self.password = self.getConfigEntry(config, 'privacyidea.sqlresolver.Password', conf, required=False)
self.table = self.getConfigEntry(config, 'privacyidea.sqlresolver.Table', conf)
usermap = self.getConfigEntry(config, 'privacyidea.sqlresolver.Map', conf)
self.map = yaml.load(usermap)
self.reverse_map = dict([[v,k] for k,v in self.map.items()])
self.where = self.getConfigEntry(config, 'privacyidea.sqlresolver.Where', conf, required=False)
self.encoding = self.getConfigEntry(config, 'privacyidea.sqlresolver.Encoding', conf, required=False, default="latin1")
self.conParams = self.getConfigEntry(config, 'privacyidea.sqlresolver.conParams', conf, required=False)
# create the connectstring like
# driver://user:passwd@seerver/database?conParams
port = ""
password = ""
if self.port:
port = ":%s" % self.port
if self.password:
password = "******" % self.password
self.connect_string = "%s://%s%s@%s%s/%s?%s" % (self.driver,
self.user,
password,
self.server,
port,
self.database,
self.conParams)
log.info("using the connect string %s" % self.connect_string)
self.engine = create_engine(self.connect_string, encoding=str(self.encoding))
# create a configured "Session" class
Session = sessionmaker(bind=self.engine)
# create a Session
self.session = Session()
self.db = SQLSoup(self.engine)
self.TABLE = self.db.entity(self.table)
return self
def getResolverDescriptor(self):
descriptor = {}
typ = self.getResolverType()
descriptor['clazz'] = "useridresolver.SQLIdResolver.IdResolver"
descriptor['config'] = {'Server' : 'string',
'Driver' : 'string',
'Database' : 'string',
'User' : 'string',
'Password' : 'string',
'Port' : 'int',
'Limit' : 'int',
'Table' : 'string',
'Map' : 'string',
'Where' : 'string',
'Encoding' : 'string',
'conParams' : 'string'}
return {typ : descriptor}
def getConfigEntry(self, config, key, conf, required=True, default=None):
ckey = key
cval = ""
if conf != "" or None:
ckey = ckey + "." + conf
if config.has_key(ckey):
cval = config[ckey]
if cval == "":
if config.has_key(key):
cval = config[key]
if cval == "" and required == True:
raise Exception("missing config entry: " + key)
if cval == "" and default:
cval = default
return cval
@classmethod
def testconnection(self, param):
'''
This function lets you test the to be saved SQL connection.
:param param: A dictionary with all necessary parameter to test the connection.
:type param: dict
:return: Tuple of success and a description
:rtype: (bool, string)
Parameters are: Server, Driver, Database, User, Password, Port, Limit, Table, Map
Where, Encoding, conParams
'''
num=-1
desc=None
port = ""
password = ""
conParams = ""
if param.get("Port"):
port = ":%s" % param.get("Port")
if param.get("Password"):
password = "******" % param.get("Password")
if param.get("conParams"):
conParams = "?%s" % param.get("conParams")
connect_string = "%s://%s%s%s%s%s/%s%s" % (param.get("Driver"),
param.get("User"),
password,
"@" if (param.get("User") or password) else "",
param.get("Server"),
port,
param.get("Database"),
conParams)
log.info("using the connect string %s" % connect_string)
engine = create_engine(connect_string)
# create a configured "Session" class
session = sessionmaker(bind=engine)()
db = SQLSoup(engine)
TABLE = db.entity(param.get("Table"))
try:
result = session.query(TABLE).count()
num = result
desc = "Found %i users." % num
except Exception as exx:
desc = "failed to retrieve users: %s" % exx
return (num, desc)
vars = _select.lower().split('select ')[1].split(',')
if len(vars) == 1:
_select = _select.replace('select', 'select shot,')
# default order is by shot
if _order is '':
if len(vars) is 1:
_order = 'order by shot'
else:
_order = 'order by ' + vars[0]
url_cpts = db_url.split('sqlite:///')
if not os.path.exists(url_cpts[-1]):
input('{fn} could not be found!!\n'.format(fn=url_cpts[-1]))
h1db = SQLSoup(db_url)
# Note - this info. is here now, but disappears if there is an error - save
tables = list(h1db._cache)
print(tables if len(tables) > 0 else ' possibly no tables in ' + db_url +
" although this doesn't mean anything sometimes")
if len(tables) == 0 and block == 1:
input('Continue?')
try:
h1db.SUMM = eval('h1db.' + table)
except Exception as reason:
print(reason.__repr__(), ' table names are ', tables)
raise
cols = h1db.SUMM.c.keys()
if plabel is not '':
def connect(self):
"""Connect to remote join backend (DB)"""
self.db = SQLSoup(self.connect_string)
from fastapi import FastAPI, HTTPException
from sqlsoup import SQLSoup
from api_test import db, model
app = FastAPI()
engine = db.get_db_engine()
db = SQLSoup(engine)
@app.get("/")
def read_root():
return {"hello": "world"}
@app.get("/counts")
def read_count_ids(only_latest: bool = False):
""" Get all counts
Field `only_latest` is optional.
If True, will only return the lastest count per device_id"""
counts = db.counts.all()
if only_latest:
return_dict = {}
for count in counts:
if count.device_id not in return_dict or count.timestamp > return_dict[
count.device_id].timestamp:
return_dict[count.device_id] = count
return return_dict
from sqlsoup import SQLSoup
# server details
SERVER = os.environ.get("SERVER", "localhost")
USERNAME = os.environ.get("DB_USERNAME", "sa")
PASSWORD = os.environ.get("DB_PASSWORD", "reallyStrongPwd123")
DATABASE = os.environ.get("DB_NAME", "DW_AccessBI")
PORT = os.environ.get("DB_PORT", 1433)
# load data from SQL Server
# db_connection = pymssql.connect(
# server=SERVER, user=USERNAME, password=PASSWORD, database=DATABASE, port=PORT)
conn_str = "mssql+pymssql://{user}:{password}@{server}/{database}?charset=utf8".format(
user=USERNAME, password=PASSWORD, server=SERVER, database=DATABASE)
sqlsoup_obj = SQLSoup(conn_str)
engine = sqlsoup_obj.engine
def get_data_from_sqlserver(sql_string, connection_obj=engine):
df = pd.read_sql(sql_string, connection_obj)
return df
def get_categorical_values(column, table_name=None):
if table_name is None:
table_name = "D_" + column.upper()
table = sqlsoup_obj.entity(table_name)
rows = table.all()
values = set()
poolclass=NullPool)
elif tipodb_source == "postgresql":
print "MIGRAZIONE DA POSTGRESQL A "
engine_source = create_engine('postgresql+psycopg2://' +
user_source + ":" +
password_source + "@" +
host_source + ":" +
port_source + "/" +
database_source,
convert_unicode=True,
encoding='utf-8',
poolclass=NullPool)
else:
print "ORIGINE MIGRAZIONE NON RICONOSCIUTO"
db_source = SQLSoup(engine_source)
if tipodb_source == "postgresql":
db_source_main = SQLSoup(engine_source)
db_source.schema = azienda_source
db_source_main.schema = "promogest2"
if tipodb_source == "postgresql":
meta_source = MetaData()
meta_source_main = MetaData()
meta_source_main.reflect(bind=engine_source, schema="promogest2")
meta_source.reflect(bind=engine_source, schema=azienda_source)
else:
meta_source = MetaData()
meta_source.reflect(bind=engine_source)
