Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:

|-- 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:

|-- 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

share|improve this question

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

share|improve this answer
Thanks Magnus. The databases names are important because they have client names appended to them. Restricting database access with pg_hba.conf and the revoking connect are great but only gives database level restriction. Is there a way to restrict visibility per schema? To only list some "visible" schemas that the user (role) has privileges. – Humber Feb 22 '10 at 20:28

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.