Re: PostgreSQL users on webhosting - Mailing list pgsql-general

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