Re: pre-proposal: permissions made easier

On Mon, Jun 29, 2009 at 9:02 PM, Josh Berkus<josh(at)agliodbs(dot)com> wrote:
> What I'm saying is that there are many users currently using schema for
> security classes.  I personally haven't ever encountered a DBA who used role
> ownership of objects as a mechanism for security context.  There's nothing
> conceptually invalid about the latter approach, but it would be hard for
> DBAs to grasp, and as a result less of them would use it.
>
> Mainly that's because the concept of schema easily maps (even if
> inaccurately) to the concept of directories, whose permissions IT staff are
> used to managing.  So it's more intuitive for a DBA to say "This is
> sensitive data so I'm going to put it in the SENSITIVE schema" than to say
> "this is sensitive data so I'm going to have the table belong to the
> SENSITIVE role".
>
> Further, it's common practice on other DBMSes to have a "database owner"
> role which owns all database objects.  So DBA who learn Postgres second are
> going to do that.

It sounds to me like they *are* using the owner to provide security then.

> If we were going for a theoretically pure approach, we'd actually have a
> "security context" concept which would include a bundle of permissions and
> each object would belong to one.

It sounds like you're confounding the the owner of the objects and
roles that users have.

In the databases I've used we had a dba user which owned all the
objects. Then we had a "www" user which had DML access to most
objects, though lacked update and delete access to others. We actually
didn't need any other users but if we had a more extensive backend
interface we would have had a "www-backend" and "reports" and so on.

So I'm not sure what problem we're trying to solve here.

There's "I just created a new "www-backend" role which I want to have
access to everything "www" has and then I'll go add a few additional
privileges. We can do that already by having "www-backend" belong to
the "www" role and then add the extra privileges manually.

And there's "I just created a new table, I want "www" and
"www-backend" to get their usual privileges without thinking about it.
You want to be able to specify default grants that an object gets
based on the schema? That seems mostly reasonable though it might be a
good idea to have a WITH DEFAULT GRANTS or something like that on the
CREATE statement so that the dba has to make it explicit. It does
seems slightly silly since surely anyone creating a new object would
just paste in their grants from another object or some common source
anyways, but I suppose that's the way with convenience features.

--
greg
http://mit.edu/~gsstark/resume.pdf