Re: Schema (namespace) privilege details

On Sat, 20 Apr 2002, Sander Steffann wrote:

> > > 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 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. But I think you can arrange for the
sort files to go on another partition, to help limit the problems
this would cause.

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.

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.

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.

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

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC