PostgreSQL users on webhosting

In a typical setup, you might do:

Edit pg_hba.conf to allow connections to the database "sameuser" which
is a special word meaning that the user can only connect to a database
of the same name.

Then, for each webhosting account you make (let's say the user is named
"foo" with password "bar"), execute the following SQL:

=# CREATE DATABASE foo;
=# CREATE USER foo WITH PASSWORD 'bar';

That works for most situations.

However, for truly good seperation, I recommend that you run a seperate
instance of postgresql (with a seperate $PGDATA directory) for each
user, and run it under the UID of that user. It requires a little more
disk space per account, but in a dollar amount it's virtually zero with
today's disk prices. You will be able to tie the user into filesystem
quotas, etc., much more easily, and also you could tune the DBs to the
individual users if needed.

Regards,
Jeff Davis

Jeff Davis wrote:

However, for truly good seperation, I recommend that you run a seperate
instance of postgresql (with a seperate $PGDATA directory) for each
user, and run it under the UID of that user. It requires a little more
disk space per account, but in a dollar amount it's virtually zero with
today's disk prices. You will be able to tie the user into filesystem
quotas, etc., much more easily, and also you could tune the DBs to the
individual users if needed.

Out of curiosity, what kind of performance hit (whether CPU, memory,
disk activity) is incurred with having a lot of postmasters running in
this kind of a setup versus one postmaster with lots of databases? We
typically run one postmaster for a lot of separate web applications, but
I like the notion of a instance-per-user (for both security and
maintenance). In the case of having several "big" databases on one
server, would tuning stragegies need to keep in mind the settings of
other instances, or would you just tune each one as if it were the only
one on the box and let the OS deal with memory+disk load of multiple
instances?

/hope this question makes sense, waiting for coffee to kick in

On Jan 5, 2005, at 9:49 AM, Alan Garrison wrote:

Out of curiosity, what kind of performance hit (whether CPU, memory,
disk activity) is incurred with having a lot of postmasters running in
this kind of a setup versus one postmaster with lots of databases? We
typically run one postmaster for a lot of

You'd have to have separate shared buffers for each which would eat
away from the filesystem cache. Not to mention overhead of having
many more PG's running (in terms of just processes htat need to be
managed and memory used by each). You'd also have to have the users
connect to PG on an alternate port and that may irritate some.

In a web hosting (multi-user) environment it may make more sense. but
you could also just give each user his own db and only allow sameuser
access to each db via pg_hba.conf.

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/

Benefits of multiple instances:
(1) Let's say you're using the one-instance method and one of your web
users is a less-than-talented developer, and makes an infinite loop that
fills the database with garbage. Not only will that hurt performance,
but if it fills the disk than no other users can even commit a
transaction! If you seperate the instances, you can run each as its own
uid and control each with quotas, etc.
(2) You can do a certain amount of favoritism, i.e. you can allocate a
lot of resources to your best customers, and less to the low-paying
customers.

Costs:
(1) The databases can't use eachother's shared memory. That will mean
that the databases with high activity can't cache data in the shared
memeory of a database with low activity.
(2) The RAM from the extra processes for each user. If a database has 0
connections, it's still using memory for the postmaster.
(3) Each instance will require about 30MB of disk for the $PGDATA
directory. In contrast, using the one-instance method it only requires
5MB for an additional DB (approximate).

Overall, I'd say it would be difficult to run seperate instances if
you're trying to have hundreds of people on the same server. If you have
a more managable number you could do it quite effectively I think. I
would recommend lowering the per-instance shared memory so that the OS
could buffer more (mitigating cost #1).

If you can't run multiple instances, just consider the risks and
understand that you should try to limit the users somehow.

Regards,
Jeff Davis

On Wed, 2005-01-05 at 13:52 -0500, Jeff wrote:
[snip]

You'd have to have separate shared buffers for each which would eat
away from the filesystem cache. Not to mention overhead of having
many more PG's running (in terms of just processes htat need to be
managed and memory used by each).

True. Those are the costs.

You'd also have to have the users
connect to PG on an alternate port and that may irritate some.

