Thread: Restricting user -> database access.
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
"C. Bensend" <benny@bennyvision.com> writes:
> 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.
Huh? \c is checked exactly as tightly as a fresh connection --- it
*is* a fresh connection, as far as the server is concerned.
> IS there a way to limit a user's ability to connect
> to only THEIR database?
See "sameuser" option 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.
The above line does not allow connections to ANY database other than
bobsdb. If anyone can connect to anything else at all, it's because
you have additional pg_hba lines that you're not showing us.
> hostssl bobsdb a.b.c.d 255.255.255.255 ident sameuser
"sameuser" in the database column (column 2) was what I was talking
about. "sameuser" as an ident argument is a different concept
altogether.
regards, tom lane
On Tue, 21 Aug 2001, Tom Lane wrote:
> See "sameuser" option in pg_hba.conf.
I have. See below.
> > 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.
>
> The above line does not allow connections to ANY database other than
> bobsdb. If anyone can connect to anything else at all, it's because
> you have additional pg_hba lines that you're not showing us.
Before that, I have:
local all crypt
host all 127.0.0.1 255.255.255.255 crypt
The above lines should not allow ANYONE access, without a
password, correct? That's all I have above said lines.
In response to "you have additional pg_hba lines that you're
not showing us", that's all I have. Honestly.
> > hostssl bobsdb a.b.c.d 255.255.255.255 ident sameuser
>
> "sameuser" in the database column (column 2) was what I was talking
> about. "sameuser" as an ident argument is a different concept
> altogether.
OK. I accept that. While I appreciate your help profusely, you
still haven't said what I'm doing wrong.
As a test, I've configured pg_hba.conf as follows:
local all crypt
host all 127.0.0.1 255.255.255.255 crypt
hostssl balmer a.b.c.d 255.255.255.255 ident sameuser
hostssl ertz a.b.c.d 255.255.255.255 ident sameuser
host all 127.0.0.1 255.255.255.255 crypt
As user 'balmer' from the client machine, I can connect to
the 'balmer' database. Yay. :) And now, as balmer, I can
"\c ertz" and connect to the ertz database. Not so good. :(
I can do this, whether the authentication is ident or crypt.
Doesn't matter - once I've authenticated as a user, "\c" works.
Where am I going wrong?
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
"C. Bensend" <benny@bennyvision.com> writes:
> local all crypt
> host all 127.0.0.1 255.255.255.255 crypt
> hostssl bobsdb a.b.c.d 255.255.255.255 ident sameuser
> In response to "you have additional pg_hba lines that you're
> not showing us", that's all I have. Honestly.
The above lines say that anyone coming from the local machine (over
either a Unix socket or loopback IP) can get into any database if they
supply a valid Postgres userid and password. Anyone coming from a.b.c.d
(I assume this is *not* your local machine) can get into only bobsdb,
and only if the Postgres userid they specify matches what ident reports
as their Unix userid.
Perhaps you want "sameuser" instead of "all" in the first two lines.
You're correct that psql's \c doesn't re-prompt for the password if the
one originally given is still accepted. Offhand that does not strike me
as a security lapse.
> hostssl balmer a.b.c.d 255.255.255.255 ident sameuser
> hostssl ertz a.b.c.d 255.255.255.255 ident sameuser
> As user 'balmer' from the client machine, I can connect to
> the 'balmer' database. Yay. :) And now, as balmer, I can
> "\c ertz" and connect to the ertz database. Not so good. :(
Sure enough, that's what the config file says is allowed. Try
replacing these two lines with
hostssl sameuser a.b.c.d 255.255.255.255 ident sameuser
which I think is closer to the behavior you are looking for.
regards, tom lane