Re: Roles inherited from a role which is the owner of a database can drop it?

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

David J.