Patroni PostgresSQL-klusteri TLS ongelma

SamCer · eilen klo 19:38

Yritän pystyttää Patronin avulla PostgresSQL-klusteria ja sen jo sainkin toimimaan, mutta nyt kun yritän ottaa TLS:sää käyttöön liikenteen salaukseen ja serverin/clientin autentikointiin, niin se tuottaa ongelmia.

Alkuinfoa:

pgs1 = 192.168.88.25
pgs2 = 192.168.88.26
pgs3 = 192.168.88.27
PostgresSQL versio = 18.0
Patroni versio = 4.0.6
Patronin DCS = Consul
Consul versio = v1.21.5

Patronin logitus valittaa ssl.SSLError: [SSL: SSLV3_ALERT_BAD_CERTIFICATE] sslv3 alert bad certificate (_ssl.c:1000). Alla logi kokonaisuudessaan aina virheeseen asti:

Rich (BB code):

Sep 25 19:12:54 pgs1.omadomain.fi systemd[1]: Started patroni.service - Runners to orchestrate a high-availability PostgreSQL.
░░ Subject: A start job for unit patroni.service has finished successfully
░░ Defined-By: systemd
░░ Support: http://www.ubuntu.com/support
░░
░░ A start job for unit patroni.service has finished successfully.
░░
░░ The job identifier is 150895.
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:54,483 WARNING: ttl=2 can't be smaller than 20, adjusting...
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:54,483 INFO: No PostgreSQL configuration items changed, nothing to reload.
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:54,499 INFO: Deregister service postgresql-cluster/pgs1
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:54,513 INFO: Lock owner: None; I am pgs1
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:54,524 INFO: Deregister service postgresql-cluster/pgs1
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:54,535 INFO: trying to bootstrap a new cluster
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:54,536 INFO: Lock owner: None; I am pgs1
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:54,536 INFO: not healthy enough for leader race
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:54,537 WARNING: Could not register service: unknown role type uninitialized
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: The files belonging to this database system will be owned by user "postgres".
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: This user must also own the server process.
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: The database cluster will be initialized with locale "en_US.UTF-8".
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: The default text search configuration will be set to "english".
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: Data page checksums are enabled.
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: fixing permissions on existing directory /var/lib/postgresql/data ... ok
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: creating subdirectories ... ok
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: selecting dynamic shared memory implementation ... posix
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: selecting default "max_connections" ...
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:54,553 INFO: bootstrap in progress
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: 100
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: selecting default "shared_buffers" ... 128MB
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: selecting default time zone ... Europe/Helsinki
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: creating configuration files ... ok
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: running bootstrap script ... ok
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: performing post-bootstrap initialization ... ok
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181762]: syncing data to disk ... ok
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181762]: initdb: warning: enabling "trust" authentication for local connections
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181762]: initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181762]: Success. You can now start the database server using:
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181762]:     /usr/lib/postgresql/18/bin/pg_ctl -D /var/lib/postgresql/data -l logfile start
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181779]: 2025-09-25 19:12:55.333 EEST [181779] LOG:  starting PostgreSQL 18.0 (Ubuntu 18.0-1.pgdg24.04+3) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181779]: 2025-09-25 19:12:55.333 EEST [181779] LOG:  listening on IPv4 address "0.0.0.0", port 5432
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:55,336 INFO: postmaster pid=181779
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181779]: 2025-09-25 19:12:55.340 EEST [181779] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181786]: 2025-09-25 19:12:55.355 EEST [181786] LOG:  database system was shut down at 2025-09-25 19:12:54 EEST
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181787]: 2025-09-25 19:12:55.360 EEST [181787] FATAL:  the database system is starting up
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181780]: localhost:5432 - rejecting connections
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181789]: 2025-09-25 19:12:55.370 EEST [181789] FATAL:  the database system is starting up
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181788]: localhost:5432 - rejecting connections
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181779]: 2025-09-25 19:12:55.382 EEST [181779] LOG:  database system is ready to accept connections
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:55,537 INFO: Lock owner: None; I am pgs1
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:55,537 INFO: not healthy enough for leader race
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:55,548 INFO: bootstrap in progress
Sep 25 19:12:56 pgs1.omadomain.fi patroni[181793]: localhost:5432 - accepting connections
Sep 25 19:12:56 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:56,385 INFO: establishing a new patroni heartbeat connection to postgres
Sep 25 19:12:56 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:56,402 INFO: Register service postgresql-cluster, params {'service_id': 'postgresql-cluster/pgs1', 'address': '192.168.88.25', 'port': 5432, 'check': {'http': 'https://192.168.88.25:8008/primary', 'interval': '5s', 'DeregisterCriticalServiceAfter': '100.0s'}, 'tags': ['traefik.enable=true', 'traefik.tcp.routers.pgsql.entrypoints=postgres', 'traefik.tcp.routers.pgsql.rule=HostSNI(`*`)', 'traefik.tcp.services.pgsql.loadbalancer.server.port=5432', 'primary', 'master'], 'enable_tag_override': True}
Sep 25 19:12:56 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:56,443 INFO: running post_bootstrap
Sep 25 19:12:56 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:56,466 ERROR: User creation is not be supported starting from v4.0.0. Please use "bootstrap.post_bootstrap" script to create users.
Sep 25 19:12:56 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:56,467 WARNING: Could not activate Linux watchdog device: Can't open watchdog device: [Errno 2] No such file or directory: '/dev/watchdog'
Sep 25 19:12:56 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:56,510 INFO: initialized a new cluster
Sep 25 19:12:57 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:57,563 INFO: no action. I am (pgs1), the leader with the lock
Sep 25 19:12:58 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:58,481 INFO: no action. I am (pgs1), the leader with the lock
Sep 25 19:12:59 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:59,469 INFO: no action. I am (pgs1), the leader with the lock
Sep 25 19:12:59 pgs1.omadomain.fi patroni[181756]: Exception in thread Thread-6 (process_request_thread):
Sep 25 19:12:59 pgs1.omadomain.fi patroni[181756]: Traceback (most recent call last):
Sep 25 19:12:59 pgs1.omadomain.fi patroni[181756]:   File "/usr/lib/python3.12/threading.py", line 1073, in _bootstrap_inner
Sep 25 19:12:59 pgs1.omadomain.fi patroni[181756]:     self.run()
Sep 25 19:12:59 pgs1.omadomain.fi patroni[181756]:   File "/usr/lib/python3.12/threading.py", line 1010, in run
Sep 25 19:12:59 pgs1.omadomain.fi patroni[181756]:     self._target(*self._args, **self._kwargs)
Sep 25 19:12:59 pgs1.omadomain.fi patroni[181756]:   File "/usr/lib/python3/dist-packages/patroni/api.py", line 1703, in process_request_thread
Sep 25 19:12:59 pgs1.omadomain.fi patroni[181756]:     request.do_handshake()
Sep 25 19:12:59 pgs1.omadomain.fi patroni[181756]:   File "/usr/lib/python3.12/ssl.py", line 1320, in do_handshake
Sep 25 19:12:59 pgs1.omadomain.fi patroni[181756]:     self._sslobj.do_handshake()
Sep 25 19:12:59 pgs1.omadomain.fi patroni[181756]: ssl.SSLError: [SSL: SSLV3_ALERT_BAD_CERTIFICATE] sslv3 alert bad certificate (_ssl.c:1000)
Sep 25 19:13:00 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:13:00,469 INFO: no action. I am (pgs1), the leader with the lock
Sep 25 19:13:01 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:13:01,469 INFO: no action. I am (pgs1), the leader with the lock
Sep 25 19:13:02 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:13:02,470 INFO: no action. I am (pgs1), the leader with the lock
Sep 25 19:13:03 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:13:03,470 INFO: no action. I am (pgs1), the leader with the lock
Sep 25 19:13:04 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:13:04,469 INFO: no action. I am (pgs1), the leader with the lock
Sep 25 19:13:04 pgs1.omadomain.fi patroni[181784]: 2025-09-25 19:13:04.624 EEST [181784] LOG:  checkpoint starting: force wait
Sep 25 19:13:04 pgs1.omadomain.fi patroni[181756]: Exception in thread Thread-7 (process_request_thread):
Sep 25 19:13:04 pgs1.omadomain.fi patroni[181756]: Traceback (most recent call last):
Sep 25 19:13:04 pgs1.omadomain.fi patroni[181756]:   File "/usr/lib/python3.12/threading.py", line 1073, in _bootstrap_inner
Sep 25 19:13:04 pgs1.omadomain.fi patroni[181756]:     self.run()
Sep 25 19:13:04 pgs1.omadomain.fi patroni[181756]:   File "/usr/lib/python3.12/threading.py", line 1010, in run
Sep 25 19:13:04 pgs1.omadomain.fi patroni[181756]:     self._target(*self._args, **self._kwargs)
Sep 25 19:13:04 pgs1.omadomain.fi patroni[181756]:   File "/usr/lib/python3/dist-packages/patroni/api.py", line 1703, in process_request_thread
Sep 25 19:13:04 pgs1.omadomain.fi patroni[181756]:     request.do_handshake()
Sep 25 19:13:04 pgs1.omadomain.fi patroni[181756]:   File "/usr/lib/python3.12/ssl.py", line 1320, in do_handshake
Sep 25 19:13:04 pgs1.omadomain.fi patroni[181756]:     self._sslobj.do_handshake()
Sep 25 19:13:04 pgs1.omadomain.fi patroni[181756]: ssl.SSLError: [SSL: SSLV3_ALERT_BAD_CERTIFICATE] sslv3 alert bad certificate (_ssl.c:1000)
Sep 25 19:13:05 pgs1.omadomain.fi patroni[181784]: 2025-09-25 19:13:05.130 EEST [181784] LOG:  checkpoint complete: wrote 4 buffers (0.0%), wrote 3 SLRU buffers; 0 WAL file(s) added, 0 removed, 0 recycled; write=0.414 s, sync=0.015 s, total=0.507 s; sync files=6, longest=0.009 s, average=0.003 s; distance=8843 kB, estimate=8843 kB; lsn=0/2000080, redo lsn=0/2000028
Sep 25 19:13:05 pgs1.omadomain.fi patroni[181784]: 2025-09-25 19:13:05.165 EEST [181784] LOG:  checkpoint starting: force wait
Sep 25 19:13:05 pgs1.omadomain.fi patroni[181784]: 2025-09-25 19:13:05.331 EEST [181784] LOG:  checkpoint complete: wrote 0 buffers (0.0%), wrote 0 SLRU buffers; 0 WAL file(s) added, 0 removed, 0 recycled; write=0.024 s, sync=0.001 s, total=0.166 s; sync files=0, longest=0.000 s, average=0.000 s; distance=16384 kB, estimate=16384 kB; lsn=0/3000080, redo lsn=0/3000028

