Postgresql Implementation Issues

kevdog · November 7, 2024, 9:57pm

I’ve struggled through this all day.

Back when program was called bitwarden_rs I ran the installatiion with a mysqllite backend but then later migrated to a postgreql 14 backend through the process documented on the website with the diesel migration instructions. All worked well.
The postgres implementation I was running was a docker container (bitnami postgres 14.4 specifically). This has served me very well for about 2-3 years.

11.4 postgres is near end of life, and I’ve struggled mightly trying upgrade to postgres 16 which is the current version. I’ve really struggled how to create another bitnami 16 container and pg_restore the data. I had a lot of time getting error messages, so to assume some better control why the upgrade wasn’t working, I simply installed postgres 16 on the docker host. I went through the same mechanism as basically documented here: Upgrade PostgreSQL in Docker to create and attempt to restore the data to the new postgresql instance. From within pgsql 16 I can list the tables within the bitwarden database:

postgres=# \c bitwarden
You are now connected to database "bitwarden" as user "postgres".
bitwarden=# \dt*
                       List of relations
 Schema |            Name            | Type  |      Owner
--------+----------------------------+-------+-----------------
 public | __diesel_schema_migrations | table | postgresql-user
 public | attachments                | table | postgresql-user
 public | auth_requests              | table | postgresql-user
 public | ciphers                    | table | postgresql-user
 public | ciphers_collections        | table | postgresql-user
 public | collections                | table | postgresql-user
 public | collections_groups         | table | postgresql-user
 public | devices                    | table | postgresql-user
 public | emergency_access           | table | postgresql-user
 public | event                      | table | postgresql-user
 public | favorites                  | table | postgresql-user
 public | folders                    | table | postgresql-user
 public | folders_ciphers            | table | postgresql-user
 public | groups                     | table | postgresql-user
 public | groups_users               | table | postgresql-user
 public | invitations                | table | postgresql-user
 public | org_policies               | table | postgresql-user
 public | organization_api_key       | table | postgresql-user
 public | organizations              | table | postgresql-user
 public | sends                      | table | postgresql-user
 public | twofactor                  | table | postgresql-user
 public | twofactor_duo_ctx          | table | postgresql-user
 public | twofactor_incomplete       | table | postgresql-user
 public | users                      | table | postgresql-user
 public | users_collections          | table | postgresql-user
 public | users_organizations        | table | postgresql-user
(26 rows)

Although this view from within the database seems very similar to the postgres11 screen, I think the issue has to do with the diesel migrations. Here are the errors from postgres16:

Nov 07 15:31:15 archBW postgres[1917441]:                version VARCHAR(50) PRIMARY KEY NOT NULL,
Nov 07 15:31:15 archBW postgres[1917441]:                run_on TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
Nov 07 15:31:15 archBW postgres[1917441]:         );
Nov 07 15:31:15 archBW postgres[1917441]:
Nov 07 15:32:18 archBW postgres[1918193]: 2024-11-07 15:32:18.111 CST [1918193] ERROR:  permission denied for schema public at character 28
Nov 07 15:32:18 archBW postgres[1918193]: 2024-11-07 15:32:18.111 CST [1918193] STATEMENT:  CREATE TABLE IF NOT EXISTS __diesel_schema_migrations (
Nov 07 15:32:18 archBW postgres[1918193]:                version VARCHAR(50) PRIMARY KEY NOT NULL,
Nov 07 15:32:18 archBW postgres[1918193]:                run_on TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
Nov 07 15:32:18 archBW postgres[1918193]:         );
Nov 07 15:32:18 archBW postgres[1918193]:

The errors from vaultwarden are the following:

/--------------------------------------------------------------------\
|                        Starting Vaultwarden                        |
|                           Version 1.32.3                           |
|--------------------------------------------------------------------|
| This is an *unofficial* Bitwarden implementation, DO NOT use the   |
| official channels to report bugs/features, regardless of client.   |
| Send usage/configuration questions or feature requests to:         |
|   https://github.com/dani-garcia/vaultwarden/discussions or        |
|   https://vaultwarden.discourse.group/                             |
| Report suspected bugs/issues in the software itself at:            |
|   https://github.com/dani-garcia/vaultwarden/issues/new            |
\--------------------------------------------------------------------/

