如何将视图的所有权限授予任意用户 [英] How to grant all privileges on views to arbitrary user

问题描述

如何向任意用户授予对所有函数和视图的读取/选择访问权限？

我使用 psql --user = postgres -d mydb -f myview.sql 创建几个函数和视图，然后运行：

 授予数据库上的所有权限mydb to myuser; 
  

已获得所有权限，我会期望 myuser 现在可以访问由 postgres 用户创建的函数和视图。但是，当我尝试使用 myuser 访问它们时，我得到一个permission denied for relation ...错误。为什么是这样？

解决方案

原因是您需要额外的权限才能访问视图或表。

它与以下函数不同： EXECUTE 特权默认授予 public 。 但该函数使用当前用户的权限执行。您可能对 SECURITY DEFINER 修饰符感兴趣， rel =nofollow noreferrer> CREATE FUNCTION 。但通常只需在所涉及的表上授予 SELECT 即可。

关于默认权限的每个文档：

根据对象的类型，初始默认权限可能
包括向 PUBLIC 授予一些权限。缺省值为没有public
表，列，模式和表空间的访问; CONNECT
特权和 TEMP 表创建特权; EXECUTE
函数的特权;和 USAGE 语言的特权。

您可能对此DDL命令感兴趣（需要Postgres 9.0 或更高版本）：

  GRANT选择在SCHEMA中的所有表public to myuser; 
  

当连接到有问题的数据库时，当然（参见@marcel的评论），以及作为用户具有足够的权限。您也可能对 DEFAULT PRIVILEGES 的设置感兴趣：

• 授予全部在数据库中的特定模式到PostgreSQL中的组角色

更详细地回答如何管理权限： p>

pgAdmin 具有更复杂的批量操作功能：

>

或者您可以查询系统目录以创建用于批量授予/撤销的DDL语句...

How do you grant read/select access on all functions and views to an arbitrary user?

I use psql --user=postgres -d mydb -f myview.sql to create several functions and views, and then I run:

GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

Having been granted all privileges, I would expect myuser to now have access to the functions and views created by the postgres user. However, when I try and access them with myuser, I get a "permission denied for relation..." error. Why is this?

解决方案

The reason is that you need additional privileges to access a view or table. Privileges on the database do not cover access to all objects in it.

It is different with functions: EXECUTE privilege is granted to public by default. But the function is executed with the privileges of the current user. You may be interested in the SECURITY DEFINER modifier for CREATE FUNCTION. But normally it is enough to grant SELECT on involved tables.

Per documentation about default privileges:

Depending on the type of object, the initial default privileges might include granting some privileges to PUBLIC. The default is no public access for tables, columns, schemas, and tablespaces; CONNECT privilege and TEMP table creation privilege for databases; EXECUTE privilege for functions; and USAGE privilege for languages.

You may be interested in this DDL command (requires Postgres 9.0 or later):

GRANT SELECT ON ALL TABLES IN SCHEMA public TO myuser;

While connected to the database in question, of course (see @marcel's comment below), and as a user with sufficient privileges. You may also be interested in the setting DEFAULT PRIVILEGES:

• Grant all on a specific schema in the db to a group role in PostgreSQL

More detailed answer how to manage privileges:

• How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

pgAdmin has a feature for more sophisticated bulk operations:

Or you can query the system catalogs to create DDL statements for bulk granting / revoking ...

这篇关于如何将视图的所有权限授予任意用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！