撤消对角色的Postgres数据库的访问权限 [英] Revoke access to postgres database for a role

问题描述

我为仅执行某些存储功能的特定用户创建了单独的角色 newrole和新架构 newschema。我设法撤消了当前数据库对模式 public的访问。

以 newrole身份登录，我仍然可以像这样访问postgres数据库：

 选择*来自pg_user 
  

我想撤消对postgres数据库的所有访问权限，并尝试以下操作不起作用：

 撤消对数据库的所有权限postgres FROM newrole 
  

以newrole身份登录后，我仍然可以读取postgres数据库。

如何撤消对postgres admin数据库的任何访问权限？

我已经搜索了很长时间，但没有找到有关访问postgres管理数据库。

TIA，

解决方案

与数据库 postgres 有关。相反，您想操纵当前数据库的目录。每个数据库都有关于模式 pg_catalog 中所有对象以及目录 information_schema 中符合标准格式的所有信息的目录，因此，您应该限制对相关角色以及 public 角色的访问，因为每个角色也是该角色的成员：

 从纽洛撤销SCHEMA上的所有特权pg_catalog; 
从公共场所撤消SCHEMA上的所有特权pg_catalog； 
撤消SCHEMA上的所有特权information_schema from newrole; 
撤消SCHEMA上的所有特权information_schema from public; 
  

但是，系统并不总是遵守这种全面限制，目录适用于原因并在数据库中提供重要功能。

通常，除非您真的知道自己在做什么，否则您通常不想摆弄目录。 / p>

I have created a separate role "newrole" and new schema "newschema" for a certain user that should only execute some stored functions. I have managed to revoke access to schema "public" for the current database.

Logged in as "newrole" I still have access to postgres database like this:

SELECT * FROM pg_user

I want to revoke all access to the postgres database and tried following that not work:

REVOKE ALL ON DATABASE postgres FROM newrole

When logged in as newrole I can still read the postgres database.

How do I revoke any access to the postgres admin database?

I have searched a long time but not found anything regarding access to the postgres admin database.

TIA,

解决方案

This issue has nothing to do with database postgres. Instead, you want to manipulate the catalog of the current database. Every database has a catalog of information on all objects in schema pg_catalog, and in standards-compliant form in schema information_schema, so you should restrict access to those for the role in question and also for the public role because every role is also member of that role:

REVOKE ALL PRIVILEGES ON SCHEMA pg_catalog FROM newrole;
REVOKE ALL PRIVILEGES ON SCHEMA pg_catalog FROM public;
REVOKE ALL PRIVILEGES ON SCHEMA information_schema FROM newrole;
REVOKE ALL PRIVILEGES ON SCHEMA information_schema FROM public;

However, the system does not always honour this accross-the-board restriction, the catalogs are there for a reason and provide important functions in the database. Particularly functions may still execute.

In general, you do not want to fiddle with the catalogs unless you really know what you are doing.

这篇关于撤消对角色的Postgres数据库的访问权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！