Support for cockroachdb

[terribleplan]

I was working on moving my installation and wanted to move from sqlite to cockroachdb for better redundancy (storing the rest of the files on network storage, so putting sqlite on that is problematic). Following the migration process outlined in the wiki I got to the point of starting vaultwarden targeting the new DB to run migrations, which failed as cockroach does not implement 100% of pg including SET CONSTRAINTS ALL DEFERRED. (Which according to this note doesn't actually do anything where the problem was first encountered)

Is there any will or desire to support cockroach? It's mostly pg compatible and pretty easy to get a single-node instance running for testing.

[BlackDex]

While it would be nice. I'm not really sure we can fully remove that. As the comment also mentions that it might be needed for that command to be included into the migrations it self.

While, i think that command does work, since it did solve the foreign key constraint issues during migrations. It may be possible to create a special environment flag to exclude that command. When i have time i will try to see if i can do some testing.

[BlackDex]

@terribleplan, I think it will be a terrible plan (pun intended) to enable cockroache support.
I have tried it doesn't even allows me to pass all migrations.

If i then export the schema of a running PostgresDB full DDL, and import that into the CockroachDB Vaultwarden at least seems to start.
I can even create a user and login.

But i can't add a cipher, create an organization etc..
I'm afraid there needs to be to much of changes done for it be a seamless integration.
If CockroachDB would support all the SQL queries and features provided by PostgreSQL and used by Vaultwarden it probably wouldn't be an issue.

So, I'm not seeing this happening in the near future I'm afraid.

[Y0ngg4n]

@BlackDex maybe YugabyteDB would be an alternative? It is more compatible with Postgresql.

[BlackDex]

I did a quick check, and it fails on an ALTER TABLE command, reporting it is not yet supported.
This was done using the latest tagged image of YugabyteDB.

Maybe a final result of a schema would work, but that still could cause future issues during migrations.

[Y0ngg4n]

@BlackDex but would be nice if we could find a way to support cluster databases.

[BlackDex]

Well, if Diesel supports it, its fine. But were not going to jump through hoops to add an other query language. That will be too much I think.

If those applications support full PostgreSQL, there would be no issue. So, i would rather poke at those alternative DB's to fix that 😉

[jamcole]

So I noticed the "SET CONSTRAINTS ALL DEFERRED" statement was removed in a recent PR...

#3964

I'll be trying Vaultwarden out on CockroachDb again soon

[jamcole]

Ah yeah, not working yet with the latest :testing tagged docker image connecting to a new blank cockroachdb from Google Cloud

[panic][ERROR] thread 'main' panicked at 'Error running migrations: QueryError(DieselMigrationName { name: "2020-08-02-025025_add_favorites_table", version: MigrationVersion("20200802025025") }, DatabaseError(Unknown, "table \"favorites\" is being added"))': src/db/mod.rs:485

I haven't spent any time debugging this issue.

FWIW, anyone else looking to test this can use one of their free instances https://www.cockroachlabs.com/pricing/

[franchb]

Solved by this patch:

Subject: [PATCH] cockroachdb migrations patch
---
Index: migrations/postgresql/2020-08-02-025025_add_favorites_table/down.sql
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/migrations/postgresql/2020-08-02-025025_add_favorites_table/down.sql b/migrations/postgresql/2020-08-02-025025_add_favorites_table/down.sql
--- a/migrations/postgresql/2020-08-02-025025_add_favorites_table/down.sql	(revision 48836501bf348386d9bb1378fb56db33c19d3732)
+++ b/migrations/postgresql/2020-08-02-025025_add_favorites_table/down.sql	(date 1700662005031)
@@ -1,13 +1,1 @@
-ALTER TABLE ciphers
-ADD COLUMN favorite BOOLEAN NOT NULL DEFAULT FALSE;
-
--- Transfer favorite status for user-owned ciphers.
-UPDATE ciphers
-SET favorite = TRUE
-WHERE EXISTS (
-  SELECT * FROM favorites
-  WHERE favorites.user_uuid = ciphers.user_uuid
-    AND favorites.cipher_uuid = ciphers.uuid
-);
-
 DROP TABLE favorites;
Index: migrations/postgresql/2020-08-02-025025_add_favorites_table/up.sql
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/migrations/postgresql/2020-08-02-025025_add_favorites_table/up.sql b/migrations/postgresql/2020-08-02-025025_add_favorites_table/up.sql
--- a/migrations/postgresql/2020-08-02-025025_add_favorites_table/up.sql	(revision 48836501bf348386d9bb1378fb56db33c19d3732)
+++ b/migrations/postgresql/2020-08-02-025025_add_favorites_table/up.sql	(date 1700662005036)
@@ -4,13 +4,3 @@
 
   PRIMARY KEY (user_uuid, cipher_uuid)
 );
