Skip to main content

Security

Traditionally in web application architectures the security is implemented in the server layer and the database is treated as a simple store of data. Partly this was due to necessity (the security policies offered by databases such as PostgreSQL were simply not granular enough), and partly this was people figuring it would reduce the workload on the database thus increases scalability. However, as applications grow, they start needing more advanced features or additional services to interact with the database. There's a couple options they have here: duplicate the authentication/authorization logic in multiple places (which can lead to discrepancies and increases the surface area for potential issues), or make sure everything goes through the original application layer (which then becomes both the development and performance bottleneck).

在传统的 Web 应用架构中,安全控制通常实现在服务端应用层,数据库只被当作单纯的数据存储来使用。这一方面是受限于过去的条件(当时 PostgreSQL 等数据库提供的安全策略粒度还不够细),另一方面也是人们认为这样可以减轻数据库负载、提升系统可扩展性。然而,随着应用规模不断扩大,业务往往需要引入更高级的特性,或是增加其他直接与数据库交互的服务。此时就会面临两难选择:要么在多个服务中重复实现一遍认证和鉴权逻辑(这容易导致逻辑不一致,还会扩大潜在的攻击面);要么强制所有请求都经过最初的应用层(这又会让该应用层同时成为开发迭代和系统性能的瓶颈)。

However, this is no longer necessary since PostgreSQL introduced much more granular permissions in the form of Row-Level Security (RLS) policies in PostgreSQL 9.5 back at the beginning of 2016. Now you can combine this with PostgreSQL established permissions system (based on roles) allowing your application to be considerably more specific about permissions: adding row-level permission constraints to the existing table- and column-based permissions.

不过,自从 PostgreSQL 在 2016 年初发布的 9.5 版本中引入粒度更细的行级安全性(RLS)策略后,就不再需要这样做了。如今,你可以将 RLS 与 PostgreSQL 成熟的基于角色的权限体系结合起来,为应用实现更加精细的权限控制:在原有的表级和列级权限基础之上,进一步叠加行级权限约束。

Now that this functionality is stable and proven (and especially with the performance improvements in the latest PostgreSQL releases), we advise that you protect your lowest level — the data itself. By doing so you can be sure that no matter how many services interact with your database they will all be protected by the same underlying permissions logic, which you only need to maintain in one place. You can add as many microservices as you like, and they can talk to the database directly!

鉴于这项特性已经十分稳定且经过了充分验证(尤其是在最新版 PostgreSQL 带来了显著性能优化之后),我们建议直接从最底层——也就是数据本身——入手做好安全防护。这样一来,无论有多少个服务与数据库交互,它们都会受同一套底层权限逻辑保护,而你只需在一处维护这套逻辑即可。你可以按需不断新增微服务,而且它们都可以直接访问数据库!

When Row Level Security (RLS) is enabled, all rows are by default not visible to any roles (except database administration roles and the role who created the database/table); and permission is selectively granted with the use of policies.

启用行级安全性(RLS)后,默认情况下所有数据行对任何角色均不可见(数据库管理员角色以及创建该数据库或表的角色除外),随后再通过策略(policy)有选择地授予访问权限。

If you already have a secure database schema that implements these technologies to protect your data at the lowest levels then you can leverage postgraphile to generate a powerful, secure and fast API very rapidly. PostGraphile simply needs enough context (via pgSettings) to understand who is making the current request.

如果你的数据库 schema 已经采用上述机制在最底层保护了数据安全,那么借助 postgraphile 就可以迅速生成强大、安全且高性能的 API。PostGraphile 只需要通过 pgSettings 获取足够的上下文信息,以识别当前请求的发起者即可。

Authentication strategies身份认证策略​

  • Sessions: Use your framework’s existing session middleware (e.g. express-session, @fastify/session). After the session has been validated you can copy the user identifier and any relevant flags into pgSettings.

    会话(Session):直接沿用 Web 框架现有的会话中间件(例如 express-session、@fastify/session)。在会话校验通过后,将用户标识及相关标记复制到 pgSettings 中即可。

  • JWTs: Verify the token in your middleware of choice, then map whichever claims you require into PostgreSQL. The JWT guide walks through that process and links to the PostgreSQL JWT specification that PostGraphile follows.

    JWT:在你选用的中间件中完成令牌验证,再将所需的声明(claim)映射到 PostgreSQL 中。JWT 指南详细介绍了这一流程,并提供了 PostGraphile 所遵循的 PostgreSQL JWT 规范的链接。

  • Other tokens: API keys, mTLS attributes, OAuth access tokens, or other credentials can authenticate the caller; convert whatever identity or policy data you need into values for pgSettings.

    其他令牌:API 密钥、mTLS 属性、OAuth 访问令牌或其他凭证均可用于对调用方进行身份认证;只需将所需身份信息或策略数据转换为 pgSettings 中的对应键值即可。

PostGraphile does not recommend one approach over another; pick whatever fits the rest of your infrastructure and long-term maintenance plans.

PostGraphile 并不偏好或强推某一种特定方案;请根据你现有的基础设施架构和长期维护规划,选择最合适的方式即可。

lazy-jwt is a stopgaplazy-jwt 只是临时过渡方案

The postgraphile/presets/lazy-jwt preset can decode simple Bearer tokens, but it deliberately does not address refresh tokens, revocation, or custom claim mapping. It can be helpful to get you started, but do not use it as a permanent solution.

postgraphile/presets/lazy-jwt preset 可以解码简单的 Bearer 令牌,但它刻意不处理刷新令牌(refresh token)、令牌吊销以及自定义声明映射。它适合用来帮你快速上手,但不要将其作为长期方案使用。

Feeding identity into PostgreSQL将身份信息传入 PostgreSQL​

Your authentication layer runs inside your web framework; PostgreSQL only sees the values you place into pgSettings. A common pattern is to copy data from the framework’s request object inside preset.grafast.context:

身份认证层运行在你的 Web 框架内部,而 PostgreSQL 只能看到你放入 pgSettings 中的值。常见的做法是在 preset.grafast.context 中从框架的请求对象里提取并复制所需数据:

graphile.config.ts
export default {
grafast: {
async context(requestContext, args) {
const req = requestContext.expressv4?.req;
const pgSettings = {
...args.contextValue?.pgSettings,
} as Record<string, string>;

if (req?.user?.id) {
pgSettings["myapp.user_id"] = String(req.user.id);
}
if (req?.user?.is_admin) {
pgSettings["myapp.is_admin"] = "true";
}

return {
...args.contextValue,
pgSettings,
};
},
},
};

Inside PostgreSQL you can read these values with current_setting:

在 PostgreSQL 内部,可以通过 current_setting 读取这些值:

create function myapp.current_user_id() returns uuid as $$
select nullif(current_setting('myapp.user_id', true), '')::uuid;
$$ language sql stable;

Apply the function (or the current_setting call directly) inside Row Level Security policies to enforce your rules.

将该函数(或直接调用 current_setting)应用到行级安全性(RLS)策略中,即可强制执行权限规则。

The configuration docs contain more variations on this pattern, including how to expose HTTP headers or other request metadata.

配置文档中还介绍了该模式的更多用法变体,包括如何透传 HTTP 请求头或其他请求元数据。

Issuing JWTs from PostgreSQL从 PostgreSQL 签发 JWT​

PostGraphile also has support for generating JWTs easily from inside your PostgreSQL schema.

PostGraphile 还支持直接从你的 PostgreSQL schema 内部轻松生成 JWT。

To do so we will take a composite type that you specify via preset.gather.pgJwtTypes:

要启用该功能,只需通过 preset.gather.pgJwtTypes 指定一个复合类型(composite type):

graphile.config.mjs
export default {
gather: {
pgJwtTypes: "jwt_token",
},
//...
};

The value of this setting is a schema-name, type-name tuple. Whenever a value of the type identified by this tuple is returned from a PostgreSQL function we will instead sign it with your JWT secret and return it as a string JWT token as part of your GraphQL response payload.

该配置项的值用于指定一对 schema 名称与类型名称(tuple)。每当 PostgreSQL 函数返回该配置所标识类型的值时,PostGraphile 就会使用你的 JWT 密钥对其签名,并将其作为字符串形式的 JWT 令牌随 GraphQL 响应 payload 一并返回。

For example, you might define a composite type such as this in PostgreSQL:

例如,你可以在 PostgreSQL 中定义如下复合类型:

create type my_public_schema.jwt_token as (
role text,
exp integer,
person_id integer,
is_admin boolean,
username varchar
);

Then run PostGraphile with this configuration

接着使用以下配置运行 PostGraphile:

graphile.config.mjs
import { PostGraphileAmberPreset } from "postgraphile/presets/amber";

export default {
extends: [PostGraphileAmberPreset],
gather: {
pgJwtTypes: "my_public_schema.jwt_token",
},
schema: {
pgJwtSecret: process.env.JWT_SECRET,
},
};

And finally you might add a PostgreSQL function such as:

最后,添加一个类似下面的 PostgreSQL 函数:

create function my_public_schema.authenticate(
email text,
password text
)
returns my_public_schema.jwt_token
as $$
declare
account my_private_schema.person_account;
begin
select a.* into account
from my_private_schema.person_account as a
where a.email = authenticate.email;

if account.password_hash = crypt(password, account.password_hash) then
return (
'person_role',
extract(epoch from now() + interval '7 days'),
account.person_id,
account.is_admin,
account.username
)::my_public_schema.jwt_token;
else
return null;
end if;
end;
$$ language plpgsql strict security definer;

Which would give you an authenticate mutation with which you can extract the jwtToken from the response payload.

这样就会生成一个 authenticate mutation,你可以从其响应 payload 中提取出 jwtToken。

Remember that the resulting token will be verified by whichever middleware you write (or by the lazy-jwt preset if you are still using it). Review the PostgreSQL JWT specification to ensure the fields you return map cleanly onto PostgreSQL session settings.

请记住,签发出来的令牌将由你自己编写的中间件(或者在你仍在使用的情况下由 lazy-jwt preset)进行校验。建议查阅 PostgreSQL JWT 规范,确保返回的字段能够清晰准确地映射到 PostgreSQL 会话设置中。

Using values inside PostgreSQL在 PostgreSQL 内部使用传入的值​

Whether you authenticate with sessions, JWTs, or another mechanism, PostGraphile ultimately sets PostgreSQL parameters with the data you place into pgSettings. The PostgreSQL JWT specification documents the exact set_config calls used for JWT claims, but the same principles apply to any custom prefix you choose.

无论你采用会话、JWT 还是其他认证机制,PostGraphile 最终都会将你放入 pgSettings 的数据设置为 PostgreSQL 会话参数。PostgreSQL JWT 规范详细说明了 JWT 声明对应的精确 set_config 调用方式,但同样的原理也适用于你自定义的任何前缀。

For example, if you push a user identifier through pgSettings then the database session might receive commands similar to:

例如,如果你通过 pgSettings 传入了用户标识,那么数据库会话可能会收到类似如下的命令:

begin;
set local myapp.user_id to '2';

-- PERFORM GRAPHQL QUERIES HERE

commit;
info说明

To save round-trips, many adaptors perform just one query to set all configs via:

为了减少网络往返次数,许多适配器(adaptor)会通过单条查询一次性设置所有配置参数:

select set_config('role', 'app_user', true),
set_config('user_id', '2', true),
...;

but showing set local is simpler to understand.

不过用 set local 来展示会更容易理解。

You can then access the data via current_setting(name, true) (the second argument says it is okay for the property to be missing). A helper function such as the following can keep row level policies tidy:

之后便可通过 current_setting(name, true) 读取这些数据(第二个参数表示允许该属性尚未设置)。编写类似下面的辅助函数可以让行级安全策略保持整洁:

create function current_user_id() returns integer as $$
select nullif(current_setting('myapp.user_id', true), '')::integer;
$$ language sql stable;

and you can rely on it inside policies:

随后就能在策略中直接调用它:

create policy update_if_author
on comments
for update
using ("userId" = current_user_id())
with check ("userId" = current_user_id());