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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
