Re: pre-proposal: permissions made easier

On Sun, Jun 28, 2009 at 2:07 PM, Jeff Davis<pgsql(at)j-davis(dot)com> wrote:
> I'd like some brief feedback on this idea before I try to make a real
> proposal.
>
> The use case is this:
>
> You have an application with several roles:
>  * admin user - owns all the objects related to that application
>  * normal user - INSERT/UPDATE/DELETE plus sequence usage
>  * read-only user - for reporting
>
> The problem is managing all the permissions requires a lot of care, and
> it's difficult to easily verify that all the permissions are set as you
> expect on all of the objects. Because it's more difficult to manage, I
> think many people just have a single user for all of these things.
>
> My idea is to have a "GRANT mask":
>
> CREATE ROLE foo_ro GRANT (SELECT ON TABLE, USAGE ON SCHEMA) FROM foo;
>
> [syntax not meant as a real proposal yet, just for illustration]
>
> And foo_ro would get the SELECT ON TABLE and USAGE ON SCHEMA privileges
> from foo, but not any INSERT privileges. That way, you can add roles
> without having to do a GRANT on each object separately. And, more
> importantly, you can pretty easily observe that the privileges are what
> you expect without inspecting the objects individually.
>
> This idea is meant to be a better solution than the "GRANT ... *" that
> MySQL offers.
>
> Questions:
>
> 1. Is this a reasonable approach from a security standpoint, or is it
> fundamentally flawed?

Reasonable.

> 2. Does it violate the SQL standard?

Don't know.

> 3. Is it completely orthogonal to "IN ROLE" and "INHERITS", or should
> they be made to work together somehow?

Maybe GRANT foo (SELECT, USAGE) TO foo_ro, meaning "grant restricted
membership in role foo to foo_ro, encompassing only the SELECT and
USAGE privileges of foo"?

I do to some degree share Tom's worry that this is an idiosyncratic
solution to a tiny subset of the problem space. On the other hand, I
have to admit I've needed to do this exact thing, so I wrote a script
to issue the necessary grant statements. Then I discovered that
whenever I created any new objects (most frequently drop and recreate
of any of the relevant views) the permissions got screwed up, so I
crontabbed the script to run every 20 minutes. This works, but it
doesn't bleed elegance.

I'm not sure what the right things to do in this area are, but I'm
glad that you (and others, like Stephen Frost) are thinking about
it...

...Robert