#77 [Bug]: TLS connection: bad protocol version when connecting to postgresql via kubectl port-forward

[Anonymous]

Originally created by: sycured
Originally owned by: debba

Describe the bug

When trying to connect to a PostgreSQL read replica deployed with the cloudnative-pg operator and forwarding the port to localhost, I'm getting a bad protocol version error on the TLS connection.

It's not a duplicate of [#75]

To Reproduce

1. install cloudnative-pg operator
2. deploy a postgresql with default configuration
3. port-forward
4. get error when trying to connect

OS Version

macOS 15.7.4

Tabularis Version

0.9.6

Relevant Log Output

[Anonymous]

Originally posted by: debba

Looks very similar to [#75] , but need to investigate better and you told me it’s not .
Are you able to debug from code?

[Anonymous]

Originally posted by: sycured

Are you able to debug from code?

Unfortunately, I think that it's coming from sqlx:

• https://github.com/launchbadge/sqlx/discussions/1645
• https://github.com/launchbadge/sqlx/issues/3264
• https://github.com/aws-samples/aurora-dsql-samples/issues/150
• https://github.com/launchbadge/sqlx/pull/3879
• https://github.com/launchbadge/sqlx/issues/3880

I'll try to rebuild your code with debugging to identify the root cause.
My opinion: it's the TLS version (TLS 1.3) and/or the CA certificate

[Anonymous]

Originally posted by: debba

Great, keep me updated.
I’m also open to switch from SQLx in case it does not support TLS

[Anonymous]

• assigned_to: debba

[Anonymous]

Originally posted by: sycured

OK, a quick test and it works

diff --git a/src-tauri/src/pool_manager.rs b/src-tauri/src/pool_manager.rs
index cd839eb..1d10308 100644
--- a/src-tauri/src/pool_manager.rs
+++ b/src-tauri/src/pool_manager.rs
@@ -1,6 +1,6 @@
 use crate::models::ConnectionParams;
 use once_cell::sync::Lazy;
-use sqlx::{MySql, Pool, Postgres, Sqlite};
+use sqlx::{MySql, Pool, Postgres, Sqlite, postgres::{PgConnectOptions,PgSslMode}};
 use std::collections::HashMap;
 use std::sync::Arc;
 use tokio::sync::RwLock;
@@ -45,17 +45,14 @@ fn build_mysql_url(params: &ConnectionParams) -> String {
     )
 }

-fn build_postgres_url(params: &ConnectionParams) -> String {

-    let user = encode(params.username.as_deref().unwrap_or_default());
-    let pass = encode(params.password.as_deref().unwrap_or_default());
-    format!(
-        "postgres://{}:{}@{}:{}/{}",
-        user,
-        pass,
-        params.host.as_deref().unwrap_or("localhost"),
-        params.port.unwrap_or(5432),
-        params.database
-    )
+fn build_postgres_url(params: &ConnectionParams) -> PgConnectOptions {
+    PgConnectOptions::new()
+    .username(params.username.as_deref().unwrap_or_default())
+    .password(params.password.as_deref().unwrap_or_default())
+    .port(params.port.unwrap_or(5432))
+    .host(params.host.as_deref().unwrap_or_default())
+    .database(&format!("{}", params.database))
+    .ssl_mode(PgSslMode::Require)
 }

 fn build_sqlite_url(params: &ConnectionParams) -> String {
@@ -152,7 +149,7 @@ pub async fn get_postgres_pool_with_id(
     let url = build_postgres_url(params);
     let pool = sqlx::postgres::PgPoolOptions::new()
         .max_connections(10)

-        .connect(&url)
+        .connect_with(url)
         .await
         .map_err(|e| {
             log::error!("Failed to create PostgreSQL connection pool: {}", e);

So the best could be to add an SSL mode in the UI to permit selecting the mode for the connection (it's an enum: https://docs.rs/sqlx/latest/sqlx/postgres/enum.PgSslMode.html)

edit: little refacto to move it as a parameter:

diff --git a/src-tauri/src/models.rs b/src-tauri/src/models.rs
index 14920c7..dc74d15 100644
--- a/src-tauri/src/models.rs
+++ b/src-tauri/src/models.rs
@@ -95,6 +95,7 @@ pub struct ConnectionParams {
     pub username: Option<String>,
     pub password: Option<String>,
     pub database: DatabaseSelection,

+    pub ssl_mode: Option<String>,
     // SSH Tunnel
     pub ssh_enabled: Option<bool>,
     pub ssh_connection_id: Option<String>,
diff --git a/src-tauri/src/pool_manager.rs b/src-tauri/src/pool_manager.rs
index cd839eb..6c3d805 100644
--- a/src-tauri/src/pool_manager.rs
+++ b/src-tauri/src/pool_manager.rs
@@ -1,6 +1,6 @@
 use crate::models::ConnectionParams;
 use once_cell::sync::Lazy;
-use sqlx::{MySql, Pool, Postgres, Sqlite};
+use sqlx::{postgres::PgConnectOptions, MySql, Pool, Postgres, Sqlite};
 use std::collections::HashMap;
 use std::sync::Arc;
 use tokio::sync::RwLock;
@@ -45,17 +45,21 @@ fn build_mysql_url(params: &ConnectionParams) -> String {
     )
 }

-fn build_postgres_url(params: &ConnectionParams) -> String {

-    let user = encode(params.username.as_deref().unwrap_or_default());
-    let pass = encode(params.password.as_deref().unwrap_or_default());
-    format!(
-        "postgres://{}:{}@{}:{}/{}",
-        user,
-        pass,
-        params.host.as_deref().unwrap_or("localhost"),
-        params.port.unwrap_or(5432),
-        params.database
-    )
+fn build_postgres_url(params: &ConnectionParams) -> PgConnectOptions {
+    let mut options = PgConnectOptions::new()
+        .username(params.username.as_deref().unwrap_or_default())
+        .password(params.password.as_deref().unwrap_or_default())
+        .port(params.port.unwrap_or(5432))
+        .host(params.host.as_deref().unwrap_or_default())
+        .database(&format!("{}", params.database));
+
+    if let Some(ssl_mode) = params.ssl_mode.as_deref() {
+        if let Ok(mode) = ssl_mode.parse() {
+            options = options.ssl_mode(mode);
+        }
+    }
+
+    options
 }

 fn build_sqlite_url(params: &ConnectionParams) -> String {
@@ -152,7 +156,7 @@ pub async fn get_postgres_pool_with_id(
     let url = build_postgres_url(params);
     let pool = sqlx::postgres::PgPoolOptions::new()
         .max_connections(10)

-        .connect(&url)
+        .connect_with(url)
         .await
         .map_err(|e| {
             log::error!("Failed to create PostgreSQL connection pool: {}", e);

[Anonymous]

Originally posted by: debba

@sycured Thanks for this I will check in the morning

[Anonymous]

Ticket changed by: debba

• status: open --> closed

[Anonymous]

Originally posted by: debba

@sycured Thanks a lot for your effort, looks ok now :)
Hope you continue to support Tabularis, your contribution has been great!