Skip site navigation (1) Skip section navigation (2)

Re: pre-proposal: permissions made easier

From: Bill Moran <wmoran(at)potentialtech(dot)com>
To: Jeff Davis <pgsql(at)j-davis(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: pre-proposal: permissions made easier
Date: 2009-06-28 18:12:15
Message-ID: 20090628141215.76d352b4.wmoran@potentialtech.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2009-06-28 18:21:35
Subject: dependencies for generated header files
Previous:From: Jeff DavisDate: 2009-06-28 18:07:30
Subject: pre-proposal: permissions made easier

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group