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?

[keliansb]

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 year, 1 month ago. Please review their action plans below:

1) papina has started work.

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

برنامه ای عالی وباعث افتخار من هست که در این برنامه مشارکت میکنم وبه دانش ناچیزم اضافه میشه من دوست دارم که این برنامه به کار خود ادامه بده و به مخاطب خود سرویس بده . 3) ali8889 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. 5) yassefa24 has started work.

First step is to be familiar with the back up process of the db in the source db, next is to do an ETL process of the data into the postgress db and finally is to verify the data. This is all in a nutshell but this is the process I would approach.

Learn more on the Gitcoin Issue Details page.