Server: PostgreSQL as alternate database provider

[kspearrin]

Adding another database provider should be fairly straight forward. Bitwarden's data access layer is abstracted away with repository interfaces which can be found under src/Core/Repositories. I've already done the work needed for making this possible. All that is left is re-writing all of the SQL tables, functions, stored procedures, etc over to PostgreSQL and then wiring them up repository implementations for PostgreSQL. I've started this work in the following commits, which can be followed as a model going forward:

https://github.com/bitwarden/server/commit/84800da1fb046ab2da0ad14f6096f9ac04f60405
https://github.com/bitwarden/server/commit/acef40ee82e81ac81d4b17de09a2c174a3b7bfbb

Choosing a database provider is as simple as either providing a connection string for MSSQL or PostgreSQL, whichever you prefer.

I do not have a lot of experience in working with PostgreSQL, so I will need some help to complete this task.

Questions:

• Are PostgreSQL functions equivalent to MSSQL stored procedures?

[jeremyVignelles]

I was just trying to setup bitwarden on my server and immediately saw ram usage getting high due to the use of SQL Server. I was wondering if there was a way to use another db provider, when I found this post.

I've never used PostgreSQL, but I'm curious to know if that would help my server support the extra load.

Why are you using stored procedures for? What other dbms-specfic feature are you using? Would SQLite be a candidate for small environments?

[tamaralo]

Hi there, long time lurker, first time poster.
As somebody who is highly interested in this feature I would like to help with at least my two cents.:

Are PostgreSQL functions equivalent to MSSQL stored procedures?

PosgreSQL stored procedures capabilities surpass MSSQL's, as this was one of the key aspects of its development. For a short info/comparison: https://stackoverflow.com/questions/339744/better-languages-than-sql-for-stored-procedures

I've never used PostgreSQL, but I'm curious to know if that would help my server support the extra load.

What would be your server's setup? Win or *nix? Usually I would say yes, it should reduce some of the load, but I don't want to give you some false hope.

I do not have a lot of experience in working with PostgreSQL, so I will need some help to complete this task.

I never helped I any open source project out of fear of doing something wrong, but if you guys need some help and are willing to help guide me on how to contribute I would gladly help.

[jeremyVignelles]

I never helped I any open source project out of fear of doing something wrong, but if you guys need some help and are willing to help guide me on how to contribute I would gladly help.

You can't mess up a project on your own by doing bad things. If you have the right development skills, go ahead, the worst that could happen is spending too much time, but you'll always learn something in the process 😉

[kspearrin]

@tamaralo All database interaction in Bitwarden with MSSQL is done with stored procedures. You can find them (all all other schema) listed here: https://github.com/bitwarden/server/tree/master/src/Sql/dbo

My assumption is that we'd do the same in PostgreSQL using stored procedures (seems to behave differently in PostgreSQL) or functions. Our SQL data mapper (Dapper) seems to only work with PostgreSQL functions.

The majority of the work here is just translating the MSSQL schema (tables, indexes, sprocs/functions) over to their equivalent on PostgreSQL. After that it's just a matter of wiring them up to a C# repository class, which is fairly easy to do. Do you have enough experience in MSSQL and PostgreSQL to translate between the two? If so, I'd say you're qualified enough to help with this.

[tamaralo]

@jeremyVignelles 😊 I give it a try! I have a lot of fear/respect for opensource projects, as they seem to be so well organized and fast paced. ^^

