Problems connecting dirt to postgres

[gagecarto]

I am having some issues connecting dirt to my database. The specified user definitely has privileges to the tables but tile calls always come back as

{"statusCode":500,"error":"Internal Server Error","message":"unable to connect to database server"}

This is returned from a tile call like below to my table called nalines
http://127.0.0.1:3000/v1/mvt/nalines/6/19/24

I am running dirt locally and my connection string is like below. Could the issue be the sslmode parameter?
postgresql://myusername:mypassword@myhostlocation:25060/defaultdb?sslmode=require

My connection string should be right on and was pulled from the Digital Ocean config page where I am hosting the postgresql. Any suggestions for troubleshooting the connection further?

[tobinbradley]

It's really hard to say what's going on there. Things I'd check:

• Postgres is running
• You can ping myhostlocation from the machine running dirt (i.e. is it a DNS problem?)
• pg_hba.conf is set to allow connections from the server dirt is on
• postgressql.conf is allowing connections over tcp/ip
• Make sure a firewall isn't eating your port
• Check to see if you're using ssl correctly. I haven't used that, but most configs I've seen involve certs and whatnot (see https://node-postgres.com/features/ssl).
• Check other services, like list layers or columns
• Try connecting to Postgres from the dirt server using psql (or anything else)

Sorry I can't be more specific, but that error is the postgres client basically saying "I can't get there from here".

[gagecarto]

Hmmm.. Yeah, kinda weird.. I can connect to my DB via python like below but not sure where Dirt or Fastify is hanging up. This works both with and without the sslmode=require piece

import psycopg2 as db; conn = db.connect("postgresql://myusername:mypassword@myhostlocation:25060/defaultdb"); print(conn.get_backend_pid()); conn.close()

[tobinbradley]

fastify-postgres uses node-postgres. You could try a sanity check with it.

• make and get in a folder
• npm init -y
• npm install pg
• touch index.js

In index.js, drop this with your credentials:

const { Pool, Client } = require('pg')

const pool = new Pool({
  user: 'dbuser',
  host: 'database.server.com',
  database: 'mydb',
  password: 'secretpassword',
  port: 3211,
})

pool.query('SELECT NOW()', (err, res) => {
  console.log(err, res)
  pool.end()
})

• node index.js

See what you get. If nothing else you might get a more detailed error message. Also, I just bumped all the project dependencies to the latest. You could do a git pull and see if that shook anything loose. I doubt it - I've never seen this problem before - but it'll rule out a bug in a dependency that got patched.

[tobinbradley]

Closing this as old and declaring victory.