Skip to main content
Question

Can't login to PostgreSQL with password containing '#' to user created with FME SQLExecutor

  • September 6, 2019
  • 4 replies
  • 91 views
P

My FME workspace runs a SQLExecutor which creates an user with provided username and password as attribute:

CREATE USER "@Value(username)" WITH PASSWORD '@Value(password)';
GRANT @Value(roles) to "@Value(username)";

On containing a '#' in the password value, I'm not able to connect to a database through that created user in QGIS. Unfortunately I can't expose the password handed over in pgAdmin to check the password set by FME. After changing the password in pgAdmin to another password containing the '#' as well, the connection works. Therefore I suppose the error to be within the SQL Executor. Does FME encode that character in some way, so that the '#' is stored as other character(s)? 

4 replies

david_r
Celebrity
  • david_rCelebrity
  • September 6, 2019

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.

P
  • pascatl
  • Author
  • September 6, 2019
david_r wrote:

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.
david_r
Celebrity
  • david_rCelebrity
  • September 6, 2019
pascatl wrote:

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.

david_r
Celebrity
  • david_rCelebrity
  • September 6, 2019

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

Reply


Helpful Members This Week

Community Stats

31,344
Posts
118,876
Replies
38,937
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.