Support for more databases

[fforootd]

Based on a discussion on [hackernews] (https://news.ycombinator.com/item?id=31266460)it might make sense for us to support multiple storages.
Even though CockroachDB will be the primary one

Acceptance criteria

• Support Postgresql #3898
• Discuss what more we can support

[fforootd]

I like to hear your thoughts on this @adlerhurst

[adlerhurst]

A question first: will we support them?
IMO before we begin we should discuss how we will test other databases. They might need different indexes. We can assume that we have to create the same indexes and index types as for crdb.

To not break the configuration files structure for v2 we have to add a database type, this must be done before the release.

[adlerhurst]

Added task for database type: #3600

[fforootd]

I did some research around the offerings from the hyperscalers. It turns out that >80% of them has some form of pure Postgresql offering. Based on this I think we should allow and test for plain Postgresql as well besides cockroachDB.

What I currently would ignore are their proprietary offerings e.g CosmosDB, Amazon RDS, Google Spanner. Even though some of them provide Postgresql as dialect

[Sytten]

We are interested in using zitadel and being able to use postgres is the largest hurdle for adoption on our side.

[fforootd]

@Sytten

We are currently valuating to support plain postgresql besides cockroachDB. This because multiple customers are interested in using the managed postgresql offerings.

I think we should be able to given an update to this in the next 2-4 weeks.

[Sytten]

Nice! Let me know if you need beta testers, willing to give you good feedback since I want to implement our integration with v2 as soon as possible 😀

[adlerhurst]

Support for mysql was requested here: #3600 (comment)

[KaKi87]

@hifabienne Considering you set priority to low, I'm guessing this this statement from two months ago is no longer applicable ?

Probably we will have a look at this in 1-2 months.

Thanks

[fforootd]

At the moment we are doing the finishing touches for Postgres which should be ready really soon to be released.

After this we will consider what additional databases might make sense.

[KaKi87]

Oh, very nice, thanks !

[fforootd]

What DB are you interested in?

[KaKi87]

Postgres is fine, I'll try it as soon as it's released.
Although MySQL is my primary database.

I also use MongoDB but I'm guessing that's irrelevant, as I've never seen an app that supports both SQL and NoSQL databases.

[adlerhurst]

hi @Sytten and @KaKi87

We have just released postgresql support of ZITADEL. If you want to check it out you can find the release notes here: https://github.com/zitadel/zitadel/releases/tag/v2.3.0

If you have any feedback we are happy to answer :-)

[KaKi87]

Since PostgreSQL support of Zitadel is limited to the latest major version, I cannot use it, therefore I look forward to MySQL support. Thanks

[fforootd]

Since PostgreSQL support of Zitadel is limited to the latest major version, I cannot use it, therefore I look forward to MySQL support. Thanks

I think it will be a while until we support mysql. Can you shine some light why it is a problem to use Postgresql 14?

[adlerhurst]

Since PostgreSQL support of Zitadel is limited to the latest major version, I cannot use it, therefore I look forward to MySQL support. Thanks

I think it will be a while until we support mysql. Can you shine some light why it is a problem to use Postgresql 14?

question was answered here: https://discord.com/channels/927474939156643850/1014503578439794688/1015302346449883176

[helmut72]

If you have any feedback we are happy to answer :-)

I'm not the ones, but give feedback :-) Also wanted to switch to Postgres after seeing Cockroach RAM usage. In Keycloak the app is the memory hog, in Zitadel it's Cockroach.

This works:

ZITADEL_DATABASE_COCKROACH_HOST=cockroach

... and this doesn't work:

ZITADEL_DATABASE_POSTGRES_HOST=postgres
ZITADEL_DATABASE_POSTGRES_HOST=5432
ZITADEL_DATABASE_POSTGRES_DATABASE=zitadel
ZITADEL_DATABASE_POSTGRES_USER_USERNAME=zitadel
ZITADEL_DATABASE_POSTGRES_USER_PASSWORD=password

