Categories: PostgreSQL

postgresql-protocol/

Сетевой протокол PostgreSQL можно считать стандартом. Многие новые СУБД реализуют именно его. Что не удивительно, ведь протокол открыт, имеет готовые клиенты для всех популярных языков программирования (см заметки о psycopg2 , pgx , JDBC , …), а также всякие полезные штуки вроде PgBouncer . Давайте же разберемся, как этот протокол устроен.

Подготовка окружения

Нам понадобится установленный и настроенный PostgreSQL. Процесс установки и настройки ранее был описан в посте Начало работы с PostgreSQL .

Для изучения сетевого трафика было решено воспользоваться самописным сниффером на базе libpcap . Просто он безо всяких аргументов выводит только интересную мне лично информацию наиболее удобным мне лично образом.

Если не предпринять дополнительных шагов, то общение между сервером PostgreSQL и клиентом будет осуществляться по UNIX-сокету. Поскольку они не поддерживаются libpcap, правим postgresql.conf таким образом:

# замените на IP вашей машины
listen_addresses = ‘192.168.88.36’

Также в pg_hba.conf добавляем:

# замените на вашу маску подсети
host all all 192.168.88.0/24 password

И наконец, установим пароль:

— замените на имя вашего пользователя
ALTER USER eax PASSWORD ‘$ecr3t’ ;

Важно! Аутентификация при помощи простого пароля используется в этом посте исключительно для демонстрационных целей. На проде вам стоит использовать более безопасный способ в лице scram-sha-256 .

Перезапускаем сервер. Затем стартуем сниффер:

sudo . / eaxsniff wlan1 ‘tcp src or dst port 5432’

Для работы с сервером на первых порах воспользуемся psql:

psql -h 192.168.88.36 -p 5432 -U eax —no-psqlrc

Если все было сделано правильно, то eaxsniff начнет показывать таинственные буквы и цифры. Попробуем понять, что они значат.

Пример аутентификации

Первым мы видим сообщение, посланное клиентом на сервер:

192.168.88.15:51394 -> 192.168.88.36:5432, 8 (0x8) bytes

00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F

0000 00 00 00 08 04 D2 16 2F ……./

Здесь 00 00 00 08 — это общая длина сообщения. Идущие следом байты — это «магическая» версия протокола, при помощи которой клиент пытается узнать, поддерживает ли сервер SSL. (В проекте PostgreSQL принято использовать именно аббревиатуру «SSL», хотя весь остальной мир говорит «TLS» .)

Так эта магическая версия объявлена в pqcomm.h :

#define PG_PROTOCOL(m,n) (((m) << 16) | (n))
/* … */
#define NEGOTIATE_SSL_CODE PG_PROTOCOL(1234,5679)

Проверим в интерпретаторе Python :

>>> «{:08X}».format( (1234 << 16) | 5679)
’04D2162F’

Все сходится.

Есть еще две магической версии протокола:

#define CANCEL_REQUEST_CODE PG_PROTOCOL(1234,5678)
#define NEGOTIATE_GSS_CODE PG_PROTOCOL(1234,5680)

Первая используется при отмене долго выполняющихся запросов, например, когда вы нажимаете Ctr+C в psql. Делается это через отдельное TCP-соединение. Вторая используется аналогично случаю с SSL, но чтобы узнать, поддерживает ли сервер GSSAPI .

В ответ на посланное сообщение сервер говорит:

192.168.88.36:5432 -> 192.168.88.15:51394, 1 (0x1) bytes

00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F

0000 4E N

То есть, SSL в данном случае не поддерживается. Кое-какие детали реализации можно найти в fe-connect.c .

Следующее сообщение от клиента:

192.168.88.15:51394 -> 192.168.88.36:5432, 74 (0x4a) bytes

00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F

