Это перевод статьи известного специалиста в области Oracle и с недавнего времени в области Postgres - Frits Hoogland'a. Я проверил и немного переделал примеры, использовал PostgresPro вместо yugabyte и, соответственно, внёс несколько других правок.
Недавно я изучал детали реализации взаимодействия клиента PostgreSQL с базой данных и был удивлён тем, насколько легко просматривать трафик PostgreSQL с помощью wireshark. Когда я поделился своим удивлением в соцсети, меня попросили привести пример, как это сделать.
Как установить wireshark на RHEL/Centos/Alma/etc.
Установка wireshark выполняется следующим образом: sudo yum install wireshark-cli
При этом устанавливается CLI-версия wireshark, для которой исполняемый файл называется tshark (а не wireshark-cli).
NB. В Altlinux это сделано логичнее:sudo apt-get install tshark
Как использовать wireshark для отслеживания трафика PostgreSQL
Следующий очевидный вопрос: хорошо, но как мне это использовать?
Это тоже, на самом деле, очень просто.
Если вы хотите просмотреть весь сетевой трафик и полное описание данных, связанных с PostgreSQL, используйте:
sudo tshark -i any -f 'tcp port 5432' -d tcp.port==5432,pgsql -O pgsql
Следует заметить: исполняемый файл wireshark должен выполняться либо на клиентском компьютере, либо на сервере. В противном случае он не сможет перехватывать сетевой трафик.
Как это выглядит?
Вот как это выглядит на моем сервере, когда я запускаю wireshark:
[root@somebox~] #tshark -i any -f 'tcp port 5432' -d tcp.port==5432,pgsql -O pgsql Running as user "root" and group "root". This could be dangerous. Capturing on 'any' ** (tshark:1700951) 17:04:52.835653 [Main MESSAGE] -- Capture started. ** (tshark:1700951) 17:04:52.835801 [Main MESSAGE] -- File: "/tmp/.private/root/wireshark_anyZJKFF3.pcapng"
Если я подключаюсь к базе данных с помощью psql,
psql -h localhost
то можно увидеть следующее:
Frame 1: 68 bytes on wire (544 bits), 68 bytes captured (544 bits) on interface any, id 0 Linux cooked capture v1 Internet Protocol Version 4, Src: 127.0.0.1, Dst: 127.0.0.1 Transmission Control Protocol, Src Port: 41362, Dst Port: 5432, Seq: 0, Len: 0 Frame 2: 68 bytes on wire (544 bits), 68 bytes captured (544 bits) on interface any, id 0 Linux cooked capture v1 Internet Protocol Version 4, Src: 127.0.0.1, Dst: 127.0.0.1 Transmission Control Protocol, Src Port: 5432, Dst Port: 41362, Seq: 0, Ack: 1, Len: 0 Frame 3: 56 bytes on wire (448 bits), 56 bytes captured (448 bits) on interface any, id 0 Linux cooked capture v1 Internet Protocol Version 4, Src: 127.0.0.1, Dst: 127.0.0.1 Transmission Control Protocol, Src Port: 41362, Dst Port: 5432, Seq: 1, Ack: 1, Len: 0 Frame 4: 64 bytes on wire (512 bits), 64 bytes captured (512 bits) on interface any, id 0 Linux cooked capture v1 Internet Protocol Version 4, Src: 127.0.0.1, Dst: 127.0.0.1 Transmission Control Protocol, Src Port: 41362, Dst Port: 5432, Seq: 1, Ack: 1, Len: 8 PostgreSQL Type: SSL request Length: 8 Frame 5: 56 bytes on wire (448 bits), 56 bytes captured (448 bits) on interface any, id 0 Linux cooked capture v1 Internet Protocol Version 4, Src: 127.0.0.1, Dst: 127.0.0.1 Transmission Control Protocol, Src Port: 5432, Dst Port: 41362, Seq: 1, Ack: 9, Len: 0 Frame 6: 57 bytes on wire (456 bits), 57 bytes captured (456 bits) on interface any, id 0 Linux cooked capture v1 Internet Protocol Version 4, Src: 127.0.0.1, Dst: 127.0.0.1 Transmission Control Protocol, Src Port: 5432, Dst Port: 41362, Seq: 1, Ack: 9, Len: 1 Frame 7: 56 bytes on wire (448 bits), 56 bytes captured (448 bits) on interface any, id 0 Linux cooked capture v1 Internet Protocol Version 4, Src: 127.0.0.1, Dst: 127.0.0.1 Transmission Control Protocol, Src Port: 41362, Dst Port: 5432, Seq: 9, Ack: 2, Len: 0 Frame 8: 140 bytes on wire (1120 bits), 140 bytes captured (1120 bits) on interface any, id 0 Linux cooked capture v1 Internet Protocol Version 4, Src: 127.0.0.1, Dst: 127.0.0.1 Transmission Control Protocol, Src Port: 41362, Dst Port: 5432, Seq: 9, Ack: 2, Len: 84 PostgreSQL Type: Startup message Length: 84 Protocol major version: 3 Protocol minor version: 0 Parameter name: user Parameter value: postgres Parameter name: database Parameter value: postgres Parameter name: application_name Parameter value: psql Parameter name: client_encoding Parameter value: UTF8 Frame 9: 440 bytes on wire (3520 bits), 440 bytes captured (3520 bits) on interface any, id 0 Linux cooked capture v1 Internet Protocol Version 4, Src: 127.0.0.1, Dst: 127.0.0.1 Transmission Control Protocol, Src Port: 5432, Dst Port: 41362, Seq: 2, Ack: 93, Len: 384 PostgreSQL Type: Authentication request Length: 8 Authentication type: Success (0) PostgreSQL Type: Parameter status Length: 26 Parameter name: application_name Parameter value: psql PostgreSQL Type: Parameter status Length: 25 Parameter name: client_encoding Parameter value: UTF8 PostgreSQL Type: Parameter status Length: 23 Parameter name: DateStyle Parameter value: ISO, MDY PostgreSQL Type: Parameter status Length: 38 Parameter name: default_transaction_read_only Parameter value: off PostgreSQL Type: Parameter status Length: 23 Parameter name: in_hot_standby Parameter value: off PostgreSQL Type: Parameter status Length: 25 Parameter name: integer_datetimes Parameter value: on PostgreSQL Type: Parameter status Length: 27 Parameter name: IntervalStyle Parameter value: postgres PostgreSQL Type: Parameter status Length: 20 Parameter name: is_superuser Parameter value: on PostgreSQL Type: Parameter status Length: 25 Parameter name: server_encoding Parameter value: UTF8 PostgreSQL Type: Parameter status Length: 24 Parameter name: server_version Parameter value: 15.3 PostgreSQL Type: Parameter status Length: 35 Parameter name: session_authorization Parameter value: postgres PostgreSQL Type: Parameter status Length: 35 Parameter name: standard_conforming_strings Parameter value: on PostgreSQL Type: Parameter status Length: 17 Parameter name: TimeZone Parameter value: GMT PostgreSQL Type: Backend key data Length: 12 PID: 1700987 Key: 2817345176 PostgreSQL Type: Ready for query Length: 5 Status: Idle (73) Frame 10: 56 bytes on wire (448 bits), 56 bytes captured (448 bits) on interface any, id 0 Linux cooked capture v1 Internet Protocol Version 4, Src: 127.0.0.1, Dst: 127.0.0.1 Transmission Control Protocol, Src Port: 41362, Dst Port: 5432, Seq: 93, Ack: 386, Len: 0 Transmission Control Protocol, Src Port: 52441, Dst Port: 5433, Seq: 93, Ack: 338, Len: 0
Это большой объем информации и все эти сетевые пакеты передаются как часть настройки подключения к базе данных.
Сюда входят первые 3 пакета, выполняющие классический трехсторонний "хендшейкинг" связи по протоколу TCP (фреймы 1-3).
Фреймы 4-7 — это согласование для SSL. Кадр 4 показывает, что клиент запрашивает: является ли трафик PostgreSQL SSL или нет.
Кадры 8-10 обрабатывают сообщение о запуске из базы данных, в котором задаются параметры и значения.
Кадр 9 отправляет множество отдельных сообщений в одном кадре в ответ на сообщение о запуске клиента, например:
ответ на запрос аутентификации;
различные сообщения о статусе параметров;
сообщение о готовности к запросу, указывающее, что сервер может получить другой запрос.
Причина, по которой большинство людей будет использовать данную информацию — это посмотреть внутренние детали запросов в базе данных. Далее рассмотрим 2 общих диалога.
Simple query protocol
Frame 12: 87 bytes on wire (696 bits), 87 bytes captured (696 bits) on interface 0
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.66.1, Dst: 192.168.66.80
Transmission Control Protocol, Src Port: 53026, Dst Port: 5433, Seq: 93, Ack: 338, Len: 19
PostgreSQL
Type: Simple query
Length: 18
Query: select now();
Frame 13: 68 bytes on wire (544 bits), 68 bytes captured (544 bits) on interface 0
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.66.80, Dst: 192.168.66.1
Transmission Control Protocol, Src Port: 5433, Dst Port: 53026, Seq: 338, Ack: 112, Len: 0
Frame 14: 157 bytes on wire (1256 bits), 157 bytes captured (1256 bits) on interface 0
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.66.80, Dst: 192.168.66.1
Transmission Control Protocol, Src Port: 5433, Dst Port: 53026, Seq: 338, Ack: 112, Len: 89
PostgreSQL
Type: Row description
Length: 28
Field count: 1
Column name: now
Table OID: 0
Column index: 0
Type OID: 1184
Column length: 8
Type modifier: -1
Format: Text (0)
PostgreSQL
Type: Data row
Length: 39
Field count: 1
Column length: 29
Data: 323032332d30342d31312030393a33333a30392e36363032...
PostgreSQL
Type: Command completion
Length: 13
Tag: SELECT 1
PostgreSQL
Type: Ready for query
Length: 5
Status: Idle (73)
Frame 15: 68 bytes on wire (544 bits), 68 bytes captured (544 bits) on interface 0
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.66.1, Dst: 192.168.66.80
Transmission Control Protocol, Src Port: 53026, Dst Port: 5433, Seq: 112, Ack: 427, Len: 0
Это диалог по протоколу Simple Query. Вопреки распространенному мнению, протокол Simple Query не является сетевым протоколом. Протокол Simple Query — это тип запроса для базы данных PostgreSQL.
На фрейме 12 показан запрос, отправляемый серверной частью базы данных. Это один запрос, который позволяет серверной части базы данных PostgreSQL выполнить 4 шага выполнения PostgreSQL.
Фрейм 14 — это ответ базы данных. Ответ базы данных состоит из нескольких сообщений внутри фрейма:
Описание строки, которую Postgres собирается отправить.
Данные строки.
Индикатор, что запрос был выполнен.
Индикатор, что backend готов принимать другие запросы.
Extended query
Frame 15: 124 bytes on wire (992 bits), 124 bytes captured (992 bits) on interface 0 Linux cooked capture Internet Protocol Version 4, Src: 192.168.66.80, Dst: 192.168.66.80 Transmission Control Protocol, Src Port: 50034, Dst Port: 5433, Seq: 258, Ack: 428, Len: 56 PostgreSQL Type: Parse Length: 20 Statement: Query: SELECT now() Parameters: 0 PostgreSQL Type: Bind Length: 12 Portal: Statement: Parameter formats: 0 Parameter values: 0 Result formats: 0 PostgreSQL Type: Describe Length: 6 Portal: PostgreSQL Type: Execute Length: 9 Portal: Returns: all rows PostgreSQL Type: Sync Length: 4 Frame 16: 68 bytes on wire (544 bits), 68 bytes captured (544 bits) on interface 0 Linux cooked capture Internet Protocol Version 4, Src: 192.168.66.80, Dst: 192.168.66.80 Transmission Control Protocol, Src Port: 5433, Dst Port: 50034, Seq: 428, Ack: 314, Len: 0 Frame 17: 167 bytes on wire (1336 bits), 167 bytes captured (1336 bits) on interface 0 Linux cooked capture Internet Protocol Version 4, Src: 192.168.66.80, Dst: 192.168.66.80 Transmission Control Protocol, Src Port: 5433, Dst Port: 50034, Seq: 428, Ack: 314, Len: 99 PostgreSQL Type: Parse completion Length: 4 PostgreSQL Type: Bind completion Length: 4 PostgreSQL Type: Row description Length: 28 Field count: 1 Column name: now Table OID: 0 Column index: 0 Type OID: 1184 Column length: 8 Type modifier: -1 Format: Text (0) PostgreSQL Type: Data row Length: 39 Field count: 1 Column length: 29 Data: 323032332d30342d31312030393a34323a35372e37303331... PostgreSQL Type: Command completion Length: 13 Tag: SELECT 1 PostgreSQL Type: Ready for query Length: 5 Status: Idle (73) Frame 18: 68 bytes on wire (544 bits), 68 bytes captured (544 bits) on interface 0 Linux cooked capture Internet Protocol Version 4, Src: 192.168.66.80, Dst: 192.168.66.80 Transmission Control Protocol, Src Port: 50034, Dst Port: 5433, Seq: 314, Ack: 527, Len: 0
Здесь мы видим, что выполняется тот же запрос (select now()), но теперь с использованием протокола расширенных запросов.
Фрейм 15 — это запрос клиента. Поскольку это отправка с использованием протокола расширенных запросов, он отправляет не один, а несколько запросов на различные шаги, которые необходимо выполнить при реализации:
Parse. Шаг клиентского парсинга вызывает 2 шага на стороне сервера, собственно, парсинг (синтаксический и семантический разбор) и шаг rewrite (перезаписи), также SQL-выражение запоминается в сессии.
Bind. Клиентский шаг "байндинга" (привязки) на стороне сервера выполняет шаг планирования запроса. Если существуют переменные привязки, то сначала они привязываются перед этапом планирования, чтобы позволить планировщику использовать эти переменные.
Execute. Выполнение на клиенте и сервере — это одно и то же. Здесь важно отметить, что функции Parse, Bind и Execute находятся в одном и том же фрейме.
Фрейм 17 содержит ответ серверной части. Как вы можете видеть, он ответил на каждый запрос: принял запрос на "парсинг" и завершил его, затем принял запрос на привязку(binding) и завершил его, а затем ответил выводом выполнения, который идентичен ответу протокола Simple Query: описание строки, строка данных, завершение команды и готовность к запросу.
Если вам интересно, что произойдет, когда будет отправлено неверное сообщение: сервер просто выдаст сообщение об ошибке:
Frame 15: 170 bytes on wire (1360 bits), 170 bytes captured (1360 bits) on interface 0 Linux cooked capture Internet Protocol Version 4, Src: 192.168.66.80, Dst: 192.168.66.80 Transmission Control Protocol, Src Port: 5433, Dst Port: 48402, Seq: 428, Ack: 311, Len: 102 PostgreSQL Type: Error Length: 101 Severity: ERROR Text: ERROR Code: 42703 Message: column "__" does not exist Position: 8 File: parse_relation.c Line: 3301 Routine: errorMissingColumn
(Я поменял запрос с select now() на select __)
И после ошибки сервер ответит, что снова готов:
Frame 17: 74 bytes on wire (592 bits), 74 bytes captured (592 bits) on interface 0Linux cooked captureInternet Protocol Version 4, Src: 192.168.66.80, Dst: 192.168.66.80Transmission Control Protocol, Src Port: 5433, Dst Port: 48402, Seq: 530, Ack: 311, Len: 6PostgreSQL Type: Ready for query Length: 5 Status: Idle (73)
Перевод статьи How to sniff PostgreSQL traffic
