Re: Keeping Admin-Owner user but creating new user with effectiveAdmin-Owner access rights?

On 3/17/20 8:23 AM, AC Gomez wrote:
> 
> We have the following scenario...
> 
> We've inherited a situation where we have a master admin user that's 
> used across the board for all processes.
> 
> We need to undo that one process at a time. So, for each process we 
> thought of creating two secondary users, among which we will rotate a 
> password.
> 
> However, since a PostgreSQL Db cannot have more than one owner then 
> these secondary users cannot act on the DB objects the same way, that is 
> our understanding.
> 
> The question is, if a DB already has an owner that we want to keep as 
> the owner for now, can we create an equivalent user that will 
> effectively have the same behaviour as the owner while not being the owner?
> 
> And, will any objects created by this new user be fully accessible by 
> the original master user?

Wouldn't INHERIT and IN ROLE work?:

https://www.postgresql.org/docs/12/sql-createrole.html

INHERIT
NOINHERIT

     These clauses determine whether a role “inherits” the privileges of 
roles it is a member of. A role with the INHERIT attribute can 
automatically use whatever database privileges have been granted to all 
roles it is directly or indirectly a member of. Without INHERIT, 
membership in another role only grants the ability to SET ROLE to that 
other role; the privileges of the other role are only available after 
having done so. If not specified, INHERIT is the default.
"


"IN ROLE role_name

     The IN ROLE clause lists one or more existing roles to which the 
new role will be immediately added as a new member. (Note that there is 
no option to add the new role as an administrator; use a separate GRANT 
command to do that.)
"

> 
> Thanks


-- 
Adrian Klaver
adrian.klaver@aklaver.com