Azure-docs: cannot connect to azure postresql - Username should be in <username@hostname> format

[terchris]

I'm trying to use your PostgreSQL for CKAN (ckan.org)
CKAN is using http://www.sqlalchemy.org/ for database connection.

A connection string looks like this:
engine = create_engine('postgresql://scott:[email protected]:5432/mydatabase')
syntax is: postgresql://username:password@hostname:5432/databasename
When trying to connect to azure postgresql I get this error:

FATAL: Invalid Username specified. Please check the Username and retry connection. The Username should be in username@hostname format.

If I try to use the username format like this:
postgresql://scott@myhost:[email protected]:5432/mydatabase

Then I get:
invalid port number: "[email protected]:5432"

Is there a solution to this ?

Regards
Terje

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: a186ed3c-cc90-158e-9c81-ef4cbceb4dfc
• Version Independent ID: 90b52047-2ca6-1c63-6729-597f468a04da
• Content: Connect to Azure Database for PostgreSQL from Python
• Content Source: articles/postgresql/connect-python.md
• Service: postgresql
• GitHub Login: @rachel-msft
• Microsoft Alias: raagyema

[BryanTrach-MSFT]

@terchris Thanks for the feedback! We are currently investigating and will update you shortly.

[rachel-msft]

Hi Terje,

Since that tool requests a url syntax as follows: postgresql://username:password@hostname:5432/databasename

You would need to escape the '@' in our username@hostname format, because that url syntax expects @ to signify the start of the full hostname.

Please try this format: postgresql://scott%40myhost:[email protected]:5432/mydatabase
Here I'm using the %40 escape for @.

[Mike-Ubezzi-MSFT]

@terchris Can you review the comment by @rachel-msft and let us know if this issue is resolved.
Thank you,
Mike

[Mike-Ubezzi-MSFT]

@terchris When you have a chance to test, please let us know if this resolves your connectivity issue.

[Mike-Ubezzi-MSFT]

@terchris We will now proceed to close this thread. If there are further questions regarding this matter, please reopen it and we will gladly continue the discussion.

[sgandon]

We have tried to escape with %40 and \ and the jdbc driver fails to connect, any idea to solve this please ?

[rachel-msft]

Hi @sgandon, what is the error message you see?

You said you are using %40 and . Where are you using \ ?

[sgandon]

Hi Rachel, thanks for the quick answer, in fact \@ made it work.

[rachel-msft]

Thanks for letting us know

[etiennecelery]

Hi. I tried everything I could think off, including escaping the @ character with %40. I am using sqlalchemy to connect to the database, and I am not able to connect, as the error says "The Username should be in format."
My password has "/" characters and my hostname is similar to "sql-rs-rd423", so it has the "-" character.
If I try to connect with the following URL I get the format error (I edited the password and hostname to a similar-styled one):

postgresql://username:sDsO/43J/TU2%[email protected]:5432/sandbox

Any help will be very much appreciated

[rachel-msft]

Hi @etiennecelery. There a couple things here.

1) It looks like you moved the %40servername lower down in the uri. It is part of the username, before the password. Could you try postgresql://username%40sql-rs-rd423:sDsO/43J/[email protected]:5432/sandbox

2) Your password has "/". Can you change to a password that doesn't use that character? The uri format will think that you are referring to a port number.
If you don't want to change it, you can try doing a uri encoding on the password. Some coding languages have functions that will do this for you.

[etiennecelery]

Hi Rachel and thank you for your reply.
I tried your suggestion unsuccessfully postgresql://username%40sql-rs-rd423:{uri_password}@sql-rs-rd423.database.windows.net:5432/sandbox, with the following error: (psycopg2.OperationalError) FATAL: The server name you tried cannot be found. Please use the correct name and retry. Please check your server name sql-rs-rd423

It is weird because I am able to connect using pyodbc module, so I can confirm that the database exists and that the login credentials are ok. However, when trying to connect via sqlalchemy or psycopg2, I do either get a format error in the username (username@hostname) or I get this "check your server name" error.

I also tried uri formating the password, but the error messages prevail. It has been very frustrating to connect to the database, so any suggestions you have would be greatly appreciated.
Thank you!

[rachel-msft]

@etiennecelery, can you confirm that you are connecting to a Postgres server? In my previous response, I only edited the url you had sent to arrange the components correctly. But now I notice you are currently using .database.windows.net which would be for Azure SQL DB. If this is a Postgres database it should be .postgres.database.azure.com

To be sure, I recommend going to the Azure Portal and selecting the Postgres server. On the Overview page you will see your server's fully defined host name.

[andreas-wilm]

For the sake of completeness: on Ubuntu 18.04 the correct version is "postgresql://user@myhost:[email protected]:5432/mydatabase

No escaping needed.

(ckan 2.8.4, python-sqlalchemy 1.1.11, PostgreSQL version 10)