Disable all operations (inluding listing with psql -l) on not owned databases for a role?

Question

We are running PostgreSQL 8.3 as the DB server for our ERP system. So far there was no necessity to create different databases under different users (roles in terms of 8.3) in postgres. And now it has appeared.

Question 1: Is it correct that a user with no superuser privilege can read/write only to its owned databases (assuming the user has the CREATEDB privilege)?

Question 2: How can I disable for a user the listing of all databases via the psql -l command? Even if the answer to the first question is yes then this listing is still available to an arbitrary user.

Thank you.

Answer 1

There's no way to turn off psql -l without possibly breaking things. You can use the sameuser configuration option in pg_hba.conf to only let users connect to their own databases.

Answer 2

Q1: No. This is all controlled by privileges at various levels. To write into a table, you need privileges on that table, etc. The only privileges on the database level (which the owner would have by default) are the ability to create schemas and temporary tables. That's probably not what you had in mind. You could probably set it up that way, but it's far from the default or the normal setup.

Q2: You could revoke the SELECT privilege on pg_database. But doing that it not really supported. I suggest you reconsider whether you really need that.