postgresql 9.1-通过函数访问表

本文介绍了postgresql 9.1-通过函数访问表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我有3个角色：超级用户，超级用户和用户。我有表数据和函数data_select和data_insert。I have 3 roles: superuser, poweruser and user. I have table "data" and functions data_select and data_insert.现在我要定义，只有超级用户才能访问表 data。 Poweruser和用户不能直接访问表数据，而只能通过函数。Now I would like to define, that only superuser can access table "data". Poweruser and user can not access table "data" directly, but only through the functions.用户只能运行data_select函数，poweruser可以同时运行data_select和data_insert。User can run only function data_select, poweruser can run both data_select and data_insert.因此，我可以创建用户alice，bob ...并继承它们的用户或poweuser特权。So then I can create users alice, bob, ... and inherits them privileges of user or poweuser.这实际上可以实现吗？我已经为此进行了第二天的战斗，却一无所获。 Is this actually achievable? I am fighting with this for the second day and not getting anywhere. 谢谢您的时间。推荐答案是的，这是超级用户可以是实际的超级用户， postgres 默认情况下。 我将普通用户的角色重命名为 usr ，因为 user 是保留字-不要"superuser" could be an actual superuser, postgres by default.I rename the role for plain users to usr, because user is a reserved word - don't use it as identifier.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;或者您可以创建没有登录名的守护程序角色来拥有函数并在表上保留各自的权利。 Or you could create daemon roles with no login to own the functions and hold the respective rights on the table. That would be even more secure.如果您选择这条路线，将会爱 更改默认权限 （在PostgreSQL 9.0中引入）。 此相关答案中的更多详细信息。If you are going this route, you will love ALTER DEFAULT PRIVILEGES (introduced with PostgreSQL 9.0). More details in this related answer.阅读本章编写安全定义器安全运行。Read the chapter Writing SECURITY DEFINER Functions Safely in the manual. 这篇关于postgresql 9.1-通过函数访问表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！