[ discussion re/ default state of minimal rights, as opposed to the
more generous situation today snipped ]
Just to add fuel to the fire, as an ex-college sys-admin having had to
deploy both Oracle and postgres, I would have to say that Oracle
allowed me to deploy a database container shared by many many students
relatively securely, while at the same time allowing them to perform
cross-schema queries to their teammates tables if and when they needed
to. The users could manage the ACLs of their own schema objects,
allowing their peers into their tables on a mutual need basis, but the
default action was to be closed off completely. Combined with per-user
quotas on the tablespaces and per-user connection limits, I could
create a new student user and be comfortable knowing they're not going
to be able to steal from others nor consume all disk space. I didn't
have to deal with CPU / memory based attacks on the box just 'cause I
was lucky I guess.
With postgres, I had to create a new database for each user (this was
pre-schema, anyway), then tweak the hba conf file and sighup. I had no
disk-based resource limits available to me (there's a creative use of
linux loopback mounts to enforce per-database quotas floatin' around on
the net somewhere, but I didn't think of that, as well as that probably
wouldn't scale to, say, thousands of users). I wasn't about to dblink
databases for 'em, so it ended up that the mass-student-consumption
learn-SQL box was Oracle, and the lesser-used DB was postgres.
So, finally, from the perspective of a college admin with a 'centralize
the student services' mindset, being able to sandbox SQL users
relatively easily while also being able to provide higher-level service
such as cross-schema queries, centralized / guaranteed backup, etc.
would have been fantastic using postgres.
I don't work there anymore, but I'm sure other university shops still
have the same issue to solve. If a tight schema sandbox isn't the
default, then a system-catalog expert managed schema patch would have
been greatly appreciated to solve at least the 'stay out of other
user's crud by default' issues.
----
James Robinson
Socialserve.com