Испльзование Foreign-Data Wrappers в PostgreSQL

31 мая 2021

Foreign-Data Wrappers (FDW) – это такая фича в PostgreSQL, позволяющая обращаться к внешним СУБД, а также файлам, веб-сервисам, да и вообще к чему угодно. В настоящее время существует много готовых FDW, в том числе для Oracle, MySQL, Redis, MongoDB, ClickHouse, Kafka, Cassandra и RocksDB. Если нужный FDW еще не написан, вы можете реализовать его самостоятельно. Сегодня мы рассмотрим основы использования FDW, на примере доступа к одному серверу PostgreSQL с другого.

Для эксперимента был использован PostgreSQL 13.3. Первый сервер был запущен на виртуалке с Ubuntu Linux 20.04 LTS. На хост-системе под управлением MacOS Catalina был установлен второй сервер. С него мы будем ходить на первый сервер по FDW.

Сервер на виртуалке был настроен, как обычно. Был создан пользователь и принадлежащая ему БД. Файлы postgresql.conf и pg_hba.conf были изменены так, чтобы к БД можно было получить доступ по сети. Также была создана такая таблица:

CREATE TABLE phonebook (
  id SERIAL PRIMARY KEY,
  name VARCHAR(64),
  phone VARCHAR(64));

Проверяем, что к первому серверу (на виртуалке) можно получить доступ со второго (на хосте):

$ psql -h 127.0.0.1 -p 5454 test_database test_user

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

На втором сервере также создаем непривилегированного пользователя и его БД:

CREATE DATABASE mac_db;
CREATE USER mac_user WITH password 'AppleForever';
GRANT ALL ON DATABASE mac_db TO mac_user;

Будучи все еще под суперпользователем, подключаемся к mac_db и включаем расширение postgres_fdw:

\c mac_db
CREATE EXTENSION postgres_fdw;

Расширение идет вместе с PostgreSQL и отдельно скачивать / компилировать его не нужно.

Указываем параметры подключения к серверу:

CREATE SERVER ubuntu_server
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (dbname 'test_database', host '127.0.0.1', port '5454');

Проверяем:

eax=# \des
           List of foreign servers
     Name      | Owner | Foreign-data wrapper
---------------+-------+----------------------
 ubuntu_server | eax   | postgres_fdw
(1 row)

eax=# select * from pg_foreign_server;
-[ RECORD 1 ]-----------------------------------------------
oid        | 37539
srvname    | ubuntu_server
srvowner   | 10
srvfdw     | 37532
srvtype    |
srvversion |
srvacl     | 
srvoptions | {dbname=test_database,host=127.0.0.1,port=5454}

Говорим, в какого пользователя должен отображаться mac_user для доступа к удаленному серверу:

CREATE USER MAPPING FOR mac_user
  SERVER ubuntu_server
  OPTIONS (USER 'test_user', password 'qwerty');

Проверяем:

eax=# select * from pg_user_mappings;
-[ RECORD 1 ]-------------------------------
umid      | 37542
srvid     | 37539
srvname   | ubuntu_server
umuser    | 37541
usename   | mac_user
umoptions | {user=test_user,password=qwerty}

Разрешаем mac_user ходить на удаленный сервер:

GRANT USAGE ON FOREIGN SERVER ubuntu_server TO mac_user;

Теперь заходим под mac_user:

$ psql -U mac_user -d mac_db

Важно! Имейте ввиду, что mac_user может прочитать пароли и явки для подключения к удаленному серверу, используя приведенные выше запросы.

Для доступа к таблице phonebook говорим:

IMPORT FOREIGN SCHEMA public LIMIT TO (phonebook)
  FROM SERVER ubuntu_server INTO public;

… или:

CREATE FOREIGN TABLE fdw_phonebook
  (id SERIAL, name VARCHAR(64), phone VARCHAR(64))
  SERVER ubuntu_server
  OPTIONS (schema_name 'public', table_name 'phonebook');

Теперь с таблицей phonebook (или fdw_phonebook) можно работать, как если бы она была локальной. Все изменения с таблицей, которые мы делаем на втором сервере, будут видны на первом, и наоборот.

Если таблица больше не нужна, от нее можно избавиться так:

DROP FOREIGN TABLE phonebook;

При этом на удаленном сервере таблица продолжит существовать.

Foreign-Data Wrappers обеспечивают доступ к разным СУБД через одну точку доступа. Это удобно при выполнении аналитических запросов, а также с некоторой долей успеха позволяет горизонтально масштабировать PostgreSQL на запись. Еще FDW полезен при миграции на PostgreSQL с неправильных СУБД.