主页 / dba / 问题 / 168127
Accepted
guettli
Asked: 2017-03-25 11:19:49 +0800 CST

确保某些行的 N:ALL 关系

  • 772

我有一个简单的 N:M 关系：

表用户：

  • 列号
  • 列用户名
  • 列 is_superuser（布尔值）

表权限：

  • 列号
  • 列名

表用户权限：

  • 列号
  • 外键用户
  • 外键权限

我想确保如果用户有“is_superuser=True”，那么他应该通过 N:M Table UserPermission 设置所有可用的权限。

典型操作：

  • 更新或插入表“User”：用户设置为“is_superuser”。现在 UserPermission 需要拥有该用户的所有权限
  • 一个新的权限被添加到权限表中。现在 UserPermission 需要获取 N 个新行（对于 is_superuser=True 的每个用户）。

如何使用 Postgres 9.6 确保这一点？

子问题（请留言）：这个模式有名字吗？

database-design postgresql

1 个回答

  1. Best Answer

    这是个错误。只需加入表格并推断权限即可。

    CREATE TABLE users (
  userid       serial  PRIMARY KEY,
  username     text    UNIQUE,
  is_superuser bool    NOT NULL DEFAULT false
);
CREATE TABLE permissions (
  permid  serial PRIMARY KEY,
  name    text
);
CREATE TABLE user_permission (
  permid  int NOT NULL REFERENCES permissions,
  userid  int NOT NULL REFERENCES users,
  PRIMARY KEY (permid, userid)
);

INSERT INTO users ( username, is_superuser ) VALUES
  ( 'ecarroll', true ),
  ( 'jbob', false );

INSERT INTO permissions (name) VALUES ('delete');
INSERT INTO permissions (name) VALUES ('select');

INSERT INTO user_permission (permid, userid) VALUES (2,2);

    现在我们编写一个交叉连接具有权限的用户的查询，这样我们就可以看到所有用户的权限。然后，我们将所有非超级用户的用户与链接表进行外部连接。我们推断他们has_permission是否是is_superuser OR /have entry in linking table/。这is_superuser是一个更好的来源：如果我们有那个 bool 集，我们甚至不再关心链接表中的内容。这使您的工作变得简单而高效。您不必使表格保持同步。

    SELECT
  users.userid,
  users.username,
  permissions.name,
  users.is_superuser OR user_permission.userid IS NOT NULL AS has_permission
FROM users
CROSS JOIN permissions
LEFT OUTER JOIN user_permission
  ON NOT is_superuser
  AND user_permission.userid = users.userid
  AND user_permission.permid = permissions.permid
ORDER BY users.userid;

    产品

     userid | username |  name  | has_permission 
--------+----------+--------+----------------
      1 | ecarroll | delete | t
      1 | ecarroll | select | t
      2 | jbob     | delete | f
      2 | jbob     | select | t
(4 rows)
    • 3

相关问题