Обсуждение: Best practice running a shared DB hosting server

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.

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

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.

   Thanks & Best Regards,
           Thomas

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.

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.

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

On Mon, 2008-08-18 at 11:48 -0400, Robert Treat wrote:

> You'll likely want to make the database owned by that role.

My intention is to have some sort of "main"-user who'll be
able to do whatever they want (because they create
all the objects) and a number of additional users
that only have the rights the "main"-user grants them.

So I'd be using the db-named role only a way to
avoid specifying users and databases in pg_hba.conf.

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

I was thinking about that, but how could you enforce space limits
through that? At least on Linux, the quota systems works using
uids/gids, and the table space files will still be owned by the same
uid/gid as the postmaster processes, right?

Also I'm worried about what exactly will happen if you run out
of space?

> Have you tested this to make sure it works?

I did, the authentication code seems to be checking
role membership only, not whether or not this is
the active role. Hopefully this is working that way
by design.

> You would then need to disable accounts by
> revoking the login bit, or by setting up password expiry.

Well I'm creating the db-named role with option NOLOGIN
so that should have the same effect.

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

I've changed my approach by revoking rights to schema public
from PUBLIC in the template1, and then granting all rights
to again just to the "main" user, and usage rights to
the additional users.

So people aren't forced to use schemas.

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

Well, improvements that make PostgreSQL better suited to separating
databases for different users would surely help drive up
the number of users. There are lots people who can't
or don't want to afford their own database server while
still needing to have access to an SQL database.

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

Hmm, yes, why not, I'll see what I can do.

On Mon, Aug 18, 2008 at 10:38 AM, Thomas Jacob <jacob@internet24.de> wrote:
> I've changed my approach by revoking rights to schema public
> from PUBLIC in the template1, and then granting all rights
> to again just to the "main" user, and usage rights to
> the additional users.
>
> So people aren't forced to use schemas.

Not sure exactly what you mean.  Everything in pgsql lives in a
schema, whether it's public or another schema.  By judicious use of
the search_path settings you can make the use of such schemas nearly
transparent to the users, but they're still there.

On Mon, Aug 18, 2008 at 10:38 AM, Thomas Jacob <jacob@internet24.de> wrote:
> On Mon, 2008-08-18 at 11:48 -0400, Robert Treat wrote:
>
>> 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).

No, it's a way of preventing the wasting of countless man hours making
changes that accomplish exactly nothing in terms of SECURITY.  Now, it
may help with your particular business rules to have that information
hidden.  But if you think hiding who the other users are gives you any
real measure of security you are sorely mistaken.

> Well, improvements that make PostgreSQL better suited to separating
> databases for different users would surely help drive up
> the number of users. There are lots people who can't
> or don't want to afford their own database server while
> still needing to have access to an SQL database.

I agree that such changes might make it easier for some shops to adopt
postgresql, but I'm not sure it's a win in a cost / benefits analysis.
 Very few users actually need to hide user info in the system catalogs
etc from other users.  For the vast majority who want it it's not
something they need, just something they'd like.  I would much rather
have the hackers working on code to make postgresql more performent
and expanding functionality than hiding information.

On Mon, Aug 18, 2008 at 10:55:27AM -0600, Scott Marlowe wrote:
> On Mon, Aug 18, 2008 at 10:38 AM, Thomas Jacob <jacob@internet24.de> wrote:
> > On Mon, 2008-08-18 at 11:48 -0400, Robert Treat wrote:
> >
> >> 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).
>
> No, it's a way of preventing the wasting of countless man hours making
> changes that accomplish exactly nothing in terms of SECURITY.  Now, it
> may help with your particular business rules to have that information
> hidden.  But if you think hiding who the other users are gives you any
> real measure of security you are sorely mistaken.

Knowing who else is running their database
the same server can easily become a matter of security.

Relying on the "hiddeness" of usernames, databases, tables
etc. as a sole security measure is one thing. That's after
all what's classically called security through obscurity. However
I want security AND obscurity, or rather anonymity, if I use
a rented service.

A scenario:

PostgreSQL would be the first major piece of code without any
security holes. Just because they haven't been published yet,
doesn't mean there aren't any. Imagine a 0-day exploit
making its way round the Internet that lets you log into
any database, and a large shared database
server in a web-hosting environment. It's well-nigh certain
that somebody's web application will have security problem. If
that can be exploited, an attacker might gain access
to a user database. But if that attacker cannot find
out anything about other databases, maybe that 0-day exploit
wouldn't help them, because they would need the name
of database to connect. If my database and user names
are random strings of a certain minimum length, the
task of breaking into other databases might become
impossible.

But sure, security/anonomity are not the only thing that matters,
and at the end of the day it's up to the PostgreSQL developers
to decide what they want to work on.

>  Very few users actually need to hide user info in the system catalogs
> etc from other users.  For the vast majority who want it it's not

The vast majority of current users maybe. But then again, most people
use database systems (in the loosest possible sense ;-) in Web applications,
and most of these use MySQL today, in a shared hosting environment.

PostgreSQL has an excellent documentation that is also much logically constructed
then MySQL's, and let's not even talk about ACID, why not see as a
serious competitor to MySQL in the "lesser" realms.

This might not matter much to the professional database administrators though.

On Mon, Aug 18, 2008 at 10:49:49AM -0600, Scott Marlowe wrote:
> On Mon, Aug 18, 2008 at 10:38 AM, Thomas Jacob <jacob@internet24.de> wrote:
> > I've changed my approach by revoking rights to schema public
> > from PUBLIC in the template1, and then granting all rights
> > to again just to the "main" user, and usage rights to
> > the additional users.
> >
> > So people aren't forced to use schemas.
>
> Not sure exactly what you mean.  Everything in pgsql lives in a
> schema, whether it's public or another schema.  By judicious use of
> the search_path settings you can make the use of such schemas nearly
> transparent to the users, but they're still there.

Sorry, that was very vague. Some people might just be used
to there being an explicit public schema. This is not very
likely, agreed, but if having access to the public schema is not
a security risk, then there is no need to force users to create additional
schemas if they don't want to.

On Mon, Aug 18, 2008 at 12:24 PM, Thomas Jacob <jacob@internet24.de> wrote:
> On Mon, Aug 18, 2008 at 10:49:49AM -0600, Scott Marlowe wrote:
>> On Mon, Aug 18, 2008 at 10:38 AM, Thomas Jacob <jacob@internet24.de> wrote:
>> > I've changed my approach by revoking rights to schema public
>> > from PUBLIC in the template1, and then granting all rights
>> > to again just to the "main" user, and usage rights to
>> > the additional users.
>> >
>> > So people aren't forced to use schemas.
>>
>> Not sure exactly what you mean.  Everything in pgsql lives in a
>> schema, whether it's public or another schema.  By judicious use of
>> the search_path settings you can make the use of such schemas nearly
>> transparent to the users, but they're still there.
>
> Sorry, that was very vague. Some people might just be used
> to there being an explicit public schema. This is not very
> likely, agreed, but if having access to the public schema is not
> a security risk, then there is no need to force users to create additional
> schemas if they don't want to.

Right,  I'd just keep the public schema.  the only issue with it is
that by default any user who can log into a database can create things
there.  However, you can revoke those privileges from public then
explicitly grant them to the users of that database.

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

I've started a page as you suggested which at the moment contains only
some introductory mumblings and some details on my current approach,
comments and suggestions would be very welcome.

==>

  http://wiki.postgresql.org/wiki/Shared_Database_Hosting