Support SQL Backend as an Authentication Provider

[phaus]

Feature Request

I would like to use authelia for a new customer project.
Since the customer does not have any user directory, the LDAP Authentication Provider can't be used.
The file based Authentication Provider is also not a good option, since I don't feel very comfortable to use a file to store the user data of several hundred users.

Description

I had a look into the UserProvider Interface and I think, creating an implementation that is backed by e.g. mariaDB or Postgres shouldn't be that complicated.

I already found #2784 and #1538.
I looks like a new approach for the implementation of different Authentication Providers would be a good thing to do.
However, the issue regarding the new design seems to be dormant for so time now.
So until it progresses again, I think I will start with a implementation of a SQL Backend and will open a PR here.
If I have some more time, I can also suggest some setup for a plugin-based implementation, but for that one need to decide, if it should be a compile time plugin or a runtime plugin system.

Use Case

• Using a database to store the Authentication Provider data.
• implement the functions of the UserProvider Interface

[james-d-elliott]

There has not been any movement since the product is security focused and we've not had the time to prioritize this feature in order to finish the design which is considered required and essential. It will likely require an agreed design before implementation.

We have had a chance to discuss it as a team recently as well as plugins, however we're probably going need to discuss users in the existing SQL backend again.

Additionally since my posting on 2784 we have identified an issue with implementing a plugin system specifically for user auth. The drawback is if we ever have to implement a change to the interface, and have an unrelated security vulnerability, anyone using a plugin may not be able to update if the plugin author has not upgraded or is not able to upgrade their implementation. This could potentially put the user using this plugin at an increased security risk that they cannot resolve without starting again.

Side Note: not entirely sure why this was created as a new issue especially since you already identified an issue this should be part of?

[phaus]

Additionally since my posting on 2784 we have identified an issue with implementing a plugin system specifically for user auth.

If you can elaborate on this, this might help me to understand the scope of this.

In regards of user auth and plugins, it might be worth to have a look at hashicorp Vault, since they are using their own gRPC Plugin System for that exact purpose: https://github.com/hashicorp/go-plugin

Side Note: not entirely sure why this was created as a new issue especially since you already identified an issue this should be part of?

As I see it:

• Add MongoDB as authentication backend #2784 is about supporting a key-value DB as a backend
• [WIP] Design: Storage / Internal Authentication Provider #1538 is a somewhat broader design that also covers a common API to access the user resources.

So this specific feature request differs from both issues.
I identifyed three different parts that might need a design and a matching implementation afterwards:

• an internal API (as of now, I would use the Authentication Provider Interface as a short-term working solution
• a middleware component, that encapsulates all DB related things (Setup/Migrating/Data Access). For that one, I planed to have a look at the currently used Database functionality that was already implemented for other purposes as an orientation.
• configuration a Database config might need some more elements to support different requirements (e.g. besides the access config, there might be a need for data mapping and also the support for different password hashing algorithms)

I am very open to wait for a new Design that I can then implement to make this contribution worth the effort, but I wasn't able to get any timeframe for when an initial design concept is done.

[james-d-elliott]

If you can elaborate on this, this might help me to understand the scope of this.

I'm not quite sure how much more I can elaborate on it. If we allow authentication plugins we either cannot change the interface at any point, or we risk putting users who utilize an authentication plugin at risk of not being able to update Authelia without losing their user database. This means that if a CVE is discovered, they could potentially be running compromised systems. Whereas in the current implementation since the interface is not public we can make changes without integrations breaking.

For things that are not Authentication related this has minimal risk, but for auth this potentially leaves users in a very precarious situation.

So this specific feature request differs from both issues.

I suppose I can understand your point. I would personally see it relating to #1538 though just because it's about the design of the SQL Auth Provider. Though it has a long way to go, the intention was to support CLI based user/group management at first.

• an internal API (as of now, I would use the Authentication Provider Interface as a short-term working solution

Yeah all authentication providers will need to implement it. We'd need a compelling reason to diverge functionality in any single implementation.

• a middleware component, that encapsulates all DB related things (Setup/Migrating/Data Access). For that one, I planed to have a look at the currently used Database functionality that was already implemented for other purposes as an orientation.

We have migrations and support for PostgreSQL, MySQL/MariaDB/SQlite.

• configuration a Database config might need some more elements to support different requirements (e.g. besides the access config, there might be a need for data mapping and also the support for different password hashing algorithms)

We use sqlx for data mapping, and we support sha512 and argon2id, both of which are strong.

I am very open to wait for a new Design that I can then implement to make this contribution worth the effort, but I wasn't able to get any timeframe for when an initial design concept is done.

I can try to get you a timeframe. The SQL Auth implementation did come up at our last team catchup and the priority of it. But only two of us made the catchup so we put off making a decision on a priority.

[Jexs]

I'm a long time lurker of the Authelia project and have wanted to jump into using it multiple times over the last year, give or take. The only thing stopping me is the lack of support for this feature. I have discussed it with many friends and they have all mentioned the same thing. In my opinion, it is the biggest short coming of this project.

We need something that can be setup to have a higher redundancy and availability than a text file, but our use case is not big enough or important enough to warrant having an LDAP server setup.

The reason for my reply here is simply to just express an immense amount of interest in this feature. Every couple months I check in on #1538 and it has been quite stagnant. You mentioned making a decision on priority, and I for one am hoping for it to make it towards the front of the list!

This issue aside, thank you to all of the contributors for your continued work. I can't wait to see what the future holds.

[james-d-elliott]

Could you clarify on what the majority of your conversations were as to the specific admin experience would look like for initial adoption? Is CLI sufficient or would there need to be a frontnend, or is the frontend just a nice to have?

[Jexs]

Could you clarify on what the majority of your conversations were as to the specific admin experience would look like for initial adoption? Is CLI sufficient or would there need to be a frontnend, or is the frontend just a nice to have?

I think CLI would be a great usable start if its fully featured as described in the WIP design. I'm sure it wouldn't fit everyone's use case if they specifically need to be able to manage users via an API. However, for my limited use case on side projects I think that would be very doable. Frontend would just be a nice to have eventually.

[Entropy-9973]

This is by a very wide margin the feature that I miss the most in the project.
Please add sqlite and postgres support

The problem is that I'm using authelia for only one small project at work and at my home lab and this does not justify developing and mainitning the feature.

This will make integrating authelia with other services muuuuuuuch easier

[james-d-elliott]

Please add sqlite and postgres support

We already support postgres and sqlite.

This will make integrating authelia with other services muuuuuuuch easier

Could you elaborate on this? How would it assist with that?

[Entropy-9973]

We already support postgres and sqlite.
Not as an authentication provider

Could you elaborate on this? How would it assist with that?
Yes, I have multiple webapps that are using sqlite and postgres to store users credentials, if you add the support, I will have a single source of truth in the database and I won't have to change the credentials in more than one place. I'm using authelia with nginx to restrict access to some services. Right now, I'm using a mix of postgres and LDAP which works but unecessirly makes the stack more complicated.
I don't know if I made this clear enough but I have a big dashboard that users use to access multiple webapps, users have different priviliges in the dashboard and also in each of the webapps. I imagine that there are many other scenarios where adding a database and an API like the one you proposed two years ago will help greatly.

Thanks for your work btw, this is a great project.

[james-d-elliott]

That's exactly why I asked. It seems there is some lack of clarity around how we plan to implement this. If we were to implement this the intention was to add the tables to our existing databases, and the users would be managed by Authelia in some way (probably CLI to begin with, with an API to follow when we implement necessary things to adjust accounts).

That doesn't exclude other applications from utilizing it, however I suspect we'd have to think long and hard before supporting third party databases, there are so many additional maintenance concerns around such an implementation.

[Entropy-9973]

That's exactly why I asked. It seems there is some lack of clarity around how we plan to implement this. If we were to implement this the intention was to add the tables to our existing databases via new tables, and the users would be managed by Authelia in some way.

That doesn't exclude other applications from utilizing it, however I suspect we'd have to think long and hard before supporting third party databases, there are so many additional maintenance concerns around such an implementation.

Even if you implement it the way you guys intend go, it will still be great. I can then take away the LDAP server. I don't feel comfortable modifying a simple file that cannot guarantee integreity hence why I went for the LDAP alternative. Even if you add your own tables, the abaility to add/delete users, fetch/change passwords would cover over 80% of the use-cases I imagine.
It will allow a single Login for many independent apps, I can restructure my apps databases to follow the Authelica schema.

I want to say this in the most friendly and respectful way haha, but are you still planning to implement that ticket from 2020 ? (I know how lame it is to urge open source contributors, I don't want to imply that I'm urging you in any way)

[james-d-elliott]

I want to say this in the most friendly and respectful way haha, but are you still planning to implement that ticket from 2020 ? (I know how lame it is to urge open source contributors, I don't want to imply that I'm urging you in any way)

Which one specifically? The one where I was working on a design for this specific feature?

[Entropy-9973]

This 1538
If that API is developed, that should also solve the problem

[james-d-elliott]

Most likely in time. It will be a natural progression as we add user interfaces to control said database. We will have to think about how we give access to that for third parties is all. The likely thing at the start is implementing the CLI. The API will come in time.

[clems4ever]

Hello, we understand that this feature is expected by some of you and we would like to collect information about the actual reasons why it's needed in order to find the best solution. We have created a form and we would be very grateful if you can give your feedback. It is available here and it is anonymous. Thank you in advance.

[adrw]

Hi, are there any updates on the team's thoughts on adding a SQL authentication provider backend? Thanks for collecting feedback with the above form and considering community feedback.

Authelia has been a great and critical piece of our infrastructure though I do worry as we scale about having to manage an LDAP server or how the existing File provider will manage. Some guidance on whether a SQL backend is coming would be helpful in our planning if it's possible for you to share. Many thanks.

Alternatively, an authentication provider which does web requests out to a running application which manages the user table could work well too similar to how Stripe Connect outlines the necessary webhook endpoints your application needs for them to hook into your existing application.

[james-d-elliott]

I think we can revisit this idea soon, and I can probably come up with a POC to see if the other maintainers like it. I don't think external databases will be part of our design consideration at all (and instead we'd be looking at technology like SCIM 2.0 to handle this and leverage the internal storage which we've had in mind for a number of years), it will if anything be the existing storage database which will have additional tables where the password is hashed with a salt and pepper (in the form of the existing encryption), and it will be managed via a CLI at least in the short term if anything.

[terefang]

@phaus you might want to take a look at lldap - https://github.com/lldap/lldap

it already supports authelia