Editing GitLab V18 Upgrade

Once again, the GitLab team has introduced a breaking change in the upgrade from v17 to v18.  The PostgreSQL database version in v18+ is 16 (instead of 14 in previous versions).  The upgrade of the database must be done in multiple steps: 
* running their database_upgrade script before (to capture the state of the deployment)
* deleting the database PVCs
* performing the upgrade (without the migrations step)
* running their database_upgrade script again to restore the database and restarting the services.

This is all well and good -- it's been required before ... but the postgresql user in the new configuration doesn't have all the needed permissions to do the database restore.  Searching for the error message in google gemini results in the following response (which solved the problem).

----

The "permission denied for function pg_ls_waldir" error during a GitLab upgrade (especially with Helm charts) to a newer version of GitLab or PostgreSQL usually indicates that the database user GitLab is using **lacks the necessary privileges** to execute this specific PostgreSQL function.

The function `pg_ls_waldir()` (or `pg_ls_dir()` in older PostgreSQL versions) is a function that allows listing the contents of the PostgreSQL Write-Ahead Log (WAL) directory. This is often used for monitoring, replication, or certain administrative tasks.

Here's why this happens and how to resolve it in a Helm-based GitLab deployment:

==Why it happens:==

* **Security hardening:** Newer PostgreSQL versions or stricter configurations of GitLab's chart might restrict access to sensitive functions like `pg_ls_waldir()` to only superusers or specific roles (like `pg_monitor`).
* **Non-superuser GitLab user:** GitLab typically runs with a non-superuser database user for security reasons. This user might not have the permissions by default.
* **Monitoring tools:** Sometimes, this error isn't from GitLab's core migrations but from a monitoring tool (like `postgres_exporter` for Prometheus) trying to gather WAL-related metrics. If the GitLab upgrade involves an upgrade of the bundled PostgreSQL or a change in how permissions are managed, this can manifest.

==How to resolve it (with Helm):==

The solution involves granting the necessary permissions to the database user that GitLab uses. Since you're using Helm, you'll generally achieve this by either:

1.  **Running a `psql` command manually (temporary/quick fix):** This is useful for immediate unblocking, but might not persist across pod restarts if your database setup doesn't automatically apply grants.
2.  **Modifying your Helm `values.yaml` to apply grants during initialization (recommended for persistence).**

==Method 1: Manual Grant (via `kubectl exec` and `psql`)==

This method is good for immediate troubleshooting.

1.  **Find your PostgreSQL pod:**
If GitLab is managing its own PostgreSQL (the default for the Helm chart), find the PostgreSQL primary pod:

 kubectl get pods -l app=postgresql,release=<your-gitlab-release-name>,statefulset=postgresql-ha-postgresql -n <your-gitlab-namespace>

If you're using an external PostgreSQL, you'll need to connect to it directly using a PostgreSQL client.

2.  **Exec into the PostgreSQL pod (or connect externally):**
 kubectl exec -it <postgresql-primary-pod-name> -n <your-gitlab-namespace> -- bash

(Once inside, you might need to become the `postgres` user: `su - postgres`)

3.  **Connect to the GitLab database with `psql`:**
 psql -d gitlabhq_production -U postgres # Or your PostgreSQL superuser