Patronin conffi luodaan ansiblen playbookilla automaattisesti, niin että ainoastaan pgs1:sen conffi sisältää bootstrap-lohkon kun oli ongelmaa saada bootstrap tapahtumaan kun kaikki jäi odottamaan leaderia ja muilla on muuten sama, mutta se bootstrap-lohko puuttuu ja tietenkin ip-osoitteet ja hostin nimet muokattu kyseiseen hostiin sopivaksi.

pgs1:~$ cat /etc/patroni/config.yml

YAML:

scope: postgresql-cluster
name: pgs1

restapi:
  listen: 0.0.0.0:8008
  connect_address: 192.168.88.25:8008
  certfile: /var/lib/postgresql/ssl/patroni-rest-api-pgs1.pem
  keyfile: /var/lib/postgresql/ssl/patroni-rest-api-pgs1.key
  cafile: /var/lib/postgresql/ssl/patroni-ca.pem
  verify_client: none

ctl:
  cacert: /var/lib/postgresql/ssl/patroni-ca.pem
  certfile: /var/lib/postgresql/ssl/patroni-ctl-pgs1.pem
  keyfile: /var/lib/postgresql/ssl/patroni-ctl-pgs1.key

consul:
  url: https://127.0.0.1:8501
  verify: true
  cacert: /var/lib/postgresql/ssl/consul-agent-ca.pem
  cert: /var/lib/postgresql/ssl/consul-client-pgs1-cert.pem
  key: /var/lib/postgresql/ssl/consul-client-pgs1-key.pem
  register_service: true
  dc: debsu-dc
  service_tags:
    - traefik.enable=true
    - traefik.tcp.routers.pgsql.rule=HostSNI(`*`)
    - traefik.tcp.routers.pgsql.entrypoints=postgres
    - traefik.tcp.services.pgsql.loadbalancer.server.port=5432

