postgresql 9.1 - 通过函数访问表

Question

我有 3 个角色: super 用户、高级用户和用户。我有表“数据”和函数 data_select 和 data_insert。

现在我想定义，只有 super 用户才能访问表“数据”。 Poweruser 和 user 不能直接访问表“数据”，只能通过函数访问。

用户只能运行函数 data_select，poweruser 可以同时运行 data_select 和 data_insert。

然后我可以创建用户 alice、bob...并继承他们的用户或 poweuser 权限。

这真的可以实现吗？我已经连续第二天与此作斗争，但一无所获。

感谢您的宝贵时间。

Answer 1

是的，这是可行的。

“ super 用户”可以是一个实际的 super 用户，默认情况下是postgres。我将普通用户的角色重命名为 usr，因为 user 是保留字 - 不要将其用作标识符。

CREATE ROLE usr;
CREATE ROLE poweruser;
GRANT usr TO poweruser;  -- poweruser can do everything usr can.

CREATE ROLE bob PASSWORD <password>;
GRANT poweruser TO bob;

CREATE ROLE alice PASSWORD <password>;
GRANT usr TO alice;

REVOKE ALL ON SCHEMA x FROM public;
GRANT USAGE ON SCHEMA x TO usr;

REVOKE ALL ON TABLE x FROM public;
REVOKE ALL ON TABLE y FROM public;

CREATE FUNCTION
  ...
SECURITY DEFINER;

REVOKE ALL ON FUNCTION ... FROM public;
GRANT EXECUTE ON FUNCTION a TO usr;
GRANT EXECUTE ON FUNCTION b TO poweruser;

或者您可以在没有登录的情况下创建守护进程角色来拥有这些功能并拥有对表的相应权限。这样会更安全。

如果你走这条路，你会喜欢 ALTER DEFAULT PRIVILEGES (在 PostgreSQL 9.0 中引入)。 More details in this related answer .

阅读章节Writing SECURITY DEFINER Functions Safely在手册中。