Skip to main content

PostgreSQL JWT specification

This specification was authored by Caleb Meredith for use in the PostGraphQL project. The language of the specification is meant to be generally applicable and adoptable by any who might want to use it.

本规范最初由 Caleb Meredith 为 PostGraphQL 项目撰写。规范的措辞力求保持通用性,任何有需要的项目都可以直接采纳。

There are alternatives to JWTs available除了 JWT 之外还有其他替代方案

What follows is a specification of how you can use JWTs to authenticate a user with PostgreSQL such that PostgreSQL may perform your authorization checks. This is not the only way of achieving this goal with PostGraphile, for example you can do as the PostGraphile maintainer does and use standard session-based authentication (i.e. with cookies) via the relevant Express/Koa/ Fastify middleware and PostGraphile’s pgSettings functionality allowing you to leverage powerful pre-built authentication stacks such as passport.js which has OAuth integration with most major “social login” providers.

下文规范了如何通过 JWT 在 PostgreSQL 中完成用户认证,从而让 PostgreSQL 能够执行后续的鉴权检查。但这并不是在 PostGraphile 中实现该目标的唯一途径——例如,你也可以像 PostGraphile 维护者那样,借助 Express/Koa/Fastify 等中间件使用标准的基于会话(即 Cookie)的认证机制,并通过 PostGraphile 的 pgSettings 功能传入上下文信息;这样还能直接复用 passport.js 等成熟的现成认证生态,轻松集成各大主流“社交登录” OAuth 服务。

PostgreSQL JSON Web Token Serialization SpecificationPostgreSQL JSON Web Token 序列化规范​

This specification aims to define a standard way to serialize JSON Web Tokens (JWT, RFC 7519) to a PostgreSQL database for developers who want to move authorization logic into their PostgreSQL schema.

对于希望将鉴权逻辑下沉到 PostgreSQL schema 中的开发者,本规范旨在定义一种将 JSON Web Tokens(JWT,RFC 7519)序列化到 PostgreSQL 数据库的标准方式。

Terminology from the JSON Web Token specification will be used.

下文中将沿用 JSON Web Token 规范中的相关术语。

After a JSON Web Token has been verified and decoded, the resulting claims will be serialized to the PostgreSQL database in two ways:

当 JSON Web Token 通过验证并完成解码后,解析得到的声明(claim)将以以下两种方式序列化到 PostgreSQL 数据库中:

  1. Using the role claim, the corresponding role will be set in the database using SET ROLE:

    对于 role 声明,使用 SET ROLE 在数据库中设置对应的角色:

    set local role $role;

    Where $role is the claim value for the role claim. It is not an error if the role claim is not set.

    其中 $role 为 role 声明的值。如果令牌中未包含 role 声明,也不视为错误。

  2. All remaining claims will be set using the SET command under the jwt.claims namespace. Using:

    所有其余声明都会通过 SET 命令设置到 jwt.claims 命名空间下,格式如下:

    set local jwt.claims.$claim_name to $claim_value;

    Will be run for every claim including registered claims like iss, sub, and the claim specified 1 (role). $claim_name is the name of the claim and $claim_value is the associated value.

    该语句会针对每一个声明执行,包括 iss、sub 等注册声明(registered claims),以及第 1 条中处理的声明(role)。$claim_name 为声明名称,$claim_value 为对应的声明值。

Example示例​

A JSON Web Token with the following claims:

假设一个 JSON Web Token 包含以下声明:

{
"sub": "postgraphql",
"role": "user",
"user_id": 2
}

Would result in the following SQL being run:

那么最终将执行以下 SQL 语句:

set local role user;
set local jwt.claims.sub to 'postgraphql';
set local jwt.claims.role to 'user';
set local jwt.claims.user_id to 2;

A Note on local关于 local 关键字的说明​

Using local for SET and SET ROLE is not required, however it is recommended. This is so that every transaction block (beginning with BEGIN and ending with COMMIT or ROLLBACK) will have its own local parameters. See the following demonstration:

在 SET 和 SET ROLE 中使用 local 并非强制要求,但强烈建议这样做。这能确保每个事务块(从 BEGIN 开始,到 COMMIT 或 ROLLBACK 结束)都拥有各自独立的局部参数,避免污染后续连接状态。请看以下示例:

begin;
set local jwt.claims.user_id to 2;

-- Has access to `jwt.claims.user_id`
commit;

-- Does not have access to `jwt.claims.user_id`

Retrieving Claims in PostgreSQL在 PostgreSQL 中读取声明​

In order to retrieve a claim set by the serialization of a JSON Web Token as defined in this spec, either the current_setting function or the SHOW command may be used like so:

要读取按本规范序列化到 PostgreSQL 中的 JSON Web Token 声明,可以使用 current_setting 函数或 SHOW 命令,如下所示:

select current_setting('jwt.claims.user_id');
-- Or…
show jwt.claims.user_id;