CREATE ROLE privilege cannot be inherited?!

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.

6 thoughts on “CREATE ROLE privilege cannot be inherited?!”

  2. @Bernd:

    Sure, it’s possible. But to use, it requires to call 3 sql queries:
    1. set role test1
    2. create role
    3. set role test2

    on the other hand – this function requires only 1 function call. And yes – you could build those “set role, create role, set role” in function itself, but it would be also more complex.

  3. In my experience, you cannot inherit any of the privileges of a superuser. My question is why not? Here is my test:

    $ psql
    psql (9.3.5)
    Type “help” for help.

    postgres=# \du
    List of roles
    Role name | Attributes | Member of
    ———–+————————————————+———–
    postgres | Superuser, Create role, Create DB, Replication | {}

    postgres=# create role my_role inherit login password ‘abcxyz’;
    CREATE ROLE
    postgres=# create role dba superuser createdb createrole noinherit nologin role my_role;
    CREATE ROLE
    postgres=# \q

    $ psql postgres my_role
    Password for user my_role:
    psql (9.3.5)
    Type “help” for help.

    postgres=> \du
    List of roles
    Role name | Attributes | Member of
    ———–+—————————————————————–+———–
    dba | Superuser, No inheritance, Create role, Create DB, Cannot login | {}
    my_role | | {dba}
    postgres | Superuser, Create role, Create DB, Replication | {}

    postgres=> create role test_role;
    ERROR: permission denied to create role
    postgres=> create database test_db;
    ERROR: permission denied to create database
    postgres=> alter role my_role superuser;
    ERROR: must be superuser to alter superusers
    postgres=> \q

    Last one is particularly interesting. It sees I am a superuser, and prevents me from altering myself because I am not a superuser? Messed up.

  5. Thanks. My main question was “Why not?” as opposed to “How?” Your answer did inspire me to search the docs again, and I did find the answer to why stated in the docs, which I had not found before, so again, thanks! (Funny how actually reading the manual does produce results) Not sure I agree with the reasoning, but I see what the intent is.

    http://www.postgresql.org/docs/9.1/static/role-membership.html
    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. You must actually SET ROLE to a specific role having one of these attributes in order to make use of the attribute. Continuing the above example, we might choose to grant CREATEDB and CREATEROLE to the admin role. Then a session connecting as role joe would not have these privileges immediately, only after doing SET ROLE admin.

Comments are closed.