Sometimes you might want to create users in PostgreSQL using a function. One use case for this is, that you want to give other users the possibility to create users without granting them the right to do so. How is that possible then? Very much the same as in Oracle you can create functions in PostgreSQL that either execute under the permission of the user who created the function or they run under the permissions of the user who executes the function. Lets see how that works.
Here is a little PL/pgSQL function that creates a user with a given password, does some checks on the input parameters and tests if the user already exists:
create or replace function f_create_user ( pv_username name , pv_password text ) returns boolean as $$ declare lb_return boolean := true; ln_count integer; begin if ( pv_username is null ) then raise warning 'Username must not be null'; lb_return := false; end if; if ( pv_password is null ) then raise warning 'Password must not be null'; lb_return := false; end if; -- test if the user already exists begin select count(*) into ln_count from pg_user where usename = pv_username; exception when no_data_found then -- ok, no user with this name is defined null; when too_many_rows then -- this should really never happen raise exception 'You have a huge issue in your catalog'; end; if ( ln_count > 0 ) then raise warning 'The user "%" already exist', pv_username; lb_return := false; else execute 'create user '||pv_username||' with password '||''''||'pv_password'||''''; end if; return lb_return; end; $$ language plpgsql;
Once that function is created:
postgres=# df List of functions Schema | Name | Result data type | Argument data types | Type --------+---------------+------------------+------------------------------------+------ public | f_create_user | boolean | pv_username name, pv_password text | func (1 row)
… users can be created by calling this function when connected as a user with permissions to do so:
postgres=# select current_user; current_user -------------- postgres (1 row) postgres=# select f_create_user('test','test'); f_create_user --------------- t (1 row) postgres=# du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} test | | {}
Trying to execute this function with a user that does not have permissions to create other users will fail:
postgres=# create user a with password 'a'; CREATE ROLE postgres=# grant EXECUTE on function f_create_user(name,text) to a; GRANT postgres=# c postgres a You are now connected to database "postgres" as user "a". postgres=> select f_create_user('test2','test2'); ERROR: permission denied to create role CONTEXT: SQL statement "create user test2 with password 'pv_password'" PL/pgSQL function f_create_user(name,text) line 35 at EXECUTE
You can make that work by saying that the function should run with the permissions of the user who created the function:
create or replace function f_create_user ( pv_username name , pv_password text ) returns boolean as $$ declare lb_return boolean := true; ln_count integer; begin ... end; $$ language plpgsql security definer;
From now on our user “a” is allowed to create other users:
postgres=> select current_user; current_user -------------- a (1 row) postgres=> select f_create_user('test2','test2'); f_create_user --------------- t (1 row) postgres=> du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- a | | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} test | | {} test2 | | {}
Before implementing something like this consider the “Writing SECURITY DEFINER Functions Safely” section in the documentation, there are some points to consider such as this:
postgres=# revoke all on function f_create_user(name,text) from public; REVOKE
… and correctly setting the search_path.