Hanami: In DATABASE_URL I can't use a password containing special characters

[olistik]

I’m trying to set up the credentials within DATABASE_URL and I’m having some issues with special characters.

In .env.development I have DATABASE_URL="postgresql://deployer:p@ss@localhost/bookshelf_development” (notice the password p@ss).

When I try to connect from the command line via psql I can authenticate (I typed p@ss when asked for the password):

$ psql --username deployer --password bookshelf_development
Password for user deployer:
psql (9.6.2)
Type "help" for help.
bookshelf_development=>

As per this very same issue in the Sequel project I tried to change DATABASE_URL into:

DATABASE_URL="postgresql://deployer:p%40ss@localhost/bookshelf_development”

but when I invoke bundle exec hanami db console I get this authentication error:

$ bundle exec hanami db console
psql: FATAL:  password authentication failed for user "deployer"

[kaikuchn]

I guess this is related: https://github.com/hanami/model/issues/402

Okay, if I surmise this correctly, then you are expected to encode your connection string yourself as required. Nobody should decode it anywhere nor mutate it - it should just be passed to postgres and everything should just work.

I'll try to reproduce this behaviour.

Edit: I am unable to reproduce this. I created a new project, added a database and user using p@ssword as the password. I required password authentication for my local postgres installation and tested that I am required to enter a password to connect.
I then created a table, generated a model and created a record in the database from within the hanami console.

It seems to work using Hanami 1.0.0, this is my database url: DATABASE_URL="postgres://ptest:p%40ssword@localhost/ptest"

[olistik]

@kaikuchn

Okay, if I surmise this correctly, then you are expected to encode your connection string yourself as required. Nobody should decode it anywhere nor mutate it - it should just be passed to postgres and everything should just work.

You nailed it in the head :-)

I am unable to reproduce this.

I'm using an OpenBSD 6.1 VM, I don't know if there are differences in my testing environment.
I'm going to test the issue in another environment.

One question related to your comment on the referenced issue:

Any significant symbols must be URL encoded

you mean percent encoded, right?

[kaikuchn]

It's the same, as the wikipedia article states ;)

Percent-encoding, also known as URL encoding [...]

I'll be happy to assist in finding out why it isn't working for you. I assume you are using Hanami 1.0?

[olistik]

It's the same, as the wikipedia article states ;)

Percent-encoding, also known as URL encoding [...]

I missed that bit :-D

I'll be happy to assist in finding out why it isn't working for you.

🙇

I assume you are using Hanami 1.0?

Yes, I am. Here's a more detailed context of my environment:

olistik@devuan:~$ uname -a
Linux devuan 3.16.0-4-amd64 #1 SMP Debian 3.16.43-2+deb8u2 (2017-06-26) x86_64 GNU/Linux
olistik@devuan:~$ chruby --version
chruby: 0.3.9
olistik@devuan:~$ ruby-install --version
ruby-install: 0.6.1
olistik@devuan:~$ chruby ruby
olistik@devuan:~$ ruby -v
ruby 2.4.1p111 (2017-03-22 revision 58053) [x86_64-linux]
olistik@devuan:~$ gem --version
2.6.11
olistik@devuan:~$ bundle --version
Bundler version 1.15.1
olistik@devuan:~$ hanami --version
v1.0.0
olistik@devuan:~$ psql --version
psql (PostgreSQL) 9.4.12
olistik@devuan:~$ /etc/init.d/postgresql status
9.4/main (port 5432): online

In pg_hba.conf I then changed the METHOD for the TYPE local from peer to md5:

olistik@devuan:~/bookshelf$ sudo vi /etc/postgresql/9.4/main/pg_hba.conf
# [...]
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# [...]

and then issued a full restart of the service:

olistik@devuan:~/bookshelf$ sudo /etc/init.d/postgresql restart
[ ok ] Restarting PostgreSQL 9.4 database server: main.

the setup of Postgres:

olistik@devuan:~$ sudo su postgres
Password:
postgres@devuan:~$ psql
psql (9.4.12)
Type "help" for help.

