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

Hello,

On 30 October 2017 at 22:10, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> 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;
>

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.