Search code examples
sqlpostgresqlpermissionspsqlroles

Permission denied when attempting to create role despite having CREATEROLE

I have created a user with CREATEROLE, but I get the error "permission denied to create role".

Here is how I created a user with the CREATEROLE ability:

# Login
PGPASSWORD=$secretpassword psql -h XXX.XXX.XXX.XXX --port XXXXX -d rdb -U admin

# Connecting to database where role should be created
\c database

# Creating role and user
CREATE ROLE admin_role CREATEROLE;

GRANT CONNECT ON DATABASE database TO admin_role;
GRANT CREATE ON DATABASE database TO admin_role;

CREATE USER admin_user WITH ENCRYPTED PASSWORD 'ADMIN_PASSWORD';

GRANT admin_role TO admin_user;

Here I log in with the new user and attempt to create a role:

# Login to database
PGPASSWORD=$secretpassword psql -h XXX.XXX.XXX.XXX --port XXXXX -d database -U admin_user

# Attempting to create a role
CREATE ROLE new_role;
>ERROR:  permission denied to create role

Can someone help me understand why the CREATEROLE permission is not working?

Solution

  • In the documentation it says:

    The INHERIT attribute governs inheritance of grantable privileges (that is, access privileges for database objects and role memberships). It does not apply to the special role attributes set by CREATE ROLE and ALTER ROLE. For example, being a member of a role with CREATEDB privilege does not immediately grant the ability to create databases, even if INHERIT is set; it would be necessary to become that role via SET ROLE before creating a database.

    source: https://www.postgresql.org/docs/current/sql-createrole.html

    This means that in order to "activate" the CREATEROLE attribute, I have to do the following:

    # Login to database
PGPASSWORD=$secretpassword psql -h XXX.XXX.XXX.XXX --port XXXXX -d database -U admin_user

# Setting role as it is not inhereted
SET ROLE admin_role;

# Attempting to create a role
CREATE ROLE new_role;