def edit_userinfo(userinfo_diff, connstr, table=UserInfo, remove=False):
if userinfo_diff is None:
return
with sqla_session(connstr) as session:
for diff_user in userinfo_diff:
username = diff_user['username']
comment = diff_user['comment']
reason = diff_user['reason']
userinfo = session.query(table).filter(
UserInfo.username == username).one_or_none()
if remove and userinfo is not None:
print('Removing user {}'.format(username))
session.delete(userinfo)
elif userinfo is None and not remove:
print('Adding user {}'.format(username))
new_userinfo = table()
new_userinfo.username = username
new_userinfo.comment = comment
new_userinfo.reason = reason
session.add(new_userinfo)
elif userinfo is not None:
if comment != userinfo.comment or reason != userinfo.reason:
print('Changing userinfo {}'.format(username))
userinfo.comment = comment
userinfo.reason = reason
session.commit()
def get(cls):
oui_list = []
with sqla_session() as session:
ouis: DeviceOui = session.query(DeviceOui).all()
for oui in ouis:
oui_list.append(oui.as_dict())
return oui_list
def edit_users(diff, connstr, remove=False):
if diff is None:
return
with sqla_session(connstr) as session:
for diff_user in diff:
username = diff_user['username']
attribute = diff_user['attribute']
op = diff_user['op']
value = diff_user['value']
user = session.query(User).filter(
User.username == username).one_or_none()
if remove and user is not None:
print('Removing user {}'.format(username))
session.delete(user)
elif user is None and not remove:
print('Adding user {}'.format(username))
new_user = User()
new_user.username = username
new_user.attribute = attribute
new_user.op = op
new_user.value = value
session.add(new_user)
elif user is not None:
if attribute != user.attribute or op != user.op or value != user.value:
print('Changing user {}'.format(username))
user.attribute = attribute
user.op = op
user.value = value
session.commit()
def edit_replies(diff, connstr, remove=False):
if diff is None:
return
with sqla_session(connstr) as session:
for diff_user in diff:
username = diff_user['username']
attribute = diff_user['attribute']
op = diff_user['op']
value = diff_user['value']
reply = session.query(Reply).filter(
Reply.username == username).filter(
Reply.attribute == attribute).one_or_none()
if remove and reply is not None:
print('Removing reply {}'.format(username))
session.delete(reply)
elif reply is None and not remove:
print('Adding reply {}'.format(username))
new_reply = Reply()
new_reply.username = username
new_reply.attribute = attribute
new_reply.op = op
new_reply.value = value
session.add(new_reply)
elif reply is not None:
if attribute != reply.attribute or op != reply.op or value != reply.value:
print('Changing reply {}'.format(username))
reply.attribute = attribute
reply.op = op
reply.value = value
session.commit()
def get_vlan(cls, username):
oui = cls.normalize(username)
with sqla_session() as session:
oui: DeviceOui = session.query(DeviceOui).filter(
DeviceOui.oui == oui).one_or_none()
if oui:
return oui.vlan
return None
def exists(cls, oui):
exists = False
oui = cls.normalize(oui)
with sqla_session() as session:
oui: DeviceOui = session.query(DeviceOui).filter(
DeviceOui.oui == oui).one_or_none()
if oui:
exists = True
return exists
def disable(cls, username):
with sqla_session() as session:
user: User = session.query(User).filter(
User.username == username).order_by(User.id).one_or_none()
if not user:
return 'Username not found'
user.attribute = 'Cleartext-Password'
user.op = ''
return ''
def delete(cls, username):
with sqla_session() as session:
res = session.query(UserInfo).filter(
UserInfo.username == username).one_or_none()
if res is None:
return 'Could not find user'
session.delete(res)
return ''
def is_enabled(cls, username):
with sqla_session() as session:
instance = session.query(User).filter(
User.username == username).one_or_none()
if not instance:
return None
if instance.op != ':=':
return False
return True
def delete(cls, username):
if not cls.get(username):
return ''
with sqla_session() as session:
nas_ports: NasPort = session.query(NasPort).filter(
NasPort.username == username).all()
for port in nas_ports:
session.delete(port)
session.commit()
return ''
def reply_vlan(cls, username, vlan):
with sqla_session() as session:
instance = session.query(Reply).filter(
Reply.username == username).filter(
Reply.attribute ==
'Tunnel-Private-Group-Id').one_or_none()
if not instance:
return 'Reply not found'
instance.value = vlan
return ''
def reply_delete(cls, username):
with sqla_session() as session:
instance = session.query(Reply).filter(
Reply.username == username).all()
if not instance:
return 'Reply not found'
for _ in instance:
session.delete(_)
session.commit()
return ''
def is_enabled(cls, username):
enabled = False
with sqla_session() as session:
user: User = session.query(User).filter(
User.username == username).order_by(User.id).one_or_none()
if not user:
return None
if user.op == ':=':
enabled = True
return enabled
def delete(cls, username):
with sqla_session() as session:
instance = session.query(User).filter(
User.username == username).all()
if not instance:
return 'Username not found'
for _ in instance:
session.delete(_)
session.commit()
return ''
def add(cls, username, password):
if cls.get(username) != []:
return 'User already exists'
with sqla_session() as session:
new_user = User()
new_user.username = username
new_user.attribute = 'Cleartext-Password'
new_user.value = password
new_user.op = ''
session.add(new_user)
return ''
def get_rows(connstr: str, table: object) -> list:
with sqla_session(connstr) as session:
rows_list = []
if table == Accounting:
rows = session.query(table).order_by(table.radacctid).all()
else:
rows = session.query(table).order_by(table.id).all()
for row in rows:
rows_list.append(row.as_dict())
return rows_list
def reply_get(cls, username=None):
result = []
with sqla_session() as session:
if username:
replymsg: Reply = session.query(Reply).filter(
Reply.username == username).order_by(Reply.id).all()
else:
replymsg: Reply = session.query(Reply).order_by(Reply.id).all()
if replymsg is None:
return result
for _ in replymsg:
result.append(_.as_dict())
return result
def get(cls, username=None):
res = []
with sqla_session() as session:
if username:
nas: NasPort = session.query(NasPort).filter(
NasPort.username == username).all()
else:
nas: NasPort = session.query(NasPort).all()
if nas is None:
return None
for port in nas:
res.append(port.as_dict())
return res
def add(cls, username, nas_ip_address, nas_identifier, nas_port_id,
calling_station_id, called_station_id):
if cls.get(username):
return ''
with sqla_session() as session:
nas = NasPort()
nas.username = username
nas.nas_ip_address = nas_ip_address
nas.nas_identifier = nas_identifier
nas.nas_port_id = nas_port_id
nas.calling_station_id = calling_station_id
nas.called_station_id = called_station_id
session.add(nas)
return ''
def get(cls, usernames=[]):
res = []
users = dict()
with sqla_session() as session:
for username in usernames:
userinfo = session.query(UserInfo).filter(
UserInfo.username == username).one_or_none()
user_info = dict()
if not userinfo:
user_info['comment'] = ''
user_info['reason'] = ''
else:
user_info['comment'] = userinfo.comment
user_info['reason'] = userinfo.reason
users[username] = user_info
return users
def add(cls, username, comment='', reason=''):
with sqla_session() as session:
res = session.query(UserInfo).filter(
UserInfo.username == username).one_or_none()
if res is not None:
if comment != '':
res.comment = comment
res.reason = reason
else:
user = UserInfo()
user.username = username
user.reason = reason
user.comment = comment
session.add(user)
return ''
def get_last_seen(cls, usernames=[], last=True):
res = []
users = dict()
with sqla_session() as session:
for username in usernames:
postauth = session.query(PostAuth).filter(
PostAuth.username == username).order_by(desc(
(PostAuth.id))).limit(1).one_or_none()
last_seen = dict()
if not postauth:
last_seen['authdate'] = ''
last_seen['reply'] = ''
else:
last_seen['authdate'] = postauth.authdate
last_seen['reply'] = postauth.reply
users[username] = last_seen
return users
def get(cls, username=''):
result = []
with sqla_session() as session:
result = []
if username == '':
query = session.query(User).order_by('username')
else:
query = session.query(User).filter(
User.username == username).all()
for _ in query:
user = _.as_dict()
user_dict = dict()
user_dict['id'] = user['id']
user_dict['username'] = user['username']
user_dict['op'] = user['op']
user_dict['attribute'] = user['attribute']
result.append(user_dict)
return result
def get_users(username=''):
result = []
with sqla_session() as session:
if username == '':
res = session.query(
User, Reply,
NasPort).filter(User.username == NasPort.username).filter(
Reply.username == User.username).filter(
Reply.attribute == 'Tunnel-Private-Group-Id').order_by(
User.username).all()
else:
res = session.query(User, Reply, NasPort).filter(
User.username ==
username).filter(User.username == NasPort.username).filter(
Reply.username == User.username).filter(
Reply.attribute == 'Tunnel-Private-Group-Id').order_by(
User.username).all()
usernames = []
for user, reply, nas_port in res:
usernames.append(user.username)
userinfos = UserInfo.get(usernames=usernames)
for user, reply, nas_port in res:
res_dict = dict()
res_dict['username'] = user.username
if user.op == ':=':
res_dict['active'] = True
else:
res_dict['active'] = False
res_dict['vlan'] = reply.value
res_dict['nas_identifier'] = nas_port.nas_identifier
res_dict['nas_port_id'] = nas_port.nas_port_id
res_dict['nas_ip_address'] = nas_port.nas_ip_address
res_dict['calling_station_id'] = nas_port.calling_station_id
res_dict['called_station_id'] = nas_port.called_station_id
res_dict['comment'] = userinfos[user.username]['comment']
res_dict['reason'] = userinfos[user.username]['reason']
result.append(res_dict)
return result
def edit_nas(nas_diff, connstr, table=NasPort, remove=False):
if nas_diff is None:
return
with sqla_session(connstr) as session:
for diff_user in nas_diff:
username = diff_user['username']
nas_identifier = diff_user['nas_identifier']
nas_port_id = diff_user['nas_port_id']
nas_ip_address = diff_user['nas_ip_address']
calling_station_id = diff_user['calling_station_id']
called_station_id = diff_user['called_station_id']
nas = session.query(table).filter(
NasPort.username == username).one_or_none()
if remove and nas is not None:
print('Removing user {}'.format(username))
session.delete(nas)
elif nas is None and not remove:
print('Adding user {}'.format(username))
new_nas = table()
new_nas.username = username
new_nas.nas_identifier = nas_identifier
new_nas.nas_port_id = nas_port_id
new_nas.nas_ip_address = nas_ip_address
new_nas.calling_station_id = calling_station_id
new_nas.called_station_id = called_station_id
session.add(new_nas)
elif nas is not None:
if nas_identifier != nas.nas_identifier or nas_port_id != nas.nas_port_id or nas_ip_address != nas.nas_ip_address or calling_station_id != nas.calling_station_id or called_station_id != nas.called_station_id:
print('Changing nas {}'.format(username))
nas.nas_identifier = nas_identifier
nas.nas_port_id = nas_port_id
nas.nas_ip_address = nas_ip_address
nas.calling_station_id = calling_station_id
nas.called_station_id = called_station_id
session.commit()
def reply_add(cls, username, vlan):
if cls.reply_get(username) != []:
return 'Reply already exists'
with sqla_session() as session:
tunnel_type = Reply()
tunnel_type.username = username
tunnel_type.attribute = 'Tunnel-Type'
tunnel_type.op = '='
tunnel_type.value = 'VLAN'
session.add(tunnel_type)
medium_type = Reply()
medium_type.username = username
medium_type.attribute = 'Tunnel-Medium-Type'
medium_type.op = '='
medium_type.value = 'IEEE-802'
session.add(medium_type)
tunnel_id = Reply()
tunnel_id.username = username
tunnel_id.attribute = 'Tunnel-Private-Group-Id'
tunnel_id.op = '='
tunnel_id.value = vlan
session.add(tunnel_id)
return ''
def copy_accounting(diffs, connstr, table=Accounting):
if diffs is None or diffs == []:
return
with sqla_session(connstr) as session:
for diff in diffs:
if diff is None:
continue
radacctid = diff['radacctid']
acctsessionid = diff['acctsessionid']
acctuniqueid = diff['acctuniqueid']
username = diff['username']
groupname = diff['groupname']
realm = diff['realm']
nasipaddress = diff['nasipaddress']
nasportid = diff['nasportid']
nasporttype = diff['nasporttype']
acctstarttime = diff['acctstarttime']
acctupdatetime = diff['acctupdatetime']
acctstoptime = diff['acctstoptime']
acctinterval = diff['acctinterval']
acctsessiontime = diff['acctsessiontime']
acctauthentic = diff['acctauthentic']
connectinfo_start = diff['connectinfo_start']
connectinfo_stop = diff['connectinfo_stop']
acctinputoctets = diff['acctinputoctets']
acctoutputoctets = diff['acctoutputoctets']
calledstationid = diff['calledstationid']
callingstationid = diff['callingstationid']
acctterminatecause = diff['acctterminatecause']
servicetype = diff['servicetype']
framedprotocol = diff['framedprotocol']
framedipaddress = diff['framedipaddress']
acct = session.query(table).filter(
Accounting.acctuniqueid == acctuniqueid).one_or_none()
if acct is None:
print('Adding accounting session {}'.format(acctsessionid))
new_acct = table()
new_acct.radacctid = radacctid
new_acct.acctsessionid = acctsessionid
new_acct.acctuniqueid = acctuniqueid
new_acct.username = username
new_acct.groupname = groupname
new_acct.realm = realm
new_acct.nasipaddress = nasipaddress
new_acct.nasportid = nasportid
new_acct.nasporttype = nasporttype
new_acct.acctstarttime = acctstarttime
new_acct.acctupdatetime = acctupdatetime
new_acct.acctstoptime = acctstoptime
new_acct.acctinterval = acctinterval
new_acct.acctsessiontime = acctsessiontime
new_acct.acctauthentic = acctauthentic
new_acct.connectinfo_start = connectinfo_start
new_acct.connectinfo_stop = connectinfo_stop
new_acct.acctinputoctets = acctinputoctets
new_acct.acctoutputoctets = acctoutputoctets
new_acct.calledstationid = calledstationid
new_acct.callingstationid = callingstationid
new_acct.acctterminatecause = acctterminatecause
new_acct.servicetype = servicetype
new_acct.framedprotocol = framedprotocol
new_acct.framedipaddress = framedipaddress
session.add(new_acct)
session.commit()
