创建具有登录名（用户）的 PostgreSQL 9 角色只是为了执行函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12712235/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create PostgreSQL 9 role with login (user) just to execute functions
提问by Ignacio
I have been looking for this for years and I have tried everything on the web with no success.
我多年来一直在寻找这个，我在网上尝试了一切，但都没有成功。
I am able to do it in MSSQL, but I didn′t find a way to do it in PostgreSQL.
我可以在 MSSQL 中做到这一点，但我没有找到在 PostgreSQL 中做到这一点的方法。
What I want to achieve is just create a role with login that cannot create, drop or alter databases, functions, tables or anything else. Just select specific functions.
我想要实现的只是创建一个无法创建、删除或更改数据库、函数、表或其他任何内容的登录角色。只需选择特定的功能。
For example, if I have a table called costumer and two functions called return_customers()and return_time()I just want a role with login that are able to select return_customers()and select return_time(). Nothing more than that.
例如，如果我有一个名为costumer 的表和两个调用的函数return_customers()，return_time()我只想要一个能够选择return_customers()和的登录角色select return_time()。仅此而已。
Thank you very much for supporting this useful web site!
非常感谢您支持这个有用的网站！
回答by cdhowie
Execute this connected to the database you want to configure.
执行此连接到要配置的数据库。
-- Create the user.
CREATE ROLE somebody WITH LOGIN PASSWORD '...';
-- Prevent all authenticated users from being able to use the database,
-- unless they have been explicitly granted permission.
REVOKE ALL PRIVILEGES ON DATABASE foo FROM PUBLIC;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM PUBLIC;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM PUBLIC;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM PUBLIC;
-- Allow the user to only use the specified functions.
GRANT CONNECT ON DATABASE foo TO somebody;
GRANT EXECUTE ON FUNCTION return_customers(), return_time() TO somebody;
If you have more schemas than "public" then you will need to add those to the two REVOKE ALL PRIVILEGES ON ALL ...statements.
如果您有比“public”更多的模式，那么您需要将它们添加到两个REVOKE ALL PRIVILEGES ON ALL ...语句中。
Do not forget that the functions must have been created with SECURITY DEFINERor this user will still be unable to execute them, as the contents of the function will be executed with the permissions of this user, instead of the user who created the function.
不要忘记函数必须是用创建的，SECURITY DEFINER否则这个用户仍然无法执行它们，因为函数的内容将在这个用户的权限下执行，而不是创建函数的用户。
See:
看：
CREATE FUNCTIONparticularlySECURITY DEFINERGRANTboth for adding users to roles and for assigning access rights to tables, sequences, etcREVOKECREATE ROLE
CREATE FUNCTION特别SECURITY DEFINERGRANT用于将用户添加到角色以及分配对表、序列等的访问权限REVOKECREATE ROLE
