授予对PostgreSQL中仅一个架构的访问权限 [英] Grant access to just one schema in postgreSQL

问题描述

我有一个用户 just_one_schema_user 。

在我的数据库中，我有两个模式： public 和 sample

In my database I have two schemas: public and sample

如何使该用户仅看到样本？

这就是我所做的：

在SCHEMA上使用GRANT示例给just_one_schema_user

在SCHEMA上撤消所有特权，从just_one_schema_user

但是用户仍然可以在 public 中列出表并查看其结构。

But the user still can list the tables in public and see their structures.

推荐答案

关于对表元数据的访问：

正如Islingre所说，

About access to the table metadata:

As Islingre commented, there is no good way to hide that information from users.

您将不得不拒绝用户访问 pg_class ， pg_namespace 和 pg_proc 以及类似的表格。如果在上将 allow_system_table_mods 设置为，则可以完成，并且PostgreSQL将继续运行，但是很多事情将不再起作用：

You would have to deny the user access to the pg_class, pg_namespace and pg_proc and similar tables. This can be done if you set allow_system_table_mods to on, and PostgreSQL will continue functioning, but a lot of things will no longer work:

• 使用 psql 实用程序命令，例如 \d 或 \dt

• Using the psql utility commands like \d or \dt

其他工具的类似工具

监视系统

基本上，您将无法再看到任何元数据。

Essentially, you won't be able to see any metadata any more.

没有办法允许用户只看到一些元数据，它是全部还是什么。

There is no way to allow a user to see only some of the metadata, it is all or nothing.

但这不是问题。没有充分的理由保留任何人的元数据-这是公共信息。

But that is not a problem. There is no good reason to keep metadata from anybody - that is public information.

PostgreSQL并不认为这是一个安全问题。仅仅因为我知道有一个表 customer 和一列 credit_card_number ，这使我无法更进一步地访问它

PostgreSQL doesn't consider that a security problem. Just because I know that there is a table customer with a column credit_card_number doesn't get me any closer to accessing it if permissions are set properly.

撤消从未授予的特权的撤销将无声无息。

A REVOKE that removes a privilege that has never been granted will silently do nothing.

模式 public 的使用权限特权授予 PUBLIC ，而不是 just_one_schema_user 。

The USAGE privilege on schema public is granted to PUBLIC, not just_one_schema_user.

使用此选项可显示<$ c $中的权限c> psql ：

\dn+

您正在寻找：

REVOKE CREATE, USAGE ON SCHEMA public FROM public;

我建议在 public ，仅扩展名。然后不要撤消使用情况，仅创建。

I would recommend storing no application data in public, only extensions. Then don't revoke USAGE, only CREATE.

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