bootstrap:
  dcs:
    ttl: 2
    loop_wait: 1
    retry_timeout: 5
    maximum_lag_on_failover: 1048576
    postgresql:
      parameters:
        ssl: 'on'
        ssl_ca_file: /var/lib/postgresql/ssl/patroni-ca.pem
        ssl_cert_file: /var/lib/postgresql/ssl/server.pem
        ssl_key_file: /var/lib/postgresql/ssl/server.key
        max_connections: 100
        shared_buffers: 256MB
        wal_level: replica
        hot_standby: "on"
        wal_keep_size: 64
        max_wal_senders: 10
        max_replication_slots: 10
      pg_hba:
        - hostssl replication pgrepl 127.0.0.1/32 md5
        - hostssl replication pgrepl 192.168.88.25/24 md5
        - hostssl replication pgrepl 192.168.88.26/24 md5
        - hostssl replication pgrepl 192.168.88.27/24 md5
        - hostssl all all 127.0.0.1/32 md5
        - hostssl all all 0.0.0.0/0 md5
  initdb:
    - encoding: UTF8
    - locale: en_US.UTF-8
    - data-checksums
  users:
    pgadmin:
      password: <salasana>
      options:
        - superuser
        - createdb
        - createrole
    pgrepl:
      password: <salasana>
      options:
        - replication

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.88.25:5432
  data_dir: /var/lib/postgresql/data
  bin_dir: /usr/lib/postgresql/18/bin
  authentication:
    replication:
      username: pgrepl
      password: <salasana>
    superuser:
      username: pgadmin
      password: <salasana>

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false

