POstgres 15 image does not work with Gitlab backup:restore task - "permission denied for schema public"

[danobot]

Due to this change in user permission in postgres 15: https://stackoverflow.com/questions/74110708/postgres-15-permission-denied-for-schema-public/74111630#comment130849690_74110708

I tried running the db:setup rake task prior to running the backup script. During backup restore, all SQL statements fail due to permission error.

I am upgrading from 15.11.5 to 16.0.0. Postgres 12 to 15 (using the sameersbn/postgres images).

[elchenberg]

I might have the same issue.

In my case I created a new database for SonarQube:

      containers:
        - name: postgres
          image: docker.io/sameersbn/postgresql:15@sha256:f5f9f8ad3b873d49a44e21c3f17231da3f92db7f0f73119215b7c771faf53e13
          env:
            - name: DB_NAME
              value: sonar
            - name: DB_PASS
              value: sonar
            - name: DB_USER
              value: sonar

I got permission denied for schema public in SonarQube, too.

As a workaround I used an initContainer (on the SonarQube pod, after the PostgreSQL database is already up and running):

        - name: create-schema
          image: docker.io/sameersbn/postgresql:15@sha256:f5f9f8ad3b873d49a44e21c3f17231da3f92db7f0f73119215b7c771faf53e13
          env:
            - name: PGPASSWORD
              value: sonar
          command:
            - psql
          args:
            - --dbname=sonar
            - --command=CREATE SCHEMA IF NOT EXISTS AUTHORIZATION sonar;
            - --host=postgres
            - --no-password
            - --username=sonar

I am wondering if something like CREATE SCHEMA IF NOT EXISTS AUTHORIZATION sonar; could be added to the create_database function (but my PostgreSQL knowledge is very limited).

Version: docker.io/sameersbn/postgresql:15@sha256:f5f9f8ad3b873d49a44e21c3f17231da3f92db7f0f73119215b7c771faf53e13

PS. I still have to double-check if my workaround actually works.