Heikki, * Heikki Linnakangas (hlinnakan...@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
Description: Digital signature