Issues connection to Postgres on Heroku

[davidpdrsn]

I am trying to connect to a postgres database running on Heroku but having some issues related to TSL. Sorry if this isn't the right place to ask, but Google gave me nothing.

Cargo.toml:

[package]
name = "instant-replay"
version = "0.1.0"
authors = ["davidpdrsn"]

[dependencies]
postgres = { version = "0.15", features = ["with-openssl"] }

main.rs:

extern crate postgres;

use postgres::{Connection, TlsMode};
use postgres::tls::openssl::OpenSsl;

fn main() {
    let openssl = OpenSsl::new().expect("failed to create openssl");

    let connection = Connection::connect(
        "postgres://_:_@_:5432/_?sslmode=require",
        TlsMode::Prefer(&openssl),
        ).expect("failed to connect");

    for row in &connection.query("select * from users limit 1", &[]).expect("query failed") {
        println!("{:?}", row);
    }
}

And finally the error

thread 'main' panicked at 'failed to connect: Error(Tls(Failure(MidHandshakeSslStream { stream: SslStream { stream: TcpStream { addr: V4(192.168.1.12:51332), peer: V4(54.228.220.197:5432), fd: 5 }, ssl: Ssl { state: "error", verify_result: X509VerifyError { code: 18, error: "self signed certificate" } } }, error: Ssl(ErrorStack([Error { code: 337047686, library: "SSL routines", function: "tls_process_server_certificate", reason: "certificate verify failed", file: "ssl/statem/statem_clnt.c", line: 1230 }])) })))', src/libcore/result.rs:859
stack backtrace:
   0: std::sys::imp::backtrace::tracing::imp::unwind_backtrace
   1: std::panicking::default_hook::{{closure}}
   2: std::panicking::default_hook
   3: std::panicking::rust_panic_with_hook
   4: std::panicking::begin_panic
   5: std::panicking::begin_panic_fmt
   6: rust_begin_unwind
   7: core::panicking::panic_fmt
   8: core::result::unwrap_failed
   9: <core::result::Result<T, E>>::expect
  10: run::main
  11: __rust_maybe_catch_panic
  12: std::rt::lang_start
  13: main

Any idea what I'm doing wrong?

[sfackler]

Heroku's Postgres instances use a self signed certificate that your server isn't going to trust by default. Last time I checked in on this, there wasn't really a better option than disabling certificate validation unfortunately 😢 .

use openssl::ssl::{SslConnectorBuilder, SSL_VERIFY_NONE};
use postgres::tls::openssl::OpenSsl;

let mut connector = SslConnectorBuilder::new().unwrap();
connector.builder_mut().set_verify(SSL_VERIFY_NONE);
let openssl = OpenSsl::from(connector.build());

One other minor note - the sslmode parameter doesn't do anything in this library. That configuration is handled by the TlsMode enum.

[davidpdrsn]

Thanks! That did it.

For reference this was the code I ended up with:

// add `openssl = "0.9.19"` to Cargo.toml
use openssl::ssl::{SslMethod, SslConnectorBuilder, SSL_VERIFY_NONE};
use postgres::tls::openssl::OpenSsl;

let mut connector = SslConnectorBuilder::new(SslMethod::tls()).unwrap();
connector.builder_mut().set_verify(SSL_VERIFY_NONE);
let openssl = OpenSsl::from(connector.build());

let db_url = env::var("DATABASE_URL").expect("Missing DATABASE_URL env var");

let connection = Connection::connect(
    db_url,
    TlsMode::Prefer(&openssl),
    ).expect("failed to connect");

[dhbradshaw]

It took me awhile to figure out how to update this example to more current package versions. Here's what I have working:

Cargo.toml

[package]
name = "postgres-basics"
version = "0.1.0"
authors = ["dhbradshaw"]
edition = "2018"

[dependencies]
dotenv = "0.13.0"
openssl = "0.10"
postgres = "0.16.0-rc.2"
postgres-openssl = "0.2.0-rc.1"

main.rs

// credential handling
use dotenv::dotenv;
use std::env;