postgres=# create user deployer with encrypted password 'p@ss';
CREATE ROLE
postgres=# create database bookshelf_development owner deployer;
CREATE DATABASE
postgres=# grant all privileges on database bookshelf_development to deployer;
GRANT

The password I'm entering at the prompt is p@ss:

olistik@devuan:~$ psql -U deployer -W bookshelf_development
Password for user deployer:
psql (9.4.12)
Type "help" for help.

bookshelf_development=>

therefore a direct connection with psql works.

olistik@devuan:~/bookshelf$ cat .env.development
# Define ENV variables for development environment
DATABASE_URL="postgresql://deployer:p%40ss@localhost/bookshelf_development"
SERVE_STATIC_ASSETS="true"
WEB_SESSIONS_SECRET="7b4aa67fc243576fb0dd9a786e2a2d4c41da0b083ff4be8053b827c1337930ba"

finally, I try to invoke the db console:

olistik@devuan:~/bookshelf$ bundle exec hanami db console
psql: FATAL:  password authentication failed for user "deployer"
FATAL:  password authentication failed for user "deployer"

[olistik]

and if I use the simpler password pass, everything works fine:

olistik@devuan:~/bookshelf$ sudo su postgres
[sudo] password for olistik:
postgres@devuan:/home/olistik/bookshelf$ psql
psql (9.4.12)
Type "help" for help.

postgres=# alter user deployer with encrypted password 'pass';
ALTER ROLE
postgres=# \q
postgres@devuan:/home/olistik/bookshelf$ exit
olistik@devuan:~/bookshelf$ chruby ruby
olistik@devuan:~/bookshelf$ bundle exec hanami db console
psql (9.4.12)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

bookshelf_development=>

[kaikuchn]

Could you check if using method password instead of md5 would work?

[olistik]

resetting the password to p@ss:

postgres=# alter user deployer with encrypted password 'p@ss';
ALTER ROLE

and changing (one by one) every method from md5 to password:

olistik@devuan:~/bookshelf$ sudo vi /etc/postgresql/9.4/main/pg_hba.conf
# [..]
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     password
# IPv4 local connections:
host    all             all             127.0.0.1/32            password
# IPv6 local connections:
host    all             all             ::1/128                 password
# [..]

each change followed by a full restart of the service:

olistik@devuan:~/bookshelf$ sudo /etc/init.d/postgresql restart
[ ok ] Restarting PostgreSQL 9.4 database server: main.

and by a test with the hanami command:

olistik@devuan:~/bookshelf$ bundle exec hanami db console
psql: FATAL:  password authentication failed for user "deployer"
FATAL:  password authentication failed for user "deployer"

[kaikuchn]

After a lengthy investigation @olistik and I found out that passwords passed to postgres via the PGPASSWORD environment variable should not be percent/url encoded.
Even though the docs state that it behaves the same as the url parameter, it does not. We therefore need to decode the password at this line: https://github.com/hanami/model/blob/master/lib/hanami/model/sql/consoles/postgresql.rb#L62

~/code/notification master*
❯ PGPASSWORD=p%40ssword psql -d ptest -U ptest                                                                                                                                                                                                                                                                                                                     130
psql: FATAL:  password authentication failed for user "ptest"

~/code/notification master*
❯ PGPASSWORD=p@ssword psql -d ptest -U ptest                                                                                                                                                                                                                                                                                                                         2
psql (9.6.3)
Type "help" for help.

ptest=>

This is oviously at odds with the connection string, where we need to url encode passwords:

❯ bundle console
irb(main):001:0> PG.connect('postgresql://ptest:p%40ssword@localhost/ptest')
=> #<PG::Connection:0x007fc549dffac8>
irb(main):002:0> PG.connect('postgresql://ptest:p@ssword@localhost/ptest')
URI::InvalidURIError: bad URI(is not URI?): postgresql://ptest:p@ssword@localhost/ptest
[...]
irb(main):003:0> PG.connect('postgresql://ptest:password@localhost/ptest')
PG::ConnectionBad: FATAL:  password authentication failed for user "ptest"
[...]
irb(main):004:0>

[TiteiKo]

Great work :+1:
@olistik @kaikuchn anyone of you would have a bit of time to submit a PR on this?