0000 00 00 00 4A 00 03 00 00 75 73 65 72 00 65 61 78 …J….user.eax
0010 00 64 61 74 61 62 61 73 65 00 65 61 78 00 61 70 .database.eax.ap
0020 70 6C 69 63 61 74 69 6F 6E 5F 6E 61 6D 65 00 70 plication_name.p
0030 73 71 6C 00 63 6C 69 65 6E 74 5F 65 6E 63 6F 64 sql.client_encod
0040 69 6E 67 00 55 54 46 38 00 00 ing.UTF8..

Первые четыре байта — это длина сообщения. Идущие следом 00 03 00 00 — это нормальная (не магическая) версия протокола, 3.0. Далее идут пары ключей и значений, разделенные 0 x 00. Через них клиент сообщает имя пользователя, базы данных и кое-какую другую информацию. Список завершается пустым ключом.

Ответ сервера:

192.168.88.36:5432 -> 192.168.88.15:51394, 9 (0x9) bytes

00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F

0000 52 00 00 00 08 00 00 00 03 R……..

После того, как клиент с сервером договорились о версии протокола, сообщения становятся однотипными, в соответствии с выбранной версией. На сегодняшний день версия протокола 3.0 является единственной.

Первый байт — это тип сообщения. В данном случае:

#define PqMsg_AuthenticationRequest ‘R’

… запрос аутентификации.

Следующие четыре байта — это длина сообщения, включая поле длины, но исключая байт с типом сообщения. Далее я не буду снова упоминать это поле, так как оно есть всегда, и его смысл не меняется. Но вы про него не забывайте! Иначе запутаетесь, когда будете смотреть на вывод сниффера.

Наконец, следом идет полезная нагрузка, зависящая от сообщения. Здесь сервер просит пользователя пройти аутентификацию при помощи пароля:

#define AUTH_REQ_PASSWORD 3

Коды всех типов сообщений и всех способов аутентификации есть в protocol.h .

Ответ клиента:

192.168.88.15:51933 -> 192.168.88.36:5432, 12 (0xc) bytes

00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F

0000 70 00 00 00 0B 24 65 63 72 33 74 00 p….$ecr3t.

Тип сообщения:

#define PqMsg_PasswordMessage ‘p’

Полезная нагрузка — пароль открытым текстом с байтом 0 x 00 на конце.

Если пароль верный, сервер ответит:

192.168.88.36:5432 -> 192.168.88.15:51933, 419 (0x1a3) bytes

00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F

0000 52 00 00 00 08 00 00 00 00 53 00 00 00 17 69 6E R……..S….in
0010 5F 68 6F 74 5F 73 74 61 6E 64 62 79 00 6F 66 66 _hot_standby.off
0020 00 53 00 00 00 19 69 6E 74 65 67 65 72 5F 64 61 .S….integer_da
0030 74 65 74 69 6D 65 73 00 6F 6E 00 53 00 00 00 1B tetimes.on.S….
0040 54 69 6D 65 5A 6F 6E 65 00 45 75 72 6F 70 65 2F TimeZone.Europe/
0050 4D 6F 73 63 6F 77 00 53 00 00 00 1B 49 6E 74 65 Moscow.S….Inte
…. (пропущено)
0170 64 65 76 65 6C 00 53 00 00 00 19 73 65 72 76 65 devel.S….serve
0180 72 5F 65 6E 63 6F 64 69 6E 67 00 55 54 46 38 00 r_encoding.UTF8.
0190 4B 00 00 00 0C 00 02 10 15 F0 48 48 2D 5A 00 00 K………HH-Z..
01A0 00 05 49 ..I

Домашнее задание: Разберите ответ сервера в случае, если пароль неверный.

Здесь в одном TCP-пакете содержится сразу несколько сообщений.

В первом сервер говорит, что аутентификация пройдена:

#define PqMsg_AuthenticationRequest ‘R’
#define AUTH_REQ_OK 0

Далее идет множество сообщений с типом:

#define PqMsg_ParameterStatus ‘S’

В них сервер сообщает используемую кодировку, часовой пояс , и так далее.

