DB connection params documentation

[benstadin]

Following the installation guide, the database connection cannot be established:

ERROR 2018-11-19T23:24:23Z: r2d2: database error: FATAL: password authentication failed for user "hecate"

It does work however with user postgres for the db writer role. For example, this will work:

cargo run -- --database_read "hecate_read:123456@localhost:5432/hecate" --database "postgres:123456@localhost:5432/hecate"

This doesn't work:

cargo run -- --database_read "hecate_read:123456@localhost:5432/hecate" --database "hecate:123456@localhost:5432/hecate"

(I changed passwords prior to running the example above in order to rule out empty password config issues).

[ingalls]

@benstadin thanks a ton for opening an issue! Let's rule out the low lying fruit.

• Did you run the schema file located here? https://github.com/mapbox/Hecate/blob/master/src/schema.sql
  • Once you create a new database it can be loaded with psql -U postgres -f src/schema.sql <DATABASE_NAME>
• Can you confirm the hecate and hecate_read users were created successfully?
  • echo "\du" | psql -U postgres will output a list of users

  Role name  |                         Attributes                         | Member of 
-------------+------------------------------------------------------------+-----------
 hecate      | No inheritance                                             | {}
 hecate_read | No inheritance                                             | {}
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

• Do all users have local access enabled? If you are running ubuntu I've had trouble with this one before when initially trying to get set up.
  • echo "show hba_file;" | psql -U postgres will print the path location
  • If you can take a look and ensure local connections are trusted that would be great!
  • I typically use the following on my dev machine

local all postgres trust
local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
host replication postgres samenet trust

Let me know if any of these fix the problem, if so I'd love to update the docs with some troubleshooting help.

[benstadin]

@benstadin thanks a ton for opening an issue! Let's rule out the low lying fruit.

• Did you run the schema file located here? https://github.com/mapbox/Hecate/blob/master/src/schema.sql

Yes. I used the same DB name, commands and users as in the Build example.

• Once you create a new database it can be loaded with psql -U postgres -f src/schema.sql <DATABASE_NAME>
  • Can you confirm the hecate and hecate_read users were created successfully?

Yes.

• echo "\du" | psql -U postgres will output a list of users

  Role name  |                         Attributes                         | Member of 
-------------+------------------------------------------------------------+-----------
 hecate      | No inheritance                                             | {}
 hecate_read | No inheritance                                             | {}
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

  Role name  |                         Attributes                         | Member of 
-------------+------------------------------------------------------------+-----------
 hecate      | No inheritance                                             | {}
 hecate_read | No inheritance                                             | {}
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

• Do all users have local access enabled? If you are running ubuntu I've had trouble with this one before when initially trying to get set up.

  • echo "show hba_file;" | psql -U postgres will print the path location
  • If you can take a look and ensure local connections are trusted that would be great!
  • I typically use the following on my dev machine

local all postgres trust
local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
host replication postgres samenet trust

Let me know if any of these fix the problem, if so I'd love to update the docs with some troubleshooting help.

This is what it's showing for me:

# 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
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     md5
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

My environment:

• OS X 10.13.6
• postgres 11.1
• rust nightly as per build instruction

Update I updated the postgres config and it works now. Thanks! The error message ("password authentication failed for user ...") is misleading. It's probably from the driver, though it should rather say the connection failed for that user.

[ingalls]

@benstadin yeah this is an error passed via the driver. I have a ticket here: https://github.com/mapbox/Hecate/issues/90 to improve initial database connection errors.

Going to close this one out and add the hba fix to the setup docs. Thanks a ton for reporting this, look forward to more bug reports if you find any!

[mapsgeek]

I'm following all the instructions for the installation but i keep getting this error ERROR: Connection unable obtain postgres version using (READ/WRITE) hecate@localhost:5432/hecate however i can connect to the database using the hecate user from pgadmin and everything looks fine from the command line. any thoughts?

[ingalls]

@mapsgeek What version of Postgres/Postgis are you using?

[mapsgeek]

v14, postgis 3.1 should i downgrade?!