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;

Wivinia

This might be better posted to the sibling site, dba.StackExchange.com – Saintebeuve

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

Wotton

Thank you very much. It is working. In the meantime I just used the plain sql in my application, but now I can change it and use the function which is much better :) – Wivinia

Do you also have a suggestion if i want to use a plain number as pw? I call the function with 'pw123' and that is working, but '12345' is not working. Actually if found out it is working. But the pw will not be 12345 after that. I checked the md5 hashes as i changed the pw with pgadmin and with my function. The password is stored as "12345" after that. So i could solve this with a reg expression inside of the function. Now if i want to go further and use special characters like $ it is not working as well. How could i solve this issue? – Wivinia

The password should have been substituted using %L (for Literal), not %I (for Identifier). I've edited the function body to fix. – Specialistic