You can also use unix local domain sockets. I'm not sure if that gains
much, but most web hosts don't allow tcp/ip connections anyway.

In a web hosting (multi-user) environment it may make more sense. but
you could also just give each user his own db and only allow sameuser
access to each db via pg_hba.conf.

I would like to add that there are risks associated with doing that. If
one user fills up the disk (like with an infinite loop in a web app)
than that affects all database users, since it's running as the
"postgres" user. No more commits can happen at all.

Regards,
Jeff Davis

how about to have only one DB with multiple DB shcemas and assign a
DB user per schema?

Will this solution use the multiple CPUs ? - I think it should....
this is my 2cents.

--- Jeff Davis <jdavis-pgsql@empires.org> wrote:

Benefits of multiple instances:
(1) Let's say you're using the one-instance method and one of your
web
users is a less-than-talented developer, and makes an infinite loop
that
fills the database with garbage. Not only will that hurt
performance,
but if it fills the disk than no other users can even commit a
transaction! If you seperate the instances, you can run each as its
own
uid and control each with quotas, etc.
(2) You can do a certain amount of favoritism, i.e. you can
allocate a
lot of resources to your best customers, and less to the low-paying
customers.

Costs:
(1) The databases can't use eachother's shared memory. That will
mean
that the databases with high activity can't cache data in the
shared
memeory of a database with low activity.
(2) The RAM from the extra processes for each user. If a database
has 0
connections, it's still using memory for the postmaster.
(3) Each instance will require about 30MB of disk for the $PGDATA
directory. In contrast, using the one-instance method it only
requires
5MB for an additional DB (approximate).

