Addressing #7890.

This allows users to specify (only) PostgreSQL connection
strings in the environment.

10 files changed, 115 insertions, 49 deletions

diff --git a/nexus/src/main/kotlin/tech/libeufin/nexus/DB.kt b/nexus/src/main/kotlin/tech/libeufin/nexus/DB.kt
index e2de7e98..c0d712fd 100644
--- a/nexus/src/main/kotlin/tech/libeufin/nexus/DB.kt
+++ b/nexus/src/main/kotlin/tech/libeufin/nexus/DB.kt
@@ -30,8 +30,6 @@ import org.jetbrains.exposed.sql.transactions.TransactionManager
 import org.jetbrains.exposed.sql.transactions.transaction
 import tech.libeufin.nexus.server.FetchLevel
 import tech.libeufin.util.*
-import java.sql.Connection
-import kotlin.reflect.typeOf
 
 /**
  * This table holds the values that exchange gave to issue a payment,
@@ -521,14 +519,14 @@ class NexusPermissionEntity(id: EntityID<Long>) : LongEntity(id) {
     var permissionName by NexusPermissionsTable.permissionName
 }
 
-fun dbDropTables(dbConnectionString: String) {
-    connectWithSchema(dbConnectionString)
+fun dbDropTables(connStringFromEnv: String) {
+    connectWithSchema(getJdbcConnectionFromPg(connStringFromEnv))
     if (isPostgres()) {
         val ret = execCommand(
             listOf(
                 "libeufin-load-sql",
                 "-d",
-                getDatabaseName(),
+                connStringFromEnv,
                 "-s",
                 "nexus",
                 "-r"
@@ -563,13 +561,13 @@ fun dbDropTables(dbConnectionString: String) {
     }
 }
 
-fun dbCreateTables(dbConnectionString: String) {
-    connectWithSchema(dbConnectionString)
+fun dbCreateTables(connStringFromEnv: String) {
+    connectWithSchema(getJdbcConnectionFromPg(connStringFromEnv))
     if (isPostgres()) {
         execCommand(listOf(
             "libeufin-load-sql",
             "-d",
-            getDatabaseName(),
+            connStringFromEnv,
             "-s",
             "nexus"
         ))
diff --git a/nexus/src/test/kotlin/MakeEnv.kt b/nexus/src/test/kotlin/MakeEnv.kt
index 9b079415..eb84e628 100644
--- a/nexus/src/test/kotlin/MakeEnv.kt
+++ b/nexus/src/test/kotlin/MakeEnv.kt
@@ -24,8 +24,7 @@ val BANK_IBAN = getIban()
 val FOO_USER_IBAN = getIban()
 val BAR_USER_IBAN = getIban()
 val TCP_POSTGRES_CONN="jdbc:postgresql://localhost:5432/libeufincheck?user=$currentUser"
-val UNIX_SOCKET_CONN= "jdbc:postgresql://localhost/libeufincheck?socketFactory=org.newsclub.net.unix." +
-        "AFUNIXSocketFactory\$FactoryArg&socketFactoryArg=/var/run/postgresql/.s.PGSQL.5432"
+val UNIX_SOCKET_CONN= "postgresql:///libeufincheck"
 val TEST_DB_CONN = UNIX_SOCKET_CONN
 
 val bankKeys = EbicsKeys(
@@ -200,7 +199,6 @@ fun prepSandboxDb(
     cashoutCurrency: String = "EUR"
 ) {
     tech.libeufin.sandbox.dbCreateTables(TEST_DB_CONN)
-    connectWithSchema(TEST_DB_CONN)
     transaction {
         val config = DemobankConfig(
             currency = currency,
diff --git a/nexus/src/test/kotlin/SandboxAccessApiTest.kt b/nexus/src/test/kotlin/SandboxAccessApiTest.kt
index 4ac26ab6..b2833890 100644
--- a/nexus/src/test/kotlin/SandboxAccessApiTest.kt
+++ b/nexus/src/test/kotlin/SandboxAccessApiTest.kt
@@ -309,7 +309,7 @@ class SandboxAccessApiTest {
                         basicAuth("foo", "foo")
                         setBody("{\"amount\": \"TESTKUDOS:99999999999\"}")
                     }
-                    assert(HttpStatusCode.Forbidden.value == r.status.value)
+                    assert(HttpStatusCode.Conflict.value == r.status.value)
                 }
             }
         }
diff --git a/nexus/src/test/kotlin/SandboxBankAccountTest.kt b/nexus/src/test/kotlin/SandboxBankAccountTest.kt
index d2e3197a..350ff3da 100644
--- a/nexus/src/test/kotlin/SandboxBankAccountTest.kt
+++ b/nexus/src/test/kotlin/SandboxBankAccountTest.kt
@@ -47,8 +47,8 @@ class SandboxBankAccountTest {
                     "TESTKUDOS:5000"
                 )
             } catch (e: SandboxError) {
-                // Future versions may wrap this case into a dedicate exception type.
-                assert(e.statusCode == HttpStatusCode.PreconditionFailed)
+                // Future versions may wrap this case into a dedicated exception type.
+                assert(e.statusCode == HttpStatusCode.Conflict)
             }
             // Trigger Insufficient funds case for the bank.
             try {
@@ -60,8 +60,8 @@ class SandboxBankAccountTest {
                     "TESTKUDOS:5000000"
                 )
             } catch (e: SandboxError) {
-                // Future versions may wrap this case into a dedicate exception type.
-                assert(e.statusCode == HttpStatusCode.PreconditionFailed)
+                // Future versions may wrap this case into a dedicated exception type.
+                assert(e.statusCode == HttpStatusCode.Conflict)
             }
             // Check balance didn't change for both parties.
             bankBalance = getBalance("admin")
diff --git a/sandbox/src/main/kotlin/tech/libeufin/sandbox/DB.kt b/sandbox/src/main/kotlin/tech/libeufin/sandbox/DB.kt
index 934dbc4f..523b1bc3 100644
--- a/sandbox/src/main/kotlin/tech/libeufin/sandbox/DB.kt
+++ b/sandbox/src/main/kotlin/tech/libeufin/sandbox/DB.kt
@@ -31,12 +31,8 @@ import org.jetbrains.exposed.dao.id.IdTable
 import org.jetbrains.exposed.dao.id.IntIdTable
 import org.jetbrains.exposed.dao.id.LongIdTable
 import org.jetbrains.exposed.sql.*
-import org.jetbrains.exposed.sql.transactions.TransactionManager
 import org.jetbrains.exposed.sql.transactions.transaction
-import org.jetbrains.exposed.sql.transactions.transactionManager
-import tech.libeufin.sandbox.CashoutSubmissionsTable.nullable
 import tech.libeufin.util.*
-import java.sql.Connection
 import kotlin.reflect.*
 import kotlin.reflect.full.*
 
@@ -666,14 +662,14 @@ class CashoutSubmissionEntity(id: EntityID<Long>) : LongEntity(id) {
     var submissionTime by CashoutSubmissionsTable.submissionTime
 }
 
-fun dbDropTables(dbConnectionString: String) {
-    connectWithSchema(dbConnectionString)
+fun dbDropTables(connStringFromEnv: String) {
+    connectWithSchema(getJdbcConnectionFromPg(connStringFromEnv))
     if (isPostgres()) {
         val ret = execCommand(
             listOf(
                 "libeufin-load-sql",
                 "-d",
-                getDatabaseName(),
+                connStringFromEnv,
                 "-s",
                 "sandbox",
                 "-r" // the drop option
@@ -713,13 +709,13 @@ fun dbDropTables(dbConnectionString: String) {
 
 }
 
-fun dbCreateTables(dbConnectionString: String) {
-    connectWithSchema(dbConnectionString)
+fun dbCreateTables(connStringFromEnv: String) {
+    connectWithSchema(getJdbcConnectionFromPg(connStringFromEnv))
     if (isPostgres()) {
         execCommand(listOf(
             "libeufin-load-sql",
             "-d",
-            getDatabaseName(),
+            connStringFromEnv,
             "-s",
             "sandbox"
         ))
diff --git a/sandbox/src/main/kotlin/tech/libeufin/sandbox/Main.kt b/sandbox/src/main/kotlin/tech/libeufin/sandbox/Main.kt
index 42a2a515..bfd521cc 100644
--- a/sandbox/src/main/kotlin/tech/libeufin/sandbox/Main.kt
+++ b/sandbox/src/main/kotlin/tech/libeufin/sandbox/Main.kt
@@ -231,7 +231,7 @@ class Camt053Tick : CliktCommand(
 ) {
     override fun run() {
         val dbConnString = getDbConnFromEnv(SANDBOX_DB_ENV_VAR_NAME)
-        dbCreateTables(dbConnString)
+        execThrowableOrTerminate { dbCreateTables(dbConnString) }
         val newStatements = mutableMapOf<String, MutableList<XLibeufinBankTransaction>>()
         /**
          * For each bank account, extract the latest statement and
@@ -293,13 +293,15 @@ class MakeTransaction : CliktCommand("Wire-transfer money between Sandbox bank a
     private val subjectArg by argument("SUBJECT", "Payment's subject")
 
     override fun run() {
-        val dbConnString = getDbConnFromEnv(SANDBOX_DB_ENV_VAR_NAME)
         /**
          * Merely connecting here (and NOT creating any table) because this
          * command should only be run after actual bank accounts exist in the
          * system, meaning therefore that the database got already set up.
          */
