如何在多个Postgresql数据库之间共享一个表

Question

如何在多个Postgresql数据库之间共享一个表

sqlpostgresqldatabase-designauthenticationdatabase-schema

14

我的Web应用有多个部署 -- 每个部署都是一个具有唯一URL的独特站点。
每个部署具有不同的数据、UI等，但有非常相似的Postgresql数据库结构（带PostGIS）。这些数据库都存储在同一数据库服务器上。我希望来自一个部署的用户能够登录到所有其他已部署的应用程序而无需重新注册。

我想要的是一个共享于多个应用程序数据库的单个 "users" 表。任何在一个应用中注册的用户应该被所有其他应用程序识别。

使用Postgres是否可以实现这一点？使用模式来实现是否可行？

- lubar

3个回答

4

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;

- nikravi

2

Take a look at dblink.

- Branko Dimitrijevic

谢谢Branko。我应该注意到所有数据库目前都在同一台服务器上，所以也许使用dblink有些过度了？ - lubar

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- filiprem · Accepted Answer

是的，模式是解决方案。使用单个PostgreSQL集群和单个数据库。

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

CREATE ROLE app;

创建全局的“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;

现在，每个应用程序都有自己的私有架构；但同时您可以共享全局数据。

很好的一点是，应用程序不必了解模式。如果您作为“app01”用户连接，则SELECT * FROM froobles默认会解析为SELECT * FROM app01.froobles。您无需指定模式名称。

作为额外措施，您可以使用表继承来基于部署实例扩展全局对象：

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