Следом за ними идет сообщение:

#define PqMsg_BackendKeyData ‘K’

С полезной нагрузкой:

00 02 10 15 F0 48 48 2D

Первые четыре байта — это id процесса, обслуживающего пользователя:

>>> 0x00021015
135189

Следующие четыре байта представляют собой cancel key. Он используется, когда вы хотите прервать выполнение запроса, например, нажатием Ctr+C в psql.

И последнее сообщение:

#define PqMsg_ReadyForQuery ‘Z’

… с полезной нагрузкой из одного байта I . Возможных значений три: I = idle, T = in transaction, E = in failed transaction. Это char, возвращаемый функцией TransactionBlockStatusCode() .

Наконец, если сейчас ввести в psql exit , будет послано сообщение:

192.168.88.15:51933 -> 192.168.88.36:5432, 5 (0x5) bytes

00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F

0000 58 00 00 00 04 X….

… с типом:

#define PqMsg_Terminate ‘X’

В отчет на него сервер просто закрывает соединение.

Простой протокол

В терминологии PostgreSQL существует простой протокол (Simple Query Protocol) и расширенный (Extended Query Protocol). Может создаться впечатление, будто речь идет о независимых сетевых протоколах. На самом деле, это отличающиеся сообщения в рамках одного протокола. Для начала рассмотрим, как выглядит простой протокол.

Попробуем выполнить следующий запрос:

CREATE TABLE IF NOT EXISTS phonebook (
id SERIAL PRIMARY KEY ,
name TEXT ,
phone BIGINT ) ;

В сниффере видим:

192.168.88.15:53373 -> 192.168.88.36:5432, 98 (0x62) bytes

00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F

0000 51 00 00 00 61 43 52 45 41 54 45 20 54 41 42 4C Q…aCREATE TABL
0010 45 20 49 46 20 4E 4F 54 20 45 58 49 53 54 53 20 E IF NOT EXISTS
0020 70 68 6F 6E 65 62 6F 6F 6B 28 0A 20 20 69 64 20 phonebook(. id
0030 53 45 52 49 41 4C 20 50 52 49 4D 41 52 59 20 4B SERIAL PRIMARY K
0040 45 59 2C 0A 20 20 6E 61 6D 65 20 54 45 58 54 2C EY,. name TEXT,
0050 0A 20 20 70 68 6F 6E 65 20 42 49 47 49 4E 54 29 . phone BIGINT)
0060 3B 00 ;.

Тип сообщения:

#define PqMsg_Query ‘Q’

… а его полезная нагрузка — текст запроса с 0 x 00 на конце.

Ответ сервера:

192.168.88.36:5432 -> 192.168.88.15:53373, 24 (0x18) bytes

00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F

0000 43 00 00 00 11 43 52 45 41 54 45 20 54 41 42 4C C….CREATE TABL
0010 45 00 5A 00 00 00 05 49 E.Z….I

Тип сообщения:

#define PqMsg_CommandComplete ‘C’

… а его полезная нагрузка — тоже строка.

Следом за этим сообщением идет уже знакомое нам сообщение ReadyForQuery, кодируемое буквой Z.

Попытаемся повторить предыдущий запрос. В ответ получим:

192.168.88.36:5432 -> 192.168.88.15:53373, 119 (0x77) bytes

00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F

0000 4E 00 00 00 76 53 4E 4F 54 49 43 45 00 56 4E 4F N…vSNOTICE.VNO
0010 54 49 43 45 00 43 34 32 50 30 37 00 4D 72 65 6C TICE.C42P07.Mrel
0020 61 74 69 6F 6E 20 22 70 68 6F 6E 65 62 6F 6F 6B ation «phonebook
0030 22 20 61 6C 72 65 61 64 79 20 65 78 69 73 74 73 » already exists
0040 2C 20 73 6B 69 70 70 69 6E 67 00 46 70 61 72 73 , skipping.Fpars
0050 65 5F 75 74 69 6C 63 6D 64 2E 63 00 4C 32 31 30 e_utilcmd.c.L210
0060 00 52 74 72 61 6E 73 66 6F 72 6D 43 72 65 61 74 .RtransformCreat
0070 65 53 74 6D 74 00 00 eStmt..

