Database Schemas
In PostgreSQL, each database consists of a number of "schemas" (also known as "namespaces"). The default schema is named "public" and many users only ever deal with this one schema.
在 PostgreSQL 中,每个数据库都由若干个“schema”(也称为“namespace”,即命名空间)组成。默认的 schema 名为“public”,很多用户日常只与这一个 schema 打交道。
In PostGraphile we advise you to use schemas to help organize your application - you can use one schema for the tables that will be exposed to GraphQL, another for the tables that should be completely private (e.g. where you store the bcrypted user passwords or other secrets never to be exposed!), and you can use other schemas too, whatever makes sense for your application.
在 PostGraphile 中,我们建议利用 schema 来组织应用结构:例如用专门的 schema 存放要暴露给 GraphQL 的表,用另一个 schema 存放必须完全私密的表(如存储经过 bcrypt 加密的用户密码或其他绝不对外暴露的机密信息),还可以根据业务需要划分更多 schema。
To create a schema in PostgreSQL:
在 PostgreSQL 中创建 schema:
create schema app_public;
To create or reference something in that schema, just prepend the name of the thing with the schema name, e.g:
要在该 schema 中创建或引用对象,只需在对象名称前加上 schema 名即可,例如:
create table app_public.users ( ... );
create function app_public.best_user() ...;
select * from app_public.users;
select * from app_public.best_user();
Advice实践建议
Having built quite a few applications on PostGraphile now, I (Benjie) have settled on the following:
基于 PostGraphile 构建过大量应用后,我(Benjie)总结出了如下划分方式:
-
app_public- tables and functions to be exposed to GraphQL (or any other system) - it's your public interface. This is the main part of your database.app_public—— 存放要暴露给 GraphQL(或其他外部系统)的表和函数,相当于你的公共接口,这也是数据库的主体部分。 -
app_hidden- same privileges asapp_public, but it's not intended to be exposed publicly. It's like "implementation details" of your app_public schema. You may not need it often.app_hidden—— 权限与app_public相同,但不打算公开暴露,相当于 app_public schema 的“内部实现细节”。实际开发中可能不常需要它。 -
app_private- SUPER SECRET STUFF 🕵️ No-one should be able to read this without aSECURITY DEFINERfunction letting them selectively do things. This is where you store passwords (bcrypted), access tokens (hopefully encrypted), etc. It should be impossible (thanks to RBAC (GRANT/REVOKE)) for web users to access this.app_private—— 高度机密区域 🕵️ 除非通过SECURITY DEFINER函数受控地开放特定操作,否则任何人都无权读取其中的内容。这里用来存放密码(经 bcrypt 哈希)、访问令牌(建议加密存储)等敏感数据。借助 RBAC(GRANT/REVOKE),Web 用户应当完全无法访问该 schema。
I personally don't use the public schema for anything other than as the
default location that PostgreSQL extensions get installed.
我个人除了将 public schema 用作 PostgreSQL 扩展的默认安装位置之外,不会在里面存放其他任何内容。
Using this pattern is not required and in fact you can just use the default
public schema if you so chose. By default, PostGraphile will automatically
ignore resources installed by extensions, so you don't need to omit these
manually. Other tables and functions can be omitted using the
smart comments functionality.
这种划分方式并非强制要求,如果愿意,你完全可以只使用默认的 public schema。默认情况下,PostGraphile 会自动忽略扩展安装的各种数据库资源,因此无需手动排除它们;其他表和函数则可通过 smart comments 功能来忽略。
Other schemas其他 schema
Here's some other schemas you might see (but should not modify):
以下是你可能在数据库中看到但切勿修改的其他 schema:
-
graphile_worker- a schema used and managed by Graphile Workergraphile_worker—— 由 Graphile Worker 使用并管理的 schema -
graphile_migrate- a schema used and managed by Graphile Migrategraphile_migrate—— 由 Graphile Migrate 使用并管理的 schema -
postgraphile_watch- if you're using PostGraphile in watch mode PostGraphile will attempt to install this schema to create PostgreSQL Event Triggers to enable watch modepostgraphile_watch—— 如果你在监听模式(watch mode)下运行 PostGraphile,它会尝试安装此 schema,以创建 PostgreSQL 事件触发器(Event Trigger)来实现监听功能 -
information_schema- a SQL standard schema for reflection over the database: https://www.postgresql.org/docs/current/information-schema.htmlinformation_schema—— 用于查询数据库元数据的 SQL 标准 schema:https://www.postgresql.org/docs/current/information-schema.html -
pg_catalog- the PostgreSQL system catalog, contain details of everything inside the database (a more PG-native form ofinformation_schema)pg_catalog—— PostgreSQL 系统目录,记录了数据库内部的一切元数据详情(比information_schema更贴近 PostgreSQL 原生结构) -
pg_*- various other PostgreSQL reserved schemaspg_*—— 其他各类 PostgreSQL 保留 schema