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; and even if it was not SET ROLE is all that would be required. Any owner can drop an object that it owns.
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.
