DB connection error from linux server

[StefanMunnes]

Hello again,
since I have the opportunity to run shiny apps and therefore also surveydown from my institute's server. Testing on my local machine was fine, after dealing with the table problem.

Now I try to transfer and implement the tested survey on the remote linux server and connect it with the supabase database. From the linux server I'm forced to disable the gssencmode with gssencmode='disable'. But even if I stored this in .env config file, I receive the following error:

invalid connection option "gssencmode"

I also receive this error if I only test this on the server with the implemented pool-function:

pool <- pool::dbPool(
  RPostgres::Postgres(),
  host = "...",
  dbname = "postgres",
  port = 6543,
  user = "...",
  password = ".",
  gssencmode = "disable"
)

Any idea what is going on?

[jhelvy]

It's saying that gssencmode is an invalid connection option, which is odd since we've used it in the past and it has worked. The main reason we have this is because sometimes we need to set it to "disable" if we're on a protected network, like a VPN, that won't let us connect otherwise.

I wonder if it's a difference in how Linux performs connection tasks. Unfortunately, the way we wrote the source code is such that this is hard-coded into the connection. Simply removing it may work, but right now the only way to do that is to change the source code. Could you try that? Just remove this line and build the package locally then see if it works. If it does, then we'll need to revise how we pass the gssencmode argument to make it optional to remove.

I also just chatted briefly with Claude on this and it says:

The sslmode parameter is used in R to control SSL connectivity to PostgreSQL, while gssencmode is typically used in other contexts like the PostgreSQL command line tools. The valid values for sslmode are:

"disable"
"allow"
"prefer"
"require"
"verify-ca"
"verify-full"

I'm not a database expert, so I'm not sure about this. I also just googled around on this and I'm not seeing much on either gssencmode or sslmode for pooled connections.

[jhelvy]

Here's a revised version of sd_db_connect() that includes gssencmode as an argument. It is set to "prefer" by default, but if you want to completely remove it from being used, set it to NULL, like this:

db <- sd_db_connect(gssencmode = NULL)

I'm not sure if that will work, but if it does then we might make this update to give users that option.

sd_db_connect <- function(env_file = ".env", ignore = FALSE, gssencmode = "prefer") {
    if (ignore) {
        cli::cli_alert_info("Database connection ignored. Saving data to local CSV file.")
        return(NULL)
    }

    # Load environment variables
    if (!file.exists(env_file)) {
        cli::cli_alert_warning("No .env file found.")
        cli::cli_alert_info("Run the following to configure your database:")
        cli::cli_code("sd_db_config()")
        return(NULL)
    }

    dotenv::load_dot_env(env_file)

    # Get all required parameters
    params <- list(
        host = Sys.getenv("SD_HOST"),
        port = Sys.getenv("SD_PORT"),
        dbname = Sys.getenv("SD_DBNAME"),
        user = Sys.getenv("SD_USER"),
        password = Sys.getenv("SD_PASSWORD"),
        table = Sys.getenv("SD_TABLE")
    )

    # Check for missing required parameters
    missing <- names(params)[!nchar(unlist(params))]
    if (length(missing) > 0) {
        cli::cli_alert_warning("Missing required database configuration:")
        cli::cli_bullets(paste0("* ", missing))
        return(NULL)
    }

    # Create connection
    tryCatch({
        # Build connection arguments
        conn_args <- list(
            drv = RPostgres::Postgres(),
            host = params$host,
            dbname = params$dbname,
            port = params$port,
            user = params$user,
            password = params$password
        )

        # Add gssencmode unless it's explicitly set to NULL
        if (!is.null(gssencmode)) {
            conn_args$gssencmode <- gssencmode
        }

        # Create pool with dynamic arguments
        pool <- do.call(pool::dbPool, conn_args)

        cli::cli_alert_success("Successfully connected to the database.")
        return(list(db = pool, table = params$table))
    }, error = function(e) {
        cli::cli_alert_warning("Failed to connect to the database:")
        cli::cli_text(conditionMessage(e))
        cli::cli_text("")
        return(NULL)
    })
}

[StefanMunnes]

I tested both, and now it runs and stores the results without problems in the supabase DB.

Thank you again for your great help!

[jhelvy]

Wait, so removing the gssencmode argument completely solved it? If that's the case then I'll update sd_db_connect() with the one I posted here so that users can set it to NULL because right now it's still a hard-coded argument.

[StefanMunnes]

Yes. I tried both. Deleting this line as you suggested worked also like implementing the updated sd_db_connect () function with setting the argument: gssencmode=NULL.

[jhelvy]

I'm re-opening this just to make sure we get it updated before closing.
@pingfan-hu can you add this to our list? Should be to make the gssencmode argument optional in sd_db_connect().

[pingfan-hu]

Just read through your conversation. Yes it's now added to the list.

[StefanMunnes]

Because I run into this problem again after copy my changed files to the linux server, I was wondering, if the gssencmode couldn't be set automatic to NULL in the function if this is the reason for not connecting to the database?

[jhelvy]

Perhaps, I'll try to look more closely into what this is truly doing. My understanding was that setting it to "prefer" was more secure, and I'd like to set defaults to be more secure than less, but I might be misinformed on that. Setting it to NULL would allow users to avoid having these issues, and it could even be removed from the .env file and just be kept as an optional argument to sd_db_connect(). I'll look into it.

[StefanMunnes]

Yeah, I'm also a fan of more secure connections. I suggested, that if an error with the connection comes up, addressing the invalid gssencmode, than try to reconnect and ignore the gssencmode.

[jhelvy]

I think what I might do is make gssencmode a parameter in sd_db_connect() rather than a parameter in the .env file. That file should store your connection parameters, and the gssencmode is not really a required parameter but rather an optional one for security purposes. The default could still be gssencmode = 'prefer', but this way the user could easily set it to NULL in the function call without having to modify their .env settings.