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?

Laurenz Albe · Accepted Answer · 2018-03-02 19:55:21Z

8

You'll have to use dynamic SQL:

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

answered Mar 2, 2018 at 19:55

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

cat_in_hat Over a year ago

this line is subject to SQL injections and I should address that, is that correct?

Laurenz Albe Over a year ago

This line is not vulnerable to SQL injection. It's good that you think about these things though! %I and %L will escape everything properly.

isgoed · Accepted Answer · 2020-12-07 15:30:33Z

3

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.

answered Dec 7, 2020 at 15:30

isgoed

8107 silver badges14 bronze badges

Collectives™ on Stack Overflow

Use CREATE USER in Postgres by passing password in variable

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related