What you ask is hardly practical - you would typically use a superuser for this. Because, quoting the manual:
The right to modify or destroy an object is always the privilege of the owner only.
And:
The right to drop an object, or to alter its definition in any way, is
not treated as a grantable privilege; it is inherent in the owner, and
cannot be granted or revoked. (However, a similar effect can be
obtained by granting or revoking membership in the role that owns the
object; see below.) The owner implicitly has all grant options for the
object, too.
So, for a non-superuser to have these privileges, too, it would have to be granted membership (directly or indirectly) in all roles that are allowed to create objects.
GRANT any_role TO admin_role;
Granting membership in a superuser role does not make the member a superuser.
The role attributes LOGIN
, SUPERUSER
, CREATEDB
, and CREATEROLE
can be
thought of as special privileges, but they are never inherited as
ordinary privileges on database objects are.
But the member role can now SET ROLE
and become superuser. May or may not be what you want.
Then, as owner, admin_role
can also GRANT
any additional privileges (that may have been revoked) himself.
Or you set up a regime that assigns ownership of all existing and future objects to a role and grant membership in that role only. The quick way to do that for all objects of given roles is REASSIGN OWNED
. Related:
But would you even want to grant ownership on objects in the system catalog, too? Those are owned by the superuser postgres
in default setup.
Instead, I would use a superuser where "all" privileges are needed. And grant only required privileges ( incl. CREATEDB
and CREATEROLE
) to an admin role. A recipe for a powerful admin role (without all privileges):