Tässä vielä esimerkkinä pgs2:sen conffi:

pgs2:~$ cat /etc/patroni/config.yml

YAML:

scope: postgresql-cluster
name: pgs2

restapi:
  listen: 0.0.0.0:8008
  connect_address: 192.168.88.26:8008
  certfile: /var/lib/postgresql/ssl/patroni-rest-api-pgs2.pem
  keyfile: /var/lib/postgresql/ssl/patroni-rest-api-pgs2.key
  cafile: /var/lib/postgresql/ssl/patroni-ca.pem
  verify_client: none

ctl:
  cacert: /var/lib/postgresql/ssl/patroni-ca.pem
  certfile: /var/lib/postgresql/ssl/patroni-ctl-pgs2.pem
  keyfile: /var/lib/postgresql/ssl/patroni-ctl-pgs2.key

consul:
  url: https://127.0.0.1:8501
  verify: true
  cacert: /var/lib/postgresql/ssl/consul-agent-ca.pem
  cert: /var/lib/postgresql/ssl/consul-client-pgs2-cert.pem
  key: /var/lib/postgresql/ssl/consul-client-pgs2-key.pem
  register_service: true
  dc: debsu-dc
  service_tags:
    - traefik.enable=true
    - traefik.tcp.routers.pgsql.rule=HostSNI(`*`)
    - traefik.tcp.routers.pgsql.entrypoints=postgres
    - traefik.tcp.services.pgsql.loadbalancer.server.port=5432

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.88.26:5432
  data_dir: /var/lib/postgresql/data
  bin_dir: /usr/lib/postgresql/18/bin
  authentication:
    replication:
      username: pgrepl
      password: <salasana>
    superuser:
      username: pgadmin
      password: <salasana>

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false

