The Postgresql DB does not exist

[iamjustfiction]

I have tried to reinstall this because I have problems with it every time I try to run gvm-start
this pops up

“See “systemctl status gvmd.service” and “journalctl -xeu gvmd.service” for details.”

when I tried to reinstall Greenbone, this pops up

WARNING: database “postgres” has a collation version mismatch
DETAIL: The database was created using collation version 2.36, but the operating system provides version 2.37.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE postgres REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

•    ERROR: The Postgresql DB does not exist.*
  

•    FIX: Run 'sudo runuser -u postgres -- /usr/share/gvm/create-postgresql-database'*
  

• ERROR: Your GVM-22.4.1 installation is not yet complete!*

Please follow the instructions marked with FIX above and run this
script again.

I tried the fix above, this pops up

could not change directory to “/home/kali”: Permission denied
WARNING: database “postgres” has a collation version mismatch
DETAIL: The database was created using collation version 2.36, but the operating system provides version 2.37.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE postgres REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
[i] User _gvm already exists in PostgreSQL
could not change directory to “/home/kali”: Permission denied
WARNING: database “postgres” has a collation version mismatch
DETAIL: The database was created using collation version 2.36, but the operating system provides version 2.37.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE postgres REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

[] Creating database*
could not change directory to “/home/kali”: Permission denied
WARNING: database “postgres” has a collation version mismatch
DETAIL: The database was created using collation version 2.36, but the operating system provides version 2.37.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE postgres REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
createdb: error: database creation failed: ERROR: template database “template1” has a collation version mismatch
DETAIL: The template database was created using collation version 2.36, but the operating system provides version 2.37.
HINT: Rebuild all objects in the template database that use the default collation and run ALTER DATABASE template1 REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
could not change directory to “/home/kali”: Permission denied
WARNING: database “postgres” has a collation version mismatch
DETAIL: The database was created using collation version 2.36, but the operating system provides version 2.37.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE postgres REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

[] Creating permissions*
could not change directory to “/home/kali”: Permission denied
psql: error: connection to server on socket “/var/run/postgresql/.s.PGSQL.5432” failed: FATAL: database “gvmd” does not exist

[] Applying permissions*
could not change directory to “/home/kali”: Permission denied
psql: error: connection to server on socket “/var/run/postgresql/.s.PGSQL.5432” failed: FATAL: database “gvmd” does not exist
could not change directory to “/home/kali”: Permission denied
psql: error: connection to server on socket “/var/run/postgresql/.s.PGSQL.5432” failed: FATAL: database “gvmd” does not exist

[] Creating extension uuid-ossp*
could not change directory to “/home/kali”: Permission denied
psql: error: connection to server on socket “/var/run/postgresql/.s.PGSQL.5432” failed: FATAL: database “gvmd” does not exist
could not change directory to “/home/kali”: Permission denied
psql: error: connection to server on socket “/var/run/postgresql/.s.PGSQL.5432” failed: FATAL: database “gvmd” does not exist

[] Creating extension pgcrypto*
could not change directory to “/home/kali”: Permission denied
psql: error: connection to server on socket “/var/run/postgresql/.s.PGSQL.5432” failed: FATAL: database “gvmd” does not exist
could not change directory to “/home/kali”: Permission denied
psql: error: connection to server on socket “/var/run/postgresql/.s.PGSQL.5432” failed: FATAL: database “gvmd” does not exist

[] Creating extension pg-gvm*
could not change directory to “/home/kali”: Permission denied
psql: error: connection to server on socket “/var/run/postgresql/.s.PGSQL.5432” failed: FATAL: database “gvmd” does not exist

I also tried solving the postgres problem with this code

sudo -u postgres psql

this is the result

could not change directory to “/home/kali”: Permission denied
WARNING: database “postgres” has a collation version mismatch
DETAIL: The database was created using collation version 2.36, but the operating system provides version 2.37.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE postgres REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
psql (15.3 (Debian 15.3-0+deb12u1))
Type “help” for help.

How can I solve this?
Can I just remove Kali completely on my pc and then start anew?

[rippledj]

I believe this is an error caused when upgrading (apt upgrade) Kali packages which cause a version conflict with PostgreSQL. This prevents GVMD from accessing PostgreSQL, but I believe this would prevent all applications from accessing PostgreSQL. Sorry, I didn’t have time to look into this error too deeply.

Unless you absolutely need to keep this installation of Kali, I would completely wipe Kali and reinstall it. If you do an apt upgrade after the installation and this issue persist, this problem is caused by Kali, but you can feel free to let us know here as well.
Also, if this bug impacts all functionality of postgres regardless of Greenbone you can report a bug to the Kali bugs forum.

Furthermore, the warnings:

could not change directory to “/home/kali”: Permission denied

Are just because PostgreSQL has is being instantiated from a directory it doesn’t have permissions to access, which doesn’t impact the commands that are run. Greenbone’s source code install instructions have been modified to remove this error since it’s easy to fix and just tends to confuscate the other error messages.

