[FEATURE REQUEST] Add Support for CockroachDB

[m-yosefpor]

Description:

We need CockroachDB support in LLDAP to take advantage of CockroachDB's superior scalability and fault tolerance, ensuring high availability and efficient data distribution for our user authentication system. This integration will significantly enhance our infrastructure's resilience and performance.

I encountered issues when attempting to use CockroachDB with LLDAP. The initial error message indicated a missing column during schema upgrade:

Execution Error: error returned from database: column "display_name" does not exist

I attempted a workaround by starting the lldap initially with PostgreSQL to finish its migrations, and then dump the db and import into CockroachDB. After the migration, I encountered another error:

error occurred while decoding column "A_group_id": mismatched types; Rust type `core::option::Option<i32>` (as SQL type `INT4`) is not compatible with SQL type `INT8`

These issues suggest compatibility problems with CockroachDB. Support for CockroachDB would be highly beneficial, as it is a scalable and resilient database option.

Environment:

• LLDAP version: v0.5.0
• CockroachDB version: v24.1.1

[nitnelave]

Hi! I'm unconvinced by the use case. Have you actually reached the scalability limits of PostgreSQL? Do you have a workflow that is so heavy against authentication that you need a distributed database, and a cache upstream of LLDAP would not work? If that's the case, then I seriously doubt that you want to use LLDAP: you would be a seriously growing company with millions or billions of users, whereas LLDAP is a hobby project for self-hosting small servers.

Or is your concern about reliability? PG is famously reliable, and with backups you can guard against machine failures.

Or is your service so critical that a 1 minute downtime to upgrade the LLDAP version is not acceptable? Then I'm afraid that the problem is not the database, but LLDAP itself. And again, what are you doing using LLDAP in such a critical setting?

[m-yosefpor]

Hi! I'm unconvinced by the use case. Have you actually reached the scalability limits of PostgreSQL? Do you have a workflow that is so heavy against authentication that you need a distributed database, and a cache upstream of LLDAP would not work? If that's the case, then I seriously doubt that you want to use LLDAP: you would be a seriously growing company with millions or billions of users, whereas LLDAP is a hobby project for self-hosting small servers.

Or is your concern about reliability? PG is famously reliable, and with backups you can guard against machine failures.

Or is your service so critical that a 1 minute downtime to upgrade the LLDAP version is not acceptable? Then I'm afraid that the problem is not the database, but LLDAP itself. And again, what are you doing using LLDAP in such a critical setting?

Yes, you are right. Our primary concern is indeed reliability, especially multi-region availability. While PostgreSQL is highly reliable and effective for many use cases, achieving multi-region availability with PostgreSQL involves additional complexity and maintenance. Typically, this setup would require PostgreSQL combined with HAProxy and Patroni for high availability, and Patroni itself needs another database like ETCD to ensure HA with the Raft protocol.

In contrast, CockroachDB inherently supports the Raft consensus algorithm and provides a straightforward solution for a highly available database cluster across multiple regions. This built-in support simplifies our architecture by removing the need for additional layers and components, leading to easier maintenance and potentially higher reliability. Given these considerations, integrating CockroachDB with LLDAP would significantly enhance our infrastructure’s resilience, particularly in a multi-region context.

Additionally, we are using LLDAP together with solutions such as Zitadel and Casdoor, for both of which we are currently utilizing CockroachDB. By integrating CockroachDB with LLDAP, we can reuse our existing database infrastructure for LLDAP as well, enhancing consistency and efficiency across our systems.

[nitnelave]

Okay, that makes more sense. I'm still not sure that multi-region availability is a must, but I'm not the one to dictate what your company does.

I'll just reiterate that LLDAP is mainly targeted at small self hosted instances, but you're free to use it as you wish.

Now, on to the bad news: there's not much I can do at the level of LLDAP to support cockroach DB. In fact, if you look in the codebase for MySQL or postgres, you won't find many references. You'd have to add support for cockroach DB at a lower level, in sea-ql (sea-query and sea-orm). Once that's merged in their project, I'd just have to update the dependency version and it'll be automatically supported.

[m-yosefpor]

Thank you for the response and the clarification. We understand that LLDAP is primarily targeted at small self-hosted instances, but given our specific requirements, we believe the solution is the best path forward for us. Additionally, we are seeing increasing appeal for company use cases in the industry as well. This appeal is largely because LLDAP is so much easier to set up and more straightforward compared to OpenLDAP or FreeIPA for most use cases (thank you for providing this wonderful solution), especially for companies using Keycloak/Zitadel/Casdoor for most of their requirements and only needing LDAP for maintaining some users and groups for services which do not support OIDC.

We appreciate the guidance regarding the necessary changes at the lower level in sea-ql (sea-query and sea-orm). We'll look into contributing to those projects to add support for CockroachDB. Once those changes are integrated, we will follow up to ensure LLDAP can update its dependencies accordingly.

Thanks again for your great project and your time and assistance.

[nitnelave]

Oh, and one more thing: sea-query is the query builder, it might actually work with postgres mode. The more complex dependency might be the DB driver, sqlx.