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

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.

Answer 2

re 1) yes, that's correct

re 2) I think it can be done by using revoke select on pg_database from public but I'm not sure what side-effects that will have.

Edit
This discussion might be interesting for you:
http://www.mail-archive.com/[email protected]/msg64005.html

Answer 3

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 4

http://wiki.postgresql.org/wiki/Shared_Database_Hosting

Postgres 8.4

Main case

We modify template1 to revoke all rights from "PUBLIC" to the public schema, to prevent access to the public schema of indiviudial customer databases by other customers.

psql -U postgres template1 -f - << EOT

REVOKE ALL ON DATABASE template1 FROM public;
REVOKE ALL ON SCHEMA public FROM public;
GRANT ALL ON SCHEMA public TO postgres;
CREATE LANGUAGE plpgsql;
EOT