如何在PostgreSQL中实现业务逻辑权限？

Question

假设我有一个项目表：

CREATE TABLE items
(
    item serial PRIMARY KEY,
    ...
);

现在我想介绍每个项目的“权限”概念（请注意，我并不是在谈论数据库访问权限，而是该项目的业务逻辑权限）。每个项目都有默认权限和每个用户的权限，这可能会覆盖默认权限。

我尝试思考如何实现这个功能，并想出了以下解决方案：

1）布尔解决方案

为每个权限使用一个布尔列：

CREATE TABLE items
(
    item serial PRIMARY KEY,

    can_change_description boolean NOT NULL,
    can_change_price boolean NOT NULL,
    can_delete_item_from_store boolean NOT NULL,
    ...
);

CREATE TABLE item_per_user_permissions
(
    item int NOT NULL REFERENCES items(item),
    user int NOT NULL REFERENCES users(user),

    PRIMARY KEY(item, user),

    can_change_description boolean NOT NULL,
    can_change_price boolean NOT NULL,
    can_delete_item_from_store boolean NOT NULL,
    ...
);

: 每个权限都有名称。 : 有数十个权限，这会显著增加列数，并且您必须在两个表中分别定义它们。

2) 整数解决方案

使用整数并将其视为位字段（即，位0用于can_change_description，位1用于can_change_price等，并使用位运算设置或读取权限）。

CREATE DOMAIN permissions AS integer;

优点: 非常快。

缺点: 您必须在数据库和前端界面中跟踪哪个位表示哪个权限。

3) 位字段解决方案

与2）相同，但使用bit(n)。可能具有相同的优点和缺点，可能略慢。

4) 枚举解决方案

使用枚举类型来表示权限：

CREATE TYPE permission AS ENUM ('can_change_description', 'can_change_price', .....);

然后为默认权限创建一个额外的表：

CREATE TABLE item_default_permissions
(
    item int NOT NULL REFERENCES items(item),
    perm permission NOT NULL,

    PRIMARY KEY(item, perm)
);

并将每个用户的定义表更改为：

CREATE TABLE item_per_user_permissions
(
    item int NOT NULL REFERENCES items(item),
    user int NOT NULL REFERENCES users(user),
    perm permission NOT NULL,

    PRIMARY KEY(item, user, perm)    
);

优点: 方便命名单个权限（无需处理位位置）。

缺点: 即使只是检索默认权限，也需要访问两个额外的表：首先是默认权限表，其次是存储枚举值的系统目录。

特别是因为必须为每个项的每个页面视图检索默认权限，最后一种选择可能会对性能产生显着影响。

您能想到其他选择吗？

应采取哪种方法？

请注意：此问题已在DBA上重新发布。

- JohnCand

---

对象中嵌入枚举权限数组，其中该数组仅包含用户拥有的权限。使用GiST数组索引支持进行成员测试。但要小心测试-不确定GiST索引是否可用于负面测试，即“没有权限x...”。此外，缓存默认位置。如果您没有使用像redis或memcached这样的缓存层，现在就是使用的时候了。 - Craig Ringer

@CraigRinger：那是另一种解决方案，但我被教导应该避免使用数组，因为它们违反了第一范式。此外，对我来说，使用枚举数组似乎*丑陋而复杂，尽管这显然是主观的。但是，例如，一个由50个不同的枚举权限组成的数组在那一行中占用了相当数量的字节，不是吗？ - JohnCand

如果您期望有许多不同的权限，则数组不适用，我会选择 bit(n) 并提供良好的文档。使用 bigint 也不适合具有如此多权限位，因为您只有 64 位可供使用。顺便说一句，我同意枚举数组很丑陋，但您正在尝试进行一种优化，牺牲了性能以换取规范化，否则您将只拥有一个子表。所以您已经忽略了“干净”的方式（在我看来是明智的）。 - Craig Ringer

我的建议是创建一个表，清晰地定义每个权限位为 主键（object_type，bit_position）。你可以使用枚举，但是没有太多好处，而且枚举具有浮点内部值，以允许非破坏性中间值插入，因此它们并不是真正理想的选择。然后，有一个代码生成器，从你的数据库权限表中创建一个类。或者从代码中创建权限表的SQL。或者从版本控制中间表示创建两者。无论如何。 - Craig Ringer

Answer 1

这将取决于您需要哪个级别的权限

基于角色 - 不是用户

基于用户 -（难以维护）

或两者的组合

用户和角色...

我们系统上的以下内容

用户表-> 包含用户名等

角色-> 角色列表，例如办公室支持，机械师

安全级别-> 级别列表，例如只读，管理员

应用程序功能表-> 包含可以应用权限的功能，例如Can_Edit_Users

应用程序安全表-> 用户名+应用程序功能ID、角色和是/否权限的位-或使用安全级别的更复杂的角色级别的整数。

这提供了最大的灵活性，但构建UI可能会很困难。

在应用程序中，我们一次获取用户权限，并执行函数检查他们是否具有权限

例如，CurrentUser.HasPermissions("Can_Edit_Users")

public bool HasPermissions(string Permission)
{
return this.Permissions.Any(p=>p.FunctionName==Permission && IsAllowed==true);
}

- Traci