Hey folks,
I'm sorry to keep bombarding you folks with seemingly
simple questions, but PostgreSQL just doesn't seem to operate
in what _I_ think is a logical fashion (I == sysadmin, not DBA).
:(
The stats:
* PostgreSQL 7.1.2 server on OpenBSD 2.9
* PostgreSQL 7.1.2 clients on OpenBSD 2.8
The background:
I have multiple users on a webserver, that I need
to have psql access to the database machine, for their own
databases only.
The problem:
I don't see any real way to keep users from connecting
to their own databases via psql, and then using "\c <otherdb>"
to connect to someone else's database. Sure, they can't do
anything, but it's troubling to be able to use "\d" to list
the otherdb's schema.
The question:
IS there a way to limit a user's ability to connect
to only THEIR database? I have tried several methods:
(in pg_hba.conf)
hostssl bobsdb a.b.c.d 255.255.255.255 crypt
This works fine, asks for a password, connects the user, and
then they can "\c otherdb" without any problem.
hostssl bobsdb a.b.c.d 255.255.255.255 ident sameuser
I enabled identd on the client machine before attempting this.
This also works, does not ask for a password (that is
expected), and then they can "\c otherdb" with no problem.
I have no "trust" relationships in pg_hba.conf, so I don't
think I'm "leaking" permissions anywhere. I keep thinking
that PostgreSQL is more configurable than this, and that I'm
missing something blindingly simple (since I'm a sysadmin, not
a DBA). To get this functionality, do I need to bite the
bullet and run a postmaster for each database? If so, I
will. I just can't imagine that no one is using PostgreSQL
in a hosting environment and has _not_ run into this.
Any clues, hints, tire irons to the head muchly appreciated,
Benny
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
You see, we're leveraging the synergies of our existing open source
solution, without reliance on a single vendor.
Or in english: We use samba cause NT sucks ass.
--greg@rage.net