def rbac_domains(req, resp): """Supplies a List of available domains. Supplies a List of available domains. Used when assigning a user's role. """ search = req.query_params.get('term') # (@vuader) Because the Root user can and have to be able to # assign Roles on ANY domain, and this view is used for that # purpose, we need to select ALL domains for the Root user. user_id = req.token.user_id if user_id == "00000000-0000-0000-0000-000000000000": sql = "SELECT name FROM luxon_domain" with db() as conn: cur = conn.execute(sql) result = cur.fetchall() domains_list = [] for r in result: domains_list.append(r['name']) else: domains_list = user_domains(req.token.user_id) if search is not None: filtered = [] for domain in domains_list: if search in domain: filtered.append(domain) return filtered return domains_list
def main(argv): # PLEASE STICK WITH THIS - ITS MORE SECURE unix_user = pwd.getpwuid(os.getuid())[0] if unix_user not in allowed: raise Exception( 'Not allowed user or ensure your authenticated via smtp' + '(%s)' % unix_user) config = g.config = Config() config.load('/var/www/tachweb/settings.ini') GetLogger().app_configure() msg = sys.stdin.read() msg = email.message_from_string(msg) with db() as conn: cursor = conn.execute('SELECT * FROM newslist') for rcpt in cursor: to = "%s <%s>" % ( rcpt['name'], rcpt['email'], ) new = format_msg(msg, html_template=html_template, text_template=text_template, email_from='*****@*****.**', email_to=to, multipart=True, token=rcpt['token'], name=rcpt['name']) try: send_email('*****@*****.**', to, msg=new) except Exception as e: log.critical('Failed to send to %s (%s)' % ( rcpt, e, ))
def acct(msg): try: pkt = msg['attributes'] except KeyError: return try: pkt = decode_packet(pkt) except Exception: return try: nas_session_id = pkt.get('Acct-Session-Id', [None])[0] unique_session_id = pkt.get('Acct-Unique-Session-Id')[0] status = pkt.get('Acct-Status-Type', [''])[0].lower() username = pkt.get('User-Name', [None])[0] client = pkt.get('Client-IP-Address')[0] nas = pkt.get('NAS-IP-Address', ['0.0.0.0'])[0] except IndexError: return True dt = utc(parse_datetime(msg.get('datetime', None))) diff = (now()-dt).total_seconds() if diff > 60: log.error('Processing radius accounting message older' + ' than 60 seconds. Age(%s)' % diff) with db() as conn: with dbw() as connw: with conn.cursor() as crsr: user = get_user(crsr, client, nas, username) crsr.commit() if not user: log.debug("user '%s' not found" % username) return False input_octets, output_octets = do_acct(connw, pkt, client, nas, nas_session_id, unique_session_id, dt, user, status) usage(connw, pkt, client, nas, nas_session_id, unique_session_id, user, input_octets, output_octets, status) return True
def db_crud(args): """Create/Update Database for given package Called when **-d** is used Args: args (parse_args object): arguments gathered from terminal """ with open(args.path.rstrip('/') + '/wsgi.py', 'r') as wsgi_file: exec_g = {} exec(wsgi_file.read(), exec_g, exec_g) # Backup Database model tables. backups = {} with db() as conn: # Backup Tables. backups = models.backup_tables(conn) # Drop Tables. models.drop_tables(conn) # Create Tables. models.create_tables() # Restore data. models.restore_tables(conn, backups)
def disconnect_user(msg): user_id = msg['user_id'] username = msg['username'] with db() as conn: result = conn.execute( 'SELECT' + ' id' + ',INET6_NTOA(nasipaddress) as nas' + ',INET6_NTOA(framedipaddress) as ip' + ',user_id' + ',acctsessionid' + ',acctupdated' + ' FROM calabiyau_session' + ' WHERE user_id = %s' + ' AND accttype != "stop"', user_id).fetchall() for session in result: session_id = session['id'] nas = session['nas'] ip = session['ip'] updated = session['acctupdated'] nas_session = session['acctsessionid'] secret = get_nas_secret(session['nas']) pod(nas, secret, username, nas_session) conn.execute( 'DELETE FROM calabiyau_session' + ' WHERE id = %s' ' AND acctupdated = %s', ( session_id, updated, )) conn.execute( 'UPDATE calabiyau_ippool' + ' SET expiry_time = NULL' + ' WHERE user_id = %s' + ' AND framedipaddress = INET6_ATON(%s)', ( user_id, ip, )) conn.commit()
def disconnect_session(msg): session_id = msg['session_id'] with db() as conn: result = conn.execute( 'SELECT' + ' INET6_NTOA(nasipaddress) as nas' + ',INET6_NTOA(framedipaddress) as ip' + ',user_id' + ',acctsessionid' + ',acctupdated' + ' FROM calabiyau_session' + ' WHERE id = %s', session_id).fetchone() if result: nas = result['nas'] ip = result['ip'] user_id = result['user_id'] username = get_username(user_id) updated = result['acctupdated'] nas_session = result['acctsessionid'] secret = get_nas_secret(result['nas']) pod(nas, secret, username, nas_session) conn.execute( 'DELETE FROM calabiyau_session' + ' WHERE id = %s' ' AND acctupdated = %s', ( session_id, updated, )) conn.execute( 'UPDATE calabiyau_ippool' + ' SET expiry_time = NULL' + ' WHERE user_id = %s' + ' AND framedipaddress = INET6_ATON(%s)', ( user_id, ip, )) conn.commit()
def get_user_roles(user_id): if user_id is None: # NOTE(cfrademan): SHORT-CIRCUIT - google is your friend. return [] domains = get_domains() roles = [] with db() as conn: query = 'SELECT' + \ ' luxon_user_role.id AS assignment_id,' + \ ' luxon_role.name AS role,' + \ ' luxon_user_role.role_id AS role_id,' + \ ' luxon_user_role.domain AS domain,' + \ ' luxon_user_role.tenant_id AS tenant_id,' + \ ' luxon_tenant.name AS tenant FROM' + \ ' luxon_user_role LEFT JOIN luxon_role ON' + \ ' luxon_user_role.role_id = luxon_role.id' + \ ' LEFT JOIN luxon_tenant ON' + \ ' luxon_user_role.tenant_id = luxon_tenant.id' + \ ' WHERE luxon_user_role.user_id = %s' crsr = conn.execute(query, user_id) roles = to_list(crsr.fetchall()) for role in roles: if role['domain'] is None and role['tenant_id'] is None: for domain in domains: role['domain'] = domain['name'] roles.append(role) return roles
def authorize(tag, username=None, password=None, domain=None): with db() as conn: auth = {} crsr = conn.execute( 'SELECT luxon_user.id AS user_id' + ' ,luxon_user.last_login AS last_login' + ' ,luxon_user.username AS username' + ' ,luxon_user.password AS password' + ' ,luxon_domain.name AS domain' + ' FROM luxon_user' + ' LEFT JOIN luxon_domain' + ' ON luxon_user.domain_id = luxon_domain.id' + ' WHERE luxon_user.enabled = 1' + ' AND luxon_user.username = %s' + ' AND luxon_domain.name = %s' + ' AND luxon_user.tag = %s', (username, domain, tag)) result = crsr.fetchone() if result is not None: # Validate Password againts stored HASHED Value. if is_valid_password(password, result['password']): auth = result.copy() return ( True, auth, ) return ( False, auth, )
def get_context_roles(user_id, domain=None, tenant_id=None): roles = [] with db() as conn: values = [] values.append(user_id) query = 'SELECT' + \ ' luxon_user_role.id as assignment_id,' + \ ' luxon_user_role.role_id AS role_id,' + \ ' luxon_user_role.domain as domain,' + \ ' luxon_role.name as role,' + \ ' luxon_user_role.tenant_id as tenant_id FROM' + \ ' luxon_user_role LEFT JOIN luxon_role ON' + \ ' luxon_user_role.role_id = luxon_role.id' + \ ' LEFT JOIN luxon_tenant ON' + \ ' luxon_user_role.tenant_id = luxon_tenant.id' + \ ' OR luxon_user_role.tenant_id is NULL' + \ ' where luxon_user_role.user_id = %s' if domain is not None: query += ' and luxon_user_role.domain = %s' values.append(domain) else: query += ' and luxon_user_role.domain IS NULL' if tenant_id is not None: query += ' and luxon_user_role.tenant_id = %s' values.append(tenant_id) else: query += ' and luxon_user_role.tenant_id IS NULL' crsr = conn.execute(query, values) for role in crsr: roles.append(role['role']) return roles
def localize(tag, username, domain): with db() as conn: values = [ tag, username, ] sql = 'SELECT username FROM luxon_user' sql += ' WHERE' sql += ' tag = %s' sql += ' AND username = %s' if domain is not None: sql += ' AND domain = %s' values.append(domain) else: sql += ' AND domain IS NULL' result = conn.execute(sql, values).fetchall() if len(result) == 0: if domain is not None: conn.execute( 'INSERT INTO luxon_user' + ' (tag, domain, username)' + ' VALUES' + ' (%s, %s, %s)', (tag, domain, username)) else: conn.execute( 'INSERT INTO luxon_user' + ' (tag, username)' + ' VALUES' + ' (%s, %s)', (tag, username)) conn.commit()
def acct(msg): fr = parse_fr(msg.get('fr', ())) status = fr.get('Acct-Status-Type', 'start').lower() dt = utc(parse_datetime(msg.get('datetime', None))) diff = (now() - dt).total_seconds() if diff > 60: log.error('Processing radius accounting message older' + ' than 60 seconds. Age(%s)' % diff) if not require_attributes('accounting', fr, [ 'User-Name', 'NAS-IP-Address', 'Acct-Status-Type', 'Acct-Session-Id', 'Acct-Unique-Session-Id', 'Acct-Input-Octets64', 'Acct-Output-Octets64' ]): return False with db() as conn: with dbw() as connw: user = get_user(conn, fr['NAS-IP-Address'], fr['User-Name']) if not user: log.debug("user '%s' not found" % (fr['User-Name'], )) return False input_octets, output_octets = do_acct(connw, fr, dt, user, status) usage(connw, fr, user, input_octets, output_octets, status) if not user['static_ip4'] and user['pool_id']: update_ip(connw, user, fr) return True
def get_username(user_id): with db() as conn: result = conn.execute( 'SELECT username FROM' + ' calabiyau_subscriber' + ' WHERE id = %s', user_id).fetchone() if result: return result
def main(argv): config = g.config = Config() config.load('/var/www/tachweb/settings.ini') GetLogger().app_configure() msg = sys.stdin.read() msg = email.message_from_string(msg) with db() as conn: cursor = conn.execute('SELECT * FROM newslist') for rcpt in cursor: to = "%s <%s>" % ( rcpt['name'], rcpt['email'], ) new = format_msg(msg, html_template=html_template, text_template=text_template, email_from='*****@*****.**', email_to=to, multipart=True, token=rcpt['token'], name=rcpt['name']) try: send_email('*****@*****.**', to, msg=new) except Exception as e: log.critical('Failed to send to %s (%s)' % ( rcpt, e, ))
def rm_user_role(self, req, resp, id, role, domain=None, tenant_id=None): """ Remove a role associated to a user. Args: id (str): UUID of user. role (str): UUID of role. domain (str): Name of domain (defaults to None). Use the text "none" to indicate global domain when tenant_id is supplied. tenant_id (str): UUID of tenant (defaults to None). Returns: 200 OK with blank body if successful 404 Not Found if now entry was affected """ with db() as conn: where = { 'user_id': id, 'role_id': role, 'tenant_id': tenant_id, 'domain': domain } query, vals = build_where(**where) sql = "DELETE FROM luxon_user_role WHERE " + query cur = conn.execute(sql, vals) conn.commit() if not cur.rowcount: raise HTTPNotFound("No entry for %s" % where)
def post_processing(queue): while True: pkt = queue.get(queue) with MBClient('subscriber') as mb: mb.send( 'radius_accounting', { 'attributes': encode_packet(pkt), 'datetime': str(datetime.utcnow()) }) with db() as dbro: with dbro.cursor() as crsr: client = pkt.get('NAS-IP-Address')[0] user = get_user(crsr, client, pkt.source[0], pkt.get('User-Name')[0]) if user: status = pkt.get('Acct-Status-Type', [''])[0].lower() if not user['static_ip4'] and user['pool_id']: with dbw() as dbwr: update_ip(dbwr, status, user, pkt) duplicate_to = g.app.config.get('radius', 'duplicate', fallback=None) if duplicate_to: with dbro.cursor() as crsr: client = pkt.get('NAS-IP-Address')[0] user = get_user(crsr, client, pkt.source[0], pkt.get('User-Name')[0]) if user: pkt['Class'] = user['package'].encode('utf-8') duplicates = duplicate_to.split(',') for duplicate_to in duplicates: duplicate_to = duplicate_to.strip() duplicate(pkt.raw_packet, duplicate_to, 1813)
def clear(nas_id): with db() as conn: result = conn.execute('SELECT * FROM calabiyau_nas' + ' WHERE id = %s', nas_id).fetchone() if result: server = result['server'] pass
def sql_api(self): with db() as conn: if self.primary_key is None: raise KeyError("Model %s:" % self.model_name + " No primary key") from None view_query, view_values = self._api_sort_range() ctx_query, ctx_values = self._api_context(True) query = "SELECT count(*) as total FROM %s " % self.model_name query += ctx_query crsr = conn.execute(query, ctx_values) result = crsr.fetchone() if result is not None: total_rows = result['total'] query = "SELECT * FROM %s " % self.model_name query += ctx_query query += view_query crsr = conn.execute(query, ctx_values) result = crsr.fetchall() crsr.commit() self._sql_parse(result) g.current_request.response.rows = ( total_rows, len(result), )
def get_nas_secret(nas): with db() as conn: result = conn.execute( 'SELECT secret FROM calabiyau_nas' + ' WHERE server = INET6_ATON(%s)', nas).fetchone() if result: return result['secret'] else: return None
def domain_id(domain): if domain is not None: with db() as conn: crsr = conn.execute('SELECT * FROM luxon_domain WHERE name = %s', domain) result = crsr.fetchone() if result is not None: return result['id'] return None
def clear(nas_id): with db() as conn: result = conn.execute('SELECT * FROM tradius_nas' + ' WHERE id = %s', nas_id).fetchone() if result: server = result['server'] conn.execute( 'UPDATE tradius_accounting' + ' SET acctstoptime = now()' + ' WHERE nasipaddress = %s', server) conn.commit()
def user_roles(req, resp, id): sql = "SELECT luxon_user_role.*,luxon_tenant.name as tenant_name," \ "luxon_role.name as role_name FROM luxon_user_role LEFT JOIN " \ "luxon_tenant ON luxon_user_role.tenant_id=luxon_tenant.id " \ "LEFT JOIN luxon_role ON luxon_user_role.role_id = luxon_role.id " \ "WHERE user_id=?" with db() as conn: cur = conn.execute(sql, id) result = cur.fetchall() return json.dumps(result, indent=4, sort_keys=True, default=str)
def data_usage(self, req, resp, user_id): content = [] user = obj(req, calabiyau_subscriber, sql_id=user_id) if user['volume_used_bytes']: used = user['volume_used_bytes'] / 1024 / 1024 / 1024 else: used = 0 f_user_id = sql.Field('user_id') v_user_id = sql.Value(user['id']) f_volume_gb = sql.Field('sum(volume_gb) as volume_gb') select = sql.Select('calabiyau_topup') select.fields = f_volume_gb select.where = f_user_id == v_user_id with db() as conn: result = conn.execute(select.query, select.values).fetchone() if result: topups = result['volume_gb'] if topups is None: topups = 0 else: topups = 0 if not user['volume_used']: f_pkg_id = sql.Field('id') v_pkg_id = sql.Value(user['package_id']) f_volume_gb = sql.Field('volume_gb') select = sql.Select('calabiyau_package') select.where = f_pkg_id == v_pkg_id result = conn.execute(select.query, select.values).fetchone() if result: pkg_volume = result['volume_gb'] if pkg_volume is None: pkg_volume = 0 pkg_volume = pkg_volume - used if pkg_volume < 0: pkg_volume = 0 else: pkg_volume = 0 else: pkg_volume = 0 topups = float(topups) - float(used) if topups < 0: topups = 0 content.append({'type': 'Topups', 'gb': round(float(topups), 2)}) content.append({'type': 'Used', 'gb': round(float(used), 2)}) content.append({'type': 'Package', 'gb': round(float(pkg_volume), 2)}) return raw_list(req, content)
def prune_thread(): with db() as conn: while True: try: conn.execute('DELETE FROM tradius_accounting' + ' WHERE acctstarttime < NOW() - INTERVAL 7 DAY' + ' AND acctstoptime is NULL') conn.commit() except Exception as e: log.warning(e) time.sleep(60)
def rbac_roles(req, resp): """Supplies a List of available roles. Supplies a List of available roles. Used when assigning a user's role. """ sql = "SELECT id,name FROM luxon_role" roles = {} with db() as conn: cur = conn.execute(sql) for r in cur.fetchall(): roles[r['id']] = r['name'] return json.dumps(roles)
def test_model(): global test1, test2 with db() as conn: try: conn.execute('DROP TABLE %s' % 'Model_Test1') conn.execute('DROP TABLE %s' % 'Model_Test2') except: pass test1 = Model_Test1() test2 = Model_Test2() test2.create_table() test1.create_table() new = {} new['stringcol'] = 'String Col' new['floatcol'] = 123.22 new['doublecol'] = 123.22 new['decimalcol'] = 123.22 new['datetimecol'] = '1983-01-17 00:00:00' new['pyobject'] = {} new['blob'] = b'Binary Data' new['text'] = "string of text" new['enum'] = 'option1' new['boolean'] = True test2.update(new) test2.commit() new = {} new['stringcol'] = 'String Col' new['floatcol'] = 123.22 new['doublecol'] = 123.22 new['decimalcol'] = 123.22 new['datetimecol'] = '1983-01-17 00:00:00' new['pyobject'] = {} new['blob'] = b'Binary Data' new['text'] = "string of text" new['enum'] = 'option1' new['boolean'] = True test1.update(new) test1.commit() assert isinstance(test1['pyobject'], dict) test1 = Model_Test1() test1.sql_query("SELECT * FROM Model_Test1") assert isinstance(test1['id'], int) assert isinstance(test1['floatcol'], float) assert isinstance(test1['doublecol'], float) assert isinstance(test1['decimalcol'], PyDecimal) assert isinstance(test1['blob'], bytes) assert isinstance(test1['text'], str) assert isinstance(test1['enum'], str) assert isinstance(test1['boolean'], bool)
def add_user_role(self, req, resp, id, role, domain=None, tenant_id=None): """ Associate role to a user. Args: id (str): UUID of user. role (str): UUID of role. domain (str): Name of domain (defaults to None). Use the text "none" to indicate global domain when tenant_id is supplied. tenant_id (str): UUID of tenant (defaults to None). Example return data: .. code-block:: json { "id": "e729af96-5672-4669-b4a1-6251493a67fa", "user_id": "e95ec7b1-4f0f-4c70-991f-4bb1bec6a524", "role_id": "08034650-1438-4e56-b5a8-674ede74fe83", "domain": "default", "tenant_id": null } """ if domain is not None and domain.lower() == "none": domain = None with db() as conn: check_context_auth(conn, id, domain, tenant_id) # Even though we have unique constraint, sqlite # does not consider null as unique. ref: # https://goo.gl/JmjT5G # So need to manually check that. check_unique(conn, id, role, domain, tenant_id) sql = "INSERT INTO luxon_user_role " \ "(`id`,`role_id`,`tenant_id`,`user_id`," \ "`domain`,`creation_time`) " \ "VALUES (?,?,?,?,?,?)" user_role_id = str(uuid4()) conn.execute(sql, (user_role_id, role, tenant_id, id, domain, now())) conn.commit() user_role = { "id": user_role_id, "user_id": id, "role_id": role, "domain": domain, "tenant_id": tenant_id } return json.dumps(user_role, indent=4)
def disconnect(virtual_id, username=None): with db() as conn: nas_nodes = conn.execute( 'SELECT * FROM tradius_nas' + ' WHERE virtual_id = %s', virtual_id).fetchall() for nas in nas_nodes: sql = 'SELECT * FROM tradius_accounting WHERE ' where = {'nasipaddress': nas['server']} if username: where = {'username': username} where, values = build_where(**where) acct = conn.execute(sql + where, values).fetchall() for session in acct: pod(session['id'])
def sql_id(self, primary_id): with db() as conn: if self.primary_key is None: raise KeyError("Model %s:" % self.model_name + " No primary key") from None crsr = conn.execute("SELECT * FROM %s" % self.model_name + " WHERE %s" % self.primary_key.name + " = %s", primary_id) result = crsr.fetchone() if result: crsr.commit() self._sql_parse([result]) else: raise ValueError('object not found')
def sql_id(self, primary_id): if isinstance(self._current, dict): with db() as conn: ctx_query, ctx_values = self._api_context(False) if self.primary_key is None: raise KeyError("Model %s:" % name + " No primary key") from None crsr = conn.execute( "SELECT * FROM %s" % self.model_name + " WHERE %s" % self.primary_key.name + " = %s" + " %s" % ctx_query, (primary_id, ) + ctx_values) result = crsr.fetchall() crsr.commit() self._sql_parse(result) else: raise NotImplementedError()
def disconnect(acct_id): with rmq() as mb: with db() as conn: result = conn.execute( 'SELECT * FROM calabiyau_accounting' + ' WHERE id = %s', acct_id).fetchall() for cdr in result: message = { 'type': 'disconnect', 'session': { 'Acct-Session-Id': cdr['acctsessionid'], 'User-Name': cdr['username'], 'NAS-IP-Address': cdr['nasipaddress'] } } mb.distribute('subscriber', **message) conn.commit()