Re: pre-proposal: permissions made easier

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