http (pgsql-http). Оболочка для библиотеки libcurl

Поддержка HTTP-клиента на уровне СУБД

Функциональность позволяет обращаться к REST-сервисам из SQL-запросов.

В СУБД Pangolin включено расширение pgsql-http, которое является оберткой поверх библиотеки libcurl. Оно включает в себя функции для обращения к REST-сервисам и несколько вспомогательных функций.

Схема процесса «Выполнение HTTP запроса в рамках SQL-запроса»:

Табличное описание процесса «Выполнение HTTP запроса в рамках SQL-запроса»:

Наименование шага	Входной документ	Описание	Исполнитель	Выходной документ	ИТ-система	Переход к шагу
010 Настроить CURL параметры		Разработчик в рамках предварительного запроса вносит изменения в CURL параметры с помощью одной из `http_XXX_curlopt` функций	Администратор АС / Администратор СУБД		СУБД Pangolin	020
020 Использовать `http_METHOD` функцию		Разработчик использует одну из `http_METHOD` функций при написании SQL запроса, чтобы при выполнении запроса реализовывался HTTP-запрос с указанными параметрами	Администратор АС / Администратор СУБД		СУБД Pangolin	Выход

SQL-функции

Название функции	Параметры	Тип возвращаемых данных	Описание
`http_header`	`field VARCHAR`, `value VARCHAR`	`http_header`	Конструктор HTTP-заголовков
`http`	`request http_request`	`http_response`	Основная функция, реализующая отправку HTTP-запроса и получение HTTP-ответа. По сути, функции, реализующие конкретные HTTP-методы, являются обертками данной функции
`http_get`	`uri VARCHAR`	`http_response`	Функция, реализующая отправку HTTP GET запроса
`http_get`	`uri VARCHAR`, `data JSONB`	`http_response`	Функция, реализующая отправку HTTP GET-запроса
`http_post`	`uri VARCHAR, content VARCHAR`, `content_type VARCHAR`	`http_response`	Функция, реализующая отправку HTTP POST-запроса
`http_post`	`uri VARCHAR`, `data JSONB`	`http_response`	Функция, реализующая отправку HTTP POST-запроса
`http_put`	`uri VARCHAR`, `content VARCHAR`, `content_type VARCHAR`	`http_response`	Функция, реализующая отправку HTTP PUT-запроса
`http_patch`	`uri VARCHAR`, `content VARCHAR`, `content_type VARCHAR`	`http_response`	Функция, реализующая отправку HTTP PATCH-запроса
`http_delete`	`uri VARCHAR`, `content VARCHAR`, `content_type VARCHAR`	`http_response`	Функция, реализующая отправку HTTP DELETE-запроса
`http_head`	`uri VARCHAR`	`http_response`	Функция, реализующая отправку HTTP HEAD-запроса
`http_set_curlopt`	`curlopt VARCHAR`, `value varchar`	`boolean`	Функция, позволяющая настроить параметры CURL, с которыми будут обрабатываться запросы. Настройки специфичны для сеанса СУБД и сохраняются до его завершения
`http_reset_curlopt`		`boolean`	Функция для сброса параметров CURL к значениям по умолчанию
`http_list_curlopt`		`setof(curlopt text, value text)`	Функция для получения списка текущих значений параметров CURL
`urlencode`	`string VARCHAR`	`text`	Функция для «URL кодирования» входящих строк. Все символы, подлежащие кодированию (не ASCII-символы, пробелы и т.д.), заменяются на «%» и две цифры в шестнадцатеричной системе
`urlencode`	`data JSONB`	`text`	Функция для «URL кодирования» входящих строк. Все символы, подлежащие кодированию (не ASCII-символы, пробелы и т.д.), заменяются на «%» и две цифры в шестнадцатеричной системе

сURL-параметры

сURL-параметры доступные для настройки с помощью функции http_set_curlopt(curlopt VARCHAR, value varchar):

Примечание:

Cписок CURL-опций, которые могут быть использованы в расширении, сильно зависит от версии libcurl, используемой в ОС, от того, с какими опциями libcurl была скомпилирована, а также от самой ОС.

