def sendMail (to,subject,text):
fro = get_config_param('database.ini','Email','domainname')
print "sendMail",fro
server = get_config_param('database.ini','Email','emailserver')
print "sendMail:",server
_sendMail(to, fro, subject, text,server)
def sendMail(to, subject, text):
fro = get_config_param('database.ini', 'Email', 'mailfrom')
if fro == False:
print "mailfrom param is not set"
return False
print "sendMail", fro
server = get_config_param('database.ini', 'Email', 'emailserver')
if server == False:
print "emailserver param is not set"
return False
print "sendMail:", server
return _sendMail(to, fro, subject, text, server)
def db_connect_tool_database():
vnf_params = db_config('database.ini', 'vnf_onboarding')
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**vnf_params)
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
# conn.cursor will return a cursor object, you can use this cursor to perform queries
cursor = conn.cursor()
table_name = get_config_param('database.ini', 'Details', 'table')
table_exist_query = "SELECT EXISTS(SELECT 1 FROM information_schema.tables WHERE table_catalog= %s AND table_schema='public' AND table_name=%s);"
table_exist_data = (vnf_params['dbname'], table_name)
cursor.execute(table_exist_query, table_exist_data)
records = cursor.fetchall()
pprint.pprint((records[0])[0])
isTable = (records[0])[0]
print(isTable)
if len(records) > 0 and isTable == True:
print "Database table {} exists".format(table_name)
else:
print "Database table {} does NOT exist".format(table_name)
try:
create_table = "CREATE TABLE {} (username varchar(18) UNIQUE not null, password varchar(18) not null , emailid varchar(50) UNIQUE not null)".format(
table_name)
#table_data = (AsIs(table_name))
print("Creating Table %s", table_name)
print("Query %s", create_table)
cursor.execute(create_table)
print("Table Created")
except psycopg2.DatabaseError, e:
print 'Error %s' % e
sys.exit(1)
def generate_and_updatepassword(credentials):
update_query = ""
randompassword = password_gen()
hashedpassword = sha256.hash(randompassword)
print "generate_and_updatepassword", randompassword
db_table = get_config_param('database.ini', 'Details', 'table')
conn = db_connect()
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
cursor = conn.cursor()
if 'username' in credentials.keys():
print "generate_and_updatepassword:Forming Query"
update_query = "UPDATE {} SET password = '******' WHERE ({}.username = '******')".format(
db_table, hashedpassword, db_table, credentials['username'])
print update_query
elif 'emailaddress' in credentials.keys():
print "generate_and_updatepassword:Forming Query"
update_query = "UPDATE {} SET password = '******' WHERE ({}.emailid = '{}')".format(
db_table, hashedpassword, db_table, credentials['emailaddress'])
print update_query
try:
cursor.execute(update_query)
except:
print "generate_and_updatepassword: Cannont execute update password query command"
cursor.close()
conn.close()
credentials['password'] = randompassword
return True
def forgetpassword():
print "Received forgetpassword request", request.data
inputs = inputs = request.get_json()
print "Forgetpassword", inputs
status = db_generate_newpassword(inputs, False)
print "status=", status
if status == 1:
print "forgetpassword:user does not exist", status
return jsonify({
"Status":
"Error",
"Message":
"Cannot generate password. User does not exist"
})
elif status == "Connection Failed":
return jsonify({
"Status":
"Error",
"Message":
"Cannot generate passwod.Failure to connect to database"
})
elif status == 0:
if get_config_param('database.ini', 'Email',
'enablesendemail') == False:
print "Email Functionality Disabled"
status = db_generate_newpassword(inputs)
if status == 0:
return jsonify({
"Status":
"Error",
"Message":
"Generated new password.Email Functionality is Disabled.So Password is set to default i.e. password@123"
})
mail_text = ""
if inputs['username']:
print "after updating password", inputs
mail_text = draft_mail_text("Forget Password", inputs['username'],
inputs['password'])
print "forget password:"******"VNF Onboarding New Password",
mail_text) == False:
status = db_generate_newpassword(inputs)
if status == 0:
return jsonify({
"Status":
"Error",
"Message":
"New Password set for user.Email Delivery Failed.So Password is set to default i.e. password@123"
})
return jsonify({
"Status":
"Success",
"Message":
"New Password generated for user. Email containing credentials has been sent to the user"
})
def signup():
pprint.pprint("received signup request")
print("Request Data:%s", request.data)
credentials = json.loads(request.data)
pprint.pprint(credentials)
status = db_user_signup(credentials['username'], credentials['password'],
credentials['emailaddress'])
print(status)
if (status == "True"):
if get_config_param('database.ini', 'Email',
'enablesendemail') == False:
print "Email Functionality Disabled"
return jsonify({
"Status":
"Success",
"Message":
"User Registration Succeeded.Email Functionality is Disabled.User can login with new credentials"
})
mail_text = draft_mail_text("User Registration",
credentials['username'],
credentials['password'])
print "signup:", mail_text
if sendMail([credentials['emailaddress']],
"VNF Onboarding User Registration", mail_text) == False:
return jsonify({
"Status":
"Error",
"Message":
"User Registration Succeeded.Email Delivery Failed.User can login with new credentials"
})
return jsonify({
"Status":
"Success",
"Message":
"User Registration Successful.Email containing credentials has been sent to the user."
})
elif status == "Connection Failed":
return jsonify({
"Status":
"Error",
"Message":
"User Registration Failed.Failure to connect to database"
})
else:
return jsonify({
"Status":
"Error",
"Message":
"User Registration Failed. Username or Email-id already exists"
})
def check_if_userexists(credentials):
username = ""
emailid = ""
userExists = False
db_table = get_config_param('database.ini', 'Details', 'table')
conn = db_connect()
cursor = conn.cursor()
check_user_query = ""
if 'username' in credentials.keys():
username = credentials['username']
check_user_query = "SELECT username,emailid FROM {} WHERE ( {}.username = '******' )".format(
db_table, db_table, username)
elif 'emailaddress' in credentials.keys():
emailid = credentials['emailaddress']
check_user_query = "SELECT username,emailid FROM {} WHERE ( {}.emailid = '{}')".format(
db_table, db_table, emailid)
try:
cursor.execute(check_user_query)
except:
print "check_if_userexists: cannot execute find username or emailid query"
# retrieve the records from the database
records = cursor.fetchall()
print(records)
if len(records) > 0:
for rec in records:
print "check_if_userexists:rec", rec
print "check if user exists: rec[0],rec[1]", rec[0], rec[1]
if username and rec[0] == username:
print "check_if_userexistes:user match found", rec[0]
print "check_if_userexists:mailid of matched user", rec[1]
credentials['emailaddress'] = rec[1]
print "check_if_userexists:user {} exists".format(username)
userExists = True
break
elif emailid and rec[1] == emailid:
print "check_if_userexistes:emailid found", rec[1]
print "check_if_userexists:user with a mailid {} is {}".format(
emailid, rec[0])
credentials['username'] = rec[0]
userExists = True
cursor.close()
conn.close()
return userExists
def insert_data(username, password, emailid):
vnf_params = db_config('database.ini', 'vnf_onboarding')
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**vnf_params)
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
# conn.cursor will return a cursor object, you can use this cursor to perform queries
cursor = conn.cursor()
table_name = get_config_param('database.ini', 'Details', 'table')
try:
#insertop = "INSERT INTO {} (username, password, emailid ) VALUES({},{},{})".format(table_name,username,password,emailid)
insertop = "INSERT INTO %s (username, password, emailid ) VALUES(%s,%s,%s)"
insertData = (AsIs(table_name), username, password, emailid)
print("insert query = %s", insertop)
cursor.execute(insertop, insertData)
except psycopg2.DatabaseError, e:
print 'Error %s' % e
#sys.exit(1)
return "False"
def db_check_credentials(username, password):
""" Connect to the PostgreSQL database server """
conn = None
# read connection parameters
params = db_config('database.ini', 'vnf_onboarding')
db_table = get_config_param('database.ini', 'Details', 'table')
# connect to the PostgreSQL server
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**params)
# conn.cursor will return a cursor object, you can use this cursor to perform queries
cursor = conn.cursor()
# execute our Query
check_user_query = "SELECT username,password FROM {} WHERE ( {}.username = '******' )".format(
db_table, db_table, username)
try:
cursor.execute(check_user_query)
except:
print "db_check_credentials:Cannot execute", check_user_query
# retrieve the records from the database
records = cursor.fetchall()
print(records)
for rec in records:
print rec
db_password = sha256.verify(password, rec[1])
print "Verified password", db_password, rec[1]
if rec[0] == username and db_password == True:
print "db_check_credentials:User {} is Authenticated".format(
username)
return True
print "Did not find user", username
cursor.close()
return False
def _sendMail(to, fro, subject, text, server="localhost"):
assert type(to) == list
msg = MIMEMultipart()
msg['From'] = fro
msg['To'] = COMMASPACE.join(to)
msg['Date'] = formatdate(localtime=True)
msg['Subject'] = subject
msg.attach(MIMEText(text))
print "_sendMail:msg", msg
status = False
username = get_config_param('database.ini', 'Email', 'username')
password = get_config_param('database.ini', 'Email', 'password')
port = int(get_config_param('database.ini', 'Email', 'port'))
if port == False:
print "parameter port is not set"
return False
smtp = None
try:
smtp = smtplib.SMTP(server, port)
status = True
except Exception as e:
print(e)
status = False
finally:
if status == False:
print "error in smtp connection"
return status
try:
#smtp = smtplib.SMTP(server,port)
#if not smtp:
# print "smtp is not defined"
# return
print "set debug level for smtp"
smtp.set_debuglevel(True)
# identify yourself, and get supported features
smtp.ehlo()
# start tls for security
if smtp.has_extn('STARTTLS'):
print "TLS Supported"
#starttls and check if it succeeded
if smtp.starttls()[0] == 220:
print "starttls succeeded"
#return in case starttls is supported and fails
else:
print "starttls failed"
status = False
return
smtp.ehlo() # re-identify ourselves over TLS connection
if username != False and password != False:
smtp.login(username, password)
sendStatus = smtp.sendmail(fro, to, msg.as_string())
print "sendStatus=", sendStatus
if sendStatus:
print "Receipient refused"
status = False
return
print "send email succeeded"
status = True
#except:
except Exception as e:
print(e)
print "failed to send mail"
status = False
return
finally:
if smtp is not None:
smtp.quit()
return status
