Skip site navigation (1) Skip section navigation (2)

Restricting user -> database access.

From: "C(dot) Bensend" <benny(at)bennyvision(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: Restricting user -> database access.
Date: 2001-08-22 00:24:27
Message-ID: Pine.BSO.4.33L2.0108211859190.32016-100000@fusion.bennyvision.com (view raw or flat)
Thread:
Lists: pgsql-admin
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(at)rage(dot)net




Responses

pgsql-admin by date

Next:From: Tom LaneDate: 2001-08-22 03:22:16
Subject: Re: Restricting user -> database access.
Previous:From: Tom LaneDate: 2001-08-21 23:45:40
Subject: Re: ERROR: Conditional NOTIFY is not implemented

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group