授予用户在PostgreSQL上访问表子集的权限

Question

我知道可以使用视图来授予对表中某些属性的访问权限。但是如何仅授予特定元组的访问权限呢？比如我有一个注册学生的表，其中包含用户名和其他一些属性，如学位状态，我该如何授予用户A只能从表中选择与用户名A相对应的元组的访问权限？我参加了一场数据库考试，正在研究一些过去的试卷，我遇到了这个问题，但我不知道该怎么回答它，也找不到我的书“数据库系统：数据库设计、实现和管理实践方法”中如何做到这一点。

谢谢任何帮助！ 马特

- matthewbpt

---

我一直在为我想做的一些事情进行研究，偶然发现了http://archives.postgresql.org/pgsql-hackers/2009-10/msg01346.php; 这是一个非常有趣的观点，可以补充下面的答案... - alex

Answer 1

假设你已经获得：

Table items (item_id, ...)
Table users (user_id, ...)
Table users_permissions( user_id, item_id, perm_type )

您可以创建如下 VIEW：

SELECT i.*, p.perm_type 
FROM items JOIN users_permissions USING (item_id) 
WHERE user_id = get_current_user_id();

用户可以从这个视图中进行选择，但不能移除限制权限的WHERE和JOIN。

get_current_user_id()函数可能是主要问题;)

- bobflux

---

如果每个“真实”的用户都使用不同的Postgres用户帐户，那么get_current_user_id()就是current_user，这应该很容易。 - user330315

是的。每个“真正”的用户确实使用不同的Postgres用户帐户，这很麻烦，但另一方面，这是唯一的方法来强制执行DB权限。拥有无数个DB用户是主要问题。如果您只有10个用户，请继续。 - bobflux

谢谢，这正是我所需要的。我不知道这个函数，非常有用 :) - matthewbpt

Answer 2

按照peufeu的回答，Postgresql中当前用户的名称可通过current_user函数获得。因此，一个视图

CREATE VIEW available_bigtable AS
SELECT * FROM bigtable
WHERE username = current_user;

看起来它可以满足你的需求。在视图上授予SELECT权限给所有人，但在底层的bigtable上除管理员外不授予权限。

- Andrew Lazarus

---

非常有用，感谢您的帮助，这个 current_user 函数正是我所需要的 :) - matthewbpt

“接受”按钮一定要在某个地方。 :) - Andrew Lazarus

Answer 3

Veil 项目为 PostgreSQL 提供了一种行级访问控制框架。

- Peter Eisentraut

Answer 4

如何创建一个函数，该函数接受用户ID并返回他可以访问的行的子集？

CREATE FUNCTION user_items(integer) RETURNS SETOF items AS $$
    SELECT * FROM items WHERE user_id = $1
$$ LANGUAGE SQL;

SELECT * FROM user_items(55); # 55 being the user id

编辑 再考虑一下，这可能会导致相当大的性能损失，因为 user_id 条件将应用于整个数据集，而不是任何其他“用户层面”的条件。

例如，SELECT * FROM user_items(55) WHERE id=45 将首先过滤整个用户物品表，然后才在该子集上查找 ID。

使用视图，查询规划器可以决定评估条件的最佳顺序（他可能会首先过滤 ID，然后再过滤用户 ID）。当使用像我建议的函数时，Postgres 无法做到这一点。

- shesek