Restrict a role in postgres to access some schemas in a database and don't list any other databases

Question

I'm using PostgreSQL 8.3 under ubuntu 9.04. I access my database server using a console client or Pgadmin III. Currently, I need to restrict access to only a couple of schemas in a database and also don't show other databases in the server.

For example, my current postgres server has the following databases:

postgres-8.3-server |-- db1 | |-- sch_a | |-- sch_b | |-- sch_c | `-- sch_d |-- db2 | |-- sch_e | |-- sch_f | `-- sch_g `-- db3 `-- sch_h 

I need to restrict role "joe" to access only db2's sch_e and shc_g, and also don't show the other schemas in the same database. When listing databases the output should be:

postgres-8.3-server |-- db2 |-- sch_e `-- sch_g 

My current solution restricts schema and database access but I can still see the database name list. When I try to access any of them (except the one I'm allowed to) I receive a message about a specific entry not found at pg_hba.conf file, which is good but not ideal.

I'm currently checking the manual and googling for more information.

Thanks in advance

Answer 1

You can't restrict the ability to see which databases exist. Why does that matter, though, since you can restrict the ability to connect?

If you prefer a different error message, you might try letting people through pg_hba.conf and then restricting the CONNECT permission on the database itself (REVOKE CONNECT ON DATABASE foo FROM public; GRANT CONNECT ON DATABASE foo TO somerole;")