def add_user(username, password): # connection = None user_added = False res={} try: connection = getDBConnection() cursor = connection.cursor() encrypted_pw = pbkdf2_sha256.hash(password) cursor.execute("""INSERT INTO users (username, password) VALUES (%s, %s) RETURNING *;""" , (username, encrypted_pw)) connection.commit() if (cursor.rowcount != 0): user_added = True else: res["error"] = "Invalid username and/or password" except (Exception, psycopg2.Error) as error: print("Error while connecting to PostgreSQL", error) res["error"] = str(error) finally: if (connection): cursor.close() connection.close() print("PostgreSQL connection is closed") res["user_added"] = user_added return res
def fetch_users(): res = {} list_of_users = [] # connection = None try: connection = getDBConnection() cursor = connection.cursor() cursor.execute("SELECT * FROM users;") user_records = cursor.fetchall() for row in user_records: list_of_users.append({'username': row[1]}) except (Exception, psycopg2.Error) as error: print("Error while connecting to PostgreSQL", error) res["error"] = str(error) finally: if (connection): cursor.close() connection.close() print("PostgreSQL connection is closed") if ((len(list_of_users) == 0) & ("error" not in res)): res["error"] = "There are no users in the system" res["users"] = list_of_users return res
def get_audio_key(session_id): connection = None try: connection = getDBConnection() cursor = connection.cursor() get_audio_key_sql = "SELECT audio_key FROM audio_keys " \ "WHERE session_id = %s " cursor.execute(get_audio_key_sql, [session_id]) key = cursor.fetchone() # if (cursor.rowcount != 0): # user_added = True except (Exception, psycopg2.Error) as error: print("Error while connecting to PostgreSQL", error) return False finally: if (connection): cursor.close() connection.close() print("PostgreSQL connection is closed") if key == None: return { "error": "There has been error, your session may have expired, please try again" } res = key[0] return res
def add_user_to_workspace(list_of_ids, workspace_id, is_admin=False): try: connection = getDBConnection() cursor = connection.cursor() insert_user_to_workspace_sql = "insert into workspace_users (user_id, workspace_id, is_admin) " \ "values (%s,%s,%s) returning user_id" count = 0 for user_id in list_of_ids: if (user_id != -1): cursor.execute(insert_user_to_workspace_sql, (user_id, workspace_id, is_admin)) connection.commit() count += cursor.rowcount except (Exception, psycopg2.Error) as error: print('Error while connecting to PostgresQL', error) return 0 finally: if (connection): # close the connection and the cursor cursor.close() connection.close() print("PostgresSQL connection is closed") return 'workspace added' return count
def fetch_user_details(username, password): res = {} password_ok = False # connection = None try: connection = getDBConnection() cursor = connection.cursor() cursor.execute("SELECT password FROM users WHERE username = %s;", (username, )) encrypted_pw = cursor.fetchone()[0] count = cursor.rowcount if (count == 1): password_ok = pbkdf2_sha256.verify(password, encrypted_pw) else: res["error"] = "Invalid username and/or password" except (Exception, psycopg2.Error) as error: print("Error while connecting to PostgreSQL", error) res["error"] = str(error) finally: if (connection): cursor.close() connection.close() print("PostgreSQL connection is closed") res["user_exists"] = password_ok return res
def create_workspace_with_users(data): users = data['users'] admin = data['admin'] workspace = data['name'] res = {} users_added = False try: connection = getDBConnection() cursor = connection.cursor() insert_workspace_sql = "insert into workspaces (name) values (%s) " \ "returning workspace_id" cursor.execute(insert_workspace_sql, (workspace, )) connection.commit() count = cursor.rowcount if (count == 0): res['error'] = 'Could not create the workspace' else: new_workspace_id = cursor.fetchone()[0] loop = asyncio.new_event_loop() admin_id = loop.run_until_complete(get_user_id(admin)) admin_added = add_user_to_workspace([admin_id], new_workspace_id, True) if (admin_added == 0): res['error'] = 'Workspace added but could not set admin to workspace.' else: user_id_list = [] for user in users: loop = asyncio.new_event_loop() single_user_id = loop.run_until_complete( get_user_id(user['username'])) user_id_list.append(single_user_id) users_added = add_user_to_workspace(user_id_list, new_workspace_id) if (users_added != len(users)): res['error'] = 'Some users could not be added to workspace. Try again' else: users_added = True except (Exception, psycopg2.Error) as error: print('Error while conecting to PostgresQL', error) res['error'] = str(error) finally: if (connection): # close the connection and the cursor cursor.close() connection.close() print("PostgresSQL connection is closed") res['users_added'] = users_added return res
def create_workspace_only(data): res = {} workspace_added = False workspace_name = data['name'] s3 = boto3.client('s3') s3.create_bucket(Bucket='%s' % workspace_name) response = s3.list_buckets() buckets = [bucket['Name'] for bucket in response['Buckets']] try: admin = data['admin'] loop = asyncio.new_event_loop() admin_id = loop.run_until_complete(get_user_id(admin)) if (admin_id == -1): res['error'] = 'Could not find user in the system so cannot add workspace for user' else: connection = getDBConnection() insert_workspace_name = "insert into workspaces (name) values (%s) returning workspace_id" cursor = connection.cursor() cursor.execute(insert_workspace_name, (workspace_name, )) connection.commit() count = cursor.rowcount if (count == 0): res['error'] = 'Could not add workspace into the system' else: new_workspace_id = cursor.fetchone()[0] admin_added = add_user_to_workspace([admin_id], new_workspace_id, True) if (admin_added != 0): workspace_added = True else: res['error'] = 'Workspace created but could not set admin. Contact support' except (Exception, psycopg2.Error) as error: print('Error while conecting to PostgresQL', error) res['error'] = str(error) finally: if (connection): # close the connection and the cursor cursor.close() connection.close() print("PostgresSQL connection is closed") res['workspace_added'] = workspace_added return res
def insert_user_invite(invite_json): user_invited = False res = {} # connection = None username = invite_json['username'] workspace = invite_json['workspace'] invited_by = invite_json['invitedBy'] loop = asyncio.new_event_loop() user_id = loop.run_until_complete(get_user_id(username)) loop = asyncio.new_event_loop() invited_by_id = loop.run_until_complete(get_user_id(invited_by)) loop = asyncio.new_event_loop() workspace_id = loop.run_until_complete(get_workspace_id(workspace)) try: # connection = psycopg2.connect( # database="ssc") # cursor = connection.cursor() connection = getDBConnection() cursor = connection.cursor() is_inviter_admin_sql = """select * from workspace_users where user_id=%s and workspace_id=%s and is_admin=True""" cursor.execute(is_inviter_admin_sql, (invited_by_id, workspace_id)) cursor.fetchone() if (cursor.rowcount != 0): insert_to_invites_sql = """insert into invites (user_id, workspace_id, invited_by_id) values(%s, %s, %s)""" cursor.execute(insert_to_invites_sql, (user_id, workspace_id, invited_by_id)) connection.commit() count = cursor.rowcount if (count != 0): user_invited = True else: res['error'] = 'User could not be invited to workspace' else: res['error'] = 'Could not match admin to workspace' except (Exception, psycopg2.Error) as error: print("Error while connecting to PostgreSQL", error) res['error'] = str(error) finally: # closing database connection. if (connection): cursor.close() connection.close() print("PostgreSQL connection is closed") res['user_invited'] = user_invited return res
def encrypt_file(f, bucket_name): print(bucket_name) f.save(secure_filename(f.filename)) s3 = boto3.client('s3') #how to get the bucket_name try: #convert key from 56 into 64 key = 'rfCFW5NYIJq5qWBLW_bXwHeg4z0PwVM9MDssLtQ-T4o=' print(key) connection = getDBConnection() cursor = connection.cursor() actualFile = secure_filename(f.filename) time_stamp = str(time.time()) file_start = str(secure_filename(f.filename))[0:-4] file_end = str(secure_filename(f.filename))[-4:] filename = (file_start + time_stamp + file_end) print(filename) with open(actualFile, 'rb') as f: file = f.read() print(file) fernet = Fernet(key) encrypted = fernet.encrypt(file) print(encrypted) with open('S3/' + filename, 'wb') as f: f.write(encrypted) s3.upload_file(filename, bucket_name, filename) print('Upload complete') except (Exception, psycopg2.Error) as error: print('Error while conecting to PostgresQL', error) finally: if (connection): # close the connection and the cursor cursor.close() connection.close() print("PostgresSQL connection is closed") return 'encrypted'
def delete_workspace(delete_request): # connection = None workspace_deleted = False res = {} deleted_by = delete_request['deleted_by'] workspace = delete_request['workspace'] loop = asyncio.new_event_loop() workspace_id = loop.run_until_complete(get_workspace_id(workspace)) loop = asyncio.new_event_loop() deleted_by_id = loop.run_until_complete(get_user_id(deleted_by)) try: if (workspace_id == -1 | deleted_by_id == -1): res['error'] = 'Could not locate workspace or user deleting the workspace' else: connection = getDBConnection() cursor = connection.cursor() loop = asyncio.new_event_loop() admin_status = loop.run_until_complete(is_user_admin(deleted_by_id, workspace_id)) if (admin_status == 0): res['error'] = 'User is not an admin of the workspace' else: delete_workspace_sql = "delete from workspaces where workspace_id=%s" cursor.execute(delete_workspace_sql, (workspace_id,)) connection.commit() count = cursor.rowcount if (count != 0): workspace_deleted = True except (Exception, psycopg2.Error) as error: print("Error while connecting to PostgreSQL", error) res['error'] = str(error) finally: # closing database connection. if (connection): cursor.close() connection.close() print("PostgreSQL connection is closed") res['workspace_deleted'] = workspace_deleted return res
def decrypt_file(workspace_name, filename, audio_key): s3 = boto3.resource('s3') time_stamp = str(time.time()) split = filename.split('-') altered_filename = split[0] + time_stamp + split[2] altered = str(split[0] + time_stamp + 'decrypted' + split[2]) key_string = bytes(audio_key, 'utf-8') encoded_key = base64.b64encode(key_string) generic_error = None try: s3.Bucket(workspace_name).download_file(filename, altered_filename) connection = getDBConnection() key = encoded_key cursor = connection.cursor() with open(altered_filename, 'rb') as f: file = f.read() fernet = Fernet(key) decrypted = fernet.decrypt(file) with open(altered, 'wb') as f: print(f.write(decrypted)) if os.path.exists(altered_filename): os.remove(altered_filename) except (Exception, psycopg2.Error) as error: print('Error while connecting to PostgresQL', error) generic_error = 1 except: generic_error = 1 finally: if (connection): cursor.close() connection.close() print("PostgresSQL connection is closed") if (generic_error == 1): return False else: to_send = send_file(altered) os.remove(altered) return to_send
def fetch_workspace_users(name): list_of_users = [] res = {} try: connection = getDBConnection() cursor = connection.cursor() if (name == None): res["error"] = "Workspace name is invalid" else: loop = asyncio.new_event_loop() workspace_id = loop.run_until_complete(get_workspace_id(name)) if (workspace_id == -1): res["error"] = "Workspace name is invalid" else: cursor.execute( """SELECT u.username, wu.is_admin FROM workspace_users wu INNER JOIN workspaces w ON w.workspace_id = wu.workspace_id INNER JOIN users u ON wu.user_id=u.user_id WHERE w.workspace_id = %s """, (workspace_id, )) workspace_users = cursor.fetchall() for row in workspace_users: list_of_users.append({ 'username': row[0], 'is_admin': str(row[1]) }) except (Exception, psycopg2.Error) as error: print("Error while connecting to PostgreSQL", error) res["error"] = str(error) finally: # closing database connection. if (connection): cursor.close() connection.close() print("PostgreSQL connection is closed") if ((len(list_of_users) == 0) & ("error" not in res)): res["error"] = "There are no users in this workspace" res["users"] = list_of_users return res
def decrypt_file(data): filename = data['filename'] print(filename) try: key = 'rfCFW5NYIJq5qWBLW_bXwHeg4z0PwVM9MDssLtQ-T4o=' print(key) connection = getDBConnection() cursor = connection.cursor() # filename = secure_filename(f.filename) with open('S3/' + filename, 'rb') as f: file = f.read() print(file) fernet = Fernet(key) decrypted = fernet.decrypt(file) print(decrypted) with open('Third_test_decrypted.txt', 'wb') as f: print(f.write(decrypted)) # with open('new_decrypted_file', 'rb') as f: # decrypted_file = f.read() except (Exception, psycopg2.Error) as error: print('Error while connecting to PostgresQL', error) finally: if (connection): # close the connection and the cursor cursor.close() connection.close() print("PostgresSQL connection is closed") return send_file('Third_test_decrypted.txt')
def fetch_user_invites(username): # connection = None res = {} list_of_invites = [] try: connection = getDBConnection() cursor = connection.cursor() loop = asyncio.new_event_loop() user_id = loop.run_until_complete(get_user_id(username)) if (user_id == -1): res["error"] = "User could not be found" else: get_invites_sql = """select w.name, u.username from invites i inner join workspaces w on i.workspace_id=w.workspace_id inner join users u on i.invited_by_id = u.user_id where i.user_id=%s""" cursor.execute(get_invites_sql, (user_id, )) user_invites = cursor.fetchall() for row in user_invites: list_of_invites.append({ 'workspace': row[0], 'invited_by': row[1] }) except (Exception, psycopg2.Error) as error: print("Error while connecting to PostgreSQL", error) res["error"] = str(error) finally: # closing database connection. if (connection): cursor.close() connection.close() print("PostgreSQL connection is closed") res["invites"] = list_of_invites return res
def fetch_user_workspaces(username): res = {} list_of_user_workspaces = [] # connection = None try: connection = getDBConnection() cursor = connection.cursor() loop = asyncio.new_event_loop() user_id = loop.run_until_complete(get_user_id(username)) if user_id == -1: res["error"] = "User does not exist in the system" else: user_workspaces_sql = "SELECT w.name, wu.is_admin " \ "FROM workspaces w " \ "JOIN workspace_users wu ON wu.workspace_id = w.workspace_id " \ "WHERE wu.user_id =%s " cursor.execute(user_workspaces_sql, (user_id,)) user_workspaces = cursor.fetchall() for row in user_workspaces: list_of_user_workspaces.append({'workspace': row[0], 'is_admin': row[1]}) except (Exception, psycopg2.Error) as error: print("Error while connecting to PostgreSQL", error) res["error"] = str(error) finally: # closing database connection. if (connection): cursor.close() connection.close() print("PostgreSQL connection is closed") res["workspaces"] = list_of_user_workspaces return res
def add_audio_key(audio_key, session_id): try: connection = getDBConnection() cursor = connection.cursor() add_audio_key_sql = "INSERT INTO audio_keys (audio_key, session_id)" \ "VALUES (%s, %s)" cursor.execute(add_audio_key_sql, (audio_key, session_id)) connection.commit() except (Exception, psycopg2.Error) as error: print("Error while connecting to PostgreSQL", error) return False finally: # closing database connection. if (connection): cursor.close() connection.close() print("PostgreSQL connection is closed") return 'Key Added Successfully'
def update_admin(workspace, admin_request): workspace_admin_updated = False res = {} username = admin_request['username'] admin_username = admin_request['admin_username'] make_admin = admin_request['make_admin'] loop = asyncio.new_event_loop() workspace_id = loop.run_until_complete(get_workspace_id(workspace)) loop = asyncio.new_event_loop() user_id = loop.run_until_complete(get_user_id(username)) loop = asyncio.new_event_loop() admin_id = loop.run_until_complete(get_user_id(admin_username)) try: if (workspace_id == -1 | admin_id == -1 | user_id == -1): res['error'] = 'Invalid input. Check username, admin and workspace are correct' else: connection = getDBConnection() cursor = connection.cursor() loop = asyncio.new_event_loop() admin_status = loop.run_until_complete( is_user_admin(admin_id, workspace_id)) if (admin_status == 0): res['error'] = 'Admin is not actual admin of workspace' else: if (make_admin == 'True'): make_admin_bool = True else: make_admin_bool = False update_admin_sql = "update workspace_users set is_admin=%s where workspace_id=%s" \ "and user_id=%s" cursor.execute(update_admin_sql, (make_admin_bool, workspace_id, user_id)) connection.commit() count = cursor.rowcount if (count == 0): res['error'] = 'Could not make user as admin' else: workspace_admin_updated = True except (Exception, psycopg2.Error) as error: print("Error while connecting to PostgreSQL", error) res['error'] = str(error) finally: # closing database connection. if (connection): cursor.close() connection.close() print("PostgreSQL connection is closed") res['workspace_admin_updated'] = workspace_admin_updated return res
def process_invite(username, invite_response): decision = invite_response['accept'] workspace = invite_response['workspace'] # connection = None invite_processed = False res = {} try: # connection = psycopg2.connect( # database="ssc") # cursor = connection.cursor() connection = getDBConnection() cursor = connection.cursor() get_invite_id_sql = """select i.invite_id, i.user_id, i.workspace_id from invites i, users u, workspaces w where u.username=%s and w.name=%s and u.user_id=i.user_id and w.workspace_id=i.workspace_id""" cursor.execute(get_invite_id_sql, (username, workspace)) invites = cursor.fetchone() if (invites is None): res["error"] = "Could not locate the invite" else: invite_id = invites[0] user_id = invites[1] workspace_id = invites[2] if (decision == 'True'): insert_user_to_workspace_sql = """insert into workspace_users (user_id, workspace_id, is_admin) values(%s, %s, false)""" cursor.execute(insert_user_to_workspace_sql, (user_id, workspace_id)) connection.commit() count = cursor.rowcount if (count == 0): res["error"] = "Could not accept the invite" else: delete_invite_sql = "delete from invites where invite_id=%s" cursor.execute(delete_invite_sql, (invite_id, )) connection.commit() count = cursor.rowcount if (count == 0): res["error"] = "Could not accept the invite" else: invite_processed = True else: delete_invite_sql = "delete from invites where invite_id=%s" cursor.execute(delete_invite_sql, (invite_id, )) connection.commit() count = cursor.rowcount if (count == 0): res["error"] = "Could not reject the invite" else: invite_processed = True except (Exception, psycopg2.Error) as error: print("Error while connecting to PostgreSQL", error) res["error"] = str(error) finally: # closing database connection. if (connection): cursor.close() connection.close() print("PostgreSQL connection is closed") res["invite_processed"] = invite_processed return res
def encrypt_file(f, bucket_name, audio_key): f.save(secure_filename(f.filename)) s3 = boto3.client('s3') key_string = bytes(audio_key, 'utf-8') encoded_key = base64.b64encode(key_string) res = {} try: # convert key from 56 into 64 key = encoded_key connection = getDBConnection() cursor = connection.cursor() actualFile = secure_filename(f.filename) time_stamp = str(time.time()) file_start = str(secure_filename(f.filename))[0:-4] file_end = str(secure_filename(f.filename))[-4:] filename = (file_start + '-' + time_stamp + '-' + file_end) with open(actualFile, 'rb') as f: file = f.read() fernet = Fernet(key) encrypted = fernet.encrypt(file) with open(filename, 'wb') as f: f.write(encrypted) s3.upload_file(filename, bucket_name, filename) loop = asyncio.new_event_loop() workspace_id = loop.run_until_complete(get_workspace_id(bucket_name)) if (workspace_id == -1): res["error"] = "Workspace name is invalid" else: add_file_to_workspace = "INSERT INTO workspace_files (workspace_id, file_name) " \ "VALUES (%s, %s) RETURNING *; " cursor.execute(add_file_to_workspace, (workspace_id, filename)) connection.commit() # if cursor.rowcount == 0: # res["error"] = "" except (Exception, psycopg2.Error) as error: print('Error while connecting to PostgresQL', error) finally: os.remove(filename) os.remove(actualFile) if (connection): # close the connection and the cursor cursor.close() connection.close() print("PostgresSQL connection is closed") return 'encrypted'
def delete_user_from_workspace(data): res = {} user_deleted = False try: # check if admin_username is the same as the workspace_admins username = data['username'] admin_username = data['admin_username'] workspace_name = data['workspace_name'] connection = getDBConnection() cursor = connection.cursor() select_user = "******" cursor.execute(select_user, [username]) user_id = cursor.fetchone() count = cursor.rowcount if (count == 0): res["error"] = "User does not exist in the system" else: cursor.execute(select_user, [admin_username]) admin_id = cursor.fetchone() count = cursor.rowcount if (count == 0): res["error"] = "Admin does not exist in the system" else: select_workspace_id = "select workspace_id from workspaces where name = (%s)" cursor.execute(select_workspace_id, [workspace_name]) workspace_id = cursor.fetchone() count = cursor.rowcount if (count == 0): res["error"] = "Workspace does not exist in the system" else: select_admin_boolean = "select is_admin from workspace_users where user_id = (%s) and workspace_id = (%s)" cursor.execute(select_admin_boolean, (admin_id, workspace_id)) admin_boolean = cursor.fetchone() count = cursor.rowcount if (count == 0) | (not admin_boolean): res["error"] = "Given admin is not actual admin of workspace" else: delete_user = "******" cursor.execute(delete_user, (user_id, workspace_id)) connection.commit() count = cursor.rowcount if (count != 0): user_deleted = True else: res["error"] = "Could not remove user from workspace" except (Exception, psycopg2.Error) as error: print('Error while conecting to PostgresQL', error) res['error'] = str(error) finally: if (connection): # close the connection and the cursor cursor.close() connection.close() print("PostgresSQL connection is closed") res["user_deleted_from_workspace"] = user_deleted return res