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')