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


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?

Thanks
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