Search code examples
sqlpostgresqlsql-grant

GRANT USER to another user does not grant permissions

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

  • If it was about inheritance, \du would show No inheritance attribute. I think it might actually be due to CREATEROLE being a cluster-wide role attribute that has to be explicitly given and isn't subject to inheritance.

    role creation A role must be explicitly given permission to create more roles (except for superusers, since those bypass all permission checks). To create such a role, use CREATE ROLE name CREATEROLE.

    So, you can't rely on test_user having also administrator role, regardless of inherit settings on any level. You need to explicitly and independently set the attribute the same way you must've done that for administrator in the first place:

    CREATE USER test_user CREATEROLE;

    Or

    CREATE USER test_user;
ALTER ROLE test_user WITH CREATEROLE;

    Unless you're ok with manually elevating to administrator through SET ROLE administrator; whenever, as test_user you need to do something only the administrator role has an adequate privilege/attribute for. For that GRANT administrator TO test_user; is enough.

    If it was about actual role privilege inheritance as opposed to the the few uninheritable role attributes, here's how role memberships work for those:

    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 SET option can do SET ROLE to 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 the INHERIT option automatically have use of the privileges of those roles, including any privileges inherited by those roles.

    To just use administrator privileges as test_user without switching between them: demo

    GRANT administrator TO test_user WITH INHERIT TRUE;

    It's normally the default. The reason why it wouldn't be can be found in the GRANT command doc:

    The INHERIT option, if it is set to TRUE, causes the member to inherit the privileges of the granted role. That is, it can automatically use whatever database privileges have been granted to that role. If set to FALSE, the member does not inherit the privileges of the granted role. If this clause is not specified, it defaults to true if the member role is set to INHERIT and to false if the member role is set to NOINHERIT. See CREATE ROLE.

    And from the CREATE ROLE...[NO]INHERIT:

    When the GRANT statement is used to confer membership in one role to another role, the GRANT may use the WITH INHERIT clause to specify whether the privileges of the granted role should be “inherited” by the new member. If the GRANT statement does not specify either inheritance behavior, the new GRANT will be created WITH INHERIT TRUE if the member role is set to INHERIT and to WITH INHERIT FALSE if it is set to NOINHERIT.

    In PostgreSQL versions before 16, the GRANT statement did not support WITH INHERIT. Therefore, changing this role-level property would also change the behavior of already-existing grants. This is no longer the case.