Infrastructure at your Service

Daniel Westermann

Creating PostgreSQL users with a PL/pgSQL function

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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure