Use CREATE USER in Postgres by passing password in variable

Question

I have the following function in PostgreSQL:

create function test_createuser(username varchar, userpassword varchar) returns void as $$
begin
  CREATE USER username WITH PASSWORD userpassword;
end;
$$ language plpgsql;

However this gives the following error:

syntax error at or near "userpassword"

The function compiles if I place instead a literal string as the password, such as 'mypassword'.

Is there a way to call create user and pass the password from a variable?

Answer 1

You'll have to use dynamic SQL:

EXECUTE format('CREATE USER %I PASSWORD %L', username, userpassword);

Answer 2

I still got the same parse error using the EXECUTE method (ERROR: syntax error at or near "2" - Note: the password starts with a "2"). Instead I added string quotation marks when I read the password from the environment variable.

\set psqluser `echo "$PSQL_USER"`
\set psqlpassword `echo "'$PSQL_PASSWORD'"`    -- Add the quotations '' for the password already here
CREATE USER :psqluser WITH ENCRYPTED PASSWORD :psqlpassword;

I don't know if it is good practice, but it works.