PostgreSQL中的权限 [英] Permission in PostgreSQL

问题描述

我想在PostgreSQL数据库上运行查询。查询如下：

I want to run a query on a PostgreSQL database. The query is as follows:

select * from pg_stat_activity;

但是我希望由只有阅读权限的用户来完成。不应允许用户在表中进行选择，因为仅是查看谁连接到数据库，而不能看到诸如 Passwords 之类的表。

But I want this to be done by a user who only has read permission. The user should not be allowed to do select in the tables because it is only to see who is connected to the database, not being able to see tables such as Passwords for example.

我只向用户授予查看系统目录表的权限。

I gave permission only to the user to see the tables of the system catalog.

用户连接到数据库，他的IP地址不会出现，并且在查询列中显示不允许他查看他已执行的查询。

But when a different user connects to the database, his IP address does not appear, and in the query column it says he is not allowed to see what query he has done.

我该如何

推荐答案

是否具有PostgreSQL v10或更高版本？ ，您可以将角色 pg_read_all_stats 授予用户。这样可以查看所有统计数据，尤其是 pg_stat_activity 中的所有条目。

If you have PostgreSQL v10 or better, you can grant the role pg_read_all_stats to the user. This enables to see all statistics data, in particular all entries in pg_stat_activity.

对于低于v10的版本，您可以可以创建属于超级用户并运行查询的 SECURITY DEFINER 函数。您应该注意文档必须说一下安全定义器函数。

For versions older than v10, you could create a SECURITY DEFINER function that belongs to a superuser and runs the query. You should observe what the documentation has to say about SECURITY DEFINER functions.

这篇关于PostgreSQL中的权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！