def update_user_usergroup(): current_app.logger.warning('/api/servergroup %s', request.data.decode()) pp.pprint(request.headers) data = json.loads(request.data.decode()) #data = request.get_json() print('------') pp.pprint(data) print('------') name = data['name'] id = data['id'] cur = get_db().cursor(cursor_factory=RealDictCursor) # Get the tree before it's modified. query = "SELECT tree FROM ServerGroup where organization_id=%s AND id=%s" cur.execute(query, [session['ORGANIZATION_ID'], id]) oldparentgroup = cur.fetchone()['tree'] # Modify the PersonGroup entry name + tree query = "UPDATE ServerGroup SET name=%s, tree=subpath(tree, 0, nlevel(tree) -1) || %s WHERE id=%s AND organization_id=%s RETURNING tree" cur.execute(query, [name, name, id, session['ORGANIZATION_ID']]) newparentgroup = cur.fetchone()['tree'] # Update PersonGroup children query = "UPDATE ServerGroup SET tree=%s || subpath(tree, nlevel(%s)) WHERE organization_id=%s AND tree <@ %s AND tree != %s" cur.execute(query, [ newparentgroup, oldparentgroup, session['ORGANIZATION_ID'], oldparentgroup, oldparentgroup ]) get_db().commit() return Response("Done", mimetype='text/plain')
def create_user_usergroup(): current_app.logger.warning('/api/servergroup %s', request.data.decode()) pp.pprint(request.headers) data = request.get_json() print(data) usergroup_id = data['usergroup_id'] login = data['login'] current_app.logger.warning( 'Received request for /api/user_usergroup with usergroup_id: %s login: %s', usergroup_id, login) query = ( "INSERT INTO person_persongroup(person_id, persongroup_id) " "VALUES((SELECT id FROM Person JOIN organization_user ON Person.id = organization_user.person_id WHERE Person.login=%s AND organization_user.organization_id=%s), " "(SELECT id FROM persongroup where organization_id=%s AND id=%s)) RETURNING id" ) current_app.logger.debug(query) cur = get_db().cursor(cursor_factory=RealDictCursor) cur.execute(query, [ login, session['ORGANIZATION_ID'], session['ORGANIZATION_ID'], usergroup_id ]) get_db().commit() resulting_id = cur.fetchone()['id'] return Response(json.dumps(resulting_id, indent=2), mimetype='application/json')
def register_server(): data = json.loads(request.data.decode()) cur = get_db().cursor() QUERY_UPSERT_SERVER = "INSERT INTO server(uuid, hostname) VALUES(%s, %s) ON CONFLICT(uuid) DO UPDATE SET modified=now() RETURNING id" cur.execute(QUERY_UPSERT_SERVER, [data['uuid'], data['hostname']]) server_id = cur.fetchone()[0] # TODO: fix to use ON CONFLICT on both organization_id and server_id cur.execute( "SELECT COUNT(*) FROM organization_server WHERE organization_id=(SELECT id from organization where uuid=%s) AND server_id=%s", [data['organization_uuid'], server_id]) count = cur.fetchone()[0] if count == 0: print("Inserting relationship.") QUERY_INSERT_SERVER = "INSERT INTO organization_server(organization_id, server_id, server_active) VALUES((SELECT id from organization where uuid=%s), %s, %s)" try: cur.execute(QUERY_INSERT_SERVER, [data['organization_uuid'], server_id, True]) except IntegrityError: return Response( "An error has occured. Please check your organization UUID is correct or contact centralized." ) get_db().commit() return Response("{} {} {}".format(data['organization_uuid'], server_id, count), mimetype='text/plain')
def delete_ugsg(): data = json.loads(request.data.decode()) cur = get_db().cursor(cursor_factory=RealDictCursor) roleid = data['id'] QUERY_DELETE = "DELETE FROM roles WHERE id = %s AND organization_id = %s" cur.execute(QUERY_DELETE, [roleid, session['ORGANIZATION_ID']]) get_db().commit() return Response("Done.", mimetype='text/plain')
def disable_user(): current_app.logger.warning('/api/user %s', request.data.decode()) data = json.loads(request.data.decode()) user_id = data['user_id'] current_app.logger.warning('Received request for /api/user with user_id: %s', user_id) query = "update organization_user set person_active='f' where organization_id=%s AND person_id=%s" current_app.logger.debug(query) cur = get_db().cursor(cursor_factory=RealDictCursor) cur.execute(query, [session['ORGANIZATION_ID'], user_id]) get_db().commit() return Response("Done.", mimetype='text/plain')
def delete_user_usergroup(): current_app.logger.warning('/api/user_usergroup %s', request.data.decode()) pp.pprint(request.headers) data = json.loads(request.data.decode()) uug_id = data['uug_id'] cur = get_db().cursor(cursor_factory=RealDictCursor) # Get the tree before it's modified. query = "DELETE FROM person_persongroup AS ppg USING persongroup AS pg WHERE pg.id = ppg.persongroup_id AND pg.organization_id=%s AND ppg.id = %s" cur.execute(query, [session['ORGANIZATION_ID'], uug_id]) get_db().commit() return Response("Done", mimetype='text/plain')
def getcreds(): uuid = request.args.get('uuid') query = ( "select p.login, p.id, p.first_name, p.last_name, p.contact_email, r.ssh, r.sudoline, r.id AS roleid, pubkey.pubkey, pubkey.pubkey_md5hash, ou.person_active, pg1.name AS groupname, unmanaged_groups " "from persongroup AS pg1 " "INNER JOIN person_persongroup AS ppg ON ppg.persongroup_id = pg1.id " "INNER JOIN person AS p ON p.id = ppg.person_id " "INNER JOIN pubkey ON p.id = pubkey.person_id " "INNER JOIN organization_user AS ou ON ou.person_id = p.id, " "roles AS r " "INNER JOIN persongroup AS pg2 ON pg2.id = r.persongroup_id " "WHERE pg1.tree <@ pg2.tree " "AND ou.person_active = 't' " "AND servergroup_id IN " "(select id from servergroup " "WHERE tree @> " "(SELECT array_agg(sg.tree) " "FROM server " "INNER JOIN server_servergroup AS ssg ON server.id = ssg.server_id " "INNER JOIN servergroup AS sg ON sg.id = ssg.servergroup_id " "WHERE uuid=%s))") cur = get_db().cursor(cursor_factory=RealDictCursor) current_app.logger.warning(cur.mogrify(query, [uuid])) cur.execute(query, [uuid]) return Response(json.dumps(cur.fetchall(), indent=2), mimetype='application/json')
def servergroup(): servergroup_id = request.args.get('id') query = "SELECT id, name, tree FROM persongroup WHERE id = %s AND organization_id = %s" cur = get_db().cursor(cursor_factory=RealDictCursor) cur.execute(query, [servergroup_id, session['ORGANIZATION_ID']]) return Response(json.dumps(cur.fetchall(), indent=2), mimetype='application/json')
def servers_list(): cur = get_db().cursor(cursor_factory=RealDictCursor) query = ( "SELECT uuid, hostname FROM server JOIN organization_server AS os ON os.server_id = server.id WHERE os.organization_id = %s" ) cur.execute(query, [session['ORGANIZATION_ID']]) return Response(json.dumps(cur.fetchall(), indent=2), mimetype='application/json')
def create_ugsg(): data = json.loads(request.data.decode()) ug_id = data['ug_id'] sg_id = data['sg_id'] sudoline = data['sudoline'] ugroups = data['ugroups'] cur = get_db().cursor(cursor_factory=RealDictCursor) QUERY_UPSERT_SERVER = "INSERT INTO roles(organization_id, persongroup_id, servergroup_id, sudoline, unmanaged_groups) VALUES(%s, %s, %s, %s, %s) RETURNING id" cur.execute(QUERY_UPSERT_SERVER, [session['ORGANIZATION_ID'], ug_id, sg_id, sudoline, ugroups]) get_db().commit() resulting_id = cur.fetchone()['id'] return Response(json.dumps(resulting_id, indent=2), mimetype='application/json')
def user_usergroup(): user_usergroup_id = request.args.get('id') query = " SELECT * FROM person_persongroup AS ppg JOIN organization_user AS ou ON ppg.person_id = ou.person_id WHERE ou.organization_id=1 AND ppg.id=1;" query = "SELECT id, name, tree FROM persongroup WHERE id = %s AND organization_id = %s" cur = get_db().cursor(cursor_factory=RealDictCursor) cur.execute(query, [user_usergroup_id, session['ORGANIZATION_ID']]) return Response(json.dumps(cur.fetchall(), indent=2), mimetype='application/json')
def create_servergroup_server(): current_app.logger.warning('/api/servergroup %s', request.data.decode()) pp.pprint(request.headers) data = request.get_json() print(data) servergroup_id = data['servergroup_id'] uuid = data['uuid'] current_app.logger.warning('Received request for /api/server_servergroup with servergroup_id: %s uuid: %s', servergroup_id, uuid) query = ( "INSERT INTO server_servergroup(server_id, servergroup_id) " "VALUES((SELECT id FROM Server JOIN organization_server ON Server.id = organization_server.server_id WHERE Server.uuid=%s AND organization_server.organization_id=%s), " "(SELECT id FROM servergroup where organization_id=%s AND id=%s)) RETURNING id" ) print(query) current_app.logger.debug(query) cur = get_db().cursor(cursor_factory=RealDictCursor) cur.execute(query, [uuid, session['ORGANIZATION_ID'], session['ORGANIZATION_ID'], servergroup_id]) get_db().commit() resulting_id = cur.fetchone()['id'] return Response(json.dumps(resulting_id, indent=2), mimetype='application/json')
def create_servergroup(): current_app.logger.warning('/api/servergroup %s', request.data.decode()) pp.pprint(request.headers) data = request.get_json() print(data) parentgroup = data['parentgroup'] name = data['name'] if not checkgroupname(name): return Response( "Invalid group name: Must match '^[A-Za-z0-9_]+$' with a limit of 256 characters", status=400, mimetype='text/plain') current_app.logger.warning( 'Received request for /api/servergroup with parentgroup: %s name: %s', parentgroup, name) query = "INSERT INTO ServerGroup(organization_id, name, tree) VALUES(%s, %s, text2ltree(concat_ws('.', (SELECT tree FROM ServerGroup WHERE organization_id = %s AND id = %s), %s))) RETURNING id" current_app.logger.debug(query) cur = get_db().cursor(cursor_factory=RealDictCursor) current_app.logger.warning( cur.mogrify(query, [ session['ORGANIZATION_ID'], name, session['ORGANIZATION_ID'], parentgroup, name ])) cur.execute(query, [ session['ORGANIZATION_ID'], name, session['ORGANIZATION_ID'], parentgroup, name ]) get_db().commit() resulting_id = cur.fetchone()['id'] return Response(json.dumps(resulting_id, indent=2), mimetype='application/json')
def delete_usergroup(): # Prohibits deletion of "all". # Prohibits deletion of group having subgroups. # Prohibits deletion of group part of a role. pp.pprint(request.data.decode()) data = json.loads(request.data.decode()) sg_id = data['sg_id'] cur = get_db().cursor(cursor_factory=RealDictCursor) # Has subgroups? query = " SELECT COUNT(*) AS in_tree, (SELECT COUNT(*) AS attached_roles FROM roles WHERE servergroup_id=%s) FROM servergroup WHERE organization_id = %s AND tree <@ (select tree FROM servergroup WHERE organization_id = %s AND id = %s);" cur.execute( query, [sg_id, session['ORGANIZATION_ID'], session['ORGANIZATION_ID'], sg_id]) res = cur.fetchone() if res['in_tree'] > 1: return Response("Error, this group has subgroups", status=400, mimetype='text/plain') if res['attached_roles'] > 0: return Response( "Error, this group is attached to a role, cannot delete it, please detach it first.", status=400, mimetype='text/plain') # Delete if not "all". query = "DELETE FROM servergroup WHERE organization_id = %s AND tree != 'all' AND id=%s" cur.execute(query, [session['ORGANIZATION_ID'], sg_id]) get_db().commit() return Response("Done", mimetype='text/plain')
def check_auth(username, password): """This function is called to check if a username / password combination is valid. """ query = "select organization_id, admin_passwd_hash = crypt(%s, admin_passwd_hash) AS password_ok, login, is_company_admin, CAST((CASE WHEN admin_passwd_hash IS NULL THEN 'f' ELSE 't' END) AS BOOLEAN) AS is_admin FROM person JOIN organization_user AS ou ON person.id = ou.person_id where ou.person_active='t' AND login=%s" print("Query: {} {} {}".format(query, password, username)) cur = get_db().cursor(cursor_factory=RealDictCursor) cur.execute(query, [password, username]) result = cur.fetchone() pp.pprint(result) if result['password_ok']: session['ORGANIZATION_ID'] = result['organization_id'] session['login'] = result['login'] session['is_admin'] = result['is_admin'] session['is_company_admin'] = result['is_company_admin'] return True return False
def ugsg_list(): query = "SELECT roles.id, pg.name AS usergroup, pg.tree AS usergroup_tree, sg.name AS servergroup, sg.tree AS servergroup_tree, sudoline, ssh, unmanaged_groups FROM roles JOIN persongroup AS pg ON roles.persongroup_id = pg.id JOIN servergroup AS sg ON roles.servergroup_id = sg.id WHERE roles.organization_id=%s" cur = get_db().cursor(cursor_factory=RealDictCursor) cur.execute(query, [session['ORGANIZATION_ID']]) return Response(json.dumps(cur.fetchall(), indent=2), mimetype='application/json')
def user_usergroups_list(): query = "SELECT ppg.id AS id, p.id AS user_id, p.login, pg.name AS groupname, pg.tree AS grouptree FROM person_persongroup AS ppg JOIN person AS p ON ppg.person_id = p.id JOIN persongroup AS pg ON pg.id = ppg.persongroup_id WHERE pg.organization_id = %s" cur = get_db().cursor(cursor_factory=RealDictCursor) cur.execute(query, [session['ORGANIZATION_ID']]) return Response(json.dumps(cur.fetchall(), indent=2), mimetype='application/json')
def servergroups_list(): query = "SELECT id, name, tree FROM servergroup WHERE organization_id = %s" cur = get_db().cursor(cursor_factory=RealDictCursor) cur.execute(query, [session['ORGANIZATION_ID']]) return Response(json.dumps(cur.fetchall(), indent=2), mimetype='application/json')
def servergroup_server(): servergroup_id = request.args.get('id') query = "select ssg.id AS ssg_id, s.uuid AS s_uuid, s.hostname AS s_hostname, sg.name AS SG_name, sg.tree AS sg_tree FROM server_servergroup AS ssg JOIN server AS s ON s.id = ssg.id JOIN servergroup AS sg ON sg.id = ssg.servergroup_id WHERE ssg.id = %s AND sg.organization_id = %s" cur = get_db().cursor(cursor_factory=RealDictCursor) cur.execute(query, [servergroup_id, session['ORGANIZATION_ID']]) return Response(json.dumps(cur.fetchall(), indent=2), mimetype='application/json')
def users_list(): cur = get_db().cursor(cursor_factory=RealDictCursor) query = "SELECT id, login, first_name, contact_email, ou.person_active AS active, CAST((CASE WHEN admin_passwd_hash IS NULL THEN 'f' ELSE 't' END) AS BOOLEAN) AS is_admin, ou.is_company_admin FROM Person JOIN organization_user AS ou ON ou.person_id = Person.id WHERE ou.organization_id=%s ORDER BY login;" cur.execute(query, [session['ORGANIZATION_ID']]) current_app.logger.info("Organization id by session {}".format(session['ORGANIZATION_ID'])) return Response(json.dumps(cur.fetchall(), indent=2), mimetype='application/json')