CURLOPT_DNS_SERVERS – список DNS-серверов, которые должны использоваться вместо того, что используется по умолчанию на уровне системы;
CURLOPT_PROXY – прокси-сервер, который следует использовать для последующих запросов. Указывается в формате (<IP-адрес> | <hostname>)[:<PORT>];
CURLOPT_PRE_PROXY – препрокси-сервер, который следует использовать для последующих запросов. Указывается в формате (<IP-адрес> | <hostname>)[:<PORT>];
CURLOPT_PROXYPORT – порт, который должен использоваться для подключения к прокси-серверу. Данная опция не рекомендована к использованию. При необходимости указания порта лучше использовать его в рамках параметра CURLOPT_PROXY;
CURLOPT_PROXYUSERPWD – имя пользователя и пароль для аутентификации на прокси-сервере. Указывается в формате <имя пользователя>:<пароль>;
CURLOPT_PROXYUSERNAME – имя пользователя для аутентификации на прокси-сервере;
CURLOPT_PROXYPASSWORD – пароль для аутентификации на прокси-сервере;
CURLOPT_PROXY_TLSAUTH_USERNAME – имя пользователя для аутентификации на прокси-сервере по протоколу TLS.;
CURLOPT_PROXY_TLSAUTH_PASSWORD – пароль для аутентификации на proxy сервере по протоколу TLS;
CURLOPT_PROXY_TLSAUTH_TYPE – метод аутентификации на proxy сервере по протоколу TLS. Допустимые значения: пустое (по умолчанию) и «SRP» для TLS-SRP-аутентификации;
CURLOPT_TLSAUTH_USERNAME – имя пользователя для аутентификации по протоколу TLS;
CURLOPT_TLSAUTH_PASSWORD – пароль для аутентификации по протоколу TLS;
CURLOPT_TLSAUTH_TYPE – метод аутентификации по протоколу TLS. Допустимые значения: пустое (по умолчанию) и «SRP» для TLS-SRP-аутентификации;
CURLOPT_SSL_VERIFYHOST – опция определяет, должна ли libcurl проверять, что серверный сертификат относится к данному серверу. Допустимые значения:
- 2 (по умолчанию) – если сервер не совпадает с сервером, указанном в сертификате, соединение не будет установлено;
- 1 – поведение различается от версии к версии, не должно быть использовано в общем случае;
- 0 – соединение будет установлено успешно независимо от того, совпадает ли имя сервера с именем в сертификате;
CURLOPT_SSL_VERIFYPEER – данный параметр определяет, будет ли проводиться проверка, что серверный сертификат был подписан CA. Допустимые значения:
- 1 (по умолчанию) – проверка будет проводиться; в случае, если сертификат был подписан некорректно, соединение не будет установлено;
- 0 – проверка не будет проводиться;
Альтернативные CA сертификаты могут быть заданы с помощью параметров CURLOPT_CAINFO и CURLOPT_CAPATH;
CURLOPT_SSLCERT – имя файла с клиентским сертификатом. Формат по умолчанию: P12 (для Secure Transport) и PEM (для остальных случаев). Формат может быть изменен с помощью параметра CURLOPT_SSLCERTTYPE;
CURLOPT_SSLKEY – имя файла с частным ключом для клиентского TLS/SSL-сертификата. Формат по умолчанию: PEM (может быть изменен с помощью параметра CURLOPT_SSLCERTTYPE);
CURLOPT_SSLCERTTYPE – тип клиентского SSL-сертификата. Поддерживаемые форматы: PEM (по умолчанию) и DER (не поддерживается Secure Transport и Schannel). OpenSSL (версии старше 0.9.3), Secure Transport (iOS 5+, OS X 10.7+) и Schannel поддерживают P12 для PKCS#12 файлов;
CURLOPT_CAINFO – путь к Certificate Authority (CA) bundle;
CURLOPT_TIMEOUT – максимальное время (в секундах), за которое запрос должен быть завершен. Если указаны оба параметра CURLOPT_TIMEOUT и CURLOPT_TIMEOUT_MS, то будет использовано значение, установленное последним;
CURLOPT_TIMEOUT_MS – максимальное время (в миллисекундах), за которое запрос должен быть завершен. Если указаны оба параметра CURLOPT_TIMEOUT и CURLOPT_TIMEOUT_MS, то будет использовано значение, установленное последним;
CURLOPT_TCP_KEEPALIVE – если данный параметр включен (по умолчанию выключен), в периоды, когда соединение неактивно, будут посылаться TCP-пробы. Интервал между пробами определяется параметром CURLOPT_TCP_KEEPIDLE;
CURLOPT_TCP_KEEPIDLE – интервал между TCP-пробами в секундах;
CURLOPT_CONNECTTIMEOUT – тайм-аут для установки соединения в секундах. В случае, если проставлено нулевое значение, используется значение по умолчанию (300 секунд);
CURLOPT_USERAGENT – user-agent HTTP-заголовок.