Kaikki certifikaatit luodaan myös samassa playbookissa ja Patronin rest-api-, ctl- ja postgres-certifikaatit luodaan käyttäen samaa itseluotua Patroni CA:ta. Consulia varten on oma CA ja Consulin ja Patronin välinen yhteys pelaa kun ~~Consuliin ilmestyy service~~ ja KV-varantoon tiedot tiedot tuosta klusterista.

Alla esimerkkinä pgs1:sen Patroni certit:

pgs1:~# openssl x509 -noout -text -in /var/lib/postgresql/ssl/patroni-ca.pem

Koodi:

Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number:
            46:7d:22:e3:30:71:4d:f7:57:6b:c3:ae:3e:03:b3:9d:26:9c:46:67
        Signature Algorithm: sha256WithRSAEncryption
        Issuer: CN = Patroni CA
        Validity
            Not Before: Sep 25 15:35:35 2025 GMT
            Not After : Sep 23 15:35:35 2035 GMT
        Subject: CN = Patroni CA
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                Public-Key: (4096 bit)
                Modulus:
                    <modulus>
                Exponent: 65537 (0x10001)
        X509v3 extensions:
            X509v3 Key Usage: critical
                Digital Signature, Certificate Sign, CRL Sign
            X509v3 Basic Constraints: critical
                CA:TRUE
            X509v3 Subject Key Identifier:
                20:4B:EA:E7:B0:2A:5A:50:CF:39:A1:03:D6:EA:70:B0:82:6A:30:23
    Signature Algorithm: sha256WithRSAEncryption
    Signature Value:
        <vallue>

pgs1:~# openssl x509 -noout -text -in /var/lib/postgresql/ssl/patroni-rest-api-pgs1.pem

Koodi:

Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number:
            0e:04:d4:39:7d:8c:a3:de:3b:62:dd:57:f0:c7:9d:1e:8d:45:2b:26
        Signature Algorithm: sha256WithRSAEncryption
        Issuer: CN = Patroni CA
        Validity
            Not Before: Sep 25 15:35:36 2025 GMT
            Not After : Sep 24 15:35:36 2030 GMT
        Subject: CN = localhost
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                Public-Key: (2048 bit)
                Modulus:
                    <modulus>
                Exponent: 65537 (0x10001)
        X509v3 extensions:
            X509v3 Subject Alternative Name:
                IP Address:192.168.88.25, IP Address:127.0.0.1, DNS:localhost, DNS:pgs1, DNS:pgs1.int.debsu.eu
            X509v3 Key Usage:
                Digital Signature, Key Encipherment
            X509v3 Extended Key Usage:
                TLS Web Server Authentication
            X509v3 Subject Key Identifier:
                03:21:42:29:09:38:78:18:ED:AE:01:69:00:D3:59:AC:66:A6:71:BC
            X509v3 Authority Key Identifier:
                20:4B:EA:E7:B0:2A:5A:50:CF:39:A1:03:D6:EA:70:B0:82:6A:30:23
    Signature Algorithm: sha256WithRSAEncryption
    Signature Value:
        <value>

pgs1:~# openssl x509 -noout -text -in /var/lib/postgresql/ssl/patroni-ctl-pgs1.pem

Koodi:

Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number:
            38:57:56:2c:52:e3:be:be:f2:42:c4:80:90:56:17:5e:e8:62:76:cc
        Signature Algorithm: sha256WithRSAEncryption
        Issuer: CN = Patroni CA
        Validity
            Not Before: Sep 25 15:35:39 2025 GMT
            Not After : Sep 24 15:35:39 2030 GMT
        Subject: CN = localhost
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                Public-Key: (2048 bit)
                Modulus:
                    <modulus>
                Exponent: 65537 (0x10001)
        X509v3 extensions:
            X509v3 Subject Alternative Name:
                IP Address:192.168.88.25, IP Address:127.0.0.1, DNS:localhost, DNS:pgs1, DNS:pgs1.int.debsu.eu
            X509v3 Key Usage:
                Digital Signature, Key Encipherment
            X509v3 Extended Key Usage:
                TLS Web Client Authentication
            X509v3 Subject Key Identifier:
                99:BB:60:4A:04:09:D4:5C:11:86:86:AF:DD:A6:25:9A:9F:4A:1E:84
            X509v3 Authority Key Identifier:
                20:4B:EA:E7:B0:2A:5A:50:CF:39:A1:03:D6:EA:70:B0:82:6A:30:23
    Signature Algorithm: sha256WithRSAEncryption
    Signature Value:
        <value>

pgs1:~# openssl x509 -noout -text -in /var/lib/postgresql/ssl/patroni-postgres-pgs1.pem

Koodi:

Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number:
            12:00:36:0b:b8:4a:cf:f0:ec:d5:c1:90:3c:e7:60:9c:49:1e:af:0b
        Signature Algorithm: sha256WithRSAEncryption
        Issuer: CN = Patroni CA
        Validity
            Not Before: Sep 25 15:35:42 2025 GMT
            Not After : Sep 24 15:35:42 2030 GMT
        Subject: CN = localhost
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                Public-Key: (2048 bit)
                Modulus:
                    <modulus>
                Exponent: 65537 (0x10001)
        X509v3 extensions:
            X509v3 Subject Alternative Name:
                IP Address:192.168.88.25, IP Address:127.0.0.1, DNS:localhost, DNS:pgs1, DNS:pgs1.int.debsu.eu
            X509v3 Key Usage:
                Digital Signature, Key Encipherment
            X509v3 Extended Key Usage:
                TLS Web Server Authentication
            X509v3 Subject Key Identifier:
                F1:B1:98:AB:0C:19:61:A4:A6:20:86:15:7A:40:5C:FA:96:7B:73:22
            X509v3 Authority Key Identifier:
                20:4B:EA:E7:B0:2A:5A:50:CF:39:A1:03:D6:EA:70:B0:82:6A:30:23
    Signature Algorithm: sha256WithRSAEncryption
    Signature Value:
        <value>

Patronin käyttämien certtien käyttöoikeudet on määritelty seuraavanlaisesti:

pgs1:~# ls -l /var/lib/postgresql/ssl/

Bash:

total 40
-rw-r--r-- 1 postgres postgres 1785 Sep 25 19:12 consul-agent-ca.pem
-rw-r--r-- 1 postgres postgres 1631 Sep 25 19:12 consul-client-pgs1-cert.pem
-rw------- 1 postgres postgres 1679 Sep 25 19:12 consul-client-pgs1-key.pem
-rw-r--r-- 1 postgres postgres 1785 Sep 25 19:12 patroni-ca.pem
-rw------- 1 postgres postgres 1679 Sep 25 19:12 patroni-ctl-pgs1.key
-rw-r--r-- 1 postgres postgres 1566 Sep 25 19:12 patroni-ctl-pgs1.pem
-rw------- 1 postgres postgres 1675 Sep 25 19:12 patroni-postgres-pgs1.key
-rw-r--r-- 1 postgres postgres 1566 Sep 25 19:12 patroni-postgres-pgs1.pem
-rw------- 1 postgres postgres 1675 Sep 25 19:12 patroni-rest-api-pgs1.key
-rw-r--r-- 1 postgres postgres 1566 Sep 25 19:12 patroni-rest-api-pgs1.pem
lrwxrwxrwx 1 root     root       49 Sep 25 19:12 server.key -> /var/lib/postgresql/ssl/patroni-postgres-pgs1.key
lrwxrwxrwx 1 root     root       49 Sep 25 19:12 server.pem -> /var/lib/postgresql/ssl/patroni-postgres-pgs1.pem

