使用登录名（用户）创建PostgreSQL 9角色只是为了执行功能 [英] Create PostgreSQL 9 role with login (user) just to execute functions

问题描述

多年来，我一直在寻找这种方法，但是我在网络上尝试了所有方法都没有成功。

I have been looking for this for years and I have tried everything on the web with no success.

我能够在MSSQL中做到这一点，但是我没有

I am able to do it in MSSQL, but I didn´t find a way to do it in 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.

例如，如果我有一个名为costumer的表和两个名为 return_customers（）的函数，和 return_time（）我只想要一个具有登录名的角色，该角色能够选择 return_customers（）和选择return_time（）。仅此而已。

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.

非常感谢您支持这个有用的网站！

Thank you very much for supporting this useful web site!

推荐答案

执行此操作以连接到要配置的数据库。

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;

如果架构多于 public，则需要将其添加到两个<$中c $ c>在所有... 语句上撤消所有特权。

If you have more schemas than "public" then you will need to add those to the two REVOKE ALL PRIVILEGES ON ALL ... statements.

不要忘记函数必须是 SECURITY DEFINER 创建的http://www.postgresql.org/docs/current/static/sql-createfunction.html>，否则此用户仍然是无法执行它们，因为函数的内容将在该用户（而不是创建函数的用户）的权限下执行。

Do not forget that the functions must have been created with SECURITY DEFINER or 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.

请参阅：

• 创建功能 ，尤其是安全定义


• GRANT 既用于向角色添加用户，又用于分配访问权限到表格，序列等


• 撤销


• 创建角色

• CREATE FUNCTION particularly SECURITY DEFINER
• GRANT both for adding users to roles and for assigning access rights to tables, sequences, etc
• REVOKE
• CREATE ROLE

这篇关于使用登录名（用户）创建PostgreSQL 9角色只是为了执行功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！