Connecting to a database with sslmode=require fails

[sunaurus]

• Did you check to see if this issue already exists?
• Is this only a single bug? Do not put multiple bugs in one issue.
• Is this a question or discussion? Don't use this, use https://lemmy.ml/c/lemmy_support .
• Is this a UI / front end issue? Use the lemmy-ui repo.

Issue Summary

Some major managed database service providers require TLS connections to the database. For example, DigitalOcean:

... you must use SSL to transmit data because it prevents eavesdropping on administrative usernames and passwords as well as the data itself as it is transmitted ...

Currently, with Lemmy, running migrations using sslmode=require will succeed, but once migrations are done, the server fails to start due to it being unable to connect to the database.

The root cause seems to be that the diesel_async library does not support creating TLS connections out of the box: weiznich/diesel_async#73 (comment)

It's possible to work around this by using for example something like rustls, example: hexbear-collective@79d9452.

Note that passing sslmode=require seems to only be possible when setting database connection string through an environment variable, there is no option to pass this through lemmy.hjson.

Steps to Reproduce

1. Spin up a postgres server that only allows SSL connections
2. Start up a Lemmy backend that passes sslmode=require through the database connection string

Would you accept a PR to this repo that adds additional dependencies in order to enable TLS connections to the database?

[jgrim]

@sunaurus I'd suggest you update your changes for the latest release and then create a PR. My hope is that it gets accepted faster that way 😄

[tristanisham]

//lemmy/crates/db_schema/src/utils.rs
/// Establishes a connection using TLS with Postgres in an attempt to allow DO hosted PG
fn establish_connection(url: &str) -> BoxFuture<ConnectionResult<AsyncPgConnection>> {
  (async {
    let mut cert_file = File::open(std::env::var("LEMMY_DATABASE_CERT").unwrap()).unwrap();
    let mut buf: Vec<u8> = vec![];
    cert_file.read_to_end(&mut buf).unwrap();
    let cert = Certificate::from_pem(&buf).unwrap();

    let mut builder = TlsConnector::builder();
    builder.add_root_certificate(cert);
    let connector = postgres_native_tls::MakeTlsConnector::new(
      builder.build().expect("Unable to build TLS connector"),
    );
    let (client, connection) = tokio_postgres::connect(url, connector)
      .await
      .map_err(|e| ConnectionError::BadConnection(e.to_string()))?;
    tokio::spawn(async move {
      if let Err(e) = connection.await {
        eprintln!("connection error: {}", e);
      }
    });
    AsyncPgConnection::try_from(client).await
  })
  .boxed()
}

async fn build_db_pool_settings_opt(settings: Option<&Settings>) -> Result<DbPool, LemmyError> {
  let db_url = get_database_url(settings);
  let pool_size = settings.map(|s| s.database.pool_size).unwrap_or(5);
  let manager = AsyncDieselConnectionManager::<AsyncPgConnection>::new_with_setup(
    &db_url,
    establish_connection,
  );
  let pool = Pool::builder(manager)
    .max_size(pool_size)
    .wait_timeout(POOL_TIMEOUT)
    .create_timeout(POOL_TIMEOUT)
    .recycle_timeout(POOL_TIMEOUT)
    .runtime(Runtime::Tokio1)
    .build()?;

  // If there's no settings, that means its a unit test, and migrations need to be run
  if settings.is_none() {
    run_migrations(&db_url);
  }
  

  Ok(pool)
}

Here's my solution. Just replace these function in lemmy/crates/db_schema/src/utils.rs and add these to that library's Cargo.toml

lemmy/crates/db_schema/src/Cargo.toml
futures = "0.3.28"
future-utils = "0.12.1"
tokio-postgres = "0.7.8"
postgres-native-tls = "0.5.0"
native-tls = "0.2.11"