Re: Best practice running a shared DB hosting server - Mailing list pgsql-admin

On Wednesday 13 August 2008 11:48:10 Thomas Jacob wrote:
> 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.
>

You'll likely want to make the database owned by that role. And if you really
want, you should create a new tablespace and assign each new database to that
tablespace. This would allow you to enforce some space limits at the OS level
for each db.

> 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?
>

Have you tested this to make sure it works? I am wondering if you will need to
do an alter user set role "role", but if you can get that bit worked out, it
would probably be a good scheme. You would then need to disable accounts by
revoking the login bit, or by setting up password expiry.

> > 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?
>

I dont think this is neccessary... in your environment it's mostly helpful in
cases where someone logs into a database they shouldn't log into.  I suppose
the safe thing to do is to try it and see if users complain about usage
issues... they might not.

> > 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.

Yes, I think the whole "security through obscurity" argument is a cop out to
get around postgresql's design choices (in this perticular instance anyway,
in many cases its valid).  unfortunatly, it's one you can't do much about. If
it isn't too ugly, you could give users database names based on hashs so it
isnt obvious who has which databases, but if users can create thier own
roles, that will be less useful.

BTW, I'd encourage you to start a page on the wiki site to track the specific
issues you run into, and specific fixes your using. Would certainly be
helpful to others and probably yourself as a reference on the topic.

HTH

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL