Re: create roles as normal user

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

Hi

2016-08-29 7:14 GMT+02:00 Johannes <jotpe@xxxxxxxxx>:

Hello,

I want a "normal" user to create roles inside the database.
Is it only possible through the createrole privilige, or is there
something like sudo possibile.

I thought it is possible with the security definer option in create
function, but that does not work for me: (In this test the function was
created as superuser).

```sql
CREATE OR REPLACE FUNCTION add_db1_user() returns void language sql
STRICT security definer AS $$
CREATE ROLE db1_testuser login password 'secret';
$$

ERROR:  permission denied to create role

your role have to have a CREATEROLE flag


 CREATE ROLE name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | CREATEUSER | NOCREATEUSER
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid

Best regards
Johannes