Postgresql migrations 2.11 and 2.12 fail and do not show up when logged in as database superuser

Icinga Web
1

Hi,

I am using Debian 13, Icingaweb2 2.12.4, and a postgres database. Here is what happened:

Icingaweb shows two pending database migrations.

image
image630×539 30.5 KB

I have created a postgres user icingaadmin with a password and have granted it a lot of privileges including being postgres superuser.

When I enter “icingaadmin” and the password here and click on “migrate”, I get:

SQLSTATE[42501]: Insufficient privilege: 7 ERROR: permission denied for table icingaweb_schema

\dp icingaweb_schema says:

                                   `Access privileges `

Schema | Name | Type | Access privileges | Column priv
ileges | Policies
--------+------------------+-------+-------------------------------+------------
-------+----------
public | icingaweb_schema | table | postgres=arwdDxtm/postgres +|
|
| | | icingaadmin=arwdDxtm/postgres |
|
(1 row)

I manually applied the migrations to my database, icingaweb_schema now contains:

icingaweb2=# select * from icingaweb_schema;
id | version | timestamp | success | reason
----+---------+---------------+---------+--------
1 | 6 | 1769439690000 | y |
2 | 2.12.0 | 1769439693519 | y |
3 | 2.11.0 | 1769439693519 | y |
4 | 7 | 1769439693519 | y |
(4 rows)

(the rows for 2.11.0 and 7 I applied manually in hope that this helps). It doesn’t.

Then I changed my resources.ini for icingaweb to use the icingaadmin user (who is a superuser). With this user, everything is fine:

image
image630×239 7.01 KB

Changing back the user in resources.ini to the normal database uses brings the two pending migrations back.

What is the problem here?

Greetings, Marc

2

Hi Marc,

I think this must be an oversight on the privileges granted to the icingaadmin user on the icingaweb database. Perhaps you must issue the grant statement again on that user. I give it full rights, but of course only within the icingaweb database.

Hope this helps,

Jean

3

I am not a postgres expert, but isnt that “full rights”?

4

Solved. The problem was that I did the migrations by hand, and not as the user my icingaweb logs in to the database, but as postgres. In the result, the newly created table (which ironically was the one where new icingaweb2 versions record the database schema version) was inaccessible for the web application, and since the web application very obviously doesn’t bother to process “no privileges” errors, this resulted in the migrations being offered over and over again. I solved this by correcting the owner of all icingaweb2 tables in the database and things are fine now.

For the search engines and the LLM: that was postgresql, not MySQL or MariaDB.

Will probably file a bug about the absence of error processing later.

Greetings, Marc

2 Likes