Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
add comment

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

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

Your Answer

 
discard

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.