postgresql - 允许一些角色更新列，如果它是 NULL，则允许其他一些角色

Question

我在表中有一列 A(类型 int)，在插入其他值时，该列的数据不可用。我不想拆分表，因为没有其他真正的理由这样做。我正在数据库级别实现特权分离。

只有某些用户(属于 A 类)应该能够随时修改 column A。但是其他用户(在 B 类中，不一定与 A 类互斥)如果尚未设置其值，则应该能够更新该列，即。如果它是 NULL。
我正在使用 PostgreSQL 9.2.4。

我怎样才能做到这一点？触发器？规则？还有别的吗？

Answer 1

only certain users should be able to modify column A
...
other users should be able to update the column if ... it is NULL.

从 public 和其他不应该有特权的人撤消 UPDATE(和 DELETE ?!?)。

REVOKE UPDATE ON TABLE tbl FROM public;
REVOKE UPDATE ON TABLE tbl FROM ...

创建一个(组)角色 some_users 允许更新 col_a(除此之外别无其他):

CREATE ROLE some_users;
GRANT SELECT, UPDATE (col_a) ON TABLE tbl TO some_users;

为什么选择 SELECT？ The manual on GRANT :

In practice, any nontrivial UPDATE command will require SELECTprivilege as well, since it must reference table columns to determinewhich rows to update, and/or to compute new values for columns.

这样，您就有了一个单点，您可以在其中分配列的权限。
创建另一个(组)角色 certain_users 可以做所有 some_users 可以做的事情(加上更多):

CREATE ROLE certain_users;
GRANT some_users TO certain_users;

根据需要将这些角色的成员资格授予用户角色:

GRANT some_users TO lowly_user;
GRANT certain_users TO chief_user;

创建条件触发器，类似于@Daniel provided , 但在另一个条件下使用 pg_has_role() :

CREATE TRIGGER tbl_value_trigger
BEFORE UPDATE ON tbl
FOR EACH ROW
WHEN (OLD.col_a IS NOT NULL AND NOT pg_has_role('some_users', 'member'))
EXECUTE PROCEDURE always_fail();

使用触发函数:

CREATE FUNCTION always_fail()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   -- To fail with exception, rolling back the whole transaction
   -- use this instead:
   -- RAISE EXCEPTION 'value is not null';
    
   -- Do nothing instead, letting the rest of the transaction commit.
   -- Requires BEFORE trigger.
   RAISE WARNING 'col_a IS NOT NULL. User % is too ambitious!', current_user;
   RETURN NULL;  -- effectively aborts UPDATE
END
$func$;

现在:

• 公众不能完全更新表格。
• some_users 和 certain_users 被允许更新列 col_a(而不是其他)，
• 只有来自 certain_users 的更改才会通过一次 col_a is NOT NULL。

注意 super 用户自动成为任何组的成员。因此，NOT pg_has_role(...) 启用 super 用户的形式，而逆逻辑可能会禁止 super 用户更新列。