Re: New Model For Role Attributes and Fine Grained Permssions

On 08/19/2014 04:27 AM, Brightwell, Adam wrote:
> Hi All,
>
> This is a "proof-of-concept" patch for a new model around role attributes
> and fine grained permissions meant to alleviate the current over dependence
> on superuser.

Hmm. How does this get us any closer to fine-grained permissions? I 
guess we need this, so that you can grant/revoke the permissions, but I 
thought the hard part is defining what the fine-grained permissions are, 
in a way that you can't escalate yourself to full superuser through any 
of them.

The new syntax could be useful even if we never get around to do 
anything about current superuser checks, so I'm going to give this a 
quick review just on its own merits.

Please add documentation. That's certainly required before this can be 
committed, but it'll make reviewing the syntax much easier too.

> This is not yet complete and only serves as a proof-of-concept at this
> point, but I wanted to share it in the hopes of receiving comments,
> suggestions and general feedback.
>
> The general gist of this patch is as follows:
>
> * New system catalog "pg_permission" that relates role id's to permissions.
>
> * New syntax.
>    - GRANT <permission> TO <role>
>    - REVOKE <permission> FROM <role>
> where, <permission> is one of an enumerated value, such as "CREATE ROLE" or
> "CREATE DATABASE".

The syntax for GRANT/REVOKE is quite complicated already. Do we want to 
overload it even more? Also keep in mind that the SQL standard specifies 
GRANT/REVOKE, so we have to be careful to not clash with the SQL 
standard syntax, or any conceivable future syntax the SQL committee 
might add to it (although we have plenty of PostgreSQL extensions in it 
already). For example, this is currently legal:

GRANT CREATE ALL ON TABLESPACE <typename> TO <role>

Is that too close to the new syntax, to cause confusion? Does the new 
syntax work for all the more fine-grained permissions you have in mind? 
I'm particularly worried of conflicts with this existing syntax:

GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]

- Heikki