postgres is the container name of Postgres.

[adlerhurst]

hi @helmut72

Thanks for your feedback. We are currently working on ram usage of crdb.

I think there is some configuration missing. If you use postgres you have to set all the fields for the connection. The defaults of zitadel are only configured for cockroachdb:

here is an example for disabled ssl on postgres:

ZITADEL_DATABASE_POSTGRES_HOST=postgres
ZITADEL_DATABASE_POSTGRES_PORT=5432
ZITADEL_DATABASE_POSTGRES_DATABASE=zitadel

ZITADEL_DATABASE_POSTGRES_USER_USERNAME=zitadel
ZITADEL_DATABASE_POSTGRES_USER_PASSWORD=password
ZITADEL_DATABASE_POSTGRES_USER_SSL_MODE=disable # is missing in your comment above

if you init the database with zitadel you have to set the admin user additionally:

ZITADEL_DATABASE_POSTGRES_ADMIN_USERNAME=root
ZITADEL_DATABASE_POSTGRES_ADMIN_PASSWORD=root
ZITADEL_DATABASE_POSTGRES_ADMIN_SSL_MODE=disable

If this doesn't help It would be great if you can share the logs of zitadel

[helmut72]

@adlerhurst
Thanks, have added SSL Mode and tried with disable, disabled, false and 0. But no luck. It still wants to connect to a Cockroach db. Which is unset:

level=fatal msg="unable to initialize the database" caller="/home/runner/work/zitadel/zitadel/cmd/initialise/init.go:47" error="dial tcp 127.0.0.1:26257: connect: connection refused"

We are currently working on ram usage of crdb.

I hope Postgres doesn't take this much RAM too... ;) Nevertheless I prefer Postgres for my home setup.

[helmut72]

Update. Now it works. First I've pulled latest :stable release. My image was from end of last week. Still got an error:

level=fatal msg="unable to initialize the database" caller="/home/runner/work/zitadel/zitadel/cmd/initialise/init.go:65" error="ID=DATAB-0pIWD Message=Errors.Database.Connection.Failed Parent=(cannot parse `host=postgres port=5432 user= application_name=zitadel sslmode= sslrootcert=`: failed to configure TLS (sslmode is invalid))"

Then I've also set the Postgres admin variables:

ZITADEL_DATABASE_POSTGRES_ADMIN_USERNAME=zitadel
ZITADEL_DATABASE_POSTGRES_ADMIN_PASSWORD=zitadel
ZITADEL_DATABASE_POSTGRES_ADMIN_SSL_MODE=disable

But even then it doesn't worked:

level=fatal msg="unable to initialize the database" caller="/home/runner/work/zitadel/zitadel/cmd/initialise/init.go:65" error="ID=DATAB-0pIWD Message=Errors.Database.Connection.Failed Parent=(failed to connect to `host=postgres user=db_user database=`: server error (FATAL: database \"db_user\" does not exist (SQLSTATE 3D000)))"

Look at the database name. Same as Postgres user variable. But I have set a different db name. My examples here are other names for security reason.

Then I have tried with zitadel everywhere. Adminuser, Username, DB-Name and Password. Then it worked. It looks like the ENV parser need a fix with ZITADEL_DATABASE_POSTGRES_DATABASE option?

All at all it would be cool if the ENV parser use default values if any option is unset. I have this experience from other projects.

I hope Postgres doesn't take this much RAM too... ;)

Can answer my own question. Postgres use 38MB. For now. That's great. :)

[hifabienne]

@adlerhurst @fforootd Should we close this issue for now? We have added Postgres and I think for now we do not have the intention to add any other? Or how do you see it?

[musicformellons]

I could use yugabytedb support. Based on postgresql, so should be 'relatively easy'.

[KaKi87]

Why not.

Although, I think MySQL is the most urgent one, as it's the most-used database.

[musicformellons]

That's right:
Black = mysql
Orange = postgres
Yellow = cockroachdb
Green = yugabytedb

