允许postgres用户仅列出自己的数据库 [英] Allow postgres user to only list his own database

问题描述

我使用的是Postgresql服务器，我想禁止我的用户查看同一服务器上还有哪些其他数据库。

I'm using a postgresql server and I want to forbid my users to see what other databases are on the same server.

基本上是 \l 应该只列出自己的数据库。

Essentially a \l should only list his own database.

我很确定有一项权利需要从用户，但我在文档中找不到它。

I'm pretty sure that there is a right which I need to revoke from the user but I can't find it in the docs.

推荐答案

这似乎可行，但可能会导致无法预料的后果。它需要修改系统目录，这并不是一个好主意！

This seems to work but might have unforeseen consequences. It requires tinkering with system catalogues, which isn't really a good idea!

首先，您必须允许超级用户通过将其添加到postgresql配置中来更新系统目录。 ：

First off, you have to permit superusers to update system catalogues by adding this to your postgresql config:

allow_system_table_mods = on

并重新启动。

现在，您可以使用DDL语句修改系统目录了（您应该担心）。连接到一个用户数据库（测试一个是一个好主意），然后：

Now, you can use DDL statements to modify system catalogues (you should be afraid). Connect to one of the user databases (a test one would be a good idea) and:

alter table pg_catalog.pg_database rename to pg_database_catalog;
create view pg_catalog.pg_database as
  select oid, 1262::oid as tableoid, pg_database_catalog.*
  from pg_catalog.pg_database_catalog
  where has_database_privilege(pg_database_catalog.oid, 'connect');    
grant select on pg_catalog.pg_database to public;

现在，您应该发现，如果连接到那个数据库的权限较低-priv用户， \l 命令将只列出该用户可以连接的数据库。

You should now find that if you connect to that database as a low-priv user, the \l command will just list the databases that that user can connect to.

问题是您现在需要猜测用户最初连接到哪个数据库以从中获取其数据库列表。如果他们最初连接到自己的数据库，那么此时您可能已经完成。如果他们首先连接到 postgres 或 template1 ，那么您需要在该数据库上进行此更改。

The problem is you now need to guess which database the users connect to initially to fetch their database list from. If they connect to their own database initially, then you're probably done at this point. If they connect to postgres or template1 first, then you need to make this change on that database instead.

在我看来这应该可行，因为 pg_database 目录是由postgres后端直接使用oid而不是oid引用的。名称，因此将其移开并更改其中显示的行应该对它们不可见。特别是，您不能阻止服务器向用户区分不存在的数据库和没有连接特权的数据库。

It seems to me that this should work, since the pg_database catalog is referred to by postgres backends directly by oid, rather than by name, so moving it out of the way and changing which rows are shown in it should be invisible to them. In particular, you can't stop the server distinguishing to the user between a database not existing and them not having connection privilege.

我不会做出任何承诺这种变化不会使其他事情陷入困境。如果失败了，您就可以保留下来。

I'm not going to make any promises that this sort of change doesn't screw something else up down the line. If it breaks, you get to keep the pieces.

您可能想在模板数据库中进行此更改，并在将来从中创建用户数据库，然后停用 allow_system_table_mods 设置完成后（记住，这需要重新启动服务器）。

You probably want to make this change in a template database, and create user databases from that in future, and deactivate the allow_system_table_mods setting when you're done (which requires a server restart, remember).

此外，我对此进行了测试在9.0上：在我看来，它也应该在某些较早的版本上也可以使用，请小心。

Also, I tested this on 9.0: it seems to me it should work on some earlier versions too, caveat emptor.

这篇关于允许postgres用户仅列出自己的数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！