Re: New Model For Role Attributes and Fine Grained Permssions

On Thu, Aug 21, 2014 at 10:49 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Heikki Linnakangas (hlinnakangas(at)vmware(dot)com) wrote:
>> On 08/19/2014 04:27 AM, Brightwell, Adam wrote:
>> >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?
>
> This patch, by itself, does not- but it adds the structure to allow us
> to add more permissions without having to add more fields to pg_authid,
> and we could build into pg_permission the "WITH ADMIN OPTION" and
> "grantor" bits that the normal GRANT syntax already supports- but
> without having to chew up additional bits for these granted permissions
> which are applied to roles instead of objects in the system.
>
> As for specific examples where this could be used beyond those
> presented, consider things like:
>
> CREATE EXTENSION
> CREATE TABLESPACE
> COPY (server-side)

I'm not opposed to this in theory, but I agree with Heikki that the
syntax you've picked (as well as the internal teminology of the patch)
is not sufficiently unambiguous. "Permission" could refer to a lot
of things, and the GRANT syntax does a lot of things already. Since
the patch appears to aim to supplant what we current do with ALTER
ROLE .. [NO] {CREATEDB | CREATEROLE }, how about something like:

ALTER ROLE role_name { GRANT | REVOKE } CAPABILITY capability_name;

In terms of catalog structure, I doubt that a
row-per-capability-per-user makes sense. Why not just add a new
rolcapability column to pg_authid? A single int64 interpreted as a
bitmask would give us room for 64 capabilities at a fraction of the
storage and lookup cost of a separate catalog. If that's not enough,
the column could be stored as an integer array or vector or something,
but a bigger problem is that Tom's head will likely explode if you
tell him you're going to have more than 64 of these things.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company