From: Thomas Jacob <jacob@internet24.de>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Best practice running a shared DB hosting server
Date: Wed, 13 Aug 2008 17:48:10 +0200
Message-ID: <1218642490.28547.50.camel@enterprise.ims-firmen.de> (raw)
In-Reply-To: <dcc563d10808130757o45cdeb45jaa87cc09d0bdd837@mail.gmail.com>
References: <1218635097.28547.28.camel@enterprise.ims-firmen.de>
<dcc563d10808130757o45cdeb45jaa87cc09d0bdd837@mail.gmail.com>
Thanks for your reply.
On Wed, 2008-08-13 at 08:57 -0600, Scott Marlowe wrote:
> Well, databases are designed to be accessed by people you trust to not
> do really stupid things that can affect the other users.
Well, in a shared hosting scenario this hope can easily
turn out to be in vain, but some sort of trust
in the user to for instance not overload the database
with huge cross joins is required, I agree.
> 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 was planing to create an extra role for each database
with the same name as the database and then grant that
role to each user for a single database.
That way I wouldn't have to configure the pg_hba.conf
for each user, and could still have several users for
each database.
The pg_hba line would look as follows
host samerole all localnetwork md5
Do you see any significant problems that could be caused by
this approach?
> Each user = a new database. Let them do what they want to in there.
What about the public schema? I've read some suggestions in various
archived mailing list to revoke the rights to
the public schema in the user databases, would
you recommend doing this? Why?
> 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.
To be sure, keeping users from logging into other
uses databases is the most important thing (and ensuring
they have sufficiently complex passwords)
from a security POV.
But it's also a question of privacy, it's nobody's
business what other databases and users exist
on the system but the superuser's, I think.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: jacob@internet24.de, scott.marlowe@gmail.com
Subject: Re: Best practice running a shared DB hosting server
In-Reply-To: <1218642490.28547.50.camel@enterprise.ims-firmen.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox