How to create a postgres user with privileges using php query

Question

I want to create a user with all privileges on a table in my database using php but my code is not working properly. I tried with this code:

 $query="CREATE USER $1 WITH password $2";
 $result = pg_prepare($dbh, "", $query);
 $result = pg_execute($dbh, "", array($utente, md5($psw)));

 $query="GRANT ALL PRIVILEGES ON match to $1";
 $result = pg_prepare($dbh, "", $query);
 $result = pg_execute($dbh, "", array($utente));

then I tried changing it moving variables outside the string but it's not working either.

 $query="CREATE USER " .$utente . " WITH password ". md5($psw);
 $result = pg_prepare($dbh, "", $query);
 $result = pg_execute($dbh, "", array());

 $query="GRANT ALL PRIVILEGES ON match to " . $utente;
 $result = pg_prepare($dbh, "", $query);
 $result = pg_execute($dbh, "", array());

$result is false, in my database I don't get the new user I want to create.

Answer 1

• The username is an identifier and can be surrounded by double quotes "" or left as is.
• The password is a text value and has to be surrounded by single quotes ''

In general: all values passed to a query should be treated as prepared statement variables. In this case I was not able to replace $1 with a value in the parameters array in pg_prepare.

So here is a working example with escaping by "hand".

$utente = pg_escape_identifier('username');
$psw = pg_escape_string('pass');

$query="CREATE USER {$utente} WITH password '{$psw}'";
pg_prepare($dbh, "", $query);
$result = pg_execute($dbh, "", []);