Bug #24460: usersessions table creation may fail on postgresql 15 and more - Rudder - Issue Tracker

Actions

Copy link

Bug #24460

open

usersessions table creation may fail on postgresql 15 and more

Added by Nicolas CHARLES about 2 months ago. Updated 4 days ago.

Status:

New

Priority:

N/A

Assignee:

Category:

System integration

Target version:

7.3.15

Pull Request:

Severity:

UX impact:

User visibility:

Effort required:

Priority:

Name check:

To do

Fix check:

To do

Regression:

Description

Since Postgresql 15, only the database owner can create table
However, it seems that some users (dating from 7.2.5) have the rudder database owned by "postgres" rather than "rudder"
I don't know how it happened, but as a consequence:

There is webapp error log

2024-03-14 16:19:46.646:WARN :oejs.HttpChannel:qtp110992469-19: /rudder/j_spring_security_check
Exception in thread "zio-fiber-9917" com.normation.errors$SystemError: SystemError(Error when saving session 'node0i5jqqg1dc3kt10qq2y4gg97wk4' info for user 'a545913',org.postgresql.util.PSQLException: ERROR: relation "usersessions" does not exist|  Position: 13)|?at zio.interop.ZioMonadError.raiseError.trace(cats.scala:545)|?at .onError(ApplicativeError.scala:241:0)|?at .guaranteeCase(MonadCancel.scala:375:0)|?at com.normation.rudder.db.Doobie.transactIOResult(Doobie.scala:92)|?at com.normation.rudder.users.JdbcUserRepository.logStartSession(UserRepository.scala:581)|?at com.normation.zio.ZioRuntime.unsafeRun(ZioCommons.scala:445)
        at zio.interop.ZioMonadError.raiseError.trace(cats.scala:545)
        at .onError(ApplicativeError.scala:241:0)
        at .guaranteeCase(MonadCancel.scala:375:0)
        at com.normation.rudder.db.Doobie.transactIOResult(Doobie.scala:92)
        at com.normation.rudder.users.JdbcUserRepository.logStartSession(UserRepository.scala:581)
        at com.normation.zio.ZioRuntime.unsafeRun(ZioCommons.scala:445)

when the webapp starts, there is the following message

[2024-03-14 16:01:49+0100] ERROR bootchecks - Error when trying to create user tables: SystemError: Error with 'Users' table creation; cause was: org.postgresql.util.PSQLException: ERROR: permission denied for schema public

the postgresql information is

rudder=# \l rudder
                                              List of databases
  Name  |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider | Access privileges
--------+----------+----------+-------------+-------------+------------+-----------------+-------------------
 rudder | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
(1 row)

we should ensure the ownership of the database to rudder

Actions

Copy link

Updated by Nicolas CHARLES about 2 months ago

Regression changed from No to Yes

Actions

Copy link

Updated by François ARMAND about 2 months ago

If the known connection doesn't have the rights to create tables, then we can't do much from Rudder.
We might forbid rudder to start, but I'm not sure it's better: at least if it starts, nodes & repports can work until the user see how to correct the right problem.

I don't think it's a regression: we never supported the case where we can't create table for migration.

Actions

Copy link