[iamjustfiction]

Thank you for this, I will try to wipe and reinstall kali and I’m just going to avoid doing apt upgrades for now. I’ll let you know if the error persists. Thank you again!

[rippledj]

You could also try to fix the install if you want with something like:

sudo apt install <package-name>=<version>

or

apt-get -s install $(apt-history rollback | tr '\n' ' ')

You can see here or read the manual for apt.

Depends on your experience level and your appetite for learning the correct downgrade process.

[iamjustfiction]

I have fixed the problem by running the code:
ALTER DATABASE postgres REFRESH COLLATION VERSION;
then kept on running the
sudo gvm-check-setup
and following the FIX every time an error occured
E.G.
FIX: create a user by running ‘sudo runuser -u _gvm – gvmd --create-user= --password=’

after that it seems the installation is now okay.

Still thanking you for answering because your answers opened doors for me to research on other topics that lead me to my solutions.

[rippledj]

Aha, thanks for posting your solution!
I honestly didn’t even see the fix the first time I read it

My answer to just wipe Kali isn’t a great one, but sometimes it’s just important to get your pentest/system running. I tend to think of Kali installations as disposable rather than long-term.

Maybe the gvm .deb installer should halt installation and exit with a clear error to avoid the extensive error and warning output and make it more clear.

Still, I should have seen the giant FIX: section in your post!

[KeithClark]

Ive run into same issue and although the above persons fix gets me past the collation error I still have another which will always come up regardless of how many times i run it and re launch gvm-setup. It appears as although I am root the recomended FIX does not do anything.
No LSB modules are available.
Distributor ID: Kali
Description: Kali GNU/Linux Rolling
Release: 2023.2
Codename: kali-rolling

sudo runuser -u postgres -- /usr/share/gvm/create-postgresql-database

──(root㉿kali)-[~]
└─# runuser -u postgres -- /usr/share/gvm/create-postgresql-database 
could not change directory to "/root": Permission denied
[i] User _gvm already exists in PostgreSQL
could not change directory to "/root": Permission denied

[*] Creating database
could not change directory to "/root": Permission denied
createdb: error: database creation failed: ERROR:  template database "template1" has a collation version mismatch
DETAIL:  The template database was created using collation version 2.36, but the operating system provides version 2.37.
HINT:  Rebuild all objects in the template database that use the default collation and run ALTER DATABASE template1 REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
could not change directory to "/root": Permission denied

[*] Creating permissions
could not change directory to "/root": Permission denied
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  database "gvmd" does not exist

[*] Applying permissions
could not change directory to "/root": Permission denied
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  database "gvmd" does not exist
could not change directory to "/root": Permission denied
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  database "gvmd" does not exist

[*] Creating extension uuid-ossp
could not change directory to "/root": Permission denied
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  database "gvmd" does not exist
could not change directory to "/root": Permission denied
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  database "gvmd" does not exist

[*] Creating extension pgcrypto
could not change directory to "/root": Permission denied
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  database "gvmd" does not exist
could not change directory to "/root": Permission denied
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  database "gvmd" does not exist

[*] Creating extension pg-gvm
could not change directory to "/root": Permission denied
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  database "gvmd" does not exist
``

[rippledj]

This issue has perplexed me. I only encountered it once when installing gvm on using the kali-experimental repository and even then only when doing a apt upgrade in addition to the apt update.

I spent some time today trying to reproduce it in various ways, but seems I can’t.

As for your question, I guess by the FIX doesn’t do anything you mean the HINT command:

ALTER DATABASE template1 REFRESH COLLATION VERSION

Here is the message I received from a Kali maintainer :

About the POSTGRESQL mismatch, I’m not sure what is the problem, but I can explain how postgresql works in Kali/debian:
in Debian, the first time postrgresql is installed it creates a cluster using the port 5432. When a new version is installed, it creates a new cluster using the port 5433…
The migration to the new version is not automatic. The admin must do it manually.
gvmd (and pg-gvm) are not just “using” postgresql, they are built against the default postgresql library in Kali-dev when we upload the package (now it’s postresql 15). So we absolutely need to use postgresql 15 when we run GVM tools.
That’s why we need to inform the user/admin about the mismatched versions. They must migrate their cluster to the latest version so that the latest postgresql lib / cluster will use the port 5432 and gvmd will run correctly.
I don’t think we can automate anything here. We really don’t want to “force” the cluster upgrade and to risk breaking other databases.

So, from what I understand (acknowledged limitation), the problem may be that you have multiple versions of PGSQL installed and as far as I know this only happens when you do the sudo apt upgrade. This issue only happened once for me when using the Kali experimental branch.

You can verify if you have multiple clusters with the following command:

pg_lsclusters

You should see:

┌──(root㉿kali)-[~]
└─# pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
15  main    5432 down   postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log

If that shows you have multiple installed you should select the newer one so that it matches the OS collation version:

sudo update-alternatives --config PostgreSQL

[cfi]

Please be also aware that the Kali packages and tools like gvm-setup or gvm-check-setup shipped within them are not provided / maintained by Greenbone.

If any problems arises from the usage of the packages, especially after an update of either the packages or the distro it is suggested to raise an issue over at:

https://bugs.kali.org

[KeithClark]

I went ahead and installed Kali 2023.2 on a completely different system then ran apt install ovenvas and sudo gvm-setup

Once completed I ran gvm-check-setup which found nothing wrong with the setup.

I then went ahead and ran apt upgrade and when running gvm-check-setup it now gives the the collation error but states the installation is ok

Question: What is the collation and will it effect anything leaving it as is at 2.36? If I do need to point gvm to use 2.37 how does one even go about doing this???

ARNING: database “gvmd” has a collation version mismatch
DETAIL: The database was created using collation version 2.36, but the operating system provides version 2.37.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE gvmd REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
WARNING: database “gvmd” has a collation version mismatch
DETAIL: The database was created using collation version 2.36, but the operating system provides version 2.37.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE gvmd REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

[KeithClark]

@KeithClark

HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE gvmd REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

Nevermind I got it

sudo -u postgres psql

List all databases to ensure your database exists

\l

Alter gvmd & postgres database to refresh Collation version

ALTER DATABASE gvmd REFRESH COLLATION VERSION;
ALTER DATABASE postgres REFRESH COLLATION VERSION;

Exit postgres

\q or exit

Restart GSM services

systemctl daemon-reload && systemctl restart gvmd.service gsad.service

Check if warnings are fixed

gvm-check-setup

[rippledj]

I thought you had tried the suggested FIX already since you said:

the recomended FIX does not do anything

The gvm-check-setup will usually halt after the first problem is encountered, so you need to keep running it again and doing the next fix.

Collation defines how string data (character data) is encoded and sorted and compared within the databases. It defines how character encodings such as UFT-8, or Latin-1 (aka ISO 8859-1) are implemented. So, I guess that using different collation versions will potentially cause the same type of problems that using the wrong encoding would.

If you try to open an ISO 8859-1 encoded file with UTF-8, your higher level programs will identify the problem and notify you or else display the unprintable character icon �∞�∆˚¶§§•º�∂ø���� but otherwise, without the warning about the collation mismatch, there would be database bugs depending on the difference between the collation versions.

[cfi]

One additional important note for this:

@KeithClark

WARNING: database “gvmd” has a collation version mismatch
DETAIL: The database was created using collation version 2.36, but the operating system provides version 2.37.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE gvmd REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

The message is originating from PostgreSQL and it is not guaranteed that a community (like this) of a software using just PosgreSQL can give exact / detailed guidance for such topics.

I have included some (to the best of my knowledge) background info in the posting below but if in doubt please contact the PostgreSQL support community for questions about their product.

The database was created using collation version 2.35, but the operating system provides version 2.36

Some more (background) info about this / the reasons for this message from PostgreSQL / operating system (Debian) side is available here: https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=1029548#10 The cause for that (IIUC) is that the database was created when libc6 was at version 2.35 and it has since been upgraded to 2.36. See also https://bugs.debian.org/1021074 snip A (major) libc6 upgrade is not something that will happen on Stable, so this issue may only occur with people running T…

[jsmacedo]

I was facing the same problem and running ALTER DATABASE template1 REFRESH COLLATION VERSION; (instead of postgres, I used template1 in the command) saved my life!
After, I also type: sudo gvm-check-setup
And like a charm, I was finally able to create my gvm user and run the scanner against the targets in the network.

[gquint]

Hello all,

Sorry to resurrect an old dead post, but i have been working on a similar or the same issue for most of the day and wanted to post my resolution in the hope it helps someone else save themselves some time.

As others have noted this seems to be caused by an update that upgrades the PostgreSQL version in my case from 15 to 16. The proposed fix here did not resolve my issue but it did point me in the direction of the fix.

As others have said some progress can be made by changing the port in the /etc/PostgreSQL/16/Main/PostgreSQL.Conf to 5432 and any other version you need to a different port number but then you will have the setup fail at the database creation.

First you need to run:
Sudo pg_lsclusters

This will list the clusters you have, in my case i had 15 Main and 16 Main this was my problem.

I then used the commands
Sudo service postgresql stop
Sudo -u postgres pg_dropcluster --stop 16 main
Sudo -u postgres pg_upgradecluster 15 main
Sudo -u postgres pg_dropcluster --stop 15 main

The first command will stop the postgreSQL database, next will remove the version 16 cluster, then pg_upgradecluster will take the version 15 and update it for 16 and then the last one is optional and will get rid of the version 15 all together. after this go back and check the port in /etc/postgresql/16/main is 5432.

Alternatively you could use the pg_dropcluster and remove both clusters and then use the pg_createcluster especially if you still have errors with the upgraded database from 15 -16.

I hope this helps someone save some searching