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


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

  • 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 clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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