Hello, On 30 October 2017 at 22:10, David G. Johnston <david.g.johns...@gmail.com> wrote:
> On Mon, Oct 30, 2017 at 12:25 PM, Ivan Voras <ivo...@gmail.com> wrote: > >> >> 3. But they do log in with "developer" roles which are inherited from the >> owner role. >> >> [...] > >> I've tried it on a dummy database and it apparently works as described >> here. Is this by design? >> >> > Not quite following but ownership is an inheritable permission; > Basically, I'm asking if "ownership" can be revoked from the set of inherited permissions? If there is a role G which is granted to role A, and G is the owner of a database, can A be made to not be able to do what only owners can (specifically in this case, drop databases)? > and even if it was not SET ROLE is all that would be required. Any owner > can drop an object that it owns. > > It's kind of the reverse: I'm wondering if ownership can be made un-inheritable. > > What are the best practices for this sort of scenario where there is a >> single owner of all the schema (which is large), where developers need >> access to everything but cannot do something as drastic as dropping the dbs >> (and possibly tables)? >> > > Don't let developers into production databases... > > Trusted people (and/or software) should be provided membership into > ownership groups. Developers should provide these people/programs with > vetted scripts to execute against production. Developers can do whatever > they want on their local database instance with full schema-modifying > privileges. > > "developers need access to everything" - there is a lot of nuance and > detail behind that fragment that is needed if one is going to develop a > data access and change management policy. > Just considering the case of dropping databases for now. I.e. let the developers do everything except that. It's a start.
