3

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.

1 Answer 1

1

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

1
  • Ok, at least this is a good explanation why there are no answers: because it is not possible. In that case I asked the wrong question (XY problem), I'll ask a new one.
    – AndreKR
    Commented Jun 7, 2018 at 5:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.