[Wulfheart]

I would leave it open for tracking the state and put it at the end of the backlog.

For now we focus to improve the currently implemented sql dialects (cockroach, postgresql). Additional dialects add an additional layer of complexity for each change and maintenance overhead. To add more dialects there must be made ground work to simplify the addition of dialects.

So maybe use an ORM like sqlboiler? But that is only a shot as I am not familiar with the code. Just interested on your opinion on this.

[cr1xu5]

SQLite support would be great for compact setups on the edge, but also for testing locally.

Together with solutions like LiteFS SQLite is a great alternative for the "regular" databases.

[gandaldf]

I don't know if anyone need this, but I was able to run Zitadel with Postgres using this docker-compose.yaml:

version: '3.8'

services:
  zitadel:
    restart: 'always'
    networks:
      - 'zitadel'
    image: 'ghcr.io/zitadel/zitadel:stable'
    command: 'start-from-init --masterkey "MasterkeyNeedsToHave32Characters" --tlsMode disabled'
    environment:
      - 'ZITADEL_EXTERNALSECURE=false'
      - 'ZITADEL_DATABASE_POSTGRES_HOST=pgdb'
      - 'ZITADEL_DATABASE_POSTGRES_PORT=5432'
      - 'ZITADEL_DATABASE_POSTGRES_DATABASE=zitadeldb'
      - 'ZITADEL_DATABASE_POSTGRES_ADMIN_USERNAME=zitadeladmin'
      - 'ZITADEL_DATABASE_POSTGRES_ADMIN_PASSWORD=zitadeladmin'
      - 'ZITADEL_DATABASE_POSTGRES_ADMIN_SSL_MODE=disable'
      - 'ZITADEL_DATABASE_POSTGRES_USER_USERNAME=zitadeluser'
      - 'ZITADEL_DATABASE_POSTGRES_USER_PASSWORD=zitadeluser'
      - 'ZITADEL_DATABASE_POSTGRES_USER_SSL_MODE=disable'
    depends_on:
      pgdb:
        condition: 'service_healthy'
    ports:
      - '8080:8080'

  pgdb:
    restart: 'always'
    networks:
      - 'zitadel'
    image: 'postgres:16beta1'
    environment:
      - POSTGRES_USER=zitadeladmin
      - POSTGRES_PASSWORD=zitadeladmin
      - POSTGRES_DB=zitadeldb
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: '10s'
      timeout: '30s'
      retries: 5
      start_period: '20s'
    ports:
      - '9090:8080'
      - '5432:5432'

networks:
  zitadel:

[gitpushy]

I don't know if anyone need this, but I was able to run Zitadel with Postgres using this docker-compose.yaml:

version: '3.8'

services:
  zitadel:
    restart: 'always'
    networks:
      - 'zitadel'
    image: 'ghcr.io/zitadel/zitadel:stable'
    command: 'start-from-init --masterkey "MasterkeyNeedsToHave32Characters" --tlsMode disabled'
    environment:
      - 'ZITADEL_EXTERNALSECURE=false'
      - 'ZITADEL_DATABASE_POSTGRES_HOST=pgdb'
      - 'ZITADEL_DATABASE_POSTGRES_PORT=5432'
      - 'ZITADEL_DATABASE_POSTGRES_DATABASE=zitadeldb'
      - 'ZITADEL_DATABASE_POSTGRES_ADMIN_USERNAME=zitadeladmin'
      - 'ZITADEL_DATABASE_POSTGRES_ADMIN_PASSWORD=zitadeladmin'
      - 'ZITADEL_DATABASE_POSTGRES_ADMIN_SSL_MODE=disable'
      - 'ZITADEL_DATABASE_POSTGRES_USER_USERNAME=zitadeluser'
      - 'ZITADEL_DATABASE_POSTGRES_USER_PASSWORD=zitadeluser'
      - 'ZITADEL_DATABASE_POSTGRES_USER_SSL_MODE=disable'
    depends_on:
      pgdb:
        condition: 'service_healthy'
    ports:
      - '8080:8080'

  pgdb:
    restart: 'always'
    networks:
      - 'zitadel'
    image: 'postgres:16beta1'
    environment:
      - POSTGRES_USER=zitadeladmin
      - POSTGRES_PASSWORD=zitadeladmin
      - POSTGRES_DB=zitadeldb
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: '10s'
      timeout: '30s'
      retries: 5
      start_period: '20s'
    ports:
      - '9090:8080'
      - '5432:5432'