(Replace `gitlabhq_production` if your database name is different, and `postgres` with your superuser if it's not the default.)

4.  **Grant the permission:**
Identify the GitLab database user (it's often `gitlab` or `gitlab-rails` if you're using the default Helm chart). You can check it in your `values.yaml` or in the `webservice` pod's environment variables.
 GRANT EXECUTE ON FUNCTION pg_catalog.pg_ls_waldir() TO <gitlab_database_user>;

For example, if your GitLab user is `gitlab`:
 GRANT EXECUTE ON FUNCTION pg_catalog.pg_ls_waldir() TO gitlab;

You might also consider granting the `pg_monitor` role to the GitLab user if that's appropriate for your security model and the version of PostgreSQL:
 GRANT pg_monitor TO <gitlab_database_user>;

'''Note:''' The `pg_monitor` role provides access to several monitoring-related functions, including `pg_ls_waldir()`, so this can be a more comprehensive solution.

5.  **Exit `psql` and the pod:**
 \q
 exit

6.  **Retry the GitLab upgrade:**
 helm upgrade <your-gitlab-release-name> gitlab/gitlab -f your_values.yaml --version <new-gitlab-version>

==Method 2: Persistent Grant (via Helm `values.yaml`)==

This is the preferred method for Helm deployments as it ensures the grant is applied consistently.

You can inject an initialization script into the PostgreSQL container that grants the necessary permissions. This typically involves using the `postgresql.initdb` configuration in your `values.yaml`.

===Example `values.yaml` addition:===


 # Add this to your values.yaml file
 postgresql:
   # This section assumes you are using the bundled PostgreSQL with the GitLab chart.
   # If using an external PostgreSQL, you would need to run this command on your external DB.
   initdb:
     ## @param initdb.scripts Allows mounting scripts into /docker-entrypoint-initdb.d/ to be executed at first boot of the Postgres container
     ## This can be used for custom grants or other database initialization.
     scripts:
       grant-wal-permissions.sql: |
         DO
         $do$
         BEGIN
           IF EXISTS (SELECT 1 FROM pg_catalog.pg_proc p JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.oid WHERE p.proname = 'pg_ls_waldir' AND n.nspname = 'pg_catalog') THEN
             GRANT EXECUTE ON FUNCTION pg_catalog.pg_ls_waldir() TO gitlab; -- Replace 'gitlab' with your actual GitLab database user if different
           END IF;
           -- Also consider granting pg_monitor role if appropriate for your security model
           IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'pg_monitor') THEN
             GRANT pg_monitor TO gitlab; -- Replace 'gitlab' with your actual GitLab database user if different
           END IF;
         END
         $do$;


===Explanation of the `initdb.scripts` addition:===

* `initdb.scripts`: This section allows you to define custom SQL scripts that will be run when the PostgreSQL container is first initialized.
* `grant-wal-permissions.sql`: This is a descriptive name for your script.
* `DO $$BEGIN ... END$$;`: This is an anonymous PL/pgSQL block, which allows for conditional logic (like checking if the function exists before trying to grant).
* `IF EXISTS (...) THEN ... END IF;`: This checks if `pg_ls_waldir` function exists (useful for PostgreSQL version compatibility) and if the `pg_monitor` role exists before attempting the `GRANT`.
* `TO gitlab;`: **Crucially, replace `gitlab` with the actual database user GitLab uses.** This is usually configured in your GitLab Helm chart's `global.psql.username` or `global.postgresql.username` setting, or if you're using the bundled PostgreSQL, it's often `gitlab`.

===After modifying `values.yaml`:===

Run the `helm upgrade` command again:

 helm upgrade <your-gitlab-release-name> gitlab/gitlab -f your_values.yaml --version <new-gitlab-version>

==Important Notes:==

* **Database Restart:** For the `initdb.scripts` method, the script runs only on *initialization* of the PostgreSQL container. If your PostgreSQL pods are already running, you might need to force a recreation of the PostgreSQL primary pod for the `initdb` script to execute. Be careful doing this in a production environment as it will cause a brief database downtime. A common way to force recreation after an `initdb` change is to taint the old pods or delete them gracefully, allowing Kubernetes to reschedule new ones.
* **GitLab Database User:** Double-check the exact database user name GitLab is using. This is critical for granting permissions to the correct user.
* **PostgreSQL Version:** The `pg_ls_waldir()` function is present in PostgreSQL 10 and newer. If you are on a very old PostgreSQL version, the function might be `pg_ls_dir()`, but GitLab 18 definitely requires a modern PostgreSQL.
* **Backups:** Always ensure you have a complete and tested backup of your GitLab instance and its database before performing any major upgrade or making database permission changes.

By applying the correct `GRANT` statement to the appropriate GitLab database user, you should resolve the "permission denied for function pg_ls_waldir" error and allow your GitLab upgrade to proceed.