On Mon, Oct 30, 2017 at 12:25 PM, Ivan Voras <ivoras@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.
