Thread: Users can see each others' databases?

Hi. I'm using PostgreSQL for a database course. Each user has their own
database on the system. Each database is named the same as the user's
user name, and pg_hba.conf is set to require sameuser and a password.

Using pgadmin3 1.2.0 b1 and PostgreSQL 7.4.3 on Fedora Core 2, when a
user connects to their database and browses down the hieararchy, they
can "see" all the other user databases, and browse the list of tables,
etc. They can even see the contents of other user's tables!

Surmising that this was a result of the public schema, I revoked all
rights to public for the public schema in each database. This partly
works in that I'm able to not see the contents of other users' tables,
but I can still get a list of table names, and other information at that
level.

I'm guessing that I'm overlooking something obvious here. What do I need
to do so that users can't see anything from the other users in pgadmin3?
Or, if that's not possible, what can be done to minimize this problem?

Thanks.

Terry

On Tue, 21 Sep 2004 17:47:32 -0500, Terry Letsche <terry@letsche.net> wrote:
> Hi. I'm using PostgreSQL for a database course. Each user has their own
> database on the system. Each database is named the same as the user's
> user name, and pg_hba.conf is set to require sameuser and a password.

First off, go (re)read the docs for pg_hba.conf carefully!  Things
like sameuser and password are not anded, they are
first-match-succeeds!  So, if you have "... ident sameuser" in your
pg_hba.conf before the password entry, passwords will not be required.

> I'm guessing that I'm overlooking something obvious here. What do I need
> to do so that users can't see anything from the other users in pgadmin3?
> Or, if that's not possible, what can be done to minimize this problem?

I suspect that in order to not be able to see other users' tables, you
must revoke permissions on the pg_catalog schema (and for the
enterprising users the information_schema schema too I suspect), which
is where all the views and whatnot that pgadmin queries to list tables
live.  If this is for a course, I'd bet on students being enterprising
when trying to circumvent access restrictions :)

--   -- Cheetah

Cheetah wrote:

> 
> I suspect that in order to not be able to see other users' tables, you
> must revoke permissions on the pg_catalog schema (and for the
> enterprising users the information_schema schema too I suspect), which
> is where all the views and whatnot that pgadmin queries to list tables
> live.  If this is for a course, I'd bet on students being enterprising
> when trying to circumvent access restrictions :)

Cheetah is completely right. Access restrictions on schema information 
come up from time to time, without a viable proposal how to implement 
it. Won't happen in the near future, and isn't supposed to be critical.

Regards,
Andreas