Re: pre-proposal: permissions made easier

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?

It seems to me that you're duplicating the functionality that is already
possible by using groups. i.e. grant the permissions to the group and
add users to the group as appropriate.

--
Bill Moran
http://www.potentialtech.com