Postgresql creating users with a function

Question

I'm currently not able to create a postgresql database user within a function. Background: I have a Java Swing application and my goal is to develop a menu to create, alter and delete database users. To make it a bit more secure I created a role "usermanagement" and only members of this role are allowed to use the function to create users. The role also contains the right "createuser"

The query runs without any problems, but it does not create a new user... So i don't know what's wrong with it.

This is how i try to use my function:

SELECT create_databaseuser(v_username := 'thisname' ,v_password := 'pwpwpw');

Can anyone help?

Here is my code:

-- Function: public.create_databaseuser(text, text)

-- DROP FUNCTION public.create_databaseuser(text, text);

CREATE OR REPLACE FUNCTION public.create_databaseuser(
    v_username text,
    v_password text)
  RETURNS numeric AS
$BODY$
DECLARE
    r_id numeric;
BEGIN
    --CREATE ROLE v_username LOGIN
    --PASSWORD 'v_password' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

    EXECUTE 'CREATE USER ' || v_username || ' WITH PASSWORD ' || v_password;
-- Alternative:CREATE ROLE v_username LOGIN PASSWORD v_password NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

    return 1;
    -- Simple Exception
EXCEPTION
    WHEN others THEN
        RETURN 0;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;
ALTER FUNCTION public.create_databaseuser(text, text)
  OWNER TO postgres;

Answer 1

Note the STRICT (returns NULL on NULL input), uses FORMAT() to help against SQL injection issues, and quotes the inputs properly. The input argument "v_username" was revised to be of type NAME, to match the type in pg_catalog.pg_roles.

DROP FUNCTION IF EXISTS public.create_databaseuser(NAME, TEXT);

CREATE OR REPLACE FUNCTION public.create_databaseuser(
    v_username NAME,
    v_password TEXT)
RETURNS smallint AS
$BODY$
DECLARE
BEGIN
    EXECUTE FORMAT('CREATE ROLE "%I" LOGIN PASSWORD ''%L''', v_username, v_password);
    RETURN 1;
    -- Simple Exception
EXCEPTION
    WHEN others THEN
        RETURN 0;
END;
$BODY$
LANGUAGE plpgsql STRICT VOLATILE SECURITY DEFINER
COST 100;

ALTER FUNCTION public.create_databaseuser(NAME, TEXT) OWNER TO postgres;


select rolname from pg_catalog.pg_roles order by 1;
SELECT create_databaseuser(v_username := 'thisname' ,v_password := 'pwpwpw');
select rolname from pg_catalog.pg_roles order by 1;