The Postgres database is rich with features well beyond that of any other database. However, most developers do not know the extent to which they can leverage the features in Postgres to completely express their application business logic in the database.
Postgres 数据库拥有远超其他同类数据库的丰富特性。然而,大多数开发者并不清楚,利用 Postgres 自身的能力究竟能在多大程度上把应用的业务逻辑完整地表达在数据库层中。
Often developers may find themselves re-implementing authentication and authorization in their apps, when Postgres comes with application level security features out of the box. Or perhaps developers may rewrite basic insert functions with some extra app logic where that too may be handled in the database.
开发者常常会在应用层重新实现身份认证和权限控制,殊不知 Postgres 原生就内置了应用级的安全特性;又或者为了附加一点业务逻辑而重写基础的数据写入函数,其实这些同样可以直接交由数据库处理。
This reimplementation of features that come with Postgres is not just an inefficient way to spend developer resources, but may also result in an interface that is slower than if the logic was implemented in Postgres itself. PostGraphile aims to make developers more efficient and their APIs faster by packaging the repeatable work in one open source project that encourages community contributions.
重复造轮子去实现 Postgres 原本就具备的功能,不仅浪费开发资源,而且往往会导致接口性能不如直接把逻辑放在 Postgres 内部实现来得快。PostGraphile 将这些重复性的工作封装在一个鼓励社区共建的开源项目中,旨在帮助开发者提升研发效率,并构建出响应更快的 API。
In this tutorial we will walk through the Postgres schema design for a forum application with users who can login and write forum posts. While we will discuss how you can use the schema we create with PostGraphile, this article should be useful for anyone designing a Postgres schema.
在本教程中,我们将一步步设计一个支持用户登录和发帖的论坛应用 Postgres schema。虽然文中会结合 PostGraphile 来探讨如何使用这套 schema,但其中的设计思路对所有设计 Postgres schema 的开发者都具有参考价值。
If you haven't installed PostGraphile already, you can follow our Quick Start Guide to get PostGraphile up and running.
如果你尚未安装 PostGraphile,可以先参考快速入门指南完成安装并启动服务。
Table of Contents目录
The Basics基础篇
Setting Up Your Schemas创建自定义 Schema
All of our database objects will go into one or two custom Postgres schemas. A
schema is essentially a namespace, it allows you to create tables with the same
name like a.person and b.person.
本教程中的所有数据库对象都会存放在一到两个自定义的 Postgres schema 中。schema 本质上是一个命名空间,它允许你在同一个数据库中创建同名的表,例如 a.person 和 b.person。
You can name your schema anything, we recommend naming your schema after your
app. This way if you are working on multiple apps in the same database (this
might only realistically happen in development), you can easily query the
databases of the different apps. We are going to create two schemas:
forum_example, and forum_example_private. To create these schemas we use the
CREATE SCHEMA
command.
schema 可以任意命名,我们建议以应用名称来命名。这样即便在同一个数据库中同时开发多个应用(尽管这种情况通常只出现在开发环境),也能轻松区分并查询不同应用的数据。接下来我们将创建两个 schema:forum_example 和 forum_example_private。创建 schema 使用 CREATE SCHEMA 命令:
create schema forum_example;
create schema forum_example_private;
You could create more or less schemas, it is all up to you and how you want to
structure your database. We decided to create two schemas. One of which,
forum_example, is meant to hold data users can see, whereas
forum_example_private will never be directly accessible to users.
具体创建多少个 schema 完全取决于你希望如何组织数据库结构。这里我们选择创建两个 schema:其中 forum_example 用于存放对用户可见的数据,而 forum_example_private 中的数据则绝不允许用户直接访问。
Theoretically we want a user to be able to log in directly to our Postgres database, and only be able to create, read, update, and delete data for their user all within SQL. This is a mind-shift from how we traditionally use a SQL database. Normally, we assume whoever is querying the database has full visibility into the system as the only one with database access is our application. In this tutorial, we want to restrict access at the database level. Don’t worry though! Postgres is very secure about this, users will have no more permissions than that which you explicitly grant.
从设计理念上讲,我们希望即使用户直接登录到 Postgres 数据库,也只能在 SQL 层面增删改查属于其本人权限范围内的数据。这与传统使用 SQL 数据库的方式截然不同——在传统用法中,只有后端应用直接连接数据库,因此默认数据库查询者拥有全库视野;而在本教程中,我们将直接在数据库层对访问权限加以限制。不过无需担心安全问题,Postgres 的权限体系非常严谨:只要没有显式授予,用户就不具备任何额外权限。
When starting PostGraphile, you will want to use the name of the
schema you created with the --schema option, like so:
postgraphile --schema forum_example. Also, don’t forget to add the --watch
flag, with watch mode enabled PostGraphile will update your API as we add
tables and types throughout this tutorial.
启动 PostGraphile 时,需要通过 --schema 参数指定刚才创建的 schema 名称,例如 postgraphile --schema forum_example。同时别忘了加上 --watch 标志,开启监听模式后,PostGraphile 会在你跟着教程陆续添加表和类型时自动刷新 API。
The Person Table创建 Person 表
Now we are going to create the tables in our database which will correspond to
our users. We will do this by running the Postgres
CREATE TABLE
command. Here is the definition for our person table:
接下来,我们要在数据库中创建用于存储用户信息的表。这通过 Postgres 的 CREATE TABLE 命令完成。以下是 person 表的定义:
create table forum_example.person (
id serial primary key,
first_name text not null check (char_length(first_name) < 80),
last_name text check (char_length(last_name) < 80),
about text,
created_at timestamp default now()
);
Now we have created a table with id, first_name, last_name, about, and
created_at columns (we will add an updated_at column later). Let’s break
down exactly what each line in this command does, we will only do this once. If
you already understand, you can skip ahead.
这样我们就创建了一张包含 id、first_name、last_name、about 和 created_at 列的表(稍后还会添加 updated_at 列)。下面我们逐行拆解这条建表语句的作用(仅此一次详解,若已熟悉可直接跳过):
-
create table forum_example.person: This tells Postgres that we are creating a table in theforum_exampleschema namedperson. This table will represent all of our forum’s users.create table forum_example.person:告诉 Postgres 在forum_exampleschema 下创建一张名为person的表,用于表示论坛的所有用户。 -
id serial primary key: This line establishes an auto-incrementing id field which is always guaranteed to be unique. The first person we create will have an id of 1, the second user will have an id of 2, and so on. Theprimary keybit is also very important. PostGraphile will use theprimary keyof a table in many places to uniquely identify an object, including the globally unique id field.id serial primary key:定义一个自增且保证唯一的 ID 字段。创建第一位用户时 ID 为 1,第二位为 2,依此类推。primary key(主键)也非常关键——PostGraphile 会在多处依赖表的主键来唯一标识对象,包括生成全局唯一的 id 字段。 -
first_name text not null check (char_length(first_name) < 80): We want all of our users to enter their first name and last name separately, so this column definition will create a column namedfirst_name, of typetext, that is required (not null), and that must be less than 80 characters long (check (char_length(first_name) < 80)). Check constraints are a very powerful feature in Postgres for data validation.first_name text not null check (char_length(first_name) < 80):为了分别记录用户的名和姓,这里创建了一个名为first_name、text类型的列,设为必填(not null),且长度必须小于 80 个字符(check (char_length(first_name) < 80))。检查约束(Check constraints)是 Postgres 中非常强大的数据校验特性。 -
last_name text check (char_length(last_name) < 80): This is very similar to our column definition forfirst_name, except it is missingnot null. This means that unlike thefirst_namecolumn,last_nameis not required.last_name text check (char_length(last_name) < 80):与first_name的定义非常相似,只是去掉了not null,表示last_name是可选字段,并非必填。 -
about text: We want users to be able to express themselves! So they get to write a mini forum post which will go on their profile page.about text:让用户自由介绍自己!他们可以在个人主页上写一段简短的个人简介。 -
created_at timestamp default now(): This final column definition will provide us with some extra meta-information about their user. If not specified explicitly, thecreated_attimestamp will default to the time the row was inserted.created_at timestamp default now():最后一列用于记录用户的元数据信息。如果插入数据时未显式指定,created_at时间戳将默认设置为该行记录插入的时间。
And that’s our person table! Pretty simple, right?
这就是我们的 person 表!相当简单直观吧?
The syntax and features of the Postgres
CREATE TABLE
command are fairly easy to learn and understand. Creating tables is the easiest,
but also the most fundamental part of your schema design.
Postgres CREATE TABLE 命令的语法和特性都很容易上手。创建数据表是 schema 设计中最基础、也最容易掌握的一步。
We prefer singular identifiers like forum_example.person over
forum_example.people because when you create a table, it is like you are
creating a class in an object-oriented language. Classes have singular names
like “Person” while collections will often have plural names like “People.”
Table as a class is a better analogy than table as a collection because
Postgres itself will internally call tables “classes.”
相比 forum_example.people,我们更推荐像 forum_example.person 这样的单数命名方式。因为创建一张表就如同在面向对象语言中定义一个类:类通常使用单数名称(如“Person”),而集合才会用复数名称(如“People”)。把表类比为“类”比类比为“集合”更贴切——事实上,Postgres 在底层内部也确实将表称为“class”。
In case you don’t like serial id of our table above, an alternative
to the serial primary key is UUIDs. To use UUIDs you would just need to add
the popular UUID extension, uuid-ossp, in your database setup, and specify a
default in your table creation. Like so:
如果你不喜欢上面用到的自增 serial 主键,也可以改用 UUID。只需在数据库中启用常用的 uuid-ossp 扩展,并在建表时指定默认值即可,如下所示:
create extension if not exists "uuid-ossp";
create table forum_example.person (
id uuid primary key default uuid_generate_v1mc(),
...
);
Alternatively you could use fully random UUIDs:
或者,也可以使用完全随机的 UUID:
create extension if not exists "pgcrypto";
create table forum_example.person (
id uuid primary key default gen_random_uuid(),
...
);
There are pros and cons to both approaches, choose what works best for your application!
这两种方式各有优劣,请根据实际应用需求自行选择!
Table Documentation表与列的注释文档
Now that we have created our table, we want to document it within the Postgres
database. By adding comments to our table and its columns using the Postgres
COMMENT
command, we will allow tools like PostGraphile to display rich domain specific
documentation.
表建好之后,我们还可以在 Postgres 数据库内部为其编写文档。通过 COMMENT 命令为表和列添加注释,PostGraphile 等工具就能直接读取并展示丰富的业务领域说明。
To add comments, just see the SQL below:
添加注释的 SQL 如下所示:
comment on table forum_example.person is 'A user of the forum.';
comment on column forum_example.person.id is 'The primary unique identifier for the person.';
comment on column forum_example.person.first_name is 'The person’s first name.';
comment on column forum_example.person.last_name is 'The person’s last name.';
comment on column forum_example.person.about is 'A short description about the user, written by the user.';
comment on column forum_example.person.created_at is 'The time this person was created.';
Incredibly simple, yet also incredibly powerful.
做法极其简单,效果却非常强大。
Feel free to write your comments in Markdown! Most tools, including GraphiQL which PostGraphile uses, will render your comments with the appropriate styles.
注释中还可以直接使用 Markdown 语法!大多数工具(包括 PostGraphile 使用的 GraphiQL)都会自动按对应的样式将其渲染出来。
With this we have completed our person table, now let’s create a table for our forum posts.
至此,person 表就全部完成了。接下来,我们为论坛帖子创建一张表。
The Post Table创建 Post 表
The users of our forum will want to be able to create posts. That’s the entire
reason we have a forum after all. To create the post table we go through a very
similar process as creating our forum_example.person table, but first we want
to create a type we will use in one of the columns. See the SQL below:
论坛的核心功能当然是让用户发表帖子。创建 post 表的过程与前面创建 forum_example.person 表非常类似,但在此之前,我们需要先定义一个自定义类型,供其中一列使用。SQL 如下:
create type forum_example.post_topic as enum (
'discussion',
'inspiration',
'help',
'showcase'
);
The Postgres
CREATE TYPE
command will let you create a custom type in your database which will allow you
to do some really cool things. You can create a
composite type
which is basically a typed object in GraphQL terms, you can create a
range type
which represents exactly what you might think, or you can create an
enum type
which is what we did here.
Postgres 的 CREATE TYPE 命令允许你在数据库中创建自定义类型,从而实现许多强大的能力:你可以创建复合类型(composite type)(在 GraphQL 中基本等价于强类型对象),可以创建顾名思义的范围类型(range type),也可以像上面那样创建枚举类型(enum type)。
Enum types are a static set of values, you must use one of the string values that make up the enum in any column of the enum’s type. Having this type is useful for us, because we want our forum posts to have one, or none, topics so user’s may easily see what a post is about.
枚举类型是一组固定的静态取值集合,任何使用该枚举类型的列,其值都必须是枚举定义中的某个字符串。这种类型在这里非常实用——我们希望每篇论坛帖子可以归属于某一个主题(或不归属任何主题),方便用户一眼看出帖子的讨论方向。
PostGraphile implements custom handling for user-defined types. An enum type like that defined above will be turned into a GraphQL enum that looks like:
PostGraphile 对用户自定义类型做了专门处理。上述枚举类型会被转换为如下的 GraphQL 枚举:
enum PostTopic {
DISCUSSION
INSPIRATION
HELP
SHOWCASE
}
You can also create custom composite types which will turn into GraphQL object types with PostGraphile.
你也可以创建自定义复合类型,PostGraphile 会将其转换为 GraphQL 对象类型。
create type my_schema.my_type as (
foo integer,
bar integer
);
Would become the following GraphQL type:
它将会生成如下 GraphQL 类型:
type MyType {
foo: Int
bar: Int
}
Now it is time to actually create our post table:
现在正式开始创建 post 表:
create table forum_example.post (
id serial primary key,
author_id integer not null references forum_example.person(id),
headline text not null check (char_length(headline) < 280),
body text,
topic forum_example.post_topic,
created_at timestamp default now()
);
comment on table forum_example.post is 'A forum post written by a user.';
comment on column forum_example.post.id is 'The primary key for the post.';
comment on column forum_example.post.headline is 'The title written by the user.';
comment on column forum_example.post.author_id is 'The id of the author user.';
comment on column forum_example.post.topic is 'The topic this has been posted in.';
comment on column forum_example.post.body is 'The main body text of our post.';
comment on column forum_example.post.created_at is 'The time this post was created.';
Pretty basic. Our headline is twice as long as a tweet, and to use our
forum_example.post_topic type we wrote it as the column type just as we may
write integer as the column type. We also made sure to include comments.
同样简洁明了。headline 的最大长度设为一条推文的两倍;在引用 forum_example.post_topic 类型时,只需像使用 integer 一样直接把它写成列类型即可。同时我们也别忘了加上完整的表和列注释。
Now that we have gone over the basics, let’s explore Postgres functions and see how we can use them to extend the functionality of our database.
掌握了建表与类型定义的基础知识后,接下来我们来探索 Postgres 函数,看看如何利用它们扩展数据库的能力。
Database Functions数据库函数
The Postgres
CREATE FUNCTION
command is truly amazing. It allows us to write functions for our database in
SQL, and other languages including JavaScript and Ruby!
Postgres 的 CREATE FUNCTION 命令非常强大。它不仅支持使用 SQL 编写数据库函数,还能用 JavaScript、Ruby 等其他语言来编写!
The following is a basic Postgres function:
下面是一个最基础的 Postgres 函数示例:
create function add(a int, b int) returns int as $$
select a + b
$$ language sql stable;
Note the form. The double dollar signs ($$) open and close the function, and
at the very end we have language sql stable. language sql means that the
function is written in SQL, pretty obvious. If you wrote your function in Ruby
it may be language plruby. The next word, stable, means that this function
does not mutate the database. By default Postgres assumes all functions will
mutate the database, you must mark your function with stable for Postgres, and
PostGraphile, to know your function is a query and not a mutation.
注意函数的书写格式:一对双美元符号($$)包裹着函数体,末尾则紧跟 language sql stable。顾名思义,language sql 表示该函数使用 SQL 编写;如果用 Ruby 编写,则可能是 language plruby。后面的 stable 表示该函数不会修改数据库状态。Postgres 默认所有函数都会修改数据库,因此必须显式标注 stable,这样 Postgres 和 PostGraphile 才能识别出它是一个只读查询(Query),而非变更操作(Mutation)。
If you are interested in running JavaScript or Ruby in Postgres, check out PL/V8 and PL/ruby respectively. It is recommended that you use SQL and PL/pgSQL (which comes native with Postgres) whenever you can (even if they are a pain). There is plenty of documentation and StackOverflow answers on both SQL and PL/pgSQL. However, there are alternatives if you so choose.
That function above isn’t so useful for us in our schema, so let’s write some functions which will be useful. We will define three.
上面那个加法函数对我们的业务 schema 没什么实际用途,下面我们来编写三个真正有用的函数。
First, a function which will concatenate the users first and last name to return their full name:
第一个函数用于将用户的名和姓拼接起来,返回完整姓名:
create function forum_example.person_full_name(person forum_example.person) returns text as $$
select person.first_name || ' ' || person.last_name
$$ language sql stable;
comment on function forum_example.person_full_name(forum_example.person) is 'A person’s full name which is a concatenation of their first and last name.';
Second, a function which will get a summary of a forum post:
第二个函数用于截取并生成论坛帖子的摘要:
create function forum_example.post_summary(
post forum_example.post,
length int default 50,
omission text default '…'
) returns text as $$
select case
when post.body is null then null
else substr(post.body, 0, length) || omission
end
$$ language sql stable;
comment on function forum_example.post_summary(forum_example.post, int, text) is 'A truncated version of the body for summaries.';
Third, a function that will get a person’s most recent forum post.
第三个函数用于获取某个用户发表的最新一篇帖子。
create function forum_example.person_latest_post(person forum_example.person) returns forum_example.post as $$
select post.*
from forum_example.post as post
where post.author_id = person.id
order by created_at desc
limit 1
$$ language sql stable;
comment on function forum_example.person_latest_post(forum_example.person) is 'Get’s the latest post written by the person.';
Don’t get too stuck on the function implementations. It is fairly easy to
discover how to express what you want in SQL through a quick search of the
Postgres documentation (which is excellent!). These functions are here to give
you some examples of what functions in Postgres look like. Also note how we
added comments to our functions with the
COMMENT
command, just like we add comments to our tables.
不必过于纠结这些函数的具体 SQL 写法,平时只要翻一翻质量极高的 Postgres 官方文档,就能很快查到所需逻辑该如何用 SQL 表达。这里的示例主要是为了直观展示 Postgres 函数的结构。另外请注意,我们同样用 COMMENT 命令为函数添加了注释,做法与给表加注释完全一致。
Any function which meets the following conditions will be treated as a computed field by PostGraphile:
凡是同时满足以下条件的函数,都会被 PostGraphile 识别为计算字段(computed field):
-
The function has a table row as the first argument.
函数的第一个参数是某张表的行类型。
-
The function is in the same schema as the table of the first argument.
函数与第一个参数对应的表定义在同一个 schema 中。
-
The function’s name is prefixed by the table’s name.
函数名以该表的表名作为前缀。
-
The function is marked as
stableorimmutablewhich makes it a query and not a mutation.函数被标记为
stable或immutable,表明它是查询(Query)而非变更操作(Mutation)。
All three of the above functions meet these conditions and as such will be computed fields. In GraphQL this ends up looking like:
上述三个函数都满足这四项条件,因此都会成为计算字段。在最终生成的 GraphQL 中,它们看起来会像这样:
type Person {
id: Int!
firstName: String!
lastName: String
...
fullName: String
latestPost: Post
}
Set Returning Functions返回集合的函数(Set Returning Functions)
Sometimes it is useful to not just return single values from your function, but perhaps entire tables. What returning a table from a function could mean is you could define a custom ordering, hide rows that were archived, or return a user’s activity feed perhaps. In our case, this Postgres feature makes it easy for us to implement search:
有时我们希望函数不仅能返回单个值,还能返回整张表的数据集。借助这一能力,你可以实现自定义排序、隐藏已归档的数据行,或是返回用户的动态信息流等。在本教程的场景中,Postgres 的这一特性让实现搜索功能变得非常简单:
create function forum_example.search_posts(search text) returns setof forum_example.post as $$
select post.*
from forum_example.post as post
where position(search in post.headline) > 0 or position(search in post.body) > 0
$$ language sql stable;
comment on function forum_example.search_posts(text) is 'Returns posts containing a given search term.';
The difference with this function and the ones before is the return signature
reads returns setof forum_example.post. This function will therefore return
all of the posts that match our search condition and not just one.
这个函数与前面几个的区别在于其返回值签名为 returns setof forum_example.post。因此,它会返回所有符合搜索条件的帖子,而不仅仅是一条。
PostGraphile will treat set returning functions as connections. This is what makes them so powerful for PostGraphile users. The function above would be queryable like so:
PostGraphile 会将返回集合的函数处理为 Connection——这正是它们对 PostGraphile 用户而言如此强大的原因。上述函数可以像下面这样进行 GraphQL 查询:
{
searchPosts(search: "Hello, world!", first: 5) {
edges {
cursor
node {
headline
body
}
}
}
}
Postgres has awesome text searching capabilities — if you want high quality full text searching you don’t need to look outside Postgres. Instead look into the Postgres Full Text Search functionality. It is a great feature, but a bit much for our simple example, so we just used a simple string position function instead.
Postgres 内置了极为出色的文本搜索能力——如果需要高质量的全文检索,完全不必引入外部搜索引擎,直接使用 Postgres 的全文搜索(Full Text Search)即可。不过对我们这个简化的示例来说全文搜索有些大材小用了,因此这里只用了简单的字符串位置查找函数代替。
Returning an array (returns post[]), and returning a set
(returns setof post) are two very different things. When you return an
array, every single value in the array will always be returned. However, when
you return a set it is like returning a table. Users can paginate through a
set using limit and offset, but not an array.
返回数组(returns post[])与返回集合(returns setof post)有着本质区别。返回数组时,数组中的所有元素总是会一次性全部返回;而返回集合则类似于返回一张虚拟表,调用方可以使用 limit 和 offset 对集合进行分页,数组却不支持这样做。
Triggers触发器(Triggers)
You can also use Postgres functions to define triggers. Triggers in Postgres
allow you to hook into events that are happening on your tables such as inserts,
updates, or deletes. You define your triggers with the
CREATE TRIGGER
command, and all trigger functions must return the special type trigger.
你还可以利用 Postgres 函数来定义触发器。触发器允许你在表发生插入、更新或删除等事件时挂载自定义逻辑。使用 CREATE TRIGGER 命令即可创建触发器,且所有触发器函数都必须返回特殊类型 trigger。
To demonstrate how triggers work, we will define a trigger that sets an
updated_at column on our forum_example.person and forum_example.post
tables whenever a row is updated. Before we can write the trigger, we need to
make sure forum_example.person and forum_example.post have an updated_at
column! To do this we will use the
ALTER TABLE
command.
为了演示触发器的用法,我们来定义一个在数据行更新时自动维护 forum_example.person 和 forum_example.post 表中 updated_at 列的触发器。编写触发器之前,先得确保这两张表拥有 updated_at 列,这里用 ALTER TABLE 命令来添加:
alter table forum_example.person add column updated_at timestamp default now();
alter table forum_example.post add column updated_at timestamp default now();
Our updated_at column has now been added to our tables and looks exactly like
our created_at column. It’s a timestamp which defaults to the time the row was
created. Next, let us define our triggers:
现在两张表都加上了 updated_at 列,其定义与 created_at 列完全一致——都是默认取行记录创建时间的时间戳。接下来,我们来定义触发器函数及触发器:
create function forum_example_private.set_updated_at() returns trigger as $$
begin
new.updated_at := current_timestamp;
return new;
end;
$$ language plpgsql;
create trigger person_updated_at before update
on forum_example.person
for each row
execute procedure forum_example_private.set_updated_at();
create trigger post_updated_at before update
on forum_example.post
for each row
execute procedure forum_example_private.set_updated_at();
To define our trigger we ran three commands. First we created a function named
set_updated_at in our forum_example_private schema because we want no one to
directly call this function as it is simply a utility.
forum_example_private.set_updated_at also returns a trigger and is
implemented in
PL/pgSQL.
上面一共执行了三条语句来配置触发器。首先,我们在 forum_example_private schema 中创建了名为 set_updated_at 的函数——因为它只是一个内部工具函数,不希望被外部直接调用。forum_example_private.set_updated_at 的返回类型为 trigger,采用 PL/pgSQL 编写。
After we define our forum_example_private.set_updated_at function, we can use
it in the triggers we create with the
CREATE TRIGGER
command. The triggers will run before a row is updated by the
UPDATE
command and will execute the function on every row being updated.
定义好 forum_example_private.set_updated_at 函数后,就可以通过 CREATE TRIGGER 命令将其绑定到触发器上。当 UPDATE 命令更新数据时,触发器会在每行记录正式写入更新前触发执行该函数。
If you find yourself wanting to do CPU intensive work in triggers,
instead consider using Postgres’ pub/sub functionality
(LISTEN /
NOTIFY) to
send the work to a "worker service" to be executed asynchronously.
Graphile Worker uses this pattern in a
fail-safe way; allowing you to run jobs "in the background" so that your HTTP
response/application code is not held up. We recommend using Graphile Worker
with any Node.js based PostgreSQL database that needs to queue actions such as
sending emails, push notifications, generating PDF reports and other such
asynchronous tasks.
如果你打算在触发器中执行 CPU 密集型任务,建议改用 Postgres 的发布/订阅机制(LISTEN / NOTIFY),把任务分发给“worker 服务”异步执行。Graphile Worker 正是以容错可靠的方式实现了这一方案,让你能够将任务放到后台运行,从而避免阻塞 HTTP 响应或应用主流程。对于任何基于 Node.js 和 PostgreSQL、需要排队执行发送邮件、推送通知、生成 PDF 报表等异步任务的项目,我们都推荐使用 Graphile Worker。
That’s about it as far as Postgres functions go! They are a fun, interesting, and useful topic to understand when it comes to good Postgres schema design. Always remember, the Postgres documentation is your best friend as you try to write your own functions. Some important documentation articles we mentioned for your reference are as follows:
关于 Postgres 函数的介绍就先到这里!在高质量的 Postgres schema 设计中,函数是一个既有趣又极具实用价值的主题。在自己动手编写函数时,别忘了 Postgres 官方文档永远是最好的帮手。以下是前文提到的几篇重要参考文档:
Next up, we are going to learn about auth in Postgres and PostGraphile!
接下来,我们将学习 Postgres 与 PostGraphile 中的身份认证与权限控制机制!
Authentication and Authorization身份认证与权限控制
Authentication and authorization is incredibly important whenever you build an application. You want your users to be able to login and out of your service, and only edit the content your platform has given them permission to edit. Postgres already has great support for authentication and authorization using a secure role based system, so PostGraphile just bridges the gap between the Postgres role mechanisms and HTTP based authorization.
在开发应用时,身份认证与权限控制至关重要。你既希望用户能够正常登录和退出系统,又需要确保他们只能修改自己有权限操作的内容。Postgres 本身已经基于安全的角色体系提供了完善的认证与鉴权支持,而 PostGraphile 的职责就是打通 Postgres 角色机制与 HTTP 鉴权之间的桥梁。
However, before we can dive into implementing authentication, we are missing some pretty important data in our schema. How are users supposed to even login? Not by guessing their first and last name one would hope, so we will define another table which will store user emails and passwords.
不过,在动手实现认证逻辑之前,我们的 schema 还缺了一块关键数据。总不能靠猜用户的姓名来登录吧?因此,我们还需要定义另一张表来存储用户的邮箱和密码。
Storing Emails and Passwords存储邮箱与密码
To store user emails and passwords we will create another table in the
forum_example_private schema.
为了保存用户的邮箱和密码,我们在 forum_example_private schema 中创建另一张表:
create table forum_example_private.person_account (
person_id integer primary key references forum_example.person(id) on delete cascade,
email text not null unique check (email ~* '^.+@.+\..+$'),
password_hash text not null
);
comment on table forum_example_private.person_account is 'Private information about a person’s account.';
comment on column forum_example_private.person_account.person_id is 'The id of the person associated with this account.';
comment on column forum_example_private.person_account.email is 'The email address of the person.';
comment on column forum_example_private.person_account.password_hash is 'An opaque hash of the person’s password.';
Never store passwords in plaintext! The password_hash column
will contain the user’s password after it has gone through a secure hashing
algorithm like Bcrypt.
Later in this tutorial we will show you how to securely hash a password in
Postgres.
切勿以明文形式存储密码!password_hash 列中保存的是用户密码经过安全哈希算法(例如 Bcrypt)处理之后的摘要。稍后我们会在本教程中演示如何在 Postgres 内安全地计算密码哈希。
Why would we choose to create a new table in the forum_example_private schema
instead of just adding columns to forum_example.person? There are a couple of
answers to this question. The first and most fundamental is separation of
concerns. By moving email and password_hash to a second table we make it
much harder to accidentally select those values when reading
forum_example.person. Also, users will not have the permission to directly
query data from forum_example_private (as we will see) making this approach
more secure. This approach is also good for PostGraphile as the
forum_example_private schema is never exposed in PostGraphile, so you will
never accidentally expose password hashes in GraphQL.
为什么我们要在 forum_example_private schema 中新建一张表,而不直接把字段加到 forum_example.person 表里?原因有几个。首要且最根本的一点是关注点分离:把 email 和 password_hash 拆到另一张表后,读取 forum_example.person 时就不会轻易误查出这些敏感字段。此外,稍后你会看到,普通用户根本没有直接查询 forum_example_private schema 的权限,从而进一步提高了安全性。这对 PostGraphile 同样十分友好——因为 forum_example_private schema 不会被暴露给 PostGraphile,也就从根源上杜绝了在 GraphQL 中意外泄露密码哈希的风险。
Besides those arguments, moving the person’s account to a separate table is also good database design in general. Say you have multiple types of users. Perhaps normal person users, and then ’brand‘ or ‘organization’ users. This pattern could easily allow you to go in that direction.
除此之外,把账户信息拆分到独立的数据表本身也是良好的数据库设计实践。假如未来系统需要支持多种用户类型(例如个人用户、品牌或组织用户),这种结构可以非常平滑地支撑这类扩展。
The forum_example_private.person_account shares its primary key
with forum_example.person. This way there can only be one
forum_example_private.person_account for every forum_example.person, a
one-to-one relationship.
forum_example_private.person_account 与 forum_example.person 共享主键,这样每条 forum_example.person 记录至多对应一条 forum_example_private.person_account 记录,形成严格的一对一关联。
For an example of a much richer user profile/account/login schema, use Membership.db as a reference.
若想参考更完善的用户资料、账户及登录体系 schema 设计,可以查阅 Membership.db。
Registering Users用户注册
Before a user can log in, they need to have an account in our database. To
register a user we are going to implement a Postgres function in PL/pgSQL which
will create two rows. The first row will be the user’s profile inserted into
forum_example.person, and the second will be an account inserted into
forum_example_private.person_account.
用户在登录之前,必须先在数据库中拥有账户。为此,我们将使用 PL/pgSQL 编写一个注册用的 Postgres 函数,一次性插入两条记录:第一条是将用户的个人资料写入 forum_example.person,第二条是将账户凭证写入 forum_example_private.person_account。
Before we define the function, we know that we will want to hash the passwords
coming into the function before inserting them into
forum_example_private.person_account. To hash passwords we will need the
Postgres
pgcrypto
extension. To add the extension, just do the following:
在定义注册函数之前,我们需要先对传入的密码做哈希处理,再写入 forum_example_private.person_account。这要用到 Postgres 的 pgcrypto 扩展,执行以下命令即可启用:
create extension if not exists "pgcrypto";
The pgcrypto extension should come with your Postgres distribution and gives
us access to hashing functions like crypt and gen_salt which were
specifically designed for hashing passwords.
pgcrypto 扩展通常会随 Postgres 发行版一同提供,其中包含了专为密码哈希设计的 crypt 和 gen_salt 等函数。
Now that we have added pgcrypto to our database, let us define our function:
在数据库中启用 pgcrypto 后,现在就可以定义注册函数了:
create function forum_example.register_person(
first_name text,
last_name text,
email text,
password text
) returns forum_example.person as $$
declare
person forum_example.person;
begin
insert into forum_example.person (first_name, last_name) values
(first_name, last_name)
returning * into person;
insert into forum_example_private.person_account (person_id, email, password_hash) values
(person.id, email, crypt(password, gen_salt('bf')));
return person;
end;
$$ language plpgsql strict security definer;
comment on function forum_example.register_person(text, text, text, text) is 'Registers a single user and creates an account in our forum.';
If you do not understand what is going on here, do not worry, writing PL/pgSQL
requires some trial and error along with some StackOverflow searching. What’s
new here compared to our other functions is that we have a new block, declare,
above our function implementation which starts with begin. In that block we
declare our intention to use a variable called person of type
forum_example.person. Then, in our first insert statement, the row we insert
will be saved into that person variable.
如果对这里的语法一时不太理解也没关系,编写 PL/pgSQL 本身就需要不断尝试并查阅资料。与前面几个函数相比,这里在 begin 函数体之前多了一个 declare 块:我们在其中声明了一个类型为 forum_example.person 的变量 person,随后在第一条插入语句中将新插入的记录保存到该 person 变量里。
After we insert a profile into forum_example.person, we use the pgcrypto
extension in the expression crypt(password, gen_salt('bf')) to hash the user’s
password before inserting into forum_example_private.person_account. This way
we aren’t storing the password in plaintext. Read the documentation for
pgcrypto on
Password Hashing Functions
to learn more about these functions and their characteristics.
向 forum_example.person 插入个人资料后,我们通过 pgcrypto 提供的 crypt(password, gen_salt('bf')) 对密码进行哈希运算,然后再写入 forum_example_private.person_account,从而避免明文存库。想深入理解这些密码哈希函数及其特性,可以参阅 pgcrypto 文档中的密码哈希函数(Password Hashing Functions)章节。
Be very careful with logging, while we encrypt our passwords here it may be possible that in a query or server log the password will be recorded in plain text! Be careful to configure your Postgres logs so this isn’t the case. PostGraphile will never log the value of any variables the client gives it. Being careful with your logs and passwords is true in any system, but especially this one.
请务必留意日志安全:虽然我们在这里对密码进行了哈希处理,但查询日志或服务器日志仍有可能把请求中的明文密码记录下来!请谨慎配置 Postgres 的日志策略以防泄露。PostGraphile 本身绝不会记录客户端传入的任何变量值。在任何系统中处理密码与日志都应格外谨慎,这套架构下尤甚。
For an overview of passwords in Postgres past the pgcrypto documentation,
see the answer to the StackOverflow question
“How can I hash passwords in Postgres?”
除了 pgcrypto 文档之外,关于 Postgres 密码处理的更多背景,还可以参考 StackOverflow 上的高赞回答“How can I hash passwords in Postgres?”。
At the end of the implementation you will see
language plpgsql strict security definer. language plpgsql we already
understand, but the other words are new. The word strict means that if the
function gets null input, then the output will be automatically null as well and
Postgres won’t call the function. That is password cannot be null or
first_name cannot be null otherwise the result will also be null and nothing
will be executed. The words security definer mean that this function is
executed with the privileges of the Postgres user who created it. Remember how
we said users would never be able to insert into
forum_example_private.person_account? Well this function can insert into
forum_example_private.person_account because it uses the privileges of the
definer.
函数定义末尾出现了 language plpgsql strict security definer。其中 language plpgsql 我们已经很熟悉了,另外两个修饰词则是新面孔:strict 表示只要传入的任意参数为 null,Postgres 就不会真正调用函数体,而是直接返回 null——也就是说 password 或 first_name 均不可为 null,否则什么都不会执行并直接返回 null。security definer 则表示该函数将以**创建者(definer)**而非调用者的 Postgres 权限来执行。还记得前面说过普通用户无法直接向 forum_example_private.person_account 插入数据吗?该函数之所以能够完成写入,正是因为它借用了函数定义者的权限。
Make sure that when you create a function with security definer
there are no ‘holes’ a user could use to see or mutate more data than they are
not allowed to. Since the above is a simple function, we are fine. If you
don’t need security definer, try not to use it.
创建带有 security definer 的函数时,务必仔细审查是否存在安全漏洞,防止用户借此越权读取或修改本不应访问的数据。上面这个函数逻辑非常简单,因此是安全的;但在其他场景下,只要不是必需,就尽量不要使用 security definer。
This function will create a user and their account, but how will we log the user in? Before we define a function which allows users to login, sign-in, authenticate, whatever you want to call it let us go over how auth works at a high level in PostGraphile. While this article is trying to be somewhat PostGraphile agnostic, the next two sections will be specific to PostGraphile, but useful to anyone wanting to learn just a little bit more about Postgres and JSON Web Tokens (JWTs).
有了注册函数,用户已经能创建账户了,但接下来该如何实现登录呢?在编写登录认证函数之前,我们先从宏观上梳理一下 PostGraphile 的认证机制。尽管本文力求尽量保持与具体框架解耦,但接下来的两节内容会具体针对 PostGraphile 展开——不过对于所有想进一步了解 Postgres 与 JSON Web Token(JWT)的读者而言,同样颇具参考价值。
Postgres RolesPostgres 角色(Roles)
When a user logs in, we want them to make their queries using a specific
PostGraphile role. Using that role we can define rules that restrict what data
the user may access. So what roles do we need to define for our forum example?
Remember when we were connecting to Postgres and we used a URL like
postgres:///mydb? Well, when you use a connection string like that, you are
logging into Postgres using your computer account’s username and no password.
Say your computer account username is buddy, then connecting with the URL
postgres:///mydb, would be the same as connecting with the URL
postgres://buddy@localhost/mydb or even specifying the port explicitly:
postgres://buddy@localhost:5432/mydb. If you wanted to connect to your
Postgres database with a password it would look like
postgres://buddy:password@localhost/mydb. When you run Postgres locally, this
account will probably be the superuser. So when you run
postgraphile -c postgres:///mydb, you are running PostGraphile with superuser
privileges. To change that let’s create a role that PostGraphile can use to
connect to our database:
当用户登录后,我们希望其后续查询以特定的 Postgres 角色身份运行,从而利用角色规则来限制用户能访问的数据范围。那么在这个论坛示例中,我们需要定义哪些角色呢?还记得前面连接 Postgres 时使用的 postgres:///mydb 吗?使用这种连接字符串时,Postgres 会默认采用当前系统账户名进行免密登录。假设你的系统用户名是 buddy,那么 postgres:///mydb 就等价于 postgres://buddy@localhost/mydb,或者显式指定端口的 postgres://buddy@localhost:5432/mydb;若需要密码,则是 postgres://buddy:password@localhost/mydb。在本地运行 Postgres 时,这个默认账户往往就是超级用户(superuser),因此直接运行 postgraphile -c postgres:///mydb 相当于让 PostGraphile 以超级用户权限连库。为了改变这种高权限状态,我们先创建一个专门供 PostGraphile 连接数据库使用的角色:
create role forum_example_postgraphile login password 'xyz';
We create this forum_example_postgraphile role with the
CREATE ROLE
command. We want to make sure our PostGraphile role can login so we specify that
with the login option and we give the user a password of ‘xyz’ with the
password option. Now we will start PostGraphile as such:
这里使用 CREATE ROLE 命令创建了 forum_example_postgraphile 角色。由于该角色需要能够登录数据库,我们加上了 login 选项,并通过 password 选项将其密码设为“xyz”。现在就可以这样启动 PostGraphile:
postgraphile -c postgres://forum_example_postgraphile:xyz@localhost/mydb
When a user who does not have a JWT token makes a request to Postgres, we do not
want that user to have the privileges we will give to the
forum_example_postgraphile role, so instead we will create another role.
当未携带 JWT 的匿名用户发起请求时,我们不希望他们拥有 forum_example_postgraphile 角色本身的权限,因此需要再创建一个匿名角色:
create role forum_example_anonymous;
grant forum_example_anonymous to forum_example_postgraphile;
Here we use
CREATE ROLE
again. This role cannot login so it does not have the login option, or a
password. We also use the
GRANT command
to grant access to the forum_example_anonymous role to the
forum_example_postgraphile role. Now, the forum_example_postgraphile role
can control and become the forum_example_anonymous role. If we did not use
that grant, we could not change into the forum_example_anonymous role in
PostGraphile.
这里再次使用了 CREATE ROLE。由于该角色不需要直接登录数据库,因此没有设置 login 选项和密码。同时,我们通过 GRANT 命令将 forum_example_anonymous 角色授予 forum_example_postgraphile。这样一来,forum_example_postgraphile 就能够切换并扮演 forum_example_anonymous 角色;如果漏掉这步授权,PostGraphile 在执行请求时将无法切换到 forum_example_anonymous。
To use this role, we could put the following into our config file:
要在 PostGraphile 中启用这个默认角色,可以在配置文件中加入如下设置:
import { PostGraphileAmberPreset } from "postgraphile/presets/amber";
export default {
extends: [PostGraphileAmberPreset],
grafast: {
context(requestContext, args) {
return {
pgSettings: {
role: "forum_example_anonymous",
},
};
},
},
};
We can still start our server as before:
之后依然像之前一样启动服务即可:
postgraphile \
--connection postgres://forum_example_postgraphile:xyz@localhost/mydb
There is one more role we want to create. When a user logs in we don’t want them
to use the forum_example_postgraphile role, or the basic
forum_example_anonymous role. So instead we will create a role that all of our
logged in users will authorize with. We will call it forum_example_person and
similarly grant it to the forum_example_postgraphile role.
最后我们还需要创建一个角色。用户登录后,既不应该继续使用 forum_example_postgraphile,也不该停留在基础的 forum_example_anonymous 角色上。因此,我们需要为所有已登录用户单独创建一个用于鉴权的角色 forum_example_person,并同样将其授予 forum_example_postgraphile。
create role forum_example_person;
grant forum_example_person to forum_example_postgraphile;
The forum_example_postgraphile role will have all of the
permissions of the roles granted to it. So it can do everything
forum_example_anonymous can do and everything forum_example_person can do.
This is why having a default role is important. We would not want an anonymous
user to have admin access level because we have granted an admin role to
forum_example_postgraphile.
forum_example_postgraphile 会继承所有授予它的角色的权限,也就是说它既能执行 forum_example_anonymous 的所有操作,也能执行 forum_example_person 的所有操作。正因如此,显式配置默认角色显得尤为重要——否则一旦某个管理员角色也授予了 forum_example_postgraphile,未配置默认角色的匿名请求就会直接拥有管理员权限。
Ok, so now we have three roles. forum_example_postgraphile,
forum_example_anonymous, and forum_example_person. We know how
forum_example_postgraphile and forum_example_anonymous get used, but how do
we know when a user is logged in and should be using forum_example_person? The
answer is JSON Web Tokens.
至此,我们已经拥有了三个角色:forum_example_postgraphile、forum_example_anonymous 和 forum_example_person。前两个角色的用途已经明确,但系统如何判断用户处于已登录状态并应切换到 forum_example_person 呢?答案就是 JSON Web Token(JWT)。
JSON Web TokensJSON Web Token(JWT)令牌
PostGraphile uses JSON Web Tokens (JWTs) for authorization. A JWT is just a JSON object that has been hashed and cryptographically signed to confirm the identity of its contents. So an object like:
PostGraphile 使用 JSON Web Token(JWT) 进行身份鉴权。JWT 本质上是一个经过哈希和加密签名、用以验证内容来源可信度的 JSON 对象。例如下面这样一个对象:
{
"a": 1,
"b": 2,
"c": 3
}
Would turn into a token that looks like:
会被编码并签名成如下形式的令牌(token):
eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJhIjoxLCJiIjoyLCJjIjozfQ.hxhGCCCmGV9nT1slief1WgEsOsfdnlVizNrODxfh1M8
The information in a JWT can be read by anyone, so do not put private information in a JWT. What makes JWTs secure is that unless they were signed by our secret, we can not accept the information inside the JWT as truth.
任何人都能解码并读取 JWT 中携带的信息,因此切勿在 JWT 中存放私密数据。JWT 之所以安全,是因为只有使用我们的密钥(secret)签名的令牌,服务端才会采信其中的声明内容。
This allows PostGraphile to securely make claims about who a user is. Attackers
would not be able to fake a claim unless they had access to the private ‘secret’
you define via preset.schema.pgJwtSecret in your configuration.
这使得 PostGraphile 能够安全地确认当前用户身份。只要你在配置中通过 preset.schema.pgJwtSecret 设置的私有密钥没有泄露,攻击者就无法伪造其中的声明(claim)。
When PostGraphile gets a JWT from an HTTP request’s Authorization header, like
so:
当 PostGraphile 从 HTTP 请求的 Authorization 请求头中收到如下 JWT 时:
Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJhIjoxLCJiIjoyLCJjIjozfQ.hxhGCCCmGV9nT1slief1WgEsOsfdnlVizNrODxfh1M8
It will verify the token using the secret, and then will serialize the claims in that token to the database. So for our token above PostGraphile would effectively run:
PostGraphile 会先使用密钥校验该令牌,然后将其中的声明序列化并传入当前数据库事务中。以上述令牌为例,PostGraphile 实际执行的效果相当于运行:
set local jwt.claims.a to 1;
set local jwt.claims.b to 2;
set local jwt.claims.c to 3;
This way your JWT is accessible in your database rules. To get these values back out in SQL, just run the following function:
这样一来,你的数据库权限规则就能直接读取 JWT 中的信息了。在 SQL 中获取这些值,只需调用如下函数:
select current_setting('jwt.claims.a', true);
All of the ‘claims’ or properties on the JWT are serialized to the database in
this way, with one exception. If you have a role property in your JWT,
PostGraphile will also set the Postgres role of the local transaction. So say
you had a role of forum_example_person. PostGraphile would run:
JWT 中的所有“声明(claims)”或属性都会按这种方式写入当前事务的上下文配置中,但有一个属性例外:如果 JWT 中包含 role 属性,PostGraphile 还会同步设置当前本地事务的 Postgres 角色。例如当 role 为 forum_example_person 时,PostGraphile 会执行:
set local role to 'forum_example_person';
set local jwt.claims.role to 'forum_example_person';
Now, the user would have the permissions of the forum_example_person role as
they execute their query.
如此一来,用户在执行本次查询时便拥有了 forum_example_person 角色的全部权限。
Unless explicitly set, JWTs never expire. Once they have been
issued they may never be invalidated. This is both good and bad, good in that
JWTs are fast in not requiring a database lookup. Bad in that if an attacker
gets their hands on a JWT you can’t stop them from using it until the token
expires. If you do not override exp then PostGraphile's defaults set JWTs
to expire after one day.
从 JWT 协议本身来说,除非显式设置过期时间,否则令牌一旦签发便永不过期且无法主动吊销。这既是优点也是隐患:优点在于校验 JWT 无需查库、速度极快;缺点在于一旦令牌落入攻击者手中,在它过期前你无法阻止对方继续使用。如果你没有显式覆盖 exp 字段,PostGraphile 默认会将 JWT 的有效期设为 1 天。
A solution to this is to use very short expiration times on your tokens and/or to use refresh tokens. A refresh token you would use whenever your JWT expires to get a new JWT without prompting the user for their password again. Refresh tokens would be stored in the database so you could easily invalidate refresh tokens.
常见的应对策略是为 JWT 设置很短的有效期,或者配合刷新令牌(refresh token)机制使用:当 JWT 过期时,客户端可通过刷新令牌换取新的 JWT,而无需让用户重新输入密码。刷新令牌保存在数据库中,因此你可以随时方便地将其吊销。
We now know how PostGraphile uses JWTs to authorize the user, but how does PostGraphile create a JWT? Stay tuned.
现在我们已经明白 PostGraphile 是如何利用 JWT 完成鉴权的了,那么 PostGraphile 又是如何签发 JWT 的呢?请继续往下看。
Logging In实现登录
You can specify preset.gather.pgJwtTypes in your configuration which is the
names (schema name and type name) of composite types in your database. When
PostGraphile would expose these types, instead of doing so as an object it does
so as a JWT scalar. So let’s define the type we will use for our JWTs:
你可以在配置中设置 preset.gather.pgJwtTypes,用于指定数据库中作为 JWT 的复合类型名称(需包含 schema 名和类型名)。当 PostGraphile 对外暴露这些类型时,不会将它们生成普通的 GraphQL 对象类型,而是将其映射为一个 JWT 标量类型。下面我们就来定义用于生成 JWT 的复合类型:
create type forum_example.jwt_token as (
role text,
person_id integer,
exp bigint
);
That’s it. We are using the
CREATE TYPE
command again as we did before to create an enum type. This time we are creating
a composite type. The definition for a composite type looks very much like the
definition of a table type, except a composite type cannot store rows. i.e. you
can’t INSERT, SELECT, UPDATE, or DELETE from a composite type. While you
can’t store rows in a composite type, PostGraphile can turn a composite type
into a JWT. Now that we’ve defined this type we will want to specify it in our
preset, along with enabling basic JWT functionality:
就是这么简单。我们再次用到了之前创建枚举类型时的 CREATE TYPE 命令,只不过这次创建的是复合类型。复合类型的定义形式与表非常相似,但它不能用来存储数据行——也就是说,无法对复合类型执行 INSERT、SELECT、UPDATE 或 DELETE 操作。虽然复合类型不存数据,但 PostGraphile 可以将它编码成 JWT。定义好该类型后,我们只需在 preset 中进行配置并开启基础 JWT 功能:
import { PostGraphileAmberPreset } from "postgraphile/presets/amber";
import { PgLazyJWTPreset } from "postgraphile/presets/lazy-jwt";
export default {
extends: [
PostGraphileAmberPreset,
PgLazyJWTPreset,
],
gather: {
pgJwtTypes: "forum_example.jwt_token",
},
schema: {
pgJwtSecret: "keyboard_kitten", // REPLACE THIS!
},
grafast: {
context(requestContext, args) {
return {
pgSettings: {
role: "forum_example_anonymous",
// JWT may override the role:
...args.contextValue?.pgSettings,
},
};
},
},
};
Next we need to create the function which will actually return the token:
接下来,编写实际负责校验凭证并返回令牌的登录函数:
create function forum_example.authenticate(
email text,
password text
) returns forum_example.jwt_token as $$
declare
account forum_example_private.person_account;
begin
select a.* into account
from forum_example_private.person_account as a
where a.email = $1;
if account.password_hash = crypt(password, account.password_hash) then
return ('forum_example_person', account.person_id, extract(epoch from (now() + interval '2 days')))::forum_example.jwt_token;
else
return null;
end if;
end;
$$ language plpgsql strict security definer;
comment on function forum_example.authenticate(text, text) is 'Creates a JWT token that will securely identify a person and give them certain permissions. This token expires in 2 days.';
This function will return null if the user failed to authenticate, and a JWT
token if the user succeeds. Returning null could mean that the password was
incorrect, a user with their email doesn’t exist, or the client forgot to pass
email and/or password arguments. It is then up to the client to raise an
error when encountering null. If a user with the provided email does exist,
and the provided password checks out with password_hash in
forum_example_private.person_account then we return an instance of
forum_example.jwt_token which will then be converted into an actual JWT by
PostGraphile.
认证失败时该函数返回 null,认证成功时则返回 JWT。返回 null 可能对应密码错误、邮箱未注册,或者客户端漏传了 email / password 参数,客户端在收到 null 后只需自行提示错误即可。反之,若邮箱对应的用户确实存在,且传入的密码与 forum_example_private.person_account 中的 password_hash 匹配,函数就会返回一个 forum_example.jwt_token 实例,随后由 PostGraphile 自动将其转换为真正的 JWT。
There are two main parts to our function body. The first is:
函数体主要分为两部分,第一部分是:
select a.* into account
from forum_example_private.person_account as a
where a.email = $1;
This code will select a single account from
forum_example_private.person_account using the provided email value. The $1
here is just another way to write the email argument. If we had wrote
email = email or even a.email = email, Postgres would not have known which
email we were referring to, so instead we just used a substitute for the email
argument which depends on its placement in the identifier $1. If we
successfully find a person with that email, we store it in the account
variable. If we do not find anything, account will be null. The second part of
our function is:
这段查询会根据传入的邮箱从 forum_example_private.person_account 中检索出对应账户。这里的 $1 代表函数的第一个参数 email——如果写成 email = email 甚至 a.email = email,Postgres 会因为列名与参数名冲突而产生歧义,因此改用基于参数位置索引的 $1 来代指 email 参数。如果查到了匹配的记录,就存入 account 变量;若未查到,account 则为 null。函数体的第二部分是:
if account.password_hash = crypt(password, account.password_hash) then
return ('forum_example_person', account.person_id)::forum_example.jwt_token;
else
return null;
end if;
This is an if/else statement that checks to see if the plaintext password
argument we were provided matches the password hash that was stored in our
forum_example_private.person_account’s password_hash table. If there is a
match, then we return a JWT token. Otherwise we return null. The password match
check is done in the code
account.password_hash = crypt(password, account.password_hash). To better
understand how this works, read the documentation for pgcrypto on
password hashing functions.
这是一个 if/else 条件分支,用于校验传入的明文 password 参数是否与 forum_example_private.person_account 表中 password_hash 列保存的哈希值一致。若匹配成功则返回 JWT,否则返回 null。密码比对由 account.password_hash = crypt(password, account.password_hash) 完成,想深入理解其原理可查阅 pgcrypto 文档中的密码哈希函数(password hashing functions)。
In order to construct a forum_example.jwt_token we use the Postgres
composite value input
syntax which looks like:
('forum_example_person', account.person_id, extract(epoch from (now() + interval '2 days'))).
Then we cast that composite value with ::forum_example.jwt_token. We use
Postgres
date/time functions
to generate a date that is two days from the current date, and then convert it
to a POSIX timestamp. The order in which the values go is the order in which
they were originally defined. Since we defined role, person_id and exp,
this JWT will have a role of forum_example_person, a person_id of
account.person_id, and an exp that is two days from now.
为了构造 forum_example.jwt_token 实例,我们使用了 Postgres 的复合类型值输入(composite value input)语法:('forum_example_person', account.person_id, extract(epoch from (now() + interval '2 days'))),随后通过 ::forum_example.jwt_token 将其显式转换为目标类型。这里借助 Postgres 的日期/时间函数计算出两天后的时间并转为 POSIX 时间戳。元组中各值的顺序必须与类型定义时的字段顺序严格一致——由于我们定义的是 role、person_id 和 exp,因此生成的 JWT 将携带 role 为 forum_example_person、person_id 为 account.person_id、过期时间 exp 为两天后。
Be careful about logging around this function too.
调用此函数时同样要注意防范日志泄露敏感信息。
Now that we know how to get JWTs for our users, let’s use the JWTs.
现在我们已经掌握了如何为用户签发 JWT,接下来看看如何在数据库中使用它。
Using the Authorized User获取当前已认证用户
Before we define permissions for our user, let’s utilize the fact that they are logged in by defining a quick Postgres function.
在具体配置用户权限之前,我们先利用用户已登录的上下文信息,编写一个便捷的 Postgres 函数:
create function forum_example.current_person() returns forum_example.person as $$
select *
from forum_example.person
where id = nullif(current_setting('jwt.claims.person_id', true), '')::integer
$$ language sql stable;
comment on function forum_example.current_person() is 'Gets the person who was identified by our JWT.';
This is a simple function that we can use in PostGraphile or our database to get
the person who is currently executing the query — by means of the token in the
request header. The one new concept here is
nullif(current_setting('jwt.claims.person_id', true), '')::integer. As we
discussed before, PostGraphile will serialize your JWT to the database in the
form of transaction local settings. Using the current_setting function is how
we access those settings. Also note that we cast the value to an integer with
::integer. This is because the Postgres current_setting function will always
return a string, if you need another data type, you will likely need to cast to
that data type.
这是一个非常简洁的辅助函数,无论是在 PostGraphile 中还是直接在数据库里,都可以用来根据请求头里的令牌获取当前正在执行查询的用户。这里出现了一个新写法:nullif(current_setting('jwt.claims.person_id', true), '')::integer。正如前文所述,PostGraphile 会将 JWT 声明序列化为数据库事务本地配置,而 current_setting 函数正是用来读取这些配置的。另外请注意末尾的 ::integer 类型转换——Postgres 的 current_setting 总是返回字符串,因此若需其他数据类型,通常都要手动进行类型转换。
Now, let’s use the JWT to define permissions.
下面我们就利用 JWT 来定义数据访问权限。
Grants表级授权(Grants)
The highest level of permission that can be given to roles using the Postgres
are access privileges assigned using the
GRANT
command. The access privileges defined by GRANT work on no smaller level than
the table level. As you can allow a role to select any value from a table, or
delete any value in a table. We will look at how to restrict access on a row
level next.
在 Postgres 中为角色分配权限最宏观的层级,是通过 GRANT 命令授予的访问权限。GRANT 所定义的访问权限最细只作用于整张表级别——例如允许某角色查询表中的任意值,或删除表中的任意记录。下一节我们将介绍如何进一步将访问控制精细到行级别。
-- after schema creation and before function creation
alter default privileges revoke execute on functions from public;
grant usage on schema forum_example to forum_example_anonymous, forum_example_person;
grant select on table forum_example.person to forum_example_anonymous, forum_example_person;
grant update, delete on table forum_example.person to forum_example_person;
grant select on table forum_example.post to forum_example_anonymous, forum_example_person;
grant insert, update, delete on table forum_example.post to forum_example_person;
grant usage on sequence forum_example.post_id_seq to forum_example_person;
grant execute on function forum_example.person_full_name(forum_example.person) to forum_example_anonymous, forum_example_person;
grant execute on function forum_example.post_summary(forum_example.post, integer, text) to forum_example_anonymous, forum_example_person;
grant execute on function forum_example.person_latest_post(forum_example.person) to forum_example_anonymous, forum_example_person;
grant execute on function forum_example.search_posts(text) to forum_example_anonymous, forum_example_person;
grant execute on function forum_example.authenticate(text, text) to forum_example_anonymous, forum_example_person;
grant execute on function forum_example.current_person() to forum_example_anonymous, forum_example_person;
grant execute on function forum_example.register_person(text, text, text, text) to forum_example_anonymous;
See how we had to grant permissions on every single Postgres object we have defined so far? Postgres permissions work as a whitelist and not a blacklist (except for functions), so therefore no one has more access than you explicitly give them. Let’s walk through the grants:
可以看到,我们必须为前面定义过的每一个 Postgres 对象显式授权。Postgres 的权限模型采用“白名单”而非“黑名单”机制(函数默认可执行除外),因此任何角色都不会拥有超出你显式授予范围的权限。下面逐条解析这些授权语句:
-
alter default privileges ...: By default, functions can be executable by public. Since we're applying our fine-grained control over function permissions here, we remove the default grant. Note that this line needs to be placed before any function definition.alter default privileges ...:默认情况下,Postgres 允许public执行新建的函数。由于这里我们需要对函数执行权限进行精细化控制,因此先撤销这一默认授权。注意,这条语句必须放在所有函数定义之前执行。 -
grant usage on schema forum_example to forum_example_anonymous, forum_example_person: We say that anonymous users (forum_example_anonymous) and logged in users (forum_example_person) may use the objects in theforum_exampleschema. This does not mean that those roles can use anything they want in the schema, it just allows the roles to know the schema exists. Also note that we did not grant usage for theforum_example_privateschema.grant usage on schema forum_example to forum_example_anonymous, forum_example_person:允许匿名用户(forum_example_anonymous)和已登录用户(forum_example_person)访问forum_exampleschema 中的对象。这并不意味着他们能随意操作该 schema 下的所有对象,只是让他们能够看到并进入该 schema。另外请注意,我们没有对forum_example_privateschema 授予 usage 权限。 -
grant select on table forum_example.person to forum_example_anonymous, forum_example_person: We give anonymous users and logged in users the ability to read all of the rows in theforum_example.persontable.grant select on table forum_example.person to forum_example_anonymous, forum_example_person:授予匿名用户和已登录用户读取forum_example.person表中所有行数据的权限。 -
grant update, delete on table forum_example.person to forum_example_person: Here we give only logged in users the ability to update and delete rows from theforum_example.persontable. This means that anonymous users can never update or delete a person. However, it does mean that users can update and delete any rows in the table. We will fix this later.grant update, delete on table forum_example.person to forum_example_person:此处仅授予已登录用户更新和删除forum_example.person表数据的权限,匿名用户绝对无法修改或删除任何用户信息。不过,光有这条授权还意味着已登录用户可以修改或删除表里的任意行——这一点我们稍后会加以限制。 -
grant select ...andgrant insert, update, delete ...: We do the same thing with these two grants as we did with the grants above. The only difference here is that we also give signed in users the ability toinsertintoforum_example.post. We do not allow anyone to insert directly intoforum_example.person, instead users should use theforum_example.register_personfunction.grant select ...与grant insert, update, delete ...:这两条授权与上面同理,唯一区别是额外允许已登录用户向forum_example.post表执行insert操作。注意我们不允许任何人直接向forum_example.person表插入记录,注册新用户必须统一通过forum_example.register_person函数完成。 -
grant usage on sequence forum_example.post_id_seq to forum_example_person: When a user creates a newforum_example.postthey will also need to get the next value in theforum_example.post_id_seqbecause we use theserialdata type for theidcolumn. A sequence also exists for our person table (forum_example.person_id_seq), but since we are only creating people throughforum_example.register_personand that function specifiessecurity definer, we don’t need to grant access to the person id sequence.grant usage on sequence forum_example.post_id_seq to forum_example_person:由于forum_example.post表的id列采用了serial自增类型,用户在发布新帖子时需要从forum_example.post_id_seq序列中获取下一个值。person表同样有对应的序列(forum_example.person_id_seq),但因为创建用户只通过声明了security definer的forum_example.register_person函数执行,所以无需单独授予该序列的访问权限。 -
grant execute ...: We have to give the anonymous user and logged in users access to all of the Postgres functions we define. All of the functions are executable by both types of users, exceptforum_example.register_personwhich we only let anonymous users execute. There’s no need for logged in users to register a new user!grant execute ...:我们需要把前面定义的所有 Postgres 函数的执行权限分别授予匿名用户和已登录用户。除forum_example.register_person外,其余函数均对两类用户开放;而注册函数仅限匿名用户调用——已登录用户自然不需要再注册新账号!
This provides basic permissions for all of our Postgres objects, but as we mentioned before users can update and delete all and any persons or posts. For obvious reasons we don’t want this, so let’s define row level security next.
至此,所有 Postgres 对象的基础权限都已经配置完毕。但正如前面所说,目前用户还能修改或删除任意用户和帖子,这显然不符合预期。接下来我们就通过行级安全策略(RLS)来收紧权限。
Row Level Security行级安全策略(RLS)
In Postgres 9.5 (released January 2016) Row Level Security (RLS) was introduced. RLS allows us to specify access to the data in our Postgres databases on a row level instead of a table level. In order to enable row level security on our tables we first need to run the following:
Postgres 9.5(发布于 2016 年 1 月)引入了行级安全策略(Row Level Security,简称 RLS)。RLS 让我们能够将数据库访问权限精细控制到“行”级别,而非仅停留在表级别。首先,需要在目标表上启用 RLS:
alter table forum_example.person enable row level security;
alter table forum_example.post enable row level security;
Before running these commands, the forum_example_person and
forum_example_anonymous roles could see every row in the table with a
select * from forum_example.person query. After running these two commands
those same roles can’t. By enabling row level security, our roles don’t have any
access to read or write to a table that you don’t explicitly give, so to
re-enable access to all the rows we will define RLS policies with the
CREATE POLICY
command.
在执行这两条命令之前,forum_example_person 和 forum_example_anonymous 角色通过 select * from forum_example.person 可以查到表中的所有数据;而启用 RLS 之后,在没有显式配置放行策略的情况下,这些角色将无法读写表中的任何一行。因此,我们需要使用 CREATE POLICY 命令来编写具体的 RLS 策略,重新开放合规的数据访问:
create policy select_person on forum_example.person for select
using (true);
create policy select_post on forum_example.post for select
using (true);
Now both anonymous users and logged in users can see all of our
forum_example.person and forum_example.post rows again. We also want signed
in users to be able to only update and delete their own row in
forum_example.person.
现在,匿名用户和已登录用户又能正常浏览 forum_example.person 和 forum_example.post 表中的所有记录了。接下来,我们要限制已登录用户在 forum_example.person 表中只能更新和删除自己的那一行记录:
create policy update_person on forum_example.person for update to forum_example_person
using (id = nullif(current_setting('jwt.claims.person_id', true), '')::integer);
create policy delete_person on forum_example.person for delete to forum_example_person
using (id = nullif(current_setting('jwt.claims.person_id', true), '')::integer);
We use the current person_id from our JWT and only allow updates and deletes
on rows with the same id. Also note how we added to forum_example_person. This
is because we only want these policies to apply for the forum_example_person
role.
这里从 JWT 中提取当前用户的 person_id,仅允许更新和删除与之 id 相同的行。另外请注意语句中的 to forum_example_person——这表示两条策略只针对 forum_example_person 角色生效。
That’s all we need to define for our person table. Now let’s define three
policies for our posts table. One for INSERT, UPDATE, and DELETE.
person 表的安全策略就全部配置好了。下面再为 post 表定义三条策略,分别对应 INSERT、UPDATE 和 DELETE 操作:
create policy insert_post on forum_example.post for insert to forum_example_person
with check (author_id = nullif(current_setting('jwt.claims.person_id', true), '')::integer);
create policy update_post on forum_example.post for update to forum_example_person
using (author_id = nullif(current_setting('jwt.claims.person_id', true), '')::integer);
create policy delete_post on forum_example.post for delete to forum_example_person
using (author_id = nullif(current_setting('jwt.claims.person_id', true), '')::integer);
These policies are very similar to the ones before, except that the
insert_post policy uses with check instead of using like our other
policies. The difference between with check and using is roughly that
using is applied before any operation occurs to the table’s rows. So in the
case of updating a post, one could not update a row that does not have the
appropriate author_id in the first place. with check is run after an
operation is applied. If the with check fails the operation will be rejected.
So in the case of an insert, Postgres sets all of the columns as specified and
then compares against with check on the new row. You must use with check
with INSERT commands because there are no rows to compare against before
insertion, and you must use using with DELETE commands because a delete
changes no rows only removes current ones.
这三条策略与前面的非常相似,唯一的区别在于 insert_post 使用了 with check 而非其他策略中的 using。两者的区别大致可以理解为:using 在行操作执行之前进行过滤——例如更新帖子时,用户从一开始就无法选中 author_id 不匹配的记录进行修改;而 with check 则在操作执行之后进行校验,一旦校验不通过,整个操作就会被拒绝。因此,在执行插入操作时,Postgres 会先按传入值构造出新行,再用 with check 对新行做校验。对于 INSERT 命令必须使用 with check,因为插入前还没有现存行可供筛选;而对于 DELETE 命令必须使用 using,因为删除操作不会产生新行,只是移除已有记录。
That’s it! We have successfully created a Postgres schema embedded with our business logic. When we use this schema with PostGraphile we will get a well designed GraphQL API that we can use in our frontend application.
大功告成!我们已经成功设计出一套内置业务逻辑的 Postgres schema。将这套 schema 接入 PostGraphile 后,就能自动获得一个设计良好的 GraphQL API,直接供前端应用调用。
The final configuration for our PostGraphile server would be as follows:
PostGraphile 服务端的最终配置如下:
import { PostGraphileAmberPreset } from "postgraphile/presets/amber";
import { PgLazyJWTPreset } from "postgraphile/presets/lazy-jwt";
export default {
extends: [PostGraphileAmberPreset, PgLazyJWTPreset],
gather: {
pgJwtTypes: "forum_example.jwt_token",
},
schema: {
pgJwtSecret: "keyboard_kitten", // REPLACE THIS!
},
grafast: {
context(requestContext, args) {
return {
pgSettings: {
role: "forum_example_anonymous",
// JWT may override the role:
...args.contextValue?.pgSettings,
},
};
},
},
};
And you'd run PostGraphile as:
随后通过以下命令启动 PostGraphile:
postgraphile \
--connection postgres://forum_example_postgraphile:xyz@localhost
Conclusion结语
You should now be equipped with the knowledge to go out and design your own Postgres schema. If you have any questions, encounter a bug, or just want to say thank you, don’t hesitate to open an issue, we’d love to hear from you. The PostGraphile community wants to invest in making you a productive developer so that you can invest back into PostGraphile.
现在你已经掌握了自主设计 Postgres schema 的核心知识。如果在使用过程中遇到任何疑问、发现 Bug,或者单纯想表达感谢,欢迎随时在 GitHub 上提交 Issue,我们非常期待听到你的声音。PostGraphile 社区致力于帮助你成为更高效的开发者,也期待你在受益的同时继续反哺社区。
This article was originally written by Caleb Meredith.
本文原作者为 Caleb Meredith。