networks:
  zitadel:

If anyone came here in search of why it complains to initialize with failed to configure TLS (sslmode is invalid)), when ZITADEL is initializing it uses the Admin user of postgres, not User.

[unique-dominik]

I'd like to also leave a mark here as I stumbled across this issue and have a different viewpoint onto the problem as well.

Our company operates within the Financial and Insurance Sector. This sectors comes with some limitations that do limit a persons choice of a database drastically (and is probably the reason why you cleverly packaged it into Enterprise 🙈 ).

• They do not especially like self-hosted databases
• They nearly all use Microsoft (few on premise, more and more in the cloud, private or public)
• They audit you/us/the database and the architecture around it
  • They like backup and restore
  • They like if its not self-made but from Microsoft and the kind
  • They like if its automated
  • They like encryption in transit and at rest
  • They like desaster recovery

Now, certainly there are services where you get Cockroach as a Service, but also this generally FIS customers do not really like, especially when the companies have 🇺🇸 -background (🇨🇭 bancs and 🇺🇸 have a special relation).

In the end, we are nearly forced, pushed / deeply motivated to take Azure-hosted databases as FIS customers trust Microsoft just more than any other startup/company that cooks their own soup.

This mindset, that large customers (and large customers bring large money) bring their own database (Cosmos, MySQL, Maria, Postgres or whatever Microsoft hosts) should be also kept in mind when evaluating this issue 🤔

Sadly, that is also why most of them and us can and won't use Zitadel Cloud. They want it in their own Azure Tenant.

Visionary thought: Given that you are 🇨🇭 based and maybe one day want to bark up the FIS sector as well (just dreaming) you would kind of start to fall into the Landing Zone Concept like we do and there, it would be (so I assume) highly encourage that you use one Microsoft DB's 🚀

[KaKi87]

That's a closed-mindedness issue, i.e. invalid from my point of view.

[fforootd]

I'd like to also leave a mark here as I stumbled across this issue and have a different viewpoint onto the problem as well.

Our company operates within the Financial and Insurance Sector. This sectors comes with some limitations that do limit a persons choice of a database drastically (and is probably the reason why you cleverly packaged it into Enterprise 🙈 ).

• They do not especially like self-hosted databases

• They nearly all use Microsoft (few on premise, more and more in the cloud, private or public)

• They audit you/us/the database and the architecture around it

  • They like backup and restore

  • They like if its not self-made but from Microsoft and the kind

  • They like if its automated

  • They like encryption in transit and at rest

  • They like desaster recovery

Now, certainly there are services where you get Cockroach as a Service, but also this generally FIS customers do not really like, especially when the companies have 🇺🇸 -background (🇨🇭 bancs and 🇺🇸 have a special relation).

In the end, we are nearly forced, pushed / deeply motivated to take Azure-hosted databases as FIS customers trust Microsoft just more than any other startup/company that cooks their own soup.

This mindset, that large customers (and large customers bring large money) bring their own database (Cosmos, MySQL, Maria, Postgres or whatever Microsoft hosts) should be also kept in mind when evaluating this issue 🤔

Sadly, that is also why most of them and us can and won't use Zitadel Cloud. They want it in their own Azure Tenant.

Visionary thought: Given that you are 🇨🇭 based and maybe one day want to bark up the FIS sector as well (just dreaming) you would kind of start to fall into the Landing Zone Concept like we do and there, it would be (so I assume) highly encourage that you use one Microsoft DB's 🚀

