Если у вас возникала идея получить доступ к метрикам операционной системы и оборудования компьютера из PostgreSQL, то теперь у вас есть инструмент для этого. Я не претендую на его зрелость и готовность к эксплуатации. Это просто прототип, позволяющий получить результаты запросов из osquery в PostgreSQL в виде табличных данных/JSON. Дальше с которыми можете использовать все привычные средства этой базы данных.
osquery
Кросплатформенный open source проект, который позволяет выполнять запросы к событиям и метрикам операционной системы, как к виртуальным таблицам в SQLite, построен на основе этой embedded базы, с присущими ей ограничениями на типы данных.
osqueryd
демон, по конфигурации собирающий результаты запросов, отправляющий эти данные, а также умеющий подписываться на события операционной системы об изменениях;osqueryi
интерактивная консоль, позволяющая отлаживать запросы, получать результаты их выполнения и не взаимодействующая сosqueryd;
Для получения результатов запросов можно использовать Thrift API, либо запуск osqueryi
в командной строке с текстом запроса и форматом результата json/csv. В свое время я использовал сериализацию Thrift API через транспорт UNIX domain socket что было совсем не просто. В этот раз, решил что запуска процесса и парсинга stdout будет достаточно. Так и появился postgres_osquery. Раньше было еще расширение pgosquery и это не интеграция с osquery, а попытка написать его "с нуля", с предсказуемым результатом - возможности минимальны и проект заброшен.
postgres_osquery
Реализовал интеграциюrun_osquery
как хранимку в PostgreSQL на Python, которая получает на вход строку запроса к osquery, а на выходе выдает JSON, заменяя пустые строки на null.
CREATE OR REPLACE FUNCTION run_osquery(query text) RETURNS json AS $$
def replace_empty_strings_with_null(json_obj):
if isinstance(json_obj, dict):
for key, value in json_obj.items():
if isinstance(value, str) and value == "":
json_obj[key] = None
elif isinstance(value, (dict, list)):
replace_empty_strings_with_null(value)
elif isinstance(json_obj, list):
for i, item in enumerate(json_obj):
if isinstance(item, str) and item == "":
json_obj[i] = None
elif isinstance(item, (dict, list)):
replace_empty_strings_with_null(item)
import subprocess
import plpy
import json
try:
process_output = subprocess.check_output(['osqueryi', '--json', query ]).decode('utf-8')
process_json = json.loads(process_output)
replace_empty_strings_with_null(process_json)
return json.dumps(process_json, indent=4)
except subprocess.CalledProcessError as e:
plpy.error('External process failed: ' + str(e))
except Exception as e:
plpy.error('An error occurred: ' + str(e))
$$ LANGUAGE plpython3u;
И казалось бы - вот оно решение, но чтобы превратить json в табличную форму, приходится вручную определять тип записи, который возвращает PostgreSQL функция json_to_recordset. Это многословно и неудобно, например:
SELECT * from json_to_recordset(run_osquery($$ SELECT * FROM routes $$)) as
(destination text, flags text, gateway text, hopcount text, interface text, metric text, mtu text, netmask text, source text, type text);
Хотелось бы не печатать для типовых виртуальных таблиц osquery портянку полей и их типов. И получилось это с помощью автогенерации типов определяемых пользователем(UDT) и функции json_populate_recordset, которая первым параметром принимает этот тип записи.
Для прошлого примера. Создаем тип в базе PostgreSQL:
CREATE TYPE osquery_routes AS (
"destination" text,
"netmask" integer,
"gateway" text,
"source" text,
"flags" integer,
"interface" text,
"mtu" integer,
"metric" integer,
"type" text,
"hopcount" integer);
Вызываем функцию:
SELECT * FROM json_populate_recordset(
null::osquery_routes,
run_osquery('select * from routes'));
И получаем результат запроса select * from routes
в виде таблицы:
![](https://habrastorage.org/getpro/habr/upload_files/3cf/74d/14e/3cf74d14e4310aa10d34b5e43afc0623.png)
Чтобы не печатать это ручками, примеры запросов для основных таблиц доступны в репозитарии, перед использованием которых надо создать типы для используемых в функциях записей.
И конечно же можете работать просто с JSON результатом:
SELECT run_osquery($$
SELECT p.pid, name, p.path as process_path, pf.path as open_path
FROM osquery_info i
JOIN processes p ON p.pid = i.pid
JOIN process_open_files pf ON pf.pid = p.pid
WHERE pf.path LIKE '/dev/%' $$);
![](https://habrastorage.org/getpro/habr/upload_files/bbe/11a/77b/bbe11a77b539d7a12408074ba179d10f.png)
Можно получить шаблон для типов строки с помощью моей функции get_common_json_record и с помощью этого шаблона преобразовать JSON в табличную запись:
![](https://habrastorage.org/getpro/habr/upload_files/30d/f95/955/30df95955b816c641b44f90b29a53d4f.png)
Полезные ссылки
https://osquery.readthedocs.io/en/stable/introduction/sql/
https://osquery.io/schema/5.9.1/osqueryi
https://fleetdm.com/guides/osquery-evented-tables-overview
https://github.com/jmpsec/osctrl
https://github.com/fleetdm/fleet
https://docs.elastic.co/en/integrations/osquery_manager
https://github.com/citusdata/pg_cron
Итоги
Результаты работы функции run_osquery
можно сохранять в таблицы с помощью pg_cron по расписанию или же запускать на каждом узле кластера CitusDB с помощью run_command_on_all_nodes().
Или построить свое SIEM( Security information and event management) решение для хостов базы данных, используя только PostgreSQL.
Надеюсь, что помог с созданием новой "игрушки" для администраторов PostgreSQL. Ведь когда работаешь с osquery из PostgreSQL, понимаешь сколько еще полезных и забавных расширений может написать сообщество для этой базы данных.