Re: Keeping Admin-Owner user but creating new user with effective Admin-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(dot)klaver(at)aklaver(dot)com