Re: Best practice running a shared DB hosting server

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.