Thank you for this insightful write up 🙏.

Allow me a question, would it not be sufficient for most to run zitadel inside their azure tenant and use one of the managed Postgres offerings from azure? Or do these customers want a managed zitadel in their own tenant?

Because this is what we see people doing instead of using our cloud service.

(Postgres works in the OSS version already, just yesterday I deployed a zitadel with Google cloud SQL)

As a side note CRDB soon will offer Azure as well, but I do not know the details 😅.

[fforootd]

That's a closed-mindedness issue, i.e. invalid from my point of view.

I am not sure I am getting this.

What is closed-minded?

[KaKi87]

What is closed-minded?

Wanting to only use Azure/AWS/GCP or SaaS, disliking self-hosting.

[unique-dominik]

🤝

Allow me a question, would it not be sufficient for most to run zitadel inside their azure tenant and use one of the managed Postgres offerings from azure? Or do these customers want a managed zitadel in their own tenant?

We see both. We have one customer asking for a fully managed service (where we actually struggle to find a good service-level agreement kind of because Privileged and Conditional Access to now their tenant). But we also have one customer that wants our solution deployed over a LZ but then manages it mostly themselves. We are also still finding our way through Microsoft and their LZ/Marketplace visions and papers but in the end the customers really just care about: Where is the data, who sees/processes it when and for how long and are they in charge of all the encryption keys or not.

Because this is what we see people doing instead of using our cloud service.

Yeah, like us.

Because this is what we see people doing instead of using our cloud service.

I am right now running terraform code to provision an Azure Flexible PSQL to do exactly that 🚀

As a side note CRDB soon will offer Azure as well, but I do not know the details 😅

This would be very interesting if they allow customer-managed encryption keys 🤔

@KaKi87 We do not want that, our customers want that. And they are regulated by authorities and the law so you get edged into a 🗳️ in these situations. Of course you can self-host but then you have to answer every audit why exactly you chose GPG as your favourite key and who exactly can access the key you made on your local machine, when did you rotate it last (show the evidence you did) etc. etc. etc.

Update: You can have a look at this document from an expert in 🇨🇭 about that Does a cloud solution meet the requirements of a Swiss bank? to get an impression of the situation.

[KaKi87]

We do not want that, our customers want that.

I know, but you should be convincing them to open themselves to what the rest of the world can offer, despite the additional efforts required, and eventually help them doing these.

Otherwise, we'll never stop GAFAM from ruling us.

[fforootd]

🤝

Allow me a question, would it not be sufficient for most to run zitadel inside their azure tenant and use one of the managed Postgres offerings from azure? Or do these customers want a managed zitadel in their own tenant?

We see both. We have one customer asking for a fully managed service (where we actually struggle to find a good service-level agreement kind of because Privileged and Conditional Access to now their tenant). But we also have one customer that wants our solution deployed over a LZ but then manages it mostly themselves. We are also still finding our way through Microsoft and their LZ/Marketplace visions and papers but in the end the customers really just care about: Where is the data, who sees/processes it when and for how long and are they in charge of all the encryption keys or not.

Because this is what we see people doing instead of using our cloud service.

Yeah, like us.

Because this is what we see people doing instead of using our cloud service.

I am right now running terraform code to provision an Azure Flexible PSQL to do exactly that 🚀

As a side note CRDB soon will offer Azure as well, but I do not know the details 😅

This would be very interesting if they allow customer-managed encryption keys 🤔

@KaKi87 We do not want that, our customers want that. And they are regulated by authorities and the law so you get edged into a 🗳️ in these situations. Of course you can self-host but then you have to answer every audit why exactly you chose GPG as your favourite key and who exactly can access the key you made on your local machine, when did you rotate it last (show the evidence you did) etc. etc. etc.

Update: You can have a look at this document from an expert in 🇨🇭 about that Does a cloud solution meet the requirements of a Swiss bank? to get an impression of the situation.

