Thread: [GENERAL] Roles inherited from a role which is the owner of a database can drop it?
[GENERAL] Roles inherited from a role which is the owner of a database can drop it?
From
Ivan Voras
Date:
Hello,
I just want to verify that what I'm observing is true, and if it is, I'd like to know how to avoid it:
1. There are databases owned by a certain role which is a superuser
2. Nobody logs in with the superuser role unless necessary
3. But they do log in with "developer" roles which are inherited from the owner role. These developer roles are not superusers themselves, but have the CREATEDB flag
4. The developer roles can still drop the databases.
I've tried it on a dummy database and it apparently works as described here. Is this by design?
If it is, is there a way to prevent the developer roles from dropping the databases?
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)?
Re: [GENERAL] Roles inherited from a role which is the owner of adatabase can drop it?
From
"David G. Johnston"
Date:
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.
Re: [GENERAL] Roles inherited from a role which is the owner of adatabase can drop it?
From
Ivan Voras
Date:
Hello,
On 30 October 2017 at 22:10, David G. Johnston <david.g.johnston@gmail.com> wrote:
Just considering the case of dropping databases for now. I.e. let the developers do everything except that. It's a start.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.
Re: [GENERAL] Roles inherited from a role which is the owner of adatabase can drop it?
From
Stephen Frost
Date:
Greetings, * Ivan Voras (ivoras@gmail.com) wrote: > On 30 October 2017 at 22:10, David G. Johnston <david.g.johnston@gmail.com> > wrote: > > 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)? No, that's exactly what role membership means- you have the same rights as the other role. > > 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. No, because even if ownership wasn't inheritable the user would simply do 'SET ROLE owner;' and then have all of the ownership rights that way. > Just considering the case of dropping databases for now. I.e. let the > developers do everything except that. It's a start. I think you're assuming far too much about what being a database owner means- I'd suggest you really think about why the developers need to be database owners at all; in other words- what's the *other* privilege that's currently only available to database owners that you need developers to be able to do? I have a hunch that it might be GRANT'ing rights on the database, but there's only a couple such rights (eg: CONNECT) and you might be better off managing those in another way. Thanks! Stephen