Re: Schema (namespace) privilege details

Hi,

> > > > Maybe to keep hostile users from filling up your disk?
>
> Actually, I was serious, not sarcastic, about that "maybe." Like
> Tom, I'm not entirely sure that it's necessary to add this complexity,
> because there are so many other ways to abuse the system.

I know... But we have to start somewhere :)

> > I think Curt is right... If users are always allowed
> > to make temp tables, you can't give someone real read-only access to the
DB.
>
> Well, I'm not sure you can give "real" read-only access anyway.
> After all, if you've got a big enough table, all a user has to do
> is submit a few queries that sort the entire thing and you'll be
> eating up disk space like mad.

Ok. I forgot about that.

> But I think you can arrange for the
> sort files to go on another partition, to help limit the problems
> this would cause.

Sounds good.

> Another question is about the best place to put temporary tables.
> Right now they go in the database you're connected to, right? So
> it's possible for users that can create temporary tables to stop
> all inserts into that database by filling up its partition, but
> other DBs might be on different partitions and be unaffected.

At the moment all our DBs are on one partition. This would be a good reason
to split them, but it also makes it difficult if someone needs more space.

> Another way to go is to do what MS SQL server does, which is to
> put temp tables in a separate database. If you put that on its own
> partition, you can limit the damage users can do to the database
> that they're connected to, but then users can stop all other users
> from creating temporary tables.

That is true, but when I look at how many of our customers actually use temp
tables, I think this is not a very big problem (for us!)

> Personally, I feel the Postgres approach is better for postgres at
> this time, but there are other differences that help to make this
> so. In SQL Server, a "database" is really more a schema in the
> postgres sense, except that it's also a separate tablespace. So
> the two approaches are not directly comparable.
>
> In the end, it seems to me that there's only so much security you
> can implement in a database. I don't think that anybody produces
> a database server where I'd let random users connect directly,
> rather than going though an application that implements further
> security. Thus, one probably doesn't want to spend a lot of time
> trying to implement perfect security.

Only the idea of real read-only users seems useful to me. Maybe if temp
tables and big sorts could be limited this would be possible? Maybe a
restriction on CPU time... I don't know if there are any other places where
a user can eat resources, but the more I think about it, the more
complicated it gets. :-(

> Am I siding with you or Tom here? I'm not sure. :-)

I don't realy care, as long as we reach a good sollution! :-)

- Sander