[INFO] Using saved config from `data/config.json` for configuration.

[WARNING] The following environment variables are being overridden by the config.json file.
[WARNING] Please use the admin panel to make changes to them:
[WARNING] DOMAIN, SIGNUPS_ALLOWED, ADMIN_TOKEN, SMTP_HOST, SMTP_SECURITY, SMTP_PORT, SMTP_FROM, SMTP_USERNAME, SMTP_PASSWORD

[2024-11-07 15:52:45.059][panic][ERROR] thread 'main' panicked at 'Error running migrations: DatabaseError(Unknown, "permission denied for schema public")': src/db/mod.rs:493
   0: vaultwarden::init_logging::{{closure}}
   1: std::panicking::rust_panic_with_hook
   2: std::panicking::begin_panic_handler::{{closure}}
   3: std::sys::backtrace::__rust_end_short_backtrace
   4: rust_begin_unwind
   5: core::panicking::panic_fmt
   6: core::result::unwrap_failed
   7: vaultwarden::db::DbPool::from_config
   8: vaultwarden::main::{{closure}}
   9: vaultwarden::main
  10: std::sys::backtrace::__rust_begin_short_backtrace
  11: main
  12: <unknown>
  13: __libc_start_main
  14: _start

Clearly the diesel migrations seem to be running, however it’s on the saved data.

So thinking of another method – is there a way to start off with a fresh postgresql database – actually export all the data from the old vaultwarden instance using the vaultwarden interface – and then reimport the data through vaultwarden directly to accomplish this? I’m really really running into issues here trying to upgrade postgres versions directly running a combination of pg_dump/pg_dumpall and then pg_restore. Or is there another method I could try?

BlackDex · November 7, 2024, 10:21pm

It looks like the rights are not correctly set.
Vaultwarden isn’t able to change the schema.

Trying to do in it any other way will not help, as you will then run into issue in the future.

Make sure the rights are correctly set and try again.

kevdog · November 7, 2024, 11:07pm

Ok I thought that initially however I’m not exactly sure where to look for these permissions (not greatly familiar with postgresql). So here are my roles, and permissions with the working 11.4 database:

psql (11.14)
Type "help" for help.

bitwarden=# \du
                                      List of roles
    Role name    |                         Attributes                         | Member of
-----------------+------------------------------------------------------------+-----------
 postgres        | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 postgresql-user | Create DB, Replication                                     | {}

bitwarden=# \dt*
                       List of relations
 Schema |            Name            | Type  |      Owner
--------+----------------------------+-------+-----------------
 public | __diesel_schema_migrations | table | postgresql-user
 public | attachments                | table | postgresql-user
 public | auth_requests              | table | postgresql-user
 public | ciphers                    | table | postgresql-user
 public | ciphers_collections        | table | postgresql-user
 public | collections                | table | postgresql-user
 public | collections_groups         | table | postgresql-user
 public | devices                    | table | postgresql-user
 public | emergency_access           | table | postgresql-user
 public | event                      | table | postgresql-user
 public | favorites                  | table | postgresql-user
 public | folders                    | table | postgresql-user
 public | folders_ciphers            | table | postgresql-user
 public | groups                     | table | postgresql-user
 public | groups_users               | table | postgresql-user
 public | invitations                | table | postgresql-user
 public | org_policies               | table | postgresql-user
 public | organization_api_key       | table | postgresql-user
 public | organizations              | table | postgresql-user
 public | sends                      | table | postgresql-user
 public | twofactor                  | table | postgresql-user
 public | twofactor_duo_ctx          | table | postgresql-user
 public | twofactor_incomplete       | table | postgresql-user
 public | users                      | table | postgresql-user
 public | users_collections          | table | postgresql-user
 public | users_organizations        | table | postgresql-user
(26 rows)

