[v1.0.0b] - Postgresql backup restore fails when not user postgres superuser

[wipash]

First Check

• This is not a feature request
• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue and didn't find it.
• I searched the Mealie documentation, with the integrated search.
• I already read the docs and didn't find an answer.

What is the issue you are experiencing?

Postgresql version: 14.4
Mealie version: 1.0.0beta-3

When restoring a backup using the builtin backup restore functionality, the backup fails if Mealie is configured to use a user other than the postgres superuser.

The cause seems to be in the drop_all() function of alchemy_exporter.py. Postgres doesn't allow SET session_replication_role = 'replica' if you are not a superuser.

Log from postgres server:

2022-07-22 04:42:24.951 GMT [424] ERROR:  permission denied to set parameter "session_replication_role"
2022-07-22 04:42:24.951 GMT [424] STATEMENT:  SET session_replication_role = 'replica'
2022-07-22 04:42:24.952 GMT [424] ERROR:  current transaction is aborted, commands ignored until end of transaction block
2022-07-22 04:42:24.952 GMT [424] STATEMENT:  SET session_replication_role = 'origin'

Mealie is deployed with these environment variables:

        TZ: Pacific/Auckland
        TOKEN_TIME: 43830
        DB_ENGINE: postgres
        POSTGRES_USER: mealie
        POSTGRES_PASSWORD: longandcomplexpassword
        POSTGRES_SERVER: my.postgres.server
        POSTGRES_PORT: 5432
        POSTGRES_DB: mealie
        BASE_URL: https://mealie.myhouse.com

If I set mealie to use the postgres superuser account, the restore works as expected.

Deployment

Docker (Linux)

Deployment Details

No response

[wipash]

Easy enough to work around

ALTER USER mealie WITH SUPERUSER;

# Run restore from Mealie

ALTER USER mealie WITH NOSUPERUSER;

[Taylor-Tyson]

Newbie question, how do I connect to the postgres database to alter the user?

[yottabit42]

I am using Mealie from the Truecharts repo in TrueNAS. I was able to grant superuser access to the mealie user in postgres by obtaining the postgres user password from the postgres container shell with the env command. I then installed pgadmin, connected to the mealie database, authenticated as the postgres user, and was able to grant superuser rights to the mealie user. I was then able to successfully restore the backup from the Mealie UI. Hope this helps anyone else that runs into the same problem.

[0skater0]

I am using Mealie from the Truecharts repo in TrueNAS. I was able to grant superuser access to the mealie user in postgres by obtaining the postgres user password from the postgres container shell with the env command. I then installed pgadmin, connected to the mealie database, authenticated as the postgres user, and was able to grant superuser rights to the mealie user. I was then able to successfully restore the backup from the Mealie UI. Hope this helps anyone else that runs into the same problem.

Thanks for the explanation!
How did you connect to the mealie database with pgadmin?
I installed pgadmin as an application within TrueNAS but i don't know what hostname/address i should use.
The hostnames i tried do not seem to work.
Do you have any advice?

[yottabit42]

Thanks for the explanation! How did you connect to the mealie database with pgadmin? I installed pgadmin as an application within TrueNAS but i don't know what hostname/address i should use. The hostnames i tried do not seem to work. Do you have any advice?

Run sudo bash heavyscript.sh dns. You can get heavyscript.sh from Github.

If you need more help, you might want to check out the TrueCharts Discord, and ask in the #unsupported channel. I finally figured out the missing pieces (the env command in the container shell) with help from folks there.

[regularguy01]

I moved from SQLite to Postgres. I ran the commands above
ALTER USER mealie WITH SUPERUSER;

# Run restore from Mealie

ALTER USER mealie WITH NOSUPERUSER;

You don't have to do this on the New Version. The mealie user is a SUPERUSER now. However you DO need to make another superuser before you run the NOSUPERUSER one as there is only one User mealie as default. If you don't you will lock the restore out since you cannot re-promote mealie if you take SUPERUSER from it.