1

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

Improve this question

1 Answer 1

Reset to default
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;")

Improve this answer
1
  • 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, 2010 at 20:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .