Skip to main content

The Default Role

PostGraphile makes full use of PostgreSQL roles, so in this article we will explain briefly how PostgreSQL roles and users work and how that relates to how we use them in PostGraphile.

PostGraphile 充分依托 PostgreSQL 的角色体系,因此本文将简要介绍 PostgreSQL 中角色(role)与用户(user)的工作原理,以及 PostGraphile 如何使用它们。

You can make any number of PostgreSQL roles with CREATE ROLE command and assign permissions to those roles with the GRANT command. Permissions like select from the table post or insert rows into the person table.

你可以使用 CREATE ROLE 命令创建任意数量的 PostgreSQL 角色,并通过 GRANT 命令为这些角色分配权限——例如查询 post 表或向 person 表插入数据的权限。

PostgreSQL roles are also hierarchical. That is you can “grant” roles to other roles. For example if I had role editor which could change the data in our database and role admin, if I granted the editor role to admin with the command:

PostgreSQL 的角色还具有层级关系,也就是说你可以将一个角色“授予”另一个角色。例如,假设存在一个可修改数据库数据的 editor 角色和一个 admin 角色,如果执行以下命令将 editor 授予 admin:

grant editor to admin;

Then the admin role would have the same permissions the editor role has. The admin role would also be able to change its role to the editor role. This means for the rest of the session you don’t have any admin permissions, but only permissions given to the editor role.

那么 admin 角色就会拥有 editor 角色的全部权限。同时,admin 角色还可以将当前会话中的身份切换为 editor 角色。一旦切换,在当前会话剩余的时间里,你将不再拥有任何 admin 权限,而仅保有授予 editor 角色的权限。

In PostgreSQL you also have the idea of a user. A user is just a role that can login. So for example, the following are equivalent as the create an admin role that can log in (or a user):

PostgreSQL 中还有“用户(user)”的概念。所谓用户,本质上就是一个允许登录的角色。因此,下面两条命令是完全等价的,都会创建一个可登录的 admin 角色(即用户):

create role admin login;
create user admin;

…and the following are also equivalent as they create a role that can’t log in:

……同理,下面两条命令也是等价的,都会创建一个无法登录的角色:

create role editor;
create user editor nologin;

“Logging in” just means we can use the role when authenticating in the PostgreSQL authentication section of the connection string. So with the above roles you could start a PostgreSQL connection with postgres://admin@localhost/mydb, but not postgres://editor@localhost/mydb.

“允许登录”仅意味着我们可以在 PostgreSQL 连接字符串的认证部分使用该角色。以前面创建的两个角色为例,你可以用 postgres://admin@localhost/mydb 建立数据库连接,但不能使用 postgres://editor@localhost/mydb。

Roles in PostGraphilePostGraphile 中的角色​

So how does this apply to PostGraphile? PostGraphile requires you to have at least one user (role that can log in) when connecting to the server. That role will be specified in your connection string and will from here on out be referred to as the authenticator. You’d connect with your authenticator as follows:

这套机制在 PostGraphile 中是如何运用的呢?PostGraphile 连接到数据库服务端时,至少需要一个用户(即可登录的角色)。这个角色会写在你的数据库连接字符串中,下文我们统一称其为 authenticator。使用 authenticator 连接数据库的方式如下:

postgraphile -c postgres://authenticator@localhost/mydb

The authenticator will have all the privileges PostGraphile might need (including, and often exclusively, the ability to switch to certain other roles), but should be restricted so that it can only see data that's intended to be exposed.

authenticator 角色应当具备 PostGraphile 正常运行所需的权限(包括且往往仅限于切换到特定其他角色的权限),但本身权限必须受到严格限制,确保它只能访问允许对外暴露的数据。

The authenticator role can be granted the ability to switch to other roles e.g. via grant visitor to authenticator;. Using the noinherit option when you define the authenticator role (create role authenticator login noinherit ...) will mean that it needs to switch to this role to gain its permissions - e.g. via set role to visitor; - this can be a great security boundary.

你可以通过 grant visitor to authenticator; 赋予 authenticator 角色切换到其他角色的能力。如果在定义 authenticator 角色时加上 noinherit 选项(create role authenticator login noinherit ...),则它必须显式切换角色(例如执行 set role to visitor;)才能获得对应角色的权限——这能构成一道非常可靠的权限隔离边界。

In PostGraphile this role switch can be triggered via the role setting which can be populated in your preset.grafast.context callback:

在 PostGraphile 中,这种角色切换可以通过 role 设置来触发,你可以在 preset.grafast.context 回调函数中填入该设置:

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

export default {
extends: [PostGraphileAmberPreset],
grafast: {
context(requestContext, args) {
// Extract the "session user" from the request context, e.g.
const user = requestContext.expressv4?.req?.user;

// Start with the role we've already set it to - e.g. via the JWT plugin
let role = args.contextValue.pgSettings?.role;

// If this is unset: default to "visitor" if logged in, anonymous otherwise
role ??= user ? "visitor" : "anonymous";

return {
pgSettings: {
// Override the role in the pgSettings
...args.contextValue.pgSettings,
role,
},
};
},
},
};

This article was originally written by Caleb Meredith.

本文最初由 Caleb Meredith 撰写。