Рецепты PostgreSQL: cURL: get, post и… email

  • Tutorial
Для приготовления cURL в PostgreSQL нам понадобится сам postgres и его расширение pg_curl. (Я дал ссылки на свой форк postgres, т.к. делал некоторые изменения, которые пока не удалось пропихнуть в оригинальный репозиторий. Можно также воспользоваться готовым образом.)

Для начала устанавливаем расширение командой

CREATE EXTENSION pg_curl

Для get запроса создадим функцию

CREATE OR REPLACE FUNCTION get(url TEXT) RETURNS TEXT LANGUAGE SQL AS $BODY$
    WITH s AS (SELECT
        pg_curl_easy_reset(), -- обнуляем (на всякий случай)
        pg_curl_easy_setopt('CURLOPT_URL', url), -- задаём адрес запроса
        pg_curl_header_append('Connection', 'close'), -- отключаемся после выполнения запроса
        pg_curl_easy_perform(), -- выполняем запрос
        pg_curl_easy_getinfo_char('CURLINFO_RESPONSE') -- получаем результат выполнения запроса
    ) SELECT pg_curl_easy_getinfo_char FROM s; -- возвращаем результат выполнения запроса
$BODY$;

Для urlencoded post запроса создадим функцию

CREATE OR REPLACE FUNCTION post(url TEXT, request JSON) RETURNS TEXT LANGUAGE SQL AS $BODY$
    WITH s AS (SELECT
        pg_curl_easy_reset(), -- обнуляем (на всякий случай)
        pg_curl_easy_setopt('CURLOPT_URL', url), -- задаём адрес запроса
        pg_curl_header_append('Connection', 'close'), -- отключаемся после выполнения запроса
        pg_curl_easy_setopt('CURLOPT_COPYPOSTFIELDS', ( -- задаём тело запроса
            WITH s AS (
                SELECT (json_each_text(request)).* -- пробегаемся по всему json-у
            ) SELECT array_to_string(array_agg(concat_ws('=', -- превращая его в urlencoded
                pg_curl_easy_escape(key),
                pg_curl_easy_escape(value)
            )), '&') FROM s
        )),
        pg_curl_easy_perform(), -- выполняем запрос
        pg_curl_easy_getinfo_char('CURLINFO_RESPONSE') -- получаем результат выполнения запроса
    ) SELECT pg_curl_easy_getinfo_char FROM s; -- возвращаем результат выполнения запроса
$BODY$;

Для json post запроса создадим функцию

CREATE OR REPLACE FUNCTION post(url TEXT, request JSON) RETURNS TEXT LANGUAGE SQL AS $BODY$
    WITH s AS (SELECT
        pg_curl_easy_reset(), -- обнуляем (на всякий случай)
        pg_curl_easy_setopt('CURLOPT_URL', url), -- задаём адрес запроса
        pg_curl_header_append('Content-Type', 'application/json; charset=utf-8'), --задаём тип тела запроса
        pg_curl_header_append('Connection', 'close'), -- отключаемся после выполнения запроса
        pg_curl_easy_setopt('CURLOPT_COPYPOSTFIELDS', request::TEXT), -- задаём тело запроса
        pg_curl_easy_perform(), -- выполняем запрос
        pg_curl_easy_getinfo_char('CURLINFO_RESPONSE') -- получаем результат выполнения запроса
    ) SELECT pg_curl_easy_getinfo_char FROM s; -- возвращаем результат выполнения запроса
$BODY$;

Для отправки почты создадим функцию

CREATE OR REPLACE FUNCTION email(url TEXT, username TEXT, password TEXT, subject TEXT, "from" TEXT, "to" TEXT[], data TEXT, type TEXT) RETURNS TEXT LANGUAGE SQL AS $BODY$
    WITH s AS (SELECT
        pg_curl_easy_reset(), -- обнуляем (на всякий случай)
        pg_curl_easy_setopt('CURLOPT_URL', url), -- задаём адрес запроса
        pg_curl_easy_setopt('CURLOPT_USERNAME', username), -- задаём логин
        pg_curl_easy_setopt('CURLOPT_PASSWORD', password), -- задаём пароль
        pg_curl_recipient_append("to"), -- задаём получателей
        pg_curl_header_append('Subject', subject), -- задаём тему
        pg_curl_header_append('From', "from"), -- задаём отправителя
        pg_curl_header_append('To', "to"), -- задаём получателей
        pg_curl_mime_data(data, type:=type), -- задаём тело
        pg_curl_header_append('Connection', 'close'), -- отключаемся после выполнения
        pg_curl_easy_perform(), -- выполняем
        pg_curl_easy_getinfo_char('CURLINFO_HEADERS') -- получаем результат
    ) SELECT pg_curl_easy_getinfo_char FROM s; -- возвращаем результат
$BODY$;

И всё это можно выполнять асинхронно в фоне с помощью планировщика.
Поделиться публикацией

Похожие публикации

Комментарии 10

    +2

    Зачем?

      –1
      Например, я у себя сделал отправку СМС прямо из PostgreSQL с помощью REST интерфейса оператора, а также регистрацию чеков в ОФД тоже с помощью REST интерфейса провайдера
        0
        но зачем?
          0
          Использую нечто подобное (отправка e-mail) на одной из моих систем. Тикая фишка важна для обеспечения высокой безопасности системы.
          В конкретном моем случае есть PHP frontend и PostgreSQL. Каждый запрос пользователя требующий обращения к базе данных происходит используя логином и паролем самого пользователя (Login/Password WEB = Login/Password DB). Со стороны базы обеспечен ROW-level security: пользователь видит только свои записи в базе и ничего более, и записи может менять только через функции. Такой подход довольно трудоемкий и требует вдумчивого дизайна поэтому на практике встречается довольно редко. Одна из «проблемных» фишек в такой архитектуре — возможность пользователю сбросить свой пароль если «забыл» старый. Соображения безопасности не позволяют отдать какую функциональность в frontend так как это подразумевало-бы наличие у PHP доступа с аккаунту «суперпользователя» что полностью подрывает архитектуру безопасности (полный/частичный взлом frontend не должен быть критичным для функционирования всей системы или, тем более, не должен давать доступа к данным пользователей). Соответственно ResetCode, а после подтверждения, и временный Password отсылаются пользователю напрямую из PostgreSQL.
          При таком подходе, для полного взлома, злоумышленнику нужно взять под свой контроль множество гетерогенных систем, что, скажем так, как минимум не тривиально.
      +1
      Наблюдаю за вашими статьями и не могу понять, зачем эти извраты? почему не использовать язык общего назначения для подобных штук?
        –1
        Ну, изначально я и писал на web2py. Но зачем использовать python, если можно его и не использовать?
        0

        А почему бы не включить какой-нибудь PL/python и через него такие вещи делать?

          –1
          Зачем использовать python, если можно его и не использовать?
          +2

          Как представлю танцы новой команды аутсорсеров над проектом без документации, в котором используется такое решение…
          (спустя… часов дебага)
          Кто, говорите, письма отправляет? PostgreSQL???

            +1
            Это ещё что! Я у себя в форке curl добавил поддержку выполнения команд по ssh, и теперь у меня postgres может зайти по ssh на сервак, циску, микротик и выполнять там команды. И всё это асинхронно благодаря планировщику!

          Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

          Самое читаемое