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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

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