Re: pre-proposal: permissions made easier

On Sun, Jun 28, 2009 at 12:52:54PM -0700, Jeff Davis wrote:
> On Sun, 2009-06-28 at 14:56 -0400, Tom Lane wrote:
> > > I meant for "foo" to be a user. "foo_ro" would be the read-only
> > > version, who has a strict subset of foo's permissions.
> > 
> > I see.  It seems like rather a complicated (and expensive)
> > mechanism for a pretty narrow use-case.  It'd only help for the
> > cases where you could define your permissions requirements that
> > way.  I agree that there are some such cases, but I think
> > real-world problems tend to be a bit more complicated than that.
> > I fear people would soon want exceptions to the "strict subset"
> > rule; and once you put that in, the conceptual simplicity
> > disappears, as does the ability to easily verify what the set of
> > GRANTs is doing.
> 
> As soon as the permissions scheme gets more complicated than what I
> suggest, I agree that the user is better off just using GRANTs on a
> per-object basis. You could still GRANT directly to the user foo_ro
> -- for instance if your reporting user needs to join against some
> other table -- but that could get complicated if you take it too
> far.
> 
> The users I'm targeting with my idea are: * Users who have a fairly
> simple set of users and permissions, and who want a simple picture
> of the permissions in their system for reassurance/verification.

I don't know of a case that started simple and stayed there without a
lot of design up front.  In other words, those who'd benefit by such a
thing are generally not those who'd want a shortcut.

>  * Users who come from MySQL every once in a while, annoyed that we
>  don't support "GRANT ... *" syntax.

I'm missing what's wrong with a wild-card GRANT syntax for this case.

>  * Users who are savvy enough to use access control, but don't have
>  rigorous procedures for making DDL changes.  Some of these users
>  depend on an ORM or similar to make DDL changes for them, and this
>  idea gives them a workaround.

Such ORMs are a problem, and accommodating them only aggravates it. :)

>  * Users who don't currently use separate permissions, but might
>  start if it's simpler to do simple things.

This is a matter of education, not tools.  The problem here is not
that permissions are unavailable, but that people are failing to use
them.

> Maybe I should shop this idea on -general and see how many people's
> problems would actually be solved?

There are a few issues at hand here, some of which could get addressed
by polling on -general:

* SQL standards compliance (clearly not a -general issue)
* Utility to current users (might be addressable on -general)
* Utility to future users (not on -general)
* Trade-offs such a solution would impose (possibly on -general

and the ever-popular

* Stuff I didn't think of ;)

> The performance issue is something to consider, but I think it would
> just be an extra catalog lookup (for each level), and the users of
> this feature would probably be willing to pay that cost.

Where did this come up?

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate