我正在尝试向 API 添加授权层，而我目前的设计导致 SQL 查询数量超过了我认为应该需要的数量，所以我想知道如何简化这一过程。

上下文

这是这部分问题的数据库模式:

CREATE TABLE IF NOT EXISTS users (
  id          TEXT PRIMARY KEY,
  email       CITEXT NOT NULL UNIQUE,
  password    TEXT NOT NULL,
  name        TEXT NOT NULL,
  created_at  DATE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS teams (
  id          TEXT PRIMARY KEY,
  email       CITEXT NOT NULL,
  name        TEXT NOT NULL,
  created_at  DATE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS memberships (
  id          TEXT PRIMARY KEY,
  "user"      TEXT NOT NULL REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE,
  team        TEXT NOT NULL REFERENCES teams(id) ON UPDATE CASCADE ON DELETE CASCADE,
  role        TEXT NOT NULL,
  created_at  DATE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE("user", team)
);

有问题的 API 端点是 GET/users/:user/teams，它返回用户所属的所有团队。该路由的 Controller 如下所示:

(注意:所有这些都是 Javascript，但为了清楚起见，它是某种伪代码。)

async getTeams(currentId, userId) {
  await exists(userId)
  await canFindTeams(currentUser, userId)
  let teams = await findTeams(userId)
  let maskedTeams = await maskTeams(currentUser, teams)
  return maskedTeams
}

这四个异步函数是授权“完成”所需发生的核心逻辑步骤。以下是这些函数中的每一个大致的样子:

async exists(userId) {
  let user = await query(`
    SELECT id
    FROM users
    WHERE id = $[userId]
  `)
  if (!user) throw new Error('user_not_found')
  return user
}

exists 只是检查 userId 的用户是否存在于数据库中，如果不存在则抛出正确的错误代码。

query 只是使用转义变量运行 SQL 查询的伪代码。

async canFindTeams(currentUser, userId) {
  if (currentUser.id == userId) return
  let isTeammate = await query(`
    SELECT role
    FROM memberships
    WHERE "user" = $[currentUser.id]
    AND team IN (
      SELECT team
      FROM memberships
      WHERE "user" = $[userId]
    )
  `)
  if (!isTeammate) throw new Error('team_find_unauthorized')
}

canFindTeams 确保当前用户是发出请求的用户，或者当前用户是相关用户的队友。不应授权其他任何人查找相关用户。在我的实际实现中，它实际上是通过具有关联的操作的roles完成的，这样队友可以teams.read但不能teams.admin 除非他们是自己的。但是我在这个例子中简化了它。

async findTeams(userId) {
  return await query(`
    SELECT
      teams.id,
      teams.email,
      teams.name,
      teams.created_at
    FROM teams
    LEFT JOIN memberships ON teams.id = memberships.team
    LEFT JOIN users ON users.id = memberships.user
    WHERE users.id = $[userId]
    ORDER BY
      memberships.created_at DESC,
      teams.id
  `)
}

findTeams 实际上会在数据库中查询团队对象。

async maskTeams(currentUser, teams) {
  let memberships = await query(`
    SELECT team
    FROM memberships
    WHERE "user" = $[currentUser.id]
  `)
  let teamIds = memberships.map(membership => membership.team)
  let maskedTeams = teams.filter(team => teamIds.includes(team.id))
  return maskedTeams
}

maskTeams 将只返回给定用户应该看到的团队。这是必需的，因为用户应该能够看到他们所有的团队，但队友应该只能看到他们共同的团队，以免泄露信息。

问题

导致我像这样分解它的一个要求是我需要一种方法来抛出那些特定的错误代码，以便返回给 API 客户端的错误是有帮助的。例如，exists 函数在 canFindTeams 函数之前运行，因此并非所有错误都显示 403 Unauthorized。

另一个，这里没有用伪代码很好地传达，是 currentUser 实际上可以是一个 app(第三方客户端)，一个 team(属于团队本身的访问 token )或 user(常见情况)。此要求使得难以将 canFindTeams 或 maskTeams 函数作为单个 SQL 语句来实现，因为逻辑必须以三种方式分支...在我的实现中，这两个函数实际上是围绕处理所有三种情况的逻辑的 switch 语句——请求者是 app、team 和 user。

但即使考虑到这些限制，也感觉需要编写大量额外代码来确保所有这些身份验证要求。我担心性能、代码可维护性以及这些查询并非全部在单个事务中这一事实。

问题

• 额外的查询是否会对性能产生有意义的影响？
• 能否将它们轻松组合成更少的查询？
• 是否有更好的授权设计来简化这一过程？
• 不使用交易会带来问题吗？
• 您还有什么要更改的吗？

谢谢!

最佳答案

我把它变成了一个函数并简化了表格，以便于测试。 SQL Fiddle .我正在做假设，因为一些规则嵌入在我不太理解的 javascript 伪代码中。

create or replace function visible_teams (
    _user_id int, _current_user_id int
) returns table (
    current_user_role int,
    team_id int,
    team_email text,
    team_name text,
    team_created_at date
) as $$
    select
        m0.role,
        m0.team,
        t.email,
        t.name,
        t.created_at
    from
        memberships m0
        left join
        memberships m1 on m0.team = m1.team and m1.user = _user_id
        inner join
        teams t on t.id = m0.team
    where m0.user = _current_user_id

    union

    select null, null, null, null, null
    where not exists (select 1 from users where id = _user_id)

    order by role nulls first
    ;
$$ language sql;

返回所有当前用户的团队加上用户共同的团队:

select * from visible_teams(3, 1);
 current_user_role | team_id | team_email | team_name | team_created_at 
-------------------+---------+------------+-----------+-----------------
                 1 |       1 | email_1    | team_1    | 2016-03-13
                 1 |       3 | email_3    | team_3    | 2016-03-13
                 2 |       2 | email_2    | team_2    | 2016-03-13
(3 rows)

当用户不存在时，它返回包含空值的第一行以及所有当前用户的团队:

select * from visible_teams(5, 1);
 current_user_role | team_id | team_email | team_name | team_created_at 
-------------------+---------+------------+-----------+-----------------
                   |         |            |           | 
                 1 |       1 | email_1    | team_1    | 2016-03-13
                 1 |       3 | email_3    | team_3    | 2016-03-13
                 2 |       2 | email_2    | team_2    | 2016-03-13
(4 rows)

当当前用户不存在则空集:

select * from visible_teams(1, 5);
 current_user_role | team_id | team_email | team_name | team_created_at 
-------------------+---------+------------+-----------+-----------------
(0 rows)

关于javascript - 如何在更少的 SQL 查询中执行复杂的 API 授权？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/35910451/