// postgres connection
use openssl::ssl::{SslConnector, SslMethod, SslVerifyMode};
use postgres::Client;
use postgres_openssl::MakeTlsConnector;

fn main() {
    // Read DATABASE_URL from .env file.
    dotenv().ok();

    // Create Ssl postgres connector without verification as required to connect to Heroku.
    let mut builder = SslConnector::builder(SslMethod::tls()).unwrap();
    builder.set_verify(SslVerifyMode::NONE);
    let connector = MakeTlsConnector::new(builder.build());

    // Create client with Heroku DATABASE_URL
    let mut client = Client::connect(
        &env::var("DATABASE_URL").expect("DATABASE_URL must be set"),
        connector,
    ).unwrap();

    //
    // Smoke test for client.
    //
 
    // 1. Create table. 
    client.simple_query("
        CREATE TABLE IF NOT EXISTS person_nonconflicting (
            id      SERIAL PRIMARY KEY,
            name    TEXT NOT NULL,
            data    BYTEA
        )
    ").unwrap();

    // 2. Save a row.
    let name = "Ferris";
    let data = None::<&[u8]>;
    client.execute(
        "INSERT INTO person_nonconflicting (name, data) VALUES ($1, $2)",
        &[&name, &data],
    ).unwrap();

    // 3. Retrieve a row and verify by printing.
    for row in client.query("SELECT id, name, data FROM person_nonconflicting", &[]).unwrap() {
        let id: i32 = row.get(0);
        let name: &str = row.get(1);
        let data: Option<&[u8]> = row.get(2);

        println!("found person_nonconflicting: {} {} {:?}", id, name, data);
    }

    // 4. Clean up your mess by dropping the table.
    client.simple_query("DROP TABLE person_nonconflicting").unwrap();
}

[Melvillian]

I tried @dhbradshaw 's code up above but it doesn't work if you need to add features, such as I did with with-chrono. I'm posting my working code below in case anyone needs to disable SSL verification AND add features.

postgres = { version = "0.15.2", features = ["with-chrono"] }
postgres-openssl = "0.1"
openssl = "0.10"

use openssl::ssl::{SslConnector, SslMethod, SslVerifyMode};
use postgres::{Connection, TlsMode};
use postgres_openssl::OpenSsl;

// Create Ssl postgres connector without verification as required to connect to Heroku.
let mut builder = SslConnector::builder(SslMethod::tls()).unwrap();
builder.set_verify(SslVerifyMode::NONE);
let mut connect = OpenSsl::from(builder.build());
connect.danger_disable_hostname_verification(true);
let conn = Connection::connect(
    env::var("DATABASE_URL").expect("no env var DATABASE_URL"),
    TlsMode::Require(&connect),
)
.unwrap();

[stepankuzmin]

Hi everyone! How can I support both Tls and NoTls connectors?

let tls_connector = match config.get_ssl_mode() {
    postgres::config::SslMode::Disable => NoTls,
    _ => {
        let connector = TlsConnector::new()
            .map_err(|err| io::Error::new(io::ErrorKind::Other, err.to_string()))?;
        MakeTlsConnector::new(connector)
    }
};

let manager = PostgresConnectionManager::new(config, tls_connector);

   |
42 |       let tls_connector = match config.get_ssl_mode() {
   |  _________________________-
43 | |         postgres::config::SslMode::Disable => NoTls,
   | |                                               ----- this is found to be of type `tokio_postgres::tls::NoTls`
44 | |         _ => {
45 | |             let connector = TlsConnector::new()
46 | |                 .map_err(|err| io::Error::new(io::ErrorKind::Other, err.to_string()))?;
47 | |             MakeTlsConnector::new(connector)
   | |             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ expected struct `tokio_postgres::tls::NoTls`, found struct `postgres_native_tls::MakeTlsConnector`
48 | |         }
49 | |     };
   | |_____- `match` arms have incompatible types

[lijunwangs]

@stepankuzmin -- I ran into similar issue -- did you find a solution to it?

[amitu]

Heroku's Postgres instances use a self signed certificate that your server isn't going to trust by default. Last time I checked in on this, there wasn't really a better option than disabling certificate validation unfortunately 😢 .

Is this still the state of art today?