192.168.88.36:5432 -> 192.168.88.15:53373, 24 (0x18) bytes

00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F

0000 43 00 00 00 11 43 52 45 41 54 45 20 54 41 42 4C C….CREATE TABL
0010 45 00 5A 00 00 00 05 49 E.Z….I

В первом TCP-пакете видим нотификацию:

#define PqMsg_NoticeResponse ‘N’

Полезная нагрузка представляет собой список строк, разделенных 0 x 00. Токен S означает , что далее идет строка, токен V — серьезность ошибки, C — код ошибки, M — сообщение, F — имя файла, L — номер строки, R — имя процедуры.

Во втором TCP-пакете видим те же сообщения, что были получены при первой попытке создать таблицу.

Запишем в таблицу каких-нибудь данных. Затем попробуем прочитать их:

192.168.88.15:53373 -> 192.168.88.36:5432, 30 (0x1e) bytes

00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F

0000 51 00 00 00 1D 53 45 4C 45 43 54 20 2A 20 46 52 Q….SELECT * FR
0010 4F 4D 20 70 68 6F 6E 65 62 6F 6F 6B 3B 00 OM phonebook;.

192.168.88.36:5432 -> 192.168.88.15:53373, 148 (0x94) bytes

00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F

0000 54 00 00 00 4A 00 03 69 64 00 00 00 40 29 00 01 T…J..id…@)..
0010 00 00 00 17 00 04 FF FF FF FF 00 00 6E 61 6D 65 …………name
0020 00 00 00 40 29 00 02 00 00 00 19 FF FF FF FF FF …@)………..
0030 FF 00 00 70 68 6F 6E 65 00 00 00 40 29 00 03 00 …phone…@)…
0040 00 00 14 00 08 FF FF FF FF 00 00 44 00 00 00 1A ………..D….
0050 00 03 00 00 00 01 31 00 00 00 04 41 6C 65 78 00 ……1….Alex.
0060 00 00 03 31 32 33 44 00 00 00 19 00 03 00 00 00 …123D………
0070 01 32 00 00 00 03 42 6F 62 00 00 00 03 34 35 36 .2….Bob….456
0080 43 00 00 00 0D 53 45 4C 45 43 54 20 32 00 5A 00 C….SELECT 2.Z.
0090 00 00 05 49 …I

Рассмотрим ответ сервера. Первым идет сообщение с типом:

#define PqMsg_RowDescription ‘T’

Его полезная нагрузка:

00 03 количество атрибутов, три

69 64 00 «id», строка с именем атрибута
00 00 40 29 Oid таблицы
00 01 номер атрибута
00 00 00 17 тип атрибута
00 04 размер атрибута (-1, если переменный)
FF FF FF FF typmod атрибута (используется в доменах)
00 00 0 = текстовое представление, 1 = бинарное

… аналогично еще для двух атрибутов …

Далее — два сообщения с данными:

#define PqMsg_DataRow ‘D’

Полезная нагрузка:

00 03 количество атрибутов
00 00 00 01 длина атрибута (-1, если NULL)
31 «1», текстовое представление атрибута
00 00 00 04 длина атрибута
41 6C 65 78 «Alex», текстовое представление атрибута
… и так далее …

Следом идут уже знакомые нам сообщения CommandComplete и ReadyForQuery с буквами C и Z соответственно.

Простой протокол довольно неэффективен. Данные ходят по сети в текстовом представлении. Запросы всегда проходят стадии парсинга и планирования, даже если один и тот же запрос выполняется много раз с разными аргументами. Кроме того, защита от SQL-injection полностью ложится на сторону приложения.

Названных недостатков лишен расширенный протокол.

