All,
First, let me talk about the problem: it's been my observation that the
majority of users, including public commercial web sites, which I run
into in the field do not employ permissions in any useful way to protect
their data. An awful lot of these applications are running as the
superuser or the database owner, partly because the company can't deal
with object permissions management, especially when the application is
under continuous development.
The pgAdmin widget doesn't really help much in this respect.
I want to avoid anything which requires an additional permissions check
or any other check at runtime. Instead, we need two tools:
1) ALTER SCHEMA SET DEFAULT PRIVILEGES statements which sets default
permissions, by ROLE and object type, on new objects.
2) a statement to set privs on all existing objects by type and role
within a schema.
These two tools would make it vastly easier for admins to manage ROLE
privileges without needing any additional runtime checks or limiting
flexibility in object permissions assignment. Further, they would make
it very simple to build the kind of very simple ROLE-based permissions
management Jeff is talking about on top of it (which is, BTW, very
popular; it's one of MSSQL's major selling points to small businesses).
--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com