Доработка

В рамках расширения в продукт Pangolin добавлены новые типы данных:

http_header;
http_request;
http_response.

Каждый HTTP-вызов состоит из запроса (http_request) и ответа (http_response).

Параметры http_request:

Название параметра	Тип	Описание
method	varchar	Название HTTP-метода
uri	varchar	URI сервиса, к которому производится запрос
headers	http_header[]	Массив HTTP-заголовков
content_type	varchar	Тип содержимого
content	varchar	Содержимое запроса

Параметры http_response:

Название параметра	Тип	Описание
status	integer	Код состояния HTTP-запроса
content_type	varchar	Тип возвращаемых данных
headers	http_header[]	Массив HTTP-заголовков
content	varchar	Содержимое ответа сервиса на HTTP-запрос

Служебные функции, http_get(), http_post(), http_put(), http_delete() и http_head() являются оболочками вокруг главной функции, http(http_request), которая возвращает http_response.

Поле headers для запросов и ответов представляет собой массив PostgreSQL типа http_header, который является кортежем.

Параметры http_header:

Название параметра	Тип	Описание
field	varchar	Название HTTP-заголовка
value	varchar	Значение HTTP-заголовка

Можно распаковать массив http_header кортежей в результирующий набор, используя функцию PostgreSQL unnest() для массива. Затем оттуда выбрать конкретный интересующий заголовок (см. подробнее об этом в разделе «Примеры»).

Внимание!

Данное расширение может быть использовано для отсылки доступных для роли данных из СУБД во внешнюю систему. Чтобы минимизировать риски утечки данных следует:

Использовать версию libcurl, скомпилированную с поддержкой минимально необходимого набора протоколов (HTTP/HTTPS/FTP). Как минимум следует отключать возможность использования протокола file (file://), чтобы исключить возможность чтения файлов на локальной файловой системе, доступных для чтения учетной записью ОС postgres.

Разрешать на уровне брандмауэра только сетевые взаимодействия из «белого списка», в который должны быть включены строго те маршруты передачи данных, которые необходимы для функционирования АС и их обслуживания.

Ограничения

Ограничения отсутствуют.

Установка расширения

Подготовительные действия:

Разверните СУБД Pangolin в конфигурации с Pangolin Manager (например, cluster-patroni-etcd-pgbouncer).

Измените настройки Pangolin Manager в /etc/patroni/postgres.yml так, чтобы в секции restapi остались только следующие строки:

для работы по протоколу HTTP:

restapi:
listen: \<IP-адрес\>:8008
connect_address: \<hostname\>:8008
allowlist: []
allowlist_include_members: true

для работы по протоколу HTTPS (необходимо указать месторасположение сертификатов):

restapi:
listen: \<IP-адрес\>:8008
connect_address: \<hostname\>:8008
allowlist: []
allowlist_include_members: true
ciphers: DEFAULT:!SSLv1:!SSLv2:!SSLv3:!TLSv1:!TLSv1.1
verify_client: optional
cafile: /pg_ssl/root.crt
capath: /pg_ssl/root_dir
certfile: /pg_ssl/server.crt
keyfile: /pg_ssl/server.key
crlpath: /pg_ssl/crl
crlfile: /pg_ssl/crl/intermediate.crl

Разархивируйте папку расширения:

cd {путь к дистрибутиву pangolin}/3rdparty/pgsql_http
# Для ОС «Альт»
sudo apt-get install /usr/pangolin-6.3/3rdparty/pgsql_http/pangolin-pgsql-http-1.6-{OS}.x86_64.rpm -y
# Для других ОС
sudo dnf install /usr/pangolin-6.3/3rdparty/pgsql_http/pangolin-pgsql-http-1.6-{OS}.x86_64.rpm -y

Перезапустите Pangolin Manager:
```
sudo systemctl restart pangolin-manager
```
Для установки расширения необходимо подключиться к БД с правами суперпользователя:
```
CREATE EXTENSION http;
```

Использование модуля

Получение URL-кода строки:

SELECT urlencode('my special string''s & things?');

Пример ответа:

urlencode
-------------------------------------
my+special+string%27s+%26+things%3F
(1 row)

Получение URL-кода ассоциативного массива JSON:

SELECT urlencode(jsonb_build_object('name','Colin & James','rate','50%'));

Пример ответа:

urlencode
-------------------------------------
name=Colin+%26+James&rate=50%25
(1 row)

Выполнение GET-запроса с просмотром содержимого:

SELECT content
FROM http_get('http://httpbun.org/ip');

Пример ответа:

content
-----------------------------
{"origin":{IP-адрес}
(1 row)

Выполнение GET-запроса с заголовком авторизации:

SELECT content::json->'headers'->>'Authorization'
FROM http((
'GET',
'http://httpbun.org/headers',
ARRAY[http_header('Authorization','Bearer {хеш}')],
NULL,
NULL
)::http_request);

Пример ответа:

content
----------------------------------------------
Bearer {хеш}
(1 row)

Считывание полей status и content_type из объекта http_response:

SELECT status, content_type
FROM http_get('http://httpbun.org/');

Пример ответа:

 status |       content_type
--------+--------------------------
    200 | text/html; charset=utf-8
(1 row)

Отображение всех http_header в объекте http_response:

SELECT (unnest(headers)).*
FROM http_get('http://httpbun.org/');

Пример ответа:

      field       |                      value
------------------+--------------------------------------------------
 Server           | nginx
 Date             | Wed, 26 Jul 2023 19:52:51 GMT
 Content-Type     | text/html
 Content-Length   | 162
 Connection       | close
 Location         | https://httpbun.org
 server           | nginx
 date             | Wed, 26 Jul 2023 19:52:51 GMT
 content-type     | text/html
 x-powered-by     | httpbun/{хеш}
 content-encoding | gzip

Отправка на сервер простого текстового документа с помощью команды PUT:

SELECT status, content_type, content::json->>'data' AS data
FROM http_put('http://httpbun.org/put', 'some text', 'text/plain');

 status |   content_type   |   data
--------+------------------+-----------
    200 | application/json | some text

Отправка на сервер простого текстового JSON-документа с помощью команды PATCH:

SELECT status, content_type, content::json->>'data' AS data
FROM http_patch('http://httpbun.org/patch', '{"this":"that"}', 'application/json');

Пример ответа:

 status |   content_type   |      data
--------+------------------+------------------
    200 | application/json | '{"this":"that"}'

Использование команды DELETE в запросе на удаление ресурса:

SELECT status, content_type, content::json->>'url' AS url
FROM http_delete('http://httpbun.org/delete');

Пример ответа:

 status |   content_type   |            url
--------+------------------+---------------------------
    200 | application/json | http://httpbun.org/delete

Передача аргумента данных в формате JSONB в качестве сокращения для передачи данных в GET-запрос:

SELECT status, content::json->'args' AS args
FROM http_get('http://httpbun.org/get',
jsonb_build_object('myvar','myval','foo','bar'));

Кодирование данных в JSONB как полезной нагрузки данных, для выполнения POST на URL, используя полезную нагрузку данных вместо параметров, встроенных в URL:

SELECT status, content::json->'form' AS form
FROM http_post('http://httpbun.org/post',
jsonb_build_object('myvar','myval','foo','bar'));

Для доступа к двоичному содержимому необходимо преобразовать его из стандартного представления varchar в представление bytea с помощью функции text_to_bytea() или функции textsend(). Использование стандартного приведения varchar::bytea не будет работать, так как приведение остановится при первом же попадании на байт с нулевым значением (что часто встречается в двоичных данных):

WITH
http AS (
SELECT * FROM http_get('https://httpbingo.org/image/png')
),
headers AS (
SELECT (unnest(headers)).* FROM http
)
SELECT
http.content_type,
length(text_to_bytea(http.content)) AS length_binary
FROM http, headers
WHERE field ilike 'Content-Type';

Пример ответа:

 content_type | length_binary
--------------+---------------
 image/png    |          8090

Аналогично, при использовании POST для отправки двоичного содержимого bytea в службу используйте функцию bytea_to_text для подготовки содержимого.

Чтобы получить доступ только к заголовкам, можно выполнить HEAD-запрос. Это не приведет к перенаправлениям:

SELECT
http.status,
headers.value AS location
FROM
http_head('http://google.com') AS http
LEFT OUTER JOIN LATERAL (SELECT value
FROM unnest(http.headers)
WHERE field = 'Location') AS headers
ON true;

Пример ответа:

 status |        location
--------+------------------------
    301 | http://www.google.com/

Поддержка HTTP-клиента на уровне СУБД​

SQL-функции​

сURL-параметры​

Доработка​

Ограничения​

Установка расширения​

Использование модуля​

Поддержка HTTP-клиента на уровне СУБД

SQL-функции

сURL-параметры

Доработка

Ограничения

Установка расширения

Использование модуля