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 项目撰写。规范的措辞力求保持通用性,任何有需要的项目都可以直接采纳。
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 数据库中:
-
Using the
roleclaim, the corresponding role will be set in the database usingSET ROLE:对于
role声明,使用SET ROLE在数据库中设置对应的角色:set local role $role;Where
$roleis the claim value for theroleclaim. It is not an error if theroleclaim is not set.其中
$role为role声明的值。如果令牌中未包含role声明,也不视为错误。 -
All remaining claims will be set using the
SETcommand under thejwt.claimsnamespace. 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_nameis the name of the claim and$claim_valueis 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 关键字的说明
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;