Project

General

Profile

Actions
Copy link

Bug #24460

open

usersessions table creation may fail on postgresql 15 and more

Added by Nicolas CHARLES 2 months ago. Updated 20 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:
0
Name check:
To do
Fix check:
To do
Regression:
No

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

Also available in: Atom PDF