Как отследить трафик PostgreSQL / forpes.ru

Главная
Как отследить трафик PostgreSQL

Как отследить трафик PostgreSQL +6

10.11.2025 13:06

drema201 0 341 Источник

Это перевод статьи известного специалиста в области 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 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: 530, Ack: 311, Len: 6
PostgreSQL
Type: Ready for query
Length: 5
Status: Idle (73)

Перевод статьи How to sniff PostgreSQL traffic