bitwarden=# \l
 bitwarden | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres                  +
           |          |          |             |             | postgres=CTc/postgres         +
           |          |          |             |             | "postgresql-user"=CTc/postgres
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres                   +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres                   +
           |          |          |             |             | postgres=CTc/postgres

Here is my 16 setup:

psql (16.3)
Type "help" for help.

postgres=# \du
                                List of roles
    Role name    |                         Attributes
-----------------+------------------------------------------------------------
 postgres        | Superuser, Create role, Create DB, Replication, Bypass RLS
 postgresql-user | Create DB, Replication

bitwarden-# \dt*
                       List of relations
 Schema |            Name            | Type  |      Owner
--------+----------------------------+-------+-----------------
 public | __diesel_schema_migrations | table | postgresql-user
 public | attachments                | table | postgresql-user
 public | auth_requests              | table | postgresql-user
 public | ciphers                    | table | postgresql-user
 public | ciphers_collections        | table | postgresql-user
 public | collections                | table | postgresql-user
 public | collections_groups         | table | postgresql-user
 public | devices                    | table | postgresql-user
 public | emergency_access           | table | postgresql-user
 public | event                      | table | postgresql-user
 public | favorites                  | table | postgresql-user
 public | folders                    | table | postgresql-user
 public | folders_ciphers            | table | postgresql-user
 public | groups                     | table | postgresql-user
 public | groups_users               | table | postgresql-user
 public | invitations                | table | postgresql-user
 public | org_policies               | table | postgresql-user
 public | organization_api_key       | table | postgresql-user
 public | organizations              | table | postgresql-user
 public | sends                      | table | postgresql-user
 public | twofactor                  | table | postgresql-user
 public | twofactor_duo_ctx          | table | postgresql-user
 public | twofactor_incomplete       | table | postgresql-user
 public | users                      | table | postgresql-user
 public | users_collections          | table | postgresql-user
 public | users_organizations        | table | postgresql-user
(26 rows)

bitwarden-# \l
                                                            List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |       Access privileges
-----------+----------+----------+-----------------+-------------+-------------+------------+-----------+--------------------------------
 bitwarden | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =Tc/postgres                  +
           |          |          |                 |             |             |            |           | postgres=CTc/postgres         +
           |          |          |                 |             |             |            |           | "postgresql-user"=CTc/postgres
 postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           |
 template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =c/postgres                   +
           |          |          |                 |             |             |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =c/postgres                   +
           |          |          |                 |             |             |            |           | postgres=CTc/postgres
(4 rows)

Is there anywhere else I should be looking?? My vaultwarden connection string to the database is the follows:

This is for 11.4 Version

DATABASE_URL=postgresql://postgresql-user:postgresql-user-pass@postgresql:5432/bitwarden

And for 16 Version

DATABASE_URL=postgresql://postgresql-user:postgresql-user-pass@host.docker.internal:5434/bitwarden

Yes I’m running the two instances on a different port

kevdog · November 7, 2024, 11:12pm

I think I may have figured this out after thinking about the problem further with you giving me a little nudge.

I had the error:
DatabaseError(Unknown, "permission denied for schema public")

A google search termed up this solution:

Which led me to this solution:

Basically something like:
GRANT ALL ON SCHEMA public to "postgresql-user";

I’m using postgresql-user although I believe in your documentation this user is called “vaultwarden”.

After making the changes things immediately connected. Thanks for giving me a nudge. Hopefully I can write the process up since it’s likely I’ll totally forget all the steps (which there really aren’t that many) since I won’t likely upgrade for a few more years.

Topic		Replies	Views
When starting Vaultwarden with a fresh postgresql backend -- does vaultwarden create the table structure?	0	211	June 23, 2024
Recommended version of PostgreSQL to use with Vaultwarden	0	1063	February 5, 2022
Can't create table `bitwarden`.`sends` (Upgrade to 1.20.0) Help	3	706	March 29, 2021
[SOLVED] Docker and MySQL Help	9	2781	October 3, 2020
Difficulties migrating from sqlite to postgresql [SOLVED] Help	2	3442	October 18, 2020

Postgresql Implementation Issues

Related topics