Hi all, not sure whether this channel or the ktor channel might be better suited for this question, but I'll give a try here first. We have a service that handles data for multiple customers. We want to improve data isolation by creating different database users for each of our customers. We must therefore tell Exposed to dynamically use different credentials when it retrieves a database connection. One of the provided Database.connect functions should allow us to do exactly that:

fun connect(getNewConnection: () -> Connection, ...)

We use ktor as our http backend and now the only problem is how we can get the auth information from ktor into this lambda. We are able to store the required info in the pipeline context of ktor. So what we specifically try right now is to get PipelineContext data into the getNewConnection lambda:

Database.connect(getNewConnection = {
    val jdbcUrl = "jdbc:${DbConfig.jdbcProtocol}://${DbConfig.hostname}:${DbConfig.port}/${DbConfig.database}"
    val user = "" // TODO how to get the data from ktor pipeline context into this lambda here?
    val password = ""  // TODO how to get the data from ktor pipeline context into this lambda here?
    DriverManager.getConnection(jdbcUrl, user, password)
})

Has somebody tried such a thing already? We need to somehow make Exposed aware of the ktor pipeline context, so we can set the credentials for the database based on the rest call at hand. Any tips or suggestions at all would be very much appreciated.

First of all it looks thatSo you will need to create DB connection for each request, or cache it somehow But This probably is not effective.

Yes. One thing that confused me here is the other statement on the GitHub Wiki:

val db = Database.connect(dataSource)

Note: Starting Exposed 0.10 executing this code more than once per db will create leaks in your application, hence it is recommended to store it for later use. Ideally, I would not like to configure all the passwords for all our customers into our application. The idea is that the api key for the rest interface coincides with the database password for that customer. Thus, even if the caller is due to some weird error resolved to the wrong customer, it would still not leak data since the database user password would need to match with the api call as well. But that would require that we call Database.connect over and over again. And as per the statement above, that would lead to problems 😞

To be honest I also had (and still have same requirement). I even tried approach with Application users ( not db user row level security) https://www.2ndquadrant.com/en/blog/application-users-vs-row-level-security/ But still it looks that this is not possible with ktor and exposed

Yes, we are also trying it for the RLS. With the session variable, the issue remains that you would need to set the variable based on the ktor pipeline somewhere. And the separate db users have the advantage that they can be secured with a password We will be trying out the following custom newSuspendedTransaction variant now, and see if and where things leak when we call Database.connect over and over again on each http request:

suspend fun <T> PipelineContext<Unit, ApplicationCall>.newSuspendedTransaction(
    statement: suspend Transaction.() -> T
) {
    val user = this.context.attributes[tenantUserContextKey]
    val password = ...
    val database = Database.connect(user, password)
    org.jetbrains.exposed.sql.transactions.experimental.newSuspendedTransaction(db = database, statement = statement)
}