How to grant all privileges on a database? There are many almost-duplicates to this question and all I found so far were wrong and/or incomplete, so I feel we need a canonical answer.

In PostgreSQL, how can I grant all permissions on a database to a user, without giving them SUPERUSER permissions?

This includes all tables, sequences, types, triggers, indexes, procedures and whatever other objects are currently in the database and any objects that are created in the database at a later time, by this user or others. Of course, it also includes to ability to create objects in the database.
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:


Is there a means to set the owner of all objects in a PostgreSQL database at the same time?


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):


How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?