-
--- Transfer favorite status for user-owned ciphers.
-INSERT INTO favorites(user_uuid, cipher_uuid)
-SELECT user_uuid, uuid
-FROM ciphers
-WHERE favorite = TRUE
-  AND user_uuid IS NOT NULL;
-
-ALTER TABLE ciphers
-DROP COLUMN favorite;
Index: migrations/postgresql/2020-08-02-025026_add_favorites_table_fix/up.sql
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/migrations/postgresql/2020-08-02-025026_add_favorites_table_fix/up.sql b/migrations/postgresql/2020-08-02-025026_add_favorites_table_fix/up.sql
new file mode 100644
--- /dev/null	(date 1700662005028)
+++ b/migrations/postgresql/2020-08-02-025026_add_favorites_table_fix/up.sql	(date 1700662005028)
@@ -0,0 +1,9 @@
+-- Transfer favorite status for user-owned ciphers.
+INSERT INTO favorites(user_uuid, cipher_uuid)
+SELECT user_uuid, uuid
+FROM ciphers
+WHERE favorite = TRUE
+  AND user_uuid IS NOT NULL;
+
+ALTER TABLE ciphers
+DROP COLUMN favorite;
Index: migrations/postgresql/2020-08-02-025026_add_favorites_table_fix/down.sql
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/migrations/postgresql/2020-08-02-025026_add_favorites_table_fix/down.sql b/migrations/postgresql/2020-08-02-025026_add_favorites_table_fix/down.sql
new file mode 100644
--- /dev/null	(date 1700662005033)
+++ b/migrations/postgresql/2020-08-02-025026_add_favorites_table_fix/down.sql	(date 1700662005033)
@@ -0,0 +1,11 @@
+ALTER TABLE ciphers
+ADD COLUMN favorite BOOLEAN NOT NULL DEFAULT FALSE;
+
+-- Transfer favorite status for user-owned ciphers.
+UPDATE ciphers
+SET favorite = TRUE
+WHERE EXISTS (
+  SELECT * FROM favorites
+  WHERE favorites.user_uuid = ciphers.user_uuid
+    AND favorites.cipher_uuid = ciphers.uuid
+);

[BlackDex]

I'm not seeing any difference in the files, just a rename of the folder, the queries are exactly the same.

[franchb]

Sorry, I had to split 2020-08-02-025025_add_favorites_table migration into two:

CREATE TABLE favorites (
  user_uuid   VARCHAR(40) NOT NULL REFERENCES users(uuid),
  cipher_uuid VARCHAR(40) NOT NULL REFERENCES ciphers(uuid),

  PRIMARY KEY (user_uuid, cipher_uuid)
);

and

-- Transfer favorite status for user-owned ciphers.
INSERT INTO favorites(user_uuid, cipher_uuid)
SELECT user_uuid, uuid
FROM ciphers
WHERE favorite = TRUE
  AND user_uuid IS NOT NULL;

ALTER TABLE ciphers
DROP COLUMN favorite;

After this fix Vaultwarden performs all migrations and starts. Another error raises after you try to create a new account and perform a login attempt.

In my fork I've simply replaced INTEGER with INT4 in schema migrations for PostgreSQL. Another option would be setting a session variable SET default_int_size = 4;.

Now my Vaultwarden works nice with CockroachDB replicated cluster.

If I will observe any issues running vw with crdb I will post it here.

Some useful links:

• me: Make migrations not depend on default_int_size param prisma/prisma-engines#2751
• [CockroachDB] No difference between integer and bigInteger knex/knex#4953

[gerwim]

@franchb Did you encounter any other issues with using CockroachDB? Would love to use it too.

[jamcole]

@franchb have you submitted a PR for this yet - and if not, is your fork still available?

[haras-unicorn]

hey yall
made a pr #5696
would appreciate if you would check it out
thx!