Re: New Model For Role Attributes and Fine Grained Permssions - Mailing list pgsql-hackers

Heikki,

* Heikki Linnakangas (hlinnakangas@vmware.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)

The question which I've been kicking around is the possible additional
constraints which we may want/need for these- a list of extensions which
the role is allowed to create, a set of directories which the role is
allowed to create tablespaces within, similairly a set of directories
the role is allowed to use server-side COPY with (and perhaps a
distinction between COPY FROM and COPY TO).

> 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.

This is definitely a concern- which is why I mention the specific items
above as needing to be constrained in some fashion.  CREATE EXTENSION
and CREATE TABLESPACE are, in a way, "naturally" constrained if you
imagine an environment where the user with those permissions doesn't
have direct access to modify the filesystem.  COPY, on the other hand,
would allow the user to gain access to pg_authid through indirect means
and therefore gain access to an actual superuser role on the system,
potentially.

(Ok- it might be possible to do that with CREATE TABLESPACE too, but
it'd be a bit more interesting to work through that than being able to
just COPY to a bytea and download the result)

> 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:

I agree that there are concerns in this area and I've not got any great
solutions.  There are certainly pros and cons to using GRANT.  It's
familiar and natural to DBAs, but it runs the risk of conflicting with
future SQL syntax, or even our own GRANT role.  We can avoid the latter
by keeping to reserved keywords only, but that may lead to some rather
odd syntax (how would the "GRANT COPY ON (dir1, dir2, dir3)" work?).

Is there a good alternative to consider though..?
Thanks!
    Stephen