Yeah there will always be a need for regulated customers to take more control over their infra and data.

We had the idea once that we could deploy zitadel into customers infra through our customer portal (so basically we deploy to your tenant and provision all the things we need). However we postponed that since it not an easy task to achieve.
An alternative could be the marketplaces, however that is not easy/cheap either 😁

This is why we currently think having postgres and good support for k8s/knative is a great approach.

[unique-dominik]

This is why we currently think having postgres and good support for k8s/knative is a great approach.

Sure! I just left my thoughts as notes in case you revisit the issue one day 🚀 And since it works since yesterday with AKS Flex PSQL we can cover most of the audited world ourselves already 💪

[moay]

Thank you for this insightful write up 🙏.

Allow me a question, would it not be sufficient for most to run zitadel inside their azure tenant and use one of the managed Postgres offerings from azure? Or do these customers want a managed zitadel in their own tenant?

Short addition here @fforootd: We are one of those customers and currently cannot switch to Zitadel because of the missing MySQL support. I'd love to, but our existing infrastructure is all MySQL, including backups, security agreements, SLAs, legal stuff, management/escalation agreements and so on. Our team is too small/busy to get into longer conversations with the legal department/compliance department/etc why we can't use existing resources, who will maintain Postgres in case of disaster, etc..

The hurdles to take might be out of control of the dev department and sometimes there is a lot of enterprise overhead in bigger structures. As I'd assume supporting MySQL should be just about adding some existing driver, I'd be really happy to see this feature and start switching over to Zitadel.

[holotrack]

Any news on MySQL/TiDB support?

[hifabienne]

Any news on MySQL/TiDB support?

At the moment we do not have any news regarding support of mysql or any other databases.
A new database always brings additional complexity into the product, as not every DB works exactly the same. We can already see that with cockcroach and PostgreSQL.
Right now, our focus is on improving the current integrations, not adding more. However, this might change in the future. So we still hold the issue open to get all your feedback and opinion.

[holotrack]

At the moment we do not have any news regarding support of mysql or any other databases. A new database always brings additional complexity into the product, as not every DB works exactly the same. We can already see that with cockcroach and PostgreSQL. Right now, our focus is on improving the current integrations, not adding more. However, this might change in the future. So we still hold the issue open to get all your feedback and opinion.

Maybe im missing something but why not to use some ORM?

[BenjaminGK]

We are a big enterprise company with several sister companies and only uses MYSQL, any plans on supporting this anytime soon as it is the only reason preventing us from using your solution?

[KaKi87]

We are a big enterprise company with several sister companies and only uses MYSQL

That doesn't make any sense.

It's like saying "we only use SOAP APIs powered by Java running on Ubuntu Server" : sure it's common but still a ridiculous meaningless limitation. 🤷

[jnischler]

I think that cloud spanner would also be a good choice.
Of course especially for the google cloud users ;)

We are currently using spanner to store permission on a global scale.
We would like to do the same with our user identities ...

Spanner should behave quite similar to Cockroach so maybe porting isn't that hard compared to other types of databases.

Thanks

[fforootd]

We are a big enterprise company with several sister companies and only uses MYSQL, any plans on supporting this anytime soon as it is the only reason preventing us from using your solution?

Nice to hear from you.

At the moment we have not planned mysql compatibility but I would encourage you to use a postgres/cockroachdb since we have a lot of experience with these storage layers.

The main reason to (not yet) support other storage layers is that we currently lack the engineering capacity to do this in a high quality 😁 I hope you understand this.

[fforootd]

I think that cloud spanner would also be a good choice. Of course especially for the google cloud users ;)

We are currently using spanner to store permission on a global scale. We would like to do the same with our user identities ...

Spanner should behave quite similar to Cockroach so maybe porting isn't that hard compared to other types of databases.

Thanks

@adlerhurst and I where playing around with the postgres driver of spanner in the past but never finished it 😁

But certainly a postgres spanner optimization would be an interesting thing.