no encryption error, but equivalent python code works

[nick-youngblut]

Works:

import psycopg2
from psycopg2.extensions import connection

def db_connect() -> connection:
    db_params = {
        'host': os.environ["DB_HOST"],
        'database': os.environ["DB_NAME"],
        'user': os.environ["DB_USERNAME"],
        'password': os.environ["DB_PASSWORD"]
        'port': '5432'
    }
    return psycopg2.connect(**db_params)

The equivalent R code does not work:

db_connect = function(){
  pool::dbPool(
    RPostgres::Postgres(),
    host = config::get("DB_HOST"),
    dbname = config::get("DB_NAME"),
    user = config::get("DB_USERNAME"),
    password = Sys.getenv("DB_PASSWORD"),
    port = 5432,
    sslmode = "disable"
  )
}

The error:

Error: connection to server at "***", port 5432 failed: FATAL:  pg_hba.conf rejects connection for host "***", user "***", database "***", no encryption

Changing sslmode to any of the other possible settings does not help.

I'm using a GCP Postgresql database.

[nick-youngblut]

Note: providing sslcert and sslkey results in the same error.

I can only connect with RPostgres::Postgres() if I use cloud-sql-proxy.

[krlmlr]

Thanks. Is there any way you could share temporary credentials with me, or perhaps teach me to set up such a database service?

[nick-youngblut]

Sorry , but I can't provide the temporary credentials.

I ended up using sslmode = "verify-ca"

[krlmlr]

Thanks. Did you connect successfully with RPostgres? Did you need to add sslkey and sslcert as well? Would you like to post the code that worked for you, for other users?

[nick-youngblut]

I was able to successfully connect with RPostgres. I did add sslkey and sslcert.

My example code:

make_db_conn = function(){
  pool::dbPool(
    RPostgres::Postgres(),
    host = config::get("GCP_SQL_DB_HOST"),
    dbname = config::get("GCP_SQL_DB_NAME"),
    user = config::get("GCP_SQL_DB_USERNAME"),
    password = Sys.getenv("GCP_SQL_DB_PASSWORD"),
    sslmode = "verify-ca",
    sslcert = PATH_TO_CLIENT_CERT,
    sslkey = PATH_TO_CLIENT_KEY,
    sslrootcert = PATH_TO_SERVER_CA,
    port = 5432
  )
}

Note: the cert files must have 0600 permissions.