Jeff,
> I don't see why either of these things should be properties of the
> schema. It seems to make much more sense for these defaults to be a
> property of the user who creates the objects.
The main reason is existing practice. Currently, most applications I
see in the field which bother with having several ROLES have all
database objects belonging to one ROLE ("db_owner"). So for most people
setting permissions for all objects belonging to a specific user would
amount to setting permissions for all objects of that type in a given
database.
There's also the fact that SCHEMAs currently have their own visibility
rules and permissions, which seems to me to dovetail nicely with the ACLs.
This is, of course, assuming that we are talking about setting
permissions in saved objects, that is, all the object belonging to a
particular user.
The approach I could see as valuable in vastly simplyfying things would
be to set the permission on the user regardless of object properties;
that is, the user is defined as WITH SELECT, INSERT, UPDATE ON ALL
TABLES. These user permissions would supercede any object permissions
for that role.
This would make DBA's lives vastly simpler and make them more likely to
use permissions. But would it actually benefit security?
The problem I see with this approach is that in 95% of the applications
I run across there are a few tables which really need to be "locked
down" and restricted from most user access (maybe accessed only by an
SRF). In large development shops where more than one person has their
hands on the DB, I can easily see one developer accidentally bypassing
object-level security set up by another DBA through this mechanism.
The second, and bigger problem I can see is that this opens a whole new
set of security holes by allowing an end-run around the existing access
control structure with attackers can try to exploit.
--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com