Problem
When executing the command univention-pkgdb-scan you get the following Traceback:
root@ucsdc0:/home/univention-support#
univention-pkgdb-scan
Traceback (most recent call last):
File "/usr/sbin/univention-pkgdb-scan", line 41, in <module>
univention.pkgdb.main()
File "/usr/lib/python3/dist-packages/univention/pkgdb.py", line 589, in main
connection = open_database_connection(config_registry, pkgdbu=False)
File "/usr/lib/python3/dist-packages/univention/pkgdb.py", line 571, in open_database_connection
connection = pgdb.connect(database=connectstring)
File "/usr/lib/python3/dist-packages/pgdb.py", line 1619, in connect
cnx = _connect(dbname, dbhost, dbport, dbopt, dbuser, dbpasswd)
pg.InternalError: FATAL: missing pg_hba.conf-Entry for Host »10.123.123.1«, User »ucsdc0$«, Database »pkgdb«, SSL on
Hint
In this scenario it is important to mention that the package for univention-pkdg, i.e. the pkgdb server itself, is not located on the primary node / DC master (ucsdc0).
This plays an important role later in the solution, because the host name and domain name must match the pkgdb server so that the pkgdb can also be accessed.
Environment
root@ucs-pkgdb:/home/univention-support# univention-app info
UCS: 5.0-8 errataXXX
Installed: pkgdb=11.0
root@ucs-pkgdb:/home/univention-support# dpkg -l | grep pkgdb
ii python3-univention-pkgdb 13.0.5-2 all UCS - Software monitoring database Python 3 library
ii univention-management-console-module-pkgdb 13.0.5-2 all UMC module for software monitoring database
ii univention-pkgdb 13.0.5-2 all UCS - Software monitoring database
ii univention-pkgdb-tools 13.0.5-2 all UCS - Software monitoring database tools
root@ucsdc0:/home/univention-support# dpkg -l | grep pkgdb
ii python3-univention-pkgdb 13.0.5-2 all UCS - Software monitoring database Python 3 library
ii univention-pkgdb-tools 13.0.5-2 all UCS - Software monitoring database tools
1st. Solution
Because the entry for ucsdc0 is not present on the pkgdb Server in /etc/postgresql/11/main/pg_hba.conf, it is not allowed to access the pkgdb.
The user can be added via UCR and the error resolved as follows.
postgres11/pg_hba/config/.*: <empty>
An additional variable is created for each user entry in which the counter is defined manually. (01,02,03 etc.)
1.
root@ucs-pkgdb:/home/univention-support#
ucr set postgres11/pg_hba/config/01='hostssl pkgdb ucsdc0$ 10.123.123.1/32 md5'
Create postgres11/pg_hba/config/01
Multifile: /etc/postgresql/11/main/pg_hba.conf
Further information for the pg_hba.conf you will find under the following link.
2.
After you create the variable successfully, you have to restart the service.
root@ucs-pkgdb:/home/univention-support#
systemctl restart postgresql.service
2nd. Solution
Because of missing group membership in the pkgdb database, the user could not use the pkgdb.
root@dc0:~# su postgres
postgres@dc0:/root$ psql
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------------+------------------------------------------------------------+-----------
admin-66485341$ | | {}
dc0$ | | {}
dejneschool2$ | | {}
hejneschool2$ | | {}
importhttpapi | | {}
jitsi-28660594$ | | {}
mejneschool2$ | | {}
openi-91689551$ | | {}
ox-co-35918462$ | | {}
ox-co-46689444$ | | {}
pkgdbg | Cannot login | {}
pkgdbu | Superuser, Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
prome-35807772$ | | {}
ucs-test$ | | {}
ucs5back$ | | {}
ucs5mail$ | | {}
ucs5repl$ | | {}
You can add the users to the missing group pkgdbg where the database pkgdb is in use:
root@dc0:~# univention-directory-listener-ctrl resync pkgdb
listener shutdown done
root@dc0:~# su postgres
postgres@dc0:/root$ psql
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------------+------------------------------------------------------------+-----------
mejneschool2$ | | {pkgdbg}
admin-66485341$ | | {pkgdbg}
dc0$ | | {pkgdbg}
dejneschool2$ | | {pkgdbg}
hejneschool2$ | | {pkgdbg}
importhttpapi | | {}
jitsi-28660594$ | | {pkgdbg}
openi-91689551$ | | {pkgdbg}
ox-co-35918462$ | | {pkgdbg}
ox-co-46689444$ | | {pkgdbg}
pkgdbg | Cannot login | {}
pkgdbu | Superuser, Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
prome-35807772$ | | {pkgdbg}
ucs-test$ | | {pkgdbg}
ucs5back$ | | {pkgdbg}
ucs5mail$ | | {pkgdbg}
ucs5repl$ | | {pkgdbg}
root@mejneschool2:~# univention-pkgdb-scan
Reading package lists... Done
Building dependency tree
Reading state information... Done
To add user to the DATABASE pkgdb, check the following article.
To recreate the DATABASE pkgdb, check the following article.
PostgreSQL Cheat Sheet