@kspearrin:
I worked with PostgreSQL, MySQL and some old versions from MSSQL ( in the 2000's ) so I think I could take a crack at it and try it at my server. The thing is, I study alongside my work and ATMI have some finals so, if its ok, I will try to take a look at it by the end of next week. 👍

[kspearrin]

@tamaralo If you have any questions when you get started, you can find me in our chatroom here: https://gitter.im/bitwarden/Lobby

[tamaralo]

@kspearrin Thx, I will come online by the end of next week (sorry, forgot the word next in the last post! Thanks for the prompt reply.

[Kovah]

The 2GB RAM requirement for SQLServer is the only thing holding me back from using Bitwarden. Would love to see an implementation for Postgres or any other database provider. 💙

[indy-singh]

Would love to help Bitwarden support PostgreSQL; though I tend to avoid stored procs/functions and prefer to bring that logic up into the code.

[mrahbar]

👍 For supporting PostgreSQL. I'm a big fan of Bitwarden and a happy paying customer. The only reason of not using a self-hosted Bitwarden is MSSQL.

On the topic tough I think that without any official endorsement of the Bitwarden team for PostgreSQL this endeavor is be doomed to failure. After the initial batch of SQL scripts is ported who is going to create new PostgreSQL scripts every time the equivalent MSSQL scripts are updated? Kudos for a defined data-abstraction-layer but I'm not optimistic when it gets to maintain two different database vendors.

So can this process be only an experiment or a real alternative solution for MSSQL?

[FingerlessGlov3s]

👍
This sounds like a good idea to me. Getting ram usage down and removing minimum 2 GB limit is good. Will make running it more resource efficient on virtual machines etc...

Although we do need support from Bitwarden themselves like @mrahbar says.

[Agraphie]

I would like to see this as well. Are there any experimental branches or any way I can help make this happen?

[CaptAintHere]

Any news about this?

[devployment]

As briefly mentioned here I'll try to volunteer here. Will set things up locally and see where I hit roadblocks.

[jeremyVignelles]

@devployment : 👏 and good luck.
Do you have any experience in working with Postgre SQL?
Please keep us posted 🙂

[devployment]

Do you have any experience in working with Postgre SQL?

@jeremyVignelles, yes I do. It's been a while. But we'll see.

[benjaminpreiss]

Is anyone already working on translating the schemes? Are there any branches?

[kspearrin]

Not that I am aware of.

[wusatosi]

Do you have any experience in working with Postgre SQL?

@jeremyVignelles, yes I do. It's been a while. But we'll see.

Any update?

[benjaminpreiss]

I have started with translating but made close to no progress - but I also have very little experience with sql

[Papina]

Just read this while investigating postgreSQL. I have good experience with tsql to pl/pgsql so I might start to give this a crack.

[Papina]

im down to the nitty gritty now of functions. Im not super familiar with docker, but i cant seem to publish the mssql ports from the docker image successfully. Is there a switch i can add that will expose the 1433 port? when i add it it i get an error os OSX

i modified the function to publish port 1433:

function updateDatabase() {
    pullSetup
    docker run -i --rm --name setup -p 1433:1433 --network container:bitwarden-mssql \
        -v $OUTPUT_DIR:/bitwarden --env-file $ENV_DIR/uid.env bitwarden/setup:$COREVERSION \
        dotnet Setup.dll -update 1 -db 1 -os $OS -corev $COREVERSION -webv $WEBVERSION
    echo "Database update complete"
}

now i get the error:

MacBook-Pro:bitwarden benjamin$ ./bitwarden.sh updatedb
 _     _ _                         _
| |__ (_) |___      ____ _ _ __ __| | ___ _ __
| '_ \| | __\ \ /\ / / _` | '__/ _` |/ _ \ '_ \
| |_) | | |_ \ V  V / (_| | | | (_| |  __/ | | |
|_.__/|_|\__| \_/\_/ \__,_|_|  \__,_|\___|_| |_|

Open source password management solutions
Copyright 2015-2019, 8bit Solutions LLC
https://bitwarden.com, https://github.com/bitwarden

===================================================

Docker version 19.03.2, build 6a30dfc
docker-compose version 1.24.1, build 4667896b

1.32.0: Pulling from bitwarden/setup
Digest: sha256:e88f1611ff88c77a6255c49189ac3c965aaa3576fa6980ba54f2be10a96907b5
Status: Image is up to date for bitwarden/setup:1.32.0
docker.io/bitwarden/setup:1.32.0
docker: Error response from daemon: conflicting options: port publishing and the container type network mode.
See 'docker run --help'.

this is more for testing, so i can make sure the functions im rewriting actually produce the same data

[kspearrin]

You want to connect to the MSSQL instance of a Bitwarden installation? Just bash into the mssql docker container. Then you can run sqlcmd.

$ docker exec bitwarden-mssql bash

[Papina]

no, i want to connect to the mssql instance from the host, so i can run the development tools against the database directly, but still be able to use the bitwarden normally at the same time.

[Papina]

[kspearrin]

Ok. Then create the following file ./bwdata/docker/docker-compose.override.yml.

version: '3'

services:
  mssql:
    ports:
      - '1433:1433'

Restart. Now you can connect to localhost:1433 from tooling on the host.

[Papina]

awesome! got it working finally in dbeaver.

screenshot for those attempting the same, it needs a non-obvious driver, not the driver that is normally used for a windows SQL instance

I will continue with the conversions.

[knocte]

@Papina glad to see some progress; instead of asking for an update I'm going to chip in some funds with gitcoin. Hopefully other people interested in this can join me (adding more funds to it) to incentivise this work to be finished and merged to master.

[gitcoinbot]

Issue Status: 1. Open 2. Started 3. Submitted 4. Done

---

__This issue now has a funding of 0.276 ETH (50.03 USD @ $181.26/ETH) attached to it.__

• If you would like to work on this issue you can 'start work' on the Gitcoin Issue Details page.
  
  
  
  • Want to chip in? Add your own contribution here.
  
  
  • Questions? Checkout Gitcoin Help or the Gitcoin Slack
  
  
  • $155,958.85 more funded OSS Work available on the Gitcoin Issue Explorer
  
  

[gitcoinbot]

Issue Status: 1. Open 2. Started 3. Submitted 4. Done

---

__Work has been started__.

These users each claimed they can complete the work by 1 month, 3 weeks from now.
Please review their action plans below:

1) papina has started work.

1funtion per week until complete
2) ali8668 has started work.

برنامه ای عالی وباعث افتخار من هست که در این برنامه مشارکت میکنم وبه دانش ناچیزم اضافه میشه من دوست دارم که این برنامه به کار خود ادامه بده و به مخاطب خود سرویس بده .
3) ali8668 has started work.

برنامه ای عالی وباعث افتخار من هست که در این برنامه مشارکت میکنم وبه دانش ناچیزم اضافه میشه من دوست دارم که این برنامه به کار خود ادامه بده و به مخاطب خود سرویس بده .
4) alcaravanosu has started work.

I will study all the environment about Bitwarden and his tables and store procedure, to start translating from MSSQL to Postgres. I already start reading https://github.com/bitwarden/server/issues/453 and downloading de docker image to raise up a dev env.

Learn more on the Gitcoin Issue Details page.

[Papina]

Just started a new job, so I have been busy, but I have a few functions translated, just haven't pushed the updates

[FingerlessGlov3s]

Whats the latest update on the conversion?

[Papina]

waiting on two pull requests before i continue further

https://github.com/bitwarden/server/pull/578
https://github.com/bitwarden/server/pull/580

[knocte]

@Papina PR #578 still has a typo which the maintainer is requesting to be addressed. And #580 needs to be rebased because it has conflicts. So it looks like what needs to be done is take action and not wait, or am I missing something?

[Papina]

spelling mistake is fixed, didnt see that review.
the conflicts are updates to existing views, just adding in 'OR REPLACE' to various DDL

[knocte]

There are now conflicts in both PRs :)

[Morl99]

Hello,
I want to chime in and share my opinion that the current approach is futile and will leave the project in a state where maintenance is really hard.

I suggest that we introduce Liquibase to manage the DBMS Setup. It should also support writing DBMS agnostic Functions/Procedures [edit: no, it doesn't] . And the best part is that converting to Liquibase can be done tool based, see their documentation: https://www.liquibase.org/documentation/existing_project.html

I am willing to help with this, if the community and maintainers thinks that this is a good idea.

I am personally motivated because I want to run my own instance of Bitwarden, but mssql is a deal-breaker for my setup. I do have a lot of pssql experience (we run about 100 pssql instances at work, though we spin them up in AWS).

[knocte]

I'm no maintainer, but seeing the other approach seems to be getting no love, I'd go for it @Morl99, worst thing that can happen is that both strategies get committed and the one that has the least maintenance work survives.

[Morl99]

I thought about it some more, and I fear that the approach is a good idea, but the tool might not be a good fit for a .NET environment, and it would be some work to bootstrap this at runtime (like adding a Java runtime to the docker container). I will look into similar tools for .NET. Or does anybody have any experience to share?

[mnuccioarpae]

I fear that there's no such thing as DBMS agnostic functions or procedures.

[Morl99]

Yeah I arrived at the same conclusion. But it would be a help if the schema itself would be declared in a DBMS agnostic way.

Another option would be to reduce the amount of procedures and pull some of that functionality into the application (using sql statements if necessary).

I am specifically looking for recommendations for a dbms agnostic schema creation/update tool that plays well in a dot.net environment.

[knocte]

Another option would be to reduce the amount of procedures and pull some of that functionality into the application (using sql statements if necessary).

I bet the best approach for that would be using EntityFramework or some other ORM, but I also bet that the maintainer of bitwarden would not be happy about this drastic change.

[Morl99]

Let us just ask 😉
@kspearrin do you have an objection against EntityFramework? I had used it in my Bachelors Thesis, and tough the development flow was really nice. But it is a long time ago and the project was a lot smaller than Bitwarden. It would surely be challenge to convert, but I think it could also be a lot of fun.

[kspearrin]

@Morl99 Yes, I am not a fan of ORMs like EF. Too much “auto magic” code generation. Stored procedures are always preferred.

[kspearrin]

@Morl99 we could still consider EF as the base for other alternative database repository implementations, however. It’s just that we cannot use EF for our Sql server production environment which has a much larger scale than the on-prem installations people here are considering using Bitwarden for. So in the end we could have sql server repositories and EF repository implementations.

[jeremyVignelles]

In my experience, EF slows down the startup of application because of the huge reflection usage. Is that still true in 2019?

Yes, using EF could save a lot of dev time in providers implementation, but it adds a dependency that might not be wanted, and adds an overhead to the application.

[jeremyVignelles]

May I suggest the use of Dapper?
It has been created with perf in mind at stackoverflow, and it seems database-agnostic.

[knocte]

IMO slow startup is not an issue for server apps because it just start once, and is running most of the time. Also IME Dapper is worse than EF (more weakly typed) but I don't want to start a flamewar here.

[jeremyVignelles]

It's not about starting a flamewar, we're just discussing alternatives. I don't have any decision power either.

After posting, I realized that Dapper might not be a good fit because you still need to write your SQL queries for anything more complex than a SELECT, so you're going to write DB-specific queries anyway.

You may be right, EF startup time might not be an issue for server-side applications, and I'm pretty sure that things got better since EF core.

EDIT: wouldn't that require to rewrite all our models though, to put annotations like foreign keys etc...?

[mnuccioarpae]

I agree that stored procedures are better. The problem I have is with the comments in the stored procedures, which do not explain the code.

For example in https://github.com/bitwarden/server/blob/master/src/Sql/dbo/Stored%20Procedures/User_DeleteById.sql there's no comment explaining why ciphers are deleted in batches of 100 records and why this is done in a separate transaction. What happens if the second transaction fails? The user remains, but his ciphers are gone? If yes, why this is desired behaviour?

I am pretty sure that all the T-SQL procedures can be translated in an equivalent PostgreSQL procedure (expecially if we require latest PostgreSQL), but it's hard to do without some help.

Maybe an automated tool like one of the ones listed at https://wiki.postgresql.org/wiki/Microsoft_SQL_Server_to_PostgreSQL_Migration_by_Ian_Harding can be helpful at least for the first translation?

[knocte]

@Morl99 Yes, I am not a fan of ORMs like EF.

Another ORMless option (and also storeprocedure-less) would be using https://github.com/rspeele/Rezoom.SQL, the only downside is that this db layer would need to be written in F# instead of C#.

[kspearrin]

Now exploring Entity Framework for this. Started some repository implementations for it here:

https://github.com/bitwarden/server/commit/7c5be176faa5c83b1545d4fd96059f4f4a3b8426

If anyone is experienced with Entity Framework, I'd love for some help.

[mnuccioarpae]

Has anyone tried https://github.com/bigsql/pgtsql ?

[insub]

Is it done ? How to switch to PostgreSQL?

[Papina]

The basic translation of t-sql to postgres functions is done for 95% of the procs. However, there is a lot of functions to go through and renaming of columns etc. A few functions didn't translate and I will have to manually do these few

[knocte]

Renaming of columns, why?

[Papina]

You'll have to read all the commits, but the decision was made to keep to postgresql standards and use snake_case_formatting. So all the functions will have to be modified to match the new schema definitions which uses this format. A bit of extra work, but worth it to get it right the first time. Also AWS DMS wasn't perfect and so I'll need to clean up some of the converted code. Most of it search/replace . But a few functions will need to be rewritten where the conversion just didn't know what to do. Feel free to help! There is plenty of functions to clean up, but we are 80% there as far as stored functions goes IMHO

[ataraxus]

also longtime lurker here. backing off hosting bitwarden because of mssql. Is this the right place to get updates regarding implementaion of psql support?

[cyphase]

Just another interested potential user, but yes, I would say it is.

[knocte]

Guys if you're very interested in this, please chip in a small contribution in the gitcoin issue. +1 comments are just noise.

[MicWit]

Now exploring Entity Framework for this. Started some repository implementations for it here:

7c5be17

If anyone is experienced with Entity Framework, I'd love for some help.

I have used entity framework in the past, but most of my career has been in PHP/MySQL,Oracle or directly in MSSQL. I don't have any spare time at the moment, but I am keen to try get Bitwarden running on MariaDB in order to utilise our Galera cluster (we have just started experimenting this and have multi master with encryption running and are about to move that onto WAN and go live).

Have you had much help with Entity Framework? Is there any documentation on the code etc (or things like relationship diagrams etc) for Bitwarden, or is it all in the code?

[TheRealTripleB]

Hi, what's the current status?

[knocte]

The current status is that I'm really saddened that no one has contributed even 10USD for this to happen. You guys like opensource projects? Then support them with your money. There are 48 upvotes on this issue, if each one of them contributed just 10USD, we would add 480USD to the funding, which would mean that we have a bounty higher than 500 USD, attractive enough for a competent developer to work on it.

[joekerna]

I want to contribute money to this cause. But where can I do it? I'm quite new to this...

[FingerlessGlov3s]

I've clicked the link https://gitcoin.co/issue/bitwarden/server/453/3550
signed in and can't see a way to add funding :-(

[ronnicek]

You will need to create account on some crypto exchange.. proceed with KYC then load money there buy ETH, create wallet for ETH, move money there and then connect it on gitcoin.. because why do it easy way when we can do it hard way?

[FingerlessGlov3s]

no wonder why only one person has backed it then.
That's hella a process!
Can't I just Paypal the PR requester when it happens 🤣

[knocte]

proceed with KYC then load money there buy ETH

There are platforms that don't require KYC where you can buy crypto, e.g. LocalCryptos or HodlHodl.
Plus, there's no need to have ETH, you can use DAI (a stablecoin pegged to USD) with GitCoin.

Can't I just Paypal the PR requester when it happens

PayPal can't act as an intermediary to know if a PR has been done/merged or not. Plus, PayPal can freeze your money in case they are not happy with your opensource project or feature. This is where crypto comes in: no counterparty risk.

[TheRealTripleB]

Okay, well, I'm gonna have to figure out how to set up the money today. I would also appreciate it if other interested people would do the same.

[FingerlessGlov3s]

Yes I know Paypal can't be used for opensource code donations, its not the right platform, it was a joke more than anything 😉

I'll have a look later I do have some Eth in my Ledger but its investment hehe. So would need to go buymore from like coinbase then send it over to gitcoin if they have eth address for my account 🤔. Shall have a better look later.

[tomdavidson]

Ill pitch in too. My family is a paid customer. My company is a paid customer. I will also pitch in directly to this issue and have been evaluating the Rust implementation of Bitwarden over the db issue.

[shortspider]

Is this still looking to be done? Is all thats involved to get parity between src/Core/Repositories/SqlServer and src/Core/Repositories/PostgreSql and the associated functions?

[kspearrin]

We're now looking at using Entity Framework to implement alternate database engines. This is currently on our backlog.

[am-oxipay]

I'm willing to help here. I've ported many projects at work to postgresql.
Is the desire to go to EF directly now ? If so can I have some guidance as to what I can safely work on without clashing with others.

[Spacelord09]

Hey guys! Any updates to this Topic?

[cscharf]

This is on our product roadmap for work by our team in 2021.