Re: create roles as normal user

Hi

2016-08-29 7:14 GMT+02:00 Johannes <jotpe(at)posteo(dot)de>:

> 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
>
>