Hi all, not sure whether this channel or the ktor ...

# exposed

[Alexander Weickmann]

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.

[Alexander Weickmann]

As far as I can see from the Exposed code, the only way would be to have a newSuspendedTransaction variant that takes the current user and password as arguments. It already allows to pass a Database object, but this is optional and if it is forgotten to pass it explicitly then the latest connected would be used, which would be fatal in our case 😞

[Kamil Kalisz]

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.

[Kamil Kalisz]

But when you retrieve db connection you can use DB object.
https://github.com/JetBrains/Exposed/wiki/Transactions#working-with-a-multiple-databases

[Alexander Weickmann]

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 😞

[Kamil Kalisz]

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

[Alexander Weickmann]

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)
}

[Alexander Weickmann]

I have doubts that it will work properly because the Database.connect does some ThreadLocal stuff and when multiple requests are in the works these might be on the same thread and mess that up

[Alexander Weickmann]

At that point, I see no other way of either forking and rewriting the Exposed such that I can get connections without having to go through this Database object, or the other option would be to migrate the entire project to another DB library. Although that will bring it's own problems with it

[Alexander Weickmann]

But to summarize that would be a feature request for exposed: Support Row Level Security
Although you can do the RLS with the Exposed so that's not really the issue. Problems arise due to the coroutine approach we have in Kotlin and this not mixing well with this static Database object we have to work with here in Exposed

[Alexander Weickmann]

It sort of assumes that we create all the connection configs we need beforehand. But for the RLS, we need something more flexible. Creating all db configs for all customers beforehand means you have to implement a lookup and you might again program an error into this lookup. but the whole point of the RLS would be to act as additional defensive mechanism against data leakage due to programming mistakes