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

postgresqlUbuntuubuntu-9.04

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

Best Answer

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;")

Related Topic