Как разделить таблицу между несколькими базами данных Postgresql

Мое веб-приложение имеет несколько развертываний - каждый из них является уникальным сайтом с уникальным URL-адресом.
Каждое развертывание имеет разные данные, пользовательский интерфейс и т.д., Но очень похожую структуру базы данных Postgresql (с PostGIS). Все базы данных находятся на одном сервере БД. Я бы хотел, чтобы пользователи из одного развертывания могли войти в систему во все другие развернутые приложения, не перерегистрировавшись.

То, что я хочу, это единая таблица "пользователей", которая разделяется между несколькими базами данных приложений. Любой пользователь, который регистрируется в одном приложении, должен быть распознан всеми другими приложениями. Возможно ли это с Postgres? Могут ли схемы сделать это?

Ответ 1

Да, схемы - это решение. Используйте один кластер PostgreSQL с единой базой данных.

Создайте группу для всех пользователей приложения:

CREATE ROLE app;

Создайте глобальную схему "приложения", в которой будут отображаться все глобальные таблицы общих приложений.

CREATE SCHEMA AUTHORIZATION app;
CREATE TABLE app.objects ( objectid int PRIMARY KEY );
ALTER TABLE app.objects OWNER TO app;

Создайте отдельный пользователь (без прав суперпользователя) для каждого развертывания:

CREATE USER app01 IN ROLE app;
CREATE USER app02 IN ROLE app;

Необязательно вместо IN ROLE app вы можете предоставить явные права для этих пользователей в выбранных объектах приложения:

GRANT USAGE ON SCHEMA app TO app01;
GRANT SELECT on app.objects TO app01;

Создайте приватные схемы, в которых будут жить таблицы, зависящие от развертывания:

CREATE SCHEMA AUTHORIZATION app01; 
CREATE SCHEMA AUTHORIZATION app02;

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

Что хорошо, это приложение не обязательно должно быть известно о схеме. SELECT * FROM froobles будет по умолчанию разрешать SELECT * FROM app01.froobles, если вы подключены как пользователь app01. Вам не нужно указывать имя схемы.

В качестве дополнительной меры вы можете использовать наследование таблицы для расширения глобальных объектов на основе развертывания:

CREATE TABLE app01.objects (
  localattr1 int,
  localattr2 text
)
INHERITS ( app.objects );

Ответ 2

Ответ filiprem является удивительным. Однако я обнаружил, что мне нужно настроить пути также для пользователей app01, app02:

ALTER USER app01 SET SEARCH_PATH TO "$user",app;
ALTER USER app02 SET SEARCH_PATH TO "$user",app;

Также, если вам нужна роль app для доступа к таблицам в схемах app01, app02, вам понадобится этот код:

-- grant all future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA app01 GRANT SELECT ON TABLES TO app;

-- grant all existing tables
GRANT SELECT 
ON ALL TABLES IN SCHEMA app01
TO app

Вместо SELECT вы можете иметь другие привилегии. То же самое для пользователя app02.

Обновление:. Чтобы пользователь app мог выбирать строки в схеме app01, для схемы app01 требуется как минимум USAGE привилегия (вместе с таблицей привилегия SELECT, определенная выше):

GRANT USAGE ON SCHEMA app01 TO app;

Ответ 3

Посмотрите dblink.