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
