如何在多个Postgresql数据库之间共享表 [英] How to share a table between multiple Postgresql databases

问题描述

我的Web应用程序具有多个部署-每个部署都是一个具有唯一URL的唯一站点.
每个部署具有不同的数据，UI等，但是具有非常相似的Postgresql数据库结构(使用PostGIS).所有数据库都位于同一台DB服务器上.我希望1个部署中的用户能够登录到所有其他部署的应用程序而不必重新注册.

My web app has multiple deployments -- each is a unique site with a unique URL.
Each deployment has different data, UI, etc. but a very similar Postgresql database structure (with PostGIS). The databases all live on the same DB server. I would like users from 1 deployment to be able to log in to all other deployed apps without having to re-register.

我想要的是一个在多个应用程序数据库之间共享的用户"表.在一个应用程序中注册的任何用户都应被所有其他应用程序识别. Postgres有可能吗?模式是执行此操作的方法吗?

What I want is a single "users" table that is shared across multiple app databases. Any user who registers in one app should be recognized by all other apps. Is this possible with Postgres? Are schemas the way to do this?

推荐答案

是的，方案就是解决方案.使用单个PostgreSQL集群和单个数据库.

Yes, schemas are the solution. Use a single PostgreSQL cluster, with a single database.

为所有应用程序用户创建一个组:

Create a group for all of app users:

CREATE ROLE app;

创建全局应用程序"架构，所有全局共享应用程序表都将驻留在该架构中.

Create global "app" schema, where all global shared applications tables will live.

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

为每个部署创建单独的用户(没有超级用户权限):

Create separate user (with no superuser rights) for each of deployments:

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

(可选)代替IN ROLE app，您可以在选定的应用程序对象上为这些用户授予显式权限:

Optionally, instead of IN ROLE app, you can grant explicit rights for these users on selected app objects:

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

创建私有模式，其中将存在与部署相关的表:

Create private schemas, where deployment-dependent tables will live:

CREATE SCHEMA AUTHORIZATION app01; 
CREATE SCHEMA AUTHORIZATION app02;

现在，您为每个已部署的应用程序都拥有一个私有模式；但是您同时拥有对全局数据的共享访问权.

Now you have a private schema for every application deployed; but at the same time you have shared access to global data.

很好的是，该应用程序不必具有架构意识.如果您以app01用户身份连接，默认情况下SELECT * FROM froobles将解析为SELECT * FROM app01.froobles.您不必指定架构名称.

What's nice, is that application does not have to be schema-aware. SELECT * FROM froobles will by default resolve to SELECT * FROM app01.froobles, if you are connected as app01 user. You do not have to specify schema name.

作为一种额外的措施，您可以使用表继承来按部署扩展全局对象:

As an extra measure, you can use table inheritance to extend global objects on per-deployment basis:

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

这篇关于如何在多个Postgresql数据库之间共享表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！