Unable to import certificat x509 module

Icinga Web
1

Hello
I just want to add icinga x509 to my setup but I’m stuck when i try to import certificat into database
fyi, on the release on github Release Icinga Certificate Monitoring v1.1.2 · Icinga/icingaweb2-module-x509 · GitHub We don’t have the postgresql schema include
We need to clone the repo to get it
So I follow the guide for postgresql, I use debian 11 and postgresql 13

 psql -l
                                  Liste des bases de données
    Nom     | Propriétaire | Encodage | Collationnement | Type caract. |    Droits d'accès     
------------+--------------+----------+-----------------+--------------+-----------------------
...

 x509       | x509         | UTF8     | fr_FR.UTF-8     | fr_FR.UTF-8  | 


/usr/share/icingaweb2/modules/x509$ sudo icingacli x509 import --file /etc/ssl/certs/ca-certificates.crt
ERROR: PDOException in /usr/share/icinga-php/ipl/vendor/ipl/sql/src/Connection.php:402 with message: SQLSTATE[22021]: Character not in repertoire: 7 ERREUR:  séquence d'octets invalide pour l'encodage « UTF8 » : 0x9a

I was thinking it’s because I use fr_FR.UTF-8 so I try to generate En_US UTF local and dropdb/user and recreate it but no luck

So I generate local

/usr/share/icingaweb2/modules/x509$ sudo locale-gen
Generating locales (this might take a while)...
  en_US.UTF-8... done
  fr_FR.UTF-8... done
Generation complete.

postgres=# CREATE DATABASE x509                                            
  WITH OWNER x509
  ENCODING 'UTF8'
  LC_COLLATE = 'en_US.UTF-8'
  LC_CTYPE = 'en_US.UTF-8';
ERREUR:  nom de locale invalide : « en_US.UTF-8 »

I use php7.4 and php-gmp / icinga-php* is installed

/usr/share/icingaweb2/modules/x509$ dpkg -l php-gmp
ii  php-gmp        2:7.4+76     all          GMP module for PHP [default]

/usr/share/icingaweb2/modules/x509$ php -v
PHP 7.4.30 (cli) (built: Jul  7 2022 15:51:43) ( NTS )
Copyright (c) The PHP Group
Zend Engine v3.4.0, Copyright (c) Zend Technologies
    with Zend OPcache v7.4.30, Copyright (c), by Zend Technologies

/usr/share/icingaweb2/modules/x509$ dpkg -l "icinga-php*"

ii  icinga-php-common     1.0.0-1.bullseye  all          Icinga PHP Common for Icinga Web 2
ii  icinga-php-library    0.10.0-1+debian11 amd64        Icinga PHP Library - IPL
ii  icinga-php-thirdparty 0.11.0-1.bullseye amd64        Icinga PHP Thirdparty for Icinga Web 2

If someone got any idea ? I don’t want to move on mysql …

2

Hi @jin,

You also need to restart the Postgres service after generating the locales. systemctl restart postgresql

Regards,
Yonas

3

yeah but no luck my template are in fr_FR-UTF8

postgres=# CREATE DATABASE x509                                            
  WITH OWNER x509
  ENCODING 'UTF8'
  LC_COLLATE = 'en_US.UTF-8'
  LC_CTYPE = 'en_US.UTF-8';
ERREUR:  le nouveau tri (en_US.UTF-8) est incompatible avec le tri de la base de
données modèle (fr_FR.UTF-8)

psql -l
...
 postgres   | postgres     | UTF8     | fr_FR.UTF-8     | fr_FR.UTF-8  | 
 template0  | postgres     | UTF8     | fr_FR.UTF-8     | fr_FR.UTF-8  | =c/postgres          +
            |              |          |                 |              | postgres=CTc/postgres
 template1  | postgres     | UTF8     | fr_FR.UTF-8     | fr_FR.UTF-8  | =c/postgres          +
            |              |          |                 |              | postgres=CTc/postgres
(5 lignes)
4

From the PostgreSQL docs:

Another common reason for copying template0 instead of template1 is that new encoding and locale settings can be specified when copying template0, whereas a copy of template1 must use the same settings it does. This is because template1 might contain encoding-specific or locale-specific data, while template0 is known not to.

So to create the database in a different locale, you have to add TEMPLATE template0 to the CREATE DATABASE command.

5

Thanks you that’s help me :+1:
But no luck still have error about UTF8

~$ sudo icingacli x509 import --file /etc/ssl/certs/ca-certificates.crt
ERROR: PDOException in /usr/share/icinga-php/ipl/vendor/ipl/sql/src/Connection.php:402 with message: SQLSTATE[22021]: Character not in repertoire: 7 ERREUR:  séquence d'octets invalide pour l'encodage « UTF8 » : 0x9a

psql -l
....
 x509       | x509         | UTF8     | en_US.UTF-8     | en_US.UTF-8  |