Allow postgres user to list only their own database

Question

I am using a postgresql server and I want to prevent my users from seeing which other databases are on the same server.

Essentially, \l should only specify its own database.

I am sure that there is a right that I need to revoke from the user, but I can not find it in the documents.

Answer 1

This seems to work, but may have unintended consequences. This requires messing with system directories, which is actually not a good idea!

First of all, you must allow superusers to update system directories by adding this to the postgresql configuration:

 allow_system_table_mods = on 

and restart.

Now you can use DDL statements to change system directories (you should be scared). Connect to one of the user databases (a test 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; 

Now you should find that if you connect to this database as a user with a low level of privacy, the \l command will simply list the databases to which this user can connect.

The problem is that now you need to guess which database users are connecting to first to retrieve their database list. If they connect to their own database first, then you are probably done. If they connect to postgres or template1 , then you need to make this change in this database.

It seems to me that this should work, since the pg_database directory is mentioned by backgends servers directly using oid, and not by name, so moving it to the side and changing the lines in it should be invisible to them. In particular, you cannot stop a server other than the user between the existing database and the lack of connection rights.

I am not going to make any promises so that this kind of change does not hang something else along the line. If it breaks, you can hold pieces.

You will probably want to make this change to the template database and create user databases in the future and disable the allow_system_table_mods parameter when you are done (which requires a server restart, remember).

In addition, I tested it on 9.0: it seems to me that it should work with some earlier versions, caveat emptor.

Answer 2

There is no such setting in pgsql. There are settings that prohibit users from connecting to databases that they do not need (grant / withdraw a connection). The ability to see the database there does not really matter. Ability to connect / edit rights, etc.

Answer 3

I would suggest that this could have negative consequences for the user, such as the inability to connect to the database, since the system does not have access to the system tables, but is not sure. But as far as it turns out which table is being canceled, this is a good general way to see what psql meta commands do:

To find out what \l does, you can also use the -E flag from the command line with psql.

 ~$ psql -E -c '\l' ********* QUERY ********** SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collation", d.datctype as "Ctype", pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges" FROM pg_catalog.pg_database d ORDER BY 1; ************************** 

Therefore, if the user does not have access to pg_database, they will not be able to use the \ l command.