0

I recently spun up a Postgres database on Azure and I'm having a lot of trouble figuring out my DB URI. The creds look something like:

Server: name.postgres.database.azure.com

User (admin role): admin@name

Password: mypassword

DB name: postgres

So I'm trying to use psql and pass in the connection string, psql postgresql://admin@name:mypassword@name.postgres.database.azure.com:5432/postgres

But it's giving me an error

psql: could not translate host name "name" to address: nodename nor servname provided, or not known

Interestingly enough, this works:

psql -h name.postgres.database.azure.com -p 5432 -U admin@name postgres

The reason why I ask is because I want to use Python and sqlalchemy and want to pass in the DB uri string.

Gary Liu
  • 13,758
  • 1
  • 17
  • 32
Vincent
  • 7,808
  • 13
  • 49
  • 63

3 Answers3

1

Ok at least for me, this works. Posting here in case others run into the same issue.

  1. For connecting to SQLAlchemy passing in this DB URI worked for me: postgresql://user:password@hostname:port/dbname. For example,

postgresql://admin@name:password@name.postgres.database.azure.com:5432/postgres

  1. For using psql in command line, the following worked for me: psql "dbname=postgres host=name.postgres.database.azure.com user=admin@name password=password port=5432"

Note, be careful to escape any special characters (e.g. for your password). For example, I'm using a bash shell and having special characters threw some weird oddities.

  1. For connecting to psycopg2 I followed the instructions here: https://learn.microsoft.com/en-us/azure/postgresql/connect-python

You can also parse a URL following the instructions here: Connect to an URI in postgres

Vincent
  • 7,808
  • 13
  • 49
  • 63
0

Per my experience, the error of using connection url as connection string for psql was caused by the psql connection string parser using Greed-Pattern algorithm.

According to the subsection 31.1.2. Parameter Key Words for psql connection string, the user parameter key word is defined as below.

user

PostgreSQL user name to connect as. Defaults to be the same as the operating system name of the user running the application.

The user naming need to match the regular expression configured via the NAME_REGEX configuration variable, as below, please see the document Linux Username Conventions to know the details.

NAME_REGEX

User and group names are checked against this regular expression. If the name doesnt match this regexp, user and group creation in adduser is refused unless --force-badname is set. With --force-badname set, only weak checks are performed. The default is the most conservative ^[a-z][-a-z0-9]*$.

So the postgres user name on Azure is not a standard Unix-like name, which match Azure user naming pattern username@servname for Azure services, such as FTP for Azure App service, Azure SQL Database, and Postgres on Azure, etc.

The above cause that the Azure postgres connection url is not Compatible with psql tool, but don't worry about using Python with psycopg2, you can refer to the offical tutorial Azure Database for PostgreSQL: Use Python to connect and query data to know it.

Peter Pan
  • 23,476
  • 4
  • 25
  • 43
  • Note that in terms of connecting with psql, following the documentation here: http://postgresguide.com/utilities/psql.html, I actually was able to connect using: `psql "dbname=dbhere host=hosthere user=userhere password=pwhere port=5432"`. But yes thanks for link to the Azure official tutorial on connecting to python with postgres – Vincent Jun 24 '17 at 18:34
  • I'm having trouble connecting via sqlalchemy. I'm able to connect via psycopg2 I think.. – Vincent Jun 29 '17 at 02:34
  • @Vincent Could you post a new thread for the trouble connecting via sqlalchemy? – Peter Pan Jun 29 '17 at 02:55
0

I was running into issues where this was hard using rfc3986_parser in a Docker image I had to use. So in the end I just URLEncoded the first @, in the URL. %40 seemed to make it through the parser and was able to connect then. Guess the Parser splits by @.

Beararmy
  • 1