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

Question

I'm trying to use azure 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"

Has anyone solved this?

Answer 1

The invalid port number is caused by the @ in the user name.

The connection url should look like this:

postgresql://scott%40myhost:[email protected]:5432/mydatabase

Answer 2

Extending Allan Kristensen's answer, you'll end up with problem if you're using the datastore extension (Bundled with CKAN Core).

Looking at the codebase of CKAN the problem is in https://github.com/ckan/ckan/blob/555e0960c43d0ca86066b1954e5c94aad565baa7/ckanext/datastore/backend/postgres.py#L1592 (or https://github.com/ckan/ckan/blob/ckan-2.6.4/ckanext/datastore/plugin.py#L200 if you're running 2.6.5 or less)

Thus, when CKAN queries the database with SELECT has_table_privilege('scott@myhost', '_foo', 'INSERT') it's fail, as there's no user named scott@myhost. Azure is doing something funky with the usernames, as if you ask the database to list the users, you'll see that the database thinks your username is scott (Atleast that's what it did for me...)

One possible, hackish, fix is:

sed -i '' 's/read_connection_user = sa_url.make_url(self.read_url).username/read_connection_user = split(sa_url.make_url(self.read_url).username, "@")[0]/' /srv/app/src/ckan/ckanext/datastore/plugin.py
sed -i '' 's/import sys/import sys\nfrom string import split/' /srv/app/src/ckan/ckanext/datastore/plugin.py

The above works for 2.6.5. Substitute the file path with /srv/app/src/ckanext/datastore/backend/postgres.py if you're running 2.7+

The above should work until upstream have found a proper fix.