Re: [GENERAL] Roles inherited from a role which is the owner of adatabase can drop it?

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.