Mikä noissa certifikaateissa on vialla, että se siittä valittaa? Käsittääkseni se valittaa tuosta REST API:n certifikaatista, mutta mikä siinä on ongelmana? Olen yrittänyt käydä läpi tuota ongelmaa ChatGPT:n ja Google Geminin avulla, mutta se ei edisty niiden avullakaan.

[edit] Olen kokeillut vaihtaa certtien CN:ksi ip-osoitteen, localhostin, tai <hostname>.omadomain.fi, mutta ne eivät auta.

[edit] Komento

curl -k https://pgs3:8008/cluster  --cacert /var/lib/postgresql/ssl/patroni-ca.pem --cert /var/lib/postgresql/ssl/patroni-ctl-pgs1.pem --key /var/lib/postgresql/ssl/patroni-ctl-pgs1.key | jq .

palauttaa:

Koodi:

  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   547    0   547    0     0  11154      0 --:--:-- --:--:-- --:--:-- 11395
{
  "members": [
    {
      "name": "pgs1",
      "role": "leader",
      "state": "running",
      "api_url": "https://192.168.88.25:8008/patroni",
      "host": "192.168.88.25",
      "port": 5432,
      "timeline": 1
    },
    {
      "name": "pgs2",
      "role": "replica",
      "state": "streaming",
      "api_url": "https://192.168.88.26:8008/patroni",
      "host": "192.168.88.26",
      "port": 5432,
      "timeline": 1,
      "lag": 0
    },
    {
      "name": "pgs3",
      "role": "replica",
      "state": "streaming",
      "api_url": "https://192.168.88.27:8008/patroni",
      "host": "192.168.88.27",
      "port": 5432,
      "timeline": 1,
      "lag": 0
    }
  ],
  "scope": "postgresql-cluster"
}

joten eikö tuo meinaa, että certit toimii kyllä?
[edit] Se näköjään palauttaa saman, vaikka ei käytäkkään noita certtejä komennolla pgs1:~# curl -k https://pgs3:8008/cluster | jq .

Torz · eilen klo 21:45

curlin -k vipu nimenomaan skippaa certin tarkistuksen, jolloin ei ole väliä mitä syötät --cacert optiolla.

Lähtisin siitä kokeile ilman -k vipua ja koita selvittää mikä se virhe on, johtuuko domainista vai varmenneketjun puutteesta vai mistä. Eli tarkempaa virhettä selville ja sen avulla tekoäly varmaankin osaa jo auttaa eteenpäin.

SamCer · eilen klo 22:27

Torz sanoi:
curlin -k vipu nimenomaan skippaa certin tarkistuksen, jolloin ei ole väliä mitä syötät --cacert optiolla.

Lähtisin siitä kokeile ilman -k vipua ja koita selvittää mikä se virhe on, johtuuko domainista vai varmenneketjun puutteesta vai mistä. Eli tarkempaa virhettä selville ja sen avulla tekoäly varmaankin osaa jo auttaa eteenpäin.

Ilman -k -vipua toimii ongelmitta, eli komennolla

curl https://pgs3:8008/cluster  --cacert /var/lib/postgresql/ssl/patroni-ca.pem --cert /var/lib/postgresql/ssl/patroni-ctl-pgs1.pem --key /var/lib/postgresql/ssl/patroni-ctl-pgs1.key | jq .

Patroni PostgresSQL-klusteri TLS ongelma

SamCer

Torz

SamCer

Uutiset

Uutisia lyhyesti

Uusimmat viestit

Statistiikka

Hinta.fi

Arvostamme yksityisyyttäsi