First of all, don't use double quotes around the strings, only use single quotes. In SQL, double quotes are for object names, single quotes are for strings.
Also, you need to add an FME_SQL_DELIMITER definition since there are two separate statements that need to be executed separately.
Which gives us:
FME_SQL_DELIMITER ;
CREATE USER '@Value(username)' WITH PASSWORD '@Value(password)';
GRANT @Value(roles) to '@Value(username)';
Notice that there is a space between FME_SQL_DELIMITER and the semi-colon.
First of all, don't use double quotes around the strings, only use single quotes. In SQL, double quotes are for object names, single quotes are for strings.
Also, you need to add an FME_SQL_DELIMITER definition since there are two separate statements that need to be executed separately.
Which gives us:
FME_SQL_DELIMITER ;
CREATE USER '@Value(username)' WITH PASSWORD '@Value(password)';
GRANT @Value(roles) to '@Value(username)';
Notice that there is a space between FME_SQL_DELIMITER and the semi-colon.
The username has to be in double quotes, because we have a '-' in every username value. With single quotes it throws a syntax error. I don't think that's the problem, until now it worked without problems. Just with a '#' in the password.
I've already using the FME_SQL_DELIMETER statement, I just didn't post it in my code example.
The username has to be in double quotes, because we have a '-' in every username value. With single quotes it throws a syntax error. I don't think that's the problem, until now it worked without problems. Just with a '#' in the password.
I've already using the FME_SQL_DELIMETER statement, I just didn't post it in my code example.
You are indeed right about the double quotes in this instance! I didn't know about this "non-standard" behavior in Postgresql, very interesting.
It looks like PostgreSQL is trying to be intelligent about the password string, which may have weird side effects if e.g. the password starts with a # or contains any backslashes.
You can tell PostgreSQL to not parse the string contents by prefixing it with an E just before the single quotes.
This worked for me:
FME_SQL_DELIMITER ;
CREATE USER "@Value(username)" WITH PASSWORD E'@Value(password)';
GRANT @Value(roles) to "@Value(username)";
Documentation: https://www.postgresql.org/docs/9.2/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE
