def handle_put(cls, **kwargs):
"""Refresh cached counts for given inventory ID."""
inventory_id = kwargs.get("inventory_id", None)
cls._system_exists(inventory_id)
DB.execute_sql("SELECT refresh_system_cached_counts(%s)",
(inventory_id, ))
return ""
def handle_patch(cls, **kwargs):
"""Update the 'status' field for a system/cve combination"""
data = kwargs['data']
in_inventory_id = data['inventory_id']
in_cve = data['cve']
in_status_id = data['status_id']
LOGGER.debug('SYSID [%s] CVE [%s] STATUS-ID [%s] ACCT [%s]',
in_inventory_id, in_cve, in_status_id, connexion.context['user'])
try:
plat = (SystemPlatform.select(SystemPlatform.inventory_id)
.where((SystemPlatform.rh_account == connexion.context['user']) &
(SystemPlatform.inventory_id == in_inventory_id) &
(SystemPlatform.opt_out == False))) # pylint: disable=singleton-comparison
if cls.hide_satellite_managed():
plat = plat.where(SystemPlatform.satellite_managed == False) # pylint: disable=singleton-comparison
vuln = (SystemVulnerabilities.update(status_id=in_status_id)
.from_(plat)
.where((SystemVulnerabilities.inventory_id == in_inventory_id) &
(SystemVulnerabilities.cve == in_cve)))
rows_modified = vuln.execute()
if rows_modified == 0:
# sysid/cve/acct combination does not exist
return cls.format_exception('inventory_id/cve must exist and inventory_id must be visible to user', 404)
except (IntegrityError, ValueError, psycopg2IntegrityError) as integ_error:
# usually means bad-status-id
DB.rollback()
return cls.format_exception(str(integ_error), 500)
return ''
def handle_patch(cls, **kwargs):
"""Set status for a CVE"""
data = kwargs['data']
cve_list = data['cve']
if isinstance(cve_list, str):
cve_list = [cve_list]
values = {}
updated = []
if 'status_id' in data:
values['status_id'] = data['status_id']
if 'status_text' in data:
try:
values['status_text'] = data['status_text'].strip() \
if data['status_text'].strip() else None
except AttributeError:
values['status_text'] = None
if not values:
return cls.format_exception(
'At least one of the "status_id" or "status_text" parameters is required.', 400)
try:
to_insert = []
cves = CveMetadata.select(CveMetadata.id, CveMetadata.cve).where(CveMetadata.cve << cve_list)
rh_account = get_or_create_account()
for cve in cves:
updated.append(cve.cve)
if 'status_id' in data:
# refresh number of divergent statuses in CVE-system pairs
# pylint: disable=singleton-comparison
query = (SystemVulnerabilities.select(fn.Count(SystemVulnerabilities.id).alias('systems'))
.join(SystemPlatform, on=(SystemVulnerabilities.system_id == SystemPlatform.id))
.join(InsightsRule, JOIN.LEFT_OUTER, on=(InsightsRule.id == SystemVulnerabilities.rule_id))
.where(SystemPlatform.rh_account_id == rh_account[0].id)
.where((SystemPlatform.opt_out == False) # noqa: E712
& (SystemPlatform.stale == False) # noqa: E712
& (SystemPlatform.when_deleted.is_null(True)))
.where(SystemVulnerabilities.cve_id == cve.id)
.where((SystemVulnerabilities.mitigation_reason.is_null(True)) | (InsightsRule.active == False))
.where((SystemVulnerabilities.when_mitigated.is_null(True)) | (InsightsRule.active == True))
.where(SystemVulnerabilities.status_id != values.get('status_id', 0))
.dicts())
values['systems_status_divergent'] = query[0]['systems']
to_insert.append((cve.id, rh_account[0].id, values.get('status_id', 0),
values.get('status_text', None), values.get('systems_status_divergent', 0)))
if not to_insert:
return cls.format_exception('At least one given CVE must exist', 404)
(CveAccountData.insert_many(to_insert, fields=cls._fields)
.on_conflict(conflict_target=cls._conflict_target, preserve=[], update=values).execute())
except (IntegrityError, psycopg2IntegrityError, DataError):
# usually means bad status
LOGGER.exception('Error during setting status (IntegrityError):')
DB.rollback()
return cls.format_exception(f"status_id={data['status_id']} is invalid", 400)
except ValueError as value_error:
LOGGER.exception('Error during setting status (ValueError):')
DB.rollback()
return cls.format_exception(f'status_text or other key value is invalid ({value_error})', 400)
return {'updated': updated}
def handle_delete(cls, **kwargs):
pid = kwargs["pid"]
try:
LOGGER.info("Killing query with PID %s", pid)
DB.execute_sql("SELECT pg_terminate_backend(%s)", (pid,))
except DatabaseError as err:
LOGGER.exception("Internal server error: %s", err)
return "Error"
return "Ok"
def handle_put(cls, **kwargs):
"""Refresh cached counts for given account ID and CVE."""
account = kwargs.get("account_id", None)
cve = kwargs.get("cve_id", None)
cls._account_exists(account)
cls._cve_exists(cve)
DB.execute_sql("SELECT refresh_cve_account_cached_counts(%s, %s)",
(cve, account))
return ""
def handle_patch(cls, **kwargs):
"""Update the 'status' field for a system/cve combination"""
# pylint: disable=singleton-comparison
data = kwargs['data']
try:
in_inventory_id_list, in_cve_list, status_to_cves_map, status_text_to_cves_map = cls._prepare_data(data)
rh_account = get_or_create_account()
systems = (SystemPlatform.select(SystemPlatform.id)
.where((SystemPlatform.rh_account_id == rh_account[0].id) &
(SystemPlatform.opt_out == False))) # noqa: E712
if in_inventory_id_list is not None:
systems = systems.where(SystemPlatform.inventory_id << in_inventory_id_list)
rows_modified = set()
# set statuses and their CVE lists
for status_id, status_cve_list in status_to_cves_map.items():
status_id_update = (SystemVulnerabilities.update(status_id=status_id)
.where((SystemVulnerabilities.system_id << systems) &
(SystemVulnerabilities.cve_id <<
(CveMetadata.select(CveMetadata.id).where(
CveMetadata.cve << status_cve_list))))
.returning(SystemVulnerabilities.id))
rows_modified.update([row.id for row in status_id_update])
cls._update_divergent_status_count(in_cve_list, rh_account[0].id)
for status_text, status_cve_list in status_text_to_cves_map.items():
status_text_update = (SystemVulnerabilities.update(status_text=status_text)
.where((SystemVulnerabilities.system_id << systems) &
(SystemVulnerabilities.cve_id <<
(CveMetadata.select(CveMetadata.id).where(
CveMetadata.cve << status_cve_list))))
.returning(SystemVulnerabilities.id))
rows_modified.update([row.id for row in status_text_update])
updated_details = (SystemVulnerabilities.select(SystemPlatform.inventory_id, CveMetadata.cve)
.join(CveMetadata, on=(SystemVulnerabilities.cve_id == CveMetadata.id))
.join(SystemPlatform, on=(SystemVulnerabilities.system_id == SystemPlatform.id))
.where(SystemVulnerabilities.id << list(rows_modified))
.dicts())
updated = []
for updated_row in updated_details:
updated.append({"inventory_id": updated_row["inventory_id"], "cve": updated_row["cve"]})
if not updated:
# sysid/cve/acct combination does not exist
return cls.format_exception('inventory_id/cve must exist and inventory_id must be visible to user', 404)
except (IntegrityError, psycopg2IntegrityError, DataError) as value_error:
# usually means bad-status-id
LOGGER.error(str(value_error))
DB.rollback()
return cls.format_exception(f'status_id={list(status_to_cves_map.keys())} is invalid', 400)
except ValueError as value_error:
LOGGER.exception('Error during setting status (ValueError):')
DB.rollback()
return cls.format_exception(f'status_text or other key value is invalid ({value_error})', 400)
return {"updated": updated}
def handle_patch(cls, **kwargs):
"""Set status for a CVE"""
data = kwargs['data']
cve_list = data['cve']
if isinstance(cve_list, str):
cve_list = [cve_list]
values = {}
updated = []
if 'status_id' in data:
values['status_id'] = data['status_id']
if 'status_text' in data:
try:
values['status_text'] = data['status_text'].strip() \
if data['status_text'].strip() else None
except AttributeError:
values['status_text'] = None
if not values:
return cls.format_exception(
'At least one of the "status_id" or "status_text" parameters is required.',
400)
try:
to_insert = []
cves = CveMetadata.select(
CveMetadata.id,
CveMetadata.cve).where(CveMetadata.cve << cve_list)
rh_account = get_or_create_account()
for cve in cves: # pylint: disable=not-an-iterable
updated.append(cve.cve)
to_insert.append(
(cve.id, rh_account[0].id, values.get('status_id', 0),
values.get('status_text', None)))
if not to_insert:
return cls.format_exception(
'At least one given CVE must exist', 404)
(CveAccountData.insert_many(
to_insert, fields=cls._fields).on_conflict(
conflict_target=cls._conflict_target,
preserve=[],
update=values).execute())
RHAccount.update(
last_status_change=datetime.now(timezone.utc)).where(
RHAccount.id == rh_account[0].id).execute()
except (IntegrityError, psycopg2IntegrityError, DataError):
# usually means bad status
LOGGER.exception('Error during setting status (IntegrityError):')
DB.rollback()
return cls.format_exception(
f"status_id={data['status_id']} is invalid", 400)
except ValueError as value_error:
LOGGER.exception('Error during setting status (ValueError):')
DB.rollback()
return cls.format_exception(
f'status_text or other key value is invalid ({value_error})',
400)
return {'updated': updated}
def handle_patch(cls, **kwargs):
"""Set business risk for a CVE"""
data = kwargs['data']
cve_list = data['cve']
if isinstance(cve_list, str):
cve_list = [cve_list]
values = {}
updated = []
if 'business_risk_id' in data:
values['business_risk_id'] = data['business_risk_id']
if 'business_risk_text' in data:
try:
values['business_risk_text'] = data['business_risk_text'].strip() \
if data['business_risk_text'].strip() else None
except AttributeError:
values['business_risk_text'] = None
if not values:
return cls.format_exception(
'At least one of the "business_risk_id" or "business_risk_text" parameters is required.',
400)
try:
to_insert = []
cves = CveMetadata.select(
CveMetadata.id,
CveMetadata.cve).where(CveMetadata.cve << cve_list)
rh_account = get_or_create_account()
for cve in cves:
updated.append(cve.cve)
to_insert.append((cve.id, rh_account[0].id,
values.get('business_risk_id', 0),
values.get('business_risk_text', None)))
if not to_insert:
return cls.format_exception(
'At least one given CVE must exist', 404)
(CveAccountData.insert_many(
to_insert, fields=cls._fields).on_conflict(
conflict_target=cls._conflict_target,
preserve=[],
update=values).execute())
except (IntegrityError, psycopg2IntegrityError, DataError):
# usually means bad business_risk_id
LOGGER.exception(
'Error during setting business risk (IntegrityError):')
DB.rollback()
return cls.format_exception(
f'business_risk_id=%s is invalid' % data['business_risk_id'],
400)
except ValueError as value_error:
LOGGER.exception(
'Error during setting business risk (ValueError):')
DB.rollback()
return cls.format_exception(
f'business_risk_text or other key value is invalid ({value_error})',
400)
return {'updated': updated}
def wait_on_cyndi():
"""Waits until inventory.hosts schema is initialized"""
LOGGER.info("Waiting for cyndi...")
while True:
try:
InventoryHosts.select(InventoryHosts.id).limit(1).exists()
LOGGER.info("Cyndi schema exists, OK")
DB.close()
break
except DatabaseError:
DB.rollback()
LOGGER.info("Cyndi not initialized, waiting")
sleep(2)
def handle_patch(cls, **kwargs):
"""Set status for a CVE"""
data = kwargs['data']
cve_list = data['cve']
if isinstance(cve_list, str):
cve_list = [cve_list]
values = {}
updated = []
if 'status_id' in data:
values['status_id'] = data['status_id']
if 'status_text' in data:
values['status_text'] = data['status_text'].strip(
) if data['status_text'].strip() else None
if not values:
return cls.format_exception(
'At least one of the "status_id" or "status_text" parameters is required.',
400)
try:
to_insert = []
cves = CveMetadata.select(
CveMetadata.id,
CveMetadata.cve).where(CveMetadata.cve << cve_list)
rh_account = RHAccount.select(RHAccount.id).where(
RHAccount.name == connexion.context['user'])
for cve in cves:
updated.append(cve.cve)
to_insert.append(
(cve.id, rh_account[0].id, values.get('status_id', 0),
values.get('status_text', None)))
if not to_insert:
return cls.format_exception(
'At least one given CVE must exist', 404)
(CveAccountData.insert_many(
to_insert, fields=cls._fields).on_conflict(
conflict_target=cls._conflict_target,
preserve=[],
update=values).execute())
except (IntegrityError, psycopg2IntegrityError):
# usually means bad status
LOGGER.exception('Error during setting status (IntegrityError):')
DB.rollback()
return cls.format_exception(
f'status_id=%s is invalid' % data['status_id'], 400)
except ValueError as value_error:
LOGGER.exception('Error during setting status (ValueError):')
DB.rollback()
return cls.format_exception(str(value_error), 500)
return {'updated': updated}
def handle_get(cls, **kwargs):
rh_account_id = kwargs["rh_account_id"]
try:
tables = DB.execute_sql("""
SELECT tableoid::pg_catalog.regclass
FROM system_vulnerabilities
WHERE rh_account_id = %s""", (rh_account_id,))
if tables.rowcount < 1:
return "No tables to vacuum"
command = ["pg_repack",
"-p", str(CFG.db_port), "-d", CFG.db_name,
"-h", CFG.db_host, "-U", CFG.db_user, "-k"]
for table in tables:
command.append("-t")
command.append(table[0])
LOGGER.info("Repacking tables %s", tables)
subprocess.Popen(command, env={"PGPASSWORD": CFG.db_pass})
except DatabaseError as err:
LOGGER.exception("Internal server error: %s", err)
return "Error"
return "Ok"
def handle_delete(cls, **kwargs): # pylint: disable=unused-argument
"""Delete systems from system_platform table missing in inventory."""
DB.execute_sql("""
UPDATE system_platform sp
SET opt_out = true, stale = true, when_deleted = now()
FROM (
SELECT sp2.inventory_id
FROM system_platform sp2 LEFT JOIN
inventory.hosts ih ON sp2.inventory_id = ih.id
WHERE ih.id IS NULL
AND sp2.when_deleted IS NULL
ORDER BY sp2.inventory_id
FOR UPDATE OF sp2
) up
WHERE sp.inventory_id = up.inventory_id
""")
return ""
def handle_get(cls, **kwargs):
rh_account_id = kwargs["rh_account_id"]
result = []
try:
tables = DB.execute_sql("""
SELECT DISTINCT(tableoid::pg_catalog.regclass)
FROM system_vulnerabilities
WHERE rh_account_id = %s""", (rh_account_id,))
for table in tables:
res = DB.execute_sql("""SELECT * FROM pgstattuple(%s)""", (table[0],)).fetchone()
res = cls.format_bloat_result(res)
res["table_name"] = table[0]
result.append(res)
except DatabaseError as err:
LOGGER.exception("Internal server error: %s", err)
return {"tables": result}
def handle_get(cls, **kwargs):
table = kwargs["table_name"]
try:
res = DB.execute_sql("""SELECT * FROM pgstattuple(%s)""", (table,)).fetchone()
except DatabaseError as err:
LOGGER.exception("Internal server error: %s", err)
return {}
return cls.format_bloat_result(res)
def handle_get(cls, **kwargs): # pylint: disable=unused-argument
"""Get count of systems in system_platform table but missing in inventory."""
cursor = DB.execute_sql("""
SELECT COUNT(sp.inventory_id)
FROM system_platform sp LEFT JOIN
inventory.hosts ih ON sp.inventory_id = ih.id
WHERE ih.id IS NULL
AND sp.when_deleted IS NULL
""")
return cursor.fetchone()[0]
def handle_get(cls, **kwargs):
threshold = kwargs.get("ms_threshold", 0)
username = kwargs.get("username", None)
wait_event_type = kwargs.get("wait_event_type", None)
result = []
try:
cursor = DB.execute_sql(
"""
SELECT pid, datname, usename, application_name,
query_start, now() - query_start AS running_time,
wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE EXTRACT(MILLISECONDS FROM now() - query_start) >= %s AND
(usename = %s OR %s IS NULL) AND
(wait_event_type = %s OR %s IS NULL) AND
state = 'active'
""", (threshold, username, username, wait_event_type,
wait_event_type))
except DatabaseError as err:
LOGGER.exception("Internal server error: %s", err)
return {"queries": result}
for query in cursor:
result.append({
"pid": query[0],
"db_name": query[1],
"username": query[2],
"app_name": query[3],
"query_start": str(query[4]),
"running_time": str(query[5]),
"wait_event_type": query[6],
"wait_event": query[7],
"query": query[8]
})
return {"queries": result}
def handle_delete(cls, **kwargs):
"""Delete system"""
inventory_id = kwargs['inventory_id']
cls._system_exists_and_visible(inventory_id)
DB.execute_sql("SELECT delete_system(%s)", (inventory_id, ))
return ""
def handle_put(cls, **kwargs):
"""Refresh cached counts for given account ID."""
account = kwargs.get("account_id", None)
cls._account_exists(account)
DB.execute_sql("SELECT refresh_account_cached_counts(%s)", (account, ))
return ""
def handle_put(cls, **kwargs):
"""Refresh cached counts for given CVE."""
cve = kwargs.get("cve_id", None)
cls._cve_exists(cve)
DB.execute_sql("SELECT refresh_cve_cached_counts(%s)", (cve, ))
return ""