Improve authentication without the need for backends

[dngferreira]

Hello,
The feature request is to allow the communication to an electric-sql service without asking a backend server to sign the JWT.
The idea is to enable a way to assign a public key to a user_id.

I think this is useful primarily for mobile apps where the backend mainly serves as an access to a central database and, if there was a secure way to connect directly to a Database, the business logic could be on the app side.
I can see electric-sql filling this gap.

The flow would be the following:
The app would be published with a private key that can sign JWT that can only be used to create other users and assign public keys. When a user creates an account on the app, the app creates on the device a key pair and uses the key packed with the mobile app,to sign the JWT and create the user on the database. This user gets propagated to the central postgresql through electric-sql and the public key, generated on the mobile device is stored, linked to the user.
The electric-sql service generates a replacement token that can be used to reset the public key, and this token is sent to the mobile device.

Now the communication can proceed with the user using his local generated private key to sign the JWT to access electric-sql.

Since the JWT is linked to the user_id it should play nicely with the Shapes and authorisation mechanisms on the roadmap.

A problem that was identified on this solution is the sharing of accounts between devices. My idea is to let it on the app side. The app could generate a QRcode with the public key and the app on another mobile read and import the key to the app.
Remember the private key is never known to electric-sql, it's the responsibility of the apps developers to keep the private key safe.

Another problem is the recovery of the account if we loose the private key. This is where the recovery token come in, it acts as a "private question" to recover the account(basically replace the public key), it's the apps developer responsibility to show the token to the user (still need to elaborate a bit more).

And the final problem it was pointed was, account creation and impersonation, basically how to double check the user is legit and we don't have a malicious agent creating thousands of fake user_ids.

This is a long post but I hope it can help to describe the feature, the use case and can help start the discussion to identify if this is a useful feature and improve on the proposal.

[alco]

Discord thread with additional context - https://discord.com/channels/933657521581858818/1157745074214535218.

[KyleAMathews]

👋 we've been working the last month on a rebuild of the Electric server over at a temporary repo https://github.com/electric-sql/electric-next/

You can read more about why we made the decision at https://next.electric-sql.com/about

We're really excited about all the new possibilities the new server brings and we hope you'll check it out soon and give us your feedback.

We're now moving the temporary repo back here. As part of that migration we're closing all the old issues, PRs, and discussions. We really appreciate you taking the time to start this discussion! If it's still relevant in the new system, please reopen.