en: November 2008 Archives

The question

Few days ago I wanted to configure a postgresql server so that users connecting to their database would not be able to see other databases. Among other things, I wanted to restrict the usage of :

psql -l database user

This command will retrieve the list of database on the server, even if the user has no access right to them. If you issue this command, you'll et something like :

                   List of databases
              Name               |  Owner   | Encoding  
---------------------------------+----------+-----------
 database_1                      | postgres | UTF8
 database_2                      | user_foo | UTF8
 ...

The solution

Well at least one solution : in each database, the special virtual table pg_database (see here for the documentation) contains the list of all the databases hosted on the server. So if we remove access rights on this table, users won't be able to list the databases anymore.

But wait, this table is available in every databases. So we need to remove these access rights on all databases. So far so good.

Oh but what if a user creates a new database ? Yes, this new database will have this pg_database table, with default access rights, which allow anybody to list its content, thus getting the list of existing databases on the server.

So what we need is to change the access rights of the pg_database on newly crated databases. How do we do that ? by connecting to the special database template1 (see here for the documentation). This database contains the default content (and their associated rights) that are used to populate newly created databases. So in addition to changing the access rights of pg_database on every databases, we'll need to do the same on template1.

Now how do we change the access right? What we want is deny users the right to get the database list. That is, extracting the database list from pg_database. That is, select'ing the data of this table. So we simply need to revoke the select permission on the table, for everybody. We do that with this command :

revoke select on pg_database from public;

Execute this query on all the databases of your server, plus on the special template1 database. Then, trying to list the databases will return :

psql -l database user
ERROR:  permission denied for relation pg_database