I have user ‘administrator’ with CREATEROLE privileges. I create new user like this:
CREATE USER test_user;
And then i grant privileges to the new user:
GRANT administrator TO test_user;
When i’m logged in as ‘administrator’, i can create users without a problem, since ‘administrator’ has the privilege to do it. But for some reason ‘test_user’ can’t create users. When i’m trying it, i get this output:
ERROR: permission denied to create role
Output of \du:
List of roles
Role name | Attributes | Member of
---------------+------------------------------------------------------------+-----------------
administrator | Create role | {}
analyst | | {}
manager | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test_user | | {administrator}
>Solution :
I think you misunderstood how role memberships work:
The members of a group role can use the privileges of the role in two ways. First, member roles that have been granted membership with the
SEToption can doSET ROLEto temporarily “become” the group role. In this state, the database session has access to the privileges of the group role rather than the original login role, and any database objects created are considered owned by the group role not the login role. Second, member roles that have been granted membership with theINHERIToption automatically have use of the privileges of those roles, including any privileges inherited by those roles.
So you need to
GRANT administrator TO test_user WITH INHERIT TRUE;
To just use administrator privileges as test_user without switching between them.