-        connectWithSchema(dbConnString)
+        execThrowableOrTerminate {
+            val pgConnString = getDbConnFromEnv(SANDBOX_DB_ENV_VAR_NAME)
+            connectWithSchema(getJdbcConnectionFromPg(pgConnString))
+        }
         // Refuse to operate without a default demobank.
         val demobank = getDemobank("default")
         if (demobank == null) {
diff --git a/sandbox/src/test/kotlin/DBTest.kt b/sandbox/src/test/kotlin/DBTest.kt
index 519e3bf7..21e47415 100644
--- a/sandbox/src/test/kotlin/DBTest.kt
+++ b/sandbox/src/test/kotlin/DBTest.kt
@@ -21,34 +21,23 @@ import org.jetbrains.exposed.sql.*
 import org.jetbrains.exposed.sql.transactions.transaction
 import org.junit.Test
 import tech.libeufin.sandbox.*
+import tech.libeufin.util.connectWithSchema
 import tech.libeufin.util.getCurrentUser
+import tech.libeufin.util.getJdbcConnectionFromPg
 import tech.libeufin.util.millis
 import java.io.File
 import java.time.LocalDateTime
 import kotlin.reflect.KProperty
+import kotlin.reflect.typeOf
 
 /**
  * Run a block after connecting to the test database.
  * Cleans up the DB file afterwards.
  */
 fun withTestDatabase(f: () -> Unit) {
-    val dbFile = "/tmp/sandbox-test.sqlite3"
-    val dbConn = "jdbc:sqlite:${dbFile}"
-    File(dbFile).also {
-        if (it.exists()) {
-            it.delete()
-        }
-    }
-    Database.connect(dbConn, user = getCurrentUser())
-    dbDropTables(dbConn)
-    dbCreateTables(dbConn)
-    try { f() }
-    finally {
-        File(dbFile).also {
-            if (it.exists())
-                it.delete()
-        }
-    }
+    dbDropTables("postgresql:///libeufincheck")
+    dbCreateTables("postgresql:///libeufincheck")
+    f()
 }
 
 class DBTest {
@@ -62,6 +51,26 @@ class DBTest {
     )
 
     /**
+     * This tests the conversion from a Postgres connection
+     * string to a JDBC one.
+     */
+    @Test
+    fun connectionStringTest() {
+        var conv = getJdbcConnectionFromPg("postgresql:///libeufincheck")
+        connectWithSchema(conv)
+        conv = getJdbcConnectionFromPg("postgresql://localhost:5432/libeufincheck?user=${System.getProperty("user.name")}")
+        connectWithSchema(conv)
+        conv = getJdbcConnectionFromPg("postgresql:///libeufincheck?host=/tmp/libeufin")
+        var exception: Exception? = null
+        try {
+            connectWithSchema(conv)
+        } catch (e: Exception) {
+            exception = e
+        }
+        assert(exception is UtilError)
+    }
+
+    /**
      * Storing configuration values into the database,
      * then extract them and check that they equal the
      * configuration model object.
diff --git a/util/src/main/kotlin/Config.kt b/util/src/main/kotlin/Config.kt
index b3c57ea2..c7958a75 100644
--- a/util/src/main/kotlin/Config.kt
+++ b/util/src/main/kotlin/Config.kt
@@ -64,6 +64,10 @@ fun getValueFromEnv(varName: String): String? {
     return ret
 }
 
+/**
+ * Gets the connection string in Postgres format and
+ * returns the JDBC version of it.
+ */
 fun getDbConnFromEnv(varName: String): String {
     val dbConnStr = System.getenv(varName)
     if (dbConnStr.isNullOrBlank() or dbConnStr.isNullOrEmpty()) {
diff --git a/util/src/main/kotlin/DB.kt b/util/src/main/kotlin/DB.kt
index b5147fa6..169a1d40 100644
--- a/util/src/main/kotlin/DB.kt
+++ b/util/src/main/kotlin/DB.kt
@@ -25,9 +25,12 @@ import logger
 import net.taler.wallet.crypto.Base32Crockford
 import org.jetbrains.exposed.sql.Database
 import org.jetbrains.exposed.sql.Transaction
+import org.jetbrains.exposed.sql.name
 import org.jetbrains.exposed.sql.transactions.TransactionManager
 import org.jetbrains.exposed.sql.transactions.transaction
 import org.postgresql.jdbc.PgConnection
+import java.net.URI
+import kotlin.system.exitProcess
 
 fun Transaction.isPostgres(): Boolean {
     return this.db.vendor == "postgresql"
@@ -237,13 +240,60 @@ fun getDatabaseName(): String {
  * to a database and ONLY use the passed schema
  * WHEN PostgreSQL is the DBMS.
  */
-fun connectWithSchema(dbConn: String, schemaName: String? = null) {
+fun connectWithSchema(jdbcConn: String, schemaName: String? = null) {
     Database.connect(
-        dbConn,
-        user = getCurrentUser(),
+        jdbcConn,
         setupConnection = { conn ->
             if (isPostgres() && schemaName != null)
                 conn.schema = schemaName
         }
     )
+    try { transaction { this.db.name } }
+    catch (e: Throwable) {
+        logger.error("Test query failed: ${e.message}")
+        throw internalServerError("Failed connection to: $jdbcConn")
+    }
+}
+
+/**
+ * This function converts a postgresql://-URI to a JDBC one.
+ * It is only needed because JDBC strings based on Unix domain
+ * sockets need individual intervention.
+ */
+fun getJdbcConnectionFromPg(pgConn: String): String {
+    if (!pgConn.startsWith("postgresql://")) {
+        logger.info("Not a Postgres connection string: $pgConn")
+        throw internalServerError("Not a Postgres connection string: $pgConn")
+    }
+    var maybeUnixSocket = false
+    val parsed = URI(pgConn)
+    val hostAsParam: String? = if (parsed.query != null)
+        getQueryParam(parsed.query, "host")
+    else null
+    /**
+     * In some cases, it is possible to leave the hostname empty
+     * and specify it via a query param, therefore a "postgresql:///"-starting
+     * connection string does NOT always mean Unix domain socket.
+     * https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING
+     */
+    if (parsed.host == null &&
+        (hostAsParam == null || hostAsParam.startsWith('/'))) {
+        maybeUnixSocket = true
+    }
+    if (maybeUnixSocket) {
+        // Check whether the database user should differ from the process user.
+        var pgUser = getCurrentUser()
+        if (parsed.query != null) {
+            val maybeUserParam = getQueryParam(parsed.query, "user")
+            if (maybeUserParam != null) pgUser = maybeUserParam
+        }
+        // Check whether the Unix domain socket location was given non-standard.
+        val socketLocation = hostAsParam ?: "/var/run/postgresql/.s.PGSQL.5432"
+        if (!socketLocation.startsWith('/')) {
+            throw internalServerError("PG connection wants Unix domain socket, but non-null host doesn't start with slash")
+        }
+        return "jdbc:postgresql://localhost${parsed.path}?user=$pgUser&socketFactory=org.newsclub.net.unix." +
+                "AFUNIXSocketFactory\$FactoryArg&socketFactoryArg=$socketLocation"
+    }
+    return "jdbc:$pgConn"
 }
\ No newline at end of file
diff --git a/util/src/main/kotlin/strings.kt b/util/src/main/kotlin/strings.kt
index dce25861..563afe34 100644
--- a/util/src/main/kotlin/strings.kt
+++ b/util/src/main/kotlin/strings.kt
@@ -203,3 +203,12 @@ fun extractReservePubFromSubject(rawSubject: String): String? {
     val result = re.find(rawSubject.replace("[\n]+".toRegex(), "")) ?: return null
     return result.value.uppercase()
 }
+
+fun getQueryParam(uriQueryString: String, param: String): String? {
+    uriQueryString.split('&').forEach {
+        val kv = it.split('=')
+        if (kv[0] == param)
+            return kv[1]
+    }
+    return null
+}