blocks|key|1920645|text|与其将数据放在单独的数据库中，不如将所有数据放入一个数据库中，然后使用图式和访问权限将数据分开。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1920646|具体地说：|1920647|公共和管理站点应该使用不同的“登录角色”(用户)进行连接。|unordered-list-item|1920648|只有管理站点使用的表可以在admin模式中，并且公共用户不会为该模式授予USAGE。|style|CODE|1920649|数据的其他部分可以放在命名模式中，其中一些可以被两个站点完全访问，有些则是混合的。(如果您将模式划分得很好，可以使用GRANT+...+ON+ALL+TABLES+IN+SCHEMA和ALTER+DEFAULT+PRIVILEGES。)|1920650|如您所说，在视图中引用公共应该具有某些只读访问权限的表。公共用户可以对这些视图(而不是底层表)进行GRANT编辑访问。视图可以引用另一个架构中的表，甚至可以跨多个架构引用表，如果这有助于您管理事物的话。|1920651|或者，对整个表的只读访问可以通过不授予该表上的公共用户INSERT和UPDATE权限来授予。|1920652|您甚至可能会发现，将公共和管理看作不是单独的应用程序，而是同一应用程序的不同前端接口--例如，应该在它们之间共享代码，而不是复制和粘贴。|1920653|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/current/interactive/ddl-schemas.html|1|http://www.postgresql.org/docs/current/interactive/user-manag.html|2|http://www.postgresql.org/docs/current/interactive/sql-alterdefaultprivileges.html^0|Z|2|0|12|4|1|0|0|0|D|5|10|5|0|1M|X|2K|O|2K|O|2|0|1D|5|0|R|6|Y|6|0|0^^$0|@$1|2|3|4|5|6|7|16|8|@]|9|@$A|17|B|18|1|19]|$A|1A|B|1B|1|1C]]|C|$]]|$1|D|3|E|5|6|7|1D|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|1E|8|@]|9|@]|C|$]]|$1|I|3|J|5|H|7|1F|8|@$A|1G|B|1H|K|L]|$A|1I|B|1J|K|L]]|9|@]|C|$]]|$1|M|3|N|5|H|7|1K|8|@$A|1L|B|1M|K|L]|$A|1N|B|1O|K|L]]|9|@$A|1P|B|1Q|1|1R]]|C|$]]|$1|O|3|P|5|H|7|1S|8|@$A|1T|B|1U|K|L]]|9|@]|C|$]]|$1|Q|3|R|5|H|7|1V|8|@$A|1W|B|1X|K|L]|$A|1Y|B|1Z|K|L]]|9|@]|C|$]]|$1|S|3|T|5|6|7|20|8|@]|9|@]|C|$]]|$1|U|3|-4|5|6|7|21|8|@]|9|@]|C|$]]]|V|$W|$5|X|Y|Z|C|$10|11]]|12|$5|X|Y|Z|C|$10|13]]|14|$5|X|Y|Z|C|$10|15]]]]

Rather than the data being in separate databases, put everything into a single database, and then use <a href="http://www.postgresql.org/docs/current/interactive/ddl-schemas.html" rel="nofollow">schemas</a> and <a href="http://www.postgresql.org/docs/current/interactive/user-manag.html" rel="nofollow">access permissions</a> to separate things out.

Specifically:

<ul>
<li>The public and admin sites should connect using different "login roles" (users).</li>
<li>Tables only used by the admin site could be in an <code>admin</code> schema, and the public user would not have <code>USAGE</code> granted for that schema.</li>
<li>Other sections of data could be put into named schemas, some of which would be fully accessible by both sites, some of which would have a mixture. (You can use <code>GRANT ... ON ALL TABLES IN SCHEMA</code> and <a href="http://www.postgresql.org/docs/current/interactive/sql-alterdefaultprivileges.html" rel="nofollow"><code>ALTER DEFAULT PRIVILEGES</code></a> if you divide your schemas up well.)</li>
<li>Tables to which public should have some read-only access could, as you say, be referenced in views. The public user can be <code>GRANT</code>ed access to these views rather than the underlying tables. Views can reference tables in another schema, or even across multiple schemas, if that helps you manage things.</li>
<li>Alternatively, read-only access to a whole table can be granted by simply not giving the public user <code>INSERT</code> and <code>UPDATE</code> privileges on that table.</li>
</ul>

You may even find it useful to think of public and admin not as separate applications, but as different front-end interfaces to the same application - there may be code that should be shared between them rather than copied-and-pasted, for instance.

blocks|key|1920672|text|让公众直接访问admin数据库，并使用角色控制权限。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1920673|数据库角色|1920674|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/current/interactive/user-manag.html^0|7|5|0|0|5|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@$9|Q|A|R|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|S|8|@]|D|@$9|T|A|U|1|V]]|E|$]]|$1|H|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|I|$J|$5|K|L|M|E|$N|O]]]]

Let public access the <code>admin</code> db directly and control the privileges with roles.

<a href="http://www.postgresql.org/docs/current/interactive/user-manag.html" rel="nofollow">Database Roles</a>

I have two web applications, admin one and public one. While both have a fair amount of separate data, a certain part, let's call them items, are used in both. To be more exact, items are created and edited in admin application, and then displayed in public application.

Since both are using PostgreSQL as database backend, I figured they could be joined in database level - I would create views in public database that get data over dblink to admin database.

Is this a good solution, given following constraints?

<ul>
<li>There are quite many items,</li>
<li>Public application will do extensive searching over the data set,</li>
<li>It's ok for the data to be little bit stale on public side,</li>
<li>Currently the applications use the same physical database server, but might split in future;</li>
</ul>

If not, what would you suggest?

Postgres - sharing data sets between applications

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有两个web应用程序，一个管理和公共一个。虽然两者都有相当数量的独立数据，但在这两种数据中都使用了特定的部分(我们称之为items )。更确切地说，项目是在管理应用程序中创建和编辑的，然后在公共应用程序中显示。由于两者都使用PostgreSQL作为数据库后端，所以我认为它们可以在数据库级别上加入--我将在公共数据库中...

问Postgres -应用程序之间的共享数据集
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Postgres -应用程序之间的共享数据集EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Postgres -应用程序之间的共享数据集
EN