Skip to main content
Question

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

  • September 6, 2019
  • 4 replies
  • 103 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)? 

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

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

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

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

Community Stats

32,207
Posts
122,305
Replies
39,891
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.