DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=LjVxOSQxgPPKCORDNCYvNnWVSS2r7QIp2lBbk5V96ZEr1BfMd+fGs5P99gv3RFkr57
	kDSgo8puXfzPEgqt3uuI1v1hKkdwoxdxFS1swbn1dMQiv8a/w1XZQXvO3sOyCivsR2Eg
	4XPmyoFgdL3ANMZakJXqpaZGN9tplgrhdNyrw=
Message-ID: <dcc563d10808130757o45cdeb45jaa87cc09d0bdd837@mail.gmail.com>
Date: Wed, 13 Aug 2008 08:57:39 -0600
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: "Thomas Jacob" <jacob@internet24.de>
Subject: Re: Best practice running a shared DB hosting server
Cc: pgsql-admin@postgresql.org
In-Reply-To: <1218635097.28547.28.camel@enterprise.ims-firmen.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <1218635097.28547.28.camel@enterprise.ims-firmen.de>

On Wed, Aug 13, 2008 at 7:44 AM, Thomas Jacob <jacob@internet24.de> wrote:
> Hello List,
>
> I was wondering if there is something like
> a best practice document for running
> PostgreSQL (probably 8.3.3) securely in a shared
> Web+DB hosting environment, where different
> people without any administrative relationship
> between them may run their databases on
> the same server.

Well, databases are designed to be accessed by people you trust to not
do really stupid things that can affect the other users.

I'd set up a db per user with pg_hba.conf set to only allow them to
log into the db of their own name.

> I am particularly interested in the
> role, permission and schema layout.

Each user = a new database. Let them do what they want to in there.

> Also I'm worried about the amount of information
> available to ordinary DB users. For instance,
> without revoking access to pg_catalog from
> PUBLIC all users can see the usernames,
> database names etc. of all other users. But
> revoking the right from pg_catalog doesn't
> seem to be an option, as this breaks
> several features of the psql utility and probably
> other things.

Hiding such things would only be security via obscurity and would
accomplish exactly nothing..  Actually keeping people from logging
into another user's database is much more important.  that you can do
with pg_hba.conf.