Overall, I'd say it would be difficult to run seperate instances if
you're trying to have hundreds of people on the same server. If you
have
a more managable number you could do it quite effectively I think.
I
would recommend lowering the per-instance shared memory so that the
OS
could buffer more (mitigating cost #1).

If you can't run multiple instances, just consider the risks and
understand that you should try to limit the users somehow.

Regards,
Jeff Davis

On Wed, 2005-01-05 at 09:49 -0500, Alan Garrison wrote:

Jeff Davis wrote:

However, for truly good seperation, I recommend that you run a

seperate

instance of postgresql (with a seperate $PGDATA directory) for

each

user, and run it under the UID of that user. It requires a

little more

disk space per account, but in a dollar amount it's virtually

zero with

today's disk prices. You will be able to tie the user into

filesystem

quotas, etc., much more easily, and also you could tune the DBs

to the

individual users if needed.

Out of curiosity, what kind of performance hit (whether CPU,

memory,

disk activity) is incurred with having a lot of postmasters

running in

this kind of a setup versus one postmaster with lots of

databases? We

typically run one postmaster for a lot of separate web

applications, but

I like the notion of a instance-per-user (for both security and
maintenance). In the case of having several "big" databases on

one

server, would tuning stragegies need to keep in mind the settings

of

other instances, or would you just tune each one as if it were

the only

one on the box and let the OS deal with memory+disk load of

multiple

instances?

/hope this question makes sense, waiting for coffee to kick in

---------------------------(end of

broadcast)---------------------------

TIP 6: Have you searched our list archives?

http://archives.postgresql.org

---------------------------(end of
broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org

__________________________________
Do you Yahoo!?
The all-new My Yahoo! - Get yours free!
http://my.yahoo.com

On Wed, 2005-01-05 at 12:34 -0800, Jeff Davis wrote:

Benefits of multiple instances:
(1) Let's say you're using the one-instance method and one of your web
users is a less-than-talented developer, and makes an infinite loop that
fills the database with garbage. Not only will that hurt performance,
but if it fills the disk than no other users can even commit a
transaction! If you seperate the instances, you can run each as its own
uid and control each with quotas, etc.
(2) You can do a certain amount of favoritism, i.e. you can allocate a
lot of resources to your best customers, and less to the low-paying
customers.

(3) Different versions of PostgreSQL on the same box. Some hosting
customers have applications that require older versions..and some
developers want the latest version. (ie, we have a few customers running
8.0 RC2..and will want 8.0 the day it's ready). I find this flexibility
a great reason why multiple instances are good for a hosting company.

-Robby

--
/***************************************
* Robby Russell | Owner.Developer.Geek
* PLANET ARGON | www.planetargon.com
* Portland, OR | robby@planetargon.com
* 503.351.4730 | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
* --- Now supporting PHP5 ---
****************************************/

On Wed, 2005-01-05 at 21:34, Jeff Davis wrote:

Benefits of multiple instances:
(1) Let's say you're using the one-instance method and one of your web
users is a less-than-talented developer, and makes an infinite loop that
fills the database with garbage. Not only will that hurt performance,
but if it fills the disk than no other users can even commit a
transaction! If you seperate the instances, you can run each as its own
uid and control each with quotas, etc.

I wonder if this could not be achieved at least partially by using
schemas and set each user's schema to different tablespaces with
different space available on them ? Say, the bulk of the low paying
customers on a bulk partition, and the important customer on it's own
partition ? I actually would like to know if this is feasable...

Cheers,
Csaba.

That's an interesting idea. First, you can't (as far as I know) do it
with just schemas to seperate the users. There is no default tablespace
for an object created inside a given schema.

However, there is a default tablespace for a given database. You can (as
superuser) create a tablespace and permit only a specific user to use
it, and then create a database within that tablespace (so that objects
created in that database use only a specific tablespace). Users can't
create their own tablespace, so they can't create objects out of that
tablespace unless the superuser creates a new tablespace and gives them
permission.

That seems like it would work quite effectively, except that you need a
bunch of size-limited areas to point the tablespaces at. It would
probably be inconvenient to have many partitions. Although you could,
like you said, put all the "cheap" accounts on one partition, and the
expensive guys on their own disk. Then again, if you're going to single
out accounts, why not just give the special hosting account their own
instance?

There's no really easy answer. It would be nice if postgres had a "max
size" parameter for tablespaces, and then you could achieve reasoanble
seperation between databases quite easily (while still sharing the
buffers). I'm not sure what the overhead on a feature like that would
be.

Regards,
Jeff Davis

Not sure what overhead - but Oracle has this ;) Infact Oracle by
default puts each user in their own schema, and each user can be
assigned a default tablespace as a property of the user. With the
advent of Tablespace in pg 8.0, is it possible to set a user's default
tablespace?

Alex Turner
NetEconomist

I must not have been clear. In postgres you can limit people to a
tablespace (in 8.0 of course). You do this by giving them a database
with a default tablespace, and only give them permission on that default
tablespace. That works fine.

The problem is, there is no limit to the size of a tablespace except the
size of the underlying disk. That means if you create two tablespaces
for two users on the same disk, than one user can run out the space of
another user.

A possible solution might be for postgres to somehow limit the size of
the tablespace, but that might be too much overhead. Another solution is
to put each user on their own partition, but that is impractical.

Regards,
Jeff Davis

Alex Turner <armtuk@gmail.com> writes:

... With the
advent of Tablespace in pg 8.0, is it possible to set a user's default
tablespace?

ALTER USER user1 SET default_tablespace = foo;

regards, tom lane

If you are using Linux and you want to do things these ways, you can take
advantage loopback files systems. You can carve up a large disk this way and
control the space requirements per client.

Quoting Jeff Davis <jdavis-pgsql@empires.org>:

I must not have been clear. In postgres you can limit people to a
tablespace (in 8.0 of course). You do this by giving them a database
with a default tablespace, and only give them permission on that default
tablespace. That works fine.

The problem is, there is no limit to the size of a tablespace except the
size of the underlying disk. That means if you create two tablespaces
for two users on the same disk, than one user can run out the space of
another user.

A possible solution might be for postgres to somehow limit the size of
the tablespace, but that might be too much overhead. Another solution is
to put each user on their own partition, but that is impractical.

Regards,
Jeff Davis

On Sat, 2005-01-08 at 14:12 -0500, Alex Turner wrote:

Not sure what overhead - but Oracle has this ;) Infact Oracle by
default puts each user in their own schema, and each user can be
assigned a default tablespace as a property of the user. With the
advent of Tablespace in pg 8.0, is it possible to set a user's default
tablespace?

Alex Turner
NetEconomist

On Fri, 07 Jan 2005 13:03:25 -0800, Jeff Davis <jdavis-pgsql@empires.org>

wrote:

That's an interesting idea. First, you can't (as far as I know) do it
with just schemas to seperate the users. There is no default tablespace
for an object created inside a given schema.

However, there is a default tablespace for a given database. You can (as
superuser) create a tablespace and permit only a specific user to use
it, and then create a database within that tablespace (so that objects
created in that database use only a specific tablespace). Users can't
create their own tablespace, so they can't create objects out of that
tablespace unless the superuser creates a new tablespace and gives them
permission.

That seems like it would work quite effectively, except that you need a
bunch of size-limited areas to point the tablespaces at. It would
probably be inconvenient to have many partitions. Although you could,
like you said, put all the "cheap" accounts on one partition, and the
expensive guys on their own disk. Then again, if you're going to single
out accounts, why not just give the special hosting account their own
instance?

There's no really easy answer. It would be nice if postgres had a "max
size" parameter for tablespaces, and then you could achieve reasoanble
seperation between databases quite easily (while still sharing the
buffers). I'm not sure what the overhead on a feature like that would
be.

Regards,
Jeff Davis

On Fri, 2005-01-07 at 10:38 +0100, Csaba Nagy wrote:

On Wed, 2005-01-05 at 21:34, Jeff Davis wrote:

Benefits of multiple instances:
(1) Let's say you're using the one-instance method and one of your

web

users is a less-than-talented developer, and makes an infinite loop

that

fills the database with garbage. Not only will that hurt

performance,

but if it fills the disk than no other users can even commit a
transaction! If you seperate the instances, you can run each as its

own

uid and control each with quotas, etc.

I wonder if this could not be achieved at least partially by using
schemas and set each user's schema to different tablespaces with
different space available on them ? Say, the bulk of the low paying
customers on a bulk partition, and the important customer on it's own
partition ? I actually would like to know if this is feasable...

Cheers,
Csaba.

---------------------------(end of

broadcast)---------------------------

TIP 9: the planner will ignore your desire to choose an index scan if

your

joining column's datatypes do not match

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

--
Keith C. Perry, MS E.E.
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com

You might be interested to see a previous thread started by me on that
very subject:
<http://archives.postgresql.org/pgsql-general/2004-04/msg00365.php&gt;

I got one reply that was very informative by William White:
<http://archives.postgresql.org/pgsql-general/2004-04/msg00366.php&gt;

The other reply suggested the multiple-postmaster idea:
<http://archives.postgresql.org/pgsql-general/2004-04/msg00380.php&gt;

Anyway, it seems like you'd want to take some things into consideration
first. One potential problem is that if you lose power, most journaling
filesystems only journal the metadata. That might mean that the backing
file of the loopback filesystem might get corrupted, even if you're
doing journaling on both the host filesystem and the loopback
filesystem. You may need to turn on actual data journaling for the host
filesystem in order to prevent that. Correct me if I'm wrong on this
point.

However, data journaling on the host system can of course cause
significant performance problems. Also, I'm not sure what the
performance impact of running pgsql through a loopback filesystem would
be (could be a lot since databases are normally bound by the i/o
performance).

Another potential problem is that let's say you gave 100MB to someone,
and then the database starts to get really big, it may be difficult to
give them more space without doing a full dump/reload. Not that big of
an issue since disk space is so cheap now, but something to consider.

Regards,
Jeff Davis

I'd just like to add that having all the users in one database has
another minor disadvantage:

If you want to make use of PITR for your clients, and client A shares a
postgres instance with client B, and client A asks to be time warped,
then you have to time warp both, since both are in the same db instance.
Granted, that's sort of a minor issue, but something to consider.

It could become an issue if you have many clients sharing that database
and it would cause downtime for all those other clients while you're
warping back to get someone's lost data.

However, you don't lose all of the benefits of PITR. You still have the
capability to do incremental backups.

Regards,
Jeff Davis