Расширенный протокол

Утилиты, идущие в поставке с PostgreSQL, имеют ограниченные возможности по использованию расширенного протокола.

В psql можно делать так:

INSERT INTO phonebook (name, phone) VALUES ($1, $2)
bind ‘Charlie’ 789 g

Однако возможность байндить значение NULL не реализована (есть воркэраунд ). Возможность использования расширенного протокола для SELECT-запросов отсутствует.

Вместо psql можно воспользоваться pgbench:

echo ‘SELECT * FROM phonebook’ > q.sql
pgbench —protocol =extended -f q.sql -t 1
-h 192.168.88.36 -p 5432 -U eax

Но в pgbench не реализована возможность передачи данных в бинарном виде.

В силу названных причин воспользуемся программой, демонстрирующей возможности libpq из исходного кода PostgreSQL. Она называется testlibpq3.c .

Заполним базу данных, скопировав в psql команды из testlibpq3.sql :

CREATE SCHEMA testlibpq3;
SET search_path = testlibpq3;
SET standard_conforming_strings = ON ;
CREATE TABLE test1 ( i INT4 , t TEXT , b BYTEA ) ;
INSERT INTO test1 VALUES ( 1 , ‘joe’ ‘s place’ , ‘ 00 01 02 03 04′ ) ;
INSERT INTO test1 VALUES ( 2 , ‘ho there’ , ‘ 04 03 02 01 00′ ) ;

Для компиляции программы воспользуемся таким скриптом:

#!/bin/sh

set -e

PRFX = / Users / eax / pginstall
cc -I $PRFX / include / -c testlibpq3.c -o testlibpq3.o
cc -L $PRFX / lib / testlibpq3.o -lpq $ ( pg_config —libs ) -o testlibpq3

Запускаем:

$ ./testlibpq3 ‘host=192.168.88.36 user=eax password=$ecr3t’
tuple 0: got
i = (4 bytes) 1
t = (11 bytes) ‘joe’s place’
b = (5 bytes) 00 01 02 03 04

tuple 0: got
i = (4 bytes) 2
t = (8 bytes) ‘ho there’
b = (5 bytes) 04 03 02 01 00

В сниффере видим:

192.168.88.15:61434 -> 192.168.88.36:5432, 93 (0x5d) bytes

00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F

0000 50 00 00 00 28 00 53 45 4C 45 43 54 20 2A 20 46 P…(.SELECT * F
0010 52 4F 4D 20 74 65 73 74 31 20 57 48 45 52 45 20 ROM test1 WHERE
0020 74 20 3D 20 24 31 00 00 00 42 00 00 00 1D 00 00 t = $1…B……
0030 00 00 00 01 00 00 00 0B 6A 6F 65 27 73 20 70 6C ……..joe’s pl
0040 61 63 65 00 01 00 01 44 00 00 00 06 50 00 45 00 ace….D….P.E.
0050 00 00 09 00 00 00 00 00 53 00 00 00 04 ……..S….

Тип первого сообщения:

#define PqMsg_Parse ‘P’

Его полезная нагрузка:

00 имя запроса, в данном случае пустое
53 45 4C .. текст запроса с 0x00 на конце
00 00 информация о параметрах, здесь отсутствует

Детали реализации вы найдете в PQsendQueryGuts() .

Далее идет сообщение с типом:

#define PqMsg_Bind ‘B’

… и полезной нагрузкой:

00 имя портала, в данном случае — пустая строка
00 имя запроса, в данном случае — пустая строка
00 00 размер массива paramFormats[]
00 01 запрос имеет один параметр
00 00 00 0B длина параметра: 11 байт (-1 для NULL)
6A 6F 65 .. значение параметра
00 01 размер массива formats[]
00 01 formats[0] = 1, все результаты в бинарном формате

То есть, здесь происходит привязка безымянного запроса к безымянному порталу. Порталы те самые, что ранее мы разбирали в посте Внутренности PostgreSQL: что такое Portal . В этом можно убедиться, посмотрев на код функции, отвечающей за обработку сообщения. Функция называется exec_bind_message() .

Если formats[] не указан, для всех столбцов будет использован текстовый формат. Если указан один формат, как в данном случае, он будет использован для всех столбцов. Иначе размер formats[] должен быть равен числу возвращаемых столбцов, или сервер вернет ошибку. В этом можно убедиться, изучив код функции PortalSetResultFormat() .

Чуть больше информации касаемо аргументов сообщения можно почерпнуть из документации на функцию PQexecParams() из libpq . Характерно, что функция позволяет указать только один формат возвращаемых столбцов, хотя протокол поддерживает много.

Далее идет сообщение:

#define PqMsg_Describe ‘D’

Его аргументы:

50 символ ‘P’, означает ‘Portal’
00 имя портала, в данном случае пустое

Далее идет:

#define PqMsg_Execute ‘E’

… с аргументами:

00 имя портала, в данном случае пустое
00 00 00 00 максимальное количество строк, 0 = без ограничения

И наконец, последнее сообщение:

#define PqMsg_Sync ‘S’

Аргументов у него нет.

Ответ сервера:

192.168.88.36:5432 -> 192.168.88.15:61434, 136 (0x88) bytes

00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F

0000 31 00 00 00 04 32 00 00 00 04 54 00 00 00 42 00 1….2….T…B.
0010 03 69 00 00 00 40 1A 00 01 00 00 00 17 00 04 FF .i…@……….
0020 FF FF FF 00 01 74 00 00 00 40 1A 00 02 00 00 00 …..t…@……
0030 19 FF FF FF FF FF FF 00 01 62 00 00 00 40 1A 00 ………b…@..
0040 03 00 00 00 11 FF FF FF FF FF FF 00 01 44 00 00 ………….D..
0050 00 26 00 03 00 00 00 04 00 00 00 01 00 00 00 0B .&…………..
0060 6A 6F 65 27 73 20 70 6C 61 63 65 00 00 00 05 00 joe’s place…..
0070 01 02 03 04 43 00 00 00 0D 53 45 4C 45 43 54 20 ….C….SELECT
0080 31 00 5A 00 00 00 05 49 1.Z….I

Здесь сервер отвечает на PqMsg_Parse:

#define PqMsg_ParseComplete ‘1’

Далее идет ответ на PqMsg_Bind:

#define PqMsg_BindComplete ‘2’

Следом идут знакомые нам сообщения с буквам T (RowDescription), D (DataRow), C (CommandComplete) и Z (ReadyForQuery). Внимательно посмотрев на них, мы убеждаемся, что сервер прислал данные в бинарном формате.

Помимо этого SQL-запроса testlibpq3.c также посылает запрос с целочисленным аргументом, передаваемом в бинарном формате. Заинтересованным читателям предлагается изучить данный запрос самостоятельно, в качестве упражнения.

Заключение

Детально изучать весь протокол в рамках одной статьи не представляется возможным. Помимо рассмотренных простого и расширенного протоколов существуют еще протокол для команды COPY, конкретная последовательность действий для отмены выполнения запроса (query cancelling), протоколы физической и логической репликации, плюс разные способы аутентификации.

Для их изучения я рекомендую ставить эксперименты и смотреть в сниффер, а также поглядывать одним глазком в исходный код PostgreSQL. Как вы уже могли убедиться, разобраться в протоколе при таком подходе не составляет труда.

В этом контексте могу еще порекомендовать доклад Jelte Fennema-Nio под названием The PostgreSQL Protocol: The Good, the Bad and the Future .

Дополнение: В обсуждении libpq: unexpected return code from PQexecParams with a DO INSTEAD rule present разбирается случай, когда возвращаемое запросом значение зависит от того, какой протокол был использован — простой или расширенный, или, что эквивалентно, какой был использован вызов libpq — PQexec() или PQexecParams() .

admin