Vapor Framework : Configure a postgres connection with SSL

Question

I'm trying to connect to my Heroku PostgreSQL database but I have the following error :

cannotEstablishConnection("FATAL:  no pg_hba.conf entry for host \"37.167.93.189\", user \"clpnkpyttmdtyq\", database \"d3h6147v73mgtu\", SSL off\n")

I know that Heroku postgres databases need to use an SSL connection but I don't know how to configure the connection on my Droplet object.

This is my postgresql.json configuration file :

{
    "host": "ec2-54-163-224-108.compute-1.amazonaws.com",
    "user": "clpnkpyttmdtyq",
    "password": "99201aa07c48e18e7bdf210937857b85bee37cd8d8cb904381b1ddff934c7a4f",
    "database": "d3h6147v73mgtu",
    "port": 5432
}

Maybe there is ssl parameter that I don't know ?

How I add the VaporPostgresSQLProvider :

let drop = Droplet()

// Tell the droplet to use our SQL provider service
try drop.addProvider(VaporPostgreSQL.Provider.self)

Any ideas ?

When I try with my local postgres database, it works, because it don't need ssl connection.

Answer 1

For Heroku we need unverifiedTLS transport. https://api.vapor.codes/postgresql/latest/PostgreSQL/Classes/PostgreSQLConnection/TransportConfig.html

let pgURL = Environment.get("DATABASE_URL") ?? "postgres://user:password@host:port/database"

let pgConfig = PostgreSQLDatabaseConfig(url: pgURL, transport: PostgreSQLConnection.TransportConfig.unverifiedTLS)!

Answer 2

it's a process that personally cost me a lot, this solution works for me, try this

---

On file Config > secrets > postgresql.json add this configuration (for use on local or remote, if this file not exist, create this)

{
   "host": "127.0.0.1",
   "user": "your_user_pc", 
   "password": "",
   "database": "your_user_pc",
   "port": 5432
}

The user can get it from Terminal

$ cd ~

---

On your file Procfile (sited on your project, show via finder) edit and add this code

web: App --env=production --workdir="./"
web: App --env=production --workdir=./ --config:servers.default.port=$PORT --config:postgresql.url=$DATABASE_URL

---

Now you can re-launch your application to heroku, you should consider having the server configured correctly from heroku with all its credentials and add-ons of Postgresql from the interface of Heroku

Note: And do not forget every change you make, run "vapor build" or "vapor build --clean"

Answer 3

Vapor 4 + build stack heroku-20 + Heroku Postgres's standard plan

Rijel David's suggestion did the trick for me

But unverifiedTLS syntax has slightly changed

if let databaseURL = Environment.get("DATABASE_URL"), var postgresConfig = PostgresConfiguration(url: databaseURL) {
    postgresConfig.tlsConfiguration = .forClient(certificateVerification: .none)
    app.databases.use(.postgres(
        configuration: postgresConfig
    ), as: .psql)
} else {
    // ...
}

Check out Vapor doc - https://docs.vapor.codes/4.0/deploy/heroku/