One of my clients hit a strange limitation – apparently you cannot inherit CREATE ROLE privilege.

First, let’s test if it’s really true:

First, let’s create role which will have CREATE ROLE privilege:

create role test1 with login createrole;

Now, let’s create new role, make it inherit privileges, and grant it test1 role:

# create role test2 with login inherit;
CREATE ROLE
 
# grant test1 to test2 with admin option;
GRANT ROLE

And now, let’s connect to test2 role, and check if we can create new roles:

> \c - test2
You are now connected to database "depesz".
 
> create role test3;
ERROR: permission denied to create role

Ok, So, let’s just check if everything is ok:

> \du test*
List of roles
Role name | Attributes | Member of
-----------+-------------+-----------
test1 | Create role | {}
test2 | | {test1}
 
> \c - test1
You are now connected to database "depesz" as user "test1".
 
> create role test3;
CREATE ROLE

OK. Clearly test1 role can create new roles, test2 inherits from it, and cannot.

So, what can we do about it?

Answer is pretty simple – let’s write a wrapper around CREATE ROLE:

\c - test1
You are now connected to database "depesz".
 
> CREATE OR REPLACE FUNCTION create_role( in_role_name TEXT, in_options TEXT ) RETURNS void as $_$
DECLARE
use_sql TEXT;
BEGIN
use_sql := 'CREATE ROLE ' || quote_ident( in_role_name );
IF in_options IS NOT NULL THEN
IF in_options ~ '(;|--)' THEN
RAISE EXCEPTION $$Don't try to be too smart ...$$;
END IF;
use_sql := use_sql || ' WITH ' || in_options;
END IF;
EXECUTE use_sql;
END;
$_$ LANGUAGE plpgsql SECURITY DEFINER;

And now we just have to revoke rights to execute this function from public (otherwise any user could call it!):

REVOKE ALL ON FUNCTION create_role( in_role_name TEXT, in_options TEXT ) FROM public;

Now, we can:

> \c - test2
You are now connected to database "depesz" as user "test2".
 
> select create_role('test4', 'login inherit');
create_role
-------------
 
(1 row)
 
> \du test*
List of roles
Role name | Attributes | Member of
-----------+-------------+-----------
test1 | Create role | {}
test2 | | {test1}
test3 | | {}
test4 | | {}

Of course, calling this function requires code change ( assuming previous code called CREATE ROLE directly ), but at least it works around missing privilege inheritance.

Notice that we didn’t have to GRANT any privileges to EXECUTE the function – this function belongs to test1, and since test2 is member of test1 – it can call the function without any explicit grant.